Sql Server 2005: Cannot Add Stored Procedure To Database On Server

Apr 16, 2008

Hello,

 I have been working with a c# project on my PC and was happy with it.  I added a stored procedure to the database on my machine and used it to run a Crystal report. It works really well.  I then logged onto the server and went into the production database and attempted to add the new stored procedure, but it did not behave the same way.I am not able to save it.  Does this have something to do with permissions?  Can somebody point me in the right direction?

Thank you

View 3 Replies


ADVERTISEMENT

How Many Stored Procedure Can I Add To A Database In SQL SERVER 2005 Express Edition?

Feb 17, 2006

How many Stored Procedure can I add to a database  in SQL SERVER 2005 Express edition?
I remember that only 32 Stored Procedure can be added to a database in SQL SERVER 2005 Express edition,but now I can add over 32 Stored Procedure to a database from SQL Server Management Studio Express CTP! How many Stored Procedure can I add to a database  in SQL SERVER 2005 Express edition?

View 1 Replies View Related

SQL Server 2005 Stored Procedure Is Very Slow Vs. SQL Server 2000

Apr 18, 2008

Hi there,

I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.

I could pin the problem down to this query:


DECLARE @Package_ID bigint

DECLARE @Email varchar(80)

DECLARE @Customer_ID bigint

DECLARE @Payment_Type tinyint

DECLARE @Payment_Status tinyint

DECLARE @Booking_Type tinyint

SELECT @Package_ID = NULL

SELECT @Email = NULL

SELECT @Customer_ID = NULL

SELECT @Payment_Type = NULL

SELECT @Payment_Status = NULL

SELECT @Booking_Type = NULL

CREATE TABLE #TempTable(

PACKAGE_ID bigint,

PRIMARY KEY (PACKAGE_ID))

INSERT INTO

#TempTable

SELECT

PACKAGE.PACKAGE_ID

FROM

PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID

LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID

LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID

WHERE

PACKAGE.PACKAGE_ID = ISNULL(@Package_ID,PACKAGE.PACKAGE_ID)

AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)

AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)

AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)

AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)

-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented

--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'

GROUP BY

PACKAGE.PACKAGE_ID

DROP TABLE #TempTable


The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment.
As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second.
I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key.
Maybe you folks can spot the error / mistake / wrong type of doing things easily.
I would appreciate any help you can offer me to solve this problem.

On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:





942659 (http://support.microsoft.com/kb/942659/)
FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000

Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.

Thanks again for any help

Best regards,
Pascal

View 9 Replies View Related

Stored Procedure From SQL Server 2000 To SQL Server 2005

Oct 31, 2007



I had few stored procedures which were working in SQL server 2000. I upgraded SQL server to 2005 and one stored procedure does not work. It gives the Error Msg 102. "Syntax error near ',' "
I already tried set quoted identifiers ON & OFFAny help would be appriciated.

View 10 Replies View Related

Forwarding Variable Number Of Parameters From VB.2005 To Sql Server 2005 Stored Procedure

Jan 15, 2008

I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....

View 1 Replies View Related

About SQL SERVER 2005(Stored Procedure) && Dot Net 1.1

Aug 31, 2006

I create a new stored procedure in SQL SERVER 2005 .It it is stored in " C:Documents and SettingsmmmMy DocumentsSQL Server Management Studio ExpressProjects" as (some_name).sql .it takes this links by default . Then I exeecute it with this command

"exec InsertValueIntoTable ;" BUT when i want to execute with clicking the execute button in the then gives an error "Could not find stored procedure 'InsertValueIntoTable'."





View 3 Replies View Related

Writing Stored Procedure In .NET 1.1 And Using In SQL Server 2005

Jul 7, 2006

Hi,
I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0.
Please refer some good tutorial for it.
Regards,Imran Ghani

View 1 Replies View Related

Calling A .NET Dll From A SQL Server 2005 Stored Procedure

Sep 13, 2006

Setup:  I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states.  There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem:  I have not yet figured out how to call the dll from the stored procedure.  Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help. 

View 2 Replies View Related

Stored Procedure / Recursion / Sql Server 2005

Sep 22, 2006

Hello my friendsThis is my sql table structureFK = ID int, Empnaam varchar(200), PK = EmpID int With this table, where i insert values, employees can hire other employees. Now i want to see with a function in visual studio who hired who. I get stuck when a person hired more than 1 person....This is my stored procedure :set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ShowHierarchy] ( @Root int ) AS BEGIN SET NOCOUNT ON DECLARE @EmpID int, @EmpNaam varchar(30) SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root) PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root) WHILE @EmpID IS NOT NULL BEGIN EXEC dbo.ShowHierarchy @EmpID SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root AND EmpID > @EmpID) END END Thanks in advance!Grtz

View 8 Replies View Related

[sql Server 2005] - Debug A Stored Procedure

Nov 28, 2006

hello world,
how cani debug a stored procedure under sql server 2005?

View 2 Replies View Related

SQL Server 2005 Trigger Or Stored Procedure

Apr 10, 2008

I need to create either a trigger or stored procedure in SQL server 2005(hopefully someone can tell me).. Here is what I need to happen: I have a table with orders that are generated from a website. After the transaction is completed, I need have the record that was just created also copy to another table. There is a field called flag and the values in this field are either 1 or 2. Imediatly after the transaction occurs, I need the records where flag = 1 to copy to this other table. How would I go about doing this?

View 10 Replies View Related

Stored Procedure In Excel From Sql Server 2005

Jan 3, 2008

I need to run a SQL Server 2005 stored procedure by pressing a command button in excel 2003. Basically, I want stored procedures run from Excel to get the view/queries from sql.

Searched and tried many solutions here and in other web resources, read about opening the datasource, but I cannot find the exact solution for my problem.

Any help would be greatly appreciated,
Thanks

View 4 Replies View Related

How To Debug Stored Procedure In SQL Server 2005

Jan 18, 2007

Hi all,

I really need to know that

" How to debug stored procedure in SQL server 2005 ?"

please help me to solve my problem ?

regards

sujithf

View 23 Replies View Related

How Do I Create A Stored Procedure In SQL Server 2005 ?

Feb 8, 2008

Yes it looks like a stupid question
but when i right click stored procedures
and click new stored procedure, it gives me a
QRY analyzer style window and all i can do is save
the qry as a regular .qry file ?

View 4 Replies View Related

External Stored Procedure In SQL Server 2005(x64)

Aug 9, 2006

I have generated a DLL file in VC++ 2005 by a 'C' file. It works fine when I put in a 32bits machine(32bits Windows Server 2003 + 32 bits SQL Server 2005).

However, when I build it into 64 bits, it doesn't work in a 64 bits machine. I have checked by Dependenct Walker, the DLL generated is linked with KERNEL32.DLL / OPENDS60.DLL / MSVCR80D.DLL, all of these DLL files are on the 64 bits machines and linked correctly.

I used the command


sp_addextendedproc 'abc', 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnabc.dll'

to create a ext. stored procedure. When I run it, the error message shows that

Could not load the DLL C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnabc.dll, or one of the DLLs it references. Reason: 126(error not found).

I would like to ask what is cause of the problem? Do I need to use CLR instead?

Thank you very much!!~

View 2 Replies View Related

Creating And Saving A Stored Procedure In SQL Server 2005. Help !!!!!!!

Mar 4, 2007

I have in the past created stored procedures using SQL Server 2000. It was easy to do. Now I am using SQL Server 2005 and the whole process is different and confusing to me. I performed the following steps to create a stored  procedure:
1.) In SQL Server management studio, I wen to the folder named "Stored Procedures"2.) I right clicked on this folder and selected "New Stored Procedure..."3.) A generic sql server stored procedure is created for me.4.) I modify the stored procedure and now want to save it?
Now where do I go from here? How should I properly save this new stored procedure and where should I save it?I noticed that a generic name is assigned such as SQLQuery13.sql, but I want to name it something else.
I actually saved the new stored procedure but I can't see it listed under the "Stored Procedures" folder. I even tried doing a refresh.
 

View 6 Replies View Related

Steps For Debugging Stored Procedure In Sql Server 2005

Dec 20, 2007

hi friends,
     Anyone give give me the steps we have to follow for debussing a stored procedure in sql server 2005...
 

View 2 Replies View Related

Stored Procedure From SQL Server Reporting Services 2005

Dec 19, 2007

Hi,

I am working with one example on SQL Server 2005 Reporting Services. I have a stored procedure with two parameters. I want to call this stored procedure from SQL Server Reporting Services 2005.

CREATE PROCEDURE spEmp
(
@id int,
@name varchar(150)
)
AS
BEGIN
SELECT EmpId, EmpName, EmpSal FROM Emp WHERE EmpId = @id AND EmpName = @name
END

I want to pass

@id AND @name

parameters from reporting services form. Can you please give me link / article / steps to follow this in SQL SERVER REPORTING SERVICES 2005 ?

Thanks
Rajesh.

View 12 Replies View Related

How To Use Switch Statement In Stored Procedure In Sql Server 2005

Apr 11, 2008

Hi

It is possible to use switch statement in stored procedure.
if yes then please tell me a sample example.

View 7 Replies View Related

Transact SQL :: How To Run Stored Procedure In Another Database On Different Server

Jul 13, 2015

I linked MS SQL and MySQL and i would like to have insert trigger on MySQL to import certain fields every time when there's new entry.

View 5 Replies View Related

Dynamically Specify Server And Database In Stored Procedure

Feb 28, 2007

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?

EXAMPLE:

SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')????)


Thanks for any help

View 1 Replies View Related

SQL Server 2005 Managment Studio Newbie: Can't See Stored Procedure

Oct 3, 2006

I just created a stored procedure in SQL Server 2005 management studio.  I went to my db and expanded the programmability folder then right clicked the stored procedures folder and created a stored procedure.  I saved the procedure as sp_getDistance in the default folder SQL managment studio picked.  Now when I went back to that stored procedure folder I did not see my stored procedure sp_getDistance, all that was there was the system stored procedure folder.  Did I save the stored procedure in the wrong place, or what did I do wrong.  I can't find the procedure anywhere, its just sitting in my My DocumentsSQL Server Management StudioProjectssp_getDistance.sql folder. Thanks,Kyle Spitzer 

View 1 Replies View Related

Passing A List/array To An SQL Server Stored Procedure 2005

Aug 16, 2007

Hi, I m using sql 2005 as a back end in my application...
I am useing Store procedure..for my data in grid..
 
ALTER PROCEDURE [dbo].[ProductZoneSearct]
(
@Productid char(8),@Proname char(8),@radius int,@mode varchar(5) = 'M',@Zone nvarchar(1000),)
ASSET NOCOUNT ON;Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))
Insert #Product Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment ,Miles, UserID, Daily, PhoneFrom [tblproduct] Where city IN (@Zone)
Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment ,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone From #Product As L Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID  Order By DateAvailable
 
if i pass value in "where city in (@Zone)" and @Zone ='CA','AD','MH'  then it can not get any result..but if write where city in ('CA','AD','MH') then it give me perfact result..
I tried to below syntax also but in no any user Where city IN ('+@Zone+')
In short if i pass value through varibale (@Zone) then i cant get result...but if i put  direct value in query then only getting result..can anybody tell me what is problem ?
Please Hel[p me !!!
Thank you !!!

View 5 Replies View Related

Managed Code In SQL Server 2005 And Stored Procedure Name Qualification

Mar 11, 2008

All --
Please help.
I am using managed code in SQL Server 2005 and have a question about stored procedure name qualification.
With a VS.NET 2005 Pro Database project, when I use >Build, >DeploySolution, it works great but my stored procedures are named with qualification using my domainusername.ProcName convention, something like this...
XYZ_TECHKamoskiM.GetData
 ...when I want them to be named with the dbo.ProcName convention, something like this...
dbo.GetData
...and I cannot see where this can be changed.
Do you happen to know?
Please advise.
(BTW, I have tried going to this setting >Project, >Properties, >Database, >AssemblyOwner, and putting in the value "dbo" but that does not do what I want.)
(BTW, as a workaround, I have simply scripted the procedures, so that I can drop them and then add them back with the right names-- but, that is a bit tedious and wasted effort, IMHO.)
Thank you.
-- Mark Kamoski

View 1 Replies View Related

Creating A .NET Stored Procedure In Sql Server 2005 Express Edition

Jan 21, 2006

Could somebody tell me how do we create a .NET Stored Procedure in Sql Server 2005 Express Edition and deploy and debug it against the database from Visual Studio 2005 or Visual Web Developer?  Can some one also let me know which approach is faster among .NET stored procedure or T-SQL stored procedure?
Regards...
Shashi Kumar Nagulakonda.
 

View 1 Replies View Related

Create XML In ASP.NET 2.0 Then Use For Joined Table In SQL Server 2005 Stored Procedure

Jun 11, 2006

Here's my problem:I'm developing an ASP.NET 2.0 application that has a user select one or moreauto manufacturers from a listbox ("lstMakes"). Once they do this, anotherlistbox ("lstModels") should be filled with all matching models made by theselected manufacturers. If lstMakes was not multi-select, I'd have noproblem. But in this case it has to be multi-select. The database is SQLServer 2005 which does not accept arrays as parameters. I've been told thatI have to create an XML document that will act as a filtered Manufacturerstable that I can join to my Models table in my stored procedure. Problem isI don't have the foggiest idea how to do this. I've seen some examples thatjust leave me scratching my head so I was hoping someone could look at whatI'm trying to do and show me how to do this. Thanks!

View 2 Replies View Related

ADO - Cannot Access The Return Parameter Of A Stored Procedure On SQL Server 2005

Apr 18, 2007

Hello,



I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.



To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.



try{

pCmd.CreateInstance((__uuidof(Command)));

pCmd->ActiveConnection = m_pConnection;

pCmd->CommandType = adCmdStoredProc;

pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");



............................ code here ........................................



pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));



VARIANT returnVal;

returnVal.vt = VT_I2;

returnVal.intVal = NULL;

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));

//Get Return value by executing the command

//The return value should be the DB unique ID.



pCmd->Execute(NULL, NULL, adCmdStoredProc);

int uniqueId = returnVal.intVal;

//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);

//GetFieldValue(0,pRst,uniqueId);



printf("The DB unique ID is: %i",uniqueId);

return uniqueId;

}



Cheers,

Seth

View 1 Replies View Related

Trouble Accessing SQL Server 2005 Stored Procedure Parameters

Jun 14, 2007

I created a stored procedure (see snippet below) and the owner is "dbo".
I created a data connection (slcbathena.SLCPrint.AdamsK) using Windows authentication.
I added a new datasource to my application in VS 2005 which created a dataset (slcprintDataSet.xsd).
I opened up the dataset in Designer so I could get to the table adapter.
I selected the table adapter and went to the properties panel.
I changed the DeleteCommand as follows: CommandType = StoredProcedure; CommandText = usp_OpConDeleteDirectory. When I clicked on the Parameters Collection to pull up the Parameters Collection Editor, there was no parameters listed to edit even though there is one defined in the stored procedure. Why?

If I create the stored procedure as "AdamsK.usp_OpConDeleteDirectory", the parameters show up correctly in the Parameters Collection Editor. Is there a relationship between the owner name of the stored procedure and the data connection name? If so, how can I create a data connection that uses "dbo" instead of "AdamsK" so I can use the stored procedure under owner "dbo"?



Any help will be greatly appreciated!



Code SnippetCREATE PROCEDURE dbo.usp_OpConDeleteDirectory
(
@DirectoryID int
)
AS
SET NOCOUNT ON
DELETE FROM OpConDirectories WHERE (DirectoryID = @DirectoryID)

View 1 Replies View Related

SQL Server 2012 :: Restore Database By Stored Procedure

Jul 26, 2014

I need to create a procedure with Restore Database command in many applications in differente platforms. But I can't "use master". How do I do it?

View 1 Replies View Related

CRUD Stored Procedure Code/Scripts Generator For SQL SERVER 2005

Apr 19, 2007

I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.
Anyone have a solution?
Thanks in Advance.

View 4 Replies View Related

Execution Time Gap Between Simple Tsql And Stored Procedure In SQl Server 2005

Oct 16, 2007

Hi ,

I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.

can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.

View 1 Replies View Related

Database Output Problem For A SQL Server 2000 Stored Procedure

Oct 27, 2006

Hi,  I have a problem with "database output"  window in executing a select-sp in vs 2005 standard. The db is a sql server 2000 one.When I execute the sp within VS, the database output correctly displays the execution information:No rows affected.(1 row(s) returned)@RETURN_VALUE = 3 but I can't see the returned rows (3 rows are returned); I only see the column names and no data.Running [dbo].[spBkm_GetList] ( @IDUser = <DEFAULT>, [.........]).IDBkm UIBkm IDUser --------------------- -------------------------------------- ---------
No rows affected. If I run the same sp in Sql Server Manager Studio Express it correctly shows the data of the 3 rows returned.  The sp uses  EXEC sp_executesql @Sql, @ParamList, ...... for executing the sql statement and the last line of sp is  RETURN @@ROWCOUNTI've tried removing the "RETURN @@ROWCOUNT" with no success.  The problem affects only one sp, the others, which are absolutely similar, work properly . I don't know what is the problem...Any idea?Thanks in advance 

View 1 Replies View Related

SQL Server 2012 :: Mirror Database - Create Stored Procedure

Nov 5, 2014

I have a database which uses "Database Mirroring", and I need to write stored procedure and pull data from "Principal Server".

My Current Logic:

CREATE PROCEDURE abc123
as
BEGIN
IF Server01 = 'ONLINE'
BEGIN

[Code] .....

The problem I am facing is: Stored procedure is not created because "One of the server is not Online"...

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved