Altering stored procedure which is part of sys schema

I am trying to alter sys.sp_helpmergeconflictrows which is part fof sys schema and is in System Stored Procedures.

Reason why I need this is because Conglict Viewer in merge replication fails to show data from one of my tables, because aforementioned sp fails during execution. It fails because sql query is declared as nvarchar(4000) and it needs to be longer. So, I tired to change it to nvarchar(max), but I cannot.

I tried few things in order to gain permission to alter that sp, but I fial always.

Can it be done at all, and if can, how?

Thanks

If you have a bug in the conflict viewer (or more specifically this stored proc), you should open a ticket with MS Tech Support to have this resolved.

Bryan

sql

Altering multiple objects schema

Hi,

I need to change the schema of the stored procedures of several databases.

Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?

thank you

Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

-Raul Garcia

SDE/T

SQL Server Engine

|||

You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

declare @.querystring nvarchar(MAX)

set @.querystring=''

select @.querystring=@.querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

exec(@.querystring)

Either way, you will have to use dynamic sql.

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
>
>sql

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> > Is there a system table that has the date when the strored procedure was
> > altered?
> > thanks
>
>

ALTER TABLE DEFAULT

/* for the google index */
ALTER TABLE
DEFAULT COLUMN
DEFAULT VALUE

I've worked out several stored procedures for altering the default column
values in a table. They were compiled from books and code snippets found
here. It was a pain to work out so I've decided to share my work and
research here. This post is just my way of saying thanks to several others
here for posting with their wisdom and intelligence.

Michael
simpsonAT(dot)cts(dot)com

This procedure gets the constraint name. If you use the design view to
setup a default value, you won't know the system assigned constraint name.
This proc makes it an non issue. This code was gleened from this news
group.
CREATE PROCEDURE [DBO].[GetConstraintName]
(
@.tablename sysname,
@.columnName sysname,
@.constraintName sysname OUTPUT
)
as
SELECT
@.constraintName = o1.name
FROM
sysobjects o1
INNER JOIN
syscolumns c ON o1.id = c.cdefault
INNER JOIN
sysobjects o2 ON o1.parent_obj = o2.id
WHERE (o2.name = @.tablename) AND (c.name = @.columnName)

This procedure changes the default value for a column that is a numeric. It
uses the previously define stored procedure to get the constraint name. A
text version of this procedure can be created by removing the cast, defining
the input parameter "newConstraint" as varchar(255).

CREATE PROCEDURE [dbo].[ChangeIntConstraint]
(
@.tableName sysname,
@.columnName sysname,
@.newConstraint int
)
AS

Declare @.conName sysname

exec GetConstraintName @.tableName, @.columnName, @.constraintName = @.conName
OUT

declare @.sql nvarchar(1024)

set @.sql = 'ALTER TABLE ' + @.tableName + ' drop constraint ' + @.conName
exec(@.sql)

set @.sql = 'ALTER TABLE ' + @.tableName + ' ADD CONSTRAINT ' + @.conName + '
DEFAULT (' + CAST(@.newConstraint AS varchar(255)) + ') FOR ' + @.columnName
exec(@.sql)Why not make @.newConstraint character.
Then you can get the column type in the SP and cater for numeric and
character columns and dates.|||I am by no means an expert. If you want to post that change here, please do.
I think I know what you mean, but it would take even more time for me to
hack it out.

I did a Google news search on the subject. I saw that it had been asked
numerous times in the past. Some people responded with dropping and
recreating the table. I was just sharing what took me several hours to hash
out.

Michael

"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:7d6610e9.0311021918.19e6c409@.posting.google.c om...
> Why not make @.newConstraint character.
> Then you can get the column type in the SP and cater for numeric and
> character columns and dates.|||Haven't tested it but this sould be quite close. Change the constraint
value parameter to character

The relies on dates being passed in in a good format - e.g. 'yyyymmdd
hh:mm:ss'

declare @.datatype varchar(10)
select @.datatype = case when DATA_TYPE like '%char%' then 'char'
when DATA_TYPE like '%date%' then 'date'
else 'numeric'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.tableName
andCOLUMN_NAME = @.columnName

select @.sql = 'ALTER TABLE ' + @.tableName
select@.sql = @.sql +' ADD CONSTRAINT ' + @.conName
if @.datatype = numeric
select@.sql = @.sql + ' DEFAULT (' + @.newConstraint + ')'
else
select@.sql = @.sql + ' DEFAULT (''' + @.newConstraint + ''')'
select@.sql = @.sql + ' FOR ' + @.columnName

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, Julia
The net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
--
David Gugick
Imceda Software
www.imceda.com

allowing access to database only through stored procedures

I have read that it is possible to configure sql server express so that the database can only beaccessed through stored procedures. Can anyone tell me how to do this.

Many thanks.

martin

Really? Where is the original article which mentions this? I guess it actually talks about security management in SQL. Yes you can limit permissions of logins (or users after mapping the logins to databases). Image such a scenario: you want a login to do some specific updates to a table, but you do not want to grant UPDATE permission on the table to the login (if you do so, the login can do any UPDATE as he like on the table). In this case you can create a stored procedure to do the specific update, and then you give the EXECUTE permission to the login. This is one of the advantages of SPs.

Stored procedures offer numerous advantages. They can:

Share application logic with other applications, thereby ensuring consistent data access and modification.

Stored procedures can encapsulate business functionality. Business rules or policies encapsulated in stored procedures can be changed in a single location. All clients can use the same stored procedures to ensure consistent data access and modification.

|||

thank you for your reply

martin

Allowing a user to execute SProcs

Hi,
I have a database that I'm using which contains many stored procedures. The
only way I have been able to allow a particular user to execute the
procedure is to select that user, then open that users permissions, find the
table and go through the list of procedures one by one allowing the user the
execute permission.
Can someone let me know how I can just allow a user permission to execute
all stored procedures that exist, as well as any new ones that may be
created. Is their like a "Can Execute" permission and if so where can i find
it.
Thanks everyone
SimonHere's one idea:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uYiTk9k8DHA.3012@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a database that I'm using which contains many stored procedures. The
only way I have been able to allow a particular user to execute the
procedure is to select that user, then open that users permissions, find the
table and go through the list of procedures one by one allowing the user the
execute permission.
Can someone let me know how I can just allow a user permission to execute
all stored procedures that exist, as well as any new ones that may be
created. Is their like a "Can Execute" permission and if so where can i find
it.
Thanks everyone
Simon

All-of-a-sudden, I get "Invalid object name"

I have been developing a database in SQL Server Management Studio Express for the past few weeks. I have been writing stored procedures and functions, creating and deleting tables, etc., etc. and everything has been working just fine.
Today, however, if I try to run any of the functions I have written I get "Invalid object name 'functionName'."
This occurs even if I create a new function, execute the CREATE statement. Then if I refresh the Functions folder I can see my newly created function, but I cannot run it.
This is terribly frustrating.

Does anyone have any ideas as to what might have changed? I am logging in as the same user, on the same machine that I have always logged in on. I created the database that is giving the error.

When I right-click SQLEXPRESS in the object browser, and go to Permissions in the Server Properties dialog, no permissions are selected as "granted" for my login name under any of the Logins/Roles. (ex. BUILTIN\Administrators: my login name is listed in the "Explicit permissions for BUILTIN\Administrators" list, but no permissions are granted. If I go down the list selecting [checking] the various permissions, then close the dialog. When I open the dialog again, rows have been added to the list that have the Permission name, then "sa" listed as the Grantor, with that row checked. But, there are still no checks next to my login name.)

The only thing that has changed on my machine, that I am aware of, is that a Windows Authentication update ran on my machine earlier today. I am using Windows Authentication in my SQL Server instance. I assume it has something to do with that.

Please help. This is urgent. We have a presentation on this project in 3 days.
ugh.

Thanks in advance for any help you can provide.

hi,

I'd check that your database users' (database principal) default schema is still the one you are used to have too...

and obviously I'd verify all your code includes full object references in the form "schema_name.object_name" and not only

CREATE PROCEDURE usp_ProcName

....

EXEC usp_ProceureName

but

CREATE PROCEDURE the_schema.usp_ProcedureName

....

EXEC the_schema.usp_ProcedureName

and the like..

regards

All stored procedures in the master database disappear? Help!

This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a lot
system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
I would greatly appreciate any help.
Bing
I doubt they are really gone since. SEM uses many of those procedures to do
it's work and I doubt SEM would even work if all the procs were gone. Of
course, I've never tried... <g>
Hopefully it's something this simple. I don't suppose you have closed and
reopened SEM?
BTW... why are you modifying procedures in master in the first place. You
should not be touching system procedures and for the most part you shouldn't
be placing 'user' procedures there.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"bing" <bing@.discussions.microsoft.com> wrote in message
news:212BA431-BCAE-425A-BF02-277B235DE3DA@.microsoft.com...
> This morning when I went to modify a stored procedure in the master
database, I noticed all the stored procedures were gone! When I clicked
'Stored Procedures', nothing returned on the right window in SQL Server
Enterprise Manager. There should be a lot system and user defined stored
procedures there. Extended Stored Procedures show up fine though. Is this
a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||From query analyzer run the following query
select * from master..sysobjects where xtype = 'S'
It will list your system sp's.
You can then be sure if they are deleted or not.
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||Sorry S for system tables P for SP's you're problem with sp's then type
xtype = 'P'
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||It's getting even weirder. I closed and reopened SEM one more time, now I can see all the stored procedures in the master database, but all the views become invisible this time. DBCC checkdb on master shows no error. Would restarting
SQL server and SQL agent help clean up some weirdness?
Bing
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing

All stored procedures in the master database disappear? Help!

This morning when I went to modify a stored procedure in the master database
, I noticed all the stored procedures were gone! When I clicked 'Stored Pro
cedures', nothing returned on the right window in SQL Server Enterprise Mana
ger. There should be a lot
system and user defined stored procedures there. Extended Stored Procedure
s show up fine though. Is this a disaster? What might have happened?
I would greatly appreciate any help.
BingI doubt they are really gone since. SEM uses many of those procedures to do
it's work and I doubt SEM would even work if all the procs were gone. Of
course, I've never tried... <g>
Hopefully it's something this simple. I don't suppose you have closed and
reopened SEM?
BTW... why are you modifying procedures in master in the first place. You
should not be touching system procedures and for the most part you shouldn't
be placing 'user' procedures there.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"bing" <bing@.discussions.microsoft.com> wrote in message
news:212BA431-BCAE-425A-BF02-277B235DE3DA@.microsoft.com...
> This morning when I went to modify a stored procedure in the master
database, I noticed all the stored procedures were gone! When I clicked
'Stored Procedures', nothing returned on the right window in SQL Server
Enterprise Manager. There should be a lot system and user defined stored
procedures there. Extended Stored Procedures show up fine though. Is this
a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||From query analyzer run the following query
select * from master..sysobjects where xtype = 'S'
It will list your system sp's.
You can then be sure if they are deleted or not.
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||Sorry S for system tables P for SP's you're problem with sp's then type
xtype = 'P'
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing|||It's getting even weirder. I closed and reopened SEM one more time, now I c
an see all the stored procedures in the master database, but all the views b
ecome invisible this time. DBCC checkdb on master shows no error. Would r
estarting
SQL server and SQL agent help clean up some weirdness?
Bing
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I no
ticed all the stored procedures were gone! When I clicked 'Stored Procedures', noth
ing returned on the right window in SQL Server Enterprise Manager. There should be
a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fin
e though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing

all stored procedures are user stored procedures

Looking (in Enterprise Manager) at the stored procedure list of a database
yesterday, I noticed that all the procedures were listed as "user"
procedures, including dt_addtosourcecontrol and the like, which are usually
system procedures, and on every other database on the server are system
procedures.
Looking at sysobjects:
select name, objectproperty(id,'IsMSShipped') as is_system from sysobjects
where type = 'P'
I get nothing but zeros for is_system, consistent with what EM shows (and
different from what I get in other databases.
I'm pretty (but not absolutely) sure that a w or two ago, this database
listed dt_addtosourcecontrol and the like as system stored procedures.
Supporting this, the creation dates of the dt_... procedures are:
1. all the same, and
2. different from the creation date of the database.
So, I'm guessing that something I (or someone) did, changed them into user
stored procedures. But unless someone deliberately fooled with the system
tables just to confuse be, I'm unsure as to what it could be. Does anyone
have any ideas?Hi
You may want to compare the other entries in sysobjects between two
different databases.
John
"Thomas Berg" wrote:

> Looking (in Enterprise Manager) at the stored procedure list of a database
> yesterday, I noticed that all the procedures were listed as "user"
> procedures, including dt_addtosourcecontrol and the like, which are usuall
y
> system procedures, and on every other database on the server are system
> procedures.
> Looking at sysobjects:
> select name, objectproperty(id,'IsMSShipped') as is_system from sysobjec
ts
> where type = 'P'
> I get nothing but zeros for is_system, consistent with what EM shows (and
> different from what I get in other databases.
> I'm pretty (but not absolutely) sure that a w or two ago, this database
> listed dt_addtosourcecontrol and the like as system stored procedures.
> Supporting this, the creation dates of the dt_... procedures are:
> 1. all the same, and
> 2. different from the creation date of the database.
> So, I'm guessing that something I (or someone) did, changed them into user
> stored procedures. But unless someone deliberately fooled with the system
> tables just to confuse be, I'm unsure as to what it could be. Does anyone
> have any ideas?
>

All SP's slow than corresponding query

I am working on a database the eventually hold a large amount of data.
For some reason, now, all my stored procedures that used to run quickly, now
a lot slower than the exact same as a query int he analyzer. The plans show
exactly the same statistics, but SP takes about 10 times longer.
Any ideas out there?
TIA1. Check the query plans to see if they are the same..
2. One common potential problem is using local variables in a where clause
in a SP... At the time the sp is optimized, the optimizer CAN see all of the
sp parameter values, but local variables do not get a value until run
time... Therefore the optimizer can NOT use index statistics on these
queries to determine the most useful index, which can lead to performance
problems in sps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Is it possible that they are recompiling for some reason? Check with
Profiler, you have SP:Recompile and some other events.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Yes, the plan is the same, an no local variables used. When I say All, I
mean all stored procedures are slower. Something is wrong. Don't know if
some setting got set somehow or what.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> 1. Check the query plans to see if they are the same..
> 2. One common potential problem is using local variables in a where clause
> in a SP... At the time the sp is optimized, the optimizer CAN see all of
the
> sp parameter values, but local variables do not get a value until run
> time... Therefore the optimizer can NOT use index statistics on these
> queries to determine the most useful index, which can lead to performance
> problems in sps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > I am working on a database the eventually hold a large amount of data.
> > For some reason, now, all my stored procedures that used to run quickly,
> now
> > a lot slower than the exact same as a query int he analyzer. The plans
> show
> > exactly the same statistics, but SP takes about 10 times longer.
> >
> > Any ideas out there?
> >
> > TIA
> >
> >
>|||does the execution plan show a hash join or a hash match
operation?
if so, are the more than 10,000 rows involved in that op?
read BOL for join types, at some point the hash join
switches from in memory to grace or recursive.
the setting is different for RPC and SQL Batch.
From an ADO client program, if you append parameters, the
proc will show up in Profiler as RPC, anything you do in
QA shows up as SQL Batch.
if this is the case, there are a few things you can do to
avoid this
>--Original Message--
>I am working on a database the eventually hold a large
amount of data.
>For some reason, now, all my stored procedures that used
to run quickly, now
>a lot slower than the exact same as a query int he
analyzer. The plans show
>exactly the same statistics, but SP takes about 10 times
longer.
>Any ideas out there?
>TIA
>
>.
>|||It was a has issue. Sorry to inconvenience you guys.
I should have seen it earlier.
"Yovan Fernandez" <yfernandez@.sai-inc.com> wrote in message
news:mwm9b.2723$ev2.1916726@.newssrv26.news.prodigy.com...
> I have experienced this problem before on another DB i had, it has to do
> with Recompiling like Dejan said, one way to stop recompiling is to turn
> auto statistics off I really think having DB option AUTO_UPDATE_STATISTIC
on
> And when you said all are you including system sp or just user sps?
> Yovan Fernandez
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:%23zA79w6eDHA.3216@.tk2msftngp13.phx.gbl...
> > Yes, the plan is the same, an no local variables used. When I say All, I
> > mean all stored procedures are slower. Something is wrong. Don't know if
> > some setting got set somehow or what.
> >
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> > > 1. Check the query plans to see if they are the same..
> > > 2. One common potential problem is using local variables in a where
> clause
> > > in a SP... At the time the sp is optimized, the optimizer CAN see all
of
> > the
> > > sp parameter values, but local variables do not get a value until run
> > > time... Therefore the optimizer can NOT use index statistics on these
> > > queries to determine the most useful index, which can lead to
> performance
> > > problems in sps.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > >
> > > "DWinter" <dwinter@.attbi.com> wrote in message
> > > news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > > > I am working on a database the eventually hold a large amount of
data.
> > > > For some reason, now, all my stored procedures that used to run
> quickly,
> > > now
> > > > a lot slower than the exact same as a query int he analyzer. The
plans
> > > show
> > > > exactly the same statistics, but SP takes about 10 times longer.
> > > >
> > > > Any ideas out there?
> > > >
> > > > TIA
> > > >
> > > >
> > >
> > >
> >
> >
>|||> It was a has issue. Sorry to inconvenience you guys.
> I should have seen it earlier.
No inconvenince, we are glad you worked it out, that's why newsgroups are
for :-)
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

All procedure in a stored procedure not being exec

I have a stored procedure that is called but it seems that not all the
Stored procedures are run. If I execute it the second time then they seem to
run after that.
Am I missing something, Like added a wait to finish clause?
Stephen K. Miyasato
MDsync
CREATE PROCEDURE FLG_UpdateFlags
/*Stored Procedure to update All the Flags*/
@.PatNo int
AS
--Put optional Flag procedures here
--
--Procedure to delete FOBT if Colonoscopy duedate is > todays or to be done
Exec FLG_FOBT @.PatNo
--procedure to delete old flags Set LastDate to Visit date in Vistal Signs
Exec FLG_OfficeVisit @.PatNo
-- Update Labs based on sort and ProtScr
Exec FLG_LabDateCholesterol @.PatNoStephen K. Miyasato (miyasat@.flex.com) writes:
> I have a stored procedure that is called but it seems that not all the
> Stored procedures are run. If I execute it the second time then they
> seem to run after that.
How do you conclude this? From where do you run the procedure? Have you
traced the procedure in Profiler to see what is going on?
The only thing I can guess on is that first time you run the procedure,
there is an error that causes the batch to be aborted, but this does
for some reason not happen when you re-run the procedure.
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

All of a sudden i Cant Create Stored Procedures?

When creating even the simplest of Stored Procedures (that i know work!), i get prompted with an error Titled : <Microsoft SQL-DMO (ODBC SQLSTATE: 42000)> with an error message: <Error 170: Line 1: Incorrect syntax near "My Stored Proc Name". Must declare the variable '@.str_PartNo'>

I know for a fact that the stored procedure does not have a syntax error because i can run a stored proc that is already saved, but if i copy and paste it into a new store proc, i get this error! Also, when i click the check syntax there are no errors.

I was told to verify if the service <SQL Server Agent> is running. It is.

I could really use some helpThe agent won't affect this
Suspect either you aren't copying the correct data or there is an invalid character somewhere.

First thing to do is look for the definition of @.str_PartNo and see why it is not defined.
The message is saying it has a problem at line 1 which is a bit odd - do you have an exec of dynamic sql after a go somewhere?

try
create proc myproc
as
select 1
go

The copy and paste may be introducing errors due to invalid characters.|||it works, it seems the problem was that i was encompassing my Stored Proc name with double quotes, i've saved others with the double quotes...strange.

Thanks for the help|||It's because of the settings in the environment you are creating the sp from.
look at

set QUOTED_IDENTIFIER off
go
create procedure "mysp"
as
select 1
go

set QUOTED_IDENTIFIER on
go
create procedure "mysp"
as
select 1
go

drop procedure mysp

All I want to do is feed a varchar into a stored procedure and get an id back... what am I

So I have been building stored procedures and things were working fine until I hit something that I am sure is incredibly simple to do; however, i have having a hell of a time with it. Here is the code:

#############################################

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR

AS
BEGIN
DECLARE @.postedbyid UNIQUEIDENTIFIER

SET @.postedbyid = (SELECT UserId
FROM aspnet_Users
WHERE (UserName = @.username))
END

###Which returns this###

Running [dbo].[GetUserIdForUser] ( @.username = jason ).

No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

#############################################

This is part of a much larger stored procedure, but this is the point of failure in the stored procedure I am trying to build. If anyone can tell me what I am doing wrong I would appreciate it. I have tried a few things that have resulted in different failures. If I remove any references to variables (delete SET @.postedbyid = and replace @.username with 'jason') I can get it to return a result. If I put @.username in though it doesn't work. Here are the examples of those:

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR

AS
BEGIN

SELECT UserId
FROM aspnet_Users
WHERE (UserName = @.username)
END

###Which returns this###

Running [dbo].[GetUserIdForUser] ( @.username = jason ).

UserId
------------
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

Here is a sanity check to show that the data is in fact in the database:

ALTER PROCEDURE dbo.GetUserIdForUser
AS
BEGIN
SELECT UserId
FROM aspnet_Users
WHERE (UserName = 'jason')
END

Running [dbo].[GetUserIdForUser].

UserId
------------
No rows affected.
(1 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

Anyone have any ideas on what I am doing wrong?

Try setting the size for the parameter in your proc.|||

Have you tried an output parameter?

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR,
@.postedbyid uniqueidentifier OUTPUT

AS
BEGIN

SELECT @.postedbyid = UserId
FROM aspnet_Users
WHERE (UserName = @.username)
END

|||

Yea, I thought about that but when I do that I get this:

Running [dbo].[GetUserIdForUser] ( @.username = jason, @.postedbyid = <DEFAULT> ).

Procedure or Function 'GetUserIdForUser' expects parameter '@.postedbyid', which was not supplied.
No rows affected.
(0 row(s) returned)
@.postedbyid =
@.RETURN_VALUE =
Finished running [dbo].[GetUserIdForUser].

For some reason OUTPUT is both and in and an output and it wants an initial variable. Regardless of what I assign it or even if I leave it null it results in the same issue.

|||

I did, same result:

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR(255)

AS
BEGIN
DECLARE @.postedbyid UNIQUEIDENTIFIER

SELECT @.postedbyid = UserId
FROM aspnet_Users
WHERE (UserName = @.username)
END

### RESULT ###

Running [dbo].[GetUserIdForUser] ( @.username = jason ).

No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].

Any other ideas?

|||

This one works:

ALTER

PROCEDURE [dbo].GetUserIdForUser]

@.UserName

NVARCHAR(255)

AS

BEGIN

SELECT UserIdFROM dbo.aspnet_Users-- WHERE LoweredUserName = LOWER(@.UserName)WHERE UserName= @.UserName

RETURN 0

END

|||What are you expecting back from the stored proc? You would either need to use a SELECT statement or OUTPUT parameters to return values. Currently there's none.|||

This one works:

ALTER

PROCEDURE [dbo].GetUserIdForUser]

@.UserName

NVARCHAR(255)

AS

BEGIN

SELECT UserIdFROM dbo.aspnet_Users-- WHERE LoweredUserName = LOWER(@.UserName)WHERE UserName= @.UserName

RETURN 0

END

Right, but using that, how do I pass that UserId to something else?

|||What is the datatype of userid in your aspnet_users table?|||

ALTER PROCEDURE dbo.GetUserIdForUser
@.username NVARCHAR(255),
@.postedbyid INT OUTPUT
AS
BEGIN

SELECT @.postedbyid = UserId
FROM aspnet_Users
WHERE (UserName = @.username)

END

When you need tro execute it:

Declare @.Pid int
EXEC dbo.GetUserIdForUser 'testusername', @.pid OUTPUt
SELECT @.pid as PostedByID

|||

You can use the userid in your sp like:

ALTER

PROCEDURE [dbo].[foo1]

@.username

NVARCHAR(255),

@.CurrentTimeUtc

datetime=NULL

AS

BEGIN

DECLARE

@.UserIduniqueidentifier

SET

@.CurrentTimeUtc=getdate()

BEGINSELECT @.UserId=UserIdFROM aspnet_UsersWHERE @.UserName= UserName

UPDATE dbo.aspnet_Users

SET LastActivityDate= @.CurrentTimeUtc

WHEREUserid=@.UserId

END

|||

What is the datatype of userid in your aspnet_users table?

---

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************

The datatype is UNIQUEIDENTIFIER. I am using the asp.net user authentication and that is the format it is in. I could probably just add another column called id as an INT, but at this point I want to know why it isn't working. It is acting like it needs to be converted but even when I try that it doesn't work. There is something strange about uniqueidentifiers but it doesn't seem to be documented anywhere.

|||

limno:

You can use the userid in your sp like:

ALTERPROCEDURE [dbo].[foo1]

@.usernameNVARCHAR(255),

@.CurrentTimeUtcdatetime=NULL

AS

BEGIN

DECLARE @.UserIduniqueidentifier

SET @.CurrentTimeUtc=getdate()

BEGIN

SELECT @.UserId=UserIdFROM aspnet_UsersWHERE @.UserName= UserName

UPDATE dbo.aspnet_Users

SET LastActivityDate= @.CurrentTimeUtc

WHEREUserid=@.UserId

END

I did this and it updated the date; however, it did not select the userid.

|||

EUREKEA!! I got it. So the problem is that UNIQUEIDENTIFIER has to be converted. Once I started searching for that I found all kinds of instances of people running into it. I just don't have the experience doing this yet to be any good at troubleshooting it. Thanks for all your help everyone and thanks for putting up with my naivete. Here is how I got it to work:

ALTER PROCEDURE dbo.GetUserIdForUser @.usernameNVARCHAR(255), @.useridVARCHAR(36) =''OUTPUTASBEGINSELECT @.UserId =CONVERT(VARCHAR(36), UserId)FROM aspnet_UsersWHERE @.UserName = UserNameEND

Here is the result:

Running [dbo].[GetUserIdForUser] ( @.username = jason, @.userid = <DEFAULT> ).

No rows affected.
(0 row(s) returned)
@.userid = FBF8AF84-BE4A-4BA4-A393-5519C9C00932
@.RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].