No idea if this is the right place to post... Need stored proc help

Hello,
I am trying to make a stored proc that will tell me if there are any rows in a table. The table name is a variable that I get at the start of the proc it is not sent in. What I am looking for in the table is sent into the stored proc.

here is where the problem is, i can not save it. It does not seem to allow me to use the if exists with variables. I have tried it also without the + and with the + ' to create a string I cant seem to get it anyway I try.
if exists (SELECT * FROM + @.TableName + WHERE + @.ColumnName + = + @.SearchStr2)

print 'got here ' +@.TableName + ' '+ @.ColumnName

END

Thanks for any help

You need to build the entire query string in a variable, and then use the dbo.sp_executesql stored procedure to execute that query string. As you've discovered, you can't use 'if exists' with variables.

For example:

declare @.sql nvarchar(max)

set @.sql = N'

if exists (select * from ' + @.TableName + N' where ' + @.ColumnName + ' = ''' + @.SearchStr2 + ''')

print ''Got here ' + @.TableName + ', ' + @.ColumnName + ''''

exec dbo.sp_executesql @.sql

|||

All of this of course is true; however, you also need to know that this is exactly the style of code that a hacker is looking to exploit for an "SQL Injection" attack. Please do some additional research before you implement this. This kind of code CREATES a security hole.

Dave

|||

Thanks Iain I will give it a shot and let you know. Also thanks Mugambo but this is a one time thing i need to run to do the following if anyone has a better idea how to do it I would love ideas.

I need to update our SQL database for an individual user. The problem is somehow this user got two ID's with different numbers and it has been about 4 months now that it has gone unnoticed and. So I now need to change the two id's into one. So what I was planning to do was to do was search ever column of every table and it would let me know if there is an exact match to the ID I put in. If so it will display the column and table so I can decide if it is data I need to change or not. We have about 200 tables so to do it one table at a time wouldnt be impossible to do but this way I learn a little more about SQL and stored procs which is what I really want anyway.

Thanks again

|||And thanks again Iain works great.|||Hi,

Can you mark a correct answer for us please.

Thanks!

NH: Best Practices Approach - call stored proc - or run it via linked server?

what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.

i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?

thanks.

Jeff

anyone?|||

One obvious thing to keep in mind is the performance. Depending on your network the executiion time could be different.

Now I am not sure if I understand your question. If you do not have permission to create the stored proc on the original server you would still need access to the underlying tables if you want to access them through server B, irrespective of whether its via stored proc or directly T-SQL.

|||

well typically I associate using a linked server to pull data from something using openquery such as ibm as400 so my question is do i get access to put the stored proc on the remote sql server (not one of my groups sql servers), or do i ask for setting up a linked server to it and execute the stored proc from my local sql server using the linked server approach.

either way?
get access to put the stored proc right on the sql server and call it in c#?
use the linked server and call it in c#

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

thanks,

JB

|||

info@.learnbartending.com:

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

Those are exactly the reasons why you might want to put the proc directly where the data is.

Next Object id already assigned

I originally posted this problem under "stored proc won't compile" in
the programming group, but now that I've found more detail, I think it
is better suited to this group with a different title.
I can't create any objects in master. What I've found is that the
dbi_nextid is already assigned to another object. Can anyone offer
any suggestions? DBCC checkcatalog and dbcc checkdb return no errors.
create proc simpleproc as select * from sysobjects
returns
Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
There is already an object named 'simpleproc' in the database.
Further research has found the the dbi_nextid is already in use.
DBCC TRACEON(3604)
DBCC DBINFO('master')
DBCC TRACEOFF(3604)
returns
dbi_nextid = 1537440551
select name, type, id, crdate from sysobjects where id = 1537440551
returns
name type id crdate
sp_dropmergearticleP 15374405512003-03-09 18:00:50.177
select max(id) from master.dbo.sysobjects
returns
2145442717
@.@.version =
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
> There is already an object named 'simpleproc' in the database.
>
> Further research has found the the dbi_nextid is already in use.
Why did you go further? It appears you already have a procedure named
simpleproc, and this is why you can't create another one. How about change
the name to splungeproc and see if that works. Why are you creating these
procs in master anyway? How many objects are in master now?
http://www.aspfaq.com/
(Reverse address to reply.)
|||I went further because I need to get the proc created. It is an SMTP
mail proc that I ultimately need to create. I used the simpleproc
example so people would not have to wade through lines of code. I am
sure simpleproc does not exist
My problem is I can't create any object in master - regardless of the
name or type. Tables, views, stored procs all receive the same error -
object already exists. If I try and drop the object first, I get that
it does not exist.
select * from sysobjects where name = 'simpleproc'
go
drop proc dbo.simpleproc
go
create proc dbo.simpleproc as select * from sysobjects
returns
(0 row(s) affected)
Server: Msg 3701, Level 11, State 5, Line 3
Cannot drop the procedure 'dbo.simpleproc', because it does not exist in
the system catalog.
Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
There is already an object named 'simpleproc' in the database.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||What about my other questions? Why does this have to be in master? How
many objects are in master now?
http://www.aspfaq.com/
(Reverse address to reply.)
"Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
news:uA3PgWCaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> I went further because I need to get the proc created. It is an SMTP
> mail proc that I ultimately need to create. I used the simpleproc
> example so people would not have to wade through lines of code. I am
> sure simpleproc does not exist
|||A count of sysobjects returns 1305.
I am putting the proc in master because on all my other servers it is in
master. This is an enterprise wide proc developed for standard use in
sending emails from SQL. I don't want it to be in master on all the
other servers and a different database on this one. In hindsight, we
might have made this an extended stored proc, but there is too much code
to go back and change it now.
But all that is really beside the point. I should be able to create
objects in master - whether it is advisable or not - and I can't because
the "next object id" is already being used.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||This sounds like a known issue - please call Product Support who will assist
you.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
news:u2A3XnCaEHA.3996@.TK2MSFTNGP12.phx.gbl...
> A count of sysobjects returns 1305.
> I am putting the proc in master because on all my other servers it is in
> master. This is an enterprise wide proc developed for standard use in
> sending emails from SQL. I don't want it to be in master on all the
> other servers and a different database on this one. In hindsight, we
> might have made this an extended stored proc, but there is too much code
> to go back and change it now.
> But all that is really beside the point. I should be able to create
> objects in master - whether it is advisable or not - and I can't because
> the "next object id" is already being used.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thank you Paul!!
Since you mentioned a known problem, I found KB Article
http://support.microsoft.com/default...48&Product=sql
And it did the trick. I could not get the supplied script to work, so
I simple coded a drop and create and duplicated the lines 50 or so
times. Then ran that until the object created successfully.
Thanks again
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message news:<e0onLTHaEHA.3888@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> This sounds like a known issue - please call Product Support who will assist
> you.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
> news:u2A3XnCaEHA.3996@.TK2MSFTNGP12.phx.gbl...

Next Object id already assigned

I originally posted this problem under "stored proc won't compile" in
the programming group, but now that I've found more detail, I think it
is better suited to this group with a different title.
I can't create any objects in master. What I've found is that the
dbi_nextid is already assigned to another object. Can anyone offer
any suggestions? DBCC checkcatalog and dbcc checkdb return no errors.
create proc simpleproc as select * from sysobjects
returns
Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
There is already an object named 'simpleproc' in the database.
Further research has found the the dbi_nextid is already in use.
DBCC TRACEON(3604)
DBCC DBINFO('master')
DBCC TRACEOFF(3604)
returns
dbi_nextid = 1537440551
select name, type, id, crdate from sysobjects where id = 1537440551
returns
name type id crdate
sp_dropmergearticle P 1537440551 2003-03-09 18:00:50.177
select max(id) from master.dbo.sysobjects
returns
2145442717
@.@.version =
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)> Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
> There is already an object named 'simpleproc' in the database.
>
> Further research has found the the dbi_nextid is already in use.
Why did you go further? It appears you already have a procedure named
simpleproc, and this is why you can't create another one. How about change
the name to splungeproc and see if that works. Why are you creating these
procs in master anyway? How many objects are in master now?
--
http://www.aspfaq.com/
(Reverse address to reply.)

Next Object id already assigned

I originally posted this problem under "stored proc won't compile" in
the programming group, but now that I've found more detail, I think it
is better suited to this group with a different title.
I can't create any objects in master. What I've found is that the
dbi_nextid is already assigned to another object. Can anyone offer
any suggestions? DBCC checkcatalog and dbcc checkdb return no errors.
create proc simpleproc as select * from sysobjects
returns
Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
There is already an object named 'simpleproc' in the database.
Further research has found the the dbi_nextid is already in use.
DBCC TRACEON(3604)
DBCC DBINFO('master')
DBCC TRACEOFF(3604)
returns
dbi_nextid = 1537440551
select name, type, id, crdate from sysobjects where id = 1537440551
returns
name type id crdate
sp_dropmergearticle P 1537440551 2003-03-09 18:00:50.177
select max(id) from master.dbo.sysobjects
returns
2145442717
@.@.version =
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)> Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
> There is already an object named 'simpleproc' in the database.
>
> Further research has found the the dbi_nextid is already in use.
Why did you go further? It appears you already have a procedure named
simpleproc, and this is why you can't create another one. How about change
the name to splungeproc and see if that works. Why are you creating these
procs in master anyway? How many objects are in master now?
http://www.aspfaq.com/
(Reverse address to reply.)|||I went further because I need to get the proc created. It is an SMTP
mail proc that I ultimately need to create. I used the simpleproc
example so people would not have to wade through lines of code. I am
sure simpleproc does not exist
My problem is I can't create any object in master - regardless of the
name or type. Tables, views, stored procs all receive the same error -
object already exists. If I try and drop the object first, I get that
it does not exist.
select * from sysobjects where name = 'simpleproc'
go
drop proc dbo.simpleproc
go
create proc dbo.simpleproc as select * from sysobjects
returns
(0 row(s) affected)
Server: Msg 3701, Level 11, State 5, Line 3
Cannot drop the procedure 'dbo.simpleproc', because it does not exist in
the system catalog.
Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
There is already an object named 'simpleproc' in the database.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||What about my other questions? Why does this have to be in master? How
many objects are in master now?
http://www.aspfaq.com/
(Reverse address to reply.)
"Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
news:uA3PgWCaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> I went further because I need to get the proc created. It is an SMTP
> mail proc that I ultimately need to create. I used the simpleproc
> example so people would not have to wade through lines of code. I am
> sure simpleproc does not exist|||A count of sysobjects returns 1305.
I am putting the proc in master because on all my other servers it is in
master. This is an enterprise wide proc developed for standard use in
sending emails from SQL. I don't want it to be in master on all the
other servers and a different database on this one. In hindsight, we
might have made this an extended stored proc, but there is too much code
to go back and change it now.
But all that is really beside the point. I should be able to create
objects in master - whether it is advisable or not - and I can't because
the "next object id" is already being used.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||This sounds like a known issue - please call Product Support who will assist
you.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
news:u2A3XnCaEHA.3996@.TK2MSFTNGP12.phx.gbl...
> A count of sysobjects returns 1305.
> I am putting the proc in master because on all my other servers it is in
> master. This is an enterprise wide proc developed for standard use in
> sending emails from SQL. I don't want it to be in master on all the
> other servers and a different database on this one. In hindsight, we
> might have made this an extended stored proc, but there is too much code
> to go back and change it now.
> But all that is really beside the point. I should be able to create
> objects in master - whether it is advisable or not - and I can't because
> the "next object id" is already being used.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thank you Paul!!
Since you mentioned a known problem, I found KB Article
http://support.microsoft.com/defaul...448&Product=sql
And it did the trick. I could not get the supplied script to work, so
I simple coded a drop and create and duplicated the lines 50 or so
times. Then ran that until the object created successfully.
Thanks again
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message news:<e0onLTHaEHA.3
888@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> This sounds like a known issue - please call Product Support who will assi
st
> you.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
> news:u2A3XnCaEHA.3996@.TK2MSFTNGP12.phx.gbl...

newbie: logging for stored procs

ok when debugging a stored proc, yes i can see print output etc in sql
query analyzer. but when the proc is called in production, i dont see that
output; moreover, im new and wondering what are the typical ways that you
all log stuff from a stored proc, etc.
is something like this common?
http://www.databasejournal.com/feat...cle.php/1467591jimbo-black wrote:
> ok when debugging a stored proc, yes i can see print output etc in sql
> query analyzer. but when the proc is called in production, i dont
> see that output; moreover, im new and wondering what are the typical
> ways that you all log stuff from a stored proc, etc.
> is something like this common?
> http://www.databasejournal.com/feat...cle.php/1467591
Presumably, you have PRINT statements in procedures while they are in
development for debugging purposes and those PRINT statements are
removed for production. If what you need is a parameter-driven method to
execute a procedure with logging, then you can use conditional
statements in the procedure to log to a table as the article explains
(or even issue PRINT statements for interactive testing). In general,
though, it's better to do this type of work in development and keep your
procedure code to a minimum. For batch routines that might need to log
their lengthly processes and progress, a logging table would be the way
to go.
David Gugick
Quest Software
www.imceda.com
www.quest.com

newbie: logging for stored procs

ok when debugging a stored proc, yes i can see print output etc in sql
query analyzer. but when the proc is called in production, i dont see that
output; moreover, im new and wondering what are the typical ways that you
all log stuff from a stored proc, etc.
is something like this common?
http://www.databasejournal.com/features/mssql/article.php/1467591jimbo-black wrote:
> ok when debugging a stored proc, yes i can see print output etc in sql
> query analyzer. but when the proc is called in production, i dont
> see that output; moreover, im new and wondering what are the typical
> ways that you all log stuff from a stored proc, etc.
> is something like this common?
> http://www.databasejournal.com/features/mssql/article.php/1467591
Presumably, you have PRINT statements in procedures while they are in
development for debugging purposes and those PRINT statements are
removed for production. If what you need is a parameter-driven method to
execute a procedure with logging, then you can use conditional
statements in the procedure to log to a table as the article explains
(or even issue PRINT statements for interactive testing). In general,
though, it's better to do this type of work in development and keep your
procedure code to a minimum. For batch routines that might need to log
their lengthly processes and progress, a logging table would be the way
to go.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

newbie: logging for stored procs

ok when debugging a stored proc, yes i can see print output etc in sql
query analyzer. but when the proc is called in production, i dont see that
output; moreover, im new and wondering what are the typical ways that you
all log stuff from a stored proc, etc.
is something like this common?
http://www.databasejournal.com/featu...le.php/1467591
jimbo-black wrote:
> ok when debugging a stored proc, yes i can see print output etc in sql
> query analyzer. but when the proc is called in production, i dont
> see that output; moreover, im new and wondering what are the typical
> ways that you all log stuff from a stored proc, etc.
> is something like this common?
> http://www.databasejournal.com/featu...le.php/1467591
Presumably, you have PRINT statements in procedures while they are in
development for debugging purposes and those PRINT statements are
removed for production. If what you need is a parameter-driven method to
execute a procedure with logging, then you can use conditional
statements in the procedure to log to a table as the article explains
(or even issue PRINT statements for interactive testing). In general,
though, it's better to do this type of work in development and keep your
procedure code to a minimum. For batch routines that might need to log
their lengthly processes and progress, a logging table would be the way
to go.
David Gugick
Quest Software
www.imceda.com
www.quest.com