Credentials and extended stored procedures

I have a database script that uses the extended stored procedures sp_OACreate and sp_OCMethod to execute an .exe file. The .exe file is located on the same machine as the SQL Server. At this time it does nothing but log the name of the user calling it.

When I execute the script from Management Studio (logged in as myself) the user being logged as the caller of the .exe is still NT AUTHORITY\SYSTEM. I don't know why NT AUTHORITY\SYSTEM is the caller, cause the SQL Server service runs under another domain account.

I have tried playing around with EXECUTE AS USER but no matter what, the caller of the .exe is always logged as NT AUTHORITY\SYSTEM.

Are there any way I can pass my credentials to the executable that I am calling from the T-SQL script?

Have you given the NT AUTHORITY/SYSTEM privileges on SQL Server?|||

No, not intentionally. It is a default SQL server installation.

I switched the user that runs the SQL service to a domain user (it was local system account before), and that is basically all that has been changed, apart from giving some domain users access to some databases on the server.

creating user procedures in the master database

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

Thanks...

Moving thread to the DB Engine forum.|||

It is not a best practice to create user stored procs in master. This SP was undocumented and not subject to the 3 release deprecation policy. I think it went away with the move to the resource database in SQL2K5.

With that, what is your specific scenario for creating stored procs in master?

Creating Stored Procuders

Hello All,
I am looking for standards for creating stored procedures. I am trying to
come up with a templete that we could use for consistancy. If anyone has any
Ideas I would appreciate it.
Thanks
Anthony Dawson MCSE *3, MCDBA
Hi
Query Analyser has some simple templates for creating a procedure look at
the File/New options. You may want to add a mandatory SET NOCOUNT ON, for an
insert/update procedure you may want to add a standard BEGIN TRANSACTION
block that checks for existing transactions and similary a COMMIT/ROLLBACK
section. You may want to look at
http://www.sommarskog.se/error-handling-II.html
John
"Anthony Dawson" <Anthony Dawson@.discussions.microsoft.com> wrote in message
news:829FDD04-9905-4CCA-8788-58336C5B3207@.microsoft.com...
> Hello All,
> I am looking for standards for creating stored procedures. I am trying to
> come up with a templete that we could use for consistancy. If anyone has
any
> Ideas I would appreciate it.
> Thanks
> Anthony Dawson MCSE *3, MCDBA
|||Thanks I will check those templates out.
"John Bell" wrote:

> Hi
> Query Analyser has some simple templates for creating a procedure look at
> the File/New options. You may want to add a mandatory SET NOCOUNT ON, for an
> insert/update procedure you may want to add a standard BEGIN TRANSACTION
> block that checks for existing transactions and similary a COMMIT/ROLLBACK
> section. You may want to look at
> http://www.sommarskog.se/error-handling-II.html
> John
> "Anthony Dawson" <Anthony Dawson@.discussions.microsoft.com> wrote in message
> news:829FDD04-9905-4CCA-8788-58336C5B3207@.microsoft.com...
> any
>
>

Creating Stored Procuders

Hello All,
I am looking for standards for creating stored procedures. I am trying to
come up with a templete that we could use for consistancy. If anyone has any
Ideas I would appreciate it.
Thanks
Anthony Dawson MCSE *3, MCDBAHi
Query Analyser has some simple templates for creating a procedure look at
the File/New options. You may want to add a mandatory SET NOCOUNT ON, for an
insert/update procedure you may want to add a standard BEGIN TRANSACTION
block that checks for existing transactions and similary a COMMIT/ROLLBACK
section. You may want to look at
http://www.sommarskog.se/error-handling-II.html
John
"Anthony Dawson" <Anthony Dawson@.discussions.microsoft.com> wrote in message
news:829FDD04-9905-4CCA-8788-58336C5B3207@.microsoft.com...
> Hello All,
> I am looking for standards for creating stored procedures. I am trying to
> come up with a templete that we could use for consistancy. If anyone has
any
> Ideas I would appreciate it.
> Thanks
> Anthony Dawson MCSE *3, MCDBA|||Thanks I will check those templates out.
"John Bell" wrote:
> Hi
> Query Analyser has some simple templates for creating a procedure look at
> the File/New options. You may want to add a mandatory SET NOCOUNT ON, for an
> insert/update procedure you may want to add a standard BEGIN TRANSACTION
> block that checks for existing transactions and similary a COMMIT/ROLLBACK
> section. You may want to look at
> http://www.sommarskog.se/error-handling-II.html
> John
> "Anthony Dawson" <Anthony Dawson@.discussions.microsoft.com> wrote in message
> news:829FDD04-9905-4CCA-8788-58336C5B3207@.microsoft.com...
> > Hello All,
> > I am looking for standards for creating stored procedures. I am trying to
> > come up with a templete that we could use for consistancy. If anyone has
> any
> > Ideas I would appreciate it.
> >
> > Thanks
> > Anthony Dawson MCSE *3, MCDBA
>
>

Creating Stored Procuders

Hello All,
I am looking for standards for creating stored procedures. I am trying to
come up with a templete that we could use for consistancy. If anyone has any
Ideas I would appreciate it.
Thanks
Anthony Dawson MCSE *3, MCDBAHi
Query Analyser has some simple templates for creating a procedure look at
the File/New options. You may want to add a mandatory SET NOCOUNT ON, for an
insert/update procedure you may want to add a standard BEGIN TRANSACTION
block that checks for existing transactions and similary a COMMIT/ROLLBACK
section. You may want to look at
http://www.sommarskog.se/error-handling-II.html
John
"Anthony Dawson" <Anthony Dawson@.discussions.microsoft.com> wrote in message
news:829FDD04-9905-4CCA-8788-58336C5B3207@.microsoft.com...
> Hello All,
> I am looking for standards for creating stored procedures. I am trying to
> come up with a templete that we could use for consistancy. If anyone has
any
> Ideas I would appreciate it.
> Thanks
> Anthony Dawson MCSE *3, MCDBA|||Thanks I will check those templates out.
"John Bell" wrote:

> Hi
> Query Analyser has some simple templates for creating a procedure look at
> the File/New options. You may want to add a mandatory SET NOCOUNT ON, for
an
> insert/update procedure you may want to add a standard BEGIN TRANSACTION
> block that checks for existing transactions and similary a COMMIT/ROLLBACK
> section. You may want to look at
> http://www.sommarskog.se/error-handling-II.html
> John
> "Anthony Dawson" <Anthony Dawson@.discussions.microsoft.com> wrote in messa
ge
> news:829FDD04-9905-4CCA-8788-58336C5B3207@.microsoft.com...
> any
>
>sql

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.
Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan
|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.
|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.
|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve
|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.
|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve
|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.
|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan
|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 =
'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=
''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=
''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=
''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=
''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - (
1;Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

Creating stored procs with MSforeachdb

I'm trying to create stored procedures in about 85 databases. I keep
running into the problem 'Incorrect syntax near the keyword
'procedure':
declare @.cmd1 varchar(500)
set @.cmd1 = 'if ''?'' like ''%_06'' CREATE procedure newproc AS select * from
item'
exec sp_MSforeachdb @.command1=@.cmd1
Thanks.Hello,
You can use something like this:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
CREATE procedure newproc AS select * from item
'')'
However, you should consider if it's better to keep so many different
databases (with the same structure) or if you should consolidate all
the data in a single database. If you are using different databases for
each company it may be OK, but if you are using different databases for
each year, you should reconsider.
Razvan|||Why don=B4t you do something like this:
SELECT 'USE ' + NAME + CHAR(13) + 'CREATE procedure newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
Then use the output for the execution.
HTH, Jens Suessmeyer.|||Razvan,
Thank you for your reply. However, this give the result "There is
already an object named 'setnewrate_06_Feb' in the database." once for
each of the databases. It creates the proc in master, never sets the
context to the other dbs, and tries to recreate the proc in master
repeatedly. Here's the actual statement:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' exec(''
create procedure setnewrate_06_Feb AS
UPDATE setRate SET TPAFee = 0.23 WHERE [Remit Date] >=''''1/1/2005'''' OR resolved = 0
UPDATE setRate SET TPAFee = 0.18 WHERE [Remit Date] >=''''1/1/2006''''
UPDATE setRate SET FFPRate = 0.6745 WHERE [Submit Date] <
''''10/1/2005'''' OR resolved = 0
UPDATE setRate SET FFPRate = 0.6698 WHERE [Submit Date] >=''''10/1/2005''''
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 0
UPDATE setRate SET AdminRate = 0 WHERE [Submit Date] <=''''11/30/04'''' AND resolved = 1
UPDATE setRate SET AdminRate = 0.015 WHERE [Submit Date] >
''''11/30/04''''
UPDATE setRate SET expcd = ([amount]*[FFPRate]*(1-AdminRate)) - ([Bill
Log Units]*[TPAFee])
'')'
I also appreciate your comment on consolidating databases. We are
actively considering that.
Thanks again.|||Jens,
This unfortunately does not work because "create procedure" must be the
first statement in a batch.
Thanks for the response.
Steve|||This is working, because "GO" is the batch separator in this case. So
th CREATE Proc is the first statement in the batch...
HTH, Jens Suessmeyer.|||Hi Jens,
I really wish it did work. Here's a part of the output that I used to
execute:
USE Glendale_06 CREATE procedure newproc AS select * from item GO
USE Ash_Fork_06 CREATE procedure newproc AS select * from item GO
USE Grand_Canyon_06 CREATE procedure newproc AS select * from item GO
Here's the output from this:
Server: Msg 111, Level 15, State 1, Line 1
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Thank you,
Steve|||Sorry, you are definitely 100% correct.
This should fix it.
SELECT 'USE ' + NAME + CHAR(13) + 'GO' + CHAR(13) + 'CREATE procedure
newproc AS select *
from item' + CHAR(13) + 'GO' from master..sysdatabases
HTH, Jens Suessmeyer.|||Hello,
The following should work:
exec sp_MSforeachdb 'if ''?'' like ''%_06'' use ? exec(''
CREATE procedure newproc AS select * from item
'')'
Razvan|||Thanks VERY MUCh to you both, I will give these both a try.
Cheers,
Steve

creating stored procedures with msde ?

hye
Do ny help me by telling
1. Can we create stored procedures with MSDE ?
2. How can we include stored procedures with deployment ?
3. How can we install MSDE with our application ?
Vijay,
1. Yes
2. You install stored procedures with a SQL script
3. Check out
http://msdn.microsoft.com/library/de...l/msdedepl.asp
Jon Jahren
"vijay" <vkodwani@.ce.com.pk> wrote in message
news:8d5a15e3.0411110415.69d6ab90@.posting.google.c om...
> hye
> Do ny help me by telling
> 1. Can we create stored procedures with MSDE ?
> 2. How can we include stored procedures with deployment ?
> 3. How can we install MSDE with our application ?

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
Thanks
Post the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedure
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
ThanksPost the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedur
e
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
ThanksPost the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedure
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating stored procedures in sql server

I just installed sql server 2005 on my machine. I've been using Access for some time and have created my stored procedures easily in it. However, this sql server 2005 is so complicated. In Access, all I had to do was "Create a New Query", write my sql statement, name and save it.

However, I am having so much trouble with this sql server. For example, when I create a stored procedure in sql server, it has a .sql extension. I am assuming the stored procedure is a file. However, after saving it, I expand my database, expand programmability, and expand stored procedures, yet my new stored procedure is not there. It's placed in a file called projects. I need to access this stored procedure from code, but in visual studio, I get an error message, "Cannot find stored procedure sp_Roster".

Here is the code I used to access my stored procedure from vb.net. I thought it would work but it didnt

Dim comm As New SqlCommand
Dim strsql As String
Dim strconn As String
strsql = "sp_Roster"
strconn = "server=Home; user=sa; pwd=juwar74; database=Book;"
With comm
.Connection = New SqlConnection(strconn)
.CommandText = strsql
.CommandType = CommandType.StoredProcedure
With .Parameters.Add("TeacherID", SqlDbType.Char)
.Value = "DawsMark@.aol.com"
End With
With .Parameters.Add("ClassID", SqlDbType.Int)
.Value = CInt(classid)
End With
With .Parameters.Add("sID", SqlDbType.Int)
.Value = ssID
End With
With .Parameters.Add("sLastName", SqlDbType.Char)
.Value = lastname
End With
With .Parameters.Add("sFirstName", SqlDbType.Char)
.Value = firstname
End With
With .Parameters.Add("sMiddleName", SqlDbType.Char)
.Value = middlename
End With
With .Parameters.Add("Student", SqlDbType.Char)
.Value = fullname
End With
With .Parameters.Add("Password", SqlDbType.Char)
.Value = password
End With
.Connection.Open()
.ExecuteNonQuery()
With comm.Connection
If .State = ConnectionState.Open Then
.Close()
End If
End With
End With

Here is my procedure that I created and that was saved as sp_Roster.sql in sql server

CREATE PROCEDURE sp_Roster

AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)
END
GO

Why isn't ado.net finding it in sql server. Is the sqlstr correct?

In order to save a stored procedure to the DB in SQL 2005 you need to execute this code instead of saving the text as a file. Just click the "Execute" button and you should be ok. Another thing however, is that you have not declared your variables. You will not be able to execute/save your stored procedure without this.

CREATEPROCEDURE sp_Roster

@.TeacherIDint,

@.ClassIDint,

@.sIDint,

@.sLastNamenvarchar(100),

@.sFirstNamenvarchar(100),

@.sMiddleNamenvarchar(50),

@.Studentnvarchar(100),

@.Passwordnvarchar(100)

AS

BEGIN

SETNOCOUNTON;

-- Insert statements for procedure here

INSERTINTO Roster(TeacherID, ClassID,sID, sLastName, sFirstName, sMiddleName, Student,Password)VALUES(@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)

END

GO

|||

Unfortunately, Sql Server doesn't work the same as Access in this way. Wehn you save a query in Sql Server, you are saving the query, but no the query to the database. The script has to be run to actually create your stored procedure. This can be done by pressing F5. However, your query itself needs some work as you have no input parameters to your stored procedure.

sql

Creating Stored Procedures Based On Variables

Hi There,

I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?

Thanks

You would have to use dynamic SQL in the stored procedure. Assuming all you tables had a unique column named ID and an associated column named NAME, you could create a simple procedure like:
Create Procedure GetNameByID(

@.tableName varchar(100),

@.ID int

)

AS

Begin

Declare @.sql nvarchar(1000)

Set @.sql = N'Select "NAME" From ' + @.tableName + N' Where ID = ' + Convert( varchar(10), @.ID )

exec( @.sql )

End

go

exec GetNameByID 'sysobjects', 1

exec GetNameByID 'syscolumns', 1

And you could get more complicated from there.

Creating Stored procedures

HI

I am trying to create a stored procedure which inputs data from an access db to an SQL DB. My code:

CREATE PROCEDURE s2_comms
@.DBpath varchar(50)
AS
EXEC sp_addlinkedserver 'TempS2commsServer', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', @.DBpath
CREATE DATABASE S2comms
USE S2comms
select * into Codes from OPENQUERY(TempS2commsServer, 'SELECT * FROM Codes')
select * into comms from OPENQUERY(TempS2commsServer, 'SELECT * FROM comms')
EXEC sp_dropserver 'TempS2commsServer'

But it tells me:
a USE database statement is not allowed in a procedure or trigger.

i am new to sql and cant figure out how to get round this, any ideas?
thanks for any help.well if you are using SQL Server, just create a DTS package to do this, much easier. Then you can schedule it to run when ever.

Creating SQL Server Stored Procedures in Deployment

Hey guys,

I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?

However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code.

I would like to create the stored procedure inside that catch block. I just don't know how.

The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.

With the help of the executenonquery u may create the stored procedures which is not the best way. stored procedures,functions or triggers creation means generally people will implement in database not through front end. Not only in this case u can perform ddl commands through executenonquery but max no one will prefer.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

Creating procedures in PL/SQL

I want to create a procedure that takes a product number as an argument and displays the name of the product. This is what I have so far, but I am getting errors. I was wondering if someone could please help me out with this. Thanks.

PROCEDURE displayProd(prono PRODUCTS.PNO%TYPE, name PRODUCTS.PNAME%TYPE)

IS


BEGIN

SELECT PRODUCTS.PNAME
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.print_line('The name of the product is ' || name);

END displayProd;

/Hello,

which errors do you get ?

Here are some suggestion ...

1) define your in or out vars for better reading
2) define a field name as a placeholder for the result in your select
3) What is print_line ? Do you mean put_line ?

PROCEDURE displayProd(prono IN PRODUCTS.PNO%TYPE, name OUT PRODUCTS.PNAME%TYPE)

IS
BEGIN
SELECT PRODUCTS.PNAME
INTO name
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.put_line('The name of the product is ' || name);

END displayProd;

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||I get an error that says: invalid sql statement. i think i need a create or replace procedure. I tried that, and it said: Warning: Procedure created with compilation errors. So, I still don't know what the prob. is.|||Originally posted by izy
I get an error that says: invalid sql statement. i think i need a create or replace procedure. I tried that, and it said: Warning: Procedure created with compilation errors. So, I still don't know what the prob. is.

Does it have to be a procedure? I think a function would be more appropriate. You would pass in one argument (the product number) and return one argument (the associated product name).

However, if you need to write a procedure (i.e., a homework assignment) then the recommendations from the previous reply should work (i.e., including the INTO clause into your SQL statement).|||Hello,

of course the beginning is
CREATE OR REPLACE PROCEDURE ...

If you would like to see the errors use f.e. the AlligatorSQL at
http://www.alligatorsql.com/download/alligator.zip

or use the statement

SELECT e.*
FROM sys.error$ e,
sys.all_objects a
WHERE a.object_id = e.obj#
AND a.owner = 'SCOTT'
AND a.object_name = 'MYPROC'
ORDER BY e.sequence#

(Please change the owner SCOTT and the object_name MYPROC to your schema and procedure name)

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||Sorry, I meant something else. I am told to write a procedure that displays the names of products for these orders that have not been shipped. When I run this, I get a 'Warning: Procedure created with compilation errors. I have no idea what the problem is. Am I declaring the null right?

CREATE OR REPLACE PROCEDURE ItemsShipped(myname IN PRODUCTS.PNAME%TYPE)

IS

BEGIN
SELECT PRODUCTS.PNAME
INTO myname
FROM PRODUCTS, ORDERS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO AND
ORDERS.SHIPPED IS NULL;

END ItemsShipped;

/

Originally posted by izy
I want to create a procedure that takes a product number as an argument and displays the name of the product. This is what I have so far, but I am getting errors. I was wondering if someone could please help me out with this. Thanks.

PROCEDURE displayProd(prono PRODUCTS.PNO%TYPE, name PRODUCTS.PNAME%TYPE)

IS


BEGIN

SELECT PRODUCTS.PNAME
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.print_line('The name of the product is ' || name);

END displayProd;

/|||Hello,

the only thing I can see is, that you have declared the myname parameter as an IN and you shift the result into this var.

Please declare it as IN OUT ...

Again ...

If you have error use a GUI that displays you the errors or select the error table like I mentioned before.

Otherwise you are developing in the dark

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||Originally posted by izy
Sorry, I meant something else. I am told to write a procedure that displays the names of products for these orders that have not been shipped. When I run this, I get a 'Warning: Procedure created with compilation errors. I have no idea what the problem is. Am I declaring the null right?

CREATE OR REPLACE PROCEDURE ItemsShipped(myname IN PRODUCTS.PNAME%TYPE)

IS

BEGIN
SELECT PRODUCTS.PNAME
INTO myname
FROM PRODUCTS, ORDERS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO AND
ORDERS.SHIPPED IS NULL;

END ItemsShipped;

/
In SQL Plus, just type SHOW ERROR after receiving the warning. In this case, you should see this:

SQL> show error
Errors for PROCEDURE ITEMSSHIPPED:

LINE/COL ERROR
--- --------------------
4/1 PL/SQL: SQL Statement ignored
5/6 PLS-00403: expression 'MYNAME' cannot be used as an INTO-target
of a SELECT/FETCH statement

This is bacause IN parameters are read-only. Change IN to OUT or IN OUT.

Creating Procedures from file and .net

I am trying to read a SP.sql file where i want to write all the procedures to create them in the database

CREATE PROCEDURE SP_1
AS
BEGIN
DELETE FROM tb1
END

CREATE PROCEDURE SP_2
AS
BEGIN
DELETE FROM tb2
END

if there is only one procedure in the file it works, with > 1 procedure it fails
inserting GO between doesnt work

how is it possible to run a sql file with many stored procedures in it from a server langages ?

thank youI tried with OSQL with GO between the two proc creation scripts , it works fine.|||no it doesnt work for me, from SQL SERVER Management studio of course it works
but reading/executing a file from a server langage doesnt work in that way

OSQL is not a server langage|||I tried with OSQL with GO between the two proc creation scripts , it works fine.

Hariarul1 is right,you should look into BOL first to use OSQL utility.
Check this..

EXEC master..xp_cmdshell 'osql -S Servername -U sa -P -ic:\SQLScript.sql'|||i just want to run from asp.net application an .SQL file to create stored procedure and views
is there any way to have in the same sql file many procedures (as my exemple shows) ?

i am not going to use another tool, if noy possible then i can run them one by one in the code , but will be fine to get all at the same place

thank you|||i just want to run from asp.net application an .SQL file to create stored procedure and views
is there any way to have in the same sql file many procedures (as my exemple shows) ?

i am not going to use another tool, if noy possible then i can run them one by one in the code , but will be fine to get all at the same place

thank you

OSql utility is given along with sql server so you need not have to install it seperately,just execute that above sql statement in your ASP.net application,thats all..you will get what you want...|||ok then i must look how it works

i am creating all the tables from aspnet aplication and i cannot creature procedures , one yes, 2 not !!!

that sound crazy

thanks a lot|||Your query is failing because CREATE PROC must be the first statement in a batch. in osql, the GOs separate batches.

GO is not a keyword in sql however. it's understood only by osql.exe and query analyzer to separate batches (sqlcmd.exe and ssms in 2005 recognize GO as well). The sql server engine itself does not recognize this keyword though, so you can't have GOs in the sql that you send to the server using SqlClient, for example (I assume you are using SqlClient since you are using .net).

If you have SMO installed however, you can also use the ServerConnection class to execute scripts from code that are separated by GOs. Use the ExecuteNonQuery() method and friends. If you use this class, you don't have to launch osql.exe as a separate process, and you don't have to use xp_cmdshell (which you should never use if you can get away with it. using it opens up some pretty big security holes).

See this page for details: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnec tion.executenonquery.aspx

Note that SMO is installed by default if you have the 2005 client tools installed (SSMS). also you can download SMO and install it separately here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en (search for SMO on that page.)

SMO will work against any 2000 or 2005 server.|||thank you jezemine
i run of course all my create procedure one by one from a class and execute command
but i find very practicle to have them outside, not hard coded
i am using a xml file

thank you to every body

creating procedures

I have seen developers create procedures by declaring the variables and
others declaring the variables and setting them to null like below.
Is one way better that another?
CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
@.issuerId int output,
@.companyId int = NULL,
@.issuerName varchar(60) = NULL,
@.dateAdded datetime = NULL,
@.dateModified datetime = NULL,
@.statusDate datetime = NULL,
@.modifiedBy smallint = NULL ....
thanks
MeenalWhen declaring parameters of a procedure the declaration can include a
default value for each parameter. This is what your example shows. This way
the parameters become optional.
On the other hand, the declaration of a variable cannot include a default
value.
ML
http://milambda.blogspot.com/|||If a parameter to a stored procedure has a default assigned, the
parameter is optional when called. If no default is defined, the
parameter is required, and if not provided it will fail:
exec sp_helpindex
Server: Msg 201, Level 16, State 4, Procedure sp_helpindex, Line 0
Procedure 'sp_helpindex' expects parameter '@.objname', which was not
supplied.
Roy Harvey
Beacon Falls, CT
On Tue, 27 Jun 2006 00:48:47 -0400, "meenal"
<meenal_dhody@.hotmail.com> wrote:

>I have seen developers create procedures by declaring the variables and
>others declaring the variables and setting them to null like below.
>Is one way better that another?
>CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
> @.issuerId int output,
> @.companyId int = NULL,
> @.issuerName varchar(60) = NULL,
> @.dateAdded datetime = NULL,
> @.dateModified datetime = NULL,
> @.statusDate datetime = NULL,
> @.modifiedBy smallint = NULL ....
>thanks
>Meenal
>|||IF they are defined in the CREATE statement, what 'looks' like variables are
really known as 'Parameters'. Variables are defined in the stored procedure
body with the DECLARE statement.
It's often easy to confuse Parameters and Variables.
When Parameters are provided values when they are defined, two things are no
w possible. First, it is not necessary to provide a value for that Parameter
when the stored procedure is executed. Second, it may be that a particular
value 'should' be used it the stored procedure if no value of offered.
In Query Analyzer, try the following example:
CREATE PROCEDURE dbo.MyTest
( @.CheckValue int = 0
, @.DateInput datetime = NULL
)
AS
IF @.DateInput IS NULL
SET @.DateInput = getdate()
IF @.CheckValue = 0
PRINT 'Choice Number 0 Selected on ' + cast( @.DateInput AS varchar(11) )
ELSE IF @.CheckValue = 1
PRINT 'Choice Number 1 Selected on ' + cast( @.DateInput AS varchar(11) )
ELSE
PRINT 'Choice Number ' + cast( @.CheckValue AS varchar(5) ) + ' Selected on '
+ cast( @.DateInput AS varchar(11) )
GO
EXECUTE dbo.MyTest
EXECUTE dbo.MyTest @.CheckValue = 1
EXECUTE dbo.MyTest @.CheckValue = 25, @.DateInput = 'jan 1 2005'
--(Note: it is a good practice to use name=value pairs when calling stored p
rocedures and passing parameters.
--It makes your intention very clear, and protects against future changes to
the stored procedure.)
-- Clean up when finished
--DROP PROCEDURE MyTest
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"meenal" <meenal_dhody@.hotmail.com> wrote in message news:uathBVamGHA.2112@.TK2MSFTNGP04.phx
.gbl...
>I have seen developers create procedures by declaring the variables and
> others declaring the variables and setting them to null like below.
>
> Is one way better that another?
>
> CREATE PROCEDURE [dbo].[testsp_InsertIssuerDetails]
> @.issuerId int output,
> @.companyId int = NULL,
> @.issuerName varchar(60) = NULL,
> @.dateAdded datetime = NULL,
> @.dateModified datetime = NULL,
> @.statusDate datetime = NULL,
> @.modifiedBy smallint = NULL ....
>
> thanks
>
> Meenal
>
>

Creating multiple Stored procedure on SQL Server through ADO

Hi,
I want to create a set of procedures through my application on SQL Server, t
hrough ADO.
My Scipts is something of the form:
Use DB1
Go
create procedure <procedurename>
as
< procedure text>
Go
Create Procedure <procedurename>
as
<procedure text>
Go
But when I execute this statement it gives error 'Error in line 2 Go'
Is there a way to excute this script in a single call or I have to make mult
iple calls.
Thanks
PushkarInstead of GO use a semicol for this:
Use DB1;
create procedure <procedurename> ...
HTH, Jens Suessmeyer.|||You can't pass GO through ADO, as GO isn't a SQL command. For each GO, do an
.Execute of your
command object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:uDUQl6h1FHA.736@.tk
2msftngp13.phx.gbl...
Hi,
I want to create a set of procedures through my application on SQL Server, t
hrough ADO.
My Scipts is something of the form:
Use DB1
Go
create procedure <procedurename>
as
< procedure text>
Go
Create Procedure <procedurename>
as
<procedure text>
Go
But when I execute this statement it gives error 'Error in line 2 Go'
Is there a way to excute this script in a single call or I have to make mult
iple calls.
Thanks
Pushkar|||Jens (Jens@.sqlserver2005.de) writes:

> Instead of GO use a semicol for this:
> Use DB1;
> create procedure <procedurename> ...
> HTH, Jens Suessmeyer.
>
use tempdb;
CREATE PROCEDURE blafs AS
SELECT 'Uhuh'
gives:
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
; is a statement terminator, handled by SQL Server.
GO is a batch terminator, handled client-side by some query tools.
GO and ; fills different purposes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp