SQL2005 OPENROWSET
Dec 4, 2007
Hi!
I'm working with SQL 2005 and I want to connect to other server but i can't use linked server option because of secutirty policies.
In the remot server I have a stored procedure which retrieve me information depending on one header and the date i'm consulting. Then I use that information in my local server and i need to automate the process.
I have been trying with OPENROWSET and it works ok, but just with the first header, when I execute it with other header it doesn't work.
Select * from OPENROWSET('SQLOLEDB',Server; 'user';'pwd', 'Exec servidor.esquema.sp_Consulta 2,''20070101'',''20071031''')
______________________________
Mens. 7355, Nivel 16, Estado 1, Línea 1
The OLE DB provider "SQLNCLI" for linked server "(null)" supplied inconsistent metadata for a column. The name was changed at execution time.
Another issue i want to solve is that i want to sent the date as a parameter, 'cause i need the information montly.
I was reading that the openrowset and opendatasource are macros not functions, I think that's why it doesn't accept parameters in the string of the query, but now i don't know how to do to work with all the information (remote and local) without OPENROWSET, OPENDATASOURCE, LINK SERVER or an SSIS.
Help plis!!!
View 4 Replies
ADVERTISEMENT
Dec 15, 2005
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
View 62 Replies
View Related
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
View 4 Replies
View Related
Dec 27, 2005
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies
View Related
Jan 4, 2007
Hi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
View 4 Replies
View Related
Sep 19, 2007
I am unable to install 32-bit SQL Server Integration Services on the server due to something that was left behind by the 64-bit version.
I've uninstalled SQL Server 2005 64-bit and when I try to install the 32-bit version of Integration Services, I get this error: "Failed to install and configure assemblies C:Program Files (x86)Microsoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087 Error message: Unknown error 0x80131501 Error descrition: FATAL: Could not find component 'Microsoft.SqlServer.Dts.Task.MessageQueueTask.ServCompMQTask' we just installed."
I can't seem to figure out how to resolve this problem with the COM+ and I can't remember if Integration Services is required.
Can anybody please advise?
View 1 Replies
View Related
Jul 8, 2007
Hello,
I have a vb program that include a dts package that has been saved to vb with sql2000 dts wizard and works very good.
Now that I upgrade my website to sql2005, this vb dts package doesn't work.
The error I get is:
Microsoft Data Transformation Services (DTS) Package
Invalid STDGMEDIUM structure
(Microsoft Data Transformation Services (DTS) Package (80040066): Invalid STDGMEDIUM structure
) (Microsoft SQL-DMO (ODBC SQLState: 42000) (80004005): [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.)
I searched in the internet how to make dts package in sql2005 and save it to vb and found no information about it.
What Can I do to get the vb code of the dts package I create in sql2005 or how do i migrage the sql2000 vb dts package code to sql2005?
Thanks,
Kubyustus
View 4 Replies
View Related
Nov 13, 2006
I have some production boxes on Win 2000 32-bits OS and some production servers have been upgraded to Win2003 64-bit OS runing SQL Server 2005. There are also a number of Win2003 32-bit OS running SQL 2005.
The issue is that when linking the 64-bit production servers to the 32-bit boxes running SQL 2005 / Win 2003 OS, the linking seems to succeed, but I am unable to see a number of entries in sys.objects. Typically, these objects are User Stored Procedures.
Moreover, the linking seemed to have worked, but data extraction does not take place between the servers. However, there are no errors. The objects (user stored procs) exist on the 64-bit side, but linking does not actually happen.
Microsoft KB has addressed this in SQL 2000 case in this KB article, but has not suggested a solution for SQL 2005.
Any ideas? Has anyone else encountered this?
Thanks.
View 1 Replies
View Related
Nov 20, 2002
How come when I run the script:
select * from openrowset ('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=\inawwwpro01d$atrUploadACK102.xls' , 'select * from [sheet1$]') from Server A I get a RESULT, and when I run from Server B I get the following error:
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
Both servers are using IDENTICAL SQL Logins.
Both servers are SQL 2000 SP2.
When I run the script on Server B logged in as SA, then I get a Result!!!
View 4 Replies
View Related
Mar 7, 2005
I can successfully retreive data using:
Declare @Path varchar(100)
Declare @CommandString varchar(100)
Set @Path = ''
Set @CommandString = 'Select * from [myTable.csv]'
EXEC('SELECT *
from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=\myServermySharedb' + @Path + ';'',''' + @CommandString + ''')')
Does anyone know an easy method to get column names into a table using OpenRowset :confused:
View 1 Replies
View Related
Jan 14, 2008
how can i use openrowset. my aim is to develop a import and export data from diff. server . i am using vb.net 2003 , sql sever 2000.
when i am running
openroeset function with window or server authontication it is showing
'OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
anybuddy help me , pls. tell me the right solution.
View 3 Replies
View Related
Aug 21, 2007
Hi!
I decided to use OPENROWSET for importing data from an excel file into a sql table.When I import the data,I have a problem:
-not all the data is imported to my sql table
-some values in the sql table are different from the ones in the excel file
For example,a value that in the excel file is:87878787 will be in the SQL table:8.78788e+007.
Can somebody pls tell me what's the problem
View 1 Replies
View Related
Jul 23, 2005
Hi Folks,I am trying to load data from a table in MS Access to SQL Server 2000using T-SQL OPENROWSET. When I select data from the remote database (MSAccess) using SQL Query analyzer, the columns do NOT appear in the sameorder as seen in Access directly.For e.g. if Access table has columns Cy, Cx, Cz the output in Queryanalyzer appears as Cx, Cy, Cz. It appears to arrange the fieldsalphabetically. This causes problems when I do a 'insert into select *from' as the field definitions do not agree.Is this a bug or is there a setting in Access/SQL which I am missing?Also, please let me know if there is a workaround for this issue.Thanks in advance!Bhaskar
View 3 Replies
View Related
Oct 15, 2014
I've got some text files that I need to read and make some calculations, before inserting data into a table.
When I use:
Code:
select BulkColumn as txt from openrowset(BULK 'c:Tempdata35.txt', SINGLE_NCLOB) as text
It only retrieves the first row. How can I select all the lines in the file?
View 1 Replies
View Related
Mar 25, 2004
I am trying to use OPENROWSET in SQL Server to connect to my Access database, but I keep getting the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file 'X:SetupDatabaseKDB_X2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I have checked the database, it is not in use, and there is no password set on the database that would prevent me from getting access to it. Any ideas? Here's the syntax I'm using:
SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',
'X:SetupDatabaseKDB_X2.mdb';'Admin';'', subPSEL_PList))
View 3 Replies
View Related
Jun 20, 2008
select @xml = bulkcolumn from openrowset(bulk 'C:Documents and
SettingsKasiDesktopewsrss.xml' , single_blob) as channel
here after bulk instead of giving path, we have to give parameter so that the paramter takes the value from the table.
the tables contains paths of xml files
View 2 Replies
View Related
Nov 7, 2006
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
View 2 Replies
View Related
Jan 14, 2007
Hi Anyone I am new member and happy to be here.
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 ?
best regards
Reza
R Khan
View 3 Replies
View Related
Apr 17, 2007
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]')
on spreadsheet data that looks like:
Model_id Model_name
. . .
123 t4556
124 x225
125 455
. . .
and get something like . . . .
Model_id Model_name
. . .
123 t4556
124 x225
125 NULL
. . .
View 1 Replies
View Related
Nov 30, 2007
Hi I have a table like:
LIB TABLENAME TABLEDESCR FIELDNAME FIELDDESCR
BISxxSETALPA0AsometableBS12A0somefield
BISxxSETALPA0AsometablePERCA0somotherfield
BISxxSETALPA1AsomeothertableCMPNA1somefield2
BISxxSETALPA1AsomeothertableMERIA1someotherfield2
BISFFBUAALPA1AanothertablePAARSsomefield3
and would like to create views for each distinct table, using openrowset.
An added complexity is that the library name depends on the company code (i.e. BISxxSET.ALPA0A turns to BIS03SET.ALPA0A for company 03 whereas BSFFBUA.ALPA1A remains intact).
Thanks in advance
Kyriakos
View 4 Replies
View Related
Mar 14, 2008
Hi
I've got the following code that uses OPENROWSET but I need to paramaterise the filepaths and its not working:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
BEGIN
declare @dataareaid as varchar(3)
declare @configpath as varchar(100)
declare @configfile as varchar(100)
declare @configformatfile as varchar(100)
set @configfile = 'C:ConfigFileConfigFileMot.txt'
set @configformatfile = 'C:ConfigFileConfigFormatFile.txt'
print @dataareaid
print @configpath
print @configfile
print @configformatfile
set @dataareaid = (SELECT dataareaid
FROM OPENROWSET( BULK @configfile , FORMATFILE = @configformatfile
) AS a)
select *
FROM AX.[P2].dbo.Dimensions Dim
WHEREDim.dataareaid = @dataareaid
END
View 3 Replies
View Related
Jul 23, 2005
Hy Forum !I tried to do somsthing like this:CREATE PROCEDURE dbo.maches ASselect * from openrowset('Microsoft.Jet.OLEDB.4.0','C: empFehler.mdb'; 'Administrator'; ' ' , Fehlerliste)This user 'Admistrator' exists and there is no password for the .mdbfile. In the end the syntax checker tells me:Fehler 7303: Datenquellenoblekt von OLE DB Provider'Microsoft.Jet.OLEDB.4.0' konnte nicht initialisiert werden.I'm using Access 2000 and SQL Server 7.Thank you
View 1 Replies
View Related
Apr 5, 2006
Please let me know the advantages and disadvantages of XQuery vsOpenRowSet in SQL Server 2005. Which would be better?Regards,Shilpa
View 1 Replies
View Related
Jan 11, 2007
Hi
I've got an openrowset query that works perfectly if I enter the computer name, but it fails when I enter the IP address.
Is there a setting somewhere that I need to enable to make this happen?
Thanks in advance
View 5 Replies
View Related
Aug 28, 2006
I have an excel file on a web server on a different location to the sql server. I want to use OPENROWSET to query the excel file, but it doesn't seem to like using a URL.
Is this the case, or am I doing it wrong? Works fine when I use a local address (eg c:excelfile.xls) when testing locally, but I need to use a URL in production.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=http://mydot.com/excelfile.xls','SELECT * FROM [page1$]')
View 1 Replies
View Related
Jul 22, 2006
I have an access database from which i want to use data in sql server if a table has column with values like 6.32404E-244 i've got an error message like:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)' returned invalid data for column '[Microsoft.Jet.OLEDB.4.0].Stoc_Max'
a simple select like 'SELECT * FROM OPENROWSET(.....,table) gives me this error.
View 3 Replies
View Related
Oct 25, 2007
Hi All,
Does anyone know or have a sample of a syntax of OpenRowset using a UDL file?
Regards,
Joseph
View 1 Replies
View Related
May 10, 2007
We have lots of OPENROWSET usage here, all with embedded user id's and passwords. In an attempt to find a way to eliminate such a foolish practice I created a system DSN on my PC and tried to use it to connect to our sqlserver 2000 database as follows:
SELECT a.*
FROM OPENROWSET('MSDASQL','DSN=TargetDB',
'SELECT * FROM table1') AS a
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Authentication failed.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I thought the reason to create an ODBC system data source was to not have to enter credentials in code but use the credentials in the system DSN. I guess I was wrong. Is there any way to not embed user id's and passwords in OPENROWSET code? Having user id's and passwords in code is dangerous and foolish and I am surprised people do it.
View 4 Replies
View Related
Jul 11, 2006
Hi everyone,
I have some doubts about OPENROWSET T-SQL function . According to my knowledges about this, openrowset function provide us to use a data from a different Server only that time that we use this function. After we use this function, there is no any linked server trace on the local server. So is this true ?
I see that everyone use different parameter for this function. For instance, everyone firstly use OLE DB name and then the second srvprovider name changes programmer to programmer, so why ?
And lastly, I wonder about the relationship between ad hoc method and openrowSet function.
Thanks
View 12 Replies
View Related
Aug 21, 2007
Hi!
I'm using OPENROWSET to import data from an excel file into a MS SQL table.I got it to work.....Now,my problem is that not all the data from the SQL table is imported and some values are different than the ones in the excel file.
For example,in the excel file,I have the value:87987845.In the SQL table,the value is:8.79878e+007.Any idea on what causes this?
View 8 Replies
View Related
Apr 18, 2008
hey..
I have to loop through number of tables on a remote servers.
Now i have witten a script(simple sql script) which loops though tables names and gets data using OpenRowSet().
My alternative way is through SSIS, keeping RetainSameConnecion=True, and then looping.
My question is,
Does calling OpenRowSet, creates a new connection each time the statement is executed?
If Not, then how's SSIS better in this case?
(please confirm that, since i was not able to see any audit login/logout in profiler)
------------------------------------------------------------------------------------------------------------------------------------------------
Here's how i tested it..
I executed OPENROWSET 3 times in a sequence. But in Profiler i see only following events..
Audit Login
Batch Started
Batch Completed
RPC:Completed.
RPC:Completed.
:
:
:
:
RPC:Completed.
I'm intersted only to see if the login happens three time, but i'm not able to see that. Also, there was no Audit Logout.
Does SQL Server automatically pool connections ?
If i run Openrowset 3 times, shouldn't it open & close connection 3 times ?
Please clarify.
View 1 Replies
View Related
May 17, 2007
I have a text file, that I need to import to SQL. I am trying to do this using OPENROWSET. This is the qry i use below.
select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver
(*.txt; *.csv)};
DefaultDir=c:edge;',
'select * from
vwConstrLendingLoanDefiDetailReady.txt')
I have created a System DSN, For the microsoft text driver. Anyhow it throws this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Any idea why?
View 5 Replies
View Related
Aug 11, 2006
Hi all.
Seeing that the old system table syslang is no longer updateable(Not even in DAC) i decided to take a look at the definition of it, just to see where the languages are stored.(out of curiosity)
Select Object_Definition(Object_Id('SysLanguages'))
and it returns
Select .......
FROM OpenRowset(TABLE SYSLANG)
The odd thing is I tried to run that query but it gives me an error telling an incorrect sintax near TABLE.
so, the question is if does anybody knows why i can't run the same query that the object_definition returned?
any comments are most apreciated
View 3 Replies
View Related
