How to exec stored proc dynamically

Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMain and
sp_RebuildIndexesSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndexesSub(@.listOnly bit=0, @.maxfrag Decimal=30.0)

This runs fine if I do pubs..sp_RebuildIndexesSub
However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndexesMain(@.dbName sysname, @.listOnly bit=0,
@.maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @.dbName

Declare @.execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @.dbName
If (@.@.FETCH_STATUS<>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @.dbName + ''''
Return -1
End

While(@.@.FETCH_STATUS=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @.dbName
Print Char(13)
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
EXEC sp_executesql @.execstr, N'@.listOnly bit, @.maxFrag Decimal',
@.listOnly, @.maxFrag
Fetch crDbs INTO @.dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
sunitjoshi@.netzero.comI believe if you change:
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
to
Set @.execstr = '[' + @.dbName + '..sp_RebuildIndexesSub] '

it should work.

Personally, instead of creating sp_RebuildIndexesSub in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndexesSub 0, 30.0
END'

Be sure not to run "exec master..sp_RebuildIndexesSub 0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.|||Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL..sp_RebuildIndexesSub'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

*** Sent via Developersdex http://www.developersdex.com ***|||Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@.execstr).

SET @.execstr = 'USE ' + @.dbname + ' exec sp_RebuildIndexesSub ' +
RTRIM(@.listOnly) + ',' + RTRIM(@.maxFrag)
EXEC (@.execstr)|||Got it. Had to change to this

Set @.execstr = @.dbName + '..sp_RebuildIndexesSub'
Exec @.execstr @.listOnly, @.maxFrag

thanks
Sunit|||You are right. Your code is much cleaner :)

How to edit store proc from Manegement Studio

I am new to sql server 2005 but this should be easy but what ever. Could someone explain how I can edit my existing store procedure from Management Studio? Any time I do a save it wants to save a .sql file !

Thanks

Click the execute button at the top to run the sql.|||Is it possible that when I try this it does excute the store proc but does not save it?|||You RIGHT click on the stored proc from the explorer on the left and click on "Modify". After you make your changes, you hit F5 key.|||

freedom1029 wrote:

I am new to sql server 2005 but this should be easy but what ever. Could someone explain how I can edit my existing store procedure from Management Studio? Any time I do a save it wants to save a .sql file !

Thanks

SQL Server files and that includes service packs are.sql files but you can always open them with note pad and save as .txt, I usually save a copy as .txt which I can open and adjust as needed. Hope this helps.

|||

Even if I might sound stupid I dont get it :( Or maybe it's me that does not explain my self clearly enoungh. Previously with sql 2000 with Sql Server Entreprise Manager I sed to go in stored procedures, double click on the procedure that I wanted to modify, the the a window open where I was able to modify my procedure and click ok and that was it.

Now in SQL Server Management Studio
Right click and modify ok but F5 execute it and do not save it. I need to modify my procedure permanently.

And File > Save want to save a sql or txt file somewhere on my disc and does not modify my store procedure premennatly either

So what am I missing?

Thanks for your help

|||

I am sorry I was not clear you need to right click on the stored procedure .sql file and you will see open with and choose note pad, then save a copy as .txt and you can modify that copy and save it back as .txt or .sql. It is not automatic but it keeps management studio or query analyzer out of what I put in or take out.

I have used it to open and read SQL Server 2000 service packs and mile long AdventureWorks installation file. I am sorry I was not clear mine uses notepad to open and save the file because notepad can save it as .sql if you need to run it automatically. Hope this helps.

|||

Ok I see where my confusion is coming from

I copied my database from 2000 over to a 2005 Sql Server and all my store proc have 3 lines added at the top of them

setANSI_NULLSON
setQUOTED_IDENTIFIERON
go

Anyway I was just trying to set those to lines of OFF and F5 was not saving my changes

I guess those settings are controled from somewhere else

How to duplicate a row in SQL Table using a single query

Hi,
I have a table with about 50 fields including intCompanyId as the
table identity.
I want to be able to use one query (or a few in a stored proc to
duplicate a specific row in this table.
I do not want to list the fields in case some are added later.
Ex : insert into T_Company select * from T_Company where intCompanyId
= 17
This query gives me an error :
An explicit value for the identity column in table 'T_Company' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Even if I turn it OFF it will not let it insert it since it will have
the same intCompanyId as the other one.
Can some one help me?
Thanks
Alain
> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
Why on earth would you want to _duplicate_ a row in a table? It makes no
sense to do that.

> I do not want to list the fields in case some are added later.
Good practice is to *always* list the columns, never use SELECT * in
production code. This should actually make the code easier to maintain and
debug if the DDL changes because your code will fail safe. INSERT ... SELECT
* is dangerous because it assumes a fixed sequential order to the columns
and that is something you shouldn't need to worry about and isn't always
easy to have complete control over.
David Portas
SQL Server MVP
|||I am really curious to know why you want to do something like that. It's like
asking us how to mess up your table...
"Alain Filiatrault" wrote:

> Hi,
> I have a table with about 50 fields including intCompanyId as the
> table identity.
> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
> I do not want to list the fields in case some are added later.
> Ex : insert into T_Company select * from T_Company where intCompanyId
> = 17
> This query gives me an error :
> An explicit value for the identity column in table 'T_Company' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Even if I turn it OFF it will not let it insert it since it will have
> the same intCompanyId as the other one.
> Can some one help me?
> Thanks
> Alain
>
|||Alain,
Like Susan, I wonder why you want to do this. But there are two
things going on here. First, because there is an identity column, you
can't insert values into every column of the table, as insert into
T_Company select * ... would do. Because you say that you can't insert
even with IDENTITY_INSERT set to ON (I assume you meant "Even if I turn
it ON", not OFF), there must be a unique or primary key constraint on
the identity column. I would hope that constraint is in place for a
reason, and that constraint is preventing you from entering duplicate
information (in the identity column, at least) into the table. Your
desire to add a duplicate row to the table is at odds with the desire of
the database designer, who designed the database to prevent anyone from
doing what you want to do.
Steve Kass
Drew University
Alain Filiatrault wrote:

>Hi,
>I have a table with about 50 fields including intCompanyId as the
>table identity.
>I want to be able to use one query (or a few in a stored proc to
>duplicate a specific row in this table.
>I do not want to list the fields in case some are added later.
>Ex : insert into T_Company select * from T_Company where intCompanyId
>= 17
>This query gives me an error :
>An explicit value for the identity column in table 'T_Company' can
>only be specified when a column list is used and IDENTITY_INSERT is
>ON.
>Even if I turn it OFF it will not let it insert it since it will have
>the same intCompanyId as the other one.
>Can some one help me?
>Thanks
>Alain
>
|||Guys, Guys, Guys,
It never ceases to amaze me how so many questions get answered with another question... instead of an answer.
I have the same question, but I think what the original author is trying to do here is duplicate the row while allowing the identity/key to grow on its own... without having to list every other column in the table. (Hence the * he is wanting to use). Furthermore, the author would like to accomplish this in a single SQL statement.
Afterall, a primary key is a primary key, and a unique identifier is a unique identifer.
It seems to me this would be a capability often persued by any seasoned and active SQL programmer. And thus, I would expect such functionality from a seasoned language (aka, sql)
With the previous assumption having been made, does anyone have the answer? Is there a way to duplicate everything in a row except for any autonumbering identifiers while allowing those autonumbers to autonumber as they were designed to (without listing every single other column in the table)?|||Yes, this is a an activity that I end up doing regularly. Typically
when I am writing a stored procedure and I want to get a working set
of data into a temporary table and have the temporary table use its
own identity key.
There is no straightforward way that I know of doing this but I use a
technique that Dan Guzman MVP gave me some time back which I think is
very neat.
--create temp table with resultset columns
SELECT *
INTO #MetaData
FROM MyBigTable
...or...
select * (where all columns are listed and have column names)
into #MetaData
from [my_bespoke_query_joining_several_tables]
--list meta data
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either your tempdb or your current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) =
OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
-- More specific case where I want to prepare a create table statement
for a
-- temp table that will hold the result set from a stored procedure
because the
-- temporary table must exist before you can use a statement like...
-- INSERT #my_tbl
-- EXECmy_proc
SELECT case ordinal_position
when 1 then ' '
else ' ,'
end
+ quotename(column_name) collate SQL_Latin1_General_CP1_CI_AS +
' '
+ case data_type
when 'int' then data_type
when 'char' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'varchar' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'nvarchar' then 'varchar(' + convert(varchar(5),
character_maximum_length) + ')'
when 'decimal' then data_type + '(' + convert(varchar(5),
numeric_precision) + ',' + convert(varchar(5), numeric_scale) + ')'
when 'smallint' then 'int'
when 'datetime' then data_type
when 'smalldatetime' then 'datetime'
else 'datatype not handled'
end
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either the tempdb or the current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) =
OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
In fact, if I am writing a stored proc that will later almost
certainly become an input to a further aggregated proc, I always put
in an additional parameter that allows the meta-data of the proc to be
returned as an additional result set.
I think this should give you a nice workaround that at worst means a
bit of cutting and pasting.
Regards
Liam
TheSqlGuy <TheSqlGuy.1dqkdq@.mail.mcse.ms> wrote in message news:<TheSqlGuy.1dqkdq@.mail.mcse.ms>...
> Guys, Guys, Guys,
> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
> I have the same question, but I think what the original author is
> trying to do here is duplicate the row while allowing the identity/key
> to grow on its own... without having to list every other column in the
> table. (Hence the * he is wanting to use). Furthermore, the author
> would like to accomplish this in a single SQL statement.
> Afterall, a primary key is a primary key, and a unique identifier is a
> unique identifer.
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer. And thus, I would expect such functionality
> from a seasoned language (aka, sql)
> With the previous assumption having been made, does anyone have the
> answer? Is there a way to duplicate everything in a row except for any
> autonumbering identifiers while allowing those autonumbers to
> autonumber as they were designed to (without listing every single other
> column in the table)?
|||> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
Answering a question directly isn't always the appropriate and professional
way to help someone who needs it. If someone asked you "Where can I get a
gun so I can shoot myself?" would you just answer the direct question or
would you offer some more constructive suggestions? The OP was asking how to
do something that will destroy the integrity of his data (if indeed it had
any to start with). My response was to ask for more information about his
actual business requirement so I could advise him better. Unfortunately
there are a lot of people posting to this group who are desperately trying
to shoot themselves in the foot. Not all of us want to help them to do it!

> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer.
Not by a *good* SQL programmer!
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...

> If someone asked you "Where can I get a
> gun so I can shoot myself?" would you just answer the direct question or
> would you offer some more constructive suggestions?
Depends on the person.
|||Also depends on what the original poster meant by "duplicate". I have
often had users ask me for funtionality to "duplicate" an order and
what they really mean is that they want the original order used as a
template to produce a new order - which is fair enough. Of course, it
would suggest that such functionality was an afterthought (which it
often is) and should have been factored into the original design.
An ability to appreciate the difference between the syntactic
precision of gun mechanics(parsed code) and the semantic ambiguity of
target shooting(meeting users requirements and expectations) is a very
useful skill.
Regards
Liam
"Mark Wilden" <mark@.mwilden.com> wrote in message news:<TfSdnZTmCJhXW_jcRVn-rw@.sti.net>...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
>
> Depends on the person.

How to duplicate a row in SQL Table using a single query

Hi,
I have a table with about 50 fields including intCompanyId as the
table identity.
I want to be able to use one query (or a few in a stored proc to
duplicate a specific row in this table.
I do not want to list the fields in case some are added later.
Ex : insert into T_Company select * from T_Company where intCompanyId
= 17
This query gives me an error :
An explicit value for the identity column in table 'T_Company' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Even if I turn it OFF it will not let it insert it since it will have
the same intCompanyId as the other one.
Can some one help me?
Thanks
Alain> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
Why on earth would you want to _duplicate_ a row in a table? It makes no
sense to do that.
> I do not want to list the fields in case some are added later.
Good practice is to *always* list the columns, never use SELECT * in
production code. This should actually make the code easier to maintain and
debug if the DDL changes because your code will fail safe. INSERT ... SELECT
* is dangerous because it assumes a fixed sequential order to the columns
and that is something you shouldn't need to worry about and isn't always
easy to have complete control over.
--
David Portas
SQL Server MVP
--|||I am really curious to know why you want to do something like that. It's like
asking us how to mess up your table...
"Alain Filiatrault" wrote:
> Hi,
> I have a table with about 50 fields including intCompanyId as the
> table identity.
> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
> I do not want to list the fields in case some are added later.
> Ex : insert into T_Company select * from T_Company where intCompanyId
> = 17
> This query gives me an error :
> An explicit value for the identity column in table 'T_Company' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Even if I turn it OFF it will not let it insert it since it will have
> the same intCompanyId as the other one.
> Can some one help me?
> Thanks
> Alain
>|||Alain,
Like Susan, I wonder why you want to do this. But there are two
things going on here. First, because there is an identity column, you
can't insert values into every column of the table, as insert into
T_Company select * ... would do. Because you say that you can't insert
even with IDENTITY_INSERT set to ON (I assume you meant "Even if I turn
it ON", not OFF), there must be a unique or primary key constraint on
the identity column. I would hope that constraint is in place for a
reason, and that constraint is preventing you from entering duplicate
information (in the identity column, at least) into the table. Your
desire to add a duplicate row to the table is at odds with the desire of
the database designer, who designed the database to prevent anyone from
doing what you want to do.
Steve Kass
Drew University
Alain Filiatrault wrote:
>Hi,
>I have a table with about 50 fields including intCompanyId as the
>table identity.
>I want to be able to use one query (or a few in a stored proc to
>duplicate a specific row in this table.
>I do not want to list the fields in case some are added later.
>Ex : insert into T_Company select * from T_Company where intCompanyId
>= 17
>This query gives me an error :
>An explicit value for the identity column in table 'T_Company' can
>only be specified when a column list is used and IDENTITY_INSERT is
>ON.
>Even if I turn it OFF it will not let it insert it since it will have
>the same intCompanyId as the other one.
>Can some one help me?
>Thanks
>Alain
>|||Guys, Guys, Guys,
It never ceases to amaze me how so many questions get answered wit
another question... instead of an answer.
I have the same question, but I think what the original author i
trying to do here is duplicate the row while allowing the identity/ke
to grow on its own... without having to list every other column in th
table. (Hence the * he is wanting to use). Furthermore, the autho
would like to accomplish this in a single SQL statement.
Afterall, a primary key is a primary key, and a unique identifier is
unique identifer.
It seems to me this would be a capability often persued by any seasone
and active SQL programmer. And thus, I would expect such functionalit
from a seasoned language (aka, sql)
With the previous assumption having been made, does anyone have th
answer? Is there a way to duplicate everything in a row except for an
autonumbering identifiers while allowing those autonumbers t
autonumber as they were designed to (without listing every single othe
column in the table)
-
TheSqlGu
----
Posted via http://www.mcse.m
----
View this thread: http://www.mcse.ms/message1119654.htm|||Yes, this is a an activity that I end up doing regularly. Typically
when I am writing a stored procedure and I want to get a working set
of data into a temporary table and have the temporary table use its
own identity key.
There is no straightforward way that I know of doing this but I use a
technique that Dan Guzman MVP gave me some time back which I think is
very neat.
--create temp table with resultset columns
SELECT *
INTO #MetaData
FROM MyBigTable
...or...
select * (where all columns are listed and have column names)
into #MetaData
from [my_bespoke_query_joining_several_tables]
--list meta data
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either your tempdb or your current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) = OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
-- More specific case where I want to prepare a create table statement
for a
-- temp table that will hold the result set from a stored procedure
because the
-- temporary table must exist before you can use a statement like...
-- INSERT #my_tbl
-- EXECmy_proc
SELECT case ordinal_position
when 1 then ' '
else ' ,'
end
+ quotename(column_name) collate SQL_Latin1_General_CP1_CI_AS +
' '
+ case data_type
when 'int' then data_type
when 'char' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'varchar' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'nvarchar' then 'varchar(' + convert(varchar(5),
character_maximum_length) + ')'
when 'decimal' then data_type + '(' + convert(varchar(5),
numeric_precision) + ',' + convert(varchar(5), numeric_scale) + ')'
when 'smallint' then 'int'
when 'datetime' then data_type
when 'smalldatetime' then 'datetime'
else 'datatype not handled'
end
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either the tempdb or the current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) = OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
In fact, if I am writing a stored proc that will later almost
certainly become an input to a further aggregated proc, I always put
in an additional parameter that allows the meta-data of the proc to be
returned as an additional result set.
I think this should give you a nice workaround that at worst means a
bit of cutting and pasting.
Regards
Liam
TheSqlGuy <TheSqlGuy.1dqkdq@.mail.mcse.ms> wrote in message news:<TheSqlGuy.1dqkdq@.mail.mcse.ms>...
> Guys, Guys, Guys,
> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
> I have the same question, but I think what the original author is
> trying to do here is duplicate the row while allowing the identity/key
> to grow on its own... without having to list every other column in the
> table. (Hence the * he is wanting to use). Furthermore, the author
> would like to accomplish this in a single SQL statement.
> Afterall, a primary key is a primary key, and a unique identifier is a
> unique identifer.
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer. And thus, I would expect such functionality
> from a seasoned language (aka, sql)
> With the previous assumption having been made, does anyone have the
> answer? Is there a way to duplicate everything in a row except for any
> autonumbering identifiers while allowing those autonumbers to
> autonumber as they were designed to (without listing every single other
> column in the table)?|||> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
Answering a question directly isn't always the appropriate and professional
way to help someone who needs it. If someone asked you "Where can I get a
gun so I can shoot myself?" would you just answer the direct question or
would you offer some more constructive suggestions? The OP was asking how to
do something that will destroy the integrity of his data (if indeed it had
any to start with). My response was to ask for more information about his
actual business requirement so I could advise him better. Unfortunately
there are a lot of people posting to this group who are desperately trying
to shoot themselves in the foot. Not all of us want to help them to do it!
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer.
Not by a *good* SQL programmer!
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
> If someone asked you "Where can I get a
> gun so I can shoot myself?" would you just answer the direct question or
> would you offer some more constructive suggestions?
Depends on the person.|||Also depends on what the original poster meant by "duplicate". I have
often had users ask me for funtionality to "duplicate" an order and
what they really mean is that they want the original order used as a
template to produce a new order - which is fair enough. Of course, it
would suggest that such functionality was an afterthought (which it
often is) and should have been factored into the original design.
An ability to appreciate the difference between the syntactic
precision of gun mechanics(parsed code) and the semantic ambiguity of
target shooting(meeting users requirements and expectations) is a very
useful skill.
Regards
Liam
"Mark Wilden" <mark@.mwilden.com> wrote in message news:<TfSdnZTmCJhXW_jcRVn-rw@.sti.net>...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
> > If someone asked you "Where can I get a
> > gun so I can shoot myself?" would you just answer the direct question or
> > would you offer some more constructive suggestions?
> Depends on the person.

How to drop a Queue Reader Agent ?

Hello,
by mistake I added an queue reader agent to a distribution database. But how can I drop the queue agent. There isn't a stored proc 'sp_dropqreader_agent'.
The only way I found (apart from dropping the whole distribution databse) is to stop and disable the generated job!

Is there any other way to get rid of the queue reader agent ?

WolfgangOnly supported way I know of is to run sp_dropdistributiondb. I haven't tested this, but as dbo or 'sa', maybe you can manually delete the job?|||Dropping the distributiondb will of course solve the problem, but it is not a good idea if there is a active replication.
I tested deleting the job yesterday and it seems that it works. But I don't know if there are side effects or not. So I prefer an offical way, for example an system procedure sp_dropqreader!

Wolfgang|||if you are not using queue updatable subscriptions, then manually dropping the job should work just fine.|||But I just found that sp_addqreaderagent generates proxy accounts and credentials as well. And these objects are not dropped automatically. I need to drop them manually! Are there more objects created by sp_addqreaderagent ? I don't know. That are more reasons for me to have a procedure to drop queue reader agents!

Wolfgang

How to do an update only if it will not be blocked by a lock ?

Hi,
I've got a stored proc called concurrently with different parameters.
In this proc, I would like to update a row of a statistic table but ONLY if
the update statement will not be blocked by a lock.
Is there any way to achieve this in SQL2000 ?Hi
> In this proc, I would like to update a row of a statistic table but ONLY i
f
> the update statement will not be blocked by a lock.
When one resource is blocked then you can't any way update except wait for
the resourse.If you dont want to wait for the resource then you can terminat
e
it through code. well you can set Lockout time and also check whether the
sproc is taking more time than that so that you can simply log the error
instead of waiting.
I f it is a deadlock then sql server returns Error: 1204 which you can catch
in @.@.error and take procedure to logical end.
If I understand you correctly, you want to avoid contention on a resourse so
that others can have access to the table.
We can sugget better answer only when we know fully what's problem is. Post
detailed problem
--
Regards
R.D
--Knowledge gets doubled when shared
"SoftLion" wrote:

> Hi,
> I've got a stored proc called concurrently with different parameters.
> In this proc, I would like to update a row of a statistic table but ONLY i
f
> the update statement will not be blocked by a lock.
> Is there any way to achieve this in SQL2000 ?
>
>|||Ok I've done this (we are inside a transaction):
SET XACT_ABORT OFF
SET LOCK_TIMEOUT 0
UPDATE MyTable WITH (ROWLOCK) SET ...
SET LOCK_TIMEOUT -1
SET XACT_ABORT ON
And it seems to work.
The only thing, I got an error in the query analyser when the update has
been aborted, but I think it can be safely ignored.

How to do a USE statement inside a stored proc?

Hi,
I have a general purpose stored proc that could be used in several
databases. I need to tell it what database to work on. My first solution was
to use the 'USE @.DB' statement at the beginning of the stored proc, where @.D
B
was a parameter passed in. Yet this does not work!
I remember having done this before, but don't recall exactly how!
Any suggestions?
--
Thanks in advance,
Juan Dent, M.Sc.EXEC('USE '+@.db+'; do something');
Please read http://www.sommarskog.se/dynamic_sql.html
"Juan Dent" <Juan_Dent@.nospam.nospam> wrote in message
news:B8CDCB05-C049-422E-AC38-60A97D7D6CCA@.microsoft.com...
> Hi,
> I have a general purpose stored proc that could be used in several
> databases. I need to tell it what database to work on. My first solution
> was
> to use the 'USE @.DB' statement at the beginning of the stored proc, where
> @.DB
> was a parameter passed in. Yet this does not work!
> I remember having done this before, but don't recall exactly how!
> Any suggestions?
> --
> Thanks in advance,
> Juan Dent, M.Sc.