I'm having some weird problems with calling remote stored procedures.
Two servers, both windows 2000 sp2, both running sql 7 version 7.00.961.
Server A is actually a cluster, but server B is a development server and not a cluster.
First off, server B can link to server A, but server A can't link to server B. We get a can't create connection error. We've used the client network utility to specify tcp/ip for both. I'm using the link settings as SQL Server connection (rather than an odbc connection) mapped to the sa user id and password. The password is blank on server B though.
I can run a remote stored procedure on server A from server B fine. I get the proper response back too. I can select anything from any database on A from B without trouble.
But here's my problem: If I use exec @result=ServerA.MyDB.dbo.myproc from server B, @result is always null. If I run MyDB.dbo.myproc from server A directly, @result comes back with the proper value.
I tried using an OUTPUT argument but it has the identical results. If I run it on server A, it works. If I run it from server B, the output argument is null.
Just for grins, I tried setting the @result value to 0 before calling MyProc. It didn't come back null, but it came back as the same value I set it before calling the procedure.
I verified that the sqlagent user has full permissions to the temp directory. TMP and TEMP both point to the same ..localemp directory, and it has full permissions in that directory.
Does anyone have any idea of things I can try? Is there a known bug with cluster services? I don't think this is happening when server B links to another server that isn't clustered.
i successfully linked the sybase database to sqlserver. at first i can select a record, after i execute the same statement the result become different. i was looking for answer in the web but i can't find one. can somebody help me...
i suspect that sqlserver cache the result.. so i try to create a view for sybase database table. when i select the view it gave me the complete result but when i rerun the statement again it became inconsistent.
is there a cache or something in sqlserver. if there is how can i clear it.. so i will always get the fresh copy of the result.
Hi!Can anybody give me a hint how to put sa resut from EXEC into avariable.EXEC is called:EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)....)Thanks!Mario.
I have a 2000 machine which calls a stored procedure on another 2000 machine via a linked server. The results come back and insert into a temporary table.
When I use the same code executing the from the 2000 machine over to 2005 machine via a linked server I cannot insert into the table. But I am able to see the data if I remove the insert statement.
I have tried to place the data into a permanent table without success. I have also checked to be sure the linked server properties are the same.
Any help on this would be appreciated. Below is the code. It is very simple and returns only one value but the bigger procedure that is ran returns several records and mutliple columns. This seems to easy but doesn't work.
declare @strDBName as varchar(100) set @strDBName = 'MYHRMIS_WEBEX' declare @EmpCode as varchar(50) set @EmpCode = '60874' declare @SQL as varchar(8000) set @SQL = ' select count(*) from ' + @strDBName + '.dbo.Reqrec_EmployeeDetails where ed_empcode = ''' + @EmpCode + ''' and ed_status in (1, 4) ' print @SQL exec(@SQL)
How do I get the result of this query into a variable. Thanks in advance.
I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.
This is my function:
ALTER FUNCTION [dbo].[ReturnPickItemValue] ( -- Add the parameters for the function here @TypeID int, @CaseID int ) RETURNS varchar(max) AS BEGIN -- Declare the return variable here DECLARE @RTN varchar(max)
IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1 BEGIN SET @RTN = 'SELECT PickItem I + CASE D.IsStartDateEstimated WHEN 0 THEN CAST(StartDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar) END + CASE D.IsEndDateEstimated WHEN 0 THEN CAST(EndDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar) END
FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND P.CaseID = ' + CAST(@CaseID as varchar) END ELSE BEGIN SET @RTN= 'SELECT I.PickItem FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND CaseID = ' + CAST(@CaseID as varchar) END
RETURN @RTN
END
Each time I try " RETURN EXEC(@RTN) " or something similar I get an error.
I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable?
As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible?
For Example (I know this doesn't work, but it is effectively what I am trying to achieve):
select @result = EXEC(@sqlcommand)
I could then use the @result variable in an insert statement to update a table with the results from the EXEC command.
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet] GO /****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[usp_sysconf]
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
I have been trying to find a solution to this for some time now. Was wondering if some1 had done is earlier and has a solution.
I have a 2 server machines. Namely: ServerOne and ServerTwo
ServerOne (main server, On 1 machine.) Table - Foofoo
ServerTwo (secondary server, on another machine) Table - Booboo
I want to be able to link these two servers and work with them.
At the moment I do something like this. NB. My Stored Procedure is on ServerOne
declare @server varchar(100)
Select @server=Servername from ServerOne.systemsettings where name='secondary' -- @server is not equal to 'ServerTwo' declare @str varchar(8000) set @str = ' select * from Foofoo f join ' + @server + '.myDB.dbo.Booboo b on b.id = f.id '
exec(@str)
My problem is that this works fine but I do not like working with long strings and then executing them at the end.
I have also been told that SQL's performance on this is not entirely that well as normal select's would be.'
Another thing that could be used is SQl's own linked servers method but apparently out system was designed some time ago and a lot of things have been developed around the current technic.
Our server names also change quite frequently making hadcoding server names difficult.
Using the string exec convention also hides from sql when you do a dependency search of a particular table.
Is there a way I can save the server name on @server and then just add it to the select stmt without using the long stringing idea.
Any feedback with ideas and solutions will be greatly appreciated.
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I've installed SQL Server 7.0 SP2 and Backup Exec 7.3. When i try to start backup exec a dr. Watson error appears. Backup exec doesn't start. The same effect with version 8.0 and 8.5 from Veritas Backup Exec. When i try to install MDAC 2.5 after installing Backup exec, error 90332 (?) appears. Backup exec "hangs". When i've installed Backup exec 7.0 or 7.2 there are no problems. (also with SQL Agent) Does somebody know to reolve this?
Does anyone want to give me a few glowing reviews for me to show my bosses? I would like to recommend this product, but the only place I am finding info (such as reviews)is tha seagate site, which may be more than a little biased.
I have a c# app. This is a piece of code out of a stored proc. it is erroring: Procedure or function getTopParentDealerFromChildDealer has too many arguments OR @dealerID is not a parameter for procedure getTopParentDealerFromChildDealer.(if I put ",@dealerID=@parentID)
I have tried all combinations "@dealerID",@dealerID=@parentID" etc.
BEGIN --get the top parent dealerID DECLARE @parentID INT SET @parentID = 0 EXEC getTopParentDealerFromChildDealer @dealerID, @parentID OUTPUT IF (@parentID>0) BEGIN
------------------------------------------------------ here is the getTopParentDealerFromChildDealer as called ------------------------------------------------------ ALTER PROCEDURE getTopParentDealerFromChildDealer @childDealerID INT AS
SET NOCOUNT ON DECLARE @dealerID INT DECLARE @parentID INT SET @dealerID = 0 SELECT @dealerID = dealerParentID from dealerRelations where dealerChildID = @childDealerID
WHILE @dealerID <> 0 BEGIN declare @temp INT set @temp = @dealerID IF (SELECT count(dealerParentID) FROM dealerRelations WHERE dealerChildID = @temp)>=1 BEGIN SELECT @dealerID = dealerParentID FROM dealerRelations where dealerChildID = @temp END ELSE BEGIN SET @dealerID=0 set @parentID = @temp END END
if (@parentID IS NULL) BEGIN set @parentID = 0 --set @parentID = @dealerID END
return @parentID
I don't usually use stored procedures but the job I have taken over previously used them. Any help would be much appreciated.
Has anyone had any problems with Seagate Backup Exec and MS SQL Server? We have been having server "lockups" occur when the Seagate Backup Exec does it's DBCC CheckDB before it backs up the databases. The server starts repeating "lazywriter" errors which take up all the server resources to where we have to "reboot" the server to recover. Apparently this is a known problem (per Microsoft Tech. Support). Has anyone else experienced this problem? and if so, how did you get around it or repair it?
I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded. So I try to have it executed in a trigger but it failed.
Here is the trigger creation script and error message
use mlcb go if exists (select name from sysobjects where name = 'test' and type = 'TR') DROP TRIGGER TEST GO
CREATE TRIGGER test on mlcb.dbo.trans_errlog for insert as declare @email_subject varchar(100), @email_content varchar(4000), @email_recipients varchar(50)
set @email_subject='SQL Mail test mail' set @email_recipients='some@world.com.tw' set @email_content='this is a test mail, don't reply this mail'
exec master.dbo.xp_sendmail @recipients=@email_recipients,@subject=@email_subj ect,@message=@email_content GO
Error Message: Server: Msg 2812, Level 16, State 62, Line 6 Could not find stored procedure 'master.xp_startmail'. The statement has been terminated.
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
I am generating some dynamic sql which I would like to run and return the data from, however when I run EXEC(@TEMP_TABLE_STRING) it cannot return the table I think because it is out of scope.
Is there a way around this.
declare@Tablename VARCHAR(150) = 'TEMP_LOCATIONS' declare@FilterClause VARCHAR(512)=NULL declare@Classes VARCHAR(100)=NULL declare@IsExcel BIT = 1 DECLARE @SQL Nvarchar(MAX) = '' SET NOCOUNT ON;
--Exec Database.Employees --Use Database --Go --Create PROCEDURE AEM.TempTable --AS --BEGIN --Select * into #emptemp From Database.Employees --End --Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime declare @end smalldatetime set @start = '2015-03-22 00:00' -- this should be a Sunday set @end = '2015-03-28 23:59' -- this should be the following Saturday
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
