Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the 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 "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:
> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the 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 "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>
Showing posts with label exec. Show all posts
Showing posts with label exec. Show all posts
Thursday, March 29, 2012
Wednesday, March 7, 2012
Creating a Dynamic Temporary Table
When I execute the following Stored Procedure with a parameter (EXEC MyProc
'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
@.MyTempTable that was passed in the SQL statement?
I meant to create the temp table named MyTab as passed to the SP from the
statement.
CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
SELECT *
INTO ##MyTempTable
FROM Customers
GOThe right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your application is a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Stop writing code like this. You are mimicking a 1950's scratch tape
file. But more than that, you never used the parameter anywhere in the
procedure -- nothing happened to it. Also, I see you used the
"Magical NVARCHAR(50)" data type. Do you really have names that long?
I doubt it. You will eventually get such a garbage name; I can give
you a Chinese Suttra if you want to do use it :)
Use the Customers table in your statements. Unfortunately, we have no
idea what you wanted to do, so nobody can help you further.|||Shariq,
I have a little confusion as to what you are attempting to achieve.
The short answer to what you asking is as follows:
CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
@.MyTempTable nvarchar(50)=''
AS
If @.MyTempTable<>''
BEGIN
DECLARE @.sSTR varchar(2000)
SET @.sSTR = ' '
SET @.sSTR = @.sSTR + ' SELECT * INTO '
SET @.sSTR = @.sSTR + '#' + @.MyTempTable
SET @.sSTR = @.sSTR + ' FROM Customers '
EXEC (@.sSTR)
END
This will achieve the result your question poses, but does not a lot to
possibly achieve your intended goal.
The one drawback to the above code is that the EXEC(@.sSTR) command runs in
an independant thread from the Stored Procedure and such a table cannot be
accessed from another thread, unless you use the ## prefix, but this, again,
brings up it's own dilemmas as the same Stored Procedure acn only be run onc
e
at a time.
"Shariq" wrote:
> When I execute the following Stored Procedure with a parameter (EXEC MyPro
c
> 'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
> @.MyTempTable that was passed in the SQL statement?
> I meant to create the temp table named MyTab as passed to the SP from the
> statement.
> CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
> SELECT *
> INTO ##MyTempTable
> FROM Customers
> GO
>|||Tony,
Thanks for you help; the code you provided is exactly what I was looking for
.
"Tony Scott" wrote:
> Shariq,
> I have a little confusion as to what you are attempting to achieve.
> The short answer to what you asking is as follows:
> CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
> @.MyTempTable nvarchar(50)=''
> AS
> If @.MyTempTable<>''
> BEGIN
> DECLARE @.sSTR varchar(2000)
> SET @.sSTR = ' '
> SET @.sSTR = @.sSTR + ' SELECT * INTO '
> SET @.sSTR = @.sSTR + '#' + @.MyTempTable
> SET @.sSTR = @.sSTR + ' FROM Customers '
> EXEC (@.sSTR)
> END
> This will achieve the result your question poses, but does not a lot to
> possibly achieve your intended goal.
> The one drawback to the above code is that the EXEC(@.sSTR) command runs in
> an independant thread from the Stored Procedure and such a table cannot be
> accessed from another thread, unless you use the ## prefix, but this, agai
n,
> brings up it's own dilemmas as the same Stored Procedure acn only be run o
nce
> at a time.
>
> "Shariq" wrote:
>
'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
@.MyTempTable that was passed in the SQL statement?
I meant to create the temp table named MyTab as passed to the SP from the
statement.
CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
SELECT *
INTO ##MyTempTable
FROM Customers
GOThe right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your application is a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Stop writing code like this. You are mimicking a 1950's scratch tape
file. But more than that, you never used the parameter anywhere in the
procedure -- nothing happened to it. Also, I see you used the
"Magical NVARCHAR(50)" data type. Do you really have names that long?
I doubt it. You will eventually get such a garbage name; I can give
you a Chinese Suttra if you want to do use it :)
Use the Customers table in your statements. Unfortunately, we have no
idea what you wanted to do, so nobody can help you further.|||Shariq,
I have a little confusion as to what you are attempting to achieve.
The short answer to what you asking is as follows:
CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
@.MyTempTable nvarchar(50)=''
AS
If @.MyTempTable<>''
BEGIN
DECLARE @.sSTR varchar(2000)
SET @.sSTR = ' '
SET @.sSTR = @.sSTR + ' SELECT * INTO '
SET @.sSTR = @.sSTR + '#' + @.MyTempTable
SET @.sSTR = @.sSTR + ' FROM Customers '
EXEC (@.sSTR)
END
This will achieve the result your question poses, but does not a lot to
possibly achieve your intended goal.
The one drawback to the above code is that the EXEC(@.sSTR) command runs in
an independant thread from the Stored Procedure and such a table cannot be
accessed from another thread, unless you use the ## prefix, but this, again,
brings up it's own dilemmas as the same Stored Procedure acn only be run onc
e
at a time.
"Shariq" wrote:
> When I execute the following Stored Procedure with a parameter (EXEC MyPro
c
> 'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
> @.MyTempTable that was passed in the SQL statement?
> I meant to create the temp table named MyTab as passed to the SP from the
> statement.
> CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
> SELECT *
> INTO ##MyTempTable
> FROM Customers
> GO
>|||Tony,
Thanks for you help; the code you provided is exactly what I was looking for
.
"Tony Scott" wrote:
> Shariq,
> I have a little confusion as to what you are attempting to achieve.
> The short answer to what you asking is as follows:
> CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
> @.MyTempTable nvarchar(50)=''
> AS
> If @.MyTempTable<>''
> BEGIN
> DECLARE @.sSTR varchar(2000)
> SET @.sSTR = ' '
> SET @.sSTR = @.sSTR + ' SELECT * INTO '
> SET @.sSTR = @.sSTR + '#' + @.MyTempTable
> SET @.sSTR = @.sSTR + ' FROM Customers '
> EXEC (@.sSTR)
> END
> This will achieve the result your question poses, but does not a lot to
> possibly achieve your intended goal.
> The one drawback to the above code is that the EXEC(@.sSTR) command runs in
> an independant thread from the Stored Procedure and such a table cannot be
> accessed from another thread, unless you use the ## prefix, but this, agai
n,
> brings up it's own dilemmas as the same Stored Procedure acn only be run o
nce
> at a time.
>
> "Shariq" wrote:
>
Friday, February 24, 2012
Creating a Cursor out of results of a SP
Is it possible to create a cursor out of the results of a stored procedure.
OR
Is it possible to get just one value.
EXEC sp_columns @.table_name = 'tablename', @.column_name='columnname'
--I want the type_name valueYou can use the INFORMATION_SCHEMA.COLUMN view...
What data do you want?|||I will look at that.
I was able to create a temp table, when i figured out the right syntax, and then selected the appropriate colomn out of the temp table.|||Good for you...
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable' AND COLUMN_NAME = 'yourColumn'
OR
Is it possible to get just one value.
EXEC sp_columns @.table_name = 'tablename', @.column_name='columnname'
--I want the type_name valueYou can use the INFORMATION_SCHEMA.COLUMN view...
What data do you want?|||I will look at that.
I was able to create a temp table, when i figured out the right syntax, and then selected the appropriate colomn out of the temp table.|||Good for you...
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable' AND COLUMN_NAME = 'yourColumn'
Sunday, February 19, 2012
CreateMHTMLBody retuns "Invalid syntax" error
Hi,
I want to add html as body of my mail message. I added CreateMHTMLBody method in my SQL code.
Syntax I used is
Exec @.hr=sp_oamethod @.imsg,'CreateMHTMLBody',NULL,@.MsgFile,0,'',''
This syntax is working fine on my system but whem I implement this on production it gives error.
Needed ASAP as I have to implmenet this on my production server today.
Thanks
GAurav
What is the error message you are getting|||are you running the same version of cdosys.dll on dev and prod?|||Why use CDO when you have sql database mail.
CreateMHTMLBody expects 4 parameters (url, cdomhtmlflags, user, pwd) so check your @.MsgFile.
|||Check the required COM DLLs are registered properly on your server.|||Using SQL database mail you can't send the HTML mails where message body is created from the HTML file
And my syntax is correct as it is working perfectly fine on my development env. but it is not working on production
|||Yes COM dll is properly registerde and it is sending Textbody mail. But when I use this CreateMHTMLBoby it gives me above error. And my code and syntax is also correct as it is working fine on development env.
Thanks
Subscribe to:
Posts (Atom)