Problem Running SP_EXECUTESQL With OpenRowSet (with Variable) As Part Of The Query
Nov 27, 2007
Hi,
I had problem when combining OpenRowSet and SP_EXECUTESQL, when i tried to run the following query, it complaints that RESID is not declared. any idea how should i put the query so i will pass @RESID as 1 of the parameter? BTW, i know that the SP_EXECUTESQL is able to run query which length up to 8000, but how about the parameter?
DECLARE @SqlToRun NVARCHAR(MAX)
DECLARE @RESOURCEIDS NVARCHAR(MAX)
SET @RESOURCEIDS = REPLICATE(CAST('ABC' AS NVARCHAR(MAX)), 1000)
SET @SqlToRun = N'SELECT * FROM
OPENROWSET(''SQLNCLI'',
''server=;database=;uid=;pwd='',
''SELECT * FROM WHERE COL=@RESID'')'
DECLARE @PARAMS NVARCHAR(MAX)
SET @PARAMS = ''@RESID NVARCHAR(MAX)'
EXECUTE SP_EXECUTESQL @SqlToRun, @PARAMS, @RESID = @RESOURCEIDS
Regards,
Derek
View 5 Replies
ADVERTISEMENT
May 11, 2007
Hello,
I responded to a very old discussion thread & afraid I buried it too deep.
I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)
but I have not gotten all the ''''' + @variable syntax right.
Here is my raw openrowset with what I am aiming at.
Code Snippet
-- I want to use some kind of variable, like this to use in the file:
DECLARE @FIL VARCHAR(65)
SET @FIL = 'C:company foldersDocumentationINVENTORY.xls;'
--
SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:company foldersDocumentationINVENTORY.xls;', 'SELECT * FROM [Inventory$]')
AS DT
Anyone game? Many thank-yous, in advance.
Kind Regards,
Claudia.
View 1 Replies
View Related
Jan 18, 2008
In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)AS DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU DECLARE @Count int DECLARE @ItemNumSKU varchar(50) SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber' EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1 IF (@Count = 0) BEGIN SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber' EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT SET @ItemNumber1 = @ItemNumSKU END
-- Creating table variable to have values from 1 to 12 DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint) DECLARE @Cnt INT SET @Cnt = 1 WHILE(@Cnt <= 12) BEGIN INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0) SET @Cnt = @Cnt + 1 END
--Joining query result with the table variable to get required result DECLARE @Status1 Char(1) SET @Status1 = 'C'
SET @SQL = N'SELECT' SET @SQL = @SQL + N' MS.MonthNumber,' SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,' SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,' SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan' SET @SQL = @SQL + N' FROM' SET @SQL = @SQL + N' (SELECT ' SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#' SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost' SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice' SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan ' SET @SQL = @SQL + N' FROM ' SET @SQL = @SQL + N' Invoice_Totals ' SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number ' SET @SQL = @SQL + N' WHERE ' SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) ' SET @SQL = @SQL + N' AND ItemNum = @ItemNumber' SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year' SET @SQL = @SQL + N' GROUP BY' SET @SQL = @SQL + N' DATEPART(mm, DateTime)' SET @SQL = @SQL + N' ) Temp ' SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#' SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1
Thanks
View 6 Replies
View Related
Sep 5, 2007
Hi
I am trying to cinstruct two strings/variables and conetenate it in sp_executsql
Isnt this the right way to do it ?
CREATE PROCEDURE dbo.XML_Final
(
@Filepath varchar(50),
@DBName varchar(20))
AS
DECLARE @hdoc int
DECLARE @doc varchar(max)
--DECLARE @doca varchar(max)
--DECLARE @SQL nvarchar(200)
DECLARE @INSERT nvarchar(max)
DECLARE @WITH nvarchar(max)
SET @SQL = N'select @doc = c from OpenRowset(BULK''' + @Filepath + N''', SINGLE_CLOB) as T(c)'
exec sp_executesql @SQL, N'@doc varchar(max) output' ,@doc OUTPUT
EXECsp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT @INSERT = N' INSERT INTO'+ @DBName + N'.dbo.Student( Name,Age,Address)'
--SET @WITH = N @TEMP1
SELECT @WITH = N' SELECT * FROM OPENXML (@hdoc,'+N'''/Name/Age'/Address+N''')' +N'
WITH
(
Name text'+ N' ' + '''../../Name' + N ''',
Age int' + ' ' + N'''../../Age+ N''',
Address int'+ ' ' + N'''../../Address'+ N'''
)'
exec sp_executesql @INSERT+@WITH
-- Remove the internal representation.
EXEC sp_xml_removedocument @hdoc
Gives me a error at
exec sp_executesql @INSERT+@WITH :
Incorrect syntax near '+'
Thanks
View 1 Replies
View Related
Jan 18, 2008
In the following stored procedure, I am doing Right outer join with the table variable. I am getting error 'Must declare the scalar variable "@MonthSales1"'. Can you please let me know how to do this?
ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber1 nvarchar(30),@StoreId1 nvarchar(500),@Year1 int)
AS
DECLARE @SQL nvarchar(1000)
-- Searching ItemNumber in Inventory_SKU
DECLARE @Count int
DECLARE @ItemNumSKU varchar(50)
SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId) AND ItemNum = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@Count12=@Count OUTPUT,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1
IF (@Count = 0)
BEGIN
SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId) AND AltSKU = @ItemNumber'
EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId nvarchar(500),@ItemNumber nvarchar(30)',@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@ItemNumSKU1=@ItemNumSKU OUTPUT
SET @ItemNumber1 = @ItemNumSKU
END
-- Creating table variable to have values from 1 to 12
DECLARE @MonthSales1 Table(MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)
DECLARE @Cnt INT
SET @Cnt = 1
WHILE(@Cnt <= 12)
BEGIN
INSERT INTO @MonthSales1 VALUES(@Cnt,0,0,0)
SET @Cnt = @Cnt + 1
END
--Joining query result with the table variable to get required result
DECLARE @Status1 Char(1)
SET @Status1 = 'C'
SET @SQL = N'SELECT'
SET @SQL = @SQL + N' MS.MonthNumber,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'
SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'
SET @SQL = @SQL + N' FROM'
SET @SQL = @SQL + N' (SELECT '
SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'
SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'
SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'
SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '
SET @SQL = @SQL + N' FROM '
SET @SQL = @SQL + N' Invoice_Totals '
SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '
SET @SQL = @SQL + N' WHERE '
SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId) '
SET @SQL = @SQL + N' AND ItemNum = @ItemNumber'
SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year'
SET @SQL = @SQL + N' GROUP BY'
SET @SQL = @SQL + N' DATEPART(mm, DateTime)'
SET @SQL = @SQL + N' ) Temp '
SET @SQL = @SQL + N' RIGHT OUTER JOIN ' + @MonthSales1 + ' MS ON MS.MonthNumber = Temp.Month#'
SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'
EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId nvarchar(500),@ItemNumber nvarchar(30),@Year int',@Status = @Status1,@StoreId = @StoreId1,@ItemNumber = @ItemNumber1,@Year=@Year1
Thanks
View 1 Replies
View Related
Jul 4, 2007
I have a text file that is being insert into a table in a remote db. I have a dev server named dbname_trunk and a production server named dbname. The dataflow task refers to
[dbname_trunk]..[temp_ActivitiesAccessControlListUsers]
On the production version I would want the above to be:
[dbname]..[temp_ActivitiesAccessControlListUsers]
Is it possible to put the following into the OpenRowset property of the DataFlowComponent's custom properties?
"[" + @[User:bName] + "]..[temp_ActivitiesAccessControlListUsers]"
Actually, I know that doesn't work. What I need to know is what would work to accomplish my purpose. Ultimately, I would like to put the value in the configuration file.
Thanks
View 1 Replies
View Related
Sep 7, 2006
Hello,to accelerate loading xml data from many files into a table using openrowset (bulk...) I want to use a variable in the file specification and increment it within a loop similar to this:
declare @datnam varchar(100);
DECLARE @MyCounter int;
SET @MyCounter = 1;
set @datnam = 'c:XML_DatenPOS_LOG_200608_'+ltrim(str(@MyCounter))+'.xml';
INSERT INTO GK_TO_KFH_ADAPTER_XML_NS (LOC_ID, MSG_CONTENT)
SELECT @MyCounter, MSG_CONTENT
FROM (
SELECT * FROM OPENROWSET (BULK @datnam, SINGLE_CLOB)
AS MSG_CONTENT) AS R(MSG_CONTENT)
But I got the following error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '@datnam'.
Is there a way to this in that manner?
Or is the bcp utility an alternative?
Regards
Peter
View 3 Replies
View Related
Dec 10, 2001
I am calling a SQL Server 6.5 Stored Procedure from Access 2000 with the following code :-
Public Function CheckDigitCalc()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
On Error GoTo TryAgain
conn.Open "DSN=WEB;uid=sa;pwd=;DATABASE=WEB;"
Set cmd.ActiveConnection = conn
cmd.CommandText = "SPtest2"
cmd.CommandType = adCmdStoredProc
cmd.Execute
MsgBox "Numbers created OK.", vbOKOnly
Exit Function
TryAgain:
MsgBox "Error occurred, see details below :-" & vbCrLf & vbCrLf & _
Err & vbCrLf & vbCrLf & _
Error & vbCrLf & vbCrLf, 48, "Error"
End Function
The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.
View 2 Replies
View Related
Sep 20, 2005
Hi my data files sit in the default directories and I think they are causing my partition to run out of space. I mainly use one db that I created but don't use the others (ie master, model, tempdb, etc). Yet I see their MDF and LDF files are growing. What can I do to shrink them down or perhaps move them off to a larger partition after shrinking?
View 6 Replies
View Related
Aug 3, 2007
I know this can be done, because I saw it and tried it once. But did not use it at the time and consequently forgot how.
I am storing an expression to a variable @Var Varchar(50)
@Var = ('aa','bb','cc')
I want to:
Select * from Table WHERE Field IN @Var
Substituting my variable for the " ('aa',bb','cc') which I can build dynamically...thus the use.
Purpose:
I have an aggregate query that currently has a CASE statement based on the contents of ('aa',bb','cc'). However, those contents can change. The aggregate will not allow the subquery (SELECT Field FROM Table).
What I remember looked something like:
SELECT * FROM Table WHERE field in ' & @Var &'.
View 1 Replies
View Related
Aug 23, 2007
I have a huge question, it's for my job im doing now. I have a table with the name TWO.dbo.SVC06105. I want to be able to take the word "TWO" and put that into a variable. I know this doesn't look correct, but I want to be able to do something like this:
DECLARE @GPNAME CHAR(100)
SET @GPNAME = 'TWO'
SELECT * FROM @GPNAME.dbo.SVC06105
I know the SELECT statement isn't correct, but I hope that you guys understand what i'm trying to do through this example. BTW, I am new to this team so nice to meet you all. L8er
View 4 Replies
View Related
Mar 9, 2008
hey all,
I have the following query:
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS
BEGIN
DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)
DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)
SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name
HAVING article_id = @article_id
END
GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
Cheers
View 6 Replies
View Related
Sep 20, 2004
Hi there,
I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:
-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'
EXEC master..sp_executesql @SQLString
And here's the error message that I get:
Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.
I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.
Can anyone help?
Thanks
View 1 Replies
View Related
Jul 23, 2005
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies
View Related
Mar 1, 2007
Hi everyone,
I'm trying to query an excel file and I get a mistake. The query is as follows:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:ExcelFile.xls', 'select * from Sheet1')
and I get the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
I'm thanking any help that you can give.
Thanks,
Oscar.
View 7 Replies
View Related
Jul 23, 2005
I'm trying to pass through a SQL statement to an Oracle database usingOPENROWSET. My problem is that I'm not sure of the exact syntax I needto use when the SQL statement itself contains single quotes.Unfortunately, OPENROWSET doesn't allow me to use parameters so I can'tget around the problem by assigning the SQL statement to a parameter oftype varchar or nvarchar as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',@chvSQL)I tried doubling the single quotes as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword','SELECT *FROM AWHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')')But that didn't work. Is there a way out of this?Thanks,Bill E.Hollywood, FL
View 1 Replies
View Related
Feb 28, 2008
I am not able to use WHERE Clause in my query. What am I doing wrong?
Here my query that will generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT WHERE trn_dt > '2007-03-08' '
I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.
Here is my query which doesn't generate error:
SELECT * INTO LN_S
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM LN_ACCT'
Using SQL Server 2000
DSN to a CACHE database on local network
Thanks in advance,
Sam
View 3 Replies
View Related
Oct 5, 2006
im running a dynamic query with open rowset in it
pseudocode:
@CMD=declare @ RETURN SELECT @RETURN =SUM(X) FROM OPENROWSET(....) SELECT @RETURN
EXEC @CMD
This pseudocode dipplay the result of @return
the problem:
capture @return into @myvalue outside the dynamic sql scope
something like
Select @myvalue=exec(@cmd)
I don't wanna run on ditributed transaction like this
insert mytable
exec(@cmd)
thanks,
joey
View 7 Replies
View Related
Aug 12, 2006
Okay, so I came across an odd performance issue that I'm wondering if some guru can help me out with.
I have a query that uses a paging algorithm that uses a paging algorithm and a table variable, then gets a page of data based on a join to that table variable. Here's a simplified query using the algoritm:
--declare table variable... not shown for brevity
--make sure we only store the least amount of records possible
SET ROWCOUNT ( @pageNumber + 1 ) * @pageSize
--insert into table variable
INSERT INTO @TableVariable( Key )
SELECT key FROM table
WHERE whatever = @p1
--we only want one page of data
SET ROWCOUNT @pageSize
--now get the page of data from the table
SELECT key FROM table
WHERE whatever = @p1
AND [TableVar Identity Column] > @pageNumber * @pageSize
The algorithm works great for our needs, BUT, I noticed something a little odd about its behavior during performance testing.
In particular, when I run the query using Sql Server Management Studio, where I manually DECLARE all the variables it ends up needing only 156 reads to complete the job. When I call it from the app using ADO.NET, however, I noticed it needs 310 reads! Huh?
I looked for differences, and the only one I could determine was that ADO.NET passes the query and uses sp_executesql and passes the parameters vs. declaring and setting them statically before executing the query. I confirmed that this was the issue by manually running sp_execute SQL and seeing that it took roughly the same number of reads (274) to process the query.
Naturally, I don't want the time it takes to perfrom my query to double, but and frankly I don't understand why there would be a difference in performance. Can anyone help me track down what is going on and suggest to me how to fix the problem.
I assume that SQL Server Management Studio optimizes the execution path somehow, but I'm not sure how to gain the same benefit for my passed query. Can I enable something with hints? Is there something else going on that I should know about?
View 10 Replies
View Related
Sep 1, 2006
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
View 1 Replies
View Related
Oct 22, 2015
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0
PRINT @Found
View 2 Replies
View Related
Feb 26, 2008
I'm experiencing issues importing XML data using a distributed query with the following statement which is run from an XP client named WorkstationA connecting to SQL2005 SP2 ServerB, the XML data is located on ServerC.
AdHoc Queries using OpenRowSet has been enabled and verified.
The SQL Server service is running using a domain user account with permissions to read the remote files. I have logged in locally to the SQL server and verified this. It still fails even if the SQL services are running using LocalSystem.
User on Workstation A is authenticated with Integrated security (SQL Admin) and has rights to read the XML files on ServerC.
WorkStationA = SQL2005 Mgt Studio running the query
ServerB = SQL2005 SP2
ServerC = XML data files
DECLARE @xml XML
SELECT @xml =CONVERT(XML, bulkcolumn, 2)
FROM OPENROWSET(BULK '\SERVERCSHAREPATHDATAFILE.XML', SINGLE_BLOB) AS x
SELECT @xml
Results: Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "\SERVERCSHAREPATHDATAFILE.XML" could not be opened. Operating system error code 5(Access Denied).
The query fails when it is run from Workstation A connected to SQL ServerB querying data on ServerC via a UNC.
The query is succesful when it is run from the local SQL ServerB. The problem is with distributed queries.
The query is succesful when the XML files are local to the SQL server including referencing them via a local UNC
Thank you for any responses.
Hamish
View 4 Replies
View Related
May 14, 2014
I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:
SELECT [ID]
,[PT_CODE]
,[FILE_PATH],
CASE WHEN [FILE_PATH] IS NOT NULL THEN
(SELECT * FROM OPENROWSET(BULK [FILE_PATH], SINGLE_BLOB) TT)
END
AS IMAGE_LOADED
FROM [DB].[dbo].[TABLE_MR_FILES]But I keep getting the error:
Incorrect syntax near 'FILE_PATH'.I have try multiple combinations without luck to make the OPENROWSET read the path stored in the column [FILE_PATH]. What am I missing?
Note: I am using MSSQL 2012. I don't want to import the images into the DB just load them in the fly as needed by the report runned from the application. I have full access to the DB so if a store procedure is the solution I can go with it.
View 4 Replies
View Related
Sep 19, 2014
I have two tables that I am pulling data from: an item table and a sales table. Almost all of the information comes from the item table (item description, location, amount on hand). The last field wanted is Year-To-Date sales. I can pull the sales field from the sales table, which gives me all sales from the creation of the db. I need to be able to run a date variable of This Year on that sales field only. I have a date field I can reference off of in the sales table.
View 6 Replies
View Related
Mar 6, 2008
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
View 4 Replies
View Related
May 25, 2007
Hi,
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.
I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.
Thanks,
Darrell Young
View 3 Replies
View Related
Oct 29, 2007
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
Thanks
Shankar
View 1 Replies
View Related
Sep 16, 2004
Is there a chance to retreive let's say rows positioned from 30-70 if a query produced result that contains 100 rows?
View 3 Replies
View Related
Nov 5, 2007
exec sp_tables_ex 'linked server'
The table_cat returned a null value. I ended up writing
select * from linked_server..schema.table and it did work. However, it was really slow. 56 seconds vs. less than a second for the pass through openquery.
My guess is that the problem is that the table_cat was null and this is why it is so slow. Is this correct? How do I resolve this speed issue?
View 4 Replies
View Related
Dec 7, 2007
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
View 3 Replies
View Related
Jun 19, 2008
Hi,In the following query the calculator gives value 3.3 but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks
View 3 Replies
View Related
May 29, 2008
I need some serious help with this query...
SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date IS NOT NULL)
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
or(IDStatus LIKE '')
or (NOT (Task LIKE '%x%'))
or (ExpressEmail LIKE '%@%')
or (WebSite NOT LIKE '')
or (Unsubscribe = 0)
or (Artwork LIKE '')
Basically the parts in red work best when they use the 'or' criteria.
My problem.
I run the query together black+red and get a different result than expected. Is there a way of saying 'do the black part first and its an imperative that the red part follows those rules on the black part of the query?' eg:
IDStatus must be LIKE '' when running the black part of the query
and expressemail must be like '%@%' on the black part of the query but not on red part of the query. So it doesnt matter that the expressemail must have idstatus like '' whilst having an @
Hope it makes sense??
View 11 Replies
View Related
Aug 28, 2006
I have a huge problem as mentioned in my previous queries some of my applications is using Link Server Query as "select * from sm-matrix.matrix.dbo.stage_orders" this doesn't work it gives following error:-
ODBC: Msg 0, Level 18, State 1
SqlDumpExceptionHandler: Process 62 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Unspecified error]
I understand best method to use is "select * from openquery(sm-matrix,"select * from stage_orders")" but i can't do away with above mentioned query as lot of places in application it has been using.
This was working fine till i moved to Windows 2003 from Windows 2000.
Following is the error we are getting:-
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/28/06 15:52:06 spid 58
*
* Exception Address = 00404743 (RecBase::Resize + 00000005 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* [ M A T R I X ] 1b 00 5b 00 4d 00 41 00 54 00 52 00 49 00 58 00 5d 00
* . . s p _ i n d e 2e 00 2e 00 73 00 70 00 5f 00 69 00 6e 00 64 00 65 00
* x e s _ r o w s e 78 00 65 00 73 00 5f 00 72 00 6f 00 77 00 73 00 65 00
* t ç S 74 00 00 00 00 00 e7 18 00 00 00 00 00 00 18 00 53 00
* T A G E _ O R D E 54 00 41 00 47 00 45 00 5f 00 4f 00 52 00 44 00 45 00
* R S ç 52 00 53 00 00 00 e7 00 00 00 00 00 00 00 ff ff 00 00
* ç d b o e7 06 00 00 00 00 00 00 06 00 64 00 62 00 6f 00
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B32FFF 00733000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* USER32 77380000 77411FFF 00092000
* GDI32 77C00000 77C47FFF 00048000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BA0000 77BF9FFF 0005a000
* UMS 41070000 4107BFFF 0000c000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 60020000 6002FFFF 00010000
* sqlevn70 10000000 10006FFF 00007000
* Secur32 76F50000 76F62FFF 00013000
* NETAPI32 110B0000 11107FFF 00058000
* ole32 113A0000 114D3FFF 00134000
* XOLEHLP 11660000 11665FFF 00006000
* MSDTCPRX 11670000 116E7FFF 00078000
* msvcp60 116F0000 11750FFF 00061000
* MTXCLU 11760000 11778FFF 00019000
* VERSION 11780000 11787FFF 00008000
* WSOCK32 11790000 11798FFF 00009000
* WS2_32 117A0000 117B6FFF 00017000
* WS2HELP 117C0000 117C7FFF 00008000
* OLEAUT32 117D0000 1185BFFF 0008c000
* CLUSAPI 118A0000 118B1FFF 00012000
* RESUTILS 118C0000 118D2FFF 00013000
* USERENV 118E0000 119A3FFF 000c4000
* mswsock 119C0000 11A00FFF 00041000
* DNSAPI 11A10000 11A38FFF 00029000
* winrnr 11A80000 11A86FFF 00007000
* WLDAP32 11A90000 11ABDFFF 0002e000
* rasadhlp 11AE0000 11AE4FFF 00005000
* SSNETLIB 00C70000 00C84FFF 00015000
* NTMARTA 00C90000 00CB1FFF 00022000
* SAMLIB 00CC0000 00CCEFFF 0000f000
* security 125D0000 125D3FFF 00004000
* hnetcfg 125E0000 12638FFF 00059000
* wshtcpip 12800000 12807FFF 00008000
* SSmsLPCn 12810000 12817FFF 00008000
* SSnmPN70 12A20000 12A25FFF 00006000
* ntdsapi 12AE0000 12AF4FFF 00015000
* kerberos 12B10000 12B67FFF 00058000
* cryptdll 12B70000 12B7BFFF 0000c000
* MSASN1 12B80000 12B91FFF 00012000
* SQLFTQRY 12920000 12951FFF 00032000
* xpsp2res 12EB0000 13174FFF 002c5000
* CLBCatQ 13180000 13202FFF 00083000
* COMRes 13210000 132D5FFF 000c6000
* sqloledb 132E0000 13360FFF 00081000
* MSDART 12960000 12979FFF 0001a000
* MSDATL3 12980000 12994FFF 00015000
* oledb32 136F0000 13768FFF 00079000
* OLEDB32R 13770000 13780FFF 00011000
* msv1_0 13810000 13836FFF 00027000
* iphlpapi 13840000 13859FFF 0001a000
* PSAPI 13860000 1386AFFF 0000b000
* xpsqlbot 13910000 13915FFF 00006000
* rsaenh 13A50000 13A7EFFF 0002f000
* xpstar 13BB0000 13BF6FFF 00047000
* SQLRESLD 13C00000 13C06FFF 00007000
* SQLSVC 13C10000 13C26FFF 00017000
* ODBC32 13C60000 13C9CFFF 0003d000
* COMCTL32 13CA0000 13D36FFF 00097000
* comdlg32 13D40000 13D89FFF 0004a000
* SHELL32 14110000 14912FFF 00803000
* SHLWAPI 13D90000 13DE1FFF 00052000
* odbcbcp 13C30000 13C35FFF 00006000
* W95SCM 13C40000 13C4BFFF 0000c000
* SQLUNIRL 13DF0000 13E1CFFF 0002d000
* WINSPOOL 13E20000 13E46FFF 00027000
* SHFOLDER 13E50000 13E58FFF 00009000
* comctl32 14920000 14A22FFF 00103000
* odbcint 13EE0000 13EF6FFF 00017000
* NDDEAPI 13F00000 13F06FFF 00007000
* SQLSVC 14CB0000 14CB5FFF 00006000
* xpstar 14CC0000 14CC8FFF 00009000
* ACTIVEDS 14CD0000 14D02FFF 00033000
* adsldpc 14D10000 14D36FFF 00027000
* credui 14D40000 14D6DFFF 0002e000
* ATL 14D70000 14D87FFF 00018000
* adsldp 14DF0000 14E1DFFF 0002e000
* SXS 14FA0000 1505BFFF 000bc000
* xplog70 15060000 15071FFF 00012000
* xplog70 13C50000 13C53FFF 00004000
* DBNETLIB 11630000 1164BFFF 0001c000
* crypt32 15180000 15212FFF 00093000
* SQLOLEDB 11650000 1165EFFF 0000f000
* dbghelp 15620000 156D4FFF 000b5000
*
* Edi: 00000005:
* Esi: 6BDE4924: 00000000 00000052 00000000 00000000 00000003 0000001B
* Eax: 00000000:
* Ebx: 3B6AFFFD: 013FF000 20000001 CA000000 01000014 CC004200 01000014
* Ecx: 6BDE4924: 00000000 00000052 00000000 00000000 00000003 0000001B
* Edx: 00000E00:
* Eip: 00404743: E183088A 04E9830E 00C9840F 4949001B 4E8B5275 08668304
* Ebp: 1289D77C: 1289D790 005BD328 00A5EA38 1289D78C 1289E8B8 1289EC74
* SegCs: 0000001B:
* EFlags: 00010246: 0057004F 003B0053 003A0043 0057005C 004E0049 004F0044
* Esp: 1289D768: 6BDE4924 00446C52 00000000 00000002 3B6AB940 1289D790
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 00404743 Module(sqlservr+00004743) (RecBase::Resize+00000005)
* 00446C52 Module(sqlservr+00046C52) (CSysScan::GetVaried+0000002A)
* 005BD328 Module(sqlservr+001BD328) (CUserScan::CbGroupBitmap+00000016)
* 006315F7 Module(sqlservr+002315F7) (SecCache::FGetFromDiskScedb+00000317)
* 0040C694 Module(sqlservr+0000C694) (checkdbperm+00000114)
* 0040C3BC Module(sqlservr+0000C3BC) (usedb+000000DA)
* 0040C2DF Module(sqlservr+0000C2DF) (CAutoDb::FUse+00000031)
* 004B2BE3 Module(sqlservr+000B2BE3) (CreateFakeTableRowset+00000038)
* 00424175 Module(sqlservr+00024175) (OpenRowsetSS::OpenRowset+000000EC)
* 0050D873 Module(sqlservr+0010D873) (GetTableCursor+00000056)
* 0050D7FE Module(sqlservr+0010D7FE) (CQScanRowset::StandardGetRowset+0000009D)
* 00539A82 Module(sqlservr+00139A82) (CQScanTableScan::CQScanTableScan+0000008E)
* 005399EA Module(sqlservr+001399EA) (CXteTableScan::QScanGet+00000089)
* 004332E2 Module(sqlservr+000332E2) (CQScanHashMatch::CQScanHashMatch+0000051A)
* 00432E02 Module(sqlservr+00032E02) (CXteHashMatch::QScanGet+0000008C)
* 00427368 Module(sqlservr+00027368) (CXteProject::QScanGet+00000092)
* 0053D884 Module(sqlservr+0013D884) (CQScanSort::CQScanSort+000000BC)
* 0053D7A7 Module(sqlservr+0013D7A7) (CXteSort::QScanGet+0000012C)
* 0042306F Module(sqlservr+0002306F) (CQueryScan::CQueryScan+0000028E)
* 00422E59 Module(sqlservr+00022E59) (CQuery::Execute+0000006A)
* 0041D456 Module(sqlservr+0001D456) (CStmtQuery::ErsqExecuteQuery+0000022C)
* 0042C4AF Module(sqlservr+0002C4AF) (CStmtSelect::XretExecute+00000229)
* 0041C3CB Module(sqlservr+0001C3CB) (CMsqlExecContext::ExecuteStmts+000003B9)
* 0041BA11 Module(sqlservr+0001BA11) (CMsqlExecContext::Execute+000001B6)
* 0041B02D Module(sqlservr+0001B02D) (CSQLSource::Execute+00000357)
* 00437EC6 Module(sqlservr+00037EC6) (execrpc+00000507)
* 00437128 Module(sqlservr+00037128) (execute_rpc+00000019)
* 0042921A Module(sqlservr+0002921A) (process_commands+00000232)
* 41072838 Module(UMS+00002838) (ProcessWorkRequests+00000272)
* 410725B3 Module(UMS+000025B3) (ThreadStartRoutine+00000098)
* 77BCB3CA Module(MSVCRT+0002B3CA) (endthread+000000AB)
* 77E66063 Module(kernel32+00026063) (GetModuleFileNameA+000000EB)
* -------------------------------------------------------------------------------
View 1 Replies
View Related
