Cannot Creat Stored Procedure On Existing Database
May 15, 2008
Hi, I already got a database and I need to add another stored procedure on it in order to call it from the SSIS package.
But when I execute the sql file to create the procedure, there is a error message popping out like:
"Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'logit'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists."
And I have tried it on another computer, there is the same error. Could you please tell me how to solve the problem. Thanks very very much.
I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts? Thanks in advance, N
Hi all, I have an Existing Stored Procedure on the database, and I want to modify it. However, when I changed it and saved it with the same name, the value,say 20 here I changed was not upgraded to the new value (I wanna it be 30). so anyone know how to solve that? Thanks in advance.
ALTER PROCEDURE [dbo].[spr_getCompanyId]( @companyname as varchar(20), @companykeyword as varchar(20) //i want to change 20 to 30 )
I have a stored procedure I created in SQL Server 2000 enterprise manager which I would like to modify in SQL Server 2005 Express Management Studio. When I right click on the stored proc and select "Modify", the code opens - however any attempt to save creates a local .SQL file. How can I save these changes to the stored procedure on the server?
I have a DataBase name Customer Customer have 30 Tables name table1 �table2�table 3... I want to have a DataBase name Customer_Offile with same Table same column like Customer ( the Customer_Offlike will used to a backup DataBase) How to wirte a script for copy Customer? thank you very much
I am new to ASP.NET and met a problem about access a database on a Web Server (www.myserver.com). My meaning here that I created a .sql from my localhost to test, but when to upload to my web server on the internet, an error occured: "System.Data.SqlClient.SqlException: SQL Server does not exist or access denied". Anyone here can help me to solve my problem. Waiting for your reply soon. Thanks
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
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?
I am wondering if there is a way to create a procedure in a logging database that can identify the source database from which it is invoked. DB_NAME() of course returns the name of the database in which the stored procedure exists. I could pass the database name as a parameter to the proc, just wondering if there is another way.
This is SQL 2005, I did look into the sys.dm_exec views but nothing seems to have a dbid reflecting the calling context.
I've Installed VS2005 and MSSQL express with it. I used to work with mssql 2003 enterprise edition where it had "Enterprise Manager" from which I could create and modify my Databases.
How do I creat a new database with MSSQL express?
I've tried to do this via visual studio 2005 "server explorer". I"m entering the "Create new SQL Server Database" dialog, I select my server's name and my new database name and I recive an error message which says "An error has occurred while estabilishing a connection to the server" and it also says that
"When connecting to SQL server 2005, this failure may be caused by the fact that under the defult settings SQL sever does not allow remote connections".
How do configure my MSSQL express to allow the remote connection?
I have a stored procedure that is located in one database and I would like to have it execute against a different database. My problem is when I go to execute it, it is still executing against the database it is stored in. Is it possible to tell this stored procedure when it runs to execute its code against this second database?
This is what I have now, which isn't working:
Code Snippet
use [Database2] exec [Database1].[dbo].usp_SetupDatabaseUser
We're new to SQL Reporting Services and have a new SQL 2005 server.
I'm having a problem activating scubscriptions.
- "New Subscription" is greyed out in Server Management Studio.
- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;
"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"
Any suggestions?
Found the problem.
When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.
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, I have a set of databases. Each user had his own database. Every database had the same stored procedures. The is one "admin" database where content about these users is stored. When I want to update or change a stored procedure I have to update it in every database. Therefore I want to put the stored procedures in the "admin" database. From there I have to access the databases of the user. I want to send the database name as a parameter. How can I change de database which te procedure should access? If I use the "use" statement I complains about that I can't use that command in a stored procedure. I've tried this method too: SELECT * FROM @databasename.dbo.tblname but that's not accepted too. Does anybody know how to select another database in the stored procedure?
We are trying to develop some standards for SQL stored procedures and I am wandering what is the preferrable method to reference a table within a stored procedure for use within a Client Server / Web OLTP. One: SELECT Customer_Id, Customer_Name FROM CUSTOMER
Or TWO: SELECT Customer_Id, Customer_Name FROM [DATABASENAME].[dbo].[CUSTOMER]
Most of my background is in working with data warehousing which is constantly loading between a staging and main DB, so I am in the habit of using TWO, but is this the best method for an OLTP and why.
Hi all, I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement. Thank you for all the help you can give me. Regards, Eve
I have created a stored procedure in the master database that can be run against any database on the server. I call this procedure from a sql server agent job. In this job I specify the database I want the procedure to run against but it always seems to run against the master database (presumably because the procedure is in this database). I can't switch database inside the procedure so what can I do other than creating a copy of the procedure in every database?
hey guyz... i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"
i have included the stored procedure below. is the stored procedure correct? if it is.. how can i sovle this problem?
CREATE Procedure spRestoreDatabase @Path VARCHAR(100) AS Restore Database Test From Disk = @Path GO
Hi. I'm trying to write a stored procedure that will access another database on a different server. How can I set this up in a stored procedure? It is a SQL Server 2000 database and it will be using SQL server authentication. Thanks!
I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'
I am running Sql Server 2005, When I create a new stored procedure and try to save it, I'm prompted with the 'save file as dialog'. Is'nt the stored procedure suppose to be saved within the database?
SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id
INSERT INTO registration_form VALUES(@firstname,@lastname,@job_title,@manager,@cost_center,@address,@city,@stateabr,@zip,@email,@phone,@roommate,@arrival,@departure,@special_needs)
IF @@rowcount>0 SET @result=1 GO
This is the specific line where I'm trying to access a different database:
SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id
