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.
View 4 Replies
ADVERTISEMENT
Mar 11, 2008
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
View 6 Replies
View Related
Apr 16, 2008
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
)
View 5 Replies
View Related
Dec 2, 2005
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?
View 1 Replies
View Related
Aug 18, 2006
I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.
But how about if I want encrypt existing stored procedures?
Which command should I use ?
View 4 Replies
View Related
Dec 1, 2007
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
View 3 Replies
View Related
Nov 20, 2003
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
View 2 Replies
View Related
Jul 20, 2005
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?
View 1 Replies
View Related
Aug 13, 2007
Hi All,
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.
Please advice me how to do it.
Thanks in advance,
Roman
View 3 Replies
View Related
Mar 28, 2007
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?
Thanks in advance
View 9 Replies
View Related
May 13, 2008
Greetings:
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!
View 5 Replies
View Related
Aug 10, 2005
is it possible to insert record into second database from a stored procedure which is in first database?
View 1 Replies
View Related
Apr 2, 2008
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.
Thanks,
Mike
View 4 Replies
View Related
Dec 3, 2006
Hello all,
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?
Thanks in advance!
View 5 Replies
View Related
Feb 14, 2008
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
The end result still executes against Database1.
Thanks for any advice,
Flea#
View 9 Replies
View Related
Jun 3, 2006
Kindly provide me the creat table querry for MS sql
its urgent.
Thank you!
View 1 Replies
View Related
May 11, 2006
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.
View 3 Replies
View Related
Nov 1, 2007
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?
View 1 Replies
View Related
May 10, 2006
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?
View 3 Replies
View Related
Apr 29, 2007
What if I attempt to creat a (merge) publication while the production sql server is online and active?
From another point of view, is there any drawback if I try to create (merge) publication while the tables are being used by users?
Thanks
Ekrem Önsoy
View 5 Replies
View Related
Jul 16, 2004
Hi there :)
I was hoping someone could help me figure out why this doesn't work....
I have a class called ProductsDB with a method called AddProduct that looks like this:
public String AddProduct(int categoryID, int makeID, string name, double price, double saleprice, double offerprice, int homepage, string thumbpath, string imagepath, string description)
{
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("ProductsAdd", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
parameterCategoryID.Value = categoryID;
myCommand.Parameters.Add(parameterCategoryID);
SqlParameter parameterMakeID = new SqlParameter("@MakeID", SqlDbType.Int, 4);
parameterMakeID.Value = makeID;
myCommand.Parameters.Add(parameterMakeID);
SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
parameterName.Value = name;
myCommand.Parameters.Add(parameterName);
SqlParameter parameterPrice = new SqlParameter("@Price", SqlDbType.Money, 8);
parameterPrice.Value = price;
myCommand.Parameters.Add(parameterPrice);
SqlParameter parameterSalePrice = new SqlParameter("@SalePrice", SqlDbType.Money, 8);
parameterSalePrice.Value = saleprice;
myCommand.Parameters.Add(parameterSalePrice);
SqlParameter parameterOfferPrice = new SqlParameter("@OfferPrice", SqlDbType.Money, 8);
parameterOfferPrice.Value = offerprice;
myCommand.Parameters.Add(parameterOfferPrice);
SqlParameter parameterHomepage = new SqlParameter("@Homepage", SqlDbType.Bit, 1);
parameterHomepage.Value = homepage;
myCommand.Parameters.Add(parameterHomepage);
SqlParameter parameterThumbnail = new SqlParameter("@Thumbnail", SqlDbType.NVarChar, 50);
parameterThumbnail.Value = thumbpath;
myCommand.Parameters.Add(parameterThumbnail);
SqlParameter parameterImage = new SqlParameter("@Image", SqlDbType.NVarChar, 50);
parameterImage.Value = imagepath;
myCommand.Parameters.Add(parameterImage);
SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
parameterProductID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterProductID);
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
// Calculate the ProductID using Output Param from SPROC
int productId = (int)parameterProductID.Value;
return productId.ToString();
}
catch
{
return String.Empty;
}
}
And then in another cs file for my aspx page I have an event handler for an add button in a webform, which looks like this:
// Add New Product to ProductDB database
bikescene.Components.ProductsDB accountSystem = new bikescene.Components.ProductsDB();
String productId = accountSystem.AddProduct(Int32.Parse(DDLproductcategory.SelectedValue), Int32.Parse(DDLproductmanufacturer.SelectedValue), TBproductname.Text, Double.Parse(TBproductprice.Text), Double.Parse(TBproductsaleprice.Text), Double.Parse(TBproductofferprice.Text), Int32.Parse(DDLproducthomepage.SelectedValue), thumbnailpath, imagepath, TBproductdescription.Text);
And finally, here is my stored procedure:
CREATE Procedure ProductsAdd
(
@ProductID int,
@CategoryID int,
@MakeID int,
@Name nvarchar(50),
@Price money,
@SalePrice money,
@OfferPrice money,
@Homepage int,
@Thumbnail nvarchar(50),
@Image nvarchar(50),
@Description nvarchar(500)
)
AS
INSERT INTO Products
(
productID,
categoryID,
makeID,
name,
price,
saleprice,
offerprice,
homepage,
thumbpath,
imagepath,
description
)
VALUES
(
@ProductID,
@CategoryID,
@MakeID,
@Name,
@Price,
@SalePrice,
@OfferPrice,
@Homepage,
@Thumbnail,
@Image,
@Description
)
SELECT
@ProductID = @@Identity
GO
Basically, my images are uploaded to the server just fine but nothing is being added to the database + I don't get any error messages.
Any ideas?
Many thanks indeed :)
View 1 Replies
View Related
Apr 18, 2001
I am trying to write a stored procedure that has an inner join between two tables from two different databases on the same sql server.
Something like...
Select * FROM
DB1-table1 INNER JOIN DB2-table1
ON DB1-table1.ID = DB2-table1.ID
yada yada yada.......
Does anyone know how to do this or is it possible? If so, what database should I put the stored procedure in or does it matter?
View 2 Replies
View Related
Nov 3, 2000
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.
Thanks
Loren
View 1 Replies
View Related
Jul 28, 2004
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
View 3 Replies
View Related
Jul 20, 2007
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?
View 4 Replies
View Related
Jun 30, 2004
Hi all,
I have an urgent problem. Can a stored procedure create a connection or access data from another database?
Thanks in advace. :)
View 11 Replies
View Related
Mar 5, 2005
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
View 4 Replies
View Related
Feb 8, 2008
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!
View 3 Replies
View Related
Jan 21, 2004
Is it possible to use a stored procedure to script an entire database in MS SQL 2000?
View 8 Replies
View Related
Apr 20, 2004
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'
select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)
create database @dbname
on
( name = @dbname + 'data',
filename = 'F:MSSQLData' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:MSSQLData' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )
select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)
end
View 3 Replies
View Related
Feb 8, 2007
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?
View 2 Replies
View Related
Feb 27, 2007
How do I correctly query from a different database table in a stored procedure?
Here is the procedure:
CREATE PROCEDURE spInsertRegistration
@firstname varchar(25),
@lastname varchar(25),
@job_title varchar(50),
@manager varchar(50),
@cost_center char(3),
@address varchar(50),
@city varchar(50),
@state_id INT,
@zip char(5),
@email varchar(50),
@phone char(10),
@roommate varchar(50),
@arrival datetime,
@departure datetime,
@special_needs varchar(500),
@result INT OUTPUT
AS
DECLARE @stateabr VARCHAR(2)
SET @result=0
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
Thank you.
View 3 Replies
View Related
