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 am trying to reference the following stored procedure. I was just wondering if it can be done. I get the following error when i try to save it. Error: The Column prefix 'CRMBackOffice.dbo' does not match with the table name or alias name used in the query
SELECT BRN_CustCode, BRN_Name1, BRN_Name2, BRN_Status FROM [VENUS_II].[dbo].[Branches] WHERE BRN_CustCode NOT IN ([CRMBackOffice].[dbo].[csp_GetCRMIIICustomerList])
I'm having problems installing a external stored procedure that I created in Visual C++. I've created the .ddl and put it in the correct directory.
I've run the following commands to install it sp_addextendedproc xp_regex, "D:garthExternalStoredProceduresTecasxp_regex.dll"
I then exec the procedure and get the following:
execute master..xp_regex
ODBC: Msg 0, Level 16, State 1 Cannot load the DLL \tedevsql02garthExternalStoredProceduresTecas, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
The .dll is definately in that directory. I'm not sure what I'm doing wrong. Any tips?
I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.
My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').
I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.
Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.
Example of stored procedure on external database:
/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/ CREATE PROCEDURE dbo.AddUser ( @Username Varchar(20), @Password Varchar(20) ) AS ( Select User_ID From UserList Where User_Username = @Username ) GO
Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!
Any help would be greatly appreciated as I am completely stuck.
I am trying to create a CLR based stored procedure in C#. When i tried printing simple "Hello" from it, it works fine. Now requirement is to run an exe file from it. For that i use process.start. But when i try to execute the procedure i get all the security execptions. Can someone please help. Following is the code snippet. -------------------------------------------------------------------------------------------------------------------------------------------------------------
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RunProc(string arg)
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("Hello");
Process.Start("E: est.exe");
}
}
CREATE ASSEMBLY [RunProcess]
FROM 'RunProcess.dll'
CREATE PROCEDURE dbo.sqlclr_RunProc
(
@arg nvarchar(1024)
)
AS EXTERNAL NAME [RunProcess].[StoredProcedures].[RunProc]
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 .....
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
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'."
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
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.
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
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?
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.
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 ?
Hi I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005. Thanks for any help
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.
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 ?
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
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.
hi there - I'm praying someone here can shed some light on this - after many google and web searches, I have found users with similar issues, but have yet to find a solution. Problem: I have recently upgraded my desktop from Windows XP to Windows Vista Ultimate (clean install) and have migrated my local machine to SQL Server 2005. However I have a number of .Net projects that access databased on another server running SQL Server 2000 - prior to my upgrade to Vista, I was running SQL Server 2000 on my desktop as well. During the install, everything seemed to go well, and I also installed the SQL Server 2005 Service pack as instructed via the MSDN site. However, when attempting to test my .net projects on my desktop pc (ie visit http://localhost), I encounter the error: 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) note that the server I am attempting to connect TO is the old SQL 2000 server - NOT 2005. I specify my connection info in the web.config - and it points to an external ip address on the web. Can anyone shed some light on this? I've tried enabling remote access on my local SQL 2005 install, but I'm fairly sure thats for servers that want to connect to my desktop, rather than my desktop attempting to connect to external servers. Any help is greatly appreciated - I'm pulling my hair out over trying to figure this out! :) To summarize my setup (if it helps) Desktop PC:Windows Vista (Ultimate)SQL Server 2005Visual Studio 2005II 7.0.Net 2.0 (ASP.Net Application) External Server (co-located at a datacenter)Windows Server 2003SQL Server 2000IIS 6.0.Net 2.0 (ASP.Net Application)
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
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 !!!
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
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.
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?
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!
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 ........................................
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)
