Hi
I am building a database using the Latin1_General_CI_AS collation.
Everything seems to work fine but I am stuck with this one view. When
creating the view I get this error:
Server: Msg 446, Level 16, State 9, Procedure qfmFieldUserNames, Line 4
Cannot resolve collation conflict for equal to operation.
View:
CREATE VIEW qfmFieldUserNames
AS
Select
T1.id,T1.name QFMTable,C1.colid,C1.name QFColumn,
Coalesce(D1.UserName,DL1.UserLabel,C1.name+'*') UserLabel
from sysobjects T1
left join syscolumns C1 on C1.id = T1.id
left join QFMDataItem D1 on D1.QFMTable = T1.name and D1.QFMColumn = C1.name
left join qfmQFMDataLabelLookup DL1 on DL1.QFMTable = T1.name and
DL1.QFMColumn = C1.name
where T1.type = 'U'
and C1.colid <> 1
and C1.name not in ('TimeStamp')
Does anyone have any tips?
Many Thanks.Lookup COLLATE in Books Online. Obviously the columns in the JOIN clause hav
e
different collations. You must declare which collation to use:
E.g.:
ColumnA has collation A
ColumnB has collation B
join ... on ColumnA collate B = ColumnB
or
join ... on ColumnA = ColumnB collate A
ML
http://milambda.blogspot.com/|||Thanks, I got to that too but all four columns are using the same collation
COLLATE SQL_Latin1_General_CP1_CI_AS
"ML" wrote:
> Lookup COLLATE in Books Online. Obviously the columns in the JOIN clause h
ave
> different collations. You must declare which collation to use:
> E.g.:
> ColumnA has collation A
> ColumnB has collation B
> join ... on ColumnA collate B = ColumnB
> or
> join ... on ColumnA = ColumnB collate A
>
> ML
> --
> http://milambda.blogspot.com/|||Weird. Try the database collation that you mentioned in your OP:
Latin1_General_CI_AS (on all affected columns in the FROM clause).
Could be due to the fact that you're trying to create a view.
ML
http://milambda.blogspot.com/|||ok I got pass the problem by collating the Coalesce part of the view as well
as these columns seems to have a different collation.
Select
T1.id,T1.name QFMTable,C1.colid,C1.name QFColumn,
Coalesce(D1.UserName,DL1.UserLabel,C1.name+'*') collate
SQL_Latin1_General_CP1_CI_AS UserLabel
from sysobjects T1
left join syscolumns C1 on C1.id = T1.id
left join QFMDataItem D1 on D1.QFMTable collate SQL_Latin1_General_CP1_CI_AS
= T1.name and D1.QFMColumn collate SQL_Latin1_General_CP1_CI_AS = C1.name
left join qfmQFMDataLabelLookup DL1 on DL1.QFMTable collate
SQL_Latin1_General_CP1_CI_AS = T1.name and DL1.QFMColumn collate
SQL_Latin1_General_CP1_CI_AS = C1.name
where T1.type = 'U'
and C1.colid <> 1
and C1.name not in ('TimeStamp')
"ML" wrote:
> Weird. Try the database collation that you mentioned in your OP:
> Latin1_General_CI_AS (on all affected columns in the FROM clause).
> Could be due to the fact that you're trying to create a view.
>
> ML
> --
> http://milambda.blogspot.com/|||Thank you, that's good to know.
ML
http://milambda.blogspot.com/
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Collation and Linked servers
Hey!
I got my self a little problem with collations and linked servers..
I have access to a View on a remote server and have set up a Linked server on my SQL-server..
I get all the data I want, the only trouble I have is getting the collations to work.
My default database collation is "Finnish_Swedish" and the remote server uses the "SQL_SwedishStd_Pref_CP1_CI_AS" collation..
When I get my data, all swedish caracters are scrambled to crap and it feels like I have tried everything possible.
I've tried using "select vchDescription collate SQL_SwedishStd_Pref_CP1_CI_AS", I've set the collation to "SQL_SwedishStd_Pref_CP1_CI_AS" in the table I transfer the data to on my server, I've tried "Use Remote Collation" when setting up the linked servers, man I've tried everythin but nothing seems to work.
What am I to do? Please help me..Seems to me you should use select vchDescription collate Finnish_Swedish if you want the data in Finnish_Swedish collation.|||Did you try to create a new test db with the same collation as the remote server and do your SELECT from there? I think there is a hidden issue that results in scrambled data.|||Originally posted by Paul Young
Seems to me you should use select vchDescription collate Finnish_Swedish if you want the data in Finnish_Swedish collation.
Yupp, I tried that to, but still no luck..|||Originally posted by rdjabarov
Did you try to create a new test db with the same collation as the remote server and do your SELECT from there? I think there is a hidden issue that results in scrambled data.
What I do is, compare the data I have on my SQL-server, with the data in the remote view. The data that has changed (ins/del/upd) gets inserted into a "temporary" table (not a #-table), thereafter I download pictures and other objects (since they need processing to fit into my system) and add them to the same table. When that's finished I move the data to the live table. The remote source has e.g. the pictures on disk (I want them in my database) with a very different format (both file type and size) than I use..
Both my temporary- and live-table uses the same collation as the remote view, but still the text gets scrambled on transfer (as soon as it is inserted into the temp-table)..|||What kind of data do you see if you just do a SELECT from that view, without inserting into a temp table? Is it scrambled at that time too?sqlsql
I got my self a little problem with collations and linked servers..
I have access to a View on a remote server and have set up a Linked server on my SQL-server..
I get all the data I want, the only trouble I have is getting the collations to work.
My default database collation is "Finnish_Swedish" and the remote server uses the "SQL_SwedishStd_Pref_CP1_CI_AS" collation..
When I get my data, all swedish caracters are scrambled to crap and it feels like I have tried everything possible.
I've tried using "select vchDescription collate SQL_SwedishStd_Pref_CP1_CI_AS", I've set the collation to "SQL_SwedishStd_Pref_CP1_CI_AS" in the table I transfer the data to on my server, I've tried "Use Remote Collation" when setting up the linked servers, man I've tried everythin but nothing seems to work.
What am I to do? Please help me..Seems to me you should use select vchDescription collate Finnish_Swedish if you want the data in Finnish_Swedish collation.|||Did you try to create a new test db with the same collation as the remote server and do your SELECT from there? I think there is a hidden issue that results in scrambled data.|||Originally posted by Paul Young
Seems to me you should use select vchDescription collate Finnish_Swedish if you want the data in Finnish_Swedish collation.
Yupp, I tried that to, but still no luck..|||Originally posted by rdjabarov
Did you try to create a new test db with the same collation as the remote server and do your SELECT from there? I think there is a hidden issue that results in scrambled data.
What I do is, compare the data I have on my SQL-server, with the data in the remote view. The data that has changed (ins/del/upd) gets inserted into a "temporary" table (not a #-table), thereafter I download pictures and other objects (since they need processing to fit into my system) and add them to the same table. When that's finished I move the data to the live table. The remote source has e.g. the pictures on disk (I want them in my database) with a very different format (both file type and size) than I use..
Both my temporary- and live-table uses the same collation as the remote view, but still the text gets scrambled on transfer (as soon as it is inserted into the temp-table)..|||What kind of data do you see if you just do a SELECT from that view, without inserting into a temp table? Is it scrambled at that time too?sqlsql
Monday, March 19, 2012
Collapse the parameters when I click view report
Hi, I am using RS 2005.
I have around 25-30 parameters which cover almost the whole screen, so
when I click on View Report, is it possible to automatically collapse
the parameters part of the window?
Thanks
AnupHi guys,
It's urgent, I am stuck at this and our requirement is when the view
report is clicked or when the report is displayed for the first time,
the parameters part of the report should be automatically collapse?
Thanks
Anup
Anup wrote:
> Hi, I am using RS 2005.
> I have around 25-30 parameters which cover almost the whole screen, so
> when I click on View Report, is it possible to automatically collapse
> the parameters part of the window?
> Thanks
> Anup|||If you are getting to the report via an href add:
&rc:Parameters=False
to the query string
Chris E
"Anup" <anupkm@.gmail.com> wrote in message
news:1166115447.858873.68660@.t46g2000cwa.googlegroups.com...
> Hi guys,
> It's urgent, I am stuck at this and our requirement is when the view
> report is clicked or when the report is displayed for the first time,
> the parameters part of the report should be automatically collapse?
> Thanks
> Anup
> Anup wrote:
>> Hi, I am using RS 2005.
>> I have around 25-30 parameters which cover almost the whole screen, so
>> when I click on View Report, is it possible to automatically collapse
>> the parameters part of the window?
>> Thanks
>> Anup
>
I have around 25-30 parameters which cover almost the whole screen, so
when I click on View Report, is it possible to automatically collapse
the parameters part of the window?
Thanks
AnupHi guys,
It's urgent, I am stuck at this and our requirement is when the view
report is clicked or when the report is displayed for the first time,
the parameters part of the report should be automatically collapse?
Thanks
Anup
Anup wrote:
> Hi, I am using RS 2005.
> I have around 25-30 parameters which cover almost the whole screen, so
> when I click on View Report, is it possible to automatically collapse
> the parameters part of the window?
> Thanks
> Anup|||If you are getting to the report via an href add:
&rc:Parameters=False
to the query string
Chris E
"Anup" <anupkm@.gmail.com> wrote in message
news:1166115447.858873.68660@.t46g2000cwa.googlegroups.com...
> Hi guys,
> It's urgent, I am stuck at this and our requirement is when the view
> report is clicked or when the report is displayed for the first time,
> the parameters part of the report should be automatically collapse?
> Thanks
> Anup
> Anup wrote:
>> Hi, I am using RS 2005.
>> I have around 25-30 parameters which cover almost the whole screen, so
>> when I click on View Report, is it possible to automatically collapse
>> the parameters part of the window?
>> Thanks
>> Anup
>
Sunday, March 11, 2012
code view access
Hello all, I need to prevent all users (other than sysadmin/dbo) privs to access the database code (SP/Views/UDF). What would be the best way to achieve that? TIA
In SQL Server 2005 this should be the default behavior. Please refer to BOL for further details:
http://msdn2.microsoft.com/en-us/library/ms191507.aspx
http://msdn2.microsoft.com/en-us/library/ms188371.aspx
If you have further question please let us know, we will be glad to help.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Thanks Raul, we are actually using SQL 2000 SP4 (8.00.2040 build) in all our environments.
Do we need to DENY access from sp_helptext/syscomments or any other objects? Any thoughts are appreciated. Thanks.
|||Unfortunately, there is really not a way to do that with SQL 2000 -I know, big time bummer.
If a user has permissions to execute the object, the user can see the code for the object -unless it is encryped (and SQL 2000 code encryption is very lame...) The concept of separating permissions for viewing the code and executing the code came about in SQL 2005.
|||Thanks, wouldn't protecting sp_helptext and syscomments resolve this? I guess whenever code is opened through any util, it must be querying syscomments either using sp_helptext / DMO in SQL 2000. Am I incorrect on this assumption?Saturday, February 25, 2012
CmdExec issue
Hello,
I have a batch file in the operating system, which runs an osql command that
create a stored procedure, a view and a function on a database. It is with
-E trust connect. It works when I just run it on windows.
However, if I run in as a SQL Job by using the CmdExec, it could not create
any stored procedure, view or function. And there is no error return
neither. The account to run the job is the same NT account with local admin
rights on the server.
Does anyone have some idea about this matter?
Many Thanks,
LianneAre you running the Job from a different server?
If so you need to use -U and -P providing a username and password. I
have experienced this problem before, but apparently linked servers do
not pass the user via windows authentication.|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I have a batch file in the operating system, which runs an osql command
> that create a stored procedure, a view and a function on a database. It
> is with -E trust connect. It works when I just run it on windows.
> However, if I run in as a SQL Job by using the CmdExec, it could not
> create any stored procedure, view or function. And there is no error
> return neither. The account to run the job is the same NT account with
> local admin rights on the server.
Did the job fail or succeed?
In case it failed, there is a checkbox in the upper right of the View
History dialog which says "Show job steps" or somesuch. The output from
the job step should have the real error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The job was running on the physical server. and the job return as
successful. but nothing was created on SQL Server side.
Thanks again,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Hi John,
I change the -E to -U sa login, but I got this error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.
Any idea?
Many Thanks,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:605603
Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I change the -E to -U sa login, but I got this error message:
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server connection.
> Any idea?
That would indicate that SQL authentication is not enabled on the server.
Well, changing the autentication should not matter, so consider that a
dead end.
As for why your job does not work as expected, I'm afraid that with the
information you have provided, all we can offer is wild guesses. What
is the exact command line you are using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Eriand,
the command line is below:
for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U sa -P
zhongshan -n -d test -i "%%f" >> Createcp.txt
this is the command I put in .bat file. on the same level, I have three sql
files, which creates a view, a user defined function and a stored procedure.
Also, I had set SQL Server running on both (SQL and NT authentication)
If you meed more informations, let me know.
Many Thanks,
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> That would indicate that SQL authentication is not enabled on the server.
> Well, changing the autentication should not matter, so consider that a
> dead end.
> As for why your job does not work as expected, I'm afraid that with the
> information you have provided, all we can offer is wild guesses. What
> is the exact command line you are using?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> the command line is below:
> for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U
> sa -P zhongshan -n -d test -i "%%f" >> Createcp.txt
> this is the command I put in .bat file. on the same level, I have three
> sql files, which creates a view, a user defined function and a stored
> procedure.
Real stupid question: the BAT file does include a CD to the directory
where you have the SQL, doesn't it?
I composed this file on my machine:
F:
cd \moje dokumenty\WWW\dynsearch
for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale -i "
%%f" >> Createcp.txt
And the I created a job of which the command text was
C:\temp\test.bat
I deleted all stored procedure in Northgale, and then started the job.
When I had completed the job, all my procedures were back.
In job history I saw this for the job step:
Executed as user: KESMETS\sql. ...search
F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Createcp.tx
t & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.txt &
.. Process Exit Code 0. The step succeeded.
A way to get more output from the job step is to go the Advanced tab
for the job step, and there select a output file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you very much for your suggection. I did not enclude the Cd line in
the .bat file since it work just fine when I run it through windows. My bad
.
I guess SQL is very piggy on directory specification then.
Thank you again. I appreciate your help very much!
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> Real stupid question: the BAT file does include a CD to the directory
> where you have the SQL, doesn't it?
> I composed this file on my machine:
> F:
> cd \moje dokumenty\WWW\dynsearch
> for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale
-i "%%f" >> Createcp.txt
> And the I created a job of which the command text was
> C:\temp\test.bat
> I deleted all stored procedure in Northgale, and then started the job.
> When I had completed the job, all my procedures were back.
> In job history I saw this for the job step:
>
> Executed as user: KES?METS?\sql. ...search
> F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Create
cp.txt & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.t
xt &... Process Exit Code 0. The step succeeded.
> A way to get more output from the job step is to go the Advanced tab
> for the job step, and there select a output file.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>
I have a batch file in the operating system, which runs an osql command that
create a stored procedure, a view and a function on a database. It is with
-E trust connect. It works when I just run it on windows.
However, if I run in as a SQL Job by using the CmdExec, it could not create
any stored procedure, view or function. And there is no error return
neither. The account to run the job is the same NT account with local admin
rights on the server.
Does anyone have some idea about this matter?
Many Thanks,
LianneAre you running the Job from a different server?
If so you need to use -U and -P providing a username and password. I
have experienced this problem before, but apparently linked servers do
not pass the user via windows authentication.|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I have a batch file in the operating system, which runs an osql command
> that create a stored procedure, a view and a function on a database. It
> is with -E trust connect. It works when I just run it on windows.
> However, if I run in as a SQL Job by using the CmdExec, it could not
> create any stored procedure, view or function. And there is no error
> return neither. The account to run the job is the same NT account with
> local admin rights on the server.
Did the job fail or succeed?
In case it failed, there is a checkbox in the upper right of the View
History dialog which says "Show job steps" or somesuch. The output from
the job step should have the real error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The job was running on the physical server. and the job return as
successful. but nothing was created on SQL Server side.
Thanks again,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Hi John,
I change the -E to -U sa login, but I got this error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.
Any idea?
Many Thanks,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:605603
Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I change the -E to -U sa login, but I got this error message:
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server connection.
> Any idea?
That would indicate that SQL authentication is not enabled on the server.
Well, changing the autentication should not matter, so consider that a
dead end.
As for why your job does not work as expected, I'm afraid that with the
information you have provided, all we can offer is wild guesses. What
is the exact command line you are using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Eriand,
the command line is below:
for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U sa -P
zhongshan -n -d test -i "%%f" >> Createcp.txt
this is the command I put in .bat file. on the same level, I have three sql
files, which creates a view, a user defined function and a stored procedure.
Also, I had set SQL Server running on both (SQL and NT authentication)
If you meed more informations, let me know.
Many Thanks,
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> That would indicate that SQL authentication is not enabled on the server.
> Well, changing the autentication should not matter, so consider that a
> dead end.
> As for why your job does not work as expected, I'm afraid that with the
> information you have provided, all we can offer is wild guesses. What
> is the exact command line you are using?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> the command line is below:
> for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U
> sa -P zhongshan -n -d test -i "%%f" >> Createcp.txt
> this is the command I put in .bat file. on the same level, I have three
> sql files, which creates a view, a user defined function and a stored
> procedure.
Real stupid question: the BAT file does include a CD to the directory
where you have the SQL, doesn't it?
I composed this file on my machine:
F:
cd \moje dokumenty\WWW\dynsearch
for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale -i "
%%f" >> Createcp.txt
And the I created a job of which the command text was
C:\temp\test.bat
I deleted all stored procedure in Northgale, and then started the job.
When I had completed the job, all my procedures were back.
In job history I saw this for the job step:
Executed as user: KESMETS\sql. ...search
F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Createcp.tx
t & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.txt &
.. Process Exit Code 0. The step succeeded.
A way to get more output from the job step is to go the Advanced tab
for the job step, and there select a output file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you very much for your suggection. I did not enclude the Cd line in
the .bat file since it work just fine when I run it through windows. My bad
.
I guess SQL is very piggy on directory specification then.
Thank you again. I appreciate your help very much!
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> Real stupid question: the BAT file does include a CD to the directory
> where you have the SQL, doesn't it?
> I composed this file on my machine:
> F:
> cd \moje dokumenty\WWW\dynsearch
> for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale
-i "%%f" >> Createcp.txt
> And the I created a job of which the command text was
> C:\temp\test.bat
> I deleted all stored procedure in Northgale, and then started the job.
> When I had completed the job, all my procedures were back.
> In job history I saw this for the job step:
>
> Executed as user: KES?METS?\sql. ...search
> F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Create
cp.txt & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.t
xt &... Process Exit Code 0. The step succeeded.
> A way to get more output from the job step is to go the Advanced tab
> for the job step, and there select a output file.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>
Tuesday, February 14, 2012
Clustered Indexed View or Custom Sync Object?
sql2k sp3
If you had more columns on Subscriber than Publisher,
would you be more likely to use Clustered Indexed Views or
Custom Sync Objects to implement 1 way, Transactional
Replication? Pros and Cons for each?
TIA, ChrisR
ChrisR,
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is increased overhead on
the publisher to maintain the materialized data. The upside is that there is
increased transparency so far as maintenance of replication is concerned.
Regards,
Paul Ibison
|||the problem is that for Chris Indexed Views won't support sp_repladdcolumn
and sp_repldropcolumn.
I am mystified as to why what works for me on my machine doesn't work on
ChrisRs. I am revisiting this script and I am hoping ChrisR and others can
test it. There may be a bug somewhere here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ChrisR,
> I personally would prefer to use indexed views.
> I suppose simplistically the downside is that there is increased overhead
on
> the publisher to maintain the materialized data. The upside is that there
is
> increased transparency so far as maintenance of replication is concerned.
> Regards,
> Paul Ibison
>
|||I thought it couldnt be done. Thats what I got from the
replies to my post:
sp_repladdcolumn on Indexed View
08/04/04 4:24 pm.
Did I misunderstand?
>--Original Message--
>the problem is that for Chris Indexed Views won't support
sp_repladdcolumn
>and sp_repldropcolumn.
>I am mystified as to why what works for me on my machine
doesn't work on
>ChrisRs. I am revisiting this script and I am hoping
ChrisR and others can[vbcol=seagreen]
>test it. There may be a bug somewhere here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
increased overhead[vbcol=seagreen]
>on
upside is that there[vbcol=seagreen]
>is
replication is concerned.
>
>.
>
|||Sorry for the confusion.
You can't use sp_repladdcolumn or sp_repldropcolumn on an indexed view which
you are publishing for replication.
The Custom Sync object works for me.
Here is the script that works for me.
Here is my TransDTL.SQL script.
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10),
[TranCode] [char] (4),
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10),
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23),
[MerchName] [varchar] (25),
[City] [varchar] (15),
[State] [varchar] (3),
[RejectReason] [varchar] (15),
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10),
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4)
) ON [PRIMARY]
GO
--this is my replication script
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
go
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--creating subscription
sp_addsubscription 'ChrisR','all', @.@.ServerName, 'ChrisRSub'
GO
--startup snapshot agent
DECLARE @.string varchar(200)
SET @.string = 'exec msdb.dbo.sp_start_job @.job_name =
'SELECT @.string = @.string + char(39) +
msdb.dbo.sysjobs.name + char(39) FROM
msdb.dbo.sysjobs,ChrisR.dbo.syspublications
WHERE ChrisR.dbo.syspublications.name = 'ChrisR'
AND ChrisR.dbo.syspublications.snapshot_jobid =
msdb.dbo.sysjobs.job_id
exec (@.string)
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=1
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=5
go
select * from transdtl where transdtlkey=5
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=5
GO
use chrisR
GO
insert into
transdtl(TransDtlKey,CustomerKey,SerialNbr,TranCod e,TransDate,TransDateShort
,TransDateMonth,TransDateYear,TransAmt,RefNbr,Merc hName,City,State,RejectRea
son,PostDate,PostDateShort,PostDateMonth,PostDateY ear,CreateDate,MerchSIC)
select
TransDtlKey+100,CustomerKey,SerialNbr,TranCode,Tra nsDate,TransDateShort,Tran
sDateMonth,TransDateYear,TransAmt,RefNbr,MerchName ,City,State,RejectReason,P
ostDate,PostDateShort,PostDateMonth,PostDateYear,C reateDate,MerchSIC from
transdtl where transdtlkey=5
go
select * from transdtl where transdtlkey=105
use chrisRSUB
GO
select * from transdtl where transdtlkey=105
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:9c2001c486c6$432bff60$a501280a@.phx.gbl...[vbcol=seagreen]
> I thought it couldnt be done. Thats what I got from the
> replies to my post:
> sp_repladdcolumn on Indexed View
> 08/04/04 4:24 pm.
> Did I misunderstand?
>
>
> sp_repladdcolumn
> doesn't work on
> ChrisR and others can
> increased overhead
> upside is that there
> replication is concerned.
If you had more columns on Subscriber than Publisher,
would you be more likely to use Clustered Indexed Views or
Custom Sync Objects to implement 1 way, Transactional
Replication? Pros and Cons for each?
TIA, ChrisR
ChrisR,
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is increased overhead on
the publisher to maintain the materialized data. The upside is that there is
increased transparency so far as maintenance of replication is concerned.
Regards,
Paul Ibison
|||the problem is that for Chris Indexed Views won't support sp_repladdcolumn
and sp_repldropcolumn.
I am mystified as to why what works for me on my machine doesn't work on
ChrisRs. I am revisiting this script and I am hoping ChrisR and others can
test it. There may be a bug somewhere here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ChrisR,
> I personally would prefer to use indexed views.
> I suppose simplistically the downside is that there is increased overhead
on
> the publisher to maintain the materialized data. The upside is that there
is
> increased transparency so far as maintenance of replication is concerned.
> Regards,
> Paul Ibison
>
|||I thought it couldnt be done. Thats what I got from the
replies to my post:
sp_repladdcolumn on Indexed View
08/04/04 4:24 pm.
Did I misunderstand?
>--Original Message--
>the problem is that for Chris Indexed Views won't support
sp_repladdcolumn
>and sp_repldropcolumn.
>I am mystified as to why what works for me on my machine
doesn't work on
>ChrisRs. I am revisiting this script and I am hoping
ChrisR and others can[vbcol=seagreen]
>test it. There may be a bug somewhere here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
increased overhead[vbcol=seagreen]
>on
upside is that there[vbcol=seagreen]
>is
replication is concerned.
>
>.
>
|||Sorry for the confusion.
You can't use sp_repladdcolumn or sp_repldropcolumn on an indexed view which
you are publishing for replication.
The Custom Sync object works for me.
Here is the script that works for me.
Here is my TransDTL.SQL script.
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10),
[TranCode] [char] (4),
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10),
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23),
[MerchName] [varchar] (25),
[City] [varchar] (15),
[State] [varchar] (3),
[RejectReason] [varchar] (15),
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10),
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4)
) ON [PRIMARY]
GO
--this is my replication script
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
go
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--creating subscription
sp_addsubscription 'ChrisR','all', @.@.ServerName, 'ChrisRSub'
GO
--startup snapshot agent
DECLARE @.string varchar(200)
SET @.string = 'exec msdb.dbo.sp_start_job @.job_name =
'SELECT @.string = @.string + char(39) +
msdb.dbo.sysjobs.name + char(39) FROM
msdb.dbo.sysjobs,ChrisR.dbo.syspublications
WHERE ChrisR.dbo.syspublications.name = 'ChrisR'
AND ChrisR.dbo.syspublications.snapshot_jobid =
msdb.dbo.sysjobs.job_id
exec (@.string)
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=1
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=5
go
select * from transdtl where transdtlkey=5
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=5
GO
use chrisR
GO
insert into
transdtl(TransDtlKey,CustomerKey,SerialNbr,TranCod e,TransDate,TransDateShort
,TransDateMonth,TransDateYear,TransAmt,RefNbr,Merc hName,City,State,RejectRea
son,PostDate,PostDateShort,PostDateMonth,PostDateY ear,CreateDate,MerchSIC)
select
TransDtlKey+100,CustomerKey,SerialNbr,TranCode,Tra nsDate,TransDateShort,Tran
sDateMonth,TransDateYear,TransAmt,RefNbr,MerchName ,City,State,RejectReason,P
ostDate,PostDateShort,PostDateMonth,PostDateYear,C reateDate,MerchSIC from
transdtl where transdtlkey=5
go
select * from transdtl where transdtlkey=105
use chrisRSUB
GO
select * from transdtl where transdtlkey=105
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:9c2001c486c6$432bff60$a501280a@.phx.gbl...[vbcol=seagreen]
> I thought it couldnt be done. Thats what I got from the
> replies to my post:
> sp_repladdcolumn on Indexed View
> 08/04/04 4:24 pm.
> Did I misunderstand?
>
>
> sp_repladdcolumn
> doesn't work on
> ChrisR and others can
> increased overhead
> upside is that there
> replication is concerned.
Sunday, February 12, 2012
Clustered Index on Indexed View from ADO.NET
I have a clustered index on an indexed view in sql server 2000. When I do a simple select in query anaylser from this view I can see from the execution plan in profiler that the clustered index was used to return the data, hence improving performance of the underlying select(this is why I am using the indexed view). However, if I run the query from an asp.net page using the sql provider I can see the call in profiler but the clustered index is not used, hence reducing the performance of the call considerably.
If anyone has experienced this please let me know.
CheersViews are query rewrites just persisted so the query optimizer usually ignore the execution plan. Try the link below to see if you can make changes to your create a View statement to improve performance. Hope this helps.
http://www.sqlteam.com/item.asp?ItemID=1015
If anyone has experienced this please let me know.
CheersViews are query rewrites just persisted so the query optimizer usually ignore the execution plan. Try the link below to see if you can make changes to your create a View statement to improve performance. Hope this helps.
http://www.sqlteam.com/item.asp?ItemID=1015
Subscribe to:
Posts (Atom)