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


ADVERTISEMENT

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

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

Sql Server 2000 - Dts Can't 'see' Stored Procedure

Jul 20, 2005

I am trying to get the DTS Execute SQL task to run a simple sql server 2000no-parameter stored procedure.The procedure runs without error when using SQL Analyzer but DTS Execute SQLreports "could not find stored procedure "[dbo].[test]"The stored procedure is as follows:create procedure test asdeclare tnames_cursor CURSORforselect database_name, tmp_table_name from[DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]open tnames_cursordeclare @tablename sysnamedeclare @databasename sysnamewhile (@@FETCH_STATUS <> -1)beginselect @databasename = rtrim(@databasename)select @tablename = rtrim(@tablename)exec ("delete from " + @databasename + ".dbo." + @tablename)fetch next from tnames_cursor into @databasename, @tablenameendclose tnames_cursordeallocate tnames_cursorGOAny ideas?

View 1 Replies View Related

SQL Server 2000 Stored Procedure With ADO And VBA

Feb 1, 2008

I have a stored procedure that builds a sql statement and executes it. When run in Query Analyzer it runs successfully and displays records as it should. When I open a recordset with the stored procedure from VBA it executes the codes but doesn't open the recordset.

The store procedure is:
ALTER PROCEDURE dbo.spTest
AS
DECLARE @sColumns varchar(2000)
DECLARE @sFrom varchar(2000)
DECLARE @sWhere varchar(2000)
DECLARE @sSqlString AS nvarchar(2000)
SET @sColumns = 'dbo.tblDoctor.lastname '
SET @sFrom = 'FROM dbo.tbldoctor '
SET @sWhere = 'WHERE dbo.tbldoctor.doctorcode is not null '
SET @sSqlString = 'SELECT ' + @scolumns + ' ' + @sFrom + ' ' + @swhere PRINT '@scolumns ' + @scolumns

PRINT '@sfrom ' + @sfrom PRINT '@swhere ' + @swhere PRINT 'sqlstring ' + @sSqlstring
EXEC Sp_executesql @sSqlString

The VBA code is:
Private Sub Command0_Click()
Dim cn As Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Set cn = CurrentProject.Connection
strsql = "exec spTest"
With rs

.Source = strsql
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Open
End With
Debug.Print rs.RecordCount & " records found"
End Sub
When it hits the line with rs.recordcount it displays the following error: "Operation is not allowed when object is closed" referring to the rs recordset never being opened.

Any suggestions?

View 4 Replies View Related

Migrating SQL Server 2000 Stored Procedures To SQL Server 2005

Dec 20, 2007

I have successfully moved my data from a SQL Server 2000 hosting site to a SQL Server 2005 hosting site. I Made a backup of my database using Enterprise Manager (2000) and imported the database tables using SQL Server Management Studio (2005). I do not know how to move the 25 or so stored procedures that I have in SQL Server 2000. I have a very short amount of time to figure this out and am hoping that someone can give me a brief step by step answer on how to get this done. I would appreciate any information you can provide. Thank you!!

View 5 Replies View Related

In MS SQL Server 2000 How To Find Last Used Stored Procedure ?

Jul 30, 2007

Hi

This is seshu.

Is there any way to find out the last used stored procedure.

Let me know about this one.

View 1 Replies View Related

SQL Server 2000 And Stored Procedure Concurrency

Apr 1, 2005

I'm using Java to connect to a SQL Server 2000 database. I connect using the Driver Manager with Sun's odbc driver ( sun.jdbc.odbc.JdbcOdbcDriver ) or I can use the jdbc driver provided by Microsoft (com.microsoft.jdbc.sqlserver.SQLServerDriver)

The Java application makes 1 Connection.

Within the database there exists a stored procedure that updates 2 Tables. The tables have a fixed number of rows that get updated continuously by calls to this stored procedure.

The Java application has a thread pool of 15 threads that create 15 CallableStatements (1 per thread) using the same instance of the Connection object.

According the the Microsoft JDBC driver docs, 1 Connection with multiple calls to the Callable statements is how it's supposed to be done. The following is an excerpt from Microsoft's "SQL Server 2000 Driver for JDBC User’s Guide and Reference" (page 86) regarding Connection Managment:
Managing Connections

Connection management is important to application performance. Optimize your application by connecting once and using multiple statement objects, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection.
This is precisely what I'm doing, but I do not know if the the stored procedures are being run concurrently, the documentation does not tell me.

So my question: What is happening inside SQL Server 2000?

View 2 Replies View Related

Problem With Stored Procedure In SQL Server 2000

May 19, 2004

Hi everyone,

I am new to the forum and I have a problem with a stored procedure in SQL Server 2000. I use a stored procedure that returns a string but the problem is that my variable is empty at least I think it's empty.

With the code below the variable is not empty:
set @Ssql = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''

But when I use the code below it's empty

- set @sSql = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 '
- set @sSql = @sSql + ' and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''


I also tried this :

- set @Start = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 '
- set @End = ' and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''
- set @sSql = @Start + @End

This works also, so I don't get why the second example won't work and the second example is the one I want to use.

So does anyone has a clue :confused:

thx in advance,

grtz

Loki2600

View 2 Replies View Related

Possible To Lock A Row Within A Stored Procedure In SQL Server 2000?

Jul 20, 2005

Hi All,I have a table that holds pregenerated member IDs.This table is used to assign an available member id to web sitevisitors who choose to register with the siteSo, conceptually the process has been, from the site (in ASP), to:- select the top record from the members table where the assigned flag= 0- update the row with details about the new member and change theassigned flag to 1- return the selected member id to the web pageNow I'm dealing with the idea that there may be brief, high trafficperiods of registration, so I'm trying to build a method (storedprocedure?) that will ensure the same member id isn't returned by theselect statement if more than 1 request to register happens at thesame instant.So, my question is, is there a way, once a record has been selected,to exclude that record from other select requests, within the boundsof a stored procedure?ie:- select statement is executed and row is instantly locked; any otherselect statement running at that exact moment will receive a differentrow returned and sill similarly lock it, ad nauseum for as manysimultaneous select statements as take place- row is updated with details and flag is updated to indicate themember id is no longer unassigned- row is released for general purposes etcIf what I'm suggesting above isn't practical, can anyone help meidentify a different way of achieving the same result?Any help immensely, immensely appreciated!Much warmth,Murray

View 12 Replies View Related

Sql Server 2000 - Xp_sendmail Stored Procedure

Aug 15, 2007

I have no idea about how can I use xp_sendmail
when I try to use it this error displayed on the screen.
There is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail ...

View 3 Replies View Related

Extended Stored Procedure Using C# For SQL Server 2000

Mar 12, 2007



Can anyone of you help me in finding online - resources to write extended stored procedure for Sql Server 2000 using C#.

Thank you,



Andy Rogers

View 1 Replies View Related

Passing XML From Sql Server 2000 In Stored Procedure

Jul 23, 2007

Hi,
I m wanting a stored procedure to pass an XML string from a stored procedure to my application. Whats the best way to achieve this?

Hope you can help

Thanks

Paul

View 10 Replies View Related

Stored Procedure Vs Simple Query In SQL Server 2000?

May 19, 2007

I am developing a windows application in VB.Net 2005 and Database is SQl Server 2000.I want to insert, update and delete records from a master table which has 8 columns.So should I write a stored procedure for this or write three queries and execute them in code.I haven't used stored procedure before. What will be advantages of using stored procedures?And tell me how to write stored procedure to insert,update and delete. Then how to call it in VB.Net code.

View 7 Replies View Related

Need Help In Writing A Stored Procedure In MSSQL Server 2000

Mar 29, 2007

Hi Everybody,

I am trying to update a column Percentage in a table named Critical Doctors with a column named

PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from

tblPercent.

I am getting an error message for the following query.

Have Two tables

1.CriticalDoctors
2.tblPercent

update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=

, >, >= or when the subquery is used as an expression.
The statement has been terminated.

Pls give me reply on how to write a stored procedure so that I can equate the percentage column

with one value and also check the condition with one value.

Thanking you guys in advance.

madhav

View 4 Replies View Related

SQL Server 2000 - Send A Mail By Stored Procedure

Mar 3, 2008

can I send a mail by stored procedure?

View 5 Replies View Related

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 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

Help!! SQL Server 2000 Extended Stored Procedure Hangs In Windows 98

Jul 20, 2005

I am trying to run xp_cmdshell from the Query Analyzer using SQLServer 2000 running on Windows 98.It seems like it should be simple - I'm typingxp_cmdshell 'dir *.exe'in the Query Analyzer in the Master db. I'm logged in as sa.The timer starts running and never stops. No error message.Can anyone PLEASE help me with this? Any suggestions would beappreciated. Are SQL Server 2000 extended stored procedures notsupported in Windows 98? I've tried searching the Knowledge Base butcan't find anything.Thanks!

View 1 Replies View Related

SQL Server 2000 - Create An Excel File By Stored Procedure..

Mar 3, 2008

Can I create an excel file by stored procedure?

View 2 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

Visual Basic Slow Down Sql Server 2000 Running A Stored Procedure

Nov 30, 2007

I hav the following problem. I have written an stored procedure in sql server 2000 as the following
CREATE PROCEDURE dbo.pa_rellena
@pFechaInicio datetime

AS
declare @pFechaFin datetime
declare @auxcod_cen char(10)

declare @importeEfectivo decimal(17,2)
declare @importeTarjetas1 decimal(17,2)
declare @importeTarjetas2 decimal(17,2)
declare @importeVales decimal(17,2)
declare @importeTalones decimal(17,2)
declare @importeGastos decimal(17,2)


select @pFechaFin=@pFechaInicio+1


--Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado
if object_id('tmpCentros') is not null
drop table tmpCentros

if object_id('tmpCentros2') is not null
drop table tmpCentros2

if object_id('tmpMaxCajas') is not null
drop table tmpMaxCajas

if object_id('tmpCajasCentro') is not null
drop table tmpCajasCentro

if object_id('tmpVales') is not null
drop table tmpVales

if object_id('tmpDiarioEfectivo') is not null
drop table tmpDiarioEfectivo

if object_id('tmpDiarioTalones') is not null
drop table tmpDiarioTalones

if object_id('tmpDiarioTarjetas') is not null
drop table tmpDiarioTarjetas

if object_id('tmpDiarioSegundaForma') is not null
drop table tmpDiarioSegundaForma

if object_id('tmpDiarioGastosTarjetas') is not null
drop table tmpDiarioGastosTarjetas

if object_id('temp1') is not null
drop table temp1

--Seleccionamos todos los centros de Salvador Bachiller
select * into tmpCentros2
from centros
where centros.tienda=1
order by cod_cen

--Seleccionamos el maximo de cajas por cada centro

select cod_cen, max(cod_caja) as cajas into tmpMaxCajas
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen
order by cod_cen

--Mezclamos los centros con el maximo de cajas
select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros
from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen

--Cajas por centro
select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin

--Los vales de cada centro
select cod_cen,sum(importe) as imp1 into tmpVales
from vales where
fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen

--Efectivo de cada centro
select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo
from diario
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01'
group by cod_cen

--Talones por centro
select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones
from centros inner join diario on centros.cod_cen=diario.cod_cen
where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02'
group by centros.cod_cen

--Tarjetas por centro
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas
from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen

--Segunda Froma de Pago
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma
from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen

--Comisiones tarjetas de pago
select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas
from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
/*
--Venta neta por centro
declare cursortemporal cursor for select cod_cen from TmpCentros2

open cursortemporal
delete detallecaja_aux
fetch next from cursortemporal into @auxcod_cen
while @@fetch_status=0
Begin
select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen
select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen
select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen
select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen
select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen
select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen

select @importeVales=isnull(@importeVales,0)
select @importeEfectivo=isnull(@importeEfectivo,0)
select @importeTalones=isnull(@importeTalones,0)
select @importeTarjetas1=isnull(@importeTarjetas1,0)
select @importeTarjetas2=isnull(@importeTarjetas2,0)
select @importeGastos=isnull(@importeGastos,0)

print @auxcod_cen
print @importeVales
print @importeEfectivo
print @importeTalones
print @importeTarjetas1
print @importeTarjetas2
print @importeGastos

insert into detallecaja_aux (cod_cen,importe1)
values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos)
fetch next from cursortemporal into @auxcod_cen

select @importeVales=0
select @importeEfectivo=0
select @importeTalones=0
select @importeTarjetas1=0
select @importeTarjetas2=0
select @importeGastos=0
end

close cursortemporal
*/
select * from detallecaja_aux
GO

When I try to run it from visual basic it slow down the sql server.

What can I do?

View 2 Replies View Related

About SQL SERVER 2005(Stored Procedure) &&amp; 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

Stored Procedure Runs Diffrently In 2000 And 2005

Apr 19, 2007

Hi there everybody,

I am writing a sales managing software using C# in .NET 2005.
The program was used to work properly with sql server 2000.
I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.

But the problem is when program executes something like this:
EXEC [AddOrder] ...
two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine.
I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.

Any ideas?

Kind Regards: Ali

View 6 Replies View Related







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