hi guys what is the syntax for using the passing variable part into the name of a table in a store procedure. in particular: (assume already declared the variable periodseq.
select * into Temp_Usage_@periodseq from Master_usage where Master_usage.PERIODSEQ = @periodseq
in particular the Temp_Usage_@periodseq line of code, how do i "add" the periodseq (which is a number) to the end of the name of Temp_Usage, i.e: Temp_Usage_112 is the syntax an & like Temp_Usage_&@periodseq? Cheers Champinco
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
Anyone got any Ideas on how to set tax...TXZip as a variable in the following script segment?
Select RecordID = Identity(int,1,1), Zip_ZipCode, Zip_SignatureCode Into #Temp from tempstatezip where State = @State
Delete From tax...TXZIP ------------------------------
While (Select Count(*) from #temp)>0 Begin
Set @rowVal = (Select top 1 RecordID from #Temp)
Id like to set this up in a store procedure so I can pass the state variable to it.....
Having problems setting a linked server as a variable thou..... Example of what I want..... Delete from @StateVar But as a linked server I always get...
declare @StateVar Varchar(11) Set @StateVar = 'Tax...TXZip' Select * from @StateVar
Msg 137, Level 15, State 2, Line 3 Must declare the variable '@StateVar'.
I'm posting this because I found this solution after much digging.
The goal here is to incorporate a variable parameter within a OPENQUERY and, ultimately build a dynamic Where clause for use within a OPENQUERY linked server routine. I'm posting because I spent a lot of time trying to get this to work and also, have seen other posts here that hinted it wasn't doable.
First of all - there a good quick article that gets close for FoxPro and possibly works as is for ACCESS:
SET @FAMILY='Touring' SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''
SET @TSQL = 'select cov,family,model from vinmast where family='+'['+@FAMILY+']'')'
EXEC (@OPENQUERY+@TSQL)
All shown are single quotes.
In Visual Foxpro, ' ' or " " or [ ] can be used a delimeters
In addition, if wanting to build a dynamic where clause, you could do something like:
SET @TSQL = 'select cov,family,model from vinmast ' IF <some condition met to include FAMILY filter> Begin SET @TSQL=@TSQL+'where family=['+@DUTFAMILY+']''' SET @TSQL=@TSQL+ ')' End ----------------- Here's the entire Stored Procedure:
CREATE PROCEDURE dbo.ewo_sp_DUTLookup ( @DUTPROJECT char(25)=NULL,--Project @DUTFAMILY char(10)=NULL,--Family @DUTMODEL char(20)=NULL,--Model @DUTYEAR char(4)=NULL,--Model Year @DUTBEGIN char(25)=NULL,--Beginning of COV/DUT number @DEBUG int=0 )
AS
DECLARE @OPENQUERY varchar(4000), @TSQL varchar(4000), @TWHERE varchar(4000), @intErrorCode int
IF @intErrorCode=0 Begin SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,''' SET @TSQL = ' select dut_pk,cov,family,model,project,modelyr from vinmast ' End
set @intErrorCode = @@ERROR
IF @intErrorCode = 0 and @DUTFAMILY is not NULL or @DUTMODEL is not NULL or @DUTPROJECT is not NULL or @DUTYEAR is not NULL or @DUTBEGIN is not NULL set @TWHERE=' where '
-- Check for Family criteria If @intErrorCode = 0 and @DUTFAMILY is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' family=['+@DUTFAMILY+'] AND ' set @intErrorCode = @@ERROR
-- Check for Model criteria If @intErrorCode = 0 and @DUTMODEL is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' model=['+@DUTMODEL+'] AND ' set @intErrorCode = @@ERROR
--Check for Project criteria If @intErrorCode = 0 and @DUTPROJECT is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' project=['+@DUTPROJECT+'] AND ' set @intErrorCode = @@ERROR
--Check for Model Year If @intErrorCode = 0 and @DUTYEAR is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' modelyr=['+@DUTYEAR+'] AND ' set @intErrorCode = @@ERROR
--Check for beginning of DUT If @intErrorCode = 0 and @DUTBEGIN is not NULL and Len(@TWHERE)>0 Begin SET @DUTBEGIN=RTRIM(@DUTBEGIN) SET @TWHERE=@TWHERE+' substr(cov,1,'+cast(len(@DUTBEGIN) as char(20))+')=['+@DUTBEGIN+'] AND ' End set @intErrorCode = @@ERROR
IF @intErrorCode=0 AND substring(@TWHERE,Len(@TWHERE)-3,4)=' AND ' Begin set @TWHERE=Substring(@TWHERE,1,Len(@TWHERE)-3) select @intErrorCode=@@ERROR End
SET @TWHERE=@TWHERE+''')'
IF @debug<>0 and @intErrorCode=0 Begin print @intErrorCode print @OPENQUERY print @TSQL print @TWHERE print @OPENQUERY+@TSQL+@TWHERE End
IF @intErrorCode=0 EXEC (@OPENQUERY+@TSQL+@TWHERE) GO
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 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.
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?
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.
Hi I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted. Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas. Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.
I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.
The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.
Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience. Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it. ///javascript function DisplayReciept(transactionnum) { recieptdis = transactionnum;
I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.
So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROM ? ......
Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:
[Task Execute SQL] Error:
Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract
the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100)
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:
1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )
2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).
I hope someone can help me on this one, as I am getting a bit frustrated with my research..
I have procA on ServerA.DatabaseA and it uses an input parameter to get a value that determines which server to ping.
If this parameter is @paramA = 1, then query into LinkedSrvX. If @paramB = 2, then query into LinkedSrvY... etc.
Is there an easier way to do this or a best practice? I really don't want to go into repeating my queries for every server. Here is a tsql mini version of what I am trying to enhance as my original queries are 1000 lines long and I need to get away from the repeated queries. I do have queries that ping within the server and to other linked servers as well without issues.
All linked servers referenced below have the same structure of tables. I also need to avoid situations incase the server may be down, this proc should not return errors.
I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;
-It has one worksheet in it named 'Add Revenue Accts'. -The name of the workbook is 'Revenue_to_All_Accounts.xls' -Its location is \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_Accounts
I have the linked server configured as follows;
-Linked Server; REVENUE_TO_ALL_ACCOUNTS -Provider; Microsoft Jet 4.0 OLE DB Provider -Data Source; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls -Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls;Persist Security Info=False
When I attempt the following query; SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')
The following message appears, and no results are returned;
[OLE/DB provider returned message: Unspecified error] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?
I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.
Here is the code: ------------------------------------- DECLARE @LastDate datetime SELECT @LastDate = MAX(DateChaged) FROM tblPersonel WHERE ACTIVE IN( 'A', 'T')
1. I tried: SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@LastDate+''')
This line gives me an error msg:
Could not execute query against OLE DB provider 'MSDASQL'. [OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]
2. I tried to use CTOD() - FOXPRO function to convert character to date.
SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@LastDate+'')')
-this doesn't give any error, but doesn't limit the result set as it should.
What: I am trying to import data from spreadsheets to SQL Server.
Where: Windows Vista, SQL Server 2005 Express, Office 2007.
How: Using linked servers following KB306397.
In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default:
Linked server: XLSX
Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product name: XLSX
Data source: D:XLSX.xlsx
Provider string: Excel 12.0 The linked server was created ok.
I then followed KB321686 and ran this:
select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]')
and got this:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".
But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]')
the error is:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object.
It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS.
From the same KB article, I also tried:
select * from XLSX...[Sheet1$]
but got this:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".
MS SQL Server 2005 Express. I'm trying to connect to Access DB (having System Database) via OPENROWSET. Everything (client, server and access file) is on local drive.
This works (ODBC):
select * from openrowset('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:MBK.mdb;SystemDB=C:SECURED.MDW;Uid=me;Pwd=pw;', 'select * from [Mbk]')
This works (Jet.OLEDB):
select * from opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:MBK.mdb;Jet OLEDBystem Database=C:SECURED.MDW;User ID=me;Password=pw;') ...Mbk
This won't work (Jet.OLEDB):
select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'MS Access;Database=C:MBK.mdb;System Database=C:SECURED.MDW;UID=me;PWD=pw;', 'select * from [Mbk]')
saying ... "Wrong argument".
This won't work (Jet.OLEDB):
select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'MS Access;Database=C:MBK.mdb;SystemDB=C:SECURED.MDW;UID=me;PWD=pw;', 'select * from [Mbk]')
saying ... "There are no permissions for usage of object C:MBK.mdb". It seems that it simply hasn't found system database file C:SECURED.MDW, cause when I change SystemDB=C:SECURED.MDW to something like BlahBlahBlah=C:SECURED.MDW the same message is shown.
So, what is the right syntax for stating System Database in OPENROWSET query string? And why 'System Database' won't work?
I am trying to use OpenRowSet to read from a csv file. I run a query like this:
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:data;','select * from data.csv')
This works very nicely on my development system. However, when I try to run the same query on my production server, I get the following error message:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
I also tried with the Jet driver:
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:data;Extended Properties="text;HDR=Yes;FMT=Delimited";', 'select * from data.csv')
The error was:
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 have run "sp_configure 'Ad Hoc Distributed Queries', 1" and "RECONFIGURE". I have checked and double checked the file names and assigned full control to everyone for the folder and file.
I tried to create a linked server to the folder, but got the same error messages. I created an odbc system data source to the folder, but had all the same problems using the dsn as well.
The server is SQL 2005 running on Windows 2003. I am current with service packs and MDAC updates.
As far as I can tell, the db configuration of my dev box and productions box are the same. I am not using any linked servers on either machine.
It seems obvious that there is some configuration setting I have missed. Anyone have any ideas for me?
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
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.
I having an excel file called TEST.XLS (in c: drive) which has 3 columns (c0,c1,c2) & 8 rows c0 c1 c2 1 a A 2 b B 3 c C 4 d D 5 e E 6 f F 7 g G 8 h H
In sql server i have one table called TEST which has three columns c0 char(10) c1 char(10) c2 char(10)
when i runnig the following query
select * into TEST from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:TEST.xls;HDR=YES', 'select * from [Sheet1$]')
i am getting error like this
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 'Sheet1$'. 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: ].
Version : Sql server 2000 & Excel 2002
Anybody having any idea why this error occuring? thanks in advance... Philkos
I am having trouble exporting the correct data from sql server to an excel spreadsheet using 'OPENROWSET'
The problem is that although the data from my sql table is say : 'a',1,2,3 , the excel spreadsheet sees the data as : 'a','1,'2,'3 ..1,2,3 are of course numbers NOT text, its just that the driver has put a single appostraphy before the number !
I know there is a bug with the ISAM driver but has anyone managed to solve this or has anyone have any alternatives ?
I'm doing an openrowset query on an excel sheet. (Using SQL Server 2005) Everything works great, except that I have one column that has both numeric and text data in the spreadsheet. The query returns that column as datatype varchar but puts nulls in the rows that have numeric data in the spreadsheet.
Any suggestions?
I run:
select * from openrowset('microsoft.jet.oledb.4.0', 'Excel 8.0;database=[filepathandname], 'select * from [Sheet1$A4:G5000]')
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select * from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
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?
Same with OpenDataSource. SQL 2000, logged in as non-SA.Getting message:Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has beendenied. You must access this provider through a linked server.Can run with SQLOLEDB with connection string to same server(obviously), but when trying to access an Excel file, get the messageabove.It appears that the file name given in the connection string to Excelis with respect to the SQL server, so I have used UNC names to thefile. Nothing.Want to avoid DTS when it's a table that I need and can't use a linkedserver because it's always a one time thing.Any tips appreciated!
