about subsuery of tsql (or anything like oracle's sequence in sql server )

exec('insert
into '+@.DestName+ '(AF_nFieldID,AF_nPublicationID,AF_nEditi
onID,AF_nAdTypeID,AF_strCode,AF_strDescription,AF_nSortID,A
F_cIfValid)'+
'select (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+'),SPD_nPubMask,SPD_nEditionMask,SPD_nGrid
ID,SPD_strPageID,SPD_strDescription,SPD_nSortNum,SPD_bIsVal
id from '+@.SourceName )
i want the subsqury (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+') make a new auto increase ID every
insert,but the sub query only make one ID,or any method
like oracle's sequence?thanksLook at identity in bol.
It will increment the field for every record inserted.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||thank you,i know the identity ,
but my question is that the destinatin table already have
data,and can't change the field to idenity
>--Original Message--
>Look at identity in bol.
>It will increment the field for every record inserted.
>Nigel Rivett
>www.nigelrivett.net
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

About sp_execute

There are many procedures in my database.I think the cpu is high and I used
profiler and got the informations like this:
exec sp_execute 2,82439484
What does it means?How can I get the procedure which been executed?
Thanks very mach!!Hi
http://lists.ibiblio.org/pipermail/freetds/2002q3/009050.html
"What is ''MISCELLANEOUS''?" <WhatisMISCELLANEOUS@.discussions.microsoft.com>
wrote in message news:506FB6E4-781C-49A6-83E2-7F0C86FE6F46@.microsoft.com...
> There are many procedures in my database.I think the cpu is high and I
> used
> profiler and got the informations like this:
> exec sp_execute 2,82439484
> What does it means?How can I get the procedure which been executed?
> Thanks very mach!!

about exec sp_dropserver

hello, i have on error for the configurepublishing and subscribers
the error message was:
SQL Server Enterprise Manager could not complete the wizard because
@.@.SERVERNAME for '' is null.
Use sp_addserver to set @.@.SERVERNAME.
but it on the query analyze type the command to view my @.@.SERVERNAME in
sysservers
use master
select * from master.dbo.syservers
i can view my servername in the recordset row..
so ...i want to use sp_dropserver to delete at first, and rebuild the record
to use sp_addserver the old servername
but when i type such below command in query analyzes windows..
use master
exec sp_dropserver 'ns'
.......but .........i have connect the server at first so have error
such as ...
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'ns'.
so...what can i use the rebuild the @.@.SERVERNAME is not null...
========================================
ps.i use the MS_SQL_7.0 Version
Thank you very much!!!!
Try this script:
Use Master
go
Sp_DropServer 'ns' , 'droplogins'
GO
Use Master
go
Sp_Addserver 'ns', 'local'
GO
Stop and Start SQL Services
Afterwards, you'll need to recreate your remote logins.
HTH,
Paul Ibison
|||i use remote control in the windows system...to operate this job..
and i go those command in the query analyzes it is error below:
Server: Msg 20582, Level 16, State 1, Line 1
Cannot drop server 'ns' because it is used as a Publisher in replication.
Server: Msg 15028, Level 16, State 1, Line 1
The server 'ns' already exists.
but i in the SQL Server Enterprise Manager can't view the Publisher or
Subscriptor in the windows but i can't find the security -> remote server it
is have tow server name
one is ns
two is repl_distributor
but i can't delete the ns or repl_distributor
when i del it,it will show error message below:
Error 20582: Cannot drop server 'NS' because it is used as a Publisher in
replication.
Error 15190:There are still remote logins for the server 'repl_distributor'.
================================
thank for help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> д?
news:e31WN6yYEHA.644@.tk2msftngp13.phx.gbl...
> Try this script:
> Use Master
> go
> Sp_DropServer 'ns' , 'droplogins'
> GO
> Use Master
> go
> Sp_Addserver 'ns', 'local'
> GO
> Stop and Start SQL Services
> Afterwards, you'll need to recreate your remote logins.
> HTH,
> Paul Ibison
>
|||OK - go to the distributor properties (right-click replication monitor, select properties, select the publishers tab) and disable ns as a publisher then run my script.
HTH,
Paul Ibison

About EXEC dynamic query ?

Hello SQL Scripters,
I have the following MS/SQL server 2000 Script:
*******************
use NorthWind
GO
declare @.RecordCount int
,@.SelectString varchar(80)
,@.QueryString varchar(80)
,@.sQuote varchar(1)
set @.sQuote=''''
set @.QueryString='LastName like '+@.sQuote+'%'+'Kos'+'%'+@.sQuote
set @.SelectString='select count(*) from dbo.Employees where
'+@.QueryString
set @.RecordCount=0
exec(@.SelectString)
GO
*******************
How can I extract the result to the local variable @.RecordCount ?
In my case, I have to use the EXEC method to perform dynamic query.
TIA
Thomas
I got it. I use @.@.ROWCOUNT
Thanks
Thomas
|||No actually that is not the correct way. First off there is no need to use
dynamic sql at all here as the SARG can be a variable.
DECLARE @.x VARCHAR(50), @.y INT
SET @.x = @.sQuote+'%'+'Kos'+'%'+@.sQuote
select count(*) from dbo.Employees where
LastName like @.x
If you want the count in a variable you can do this:
select @.y = count(*) from dbo.Employees where
LastName like @.x
And if you did have the need to use dynamic sql and wanted to return a value
you should use sp_executesql instead. See here for more details:
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
Andrew J. Kelly SQL MVP
"Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
news:OrH$yzr3EHA.1188@.tk2msftngp13.phx.gbl...
>I got it. I use @.@.ROWCOUNT
> Thanks
> Thomas
>
|||Andrew,
Thanks for your response. The problem with my requirement is that I have to
construct the dynamic SQL string. This is a "Procedure" that allows the user
to submit fields s/he wishes to query against a selected Table and selected
list of fields within a selected table at runtime.
Therefore, your instruction "select count(*) from dbo.Employees where
LastName like @.x" will not work for me because the field "LastName" is also
dynamic (unknown at design time).
Here is my Procedure in real life: (@.QryString can be as "LastName like
'Woo%' and Occupation='Golf' order by LastName,WinningScore; @.TableName can
be "GolfTournament"; @.RV=Return Value that holds the number of records; and
finally, the returned dataset (not shown here))
****************************
Create Procedure tQuery
@.QryString varchar(2048)
, @.Tablename varchar(60)
, @.RV int out as
Declare @.s varchar(2048)
set @.s='select * from '+@.TableName+' Where '+@.QryString
EXEC(@.s)
set @.RV=@.@.ROWCOUNT --This always gives me the accurate number of
records returned by the @.QryString
Return @.RV
Go
*****************************
As a result, I had to use the @.@.ROWCOUNT immediately after the EXEC(@.s). My
goal is to count 1st to make sure there is data for a submitted QryString,
but I just cannot simply say:
set @.RV=(select count(*) from @.Tablename where @.QryString
The above instreuction just does not compile.
Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23umX%23Ks3EHA.1976@.TK2MSFTNGP09.phx.gbl...
> No actually that is not the correct way. First off there is no need to
> use dynamic sql at all here as the SARG can be a variable.
>
> DECLARE @.x VARCHAR(50), @.y INT
> SET @.x = @.sQuote+'%'+'Kos'+'%'+@.sQuote
> select count(*) from dbo.Employees where
> LastName like @.x
>
> If you want the count in a variable you can do this:
>
> select @.y = count(*) from dbo.Employees where
> LastName like @.x
>
> And if you did have the need to use dynamic sql and wanted to return a
> value you should use sp_executesql instead. See here for more details:
>
> http://www.support.microsoft.com/?id=262499 Using OutPut Params &
> sp_executeSql
> http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
>
> --
> Andrew J. Kelly SQL MVP
>
> "Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
> news:OrH$yzr3EHA.1188@.tk2msftngp13.phx.gbl...
>
|||If you simply want to find out if there are any rows that match the query
you should use EXISTS instead of count(*) as it can be much more efficient.
But in any case the links I sent should be helpful in working with dynamic
sql.
Andrew J. Kelly SQL MVP
"Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
news:efgZqjs3EHA.4004@.tk2msftngp13.phx.gbl...
> Andrew,
> Thanks for your response. The problem with my requirement is that I have
> to construct the dynamic SQL string. This is a "Procedure" that allows the
> user to submit fields s/he wishes to query against a selected Table and
> selected list of fields within a selected table at runtime.
> Therefore, your instruction "select count(*) from dbo.Employees where
> LastName like @.x" will not work for me because the field "LastName" is
> also dynamic (unknown at design time).
> Here is my Procedure in real life: (@.QryString can be as "LastName like
> 'Woo%' and Occupation='Golf' order by LastName,WinningScore; @.TableName
> can be "GolfTournament"; @.RV=Return Value that holds the number of
> records; and finally, the returned dataset (not shown here))
> ****************************
> Create Procedure tQuery
> @.QryString varchar(2048)
> , @.Tablename varchar(60)
> , @.RV int out as
> Declare @.s varchar(2048)
> set @.s='select * from '+@.TableName+' Where '+@.QryString
> EXEC(@.s)
> set @.RV=@.@.ROWCOUNT --This always gives me the accurate number of
> records returned by the @.QryString
> Return @.RV
> Go
> *****************************
> As a result, I had to use the @.@.ROWCOUNT immediately after the EXEC(@.s).
> My goal is to count 1st to make sure there is data for a submitted
> QryString, but I just cannot simply say:
> set @.RV=(select count(*) from @.Tablename where @.QryString
> The above instreuction just does not compile.
> Thomas
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23umX%23Ks3EHA.1976@.TK2MSFTNGP09.phx.gbl...
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ZRd5tt3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> If you simply want to find out if there are any rows that match the query
> you should use EXISTS instead of count(*) as it can be much more
> efficient. But in any case the links I sent should be helpful in working
> with dynamic sql.
>
Thanks Andrew. I need the RecordCount for calculation purposes. But the
EXISTS function is definitely good. I am exploring more about the links you
gave. Very good stuffs.
Take care!
Thomas

About EXEC dynamic query ?

Hello SQL Scripters,
I have the following MS/SQL server 2000 Script:
*******************
use NorthWind
GO
declare @.RecordCount int
,@.SelectString varchar(80)
,@.QueryString varchar(80)
,@.sQuote varchar(1)
set @.sQuote=''''
set @.QueryString='LastName like '+@.sQuote+'%'+'Kos'+'%'+@.sQuote
set @.SelectString='select count(*) from dbo.Employees where
'+@.QueryString
set @.RecordCount=0
exec(@.SelectString)
GO
*******************
How can I extract the result to the local variable @.RecordCount ?
In my case, I have to use the EXEC method to perform dynamic query.
TIA
Thomas
Hi Thomas,
If dynamic SQL is a requirement, you could us a temp table to hold the EXEC
value. For example:
************
use msdb
GO
declare @.SelectString varchar(80)
,@.QueryString varchar(80)
,@.sQuote varchar(1)
CREATE TABLE #RecordCount
(RecordCount int)
set @.sQuote=''''
set @.QueryString='name like '+@.sQuote+'%'+'sys'+'%'+@.sQuote
set @.SelectString='select count(*) from dbo.sysobjects where
'+@.QueryString
INSERT #RecordCount (RecordCount)
exec(@.SelectString)
SELECT RecordCount
FROM #RecordCount
DROP TABLE #RecordCount
************
Otherwise - if dynamic SQL isn't necessary, you could encapsulate this code
in a stored procedure and return the count value as an output parameter.
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"Thomas McFarlane" wrote:

> Hello SQL Scripters,
> I have the following MS/SQL server 2000 Script:
> *******************
> use NorthWind
> GO
> declare @.RecordCount int
> ,@.SelectString varchar(80)
> ,@.QueryString varchar(80)
> ,@.sQuote varchar(1)
> set @.sQuote=''''
> set @.QueryString='LastName like '+@.sQuote+'%'+'Kos'+'%'+@.sQuote
> set @.SelectString='select count(*) from dbo.Employees where
> '+@.QueryString
> set @.RecordCount=0
> exec(@.SelectString)
> GO
> *******************
> How can I extract the result to the local variable @.RecordCount ?
> In my case, I have to use the EXEC method to perform dynamic query.
> TIA
> Thomas
>
>
|||Hi Joe,
I'm looking for the same solution.
I tried your suggestion.
Unfortunately it doesn't work. I got this Error-Message:
The operation could not performed, because OLE DB-Provider 'MSDAORA' could not start a distributed transaction.
OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
(Original:
Die Operation konnte nicht ausgef?hrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte.
OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].)
I can execute the statement without the Temporary-Table.
Any ideas to this problem.......
Stefan
Message posted via http://www.sqlmonster.com
|||Hi Stefan,
Are you running this using a distributed query? Or against a local database?
The error you are getting relates to distributed query issues (MSDTC and the
lot).
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"Stefan Hensinger via SQLMonster.com" wrote:

> Hi Joe,
> I'm looking for the same solution.
> I tried your suggestion.
> Unfortunately it doesn't work. I got this Error-Message:
> The operation could not performed, because OLE DB-Provider 'MSDAORA' could not start a distributed transaction.
> OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
> (Original:
> Die Operation konnte nicht ausgef?hrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte.
> OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].)
> I can execute the statement without the Temporary-Table.
> Any ideas to this problem.......
> Stefan
> --
> Message posted via http://www.sqlmonster.com
>
|||Hi Joe,
You are right. I running this using a distributed query?
I will check the MSDTC.
Stefan
Message posted via http://www.sqlmonster.com