PROBLEM WIHT OPENROWSET FUNCTION

HI FRIENDS THIS IS AMIT. THE PROBLEM IS I M TRYING TO EXPORT DATA FROM SQL SERVER TABLE TO EXCEL FILE USING FOLLOWING CODE SNIPPET,

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 7.0;Database=c:\testing.xls;',
'SELECT * FROM [Sheet1$]') select * from TABLE1

BUT IT IS SHOWING SOME ERROR WHICH IS AS FOLLOWS,

Invalid object name 'OPENROWSET'.

I FOUND OPENROWSET FUNCTION IN T-SQL REFERENCE BUT STILL THE ABOVE MESSAGE IS COMING PLS HELP IN THIS MATTER ASAP.

REGARDS,

AMIT.

What version of SQL Server are you using (show the output of SELECT @.@.VERSION)?

Steve Kass
Drew University
http://www.stevekass.com|||

Make sure you have "Ad Hoc Remote Queries" enabled for your instance.

|||Kindly provide the version of the SQL Server you are using, it should ideally work without problem in SQL Server 2000 and above. You can however try the same using DTS (Data Transformation Services)|||Excel 7.0??

Try Excel 5.0 or Excel 8.0

Problem while making Setup of Project

Hello friends,
While making a setup copy of my project I got the f errors in the following files-
i)d:\winnt\system32\crpe32.dll
ii)d:\winnt\system32\msvcrt.dll
iii)d:\winnt\system32\mfc42.dll
The error is-
The destination file is in use. Please ensure all other applications are closed. You have a chance to Abort, Retry, Ignore..If U ignore again the following message-
If U ignore, the file will not be copied and application may not run properly. Do U want to ignore.
ANother error I got was-
An error occured while registering d:\winnt\system32\msado20.tlb

Please let me know how to overcome these 4 errors...By the way I developed in VB6.0 and Oracle8i with Crystal Report 7.0 for reporting...O/S is Win 2000 Advanced Server..

Bye and Thanx...Hi,
When creating setup make sure that all the applications are closed. This may solve the first three problems. For the last problem, Use Microsoft ActiveX Data objects 2.0 library in the project.

Madhivanan|||Hai

I am also facing the same problem. but Mathi's solution does not solve the problem. The same problem exists again and again.

Anyone knows, help us.

Regards,

Velayudham.|||Hi Madhi,
Thanx for ur reply...But I tried both that u suggest...Even then it's still not workin...Any other suggestion...

Velayudham if U get the solution pl let me know...|||Hi

which version of Crystal Report are you using? Try to register those dlls.

For the last error try the following

In your package and deployment look for the file setup.lst then under the [Setup1 Files] section look for the entry of Msado20.tlb, make sure that the registration is set to TLBRegister instead of DLLSelfRegister. Package and deployment wizard causes this error, just change and save your setup.lst then re-install your package

Madhivanan|||Hi,

I had got a new installer. You can try this.

Use this link: http://www.jrsoftware.org/isdl.php

have a good day. Bye.|||Hello Madhi,
Still no working ya...Pl say some other way...

Velayudham,
Thanx... I'll try...|||Hi friends, pl help me-

I implemented a s/w in an organisation..It has win 2000 advanced server as o/s, oracle 8i as RDBMS, Front End in VB 6.0 and reporting in crystal report..

Now there are two tables service_usage_detail and invoice-detail. invoice-detail has a FK to service_usage_detail...

Bfore I could updated any service_name in the invoice_detail table, if the user does any mistake..But now I'm not being able to do the updation as it gives an eror message- Parent key not found Error no 2291...

By the way bfore the problem persists I did defragmentation and check (Scandisk I suppose) of all drives of the HDD from drive property's Tools tab...Out of the drives I skiped defragmentation half way on drive H:..I have noticed that the prob arises after that...

Problem using Portugueses characters

Dear friends,
I got a XML string that I need to convert in a select statement. I'm using
OPENXML to do it and everything works well, but when I write Portugueses
words in my XML document(for sample: valida?o) is shown something weird
like: valida?o
I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
on, but nothing works. Check out my code.
declare @.idoc int
declare @.doc_xml_nota varchar(8000)
set @.doc_xml_nota = '
<?xml version="1.0" encoding="ISO-8859-1"?>
<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida?o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
select *
FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico', 2)
WITH
(
des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
)
EXEC sp_xml_removedocument @.idoc
Thanks,
Euler Almeida
Message posted via http://www.sqlmonster.com
Can you use Unicode characters instead?
E.g,
declare @.doc_xml_nota nvarchar(8000)
set @.doc_xml_nota = N'<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida?o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet> '
Otherwise, you need to make sure that the database collation and the
encoding inside the XML and the character codes are all aligned...
Best regards
Michael
"Euler Almeida via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cdc1fae2f9d34240ac26017dcba8b8aa@.SQLMonster.c om...
> Dear friends,
> I got a XML string that I need to convert in a select statement. I'm using
> OPENXML to do it and everything works well, but when I write Portugueses
> words in my XML document(for sample: valida?o) is shown something weird
> like: valida?o
> I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
> on, but nothing works. Check out my code.
> declare @.idoc int
> declare @.doc_xml_nota varchar(8000)
> set @.doc_xml_nota = '
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <NewDataSet>
> <nota_fiscal_fornecedor_setor_publico>
> <des_informacao_complement>valida?o </des_informacao_complement>
> </nota_fiscal_fornecedor_setor_publico>
> </NewDataSet>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
> select *
> FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico',
> 2)
> WITH
> (
> des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
> )
> EXEC sp_xml_removedocument @.idoc
>
> Thanks,
> Euler Almeida
> --
> Message posted via http://www.sqlmonster.com

Problem using Portugueses characters

Dear friends,
I got a XML string that I need to convert in a select statement. I'm using
OPENXML to do it and everything works well, but when I write Portugueses
words in my XML document(for sample: valida'o) is shown something weird
like: valida'o
I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
on, but nothing works. Check out my code.
---
declare @.idoc int
declare @.doc_xml_nota varchar(8000)
set @.doc_xml_nota = '
<?xml version="1.0" encoding="ISO-8859-1"?>
<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida'o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
select *
FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico', 2)
WITH
(
des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
)
EXEC sp_xml_removedocument @.idoc
Thanks,
Euler Almeida
Message posted via http://www.webservertalk.comCan you use Unicode characters instead?
E.g,
declare @.doc_xml_nota nvarchar(8000)
set @.doc_xml_nota = N'<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida'o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet> '
Otherwise, you need to make sure that the database collation and the
encoding inside the XML and the character codes are all aligned...
Best regards
Michael
"Euler Almeida via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:cdc1fae2f9d34240ac26017dcba8b8aa@.SQ
webservertalk.com...
> Dear friends,
> I got a XML string that I need to convert in a select statement. I'm using
> OPENXML to do it and everything works well, but when I write Portugueses
> words in my XML document(for sample: valida'o) is shown something weird
> like: valida'o
> I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
> on, but nothing works. Check out my code.
> ---
> declare @.idoc int
> declare @.doc_xml_nota varchar(8000)
> set @.doc_xml_nota = '
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <NewDataSet>
> <nota_fiscal_fornecedor_setor_publico>
> <des_informacao_complement>valida'o </des_informacao_complement>
> </nota_fiscal_fornecedor_setor_publico>
> </NewDataSet>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
> select *
> FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico',
> 2)
> WITH
> (
> des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
> )
> EXEC sp_xml_removedocument @.idoc
>
> Thanks,
> Euler Almeida
> --
> Message posted via http://www.webservertalk.com