Cross-database Stored Procedure Stops SQLEXPRESS Service

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


ADVERTISEMENT

Stored Procedure Stops Before Completing

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

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

Receiving Queue Stops Firing Stored Procedure

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

Cross Tab Stored Procedure

Jul 27, 2005

HiI have a table with rows of timestamped values for given variables. Each of the variables are periodically inserted at the same time. ieDateTime      Variable      Value.T1                  V1               xxxxT1                  V2               xxxxT1                  V3               xxxxT2                  V1               yyyyT2                  V2               yyyyT2                  V3               yyyyIn a SQLserver stored procedure I would like to insert this data into a temp table like the following. I don't need summaries just need a table or query where I can select on a variables value. ie "select * from temptable where V3 = yyyy"Datetime      V1         V2         V3T1                 xxxx      xxxx      xxxxT2                 yyyy      yyyy      yyyy any suggestions for an SQL statement.thanks in advance.

View 2 Replies View Related

Conversation Handle Processed By An Activated Stored Procedure Service Can Not Be Invoked By A CLR Service Instance?

Dec 1, 2006

I have a initiator and a target service broker peer.

Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.

Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.

The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.

For this the C# unit test creates a Conversation off of the saved conversation handle:


Service client = new Service("cleintservicename", conn, tran);

Conversation dialog = null;

dialog = new Conversation(client, convHandle);
Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>".
Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR?
If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether?
Any help - greatly appreciated.

View 7 Replies View Related

SQL Server Service Starts Then Stops

Mar 30, 2008

I have just installed SQL Server 2005. The machine had an expired version of SQL Express on it which I removed. Now when i go to open the Management Studio it says it can't connect. When I check the service, it is set to automatic using the local account but is not running. When I start it, it throws an error stating that it started and then stopped. The first error record in the log file states:

Server TCP provider failed to listen on [ 'any' <ipv4> 1433]. Tcp port is already in use.

Could this be causing the problem? How do I check to see what is using 1433? In general, what the heck did I do wrong during the install? One article said something about the new installation thinking it was still the expired version. Could there be something left in the registry from the old version of Express causing this?

Thanks in advance for the help

View 20 Replies View Related

The SQL Server (SQLEXPRESS) Service Terminated With Service-specific Error 10013.

Jul 12, 2007

I have an almost virgin install of SQLExpres running on a WIN2K Pro system.

Have been able to create and connect db to Access 2000 without problem.



Now I wish to extend to remote connections. Using Surface Area Configuration tool, I changed Remote Connections to Local and Remote.

Whenever this setting contains TCP/IP and I try to restart the service I receive the following errors:



System Log:



The SQL Server (SQLEXPRESS) service terminated with service-specific error 10013.



Application Log:



Server TCP provider failed to listen on [ 'any' <ipv4> 0]. Tcp port is already in use.



TDSSNIClient initialization failed with error 0x271d, status code 0xa.



TDSSNIClient initialization failed with error 0x271d, status code 0x1.



Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.



SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.





If I change back to Local Connections Only or Local and Remote using named pipes only, the service starts up again without a problem.



After five days of investigating, researching, reinstalling and waiting I have to ask for help.



Any suggestions or leads?



Thank You in advance.

View 14 Replies View Related

Service Starts Then Stops-Reporting Services

May 3, 2007

Hello,



I tried deploying a Report Model Project and received an error:




"TITLE: Microsoft Semantic Model Designer
------------------------------

A connection could not be made to the report server http://localhost/ReportServer.

------------------------------
ADDITIONAL INFORMATION:

The request failed with HTTP status 405: Method not allowed. (Microsoft.ReportingServices.SemanticQueryDesign)

------------------------------


When I checked services I realised that Reporting Services wasn't started. I tried starting it and received a message:



"The Sql Server Reporting Services (MSSQLServer) started and then stopped. Some services stop automatically if they have no work to do.........."



I also tried to start the service from Configure Report Server and received an error:



"System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed.
at System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout)
at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start)"





Any idea why this is happening? Should I install SP2?







View 3 Replies View Related

Report Server Windows Service Stops Running.

Jan 30, 2007

I have a test server running both 2000 and 2005 Reporting Services. Each day I have to restart the Report Server Windows service for the 2005 Report Server. This is the error I get when I try to access the Report Manager

The Report Server Windows service 'ReportServer$TSQL4' is not running. The service must be running to use Report Server. (rsReportServerServiceUnavailable) Get Online Help

After I start the Report Server Windows service using the Report Services Configuration tool I can access the 2005 Report Manager without errors. The windows service start up type is set to automatic.

I can not find why the 2005 version keeps going down.

View 1 Replies View Related

DataTables In SQLExpress Database Are Not Updating When Using Stored Procedures From Windows Application

Jan 30, 2008

I'm writing a Windows application (Visual Studio 2005, c#) utilizing a local SQLExpress database. It consists of about 10 tables and I've created about 15 Stored Procedures to address various functions... I can run the update Stored Procedures interactively within the designer and the data tables update as designed. However, if I run the update Stored Procedures from within my windows application is where they fail. I get no error messages, if I return a rowcount variable from the Stored Procedure it tells me that one row was updated (SELECT @RtnVal == @@rowcount)... but when I open the subject table in the designer, there is no new data.

My update queries address both insert and update functions, so if it is new row of data, it performs the insert action, otherwise it updates an existing row.

I can query the data using my stored procedures to load default values into my windows form, I can search and find client records to display in the form... I just can't update records. I'm of the opinion that it is a rights issue, but I can't find any resources that address user access accounts with SQLExpress and windows apps. The current connection string for my local database is set for Integrated Security = true; User Instance = true

I've tried two approaches... one utilizes my stored procedure....


bool bSave;
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand("SavAuthTesterData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CtrId", SqlDbType.NVarChar, 20).Value = TestCenterID.ToString();
cmd.Parameters.Add("@AT_Id", SqlDbType.NVarChar, 15).Value = GnuAuthTesterId.ToString();
cmd.Parameters.Add("@AT_Name", SqlDbType.NVarChar, 50).Value = GnuAuthTesterName.ToString();
cmd.Parameters.Add("@AT_Pwd", SqlDbType.NVarChar, 15).Value = GnuAuthTesterPwd.ToString();
cmd.Parameters.Add("@Maint", SqlDbType.NChar, 1).Value = GnuAuthTesterStatus.ToString();
cmd.Parameters.Add("@ModBy", SqlDbType.NVarChar, 15).Value = sAuthTester.ToString();
try
{
conn.Open();
cmd.ExecuteNonQuery();
bSave = true;
}

Then I tried a direct insert...

SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlTransaction myTrans;
conn.Open();
cmd.Connection = conn;
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
string disDte = DateTime.Now.ToShortDateString();
try
{
string sCmdText = "INSERT INTO Tester (CenterId, AuthTesterId, AuthTesterName, AuthTesterPwd, "
+ "CreateDte, Maint, ModBy) "
+ "VALUES ('" + TestCenterID.ToString() + "','"
+ GnuAuthTesterId.ToString() + "','"
+ GnuAuthTesterName.ToString() + "','"
+ GnuAuthTesterPwd.ToString() + "','"
+ disDte.ToString() + "','"
+ GnuAuthTesterStatus.ToString() + "','"
+ sAuthTester.ToString() + "')";
cmd.CommandText = sCmdText;
cmd.ExecuteNonQuery();
myTrans.Commit();
bSave = true;
}


Both fail... which points to the only common point in both approaches, the connection string and user rights... but I can't find any place to address user accounts or configurations for windows apps.

Thanks for whatever help you can offer...

Jim-

View 5 Replies View Related

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related

Can Report Service Run A Stored Procedure?

Jul 16, 2007

Hi, there,





I have a report that requires to join a lot of tables.


May I ask if I can use a stored procedure to make all the joins and populate a temp table, then make the report sit on the temp table. so every time the user runs the report, it will kick off the stored procedure which will truncate the temp table and repopulate it, then show the report.





Thanks

View 4 Replies View Related

Service Broker: Cross DB, Same Instance

Jun 14, 2006

I'd like to set up a Service Broker queue in one database (dbRespond) on a server so that events in another database (dbEvent) on the same server instance can post messages to the queue. The problem I'm having is that:

The BEGIN DIALOG CONVERSATION needs to reference a Contract that is in the current database, and I want to call BEGIN DIALOG CONVERSATION from dbEvent
The target service is in dbRespond. Based on the "Hello World" Service Broker example that comes with SQL 2005, in dbRespond I need to specify the contract in the CREATE SERVICE call that creates the target service. Here, too, the contract must be defined in the current database.
How do I deal with needing to have the same one contract in two different databases?

View 5 Replies View Related

Calling Web Service In Stored Procedure (SQL 2005)

Aug 18, 2006

I need to create a stored procedure (SQL 2005). I did it in C# (Visual Studio). The .dll file (inside) uses web services of Project Server 2007 (I just need to log on, create a project and log out). I prepared 2 files (exactly the same code): dll an exe. the .exe works fine. the problem is with .dll file (stored procedure in SQL)...

by the way: I created serialization file with sgen tool.

the problems start when I am trying to register these 2 .dlls in SQL server. I can do that in unsafe mode only. when I am trying to register them in externall access (normal dll) and safe mode (serialization .dll) I am getting errors:

CREATE ASSEMBLY failed because method "add_QueueCheckInProjectCompleted" on type "ConsoleApplication11.ProjectWebSvc.Project" in external_access assembly "ConsoleApplication11" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies

thus I did it in unsafe mode (both of them). than I created the procedure.

Now, I tried to call that procedure. I am getting error:

A .NET Framework error occurred during execution of user defined routine or aggregate 'Main':

System.Net.WebException: The request failed with the error message:

--

<html><head><title>Object moved</title></head><body>

<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>

</body></html>

--.

System.Net.WebException:

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at ConsoleApplication11.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)

at ConsoleApplication11.Program.Main()

That means "Object reference not set to an instance of an object". the problem occurs when calling QueueCreateProject method

I dont understand that error. I wrote the same code for .dll and .exe. the .exe file is working ok (no errors) but .dll (as a stored procedure) shows this error



my code in C#:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Net;

using PSLibrary = Microsoft.Office.Project.Server.Library;

namespace ConsoleApplication11

{

public class Program

{

public static void Main()

{

const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";

string baseUrl = "http://localhost:22278/projectserver/";

CookieContainer cookies = new CookieContainer();



LoginWindowsWebSvc.LoginWindows loginWindows = new LoginWindowsWebSvc.LoginWindows();

loginWindows.Url = baseUrl + LOGINWINDOWS;

loginWindows.Credentials = CredentialCache.DefaultCredentials;

loginWindows.Login();

ProjectWebSvc.Project project = new ProjectWebSvc.Project();

project.Credentials = loginWindows.Credentials;

project.Url = baseUrl + "_vti_bin/psi/project.asmx";

ProjectWebSvc.ProjectDataSet dsProject = new ProjectWebSvc.ProjectDataSet();

ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = dsProject.Project.NewProjectRow();

Guid projectGuid = Guid.NewGuid();

projectRow.PROJ_UID = projectGuid;

projectRow.PROJ_TYPE = 0;

projectRow.PROJ_NAME = "Jakis";

projectRow.PROJ_SESSION_UID = Guid.NewGuid();

dsProject.Project.AddProjectRow(projectRow);

Guid jobGuid = Guid.NewGuid();

project.QueueCreateProject(jobGuid, dsProject, false);

System.Threading.Thread.Sleep(5000);



loginWindows.Logoff();

project.Credentials = null;

}

}

}





any help will be appreciated. my email is: stro.na@interia.pl

View 4 Replies View Related

Error Deploying A CLR Stored Procedure That Uses A Web Service

Aug 14, 2007



Ok, first some background.


I am writing my first (complex) CLR Stored Procedure using Visual Studio 2005.

This SP worked fine until I added code to make a web service call. That web service is a wrapper web service I created because the actual web service I need to call uses System. Web.Extensions which was not available in my VS2005 Database Project.

At first I was getting the standard "External Access Assembly" errors, so I created a new user (was using SA) and assigned database ownership to the new user, then assigned permissions to that user. This worked to get it deployed, but I get the following error when its run:

<code>

System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)

at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)

at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)

at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)

at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)

at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, S
</code>

Anyone have any ideas?

Thanks!

Dave Borneman
Solution Architect,
anyWare Mobile Solutions.

View 1 Replies View Related

Calling Asp.net Web Service From A Trigger Or Stored Procedure

May 6, 2008



Hi,
I need to call a webservice directly from a tigger or stored procedrue instead of creating a window service to read from a table then call the webservice .Is it possible ? if yes, please i need your support.
Thanks,

View 1 Replies View Related

Service Broker: Cross DB, Same Instance - Part 2

Jun 21, 2006

Hi,

I run the code sample below in a SQL Mgt Studio query window. I get no error messages. However, the two SELECT statements at the end produce undesireable results (see comments). My messages to not make it to the target queue; they get stuck in sys.transmission_queue with the error message in the final comment of the code. The server principal "ddddXXXXX" is a Windows Active Directory account that: 1) is a domain admin in the domain that my SQL Server box is a member server of, 2) is the login used by the "SQL Server (MSSQLSERVER)" service account, and 3) is a member of the SQL Server instance's sysadmin fixed server role.

What is it I'm missing that is denying a sysadmin login access to a database?

/***
Service Broker try 1
***/
USE master;
GO

CREATE DATABASE sbRespond;
GO

CREATE DATABASE sbEvent;
GO

USE sbRespond;
GO

CREATE MESSAGE TYPE ActivatedStudyMessage
VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT ActivatedStudyContract
(ActivatedStudyMessage SENT BY INITIATOR);
GO

CREATE QUEUE [dbo].[ActivatedStudyTargetQueue];
GO

CREATE SERVICE ActivatedStudyTargetService
ON QUEUE [dbo].[ActivatedStudyTargetQueue]
(ActivatedStudyContract);
GO

USE sbEvent;
GO

CREATE MESSAGE TYPE ActivatedStudyMessage
VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT ActivatedStudyContract
(ActivatedStudyMessage SENT BY INITIATOR);
GO

CREATE QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO

CREATE SERVICE ActivatedStudyInitiatorService
ON QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO

-- Send a message.
USE sbEvent;
GO

BEGIN TRANSACTION;
GO

DECLARE @message XML;
SET @message = N'
<message>
<PROT_ID>123456</PROT_ID>
<StudyID>AAAA1234</StudyID>
</message>
';

DECLARE @conversationHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE ActivatedStudyInitiatorService
TO SERVICE 'ActivatedStudyTargetService'
ON CONTRACT ActivatedStudyContract
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE ActivatedStudyMessage
(@message);

END CONVERSATION @conversationHandle;
GO

COMMIT TRANSACTION;
GO

USE sbRespond;
GO

SELECT * FROM [dbo].[ActivatedStudyTargetQueue];
GO
--The query above returns zero records

SELECT * FROM sbEvent.sys.transmission_queue;
GO
--The query above returns two records, each with this "transmission_status" value:
--An exception occurred while enqueueing a message in the target queue.
-- Error: 916, State: 3.
-- The server principal "ddddXXXXX" is not able to access the
-- database "sbRespond" under the current security context.

View 3 Replies View Related

Service Broker Problem In Executing A Stored Procedure

Jan 3, 2008



Hi,

i am having a problem with service broker. here is what i have

i have two databases (DB1 and DB2) and both are on seprate servers (seprate instances)
in DB1 i am putting raw data from plc using rssql.
once data gets into the table, i am executing a trigger which bind the data and send it to the target service.
in the target service i have a activated stored procedure which unbind data and then call a stored procedure which is on in DB2( i am using Linked Server ). on DB2 i am doing really really complex calculations thats why i have to use service broker to seprate the calculation to data input for performance.

i ran this query on both databases so i won't have to create the certificate.
(ALTER DATABASE MyDatabaseName SET TRUSTWORTHY ON)

now my problem is, its not executing the stored procedure from DB2 and disabling the service everytime i enter the data into rawdata table(DB1).
But if i run the stored procedure manuly from TSQL it works fine.

can you guyz help me out on this issue.

Thanks.




View 13 Replies View Related

Cannot Start SQLExpress Service

Apr 21, 2008

I had a working VS Express 2005 system 2 years ago and have not done any development on it since. Recently I wanted to enhance the application I had developed and so updated my system to VS EXpress 2008 and now can't start my database service. What do I need to do to get access to my database again?

I am running on an up to date Windows XP Pro system




Dennis J.

View 9 Replies View Related

SQL2005 + Service Broker + CLR Stored Procedure + XML + Temp Tables

Feb 8, 2008

This is killing me...and not slowly.

I have a stored procedure (a) which calls another stored procedure (b).

Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements

If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.

But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.

Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable

- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.

The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.



----------------- UPDATE --------------
I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.

If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.

View 3 Replies View Related

Raiserror In Service Broker Internal Activation Stored Procedure

May 17, 2006

I use Try ... catch blok in my activation stored procedure. When SQL Server raise error (e.g. Primary key violation) in Try blok, XACT_STATE in Catch blok has value 1 = commitable transaction and I can use rollback transaction to savepoint. But when I use Raiserror() in Try blok, XACT_STATE in Catch blok has value -1 = uncommitable transaction and I can't use rollback transaction to savepoint. When I drop automatic activation for given queue and I run this stored procedure with Raiserror(), XACT_STATE has value 1 = commitable transaction.

What a problem may cause this different behavior ?

Best Regards,
Pavel

View 4 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

How To Access Analysis Service 2005 Cubes Through Normal Stored Procedure

Apr 2, 2007



Hi,



Can anybody tell me How to access Analysis Service 2005 cubes through normal Stored Procedure.



Basically can write a stored procedure that we normally write in database service and use it access the Analysis Service 2005 cubes.



Is it possible



Regards,

gokul

View 1 Replies View Related

SQLEXPRESS Service Started And Then Stopped! Plz Help, Its Urgent!

Mar 9, 2008

Hi,
My SQLEXPRESS service suddenly gives me this error.
ERROR: The SQL Server(SQLEXPRESS) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
My OS is Microsoft Windows Server 2003, Enterprise Edition, SP2.
It all started when I restarted the system prompted by the Windows updates.
Please help. Its urgent as this is one of the important servers to be managed.
Thanks and Regards
Madhu

View 3 Replies View Related

SQL Server 2005 Service Broker - Activation Stored Procedure Memory Usuage

Nov 9, 2007

We are looking for some guidance with an issue we have picked up with our implementation of Service Broker here on the ABSA Capital project and I am hoping you can help or point us in the direction of someone.

The architecture we have implemented for service broker is to make use of an Activation stored procedure on two queues (1 SP per queue) to process the messages received. What we have found is that the activation stored procedure runs on a background session and its CPU time and memory just grows to the point where it brought one of our UAT servers to a grinding halt.

Is there anyway we can reduce the memory consumption of the activation stored procedure or is this one of those things that still need to be ironed out in Service Broker?

View 6 Replies View Related

Dump Database Task Stops SQL Executive

Aug 18, 1998

We are running sql 6.5 with Serv. pack 4. All of our Prod. database are backedup weekly via scheduled tasks. On occasion the dump of a particular database will cause 1 of 2 things:
1. The db dump seems to work correctly but stops sql executive service.
2. The db dump seems to work correctly but sends an alert email msg to me indicating that it failed. The msg in the email is "NO Message".

Has anyone experienced either of these 2 problems or have any solutions?

View 1 Replies View Related

SQLEXPRESS Backup File Losing NETWORK SERVICE User When Copied

Mar 11, 2008

I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:


srv = New Server("MYPCSQLEXPRESS")

db = srv.Databases("washmaster")

Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)

Dim recoverymod As RecoveryModel

recoverymod = db.DatabaseOptions.RecoveryModel

rs.NoRecovery = False

rs.Devices.Add(bdi)

rs.Database = "washmaster"

rs.ReplaceDatabase = True

srv.KillAllProcesses("washmaster")

rs.SqlRestore(srv)


This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:

....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)

The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.

How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?

Thanks,
SJonesy

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Multiple Database / Cross Database Referencial Integrity (foreign Keys)

Feb 12, 2008

Using SQL Server Express 2005, I have two databases.  AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables.  Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky?  (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database.  Maybe I should logically separate my databases this way?  Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB.  Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing.  Thanks,Adam Nofsingerucnmedia.com

View 2 Replies View Related

Inserting Record Into Second Database From A Stored Procedure In First Database

Aug 10, 2005

is it possible to insert record into second database from a stored procedure which is in first database?

View 1 Replies View Related

How Can I Identify The Caller's Database From A Stored Procedure In Another Database?

Apr 2, 2008

I am wondering if there is a way to create a procedure in a logging database that can identify the source database from which it is invoked. DB_NAME() of course returns the name of the database in which the stored procedure exists. I could pass the database name as a parameter to the proc, just wondering if there is another way.

This is SQL 2005, I did look into the sys.dm_exec views but nothing seems to have a dbid reflecting the calling context.

Thanks,
Mike

View 4 Replies View Related

How To Run A Stored Procedure Located In One Database Against Another Database

Feb 14, 2008



I have a stored procedure that is located in one database and I would like to have it execute against a different database. My problem is when I go to execute it, it is still executing against the database it is stored in. Is it possible to tell this stored procedure when it runs to execute its code against this second database?

This is what I have now, which isn't working:




Code Snippet

use [Database2]
exec [Database1].[dbo].usp_SetupDatabaseUser





The end result still executes against Database1.

Thanks for any advice,

Flea#

View 9 Replies View Related







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