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 site
visitors who choose to register with the site
So, 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 the
assigned flag to 1
- return the selected member id to the web page
Now I'm dealing with the idea that there may be brief, high traffic
periods of registration, so I'm trying to build a method (stored
procedure?) that will ensure the same member id isn't returned by the
select statement if more than 1 request to register happens at the
same 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 bounds
of a stored procedure?
ie:
- select statement is executed and row is instantly locked; any other
select statement running at that exact moment will receive a different
row returned and sill similarly lock it, ad nauseum for as many
simultaneous select statements as take place
- row is updated with details and flag is updated to indicate the
member id is no longer unassigned
- row is released for general purposes etc
If what I'm suggesting above isn't practical, can anyone help me
identify a different way of achieving the same result?
Hello,Can we lock stored procedure until its execution is complete ?I dont want 2 clients to simultenously execute the stored procedure inSql Server 2000.My front end is ASP.net 1.0Thanks in advance.waiting for the reply.
I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.
I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.
The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.
The table looks like this: Logid PK LogTypeID -- what category LogValue -- LogTime -- when it occurred
My imaginary stored procedure looks something like this:
CREATE PROCEDURE ShrinkDB AS -- 1) "lock table log" -- do I have to do something like this?
-- 2) select * into log_keep FROM Log where ( logtypeid <> 2020 AND -- activity played logtypeid <> 5020 AND -- database connected -- ....etc et..... about 10 different things I don't need to keep or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours ) -- 3) drop table log
-- 4) EXEC sp_rename 'log_keep', 'log' GO
I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??
Hopefully it works that way, but if not I assume I'll run into these two problems:
- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?
- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?
Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject.... :-)
Hello!I am trying to investigate strange problem with particular storedprocedure. It runs OK for several days and suddenly we start gettingand lotof locks. The reason being [COMPILE] lock placed on this procedure. Asaresult, we have 40-50 other connections waiting, then next connectionusingthis procedure has [COMPILE] lock etc. Client is fully qualifyingstoredprocedure by database/owner name and it doesn't start with sp_. I knowthese are the reasons for [COMPILE] lock being placed. Is theresomethingelse that might trigger this lock? When troubleshooting this issue, Inoticed there was no plan for this procedure in syscacheobjects. Thestoredprocedure is very simple (I know it could be rewritten/optimized butourdeveloper wrote it):CREATE PROCEDURE [dbo].[vsp_mail_select]@user_id int,@folder_id int,@is_read bit = 1, --IF 1, pull everything, else just pull unread mail@start_index int = null, --unused for now, we return everything@total_count int = null output, -- count of all mail in specifiedfolder@unread_count int = null output -- count of unread mail in specifiedfolderASSET NOCOUNT ONselect m1.* from mail m1(nolock) where m1.user_id=@user_id andfolder_id=@folder_id and ((@is_read=0 and is_read=0) or (@is_read=1))orderby date_sent descselect @total_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and ((is_read=0 and@is_read=0)or (@is_read=1))select @unread_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and is_read=0GOI was monitoring server for a couple of day before and I am not surewhythis happens every 3-4 days only!Any help on this matter would be greately appreciated!Thanks,Igor
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.
Hi We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
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?
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
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.
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?
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.
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 ...
Devteam writes "Hi, WIndows version 5.0 (SP4) I am using sql server 2000. in our application i am using simple select statement to retrieve the value from the database. Sometime the same query is accessed i did not get the result.i have tried to run the same query using query analyzer. but the query is keep on running. at the same time when accessing other rows in the table, it gets retrieve the values.
what is the problem here... is there anything locked that the row before executing the select statement. if there is any lock is present then how to identify it and also how to prevent a row from locking... if the lock is occured then the other transactions also not executed. why the lock is placed in the first place."
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.
Hi, i am getting this error when i am running a stored procedure.
Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
i think so it is getting this error becasue it blocking it self at one point in the SP
DECLARE cty_Cursor CURSOR FOR SELECT Country FROM TB_Country
declare @cty varchar(2)
OPEN cty_Cursor; FETCH NEXT FROM cty_Cursor into @cty; WHILE @@FETCH_STATUS = 0 BEGIN EXEC SP_DO_SOMETHING @cty FETCH NEXT FROM cty_Cursor into @cty; END; CLOSE cty_Cursor; DEALLOCATE cty_Cursor;
i think so it calls the SP then before SP finsih its working it calls it back from cursor with other argument.
how we can make it sure it finish it execution before it is being called again. i think so we need some sort of lock here but i am not able to find right solution . please anyone suggest something.
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
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!
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.
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.
I'm not a SQL Server expert, but need to understand something. I'm currently have SQL Server 2000 SP4 installed locally on my machine for application development.
Using sp_lock, I noticed that there are lots of shared database locks. After some study, i produced this interesting script:
use Master go sp_lock go use Experience go sp_lock go
which results in:
spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 52 1 85575343 0 TAB IS GRANT spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 52 13 0 0 DB S GRANT 52 1 85575343 0 TAB IS GRANT
My question is about the extra lock added by the "Use Experience statement". It lasts as long as the Experience DB connection is open
Question 1: The way I read this is: the "use Experience" statement creates a shared database lock. Am I reading this right?
Question 2: I started looking into this because my application produces lots of locks that seem to live for 15-20 minutes. I have not noticed other adverse side effects of these locks. I have confirmed that nothing I have control over creates transactions, so I do not see why locks would be maintained. Should I be concerned about these locks? Is this expected behavior?
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)
--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
insert into detallecaja_aux (cod_cen,importe1) values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos) fetch next from cursortemporal into @auxcod_cen
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Hi,Sorry, this is a very easy question. I have to create a storedprocedure in a SQL SERVER Database.I have read lots on MSDN about the syntax of this stored procedure :ie.CREATE PROCEDURE spGetContctDetailsAS-- get everything out of contact tableSELECT * FROM tblContactbut I cannot find anywhere how to actually add this procedure, so thatit is accessable in my VB.NET program.What I am asking is : what screen do I type this into? Is it QueryAnalyzer, if so where?Its got me oh so confused. When I've got time i'll read all about SQLServer, but for this morning I need to know how to simply add thatStored Procedure to my database.Thanks LOADS for any help.
I have a MSSQL2000 box with a large database containing circa 150 SP's.I want to move the DB to another SQL machine, but many of the SP's havereferences to the name of the current machine. Is there any way tobatch edit the procedures to refer to the new server, disassociatingthe old machine completely?Tia,Graham
