I am looking to implement a system whereby a given user can 'authorise' changes to the system itself and promote from test to live. For instance a user might be running an application which has a SQL 2005 database DataA opena nd runnign stored procedures from that. I'd like a Stored procedure in that able to copy over a given stored procedure from a different database - DataB and copy into DataA. Is that possible? Also is it possible from T-SQL to copy files on the servers underlying filesystem? Ie to copy some .aspx files from one location to another. Regards Clive
I frequently use the "Generate Script" wizard to create a folder of .sql files (scripts) that I use to then transfer to my colleagues.
The question is, I don't see how to reverse the procedure, that is, to import the files with a bulk procedure, rather than copying them one at a time. What's the best way to do this?
Hi, I know that SQL Server (MSDE), databases run much faster when the data and log files are on seperate physical devices. I can do this explictly when I write a CREATE DATABASE SQL script, but would like to change the MODEL database so that all new databases use the sperate data/log file assignments by default. I haven't found a way to alter the sysfiles table in model - I can read the data, but it balks at trying to update it. (recordset is not updateable, or adhoc querries not allowed against a system database).
I didn't see any options on the MSDE install that implements seperate data/log paths.
I don't have the SQL Server tools (like Enterprise Manager) available, but I do have VS.NET 03 (Standard). And Web Data Administrator.
I've a doubt regarding deploying and creating package (MSI and EXE files) for SQL Stored procedures,views,tables,functions,triggers etc.,just like ASP.NET application by publishing and deploying..Is it possible in the same way for sql objects.If so,pls help me how to do it?
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql. I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
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!!
All, I have beating my head over this and have asked questions to clear this up in small bits.
In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:
--Normal ASP header <% myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'
'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable Set SysMainQuery=Server.CreateObject("ADODB.Recordset" ) SysMainQuery.open "Select * from tbl_Date", strDSNPath
howmanyfields=SysMainQuery.fields.count -1
response.write "<table border='1'><tr>"
'Put Headings On The Table of Field Names FOR i=0 to howmanyfields response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>" NEXT response.write "</tr>"
' Now loop through the data DO WHILE NOT SysMainQuery.eof response.write "<tr>" FOR i = 0 to howmanyfields fieldvalue=SysMainQuery(i) If isnull(fieldvalue) THEN fieldvalue="n/a" END IF If trim(fieldvalue)="" THEN fieldvalue=" " END IF response.write "<td valign='top' NOWRAP><Font Size=2>" response.write fieldvalue response.write "</td>" next response.write "</tr>" SysMainQuery.movenext 'howmanyrecs=howmanyrecs+1 LOOP response.write "</table></font><p>"
' close, destroy SysMainQuery.close set SysMainQuery=nothing %> </body></html>
The stored procedure takes the input an returns the sql statement:
Alter Procedure MainSysLogQuery @myDates nvarchar(200) = '', As declare @oSql nvarchar(4000) declare @viewtmp nvarchar(3000) declare @querytmp nvarchar(3000) declare @wheretmp nvarchar(3000) declare @ordertmp nvarchar(3000) declare @tmplen int
Set @wheretmp = '' Set @tmplen = 0
if @myDates != '' Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''
If len(@wheretmp)>0 Set @wheretmp = ' WHERE ' + @wheretmp
print @wheretmp Set @viewtmp = 'SELECT *' Set @querytmp = ' FROM tbl_All_SysData' Set @ordertmp = ' ORDER BY LongDate DESC' Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp PRINT @oSQL Exec(@oSQL) Go
The problem is I keep getting: Error Type: ADODB.Recordset (0x800A0E78) Operation is not allowed when the object is closed.
It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure.... The Errors are on on the recordset.eof, recordset.movenext, etc....
How would you set up a group of developers-application programers in SQL 6.5 to let them have authority so that they all can store, update, delete, & execute each others stored procedures, within a particular database. They are not permitted to modify the table structures within a data base, but I can not seem to let them have authority so that they can work on and execute any of their sp's unless the DBO actually does the sp modifications? They do not want to modify any code by putting the sp owners name in front of the sp name (I don't blame them), otherwise Error 2812 results.
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.
I am developing a complex database-driven application with SQL Server 2000. My database has dozens of stored procedures, and whenever I want to rename a database field, I have to go through my stored procedures, finding where that field is used. This is a laborious and error-prone process, even when I look up depenencies.
Is there an easier way to work stored procedure code – some tool to search/replace the text perhaps?
Hello, can anyone offer any advice on this problem related to store procedures.
The following 2 chunks of SQL illustrate the problem
--1 declare @lsFilt varchar(16) select @lsFilt = 'fil%ter' select * from sysobjects where name like @lsFilt
--2 declare @lsQuery varchar(128) select @lsQuery = 'select * from sysobjects where name like ''fil%ter''' exec (@lsQuery)
When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.
Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of
create proc SP2Style @psFilter varchar(16) AS declare @lsQuery varchar(128) select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + '''' exec (@lsQuery) GO
instead of
create proc SP1Style @psFilter varchar(16) AS select * from sysobjects where name like @psFilter GO
Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?
i am one of the user of database and in that user i created store procedures.
now i want to create another user and give that user to permission of excute all my proceudres and i also give him privillages such a way that user can also modify my procedures...
I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help
Can any one please tell me where i am going wrong..
Code Snippet create proc SP_PercentageRMU as SELECT cast (sum([Usage Qty]) as [decimal] (28,8))as 'TUsageQty' ,RAW_MATERIAL into #TZMelt_Pound FROM [LatrobeOCT].[dbo].[ZMelt_Pound]
group by RAW_MATERIAL GO select [Usage Qty],(case when r.raw_material = z.raw_material then cast ((r.[usage QTY] / z.TUsageQty) as decimal (28,8)) else 0 end) as '%UsageQty' ,r.[PRODL]
,r.RAW_MATERIAL ,r.[GRADECODE] into #PZMelt_Pound FROM [LatrobeOCT].[dbo].[ZMelt_Pound]r inner join #TZMelt_Pound z on r.raw_material = z.raw_material
Hi All, I want to store files (any kind of file) in DB and retrieve them.What is the best method to do it and also please post sample code as I have never done that. Thanks, Kumar.
I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.
Has anyone come across doing this and how they did it?
I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.
Here's the DMV I am planning to use:
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME] ,cached_time ,last_execution_time ,execution_count
[Code] ....
I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?
Hi all i need little help regarding stored procedure. i have some job say ".one." that runs daily on sql server agent But i have some holiday schedule as well in database suppose if a holiday come and i want to stop that job on sql server agent on that particular holiday. can anybody help me writing it .. how to do it by calling SQL server agent to stop and start with date in stored procedure. Regards
In a report called ICD_PrivateHospital, I have designed to show output to two table from two store procedures. 1st store proc: usp_RPT_Private and 2nd store proc: usp_RPT_Private2.
I have created both the store procs and it is executing successfully. I also have designed the Crystal Report in Visual Studio 2008.
I have added both the store procs in the crystal report with no error. I only wants to show top 20 records. It is running successfully when I add the fields and parameters from the first store procedure. But when I add the 2nd store procedures fields into the report, duplication occurs for both the results in store proc 1 and store proc 2. How to solve this issue?
I have several developers working on different systems but same database; these developers make Store Procedures and all utilize these SP€™s; at the moment our SP list grows and keeps about 200 + SP's, whereas I expect a lot more to come.
Now I want to organize the SP''s under certain folders; example by the System and underneath by User name so that traceability of the work can be easy as well as organization of the SP's will be achieved.
I cannot find any way how to customize the Store Procedure node; and create folder underneath.
I know the filter option helps for searching the SP's but we also have remote users who are working somewhere away from us; so I want to get their work only by filtering the folder of these user name and also sometimes I need to know who made these SP's so that that developer can fix or alter the procedures.
We may do this using security instead of dbo, the developer who is creating the procedure will carry his name; but this needs tight security and we are in the phase of testing and lot of changes occurs on daily basis which requires little loose security till we pass this phase.
Any Idea or suggestions to make it viewable and accessible and copy the work of the specific users right away; rather than searching his Sp's, with the coordination of the user or with the maintained documentation.
Has anyone used sql 7 to store image data? Is it easy to load .tiff files into a sql database using the image datatype? If anyone knows how to do this, please reply.
How does one pass into a Stored Procedure an array of numbers to be used in an IN clause? If I pass "1,2" in a VARCHAR, the stored procedure sees only the first number (1 in this case). I'm using VB and ADO.NET, but I don't know how to set up the stored procedure for an array. Is there a parsing function to do this? CREATE PROCEDURE TestInClause( @TeamList VARCHAR)ASSELECT Name FROM Teams WHERE TeamID IN (@TeamList); /* sees only 1st number */GO