Problems Executing A SELECT Inside A TRAN Against Other Computer
Nov 10, 2006
Hi
I have a problem executing a SELECT inside a TRAN against other computer
For example:
IN THE SQL Query Analizer of the COMPUTER2
1) this runs OK
BEGIN TRAN
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
COMMIT TRAN
2) this runs OK
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
3) this runs OK
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
4) this runs bad
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN
The problem is that TABLE locks and it does not finish.
I've been looking for similar ERRORS in Microsoft Support but I found nothing
I've uninstall and install de SQL server 2000 SP4 and the problems continues the same
I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.
My reuierement is to execute a bcp to write the query output to a flat file. I am using the following syntax...
exec xp_cmdshell 'bcp "SELECT top 50 * tbl_xyz" queryout tbl_xyz_output.txt -c -S abc -U x -P x'
But I am getting the following error....
output --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CTLIB Message: - L6/O8/S5/N3/5/0: ct_connect(): directory service layer: internal directory control layer error: Requested server name not found. Establishing connection failed. NULL
This bcp executes perfect if I am going to call from command prmomt.
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
CREATE PROCEDURE SelectCostumers @name varchar(100) Declare @SQL = "SELECT Id, Name FROM Costumers" AS IF (@name IS NULL) @SQL ELSE @SQL += "WHERE Name LIKE @name"
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
I am running an Execute SQL task that does a Begin Tran, then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.
Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?
I want to rollback my t-sql if it encounters an error. I wrote this code:
begin tran mytrans; insert into table1 values (1, 'test'); insert into table1 values (1, 'jsaureouwrolsjflseorwurw'); -- it will encounter error here since max value to be inputted is 10 commit tran mytrans;
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended.
The statement has a select with an imbedded select and when I execute it I get the error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Thia is a crude sample of the statement
SELECT ID + ( select * from tableB where TableB = 0 ) as result1 FROM TableB
Why am I getting this error and how do I fix the statement? thanks
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND
iam trying to rerieve a certain value from one table and i want to use that vaue inside a UDF iam usinf a table valued function as i have to retireve no of values Can i do something like this to retrieve the value
SET @Value=Select Value from Table WHERE xyz='some no.' as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
Hi All, Can we use the while loop inside a select statement? Meaning, something like this:
Code Block SELECT DATE, WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23 (SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) ) FROM SC_DATEDIMENSION_TABLE
What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right.
Hi, can you add a counter inside a select statement to get a unique id line of the rows? In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order. This is the normal way: SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements This is what i want: DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1) Anyone know if this can be done?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query.
So! Here's the statement that works:
====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END
CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate
====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea).
Is it possible to encapsulate this inside a select statement?
Is it normal practice to check for @@ERROR after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.
Hi there, I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results. Here is my basic structure:
Hello All!! Below is a part of my code. I need to find out if I could execute the below statement.
select @string_sortorder = 'select max(sortorder) from geo_lineobj where pipeid = ' + str(@geo_pipeid) select @string_sortorder exec @string_sortorder @max_sortorder
When I select the @max_sortorder the value I'm getting is a null. The sortorder is an int datatype and the @geo_pipeid is also an int datatype. I need to to get the @max_sortorder to continue my coding. Does anyone know why I'm getting a null @max_sortorder?
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
I have multiple SqlDataSources on a page. I only want them to execute the select query when the user performs a certain action. Is this possible? Or do they all execute as soon as the page is loaded?
Hi, Please help me in this problem... i am new to sql server.. i am using sql server version 8...(doesnot support function with retun values..) so i have created a procedure... -----------procedure------------------(to find next monday after 6 months)------------------- [code] create proc next_Monday ( @myDate DATETIME ) as BEGIN set @myDate = dateadd(mm, 6, @myDate) while datepart(dw,@myDate) <> 2 begin set @myDate = dateadd(dd, 1, @myDate) end select @myDate end go [/code] -------------------------------------------------------- i can able to execute this procedure separately.... working well... but don't know how to call it inside another query.... the following throws error.... select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster please help me... how to fix this problem...
VIEW behaviour depending on what operation is run on it.
VIEW syntax is CREATE VIEW xx AS SELECT ... It would be very convinient to know wether a select, update, insert or delete is the operation executed on it so the view could take action accordingly, ie CREATE VIEW xxx AS SELECT .. CASE UPDATE THEN do_this CASE SELECT THEN do_that etc. Does anyone know if this is possible?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
