Saving Stored Procedure

Hello,
I installed SQL Server 2005 on my PC and am using it to access a SQL
database running on a remote server. Using SQL Server Management Studio on
my PC, I connect to the remote database using SQL Server Authentication and
an account that has database owner permissions. At least I think it does -
when I go to the database, then Security / Roles / Database Roles / db_owner
,
it is listed as a role member there.
Once connected, I can change data in tables and view the stored procedures.
However, when I modify one of the stored procedures (by right clicking on it
in Object Explorer, then clicking Modify, then changing it), and then attemp
t
to save the changes, a window pops up on my screen asking me to identify the
location where I want to save the changed stored procedure. It defaults to
a
folder on my local hard drive: My Documents/SQL Server Management
Studio/Projects.
I just want to save the changes in the stored procedure on the remote
database (the master version). Shouldn't I be able to do that? If so, how?
By the way, when I click Modify to view the stored procedure, it's name, at
the top of the window where the T-SQL text is, reads: the address of the
server…SQLQuery1.sql (instead of it's actual name) - for example it reads:
100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
Thanks in advance for help.SSMS opens a Query Window for you when you select to modify the stored proce
dure. Same thing as if
you right-click the server and say "New Query". SSMS also pastes the source
code for the proc in
that query window. As with Word, for instance, a new document has some stand
ard name and when you
select "save" Word (or SSMS) ask you for how you want to name that file.
If you want to execute the ALTER PROCEDURE statement to actually change the
procedure, just execute
the code in the query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio o
n
> my PC, I connect to the remote database using SQL Server Authentication an
d
> an account that has database owner permissions. At least I think it does
-
> when I go to the database, then Security / Roles / Database Roles / db_own
er,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored procedures
.
> However, when I modify one of the stored procedures (by right clicking on
it
> in Object Explorer, then clicking Modify, then changing it), and then atte
mpt
> to save the changes, a window pops up on my screen asking me to identify t
he
> location where I want to save the changed stored procedure. It defaults t
o a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so, ho
w?
> By the way, when I click Modify to view the stored procedure, it's name, a
t
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it read
s:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||You don't use the Save button. Use the Execute button (or press F5). This
runs the ALTER PROCEDURE script you have modified. The Save button is for
saving scripts on your machine, not for saving changes to the database...
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio
> on
> my PC, I connect to the remote database using SQL Server Authentication
> and
> an account that has database owner permissions. At least I think it
> does -
> when I go to the database, then Security / Roles / Database Roles /
> db_owner,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored
> procedures.
> However, when I modify one of the stored procedures (by right clicking on
> it
> in Object Explorer, then clicking Modify, then changing it), and then
> attempt
> to save the changes, a window pops up on my screen asking me to identify
> the
> location where I want to save the changed stored procedure. It defaults
> to a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
> By the way, when I click Modify to view the stored procedure, it's name,
> at
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it
> reads:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||conmat (conmat@.discussions.microsoft.com) writes:
> However, when I modify one of the stored procedures (by right clicking
> on it in Object Explorer, then clicking Modify, then changing it), and
> then attempt to save the changes, a window pops up on my screen asking
> me to identify the location where I want to save the changed stored
> procedure. It defaults to a folder on my local hard drive: My
> Documents/SQL Server Management Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
But you should really save the code to disk and put it under version
control. The datbase should just be viewed as a binary repository.
Of course, as long as you are only playing around, the database may do,
but it's not suitable for real application development.
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|||Thank you for your help.
I don't think I understand the difference between:
1) Modifying the script in a stored procedure on the database without
actually running the content / code of the procedure
versus
2) Running the actual content / code of the procedure.
If I right click on a stored procedure, choose modify, the script of the
procedure pops up in the Query Window(?), and it starts with:
ALTER PROCEDURE ____
AS
If I then modify the script, then Execute, does that just change the script
without actually running the content (I think the answer is yes, if I
understand you correctly)?
If that is so, how do I then actually execute the content - do I need to
remove the ALTER PROCEDURE___ AS, then Execute (but, how do I remove the
ALTER PROCEDURE without altering the procedure)?
I'm very new to SQL Sever, as you can tell - thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You don't use the Save button. Use the Execute button (or press F5). Thi
s
> runs the ALTER PROCEDURE script you have modified. The Save button is for
> saving scripts on your machine, not for saving changes to the database...
>
>
> "conmat" <conmat@.discussions.microsoft.com> wrote in message
> news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
>
>|||> 1) Modifying the script in a stored procedure on the database without
> actually running the content / code of the procedure
> versus
This changes the code that SQL Server has for the stored procedure. This
means it will change what is stored, and will change what is executed every
time anyone calls the stored procedure.

> 2) Running the actual content / code of the procedure.
This merely runs the code, once.
Consider:
CREATE PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 1;
END
GO
When you run this code, it creates a procedure. It does not run the SELECT
statement. When you *EXECUTE* the stored procedure:
EXEC dbo.foo;
You will see a single-column resultset with the value 1. Just like you
would have seen if you ran the code within the procedure, by itself:
SELECT a = 1;
Now, if you run this:
ALTER PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 2;
END
GO
Again, it does not run the SELECT statement, it changes the script that SQL
Server has stored for the procedure. So the next time someone executes the
stored procedure, they will get a single-column resultset with the value 2.

> If that is so, how do I then actually execute the content
Well, if you have already run the ALTER and you are happy with it, then like
above:
EXEC dbo.foo;

> I'm very new to SQL Sever, as you can tell - thanks again for your help.
I think there is plenty of material out there that you should familiarize
yourself with.
http://www.aspfaq.com/2423|||Hey Guys,
The Orginal question was how to save a modified stored procedure.
I read all your reponses but none addressed the issue.
I have a similar problem
Can anyone just simple show how to save a modified sp in sql server managem
ent studio.
Thanks|||> The Orginal question was how to save a modified stored procedure.
Save where? Do you want to create a file containing the TSQL code used to cr
eate that procedure? Or
do you want to execute the ALTER PROCEDURE ... command so you do modify that
procedure according to
the changes you have made?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sportrx" <sportrx.24qulq@.mail.codecomments.com> wrote in message
news:sportrx.24qulq@.mail.codecomments.com...
> Hey Guys,
> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
> I have a similar problem
> Can anyone just simple show how to save a modified sp in sql server
> management studio.
> Thanks
>
> --
> sportrx
> ---
> Posted via http://www.codecomments.com
> ---
>|||> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
That's because the original question remains unclear.

> Can anyone just simple show how to save a modified sp in sql server
> management studio.
What does "save a modified sp" mean? Do you want to apply the changes to
the database, save the script in a .PRC or .SQL file, check the changes into
source control, ...?
If you run an ALTER PROCEDURE script, that will apply the changes to the
stored procedure in the current database. If you want to save the script to
a file, click the Save icon. Those are two *very* different actions.
A

Saving datetime data in a stored procedure

Hello everyone

I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output
25/05/2007 18:30
Does anyone know the best way to get this into the correct format for my SP parameter

cmdPublish.Parameters.Add(newSqlParameter("@.datPublishDate", System.Data.SqlDbType.DateTime));

cmdPublish.Parameters["@.datPublishDate"].Value = ??

Thanks

Use the DateTime.ParseExact( ) method and pass your date value as an argument

ParseExact(Value,Format,FormatProvide) - Format provide can be passed as null and provide the Format value as string of whatever format you want it will return DateTime object which can be assigned to your Parameter

http://msdn2.microsoft.com/en-us/library/w2sa9yss.aspx

|||

If you are doing this from C# there is no "format" you need - you need to use a DateTime variable:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = DateTime.Now;

If you are trying to get some string value from something like a webform, then I would use something like this:

DateTime dt;cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParse( yourStringValue,null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

You can also use TryParseExact:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParseExact( yourStringValue,"dd/MM/yyyy hh:mm",null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

|||

Thanks for the help - in the end I didn't need to do any of that.
I used
Convert.ToDateTime(str)

and it worked. turned out that there was something ELSE wrong with the SP.

How embarrassing.

Thanks

Saving Images in SQL Servers Image Datatype

I would like to store images in and retrieve images from SQL Server. And the images are to be stored and retrieved from ASP. How do I do it?Hi..

Check out in this web site

http://www.sqlteam.com/item.asp?ItemID=986

Regards,
Selva Balaji B.sql

Saving datetime data in a stored procedure

Hello everyone

I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output
25/05/2007 18:30
Does anyone know the best way to get this into the correct format for my SP parameter

cmdPublish.Parameters.Add(newSqlParameter("@.datPublishDate", System.Data.SqlDbType.DateTime));

cmdPublish.Parameters["@.datPublishDate"].Value = ??

Thanks

Use the DateTime.ParseExact( ) method and pass your date value as an argument

ParseExact(Value,Format,FormatProvide) - Format provide can be passed as null and provide the Format value as string of whatever format you want it will return DateTime object which can be assigned to your Parameter

http://msdn2.microsoft.com/en-us/library/w2sa9yss.aspx

|||

If you are doing this from C# there is no "format" you need - you need to use a DateTime variable:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = DateTime.Now;

If you are trying to get some string value from something like a webform, then I would use something like this:

DateTime dt;cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParse( yourStringValue,null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

You can also use TryParseExact:

cmdPublish.Parameters.Add("@.datPublishDate", SqlDbType.DateTime ).Value = ( DateTime.TryParseExact( yourStringValue,"dd/MM/yyyy hh:mm",null, System.Globalization.DateTimeStyles.None,out dt ) ) ? (object) dt : (object) DBNull.Value;

|||

Thanks for the help - in the end I didn't need to do any of that.
I used
Convert.ToDateTime(str)

and it worked. turned out that there was something ELSE wrong with the SP.

How embarrassing.

Thanks

Saving a stored procedure or a user defined function in SQL 2000

I can create stored procedures and user defined functions in the Query
Analyzer, but the only option I see for saving are to save to an external
sql file. Is there a way to save directly to the database while in Query
Analyzer?
Bill
They are normally saved by default. If you start your stored procedure, for
example, with CREATE PROCEDURE myProcname etc. and you execute that query
you have just created (or saved sort of) that myProcname Stored Procedure
into your database.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46575c07$0$16706$4c368faf@.roadrunner.com...
>I can create stored procedures and user defined functions in the Query
>Analyzer, but the only option I see for saving are to save to an external
>sql file. Is there a way to save directly to the database while in Query
>Analyzer?
> Bill
>
|||To clarify. Not "by default" you have to actually execute a query to save
it. This query is the CREATE syntax for creating the object. When you
execute that it is being saved. To modify it you would then ALTER it. If you
go to books online and search for CREATE PROCEDURE you will see more on
that.
HTH
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
> They are normally saved by default. If you start your stored procedure,
> for example, with CREATE PROCEDURE myProcname etc. and you execute that
> query you have just created (or saved sort of) that myProcname Stored
> Procedure into your database.
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>
|||Mike,
I tried this for a stored procedure and it did save it to my database. But
I can't get it to work for a user defined function. In Query Analyzer the
user defined function parses with no errors, but it won't create in the
database. Any thoughts?
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
> To clarify. Not "by default" you have to actually execute a query to save
> it. This query is the CREATE syntax for creating the object. When you
> execute that it is being saved. To modify it you would then ALTER it. If
> you go to books online and search for CREATE PROCEDURE you will see more
> on that.
> HTH
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Bill
So when you execute the code that says CREATE FUNCTION ...
what happens? If you don't get any error, the function should be saved in
your database.
What makes you think it isn't created?
Perhaps you are looking for it in the Object Explorer and you didn't
refresh?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>
|||> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
What error message are you getting when you execute your CREATE FUNCTION
statement?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>
|||Mike,
It appears the function did save into the database, although it's not
showing up under User Defined Functions. It is showing up when I view
permissions under Users for user dbo, and an error message is popping up
there as follows:
"Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not found
in the UserDefinedFunctions collection. If the name is a qualified name,
use [] to separate various parts of the name, and try again."
I can't change the name or delete the function because I can't see it in the
database. Is there a way to repair this?
Bill
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>
|||Have you refreshed the list of User Defined Functions as Kalen suggested? If
it is showing up as an object that you can assign permissions to it is in
your database.
When are you getting that error? When trying to assign permissions? Try and
close Enterprise Manager and reopen it.
Can you call the function and use it?
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46577035$0$4642$4c368faf@.roadrunner.com...
> Mike,
> It appears the function did save into the database, although it's not
> showing up under User Defined Functions. It is showing up when I view
> permissions under Users for user dbo, and an error message is popping up
> there as follows:
> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
> found in the UserDefinedFunctions collection. If the name is a qualified
> name, use [] to separate various parts of the name, and try again."
> I can't change the name or delete the function because I can't see it in
> the database. Is there a way to repair this?
> Bill
>
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46576681$0$4901$4c368faf@.roadrunner.com...
>
|||I did the refresh on User Defined Functions and the function is now in the
list. Before, I was getting the error message when clicking on Permissions
for the dbo user who owns the function. This went away after the refresh.
Thanks to all for your help.
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Have you refreshed the list of User Defined Functions as Kalen suggested?
> If it is showing up as an object that you can assign permissions to it is
> in your database.
> When are you getting that error? When trying to assign permissions? Try
> and close Enterprise Manager and reopen it.
> Can you call the function and use it?
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46577035$0$4642$4c368faf@.roadrunner.com...
>
|||Glad to help have a great weekend.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46578928$0$15089$4c368faf@.roadrunner.com...
>I did the refresh on User Defined Functions and the function is now in the
>list. Before, I was getting the error message when clicking on Permissions
>for the dbo user who owns the function. This went away after the refresh.
> Thanks to all for your help.
> Bill
>
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
>

Saving a stored procedure or a user defined function in SQL 2000

I can create stored procedures and user defined functions in the Query
Analyzer, but the only option I see for saving are to save to an external
sql file. Is there a way to save directly to the database while in Query
Analyzer?
BillThey are normally saved by default. If you start your stored procedure, for
example, with CREATE PROCEDURE myProcname etc. and you execute that query
you have just created (or saved sort of) that myProcname Stored Procedure
into your database.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46575c07$0$16706$4c368faf@.roadrunne
r.com...
>I can create stored procedures and user defined functions in the Query
>Analyzer, but the only option I see for saving are to save to an external
>sql file. Is there a way to save directly to the database while in Query
>Analyzer?
> Bill
>|||To clarify. Not "by default" you have to actually execute a query to save
it. This query is the CREATE syntax for creating the object. When you
execute that it is being saved. To modify it you would then ALTER it. If you
go to books online and search for CREATE PROCEDURE you will see more on
that.
HTH
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote i
n message
news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
> They are normally saved by default. If you start your stored procedure,
> for example, with CREATE PROCEDURE myProcname etc. and you execute that
> query you have just created (or saved sort of) that myProcname Stored
> Procedure into your database.
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46575c07$0$16706$4c368faf@.roadrunne
r.com...
>|||Mike,
I tried this for a stored procedure and it did save it to my database. But
I can't get it to work for a user defined function. In Query Analyzer the
user defined function parses with no errors, but it won't create in the
database. Any thoughts?
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote i
n message
news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
> To clarify. Not "by default" you have to actually execute a query to save
> it. This query is the CREATE syntax for creating the object. When you
> execute that it is being saved. To modify it you would then ALTER it. If
> you go to books online and search for CREATE PROCEDURE you will see more
> on that.
> HTH
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote
in message
> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>|||Hi Bill
So when you execute the code that says CREATE FUNCTION ...
what happens? If you don't get any error, the function should be saved in
your database.
What makes you think it isn't created?
Perhaps you are looking for it in the Object Explorer and you didn't
refresh?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner
.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote
in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>|||> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
What error message are you getting when you execute your CREATE FUNCTION
statement?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner
.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote
in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>|||Mike,
It appears the function did save into the database, although it's not
showing up under User Defined Functions. It is showing up when I view
permissions under Users for user dbo, and an error message is popping up
there as follows:
"Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not fo
und
in the UserDefinedFunctions collection. If the name is a qualified name,
use [] to separate various parts of the name, and try again."
I can't change the name or delete the function because I can't see it in the
database. Is there a way to repair this?
Bill
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner
.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote
in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>|||Have you refreshed the list of User Defined Functions as Kalen suggested? If
it is showing up as an object that you can assign permissions to it is in
your database.
When are you getting that error? When trying to assign permissions? Try and
close Enterprise Manager and reopen it.
Can you call the function and use it?
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46577035$0$4642$4c368faf@.roadrunner
.com...
> Mike,
> It appears the function did save into the database, although it's not
> showing up under User Defined Functions. It is showing up when I view
> permissions under Users for user dbo, and an error message is popping up
> there as follows:
> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
> found in the UserDefinedFunctions collection. If the name is a qualified
> name, use [] to separate various parts of the name, and try again."
> I can't change the name or delete the function because I can't see it in
> the database. Is there a way to repair this?
> Bill
>
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46576681$0$4901$4c368faf@.roadrunner
.com...
>|||I did the refresh on User Defined Functions and the function is now in the
list. Before, I was getting the error message when clicking on Permissions
for the dbo user who owns the function. This went away after the refresh.
Thanks to all for your help.
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote i
n message
news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Have you refreshed the list of User Defined Functions as Kalen suggested?
> If it is showing up as an object that you can assign permissions to it is
> in your database.
> When are you getting that error? When trying to assign permissions? Try
> and close Enterprise Manager and reopen it.
> Can you call the function and use it?
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46577035$0$4642$4c368faf@.roadrunner
.com...
>|||Glad to help have a great weekend.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46578928$0$15089$4c368faf@.roadrunne
r.com...
>I did the refresh on User Defined Functions and the function is now in the
>list. Before, I was getting the error message when clicking on Permissions
>for the dbo user who owns the function. This went away after the refresh.
> Thanks to all for your help.
> Bill
>
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote
in message
> news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
>

Saving a stored procedure or a user defined function in SQL 2000

I can create stored procedures and user defined functions in the Query
Analyzer, but the only option I see for saving are to save to an external
sql file. Is there a way to save directly to the database while in Query
Analyzer?
BillThey are normally saved by default. If you start your stored procedure, for
example, with CREATE PROCEDURE myProcname etc. and you execute that query
you have just created (or saved sort of) that myProcname Stored Procedure
into your database.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46575c07$0$16706$4c368faf@.roadrunner.com...
>I can create stored procedures and user defined functions in the Query
>Analyzer, but the only option I see for saving are to save to an external
>sql file. Is there a way to save directly to the database while in Query
>Analyzer?
> Bill
>|||To clarify. Not "by default" you have to actually execute a query to save
it. This query is the CREATE syntax for creating the object. When you
execute that it is being saved. To modify it you would then ALTER it. If you
go to books online and search for CREATE PROCEDURE you will see more on
that.
HTH
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
> They are normally saved by default. If you start your stored procedure,
> for example, with CREATE PROCEDURE myProcname etc. and you execute that
> query you have just created (or saved sort of) that myProcname Stored
> Procedure into your database.
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an external
>>sql file. Is there a way to save directly to the database while in Query
>>Analyzer?
>> Bill
>|||Mike,
I tried this for a stored procedure and it did save it to my database. But
I can't get it to work for a user defined function. In Query Analyzer the
user defined function parses with no errors, but it won't create in the
database. Any thoughts?
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
> To clarify. Not "by default" you have to actually execute a query to save
> it. This query is the CREATE syntax for creating the object. When you
> execute that it is being saved. To modify it you would then ALTER it. If
> you go to books online and search for CREATE PROCEDURE you will see more
> on that.
> HTH
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an external
>>sql file. Is there a way to save directly to the database while in Query
>>Analyzer?
>> Bill
>>
>|||> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
What error message are you getting when you execute your CREATE FUNCTION
statement?
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to save
>> it. This query is the CREATE syntax for creating the object. When you
>> execute that it is being saved. To modify it you would then ALTER it. If
>> you go to books online and search for CREATE PROCEDURE you will see more
>> on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>|||Hi Bill
So when you execute the code that says CREATE FUNCTION ...
what happens? If you don't get any error, the function should be saved in
your database.
What makes you think it isn't created?
Perhaps you are looking for it in the Object Explorer and you didn't
refresh?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to save
>> it. This query is the CREATE syntax for creating the object. When you
>> execute that it is being saved. To modify it you would then ALTER it. If
>> you go to books online and search for CREATE PROCEDURE you will see more
>> on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>|||Mike,
It appears the function did save into the database, although it's not
showing up under User Defined Functions. It is showing up when I view
permissions under Users for user dbo, and an error message is popping up
there as follows:
"Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not found
in the UserDefinedFunctions collection. If the name is a qualified name,
use [] to separate various parts of the name, and try again."
I can't change the name or delete the function because I can't see it in the
database. Is there a way to repair this?
Bill
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to save
>> it. This query is the CREATE syntax for creating the object. When you
>> execute that it is being saved. To modify it you would then ALTER it. If
>> you go to books online and search for CREATE PROCEDURE you will see more
>> on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>|||Have you refreshed the list of User Defined Functions as Kalen suggested? If
it is showing up as an object that you can assign permissions to it is in
your database.
When are you getting that error? When trying to assign permissions? Try and
close Enterprise Manager and reopen it.
Can you call the function and use it?
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46577035$0$4642$4c368faf@.roadrunner.com...
> Mike,
> It appears the function did save into the database, although it's not
> showing up under User Defined Functions. It is showing up when I view
> permissions under Users for user dbo, and an error message is popping up
> there as follows:
> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
> found in the UserDefinedFunctions collection. If the name is a qualified
> name, use [] to separate various parts of the name, and try again."
> I can't change the name or delete the function because I can't see it in
> the database. Is there a way to repair this?
> Bill
>
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46576681$0$4901$4c368faf@.roadrunner.com...
>> Mike,
>> I tried this for a stored procedure and it did save it to my database.
>> But I can't get it to work for a user defined function. In Query
>> Analyzer the user defined function parses with no errors, but it won't
>> create in the database. Any thoughts?
>> Bill
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to
>> save it. This query is the CREATE syntax for creating the object. When
>> you execute that it is being saved. To modify it you would then ALTER
>> it. If you go to books online and search for CREATE PROCEDURE you will
>> see more on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored procedure,
>> for example, with CREATE PROCEDURE myProcname etc. and you execute that
>> query you have just created (or saved sort of) that myProcname Stored
>> Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>>
>|||I did the refresh on User Defined Functions and the function is now in the
list. Before, I was getting the error message when clicking on Permissions
for the dbo user who owns the function. This went away after the refresh.
Thanks to all for your help.
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Have you refreshed the list of User Defined Functions as Kalen suggested?
> If it is showing up as an object that you can assign permissions to it is
> in your database.
> When are you getting that error? When trying to assign permissions? Try
> and close Enterprise Manager and reopen it.
> Can you call the function and use it?
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46577035$0$4642$4c368faf@.roadrunner.com...
>> Mike,
>> It appears the function did save into the database, although it's not
>> showing up under User Defined Functions. It is showing up when I view
>> permissions under Users for user dbo, and an error message is popping up
>> there as follows:
>> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
>> found in the UserDefinedFunctions collection. If the name is a qualified
>> name, use [] to separate various parts of the name, and try again."
>> I can't change the name or delete the function because I can't see it in
>> the database. Is there a way to repair this?
>> Bill
>>
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46576681$0$4901$4c368faf@.roadrunner.com...
>> Mike,
>> I tried this for a stored procedure and it did save it to my database.
>> But I can't get it to work for a user defined function. In Query
>> Analyzer the user defined function parses with no errors, but it won't
>> create in the database. Any thoughts?
>> Bill
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to
>> save it. This query is the CREATE syntax for creating the object. When
>> you execute that it is being saved. To modify it you would then ALTER
>> it. If you go to books online and search for CREATE PROCEDURE you will
>> see more on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored
>> procedure, for example, with CREATE PROCEDURE myProcname etc. and you
>> execute that query you have just created (or saved sort of) that
>> myProcname Stored Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>I can create stored procedures and user defined functions in the Query
>>Analyzer, but the only option I see for saving are to save to an
>>external sql file. Is there a way to save directly to the database
>>while in Query Analyzer?
>> Bill
>>
>>
>>
>>
>|||Glad to help have a great weekend.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46578928$0$15089$4c368faf@.roadrunner.com...
>I did the refresh on User Defined Functions and the function is now in the
>list. Before, I was getting the error message when clicking on Permissions
>for the dbo user who owns the function. This went away after the refresh.
> Thanks to all for your help.
> Bill
>
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Have you refreshed the list of User Defined Functions as Kalen suggested?
>> If it is showing up as an object that you can assign permissions to it is
>> in your database.
>> When are you getting that error? When trying to assign permissions? Try
>> and close Enterprise Manager and reopen it.
>> Can you call the function and use it?
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46577035$0$4642$4c368faf@.roadrunner.com...
>> Mike,
>> It appears the function did save into the database, although it's not
>> showing up under User Defined Functions. It is showing up when I view
>> permissions under Users for user dbo, and an error message is popping up
>> there as follows:
>> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
>> found in the UserDefinedFunctions collection. If the name is a
>> qualified name, use [] to separate various parts of the name, and try
>> again."
>> I can't change the name or delete the function because I can't see it in
>> the database. Is there a way to repair this?
>> Bill
>>
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46576681$0$4901$4c368faf@.roadrunner.com...
>> Mike,
>> I tried this for a stored procedure and it did save it to my database.
>> But I can't get it to work for a user defined function. In Query
>> Analyzer the user defined function parses with no errors, but it won't
>> create in the database. Any thoughts?
>> Bill
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>> To clarify. Not "by default" you have to actually execute a query to
>> save it. This query is the CREATE syntax for creating the object. When
>> you execute that it is being saved. To modify it you would then ALTER
>> it. If you go to books online and search for CREATE PROCEDURE you will
>> see more on that.
>> HTH
>> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
>> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> They are normally saved by default. If you start your stored
>> procedure, for example, with CREATE PROCEDURE myProcname etc. and you
>> execute that query you have just created (or saved sort of) that
>> myProcname Stored Procedure into your database.
>> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
>> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>>>I can create stored procedures and user defined functions in the
>>>Query Analyzer, but the only option I see for saving are to save to
>>>an external sql file. Is there a way to save directly to the
>>>database while in Query Analyzer?
>>>
>>> Bill
>>>
>>
>>
>>
>>
>>
>sql

saving a collection of objects to a table

Hello all,
Is there a more efficient way to save a collection of custom objects to a sql server database, other then calling a stored procedure in a loop for each object in the collection?
Any suggestions on how to make this as efficient as possible are welcome.What is the nature of the objects?

If the objects can be serialized to XML then storing the collection as an XML string should work well.|||Actually this is the funny part,
The collection is a result of an xml parsing function. Due to the cost of xml parsing i'm saving
the parse result to a database.
Maybe you could advise on general program design here. I'm very new to the xml thing
and what i need to do is to make a details page of sorts ( which in current form ) at runtime
accesses and parses up to 8 xml files from another server on the network. There is significant
delay between details link click and the actual details page coming up due to the processing taking place behind the scenes, hence my decision to parse once and respond to the subsequent calls with results from the database. The processing involves a number of loops since it is the only logical way i can think of to parse the xml. The only way to parse the xml doc is to
basically loop thourgh it and get what you want am i correct?
Maybe you have and idea for a better quicker design?
thank you for response.|||XML is a good means for exchanging data but as you've seen not necessarily a great means for storing data for quick retrieval. It sounds like parsing once and storing the relevant results into a database for easier retrieval is a good strategy. I've seen sites use this approach for the same reasons you've encountered. (One company's engineers had to "hide" this fact because there was a design dictum to use XML for everything.)

I'm not an expert in XML parsing and ways to improve parsing performance. There's probably a .Net forum for that. I've always tried to keep my XML as straightforward as possible and use it only when a better/faster alternative isn't available.|||ok thanks. I'll just keep plucking along the current path.|||why not parse once and then cache the collection in memory?

this will give even more benefits than accessing a database.|||That would be fine except we're talking lots and lots of items here (more then 15,000) with
up to 7 xml files for each. Do you think it'd still work or would
the server crap out?

save upload image to db using stored procedure problem

I am trying to save an uploaded image and its associated info to sql server database using a stored procedure but keep getting trouble. When trying to save, the RowAffected always return -1. but when i debug it, I dont' see problem both from stored procedure server
explore and codebehind. it looks to me every input param contains correct value(such as the uploaded image file name, contentType and etc). well, for the imgbin its input param value returns something like "byte[] imgbin={Length=516}". Below is my code, could anyone help to point out what did I do wrong?
Thank you.

================================================
CREATE PROCEDURE [dbo].[sp_SaveInfo]
(
@.UserID varchar(12),
@.Image_FileName nvarchar(50),
@.Image_ContentType nvarchar(50),
@.Image_ImageData image,
@.Create_DateTime datetime)

AS
set nocount on

insert ExpertImage(UserID, Image_FileName, Image_ContentType, Image_ImageData, Image_ReceiveDateTime)
values(@.UserID, @.Image_FileName, @.Image_ContentType, @.Image_ImageData, @.Create_DateTime)
GO

private void Submit1_ServerClick(object sender, System.EventArgs e)
{
if(Page.IsValid)
{
Stream imgStream = File1.PostedFile.InputStream;
int imgLen=File1.PostedFile.ContentLength;
string imgContentType = File1.PostedFile.ContentType;
string imgName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\\") + 1);
byte[] imgBinaryData = new byte[imgLen];
int n=imgStream.Read(imgBinaryData, 0, imgLen);
int RowsAffected = SaveInfo(imgName,imgBinaryData, imgContentType);
if(RowsAffected > 0)
{
..
}
else
{
..
}
}
}

public int SaveInfo(string imgName, byte[] imgbin, string imgcontenttype)
{

SqlConnection objConn = new DSConnection().DbConn;
SqlCommand objCMD = new SqlCommand("sp_SaveInfo", objConn);
objCMD.CommandType = CommandType.StoredProcedure;

objCMD.Parameters.Add("@.UserID", SqlDbType.VarChar, 12);
objCMD.Parameters["@.UserID"].Value = txtMemberID.Text.ToString();
objCMD.Parameters["@.UserID"].Direction = ParameterDirection.Input;

objCMD.Parameters.Add("@.Create_DateTime", SqlDbType.DateTime);
objCMD.Parameters["@.Create_DateTime"].Value = DateTime.Now.ToLongTimeString();
objCMD.Parameters["@.Create_DateTime"].Direction = ParameterDirection.Input;

objCMD.Parameters.Add("@.Image_FileName", SqlDbType.NVarChar, 50);
objCMD.Parameters["@.Image_FileName"].Value = imgName;
objCMD.Parameters["@.Image_FileName"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@.Image_ContentType", SqlDbType.NVarChar, 50);
objCMD.Parameters["@.Image_ContentType"].Value = imgcontenttype;
objCMD.Parameters["@.Image_ContentType"].Direction = ParameterDirection.Input;

objCMD.Parameters.Add("@.Image_ImageData", SqlDbType.Image);
objCMD.Parameters["@.Image_ImageData"].Value = imgbin;
objCMD.Parameters["@.Image_ImageData"].Direction = ParameterDirection.Input;

int numRowsAffected = objCMD.ExecuteNonQuery();
return numRowsAffected;

}Are you saying you don't get an exception and there row isn't inserted? BTW Don't prefix your procs with "sp_" its bad for perf' they're really reserved for "system procedure".

Save system stored procedure results?

Is there a way to save the results of a system stored procedure into a
table? Example - I want to run xp_fixeddrives and send the results to a
temp table. Is this possible?
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Yes, here's some quick code:
CREATE TABLE #FixedDrivesOutput
(
Drive char(1),
MBFree int
)
GO
INSERT INTO #FixedDrivesOutput (Drive, MBFree)
EXEC master.dbo.xp_fixeddrives
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:Onzn0A5YEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hey -
How are you running the SP, ie.. from Query Analyzer, from isql ?
If from Query Analyzer you can save .CSV file or any other type of file and
then load it into a table.
If from isql, redirect the output to a file and then load it into a table.
HTH
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:Onzn0A5YEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Rachel
try do to something like this
-- Create a table with the same structure that the procedure returns
Create table tempdb.dbo.sphelpdb( name_1 varchar(255), db_size varchar(255), owner varchar(255), db_id int ,created varchar(255), status varchar(255))
-- Insert into the table the results got using a EXEC and procedure with parameters
Insert tempdb.dbo.sphelpdb(name_1, db_size, owner, db_id, created, status)
EXEC('sp_helpdb')
-- Voila! the table contains, the procedure results.
Select * from tempdb.dbo.sphelpdb
"Rachael Faber" wrote:
> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Save system stored procedure results?

Is there a way to save the results of a system stored procedure into a
table? Example - I want to run xp_fixeddrives and send the results to a
temp table. Is this possible?
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Yes, here's some quick code:
CREATE TABLE #FixedDrivesOutput
(
Drive char(1),
MBFree int
)
GO
INSERT INTO #FixedDrivesOutput (Drive, MBFree)
EXEC master.dbo.xp_fixeddrives
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:Onzn0A5YEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hey -
How are you running the SP, ie.. from Query Analyzer, from isql ?
If from Query Analyzer you can save .CSV file or any other type of file and
then load it into a table.
If from isql, redirect the output to a file and then load it into a table.
HTH
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:Onzn0A5YEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Rachel
try do to something like this
-- Create a table with the same structure that the procedure returns
Create table tempdb.dbo.sphelpdb( name_1 varchar(255), db_size varchar(255),
owner varchar(255), db_id int ,created varchar(255), status varchar(255))
-- Insert into the table the results got using a EXEC and procedure with par
ameters
Insert tempdb.dbo.sphelpdb(name_1, db_size, owner, db_id, created, status)
EXEC('sp_helpdb')
-- Voila! the table contains, the procedure results.
Select * from tempdb.dbo.sphelpdb
"Rachael Faber" wrote:

> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Thanks for the suggestions. That's exactly what I needed!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Save system stored procedure results?

Is there a way to save the results of a system stored procedure into a
table? Example - I want to run xp_fixeddrives and send the results to a
temp table. Is this possible?
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Yes, here's some quick code:
CREATE TABLE #FixedDrivesOutput
(
Drive char(1),
MBFree int
)
GO
INSERT INTO #FixedDrivesOutput (Drive, MBFree)
EXEC master.dbo.xp_fixeddrives
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:Onzn0A5YEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hey -
How are you running the SP, ie.. from Query Analyzer, from isql ?
If from Query Analyzer you can save .CSV file or any other type of file and
then load it into a table.
If from isql, redirect the output to a file and then load it into a table.
HTH
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:Onzn0A5YEHA.2816@.TK2MSFTNGP11.phx.gbl...
> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Rachel
try do to something like this
-- Create a table with the same structure that the procedure returns
Create table tempdb.dbo.sphelpdb( name_1 varchar(255), db_size varchar(255), owner varchar(255), db_id int ,created varchar(255), status varchar(255))
-- Insert into the table the results got using a EXEC and procedure with parameters
Insert tempdb.dbo.sphelpdb(name_1, db_size, owner, db_id, created, status)
EXEC('sp_helpdb')
-- Voila! the table contains, the procedure results.
Select * from tempdb.dbo.sphelpdb
"Rachael Faber" wrote:

> Is there a way to save the results of a system stored procedure into a
> table? Example - I want to run xp_fixeddrives and send the results to a
> temp table. Is this possible?
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Thanks for the suggestions. That's exactly what I needed!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Save some Stored proc in file and create SP from file

Every day we are restoring prod DB in Development env. I need to save before restore users stored proc,

restore DB and after create SP from file.

Thanks.

use

sqlcmd or osql

|||The command-line tools may not be adequate because the defaults do not suffice if your definitions are larger than say 8000 characters. And it will be a pain to automate the scripting of multiple objects and so on. Best is to use SQL-DMO/SMO or DTS/SSIS tasks for this.

save boolean values in a sql2000 table

I try to insert VB.NET boolean true values into a SQL2000 table thru a stored procedure. While varchar fields of the table are correctly filled, I'm surprised to find the bit fields all stay 0 after every single insert. This makes me wonder if VB.NET boolean variables can be passed to sp bit parameters and inserted into a SQL2000 table. Or did I miss something? Please advise. Thanks.Oop! I found a bug in the GUI that captures the input. It's fixed and now works fine. Thanks.