Folder Underneath Programmability ==&&> Store Procedures
Aug 15, 2007
Hello there
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.
Due to "security considerations", the company I am currently working with does not want me deploying custom assemblies into Microsoft SQL ServerMSSQL.3Reporting ServicesReportServerin. They have created a subdirectory:
Microsoft SQL ServerMSSQL.3Reporting ServicesReportServerinmySubDirectoryName
Unfortunately, I can't get my report to see the dll when it's in the subdirectory. Is there any way to do this?
I am workin on a website for me and my friend. I want to host it at a hosting company. In asp.net 2.0 projects are build with the App_data folder, which can stores data source such as a SQL Server 2005 database. But when i want to host the site at a hosting company, will the database goed into this folder?
As SQL Server 2016 category is not created so I am posting this query in SQL Server 2014 window.
I have enabled Query Store in SQL Server 2016 using ALTER DATABASE and later tried with Properties of Database too. But I am not able to see the Query Store subfolder under database.
Hi, all here, I am new to data mining programmability. And as we know with the data mining programmability, we can integrate data mining with client applications via a series of data mining programmability APIs like AMO.NET, ADOMD.NET,SERVER ADOMD.NET, OLEDB, ADO,ADO.NET and so on. So what is the environment for the data mining programmability then? I mean what kind of IDE can we develop the data mining programmabiliy? Thanks a lot for any guidance and help.
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
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.
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
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?
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
How does MicroSoft store the stored procedures in seperate files.What tools are used.Cause that's what I like to do too.Arno de Jong,The Netherlands.(SCPTFXR does not have the option to store the stored procedures indifferent filesI think)
I am able to use user data types for creating local temporally tables in store procedures, but I receive an error at run-time about the user data type.
Msg 2715, Level 16, State 7, Procedure SP_SAMPLE_TEST, Line 4 Column, parameter, or variable #1: Cannot find data type D_ORDER_NUMBER.
The same user data type is used as parameters in several other store procedures, and they work perfect. It is also used in several table definitions.
What am I doing wrong?
Thanks in advance to any one who can help me with this problem.
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message "cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again." After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message. So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
I have a SharePoint (2007) Document Library site called B. Web Client is enabled on the server and B is mapped as a Drive (let's call it Y for this discussion)
I want to move documents in A to B. Easy enough, right? Not so....
I first started by creating a batch file that issues a COPY \A \Y /Y at the command prompt. Viola! Worked Great!
I then moved that command to a SQL Agent job as a CMDExec statement (exact same statement) and attempted to run it.....CRASH! It found the files in A but then said "The system cannot find the path specified"
Ok, so I tried it in SSIS. CRASH! Checked the error log. Same thing...
So I then checked the account under which the SQl Agent was running (special domain account for all our SQL Servers). Thinking it might mater I changed it to run under my name (I'm in Domain admin). I also ensured I had permissions to the SPS 2007 library as well. (I did).
Ran again! CRASH! Same error....
So, I created a batch file , placed thec ommand in the batch file and ran that from the command prompt! Viola! Worked Great.
So, I was thinking of how ingenious I was as I pasted my C:RootCopy.bat into my SQL Agent job. With a big grin on my face I right clicked and picked "Start Job at step".......CRASH! Same error.
Does anyone have any ideas on this ???????????????
