I have 18 odd subscribers at the moment, my publisher and disttribution is on the same machine with push subscriptions.
The questions I have
nr 1.
While trying to initialize new subscribers I get loads of deadlocks even after I stop dist cleanup agent. This *I think* cause some other unexpected problems.
nr2.
The queue reader would fail saing it cannot find the "insert" proc on the publisher, although it exists.
I have changed anything on the publication so I'm not sure how this happens or why.
nr3.
I replicate a varbinary(max) column and on the odd occasion get the "Length of LOB data" errors which I then set with sp_configure. The catch here is that the length never exceeds a "len()" of 4000, thus the reported LOB and my calculation doesn't tie up.
col1 col2 col3 col4 (No column name) A123 Test 1 Test 1 Y N 0 Y N 0 A125 Test 1 Test 9 Y N 0 N Y 0
but what I would like is col 1 populated As A123 until it hits A125 then populated A125 etc. I was thinking about using the iff but not getting anywhere fast.
hello im trying to update the notification by yes or no using stored procedure when the email is sent to the user. the sending o email is being accomplish using SendEmail Class. the send email class will return success and error if success or error the stored procedure will run and update the notification.
My question is how can i get the return value of that SendEmail class and how can i connect those two task? Please Help..
I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.
To continue with the set up, here is the stored procedure in question: CREATE PROCEDURE [dbo].[HE_GetStks] AS
select top 15 Rating, rank, coname, PriceClose, pricechg, DailyVol, symbol from
(selectf.rating, f.rank, s.coname, cast ( f.priceclose as decimal(10,2)) as PriceClose, cast ( f.pricechg as decimal(10,2)) as pricechg, f.DailyVol, f.symbol from dailydata f, snames s where f.tendcash = 0 and f.status = 1 and f.typ = 1 and f.osid = s.osid) tt order by rating desc, rank desc
GO
The code in the calling bat file is: REM ************************* REM BCP .WRK FILE REM ************************* bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.
I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.
I'm using a stored proceedure which should update a number of rows in atable depending on a key value supplied (in this case 'JobID'). Butwhat's happening is when I call the proc from within the program, onlyone row gets updated.SoWhen I call the proc from Query Analyser, all rows get updated.When I call the proc from within the program, only one row gets updatedAny ideas as to why this is happening??JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 073412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0^^^^Only one record gets updated, so the table ends up being...JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 473412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0Public Sub UpdateAllItems() As BooleanDim objCnn As ADODB.ConnectionDim objCmd As ADODB.CommandSet objCnn = New ADODB.ConnectionWith objCnn.ConnectionString = cnConn.CursorLocation = adUseClient.OpenEnd WithSet objCmd = New ADODB.CommandSet objCmd.ActiveConnection = objCnnWith objCmd.CommandText = "sp_UpdateJobItem".CommandType = adCmdStoredProc.Parameters.Append .CreateParameter("@Status", adInteger,adParamInput, 4, Me.Status).Parameters.Append .CreateParameter("@JobID", adInteger,adParamInput, 4, Me.iJobID).ExecuteEnd WithSet objCnn = NothingSet objCmd = NothingEnd Sub-----------------------------------------------------------------SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE dbo.sp_UpdateJobItem@JobID As int, @Status As intAS--================================================== ===========================================SET XACT_ABORT OFF -- Allow procedure to continue aftererrorDECLARE @error integer -- Local variable to capture theerror OnHoldAction.--================================================== ===========================================BEGIN TRANSACTIONUPDATE tbl_JobItemsSET Status = @statusWHERE JobID = @JobID--================================================== ===========================================-- Check for errors--================================================== ===========================================SELECT @error = @ERRORIf @error > 0BEGINROLLBACK TRANSACTIONENDElseBEGINCOMMIT TRANSACTIONENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
Hi all,I've a problem with sql server 2000 EE.I've a DB with a table with lots of records. I run a SELECT fromquery analyzer, and I've results in 10 seconds. Perfect.When I run the query two times in the same table, seems likeit been queued. Result in 20 seconds.With another query, I've result in 40 seconds. If I run this query 2times, I've results in 80 seconds.How can it be? Does Sql Server queue the query?thanks, and sorry for bad english! :D
When multiple readers are waiting on a message from the same queue, I would expect that the reader that has been waiting the longest would be the first to pick up a message. However, I'm shocked to discover that the opposite seems to be true; in my tests I'm showing that the reader that has been waiting the least time picks up a message first! This seems totally counter-intuitive, and I'd like to know why it's working this way. This implementation will cause a lot more reader timeouts to occur than a properly queued method. For instance, assume that I have two readers, each using a one minute timeout. Reader #1 starts waiting, and reader #2 is busy for another 20 seconds before it starts waiting. 39 seconds later a message comes in and reader #2 will pick it up, leaving reader #1 to time out one second later! I would much rather have reader #1 pick up the message and reader #2 continue to wait for 20 more seconds.
I'm considering filing a bug on Connect about this, but I thought I'd post here first and see if I can get an answer...
Following is the script I'm using to test:
---Setup / Window #1---
-------------------------------------- CREATE DATABASE SimpleSSB GO
USE SimpleSSB GO
--Create a database master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'onteuhoeu' GO
--Create a message type CREATE MESSAGE TYPE Simple_Msg VALIDATION = EMPTY GO
--Create a contract based on the message type CREATE CONTRACT Simple_Contract (Simple_Msg SENT BY INITIATOR) GO
--create a queue CREATE QUEUE Simple_Queue GO
--Create a service CREATE SERVICE Simple_Service ON QUEUE Simple_Queue (Simple_Contract) GO --------------------------------------
---Go start the other windows now---
---Readers: Windows #2-n ---
-------------------------------------- USE SimpleSSB GO
WAITFOR ( RECEIVE * FROM Simple_Queue ), TIMEOUT 300000
--------------------------------------
---Start at least two readers, then do--- --------------------------------------
--send a message... DECLARE @h UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @h FROM SERVICE Simple_Service TO SERVICE 'Simple_Service' ON CONTRACT Simple_Contract WITH ENCRYPTION=OFF;
SEND ON CONVERSATION @h MESSAGE TYPE Simple_Msg GO
--------------------------------------
... the last reader you've started will pick up the message first. Note I'm testing on 9.0.3033, in case that matters.
What command can I issue from query analyzer to list tasks in the queued_tasks table. I would like to see tasks assigned to be run, completed, running, and error.
1)What is the difference between Queued Updating & Immediate Updating in Transactional Relplication.
2)What is the difference between Meged Replication & Peer to Peer replication.Because in both each node will Act as Publisher/Subscriber.So load balancing is possible both type replication na?
3) how to replicate views,stored procedure & functions.Beacuse when applying intial snapshot the copy is getting in subscriber.But afterwards whatever changes occurring in view,procedure not propagating from publisher to subscriber.what to do in this case
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
I am using BULK INSERT to import some pipe-delimited flat files into a database.
I am firstly converting the file using VB.NET, to ensure each line of the file has a carriage return (by using streamwriter.writeline), and I am also ensuring there is no blank line at the end of the file (by using streamwriter.write).
Once I have done this, my BULK INSERT command appears to work OK. This is how I am using the statement:
BULK INSERT tempHISTORY FROM 'C:TEMPHISTORY.TXT' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = ' ' )
NB: The first row in the file is a header row.
This appears to work OK, however, I have found that certain files seem to miss the final line of the file! I have analysed these files incase they have an inconsistant number of columns but they don't.
I have also found that if I knock off the last column of the tempHISTORY table, the correct number of rows are imported. However, of course, I can't just discard one of the columns from the file, I need to import the entire file.
I cannot understand why BULK INSERT is choosing to miss the final line in the file, when the schema of the destination table matches the structure of the file.
This is my syntax which looks valid to me, but the message will never send, need to get the message to send? (@recipients & @from_address are junk here, but valid email addresses in my procedure)
I have a problem replicating data from Subscriber to Publisher.
I configured Queued updatable subscriptions in transactional replication. Publisher is on SQL Server Standard edition and Subscriber is on SQL Server Express edition, both on Windows XP.
When I insert/update data in publisher, the data is replicated to subscriber. If I insert/update data on subscriber the data is not replicated. When I see the replication monitor for errors, it shows an error "Cannot load a DLL xprepl.dll or one of its resources".
Could any one give me a solution or please tell me if I missing something?
I want to send an email twice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.
How can i find that "Mail queued" now i should commit.
I have an After Update Trigger specified on a database table to notify specific users via email when certain customer values are modified. (This is a HACK / Workaround for functionality that doesn't exsist in the product.)
I had be using xp_sendmail without a problem. I recently upgraded the database server to SQL2005 and wanted to try sp_send_dbmail. (I was interested in the asynch and the non-MAPI nature of Database Mail.)
This is the new code:
EXEC @retval = msdb.dbo.sp_send_dbmail
@recipients = @rec,
@blind_copy_recipients = @bcc,
@subject = 'Important Customer Information Change',
@body = @body
This is the old code:
EXEC @retval = master.dbo.xp_sendmail
@recipients = @rec,
@blind_copy_recipients = @bcc,
@subject = 'Important Customer Information Change',
@message = @body
The problem now is that the application reports an update failure because the sp_send_dbmail return the message "Mail Queued."
I don't know if there's a way to suppress this message or not. Or some other way to indicate that "all is well" even though the stored procedure returned a message.
I am converting an old Access/Forms application to a .Net/SQL intranet site. The data was imported and most CRUD features are working. But, I have some data issues to contend with.For this example I have two tables, Cases and Parties. The Parties table includes a Column ReportID, which happens to reference the primary key of some unique Case. Lets say Bob, Sally, and Mary are all associated with ReportID = 2.Looking at the Case table, I see indexes (ReportID) 1,3,4,5, such that Bob, Sally, and Mary are orphaned with out a case to reference.
1. Is it possible to bypass the auto-increment for the primary key of Cases and manually inject the missing Case (ReportID) with a specific primary key of 2? See my image to fill in some of the details.It is too early to tell but perhaps 10% of the Parties are orphan without an associated ReportID in the Case table. This fact is one of the reasons it was decided to rebuild the application, which was also never able to produce any reports.
2. What is an easy way to find all the orphan Parties?
I suppose I could export the Cases table to excel, add the missing Rows, and then overwrite the defective Cases table data with the corrected data. I would rather learn to fix the data issues using the responses from this request.
given a variable @requestID and @session ID, I need to move requests from a holding table to the request table using the generated request ID.
In a perfect world...
INSERT INTO Requests(ReqID, field1, field2) VALUES (SELECT @requestID AS RID, field1, field2 FROM holding WHERE session = '1234')
So that all 5 or so rows from the holding table having a session ID of 1234 get transfered to the request table using the variable value @requestid as the value satisfying ReqID.
I want to use SSIS in order to synchronize data. The OLEDB and ADO.NET Destination Adapter just inserts the rows read from the source. Since I have PK Constraint on destination, the tranform fails. How can I ask to update the records in destination DB?
We have a ASP.Net 2.0 web application and need to send out emails. We have an EmailQueue table in our database where email gets stored before it is send out. We are looking at the pros and cons of using Database Mail in SQL Server 2005 to send out our emails. Should we use SQL or our web app to send email???Tips, tricks, articles, experiences, opinions are greatly appreciated. Newbie
I want to send an email two ice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.
How can i find that "Mail queued" now i should commit.
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
hi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn); cmdStoreTrans.Parameters.Add("@ImportID",importId); cmdStoreTrans.Parameters.Add("@ProfileID",profileId); cmdStoreTrans.Parameters.Add("@RowID",i); try { conn.Open(); cmdStoreTrans.ExecuteNonQuery(); conn.Close(); } catch(SqlException ex) { throw(ex); }I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm
Hi, I'm fairly new to strored procedures. what I need to do is create a new row with an INSERT specifying value A as a param ( so far no problem ) and value B as a value from table2. Is this possible? Thanks for your help Raif
I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.
I created a test in MS Access and it loooks like this:
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID ) SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID FROM Country
This works great in Access but not in SQL Server. In SQL Server 2 = @PatientTripID
I can't seem to get this stored proc to run properly. It won't let me insert records into it. Any suggestions? Can someone tell me if something doesn't look right? I've been over this a million times!
CREATE PROCEDURE TEST_PROC
/* My Name */
AS
BEGIN
SET NOCOUNT ON
DECLARE @MAVG2003 AS MONEY
SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK) LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK) ON OH.CUSTOMER_ID = C.CUSTOMER_ID LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK) ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK) ON P.PRODUCT_ID = OD.PRODUCT_ID WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')
INSERT INTO #FORECAST SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR, 0, 0 FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK) LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK) ON OH.CUSTOMER_ID = C.CUSTOMER_ID LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK) ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK) ON P.PRODUCT_ID = OD.PRODUCT_ID WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003' GROUP BY C.CUSTOMER_ID
UPDATE #FORECAST SET FORECAST_2004 = TOTAL_2003 * (TOTAL_2003 / @MAVG2003)
UPDATE #FORECAST SET FORECAST_2005 = FORECAST_2004 + (FORECAST_2004 * .0105)
SELECT CUSTOMER_ID, TOTAL_2003, FORECAST_2004, FORECAST_2005 FROM #FORECAST ORDER BY TOTAL_2003 DESC
Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.
Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".
The common field in both SELECT and INSERT is phonenumber.
quote:PROCEDURE [dbo].[usp_select_and_insert]
@name varchar(20),
AS
SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');
GO
Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)
exec('bulk insert SCORPIO_STAGE_BULK_DATAPDCC from ''\shodbs29CDRDataonmech_stat_apd_clark_credit.dat'' with (formatfile = ''\dixdbs01ScorpioBulkDATAPDCC.fmt'')')
This is a stored proc with execute as a SQL user. It runs one bulk insert. The user bulk_insert_test_jcb does have BulkAdmin rights and if the user is logged in directly to the server, this works fine. If a SQL user is logged in and runs it (a user other than bulk_insert_test_jcb), this also works
However, if I run this as a windows user logged into the server
alter database stage_scorpio_bulk_jcb set trustworthy off
exec jason_test
--Msg 4834, Level 16, State 4, Procedure jason_test, Line 4
--You do not have permission to use the bulk load statement.
I expect this because the server-level permissions (bulk) are stripped off unless the database is trustworthy, so...
alter database stage_scorpio_bulk_jcb set trustworthy on
exec jason_test
--Msg 4861, Level 16, State 1, Procedure jason_test, Line 4
--Cannot bulk load because the file "\shodbs29CDRDataonmech_stat_apd_clark_credit.dat" could not be opened. Operating system error code 5(Access is denied.).
Why does this happen? I thought that, since I'm executing as a SQL user, SQL Server would authenticate over to the server with the datafiles as the service account, but I see the following in the log at SHODBS29
--User Logoff:
-- User Name: ANONYMOUS LOGON
-- Domain: NT AUTHORITY
-- Logon ID: (0x0,0x4C99BD2F)
-- Logon Type: 3
--
--
--For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Any ideas? It seems as if it is still trying to used windows authentication even though the stored proc is supposed to execute as a SQL user.
Someone in another forum said that ownership chaining not being allowed for bulk operations was the problem, but I don't think so, since if I put an "execute as user='bulk_insert_test_jcb'" into the exec string, it still fails with the same issue.
Hi,Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML?? Can anyone tell me what is the best option for me?Thanks,C
I have a code to update an access database from one of my dataset tables, but i want to insert columns manually
currently i am using this code to update my db
Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(dtadpt)
AccessConn.Open()
dtadpt.Update(DataSet, "recordsforupdate")
AccessConn.Close()
this code automatically inserts all the columns of dataset table to the access database table.
what i want is to inset the values for eg. in col4 of my dataset table into col5 of my access table, for that i want to manually use "inset into" statement to update the db table, but i am having problem with the syntax for using dataset..can anyone help please
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC SET LKC.combo = lockCombo2 FROM [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
Hi, How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[showtube_addNewSUser] -- Add the parameters for the stored procedure here @UserId uniqueidentifier, @FirstName nvarchar(32), @LastName nvarchar(32), @DescShort nvarchar(256), @DescLong ntext, @ClanID uniqueidentifier AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID) values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID); END
I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.
I have the following stored procedure that inserts records and updates the new record. The parameter @rpt_id has a value of -1 when entering the procedure. It needs to be updated with the new record if (identity) once the record is inserted, bu sometimes the update does not happen. The new records ends up with -1 in the rpt_id column.
I have included the stored procedure. I will appreciate any ideas?
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter. ----------------------------- declare @stringSQL varchar(200) select @stringSQL= 'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0' print @stringSQL_GetUserName exec (@stringSQL_GetUserName) select * from temp1 ------------------------------ above dynamic query works fine. Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang... Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
