This question is for a SQL Server 2000 Installation.
I have created Windows Groups for three types of users Admins, Technical, and Standard users. Some of the users in all of the groups attach using Enterprise Manager.
The data base access is right on, also you cannot see logins under the individual databases on the server which is perfect; however, from the Server level, the Security folder is still visible. When using the Technical or Standard, you cannot change anything; however, I would prefer that the Security Folder not be visible. Also the same is true with these two groups for DTS Packages and Stored Procedures. I do not want them to see DTS packages, or Stored Procedures. What is the process for securing these two areas using Windows Authentication?
I have a DTS package that I wanted to call from a Coldfusion page. Going through some searches, I found this page which uses three seperate stored procedures to run a DTS packages.
All seems fine and dandy until nothing changes after running the package with coldfusion. I start dumping the cfstoredproc variable, and it turns out that the DTS fails every time it gets to the Excel file. Looking at the logs, the reason is that the JET Database Engine failed creating the file. This doesn't make sense, as the DTS runs fine from within Enterprise Manager.
Is there something in the connection between the stored procedure and the DTS that would cause a problem like this? Might calling the DTS from a stored procedure change the full path that the file looks at?
EDIT:
Here's the exact error I'm given from the DTS logs:
Code:
Step Error Source: Microsoft JET Database Engine Step Error Description:Failure creating file. Step Error code: 80004005 Step Error Help File: Step Error Help Context ID:5003436
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!!
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.
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?
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?
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?
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"?
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)?
If I deploy an SSIS packages in SQL Server (not in file system).
Where are they stored? Are they stored in a DB? if yes, which one?
Also, is it a best practise to deploy the SSIS package in SQL (I guess it should be because I can have a central place for deploying all package and have one place to load all the packages from).
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?
Hi, hoping someone may be able to shed some light on this.
I have a stored procedure that conditionally executes different DTS Packages. Everything seems to work, there's no errors at all, but the DTS Packages are never executed. Wondering if anyone has any ideas.
Here's the stored procedure....
CREATE PROCEDURE exportLists AS
DECLARE @script VARCHAR(8000) DECLARE @id INT DECLARE @max INT
select regionid,count(distinct homephone) as phone into #phone from tbl_template Where exportid is null group by regionid
select officeid,listpath into #office from tbl_office WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL) and listpath is not null and listpath <> ''
Select case when phone > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + convert(varchar(10),officeid) + '" ' Else '"resetTemplate" /A OfficeID="' + convert(varchar(10),officeid) + '" ' end as cmd ,Identity(int,1,1) as rowno ,phone into #temp from #office as O inner Join #phone as p on o.officeid=p.regionid
Set @max=@@rowcount set @id = 1
While @id <=@max Begin Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phone > 2500 then '/A ExportID="' + convert(varchar(10),(Select coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" ' else ' ' end , @id=@id+1 from #temp Where rowno = @id End
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)?
I am able to connect to Integration Services in MSSMS. However, when I try to expand and click on MSDB under Stored Packages, I get a Login Timeout expired error. I get this error both locally, and remotely. Error message suggested "does not allow remote connections", but I checked Surface Area Configuration, and remote connection is already enabled. Moreover, I get the same error even when connecting locally.
I've just installed sql 2005x64 and sp2 on a active/passive cluster on 2003. Everything is working fine except browsing the stored msdb packages in SSIS. Remote acces is enabled - as are named pipes. I've tried doing this on the local server as well. I was able to create a maintenance plan and run it with no problems.
We got an error during the install pertaining to SSIS, but hit ignore and it went on successfully.
It was - failed to install and configure assemblies .....MSMQTask.dll in the com+ catalog. Error -2147467259.
description: Error HRESULT E_FAIL has been returned from a call to a COM component.
The browsing error is below.
Thanks!
Sam
TITLE: Microsoft SQL Server Management Studio ------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.
Login timeout expired An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)
I'm running several SSIS-packages in stored procedure using dtexec. Actually, there is a software that is running the procedure, but that's not important here.
The problem: if some of the packages failes, the whole procedure does not fail and I don't know if all the packages are successfully completed.
Here is a sample list of ssis-packages from procedure:
Hi, newbie to sql2005. I am trying to write a VB.NET program to list all ssis packages we have stored in a file folder (not in MSDB) where both SqlServer and ssis servers are located. These packages currently running on daily basis by automated jobs with no problem.
I have found the following link on the web that includes VB.Net program. If you scroll down under Example (SSIS Package Store) , you'll see a VB.NET program that I am trying to get it to work for me but keep getting error message like "Cannot find folder......"
My goal is to: 1. List all ssis packages stored in the file systems (.dstx) 2. List DB source and distination providers(tables in/out) in a ssis package 3. List properties for DB providers 4. List FlatFile & Excel file provider properties in a ssis package. My assumption, but not sure, is that I may need to register this file folder (where packages are located) to the SqlServer if that is not done already. Any command or sys table in Sql2005 to figure this out?
I would appreciate any help on this in advance. Thank You.
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'
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
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)
Hello every one, I m working in aspx 2.0 with sql server 2005, please tell me how can I create Stored Procedures for two or more tables not a single table(select,insert,update,delete please send me the queries which can help me in easy way I will very thankful to you Thank you
I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better. So i am wondering what are these used for:DECLARE vs just entering "@param1 varchar(30)"When i use "DECLARE @rc int" i get the error "Incorrect syntax near DECLARE"How to return values to ASP page in Visual Studio 2005 How to use @@rowcount - doesn't seem to work for me?i tried using DECLARE @rc intSET @rc = @@rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?
