Hi anyone know of a quick way of copying Stored procedures on SSMS ?
I just need to back a bunch of SPs up.
I don't need to export them just to save them as files, I can do it but its one at a time
and is really slow.
Is there an easy simple way to backup all my personal Stored Procedures.
In the stored procedures collection, my stored procedures start with "DEF".
I would like to have the stored procedures places as text files in a personal backup folder.
Is this possible?
Since sql server EM doesn`t allow you to restore a stored proc. from a dump tape, I`ve been testing bcp and the sp_helptext procedure to dump our stored procs. out to a file to provide a backup.
Small stored procs dump just fine but larger ones cause an occasional "wrap around" in the output file. The output file looks something like this:
CREATE PROCEDURE xyz AS
declare @table sysname
decla
re @count integer
So when I copy/paste from the output file to a new stored proc in EM and try to save it, I get a syntax error.
Does anyone know how to dump a stored proc to a file so that it doesn`t wrap?
I`d like to use bcp or some utility to backup all stored procs. to a flat file on a nightly basis.
Thanks RIchard
In SQL 7.0 is there a way to backup only Stored Procedures and schema?
This would be similar to the Oracle backup switches: (full=yes and rows=no) or (fromuser=usera touser=usera rows=no).
I have to retrieve all the stored procedures in a certain database via a aspx page. My problem is that I have basically no idea how to do it. I did manage to get all the data as XML and reconstruct the database (pain). All I need now (I hope) is the sprocs. I do not have any other access to the database. And I'm a novice with SQL Server. Any guidance is appreciated.
Steve
Hi,
Does anyone know how to export all stored procedures attached to a database (MS SQL Server) into a sql script. Please help.
Thanks
I have a need to export all of the stored procedures in a database to files on the server dirve. I know that this can be done through the management interface but I need a way to do it programatically. I need to have a script or stored proc that dumps all of the procedures to a defined location on disk. Does anyone know how this can be done?
Thanks!
I've never dealt with stored procedures much.. but i have a new database
created.. imported the tables, but the stored procedures didnt get copied
over..
Is there an easy way to export them.. perhaps to a .sql file ... then import
them or run a script on the other database..
I have never done much with the query window before, so i'm not sure how to
handle this.. as there are around 20 stored procedures that need imported..
Thanks for any tips...
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
I am trying to backup and restore a 20GB SQL database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues:
1) The developers [against best practice] have stored multiple images in fields within the database as binary code.
This therefore exceeds the 65532 character limit in some fields, so even though the images do show [based upon the data saved within this field], I cannot find the data in the field beyond this 65532 limit, within SQL Server.
How can I export / locate this data after the 65532 character limit?
2) When I have attempted to restore the database I am getting this error message:
Restore of database 'zapkam' failed. (Microsoft.SqlServer.Management.RelationalEngineTa sks)
Additional information:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (1:1592996) in database "zapkam" as read from the backup set. (Microsoft.SqlServer.SmoExtended)
I have managed to restore two other smaller databases using the same technique, but am wondering if it's an issue with the database itself.
3) I have uploaded this database to the new server using FileZilla FTP Client, but it has cut out, painfully at 80% + 90% on a couple of occasions.
Is there a better solution for uploading these big files that I could possibly use? For example, uploading table by table or similar...
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
Reading through the forums, I found some great imformation for importing/exporting an excel spreadsheet via a stored procedure, however, the amount of data I have won't fit in excel. Can someone help me export a CSV file via a stored procedure? I don't know if XML is the answer or if there is another way. I am able to use Bulk for an insert of a csv via stored procudure, although it doesn't allow me to use a variable for the file name and pass it in. We are currently using SQL Server 2000.
Thank you,
Stacy
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedGreetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
Hi all,
Please ignore my ignorance, but I was wondering if someone could help me out a bit?
I'm trying to export the results from a stored proc, to an csv file.
So for example, I want to run this query:
SELECT TOP 10 pkID FROM myTable ORDER BY pkID DESC
and I wanted it to be saved to:
C:spExport.csv
How would I go about doing that?
Hope you can help :-)
bwuk
I'm new to Integration Services and going through the learning challenges one often faces when employing unfamiliar technology. I've created a simple package that executes a parameterized stored procedure and returns the results (multiple rows) into an object variable. How do I export the results to an XLS file? Do I have to use a script task to do this?
Regards,
Orlanzo
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
Hi,
I am looking to create a job on our SQL Server 7.0 dB to:
1. Manipulate and move data from table 'a' to table 'b' (via stored proc)
2. Export table 'b' to a comma delimeted, double quote text identifier file (via dts export wizard stored as a DTS )
3. Cleanup table 'b' and end the job.
I am creating a DTS package to execute step 1, then 2 and lastly 3.
How do I execute another DTS package (#2) from within this DTS package?
Also, is there a way I can make the file name for #2 a dynamically generated name (i.e. include date and time in the file name)?
Thank You
Jamie Reis
I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.
I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.
Any ideas
Thanks
We just upgraded from SQL 2000 to 2005. Under 2000, I could export multiple stored procs to separate windows files.
Is there a way to do this under 2005 without exporting 1 proc at a time?
Goal: To run a stored procedure and export its results to Excel.
Challenge: The stored procedure produces multiple result sets. We want to programmatically export each result set into just 1 Excel file but into different Worksheets. Ex: result set 1 into Worksheet 1, result set 2 into Worksheet 2 and so on. Tools like
BCP utility and OPENROWSET are not enabled for use on the server. So those are out of the league for this one.After I run "EXEC [sp_select_ view_ columns_ from_pp]" -- Within the Results tab of Microsoft SQL Server Management Studio, I see:
viewName columnName columnDataType columnLength column
test test_id varchar 8 0
viewName columnName columnDataType columnLength column
test1 test1_id varchar 8 0
viewName columnName columnDataType columnLength column
test2 test2_id varchar 8 0
As you can see from above, that stored procedure sp_select_view_columns_from_pp is producing multiple result sets within the same Results tab in Microsoft SQL Server Management Studio. We want to make it so it exports to Excel into its own worksheets.
Code:
ALTER PROCEDURE [dbo].[sp_select_view_columns_from_pp]
AS
DECLARE @object_id INT;
DECLARE cur CURSOR FOR SELECT object_id
[code]....
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
hi
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
looking forward for a replygayathri
I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
Hello I have two stored procedures
@ID INT
AS
SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER
FROM (SELECT (SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM,
(SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X
and
@ID INT
AS
SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL(
(SELECT SUM(TRANSACTION_VALUE)
FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1
GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P
undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings
I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player
Thanks in advance for any light you can shine on this.
I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource?
And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"?
Now I have around 40 stored procedures, and quite many of them looks the same except the where-part...
(Iam just a beginner with SQL)