Stored Procedure Stops Working
Jun 1, 1999
I have a stored procedure which does a simple select joining 3 tables.
This had been working fine for weeks and then just stopped returning any rows even though data existed for it to return.
After re-compiling the procedure, it worked fine as before.
Does anyone know of any reason why a procedure would need recompiling like this?
We have been doing data restores and also dropping/recreating tables during this development. Would any of this affect a procedure?
View 3 Replies
ADVERTISEMENT
Feb 28, 2001
I have a stored procedure that is calling a cursor to populate some variables it then uses those variable to get more information and then inserts that info into a final table. The estimated number of records that it should insert is around 2 million. The procedure stops after about 6000 records inserted. It still apears to be running but in fact is not. Can anyone help? I have also attached the code.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [dbo].[create_table1_tmp] AS
/* table1 variables */
declare@v_ARCKEY int
declare@v_INVKEY int
declare@v_ITEM char (15)
declare@v_BRAND char(15)
declare@v_PRICE decimal(12, 2)
declare@v_QTYORD decimal(12, 3)
declare@v_QTYSHP decimal(12, 3)
declare@v_CTCKEY int
declare@v_SOMKEY int
declare@v_ORDATE datetime
declare@v_FNAME char (15)
declare@v_CONTACT varchar (20)
declare@v_TITLE varchar (25)
declare@v_SALUT char (4)
declare@v_DEGREE char (6)
declare@v_SALESMN2char (3)
declare@v_TTL_CODEchar (4)
declare@v_SPECIALTYvarchar (30)
declare@v_COMPANYvarchar (35)
declare@v_ADDRESS1varchar (50)
declare@v_ADDRESS2varchar (50)
declare@v_CITYvarchar (20)
declare@v_STATEchar (10)
declare@v_KEYCODEchar (10)
declare@v_ZIPchar (10)
declare@v_COUNTRYchar (2)
declare@v_ARCSOURCEchar (5)
declare@v_SLSVOLchar (1)
declare@v_TYPEchar (8)
declare@v_NUMDRSdecimal(9,0)
declare@v_BEDSIZEdecimal(4,0)
declare@v_NUMLIVESdecimal(9,0)
declare@v_CODEchar (3)
declare@v_SUBKEYint
declare@v_SUBTYPEchar (1)
declare@v_STARTDATEdatetime
declare@v_TERMdecimal(2, 0)
declare@v_STATUSchar (1)
declare@v_STATDATEdatetime
declare@v_XRENEWEDdecimal(2, 0)
declare@v_EXPDATEdatetime
declare@v_SHPAMTdecimal(12, 2)
declare@v_SOMSOURCEchar (5)
declare@v_PMETHchar (1)
declare@v_EXTPRICEdecimal(12, 2)
declare@v_CTYPEchar (5)
declare@v_CTCKEY_SBSUBS int
declare@v_ACRONYMchar (8)
declare@v_PONUMvarchar (20)
declare@v_EMAILvarchar (50)
declare@v_ARCPHONEVARCHAR (20)
declare@v_ARCFAXNOVARCHAR (20)
declare@v_CMCPHONEVARCHAR (20)
declare@v_CMCFAXNOVARCHAR (20)
declare@v_DOFAXTINYINT
declare@v_DOPHONETINYINT
declare@v_DORENEWTINYINT
declare@v_DOMAILTINYINT
declare@v_DOUPDATETINYINT
declare@v_UPDONLYTINYINT
declare@v_EMAIL_INFOTINYINT
declare@v_EMAILPROMOTINYINT
declare@v_DISCdecimal(7,3)
declare @v_EUPDATETINYINT
/* processing Variables */
declare @v_tmpint
declare@v_amtdecimal(9,3)
declare@v_loopint
declare@v_arckey_tmp INT
declare@v_ctckey_tmp INT
declare@v_invkey_tmp INT
declare@v_subkey_tmp INT
declare@v_arcDORENEW TINYINT
declare@v_cmcDORENEW TINYINT
declare@v_sbsDORENEW TINYINT
declare@v_arcDOFAX TINYINT
declare@v_cmcDOFAX TINYINT
declare@v_arcDOMAIL TINYINT
declare@v_cmcDOMAIL TINYINT
declare@v_arcDOPHONE TINYINT
declare@v_cmcDOPHONE TINYINT
/* cursors for retrieving online data*/
declarev_mast cursor for
select /*+ INDEX_COMBINE(arc) */
som.arckey arckey,
sot.invkey invkey,
sot.item item,
sot.price price,
sot.qtyord qtyord,
sot.qtyshp qtyshp,
som.ctckey ctckey,
som.somkey somkey,
sot.ordate ordate,
arc.phone arcphone,
arc.faxno arcfaxno,
arc.salesmn2 salesmn2,
arc.country country,
arc.source arcsource,
arc.slsvol slsvol,
arc.type type,
arc.specialty specialty,
arc.numdrs numdrs,
arc.bedsize bedsize,
arc.numlives numlives,
arc.code code,
arc.dorenew arcdorenew,
arc.company company,
arc.address1 address1,
arc.address2 address2,
arc.city city,
arc.state state,
arc.zipzip,
sub.eupdate eupdate,
sub.doupdate doupdate,
sub.subkey subkey,
sub.subtype subtype,
sub.startdate startdate,
sub.term term,
sub.status status,
sub.statdate statdate,
sub.xrenewed xrenewed,
sub.dorenew sbsdorenew,
som.shpamt shpamt,
som.source somsource,
som.pmeth pmeth,
sub.ctckey ctckey_sbsubs,
sub.updonly updonly,
sot.disc disc,
arc.dofax arcdofax,
som.ponum,
arc.domail,
arc.dophone
from sotran sot,somast som, arcust arc,sbsubs sub
where (som.arckey != '121449' and som.arckey != '1364166')
and (som.sotype not in (
select value from table1_param
where name = 'sotype') or
som.sotype is null)
and (som.sostat not in (
select value from table1_param
where name = 'somstat') or
som.sostat is null)
and arc.arckey = som.arckey
and (arc.code not in (
select value from table1_param
where name = 'code') or
arc.code is null)
and (arc.slsvol not in (
select value from table1_param
where name = 'slsvol') or
arc.slsvol is null)
and arc.foreign_ = 0
and sot.somkey = som.somkey
and (sot.sostat not in (
select value from table1_param
where name = 'sotstat') or
sot.sostat is null)
and sot.item > '0100'
and sub.subkey =* sot.subkey
order by arc.arckey,som.ctckey,sot.invkey,sub.subkey,sot.rq date desc
-- BEGIN PROCESSING THE CURSOR DATA HERE
OPEN v_mast
--pull all somast records where custno isn't 121449 or 1364166
--and sotype filtered and sostat isnot filtered
set @v_loop = 0
fetch next from v_mast into @v_arckey, @v_invkey, @v_item, @v_price, @v_qtyord,
@v_qtyshp, @v_ctckey, @v_somkey, @v_ordate, @v_arcphone, @v_arcfaxno, @v_salesmn2, @v_country,
@v_arcsource, @v_slsvol, @v_type, @v_specialty, @v_numdrs, @v_bedsize, @v_numlives,
@v_code, @v_arcdorenew, @v_company, @v_address1, @v_address2, @v_city,
@v_state, @v_zip, @v_eupdate, @v_doupdate, @v_subkey, @v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_sbsdorenew, @v_shpamt, @v_somsource, @v_pmeth, @v_ctckey_sbsubs,
@v_updonly, @v_disc, @v_arcdofax, @v_ponum, @v_arcdomail, @v_arcdophone
while @@fetch_status = 0--for v_rec in v_mast loop
Begin
set @v_loop = @v_loop + 1
set @v_expdate = dateadd(month,@v_term,@v_startdate) - 1
set @v_keycode = ''
--select company, address1, address2, city, state, zip
--from cmcship
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--set @v_tmp = null
--else
--begin
begin tran t1
set @v_company = (select top 1 company from cmcship where ctckey = @v_ctckey)
set @v_address1 = (select top 1 address1 from cmcship where ctckey = @v_ctckey)
set @v_address2 = (select top 1 address2 from cmcship where ctckey = @v_ctckey)
set @v_city = (select top 1 city from cmcship where ctckey = @v_ctckey)
set @v_state = (select top 1 state from cmcship where ctckey = @v_ctckey)
set @v_zip = (select top 1 zip from cmcship where ctckey = @v_ctckey)
commit tran t1
--end
--select company, address1, address2, city, state, zip
--from cmcadd
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--set @v_tmp = null
--else
--begin
begin tran t2
set @v_company = (select top 1 company from cmcadd where ctckey = @v_ctckey)
set @v_address1 = (select top 1 address1 from cmcadd where ctckey = @v_ctckey)
set @v_address2 = (select top 1 address2 from cmcadd where ctckey = @v_ctckey)
set @v_city = (select top 1 city from cmcadd where ctckey = @v_ctckey)
set @v_state = (select top 1 state from cmcadd where ctckey = @v_ctckey)
set @v_zip = (select top 1 zip from cmcadd where ctckey = @v_ctckey)
commit tran t2
--end
--select fname, contact, title, salut, degree, phone, faxno, ttl_code, dorenew,
--ctype, email, dofax, domail, email_info, emailpromo, docall
--from cmctac
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--RAISERROR ('cmc not found', 16, 1)
--else
--Begin
begin tran t3
set @v_fname = (select top 1 fname from cmctac where ctckey = @v_ctckey)
set @v_contact = (select top 1 contact from cmctac where ctckey = @v_ctckey)
set @v_title = (select top 1 title from cmctac where ctckey = @v_ctckey)
set @v_salut = (select top 1 salut from cmctac where ctckey = @v_ctckey)
set @v_degree = (select top 1 degree from cmctac where ctckey = @v_ctckey)
set @v_cmcphone = (select top 1 phone from cmctac where ctckey = @v_ctckey)
set @v_cmcfaxno = (select top 1 faxno from cmctac where ctckey = @v_ctckey)
set @v_ttl_code = (select top 1 ttl_code from cmctac where ctckey = @v_ctckey)
set @v_cmcdorenew = (select top 1 dorenew from cmctac where ctckey = @v_ctckey)
set @v_ctype = (select top 1 ctype from cmctac where ctckey = @v_ctckey)
set @v_email = (select top 1 email from cmctac where ctckey = @v_ctckey)
set @v_cmcdofax = (select top 1 dofax from cmctac where ctckey = @v_ctckey)
set @v_cmcdomail = (select top 1 domail from cmctac where ctckey = @v_ctckey)
set @v_email_info = (select top 1 email_info from cmctac where ctckey = @v_ctckey)
set @v_emailpromo = (select top 1 emailpromo from cmctac where ctckey = @v_ctckey)
set @v_cmcdophone = (select top 1 docall from cmctac where ctckey = @v_ctckey)
commit tran t3
--end
--select acronym, brand from invhead
--where invkey = @v_invkey
--if (@@cursor_rows = 0)
--set @v_tmp = null
--else
--begin
begin tran t4
set @v_acronym = (select top 1 acronym from invhead where invkey = @v_invkey)
set @v_brand = (select top 1 brand from invhead where invkey = @v_invkey)
commit tran t4
--end
set @v_amt = ((@v_qtyshp+@v_qtyord)*@v_price *(100-@v_disc))*.01
if @v_amt < 0
set @v_extprice = round(@v_amt,2)
else
set @v_extprice = round(@v_amt,2)
if (@v_arcdomail = 0 or @v_cmcdomail = 0)
set @v_domail = 0
else
set @v_domail = 1
if (@v_arcdofax = 0 or @v_cmcdofax = 0)
set @v_dofax = 0
else
set @v_dofax = 1
if (@v_arcdophone = 0 or @v_cmcdophone = 0)
set @v_dophone = 0
else
set @v_dophone = 1
if ((@v_arcdorenew = 0 or @v_cmcdorenew = 0) or @v_sbsdorenew = 0)
set @v_dorenew = 0
else
set @v_dorenew = 1
-- remove all but the newest orders
if @v_arckey = @v_arckey_tmp and @v_ctckey = @v_ctckey_tmp and
@v_invkey = @v_invkey_tmp and ((@v_subkey is null and
@v_subkey_tmp is null) or (@v_subkey = @v_subkey_tmp))
set @v_tmp = null
else
Begin
begin tran t5
set @v_arckey_tmp = @v_arckey
set @v_ctckey_tmp = @v_ctckey
set @v_invkey_tmp = @v_invkey
set @v_subkey_tmp = @v_subkey
commit tran t5
begin tran t6
insert into table1_tmp
(
arckey, invkey, item, brand, price, qtyord, qtyshp, ctckey, somkey,
ordate, fname, contact, title, salut, degree, salesmn2, ttl_code,
specialty, company, address1, address2, city, state, keycode,
zip, country, arcsource, slsvol, type, numdrs, bedsize, numlives,
code, subkey, subtype, startdate, term, status,
statdate, xrenewed,expdate, shpamt, somsource,
pmeth, extprice, ctype,ctckey_sbsubs, acronym,
ponum, email, arcphone, arcfaxno, cmcphone, cmcfaxno,
dofax, dophone, dorenew, domail, doupdate, updonly,
email_info, emailpromo, disc)
values (
@v_arckey, @v_invkey, @v_item, @v_brand, @v_price, @v_qtyord, @v_qtyshp,
@v_ctckey, @v_somkey, @v_ordate, @v_fname, @v_contact, @v_title,
@v_salut, @v_degree, @v_salesmn2, @v_ttl_code, @v_specialty, @v_company,
@v_address1, @v_address2, @v_city, @v_state, @v_keycode,
@v_zip, @v_country, @v_arcsource, @v_slsvol,
@v_type, @v_numdrs, @v_bedsize, @v_numlives, @v_code,
@v_subkey,@v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_expdate, @v_shpamt, @v_somsource,
@v_pmeth, @v_extprice, @v_ctype, @v_ctckey_sbsubs, @v_acronym,
@v_ponum, @v_email, @v_arcphone, @v_arcfaxno, @v_cmcphone,
@v_cmcfaxno, @v_dofax, @v_dophone, @v_dorenew, @v_domail,
@v_doupdate, @v_updonly, @v_email_info, @v_emailpromo, @v_disc
)
commit tran t6
End
fetch next from v_mast into @v_arckey, @v_invkey, @v_item, @v_price, @v_qtyord,
@v_qtyshp, @v_ctckey, @v_somkey, @v_ordate, @v_arcphone, @v_arcfaxno, @v_salesmn2, @v_country,
@v_arcsource, @v_slsvol, @v_type, @v_specialty, @v_numdrs, @v_bedsize, @v_numlives,
@v_code, @v_arcdorenew, @v_company, @v_address1, @v_address2, @v_city,
@v_state, @v_zip, @v_eupdate, @v_doupdate, @v_subkey, @v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_sbsdorenew, @v_shpamt, @v_somsource, @v_pmeth, @v_ctckey_sbsubs,
@v_updonly, @v_disc, @v_arcdofax, @v_ponum, @v_arcdomail, @v_arcdophone
end --while loop
close v_mast
deallocate v_mast
View 1 Replies
View Related
Oct 5, 2007
I've set up a Service Broker and it was working fine.
All of a sudden each time I switch on my computer it's not working anymore.
I have to drop the services, queues, contract and messages and create them again.
Then it works again until I shutdown my computer. When I start it again it's not working.
Any suggestion on how to fix this?
Thank you
View 8 Replies
View Related
Feb 22, 2008
I've written a stored procedure that copies records from tables in one database to the same tables in another database. Here is a snippet for a single table:
delete from washmaster_mirror.dbo.batches
DBCC CHECKIDENT (washmaster_mirror.dbo.batches', RESEED, 0)
alter table washmaster_mirror.dbo.batches disable trigger tr_BATCHES_INSERT
alter table washmaster_mirror.dbo.batches disable trigger tr_BATCHES_UPDATE
insert into washmaster_mirror.dbo.batches(processorid,batchnum,processdate,transcount,batchamount,dtmCreate,dtmUpdate,keypreserve)
(select processorid,batchnum,processdate,transcount,batchamount,dtmCreate,dtmUpdate,[ID]
from washmaster.dbo.batches)
alter table washmaster_mirror.dbo.batches enable trigger tr_BATCHES_INSERT
alter table washmaster_mirror.dbo.batches enable trigger tr_BATCHES_UPDATE
This kind of block is repeated in the same stored procedure for each table. There are 20 tables populated this way. The idea is to copy records from the working (washmaster) database to a mirror (washmaster_mirror) database then back up the mirror database so I can avoid having down time during the back up process (due to database locking).
The problem I have is that the above stored procedure sometimes causes the SQLEXPRESS service to stop, thus the Sql Express connection to break. I've checked the error logs and the event log (I'm running on Windows Vista) and nothing sheds light on the issue. When I originally coded the stored procedure I surrounded it by a TRY - CATCH block, which forced it to lose the connection every time. When I eliminated the TRY - CATCH block the broken connection issue became rare, but still occurs occasionally. Any ideas why this might be occuring or what I can do to solve it?
Thanks,
SJonesy
View 5 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
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)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'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.
View 11 Replies
View Related
Aug 18, 2006
I created some test data in two tables. Then I went to one of the tables and right clicked then Selected Script Table asSelect ToNew Query Editor Window. I then cleared the generated data and selectedDesign Query in Editor. I then picked both tables in the 'Add Table' window and picked 'Add'. This produced two windows and I made the links I needed and clicked OK. This generated the SQL I wanted and it works great! But when I close the SQL Express and then re-open it the SQL does not work! I open SQL Express and selectFileOpenFile and pick the SQL I had just saved. Then I pick Execute I get the following message.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.FileData'
How can I save SQL that works and then not be able to use it?
Beats Me,
Miname
View 3 Replies
View Related
Dec 29, 2004
I have had my site running for several months now. My pages retrieve data from the SQL Server on the same machine. Today my users are are getting the message "SQL Server does not exist or access denied"
I have made sure that the credentials are correct. I can use those credentials to create a DSN on the server. So I don't know where to look for the problem. Help please!
View 1 Replies
View Related
Oct 10, 2006
I've been coding for a few weeks now, building an ASP.NET application. ran the aspnet_regsql.exe wizard and it created my table and procedures correctly within the live SQL 2005 server being run by my host (ASPNIX.COM). I've been able to run my app just fine locally saving to my remote SQL server. However, now that I've moved my code onto my hosted server, my app stopped working. You would think this would "jus work" since I've been using the same SQL server throughout...BUT NOoooo! I've scoured the net trying to find any hint about what might be happening. The ONLY thing I've been able to find is a story in the May issue of asp.netPRO that says "Once the scripts have been executed, grant the user account used to access the database from the Web application Execute permissions on all the new stored procedures and Selection permissions on the views that were created"This may be my problem, but I haven't figured out how to accomplish this. Within the SQl Server Management Studio Express, I can pull up the properties of each stored procedure and place a check next to my "USER" account. However it does not appear to be saved.My symptoms at this point are:It will not log me in under accounts I'd already created.
View 7 Replies
View Related
Jun 5, 2007
I have a few reports that suddenly stopped printing.
Nothing changed on the report server, and one minute they could print, the other they couldn't
I have one report that is 4 pages in preview and six when it prints. The users press the print control on the web page and the print dialog comes up, they select a printer and the printing messagebox comes up. Then it will print a couple of pages VERY SLOWLY... and eventually will hang at the 3rd page.
IT appears it is just spooling in the printer control viewer...
Other similiar reports that use the same query but a different parameter, work just fine.
I have rebuilt and redployed the report. The problem is happening on both my live and test environments.
I have looked through this forum but not found an answer. I did delete and re-isntall the client print control.
I have turned the client logging on. But no errrors show up anywhere.
I am running ssrs 2005 on both machines. test machine has win2003 sp1 , sql 2005 sp2 AND hotfix's
live machine has win2003 and sql 2000 latest sp.
I need help!
TIA
Daryl
View 2 Replies
View Related
Dec 29, 2006
Hi All
We built a Cache component that take advantage of the SQL Server 2005 query notification mechanism, all went well , we tested the component in a console application , and notifications kept coming for as long time as the console application ran.
When we initiate our Cache Component in our web service global.asx application start event , the query notification works for a few minutes , but if we came after 10 minutes or so , we stoped getting notifications from sql, the SQL Server queue is empty , and all is showing that there is nothing wrong on the DB side...
Our Cache component is a Singleton class , that perform all registrations ,catch the notification events and resubscribe for notifications.
What can be the problem? is our Cache component object are being collected by GC?
Does IIS disposes the SQL Connection that the Query notification uses?
We are on a crisis...
Thanks in advance.
View 7 Replies
View Related
Nov 22, 2015
I have a working set of parameters, when they are all visible. However, when I change the second parameter to hidden it stops working.
First Parameter - Contains static values 'MTD' & Daily
Second Parameter - has available values and a default. Set up to be invalidated when the First parameter changes... like i say it works beautifully when not hidden.
Third Parameter - has an expression takes the value of the 2nd parameter value, removes the first character off and then converts the remainder to a date and uses it as its default value.
It based on this webpage: URL....However, when i set the second parameter to hidden it no longer works.
View 2 Replies
View Related
Jan 17, 2008
I was trying to migrate an ASP application from Access to SQL Server 2005 express, and found surprisingly that some code stops working which would seem to be independent of the data source. For example:
qtext = "SELECT MainText, TextType FROM MessageText WHERE [etc. etc. -- the query is OK]"
rsMain.open qtext, dbcon, , , adCmdText
if not (rsMain.BOF and rsMain.EOF) then
rsMain.MoveFirst
do while not rsMain.EOF
select case rsMain.fields(1).value
case 1
session("headline") = rsMain.fields(0).value
case 2
introtext = rsMain.fields(0).value
case 6
helptext = rsMain.fields(0).value
end select
rsMain.MoveNext
loop
end if
rsMain.Close
"rsMain" is an ADO recorset which defaults to a forward-only cursor as opened above; dbcon is an ADO database connection. Nothing fancy; and it has been working for years as expected with Access. But the recordset seems to behave differently when SQL Server is the data source (even though the whole point of ADO is to provide a level of abstraction, and I expected it not to change.) In the code above, checking the value of "rsMain.Fields(1).value" causes "rsMain.Fields(0).value" to disappear or become inaccessible. As a simple debug exercise, I tried response.writing "rsMain.Fields(0).value", a block of text, prior to the select case block, and it works fine once (as long as rsMain.Fields(1).value has not been accessed), but then a second write comes up empty.
Is it reasonable to expect that ADO recordsets and cursors function differently if SQL server is the data source vs. Access? Is this documented anywhere?
View 3 Replies
View Related
Apr 21, 2014
I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different failover scenarios to see that everything is working properly. Everything works fine until I try testing the failure of the SQL Server service. When I stop the SQL Service on the primary server, it fails over to the secondary server as expected. I then start the service on the (now) secondary server and it comes back online as the secondary server. I then try to test that the service will fail back over when I stop the service on the new primary server.
However, when I stop the service, the secondary server now shows "resolving" and never comes back online. When I bring the service back up on the primary server, the secondary now shows as secondary instead of resolving. So to see if it's something about failing over from one server to another, I do a manual failover making the original primary server the primary again and everything is as it was originally.
I then stop the service on the primary server, but the secondary server now says resolving and the AG will not become available again until I start the service on the primary server.
It seems that when I first configured the quorum it worked fine the first failover scenario, then stopped working. I then added the file sharing witness, and failover worked the first time again, but not after that. For some reason after the initial failover it won't automatically failover again after that.
Config:
Servers: Windows Server 2012 Standard
SQL : SQL Server 2012 Enterprise SP1
View 5 Replies
View Related
Aug 14, 2007
Hi,
I have a report which has 3 tables kept inside rectangle.I have PageBreakAtBegin Set to True for the last 2 tables.Hide Property for rectangle is False.Everything is working fine.
Moment,I place condition in Hide Property,no matter it's True or False,page break stops wroking.I can see all the tables in the single Excel file or report
Any help will be appriciated
-Thanks,
Digs
View 5 Replies
View Related
May 25, 2007
Hi,
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.
I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.
Thanks,
Darrell Young
View 3 Replies
View Related
Jul 31, 2006
Help Stored procedure working but not doing anything New Post
Quote Reply
Please i need some help.
I am calling a stored procedure from asp.net and there is a
cursor in the stored procedure that does some processing on serval
tables.
if i run the stored procedure on Query Analyzer it works and does what
it is suppose to do but if i run it from my asp.net/module control it
goes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where is
called programmatically from the asp.net/module control page.plus it
does not throw any errors
This is the code from my control
System.Data.SqlClient.SqlParameter [] param={new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new
System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ "
"+UserInfo.Username)};
string
str=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
System.Data.SqlClient.SqlConnection cn=new
System.Data.SqlClient.SqlConnection(str);
cn.Open();
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();
SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);
------------------------THis is the code for my storedprocedure-------------
CREATE PROCEDURE [dbo].[generatePaylistTuned]
@periodStart datetime,
@periodEnd datetime,
@addedby varchar(40)
AS
begin transaction generatePayList
DECLARE @pensioner_id int, @dateadded datetime,
@amountpaid float,
@currentMonthlypension float,@actionType varchar(50),
@isAlive bit,@isActive bit,@message varchar(80),@NoOfLoadedPensioners int,
@NoOfDeadPensioners int,@NoOfEnrolledPensioners int,@DeactivatedPensioners int,
@reportSummary varchar(500)
set @NoOfLoadedPensioners =0
set @NoOfDeadPensioners=0
set @NoOfEnrolledPensioners=0
set @DeactivatedPensioners=0
set @actionType ="PayList Generation"
DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId
where p.isActive = 1
OPEN paylist_cursor
FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
WHILE @@FETCH_STATUS = 0
BEGIN
set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1
if(@isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@pensioner_id
set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners
set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension)
set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners
end
-- Get the next author.
FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
END
CLOSE paylist_cursor
DEALLOCATE paylist_cursor
set @reportSummary ="The No. of Pensioners Loaded:
"+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of
Deactivated Pensioners:
"+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of
Enrolled Pensioners:
"+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead
Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')
if (@@ERROR <> 0)
BEGIN
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')
ROLLBACK TRANSACTION generatePayList
END
commit Transaction generatePayList
GO
View 5 Replies
View Related
Jan 31, 2008
Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key.
Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID.
ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField
@publicationID Int=null,@title nvarchar(MAX)=null,@filePath nvarchar(MAX)=null
ASBEGINSET NOCOUNT ON
IF EXISTS (SELECT * FROM Publication WHERE title = @title)SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)SET @publicationID = @@IDENTITYEND
IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)BEGININSERT INTO PublicationFile (publicationID, filePath)VALUES (@publicationID, @filePath)END
View 5 Replies
View Related
Jun 23, 2005
Having a little trouble not seeing why this insert is not happening.... --snip-- DECLARE c_studId CURSOR FOR SELECT studentId FROM students FOR READ ONLY OPEN c_studId FETCH NEXT FROM c_studId INTO @studentId IF( @@FETCH_STATUS = 0 ) BEGIN SET @studRec = 'Found' END CLOSE c_studId DEALLOCATE c_studId
BEGIN TRAN IF (@studRec <> 'Found') BEGIN INSERT INTO students (studentId) VALUES (@studentId) END Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath
View 6 Replies
View Related
Jul 14, 2005
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
what are mine doing wrong ?
Please help
CREATE PROCEDURE DBAuthenticate
(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As
DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )
SELECT
@ID = IdentityCol,
@actualPassword = password
FROM CandidatesAccount
WHERE username = @username and Activate = 1
IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1
GO
View 5 Replies
View Related
Oct 24, 2007
Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table
USE [dw_data]
GO
/****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_address]
(
@TableType INT = null
)
AS
IF @TableType is NULL OR @TableType = 1
BEGIN
IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')
CREATE TABLE [dw_build].[dbo].[address] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[forename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[other_inv_link] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[out_of_district] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[packed_address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_ignore] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_valid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pct_of_res] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postcode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[real_end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[surname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[telephone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[town] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
END
IF @TableType is NULL OR @TableType = 2
BEGIN
CREATE TABLE [dw_build].[dbo].[address_cl] (
[_id] [int] NULL,
[address_1] [varchar](40) NULL,
[address_2] [varchar](40) NULL,
[category] [varchar](7) NULL,
[category_userno] [int] NULL,
[county] [varchar](40) NULL,
[created] [datetime] NULL,
[creator] [int] NULL,
[end_date] [datetime] NULL,
[forename] [varchar](40) NULL,
[notes] [varchar](max) NULL,
[other_inv_link] [int] NULL,
[out_of_district] [bit] NOT NULL,
[packed_address] [varchar](220) NULL,
[paf_ignore] [bit] NOT NULL,
[paf_valid] [bit] NOT NULL,
[patient] [int] NULL,
[patient_date] [datetime] NULL,
[pct_of_res] [int] NULL,
[postcode] [varchar](40) NULL,
[real_end_date] [datetime] NULL,
[relationship] [varchar](7) NULL,
[relationship_userno] [int] NULL,
[surname] [varchar](40) NULL,
[telephone] [varchar](40) NULL,
[title] [varchar](40) NULL,
[town] [varchar](40) NULL,
[updated] [datetime] NULL,
[updator] [int] NULL
) ON [PRIMARY]
END
View 13 Replies
View Related
Dec 22, 2006
Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!
View 2 Replies
View Related
Jul 11, 2007
OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work:
1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record.
So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results.
Here is my code (some parts left out for brevity):1 int result = 0;
2 if (!String.IsNullOrEmpty(icaoCode))
3 {
4 icaoCode = icaoCode.Trim().ToUpper();
5 try
6 {
7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName);
8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName;
9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName);
10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
11 cmd.Connection = mConnection; // connection has been opened already, not shown here
12 cmd.CommandType = CommandType.StoredProcedure;
13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1
14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
15
16 }
17 catch (Exception ex)
18 {
19 result = -1;
20 }
21 }
And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName]
2 -- Add the parameters for the stored procedure here
3 @Id bigint = null,-- Airport Id, OR
4 @Icao char(4) = null,-- ICAO code
5 @AirportName nvarchar(50)
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11
12 -- Parameter checking
13 IF @Id IS NULL AND @Icao IS NULL
14 BEGIN
15 RETURN -1;-- Did not specify which record to change
16 END
17 -- Get Id if not known given the ICAO code
18 IF @Id IS NULL
19 BEGIN
20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao);
21 --PRINT @id
22 IF @Id IS NULL
23 BEGIN
24 RETURN -2;-- No airport found with that ICAO Id
25 END
26 END
27 -- Update record
28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id;
29 RETURN @@ROWCOUNT
30 END
As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).
View 2 Replies
View Related
May 4, 2006
hi there,
i need a procedure that works with C# e.g.:
using (SqlCommand cmd = GetCommand("Procedure_Name"))
{
//i=an array of integer values
cmd.Parameters.Add("@array", SqlDbType.????!?!???).Value = i;
cmd.ExecuteScalar();
}
i need to write a stored procedure that takes as input an array of integers (amongst other values)
this procedure must loop through every integer in the array and INSERT a new record into a table.
i have never used T-SQL before.
Many thanks
View 3 Replies
View Related
Apr 29, 2008
I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:
CREATE PROCEDURE xxxxx
WITH EXECUTE AS 'domainusername'
AS
EXEC msdb.dbo.sp_start_job B'jobname' ;
RETURN
The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.
This worked for the past several months.
Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'
I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.
Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.
Thanks - SM
View 3 Replies
View Related
May 24, 2008
I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.
Thanks for the help.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState]
(@ShippingStateProvince VARCHAR(50),
@ShippingCountry VARCHAR(50),
@StartDate smalldatetime,
@EndDate smalldatetime)
AS
SELECT OrderID,
DateCreated,
DateShipped,
Comments,
Status,
CustomerID,
AuthCode,
Reference,
ShippingCounty,
ShippingStateProvince,
ShippingCountry,
ShippingID,
TaxID,
ShippingAmount,
TaxAmount
FROM Orders
WHERE (DateCreated BETWEEN @StartDate AND @EndDate)
AND (ShippingStateProvince = @ShippingStateProvince)
AND (ShippingCountry = @ShippingCountry)
ORDER BY DateCreated DESC'
END
View 4 Replies
View Related
May 31, 2007
Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDURE fetchpersondetails
AS
CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300),
StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200),
Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
Insert into #tempperson
SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName,
dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress,
dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress,
dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone,
dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2,
N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email,
dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category,
dbo.tblCountry.countryNameFull AS countryaddress
FROM dbo.tblperson INNER JOIN
dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN
dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
please let me know the solurion of this error.
View 2 Replies
View Related
Aug 21, 2005
I am trying to follow an exercise for debugging a stored procedure in the .net IDE. I have had success in adding a connection to the application, until I get the point of where I should set a BreakPoint in the stored procedure "Ten Most Expensive Products" which belongs to the Northwind database. When I open Sever Explorer -->Data Connections- ->WI0001.NorthWind.dbo-->Stored Procedures and Right-click the procedure, the context menu does not give me an edit item to select to open this SP in the editor. Nor can I double-click on the stored procedure to open it either.
View 1 Replies
View Related
Apr 13, 2008
This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T (@PartNo varchar(20), @Wkorder varchar(10), @Setup datetime, @Line smallint, @TT integer, @Tester smallint, @LT1 integer, @LT2 integer, @LT3 integer, @LT4 integer, @LT5 integer, @LT6 integer, @LT7 integer, @LT8 integer, @LT9 integer, @LT10 integer, @LT11 integer, @LT12 integer, @LT13 integer, @LT14 integer, @LT15 integer, @LT16 integer, @LT17 integer, @LT18 integer, @LT19 integer, @LT20 integer, @LT21 integer, @LT22 integer, @LT23 integer, @LT24 integer, @LT25 integer, @LT26 integer, @LT27 integer, @LT28 integer, @LT29 integer, @LT30 integer, @LT31 integer, @LT32 integer, @LT33 integer, @LT34 integer, @LT35 integer, @LT36 integer, @UnitFound integer OUT, @parameters_LamType varchar(50) OUT, @parameters_Shunt real OUT, @parameters_ShuType varchar(50) OUT, @parameters_Stack real OUT, @parameters_Steel varchar(50) OUT, @Partno11 varchar(20) OUT, @Wkorder11 varchar(10) OUT, @Partno12 varchar(20) OUT, @Wkorder12 varchar(10) OUT, @Partno24 varchar(20) OUT, @Wkorder24 varchar(10) OUT, @Partno29 varchar(20) OUT, @Wkorder29 varchar(10) OUT, @Partno34 varchar(20) OUT, @Wkorder34 varchar(10) OUT, --@DL1 integer OUT, --@DL2 integer OUT, --@DL3 integer OUT, --@DL4 integer OUT, --@DL5 integer OUT, --@DL6 integer OUT, --@DL7 integer OUT, --@DL8 integer OUT, --@DL9 integer OUT, --@DL10 integer OUT, @DL11 integer OUT, @DL12 integer OUT, --@DL13 integer OUT, --@DL14 integer OUT, --@DL15 integer OUT, --@DL16 integer OUT, --@DL17 integer OUT, --@DL18 integer OUT, --@DL19 integer OUT, --@DL20 integer OUT, --@DL21 integer OUT, --@DL22 integer OUT, --@DL23 integer OUT, @DL24 integer OUT, --@DL25 integer OUT, --@DL26 integer OUT, --@DL27 integer OUT, --@DL28 integer OUT, @DL29 integer OUT, --@DL30 integer OUT, --@DL31 integer OUT, --@DL32 integer OUT, --@DL33 integer OUT, @DL34 integer OUT) --@DL35 integer OUT, --@DL36 integer OUT)ASSET @Tester = 1WHILE @Tester < 36 BEGIN Set @Line = (Select Line from dbo.location where Tester = @Tester) IF @Line = 453 BEGIN If @Tester = 1 BEGIN SET @LT1 = 453 END If @Tester = 2 BEGIN SET @LT2 = 453 END If @Tester = 3 BEGIN SET @LT3 = 453 END If @Tester = 4 BEGIN SET @LT4 = 453 END If @Tester = 5 BEGIN SET @LT5 = 453 END If @Tester = 6 BEGIN SET @LT6 = 453 END If @Tester = 7 BEGIN SET @LT7 = 453 END If @Tester = 8 BEGIN SET @LT8 = 453 END If @Tester = 9 BEGIN SET @LT9 = 453 END If @Tester = 10 BEGIN SET @LT10 = 453 END If @Tester = 11 BEGIN SET @LT11 = 453 END If @Tester = 12 BEGIN SET @LT12 = 453 END If @Tester = 13 BEGIN SET @LT13 = 453 END If @Tester = 14 BEGIN SET @LT14 = 453 END If @Tester = 15 BEGIN SET @LT15 = 453 END If @Tester = 16 BEGIN SET @LT16 = 453 END If @Tester = 17 BEGIN SET @LT17 = 453 END If @Tester = 18 BEGIN SET @LT18 = 453 END If @Tester = 19 BEGIN SET @LT19 = 453 END If @Tester = 20 BEGIN SET @LT20 = 453 END If @Tester = 21 BEGIN SET @LT21 = 453 END If @Tester = 22 BEGIN SET @LT22 = 453 END If @Tester = 23 BEGIN SET @LT23 = 453 END If @Tester = 24 BEGIN SET @LT24 = 453 END If @Tester = 25 BEGIN SET @LT25 = 453 END If @Tester = 26 BEGIN SET @LT26 = 453 END If @Tester = 27 BEGIN SET @LT27 = 453 END If @Tester = 28 BEGIN SET @LT28 = 453 END If @Tester = 29 BEGIN SET @LT29 = 453 END If @Tester = 30 BEGIN SET @LT30 = 453 END If @Tester = 31 BEGIN SET @LT31 = 453 END If @Tester = 32 BEGIN SET @LT32 = 453 END If @Tester = 33 BEGIN SET @LT33 = 453 END If @Tester = 34 BEGIN SET @LT34 = 453 END If @Tester = 35 BEGIN SET @LT35 = 453 END END SET @Tester = @Tester + 1 ENDSELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS A INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF WHERE B.PARTNO = @PARTNO SET @UnitFound = @@rowcountIF @UnitFound = 0 BEGIN SELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS WHERE PARTNO = @PARTNO SET @UnitFound = @@rowcount END --IF @LT1 = @Line BEGIN SET @DL1 = 1 END --IF @LT2 = @Line BEGIN SET @DL2 = 1 END --IF @LT3 = @Line BEGIN SET @DL3 = 1 END --IF @LT4 = @Line BEGIN SET @DL4 = 1 END --IF @LT5 = @Line BEGIN SET @DL5 = 1 END --IF @LT6 = @Line BEGIN SET @DL6 = 1 END --IF @LT7 = @Line BEGIN SET @DL7 = 1 END --IF @LT8 = @Line BEGIN SET @DL8 = 1 END --IF @LT9 = @Line BEGIN SET @DL9 = 1 END --IF @LT10 = @Line BEGIN SET @DL10 = 1 END IF @LT11 = 453 BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END --IF @LT11 = @Line BEGIN SET @DL11 = 1 END IF @LT12 = 453 BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END --IF @LT13 = @Line BEGIN SET @DL13 = 1 END --IF @LT14 = @Line BEGIN SET @DL14 = 1 END --IF @LT15 = @Line BEGIN SET @DL15 = 1 END --IF @LT16 = @Line BEGIN SET @DL16 = 1 END --IF @LT17 = @Line BEGIN SET @DL17 = 1 END --IF @LT18 = @Line BEGIN SET @DL18 = 1 END --IF @LT19 = @Line BEGIN SET @DL19 = 1 END --IF @LT20 = @Line BEGIN SET @DL20 = 1 END --IF @LT21 = @Line BEGIN SET @DL21 = 1 END --IF @LT22 = @Line BEGIN SET @DL22 = 1 END --IF @LT23 = @Line BEGIN SET @DL23 = 1 END IF @LT24 = 453 BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END --IF @LT25 = @Line BEGIN SET @DL25 = 1 END --IF @LT26 = @Line BEGIN SET @DL26 = 1 END --IF @LT27 = @Line BEGIN SET @DL27 = 1 END --IF @LT28 = @Line BEGIN SET @DL28 = 1 END IF @LT29 = 453 BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END --IF @LT30 = @Line BEGIN SET @DL30 = 1 END --IF @LT31 = @Line BEGIN SET @DL31 = 1 END --IF @LT32 = @Line BEGIN SET @DL32 = 1 END --IF @LT33 = @Line BEGIN SET @DL33 = 1 END IF @LT34 = 453 BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END --IF @LT35 = @Line BEGIN SET @DL35 = 1 END --IF @LT36 = @Line BEGIN SET @DL36 = 1 ENDGO
View 1 Replies
View Related
Apr 25, 2006
Hi,
[I'm using VWD Express (ASP.NET 2.0)]
Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
<asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma"
Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px">
</asp:DropDownList> <asp:Button ID="btnImport" runat="server" Text="Import" />
<asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>"
SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber">
<SelectParameters>
<asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
View 3 Replies
View Related
Mar 4, 2004
Hy all.
My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign)
So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL.
Obviously I'd like to do this:
SELECT * FROM EXEC sp_fkeys @table_name = 'xy'
of course it is not working this way, but I'd like something like this.
Please help! Thanx!
View 3 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jan 29, 2015
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],
[Code] ....
View 9 Replies
View Related
