Extract hh AM/PM from getdate()

Hi,
I am looking for a query to extract hour and AM or PM value from a date on sql2000.

ex/-
Input : 2001-12-28 22:18:07.810 (from getdate())
Output : 10 PM

select convert(varchar, (datepart(hh, convert(varchar, getdate(), 8)) % 12)) + ' ' +
substring (convert(varchar, convert(datetime, getdate(),20), 100),
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100)) - 1,
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100 )))

The above works but is there a better way to do this?This is a little shorter:

SELECT CONVERT(VARCHAR,DATEPART(hh,GETDATE())%12) +
CASE WHEN (DATEPART(hh,GETDATE())%12) > 0 THEN ' PM' ELSE ' AM' END|||thanks for your reply.
but i figured that 12 AM or 12 PM was displayed as 0 AM and 0 PM.
Hence to reduce my troubles, i will stick with the good ol' substring.

SELECT (substring(CONVERT(VARCHAR,getdate(),22),10,2) + ' ' +
substring(CONVERT(VARCHAR,getdate(),22), 19,2))

Extra xml node

This is in sql 2005
I have a query that is returning a set of rows each with one xml field
row1 -<apple></apple>
row3 -<orange></orange>
row2-<grape></grape>
I want the output to look like this
<fruits>
<apple></apple>
<orange></orange>
<grape></grape>
</fruits>
However when I use FOR XML AUTO, root(''Fruits'')
<fruits>
<fruit><apple></apple></fruit>
<fruit><orange></orange></fruit>
<fruit> <grape></grape></fruit>
</fruits>
How do I remove the extra fruit element?
ENDHello Hyper,
Try using a FOR XML PATH query instead, ala for xml path (''),root('fruits')
,type
Thanks!
Kent

> This is in sql 2005
> I have a query that is returning a set of rows each with one xml field
> row1 -<apple></apple>
> row3 -<orange></orange>
> row2-<grape></grape>
> I want the output to look like this
> <fruits>
> <apple></apple>
> <orange></orange>
> <grape></grape>
> </fruits>
> However when I use FOR XML AUTO, root(''Fruits'')
> <fruits>
> <fruit><apple></apple></fruit>
> <fruit><orange></orange></fruit>
> <fruit> <grape></grape></fruit>
> </fruits>
> How do I remove the extra fruit element?
> END
>
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Exterpise Manager Select Export (ASCII,Excel,Access)?

Hi All. A client needs to send me some sample data. He has insisted he can query the table in Enterprise Manager via a simple select... "Select * from Table1"...

Now I need to get the data in some simple form (ASCII, Excel, Access, etc.) sent to me.

Can someone please provide me the info so I can pass it on for him to query a table from Enterprise Manager and "export it" to a simple file so I can receive it.

ANY THOUGHTS would be helpfull and GREATLY Appreciated!

Thanks.

BillIf this is a one timer I would go for "Tools -> Data Transformation Services -> Export Data".

// Pati|||Hey Pati... It may just be a one timer... but if the data looks good, it may be more frequent. it turns out some other process is taking records from this table, and may be removing them... Part of the reason we are trying to get some snapshots of the data.

I'm trying not to write an app until I know if we need this data.

Is it really simple to use to do the menu picks? Doesn't seem like the end user is very experienced, nor am I on Sql Server.

Thanks for the thoughts.

Bill|||If you think that you will need the same procedure again then you can save the DTS package for further use and then schedule it to run as desired.
...or then for another approach you could automate everything with scripts/scheduled jobs.

But as you're saying that you don't have much experience in SQL Server and that this might just be a one off solution then I would stick to DTS.

// Patisql

Extending CDOSYS Mail to include Query Attachments?

Hi,

I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?

I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech

Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail

Thanks,
KimHi,

I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?

I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech

Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail

Thanks,
Kim

Jasper has written an sp for it check it out here:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649|||Sorry, I don't think I explained myself very well!

I currently use xp_sendmail and pass a query as a parameter and attach the results of this query to an email. I would like to know if anyone has extended CDOSYS Mail to have the same functionality? Example of how I use this is in xp_sendmail @.query parameter would be a query which returns the total number of records on an import table and xp_sendmail sends the results in an email attachement OR sends the results as part of the body of the email.

Extract of the xp_sendmail Syntax:
xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.query =] 'query']

[,[@.attach_results =] 'attach_value']|||I had the same problem and found a workaround using osql to write the results of the query to a file. I then include the file as an attachment and delete it from the server. Seems to work well even within a loop.

I used this to work from.
http://www.sqlteam.com/item.asp?ItemID=4722

And came up with this command line that seems to create a file with the same format as the attach query results did with sendMail:

SET @.bcpCommand = "osql -h-1 -w800 /U usrId /P pw /d " + @.dbName + " /Q ""myprocname parm1, parm2"" -o "
SET @.bcpCommand = @.bcpCommand + @.FileName

EXEC master..xp_cmdshell @.bcpCommand

Then just pass @.fileName as the attachment to the cdosys mail procedure. I then use xp_cmdshell to delete the file from the server.

Extended Stored Procedure

When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. When
I add the same to a stored proc my app hangs and I get a timeout.
Any help is greatly appreciated. I really don't want to create a job that
runs this.
EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) ENDHi
At a guess as the application hangs it probably means it can't find the
object, which implies it is not being run in the same context and/or possibl
y
a lack of permissions. You also need to be symin to run sp_OACreate.
John
"Mark - HYPERe" wrote:

> When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. Wh
en
> I add the same to a stored proc my app hangs and I get a timeout.
> Any help is greatly appreciated. I really don't want to create a job that
> runs this.
> EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
> IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) END

Extended Store Procedure Query

Please help me..
I am stuck on the Problem related to Store Procedure
I want to use Extended Store Procedure.
I read tech document related that on MSDN,
but i am unable to register the Extended Store Procedure.
Please guide me step wise step, So that I can register the extended
store Procedure (.DLL)
File in SQL Server.
I also want to Kow How to use it in our Program.
With regards
Tarun SinhaCopy the DLL into a suitable directory on the machine running
SQL Server, typically
C:\Program Files\Microsoft SQL Server\MSSQL\binn
Register the extended stored procedure using
exec sp_addextendedproc N'myxp', N'myxp.dll'
You can execute it using
exec master.dbo.myxp
remembering to supply any parameters it requires.|||To install the DLL,
copy the file to the directory containing the standard SQL Server DLL
files (C:\Program Files\Microsoft SQL Server\Mssql\Binn by default).
then run this (of course your name will be different)
sp_addextendedproc 'xp_YourProc', 'xp_YourProc.dll'
http://sqlservercode.blogspot.com/|||Hello
Thank's for replying me
I would I Know What Parameter the Extended Store Procedure Take .
Please consider this Stupid Question and reply me.
I download xpRegex.dll and copy it in C:\Program Files\Microsoft SQL
Server\MSSQL\binn ,
I Also Register in the extended store Procedure, but How to Know what
parameter it takes.
With regards
Tarun sinha|||In order to find out the parameters it needs for execution, you
will need to refer back to the documentation from where you
downloaded it. You can't tell otherwise.
Registering it doesn't require any parameters, although it is possible
to have multiple entry points in the same DLL. In which case
you would call sp_addextendedproc once per entry point, e.g.
exec sp_addextendedproc N'xpRegex_X', N'xpRegex.dll'
exec sp_addextendedproc N'xpRegex_Y', N'xpRegex.dll'
All this should be explained in the place where you downloaded it.|||Thank You sir
U explain my query so well ..
I am great thank ful to you
with regards
Tarun Sinha

Extended Property

How do I query the extended property of a column (like MS_Description
property). I run the following query and SqlServer returns nothing. But
I see the description in enterprise manager. Am I missing anything here
please?
SELECT *
FROM ::fn_listextendedproperty (N'MS_Description',
N'user', N'dbo', N'table', N'ED_Account', N'column', 'AccountID')Your query looks okay. Can you try the query below,
this should return all column descriptions for your table.
select value,col_name(id, smallid)
from sysproperties
where name='MS_Description'
and id=object_id('dbo.ED_Account')|||Thanks that works
markc...@.hotmail.com wrote:
> Your query looks okay. Can you try the query below,
> this should return all column descriptions for your table.
> select value,col_name(id, smallid)
> from sysproperties
> where name='MS_Description'
> and id=object_id('dbo.ED_Account')|||Note that sysproperties isn't documented and was removed in 2005 (replaced b
y a documented catalog
view). So, I do not recommend that you use it. I have a feeling that Mark su
ggested you the query
just to troubleshoot, verifying that there actually has been defined extende
d properties for this
object (etc.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149091258.427862.291020@.f6g2000cwb.googlegroups.com...
> Thanks that works
> markc...@.hotmail.com wrote:
>

extended connect timeout does not work

I have a stored proc to query a very large database, and it always times out at the 30th sec, even though I have changed the connect timeout to 300, as follows

(Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.)

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Administrator\My Documents\abc.mdf";Integrated Security=True;Connect Timeout=300;User Instance=True

This timeout issue does not occur to smaller databases. Any idea?

hi,

the connection timeout setting is relevant to "connection timeout"

you have to modify the command timeout property within your code so that the executing command enables more time..

tipically

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand With cmd .CommandText = "the command text or procedure name" .CommandType = CommandType.Text ' or CommandType.StoredProcedure .CommandTimeout = 300 'or whatever .Connection = yourConnectionObject End With

regards

|||Thanks for pointing out this. Can this be done once for all / globally, say by using a SQL statement?|||

hi,

AFAIK, that should be done for each command..

regards

|||I wish I am not asking too much, but how can this be done in a stored procedure, please?|||

hi,

actually that should be done outside the procedure, in the command (or whatever you use) to execute the actual procedure..

there's a server wide option, query wait, but I'd not set it to a defined/manual value as this one will be valid for all queries at instance level (again, server wide option).. I'd go for the standard behavior only setting "manual configurations" for particular activities via the (external) command property..

regards

Expression from within sproc?

If I'm inside a stored procedure called by an MDX query, the following would query the cube and figure out the sales for the current tuple:

new Expression("[Measures].[Internet Sales Amount]").Calculate(null).ToDouble()

So I was expecting to be able to do that from within a stored proc called from a DMX predict query. I was hoping that I could evaluate the $Cluster expression from within a sproc and figure out what cluster the current row in the DMX query belongs to. Basically, I'm trying to make my sproc function signature more elegant so I don't have to pass in the NodeId like:

select $Cluster, MyAssembly.MySproc(PredictNodeId(Cluster())) as CoolData
from [Customer Clusters]
NATURAL PREDICTION JOIN
(select * from [Customer Clusters].Cases) as x
where $Cluster = 'Cluster 10'

Is there any way to do that?

Unfortunately no, although this is an improvement we are considering for future versions. The improvement could be to enable you to access the input case directly and/or call the models prediction functions.

expose 1433 on net

I need to exposed 1433 on the net to enable a IIS6 pages to query remote SQL
servers.
Im looking for information on how to do this as securely as possible
(although im sure this is not ideal) ?
At the moment im using SQL authentication only.
Thanks for any information
ScottWhy not install SQLXML and take advantage of the easy stored procedure to
web services tool. Then you can apply an SSL certificate and have your web
site only talk to SQL Server over TCP 443. It's safer, it's encrypted, it's
extensible, and it's more portable. It's SOA!
hth,
Eric
scott wrote:
> I need to exposed 1433 on the net to enable a IIS6 pages to query
> remote SQL servers.
> Im looking for information on how to do this as securely as possible
> (although im sure this is not ideal) ?
> At the moment im using SQL authentication only.
> Thanks for any information
> Scott|||sounds excellent but how do you set it up ?
Currently my aspx app on IIS6 queries SQL on an IP addresson port 1433 and
pulls back XML.
How do i install SQLXML ?
Use the web service tool ?
Setup SSL ?
(guess I just forward port 443)
Sorry for all the questions
Scott|||you should start by reading about sqlxml at
http://www.microsoft.com/sql/techinfo/xml/default.asp
scott wrote:
> sounds excellent but how do you set it up ?
> Currently my aspx app on IIS6 queries SQL on an IP addresson port
> 1433 and pulls back XML.
> How do i install SQLXML ?
> Use the web service tool ?
> Setup SSL ?
> (guess I just forward port 443)
> Sorry for all the questions
> Scott

expose 1433 on net

I need to exposed 1433 on the net to enable a IIS6 pages to query remote SQL
servers.
Im looking for information on how to do this as securely as possible
(although im sure this is not ideal) ?
At the moment im using SQL authentication only.
Thanks for any information
Scott
Why not install SQLXML and take advantage of the easy stored procedure to
web services tool. Then you can apply an SSL certificate and have your web
site only talk to SQL Server over TCP 443. It's safer, it's encrypted, it's
extensible, and it's more portable. It's SOA!
hth,
Eric
scott wrote:
> I need to exposed 1433 on the net to enable a IIS6 pages to query
> remote SQL servers.
> Im looking for information on how to do this as securely as possible
> (although im sure this is not ideal) ?
> At the moment im using SQL authentication only.
> Thanks for any information
> Scott
|||sounds excellent but how do you set it up ?
Currently my aspx app on IIS6 queries SQL on an IP addresson port 1433 and
pulls back XML.
How do i install SQLXML ?
Use the web service tool ?
Setup SSL ?
(guess I just forward port 443)
Sorry for all the questions
Scott
|||you should start by reading about sqlxml at
http://www.microsoft.com/sql/techinfo/xml/default.asp
scott wrote:
> sounds excellent but how do you set it up ?
> Currently my aspx app on IIS6 queries SQL on an IP addresson port
> 1433 and pulls back XML.
> How do i install SQLXML ?
> Use the web service tool ?
> Setup SSL ?
> (guess I just forward port 443)
> Sorry for all the questions
> Scott

Exporting XML using bcp

I am exporting XML query results from a stored procedure using bcp. The query is pulling data from several tables and create XML using FOR XML EXPLICIT.

The following is the bcp command used to export the result:

bcp "exec sprocname" queryout output.xml -w -T

The resulting output file appears to limit the number of charcters per line to 2033 characters and cut the data into the next row regardless whether the 2033th character is in the middle of an xml tag. As a result the resulting xml output become unreadable.

Any suggestions would be much appreciated.

I hope you are using SQL2005. If so, just add ,TYPE at the end of your FOR XML query as

SELECT ...

FOR EXPLICIT, TYPE

Exporting XML using bcp

I am exporting XML query results from a stored procedure using bcp. The query is pulling data from several tables and create XML using FOR XML EXPLICIT.

The following is the bcp command used to export the result:

bcp "exec sprocname" queryout output.xml -w -T

The resulting output file appears to limit the number of charcters per line to 2033 characters and cut the data into the next row regardless whether the 2033th character is in the middle of an xml tag. As a result the resulting xml output become unreadable.

Any suggestions would be much appreciated.

I hope you are using SQL2005. If so, just add ,TYPE at the end of your FOR XML query as

SELECT ...

FOR EXPLICIT, TYPE

exporting xml

Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks
Here's one way:
In your ASP code, use the SQLXMLOLEDB provider to return the XML to a
DOMDocument - specifying an appropriate xml root property to make the XML
well-formed. Then use the Save method of the DOCDocument object to save the
file.
Here's an example (it assumes SQLXML 3.0 is installed):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command (you'd need to add code to generate this dynamically -
this is just an example based on your desired output)
cmd.CommandText = "SELECT ProductID FIELD1, ProductName FIELD2 FROM Products
ROW FOR XML AUTO"
'Specify the root tag
cmd.Properties("xml root") = "ROWSET"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Save the XML
xmlDoc.Save "C:\Results.xml"
The only major issue you'll have is getting your "num" attribute. If the
number relates to a field in the data (e.g. a ProductNo column or similar)
then you'll need to use an EXPLICIT mode query to retrieve it as an
attribute when everything else is an element. If it's not a data column, and
just the number of the row in the result set you'll need to either write a
stored procedure to generate the right values for each row (off the top of
my head, you could retrieve the data into a temp table with an IDENTITY
column and then return the data from that) or you could just retrieve the
data and then add the num attribute to each ROW element in the DOMDocument
before saving.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:ODlXKMIKFHA.580@.TK2MSFTNGP15.phx.gbl...
Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
...
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks

exporting xml

Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
ThanksHere's one way:
In your ASP code, use the SQLXMLOLEDB provider to return the XML to a
DOMDocument - specifying an appropriate xml root property to make the XML
well-formed. Then use the Save method of the DOCDocument object to save the
file.
Here's an example (it assumes SQLXML 3.0 is installed):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
" SERVER=(local);DATABASE=northwind;INTEGR
ATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command (you'd need to add code to generate this dynamically -
this is just an example based on your desired output)
cmd.CommandText = "SELECT ProductID FIELD1, ProductName FIELD2 FROM Products
ROW FOR XML AUTO"
'Specify the root tag
cmd.Properties("xml root") = "ROWSET"
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
'Save the XML
xmlDoc.Save "C:\Results.xml"
The only major issue you'll have is getting your "num" attribute. If the
number relates to a field in the data (e.g. a ProductNo column or similar)
then you'll need to use an EXPLICIT mode query to retrieve it as an
attribute when everything else is an element. If it's not a data column, and
just the number of the row in the result set you'll need to either write a
stored procedure to generate the right values for each row (off the top of
my head, you could retrieve the data into a temp table with an IDENTITY
column and then return the data from that) or you could just retrieve the
data and then add the num attribute to each ROW element in the DOMDocument
before saving.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Denis" <dzoddi@.mvmnet.com> wrote in message
news:ODlXKMIKFHA.580@.TK2MSFTNGP15.phx.gbl...
Hello
I'm using asp to perform a query against an MSSQL database
This query gets data dinamically and should write an xml file with the
resulting recordset
XML should be formatted as follow
I've just tryed FOR XML but I'm not able to get the well formatted xml
So:
1. I need help to get well formatted xml like the following
2. I'd like to know the best way to save the resulting xml to a file (in
asp)
<?xml version = '1.0' encoding='iso-8859-1'?>
<ROWSET>
<ROW num="1">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
<ROW num="2">
<FIELD1>value..</FIELD1>
<FIELD2>value...</FIELD2>
..
<FIELDN>value...</FIELDN>
</ROW>
</ROWSET>
Thanks

Exporting ToExcel file

Hi, I have a SP that adds an Excel file as a linked server, then tries to
send the result of a query into this file.
I get the following error :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
convert(varchar(10),ProductId), ProductName, Convert (varchar(20),UnitPrice)
from Northwind..Products
[OLE/DB provider returned message: Cannot start your application.
The workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I am executing this on my Laptop(winXP SP2), sql 2000 is on my laptop. So
what authentification is failing?
Thanks in advance
Hi,
Could you please post the exact text of the stored
procedure? You can use sp_helptext.
You may also want to test this script to see if there is
any problem:
sp_dropserver 'EXCELSOURCE', 'droplogins'
go
--Replace 'E:\test.xls' appropriately
sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
@.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
'E:\test.xls' , @.provstr = 'Excel 5.0'
go
Insert ExcelSource...[ExcelTable$] ( A,B,C )
select convert(varchar(10),ProductId), ProductName,
Convert (varchar(20),UnitPrice)
from Northwind..Products
go
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUYosS7NtNk3oRyQvKh7cX1Ypnwdw==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>Subject: Exporting ToExcel file
>Date: Mon, 21 Feb 2005 21:53:01 -0800
>Lines: 18
>Message-ID:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26922
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hi, I have a SP that adds an Excel file as a linked
server, then tries to
>send the result of a query into this file.
>I get the following error :
>--
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. Authentication
>failed.
>Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
>convert(varchar(10),ProductId), ProductName, Convert
(varchar(20),UnitPrice)
>from Northwind..Products
>[OLE/DB provider returned message: Cannot start your
application.
>The workgroup information file is missing or opened
exclusively by another
>user.]
>OLE DB error trace [OLE/DB Provider
'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80040e4d:
Authentication failed.].
>--
>I am executing this on my Laptop(winXP SP2), sql 2000
is on my laptop. So
>what authentification is failing?
>Thanks in advance
>
|||Hello, here is the SP :
CREATE proc sp_write2Excel
(
@.fileName varchar(100),
@.NumOfColumns tinyint,
@.query varchar(200)
)
--Obligation : create an empty Excel file with a fixed name and place on the
server
/*
Usage
exec sp_write2Excel
-- Target Excel file
'c:\temp\NorthProducts.xls' ,
-- Number of columns in result
3,
-- The query to be exported
'select convert(varchar(10),ProductId),
ProductName,
Convert (varchar(20),UnitPrice) from Northwind..Products'
*/
AS
Begin
declare @.dosStmt varchar(200)
declare @.tsqlStmt varchar(500)
declare @.colList varchar(200)
declare @.charInd tinyint
set nocount on
-- construct the columnList A,B,C ...
-- until Num Of columns is reached.
set @.charInd=0
set @.colList = 'A'
while @.charInd < @.NumOfColumns - 1
begin
set @.charInd = @.charInd + 1
set @.colList = @.colList + ',' + char(65 + @.charInd)
end
-- Create an Empty Excel file as the target file name by copying the
template Empty excel File
set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' + @.fileName
exec master..xp_cmdshell @.dosStmt
-- Create a "temporary" linked server to that file in order to
"Export" Data
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
-- construct a T-SQL statement that will actually export the query
results
-- to the Table in the target linked server
set @.tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' + ' ( ' +
@.colList + ' ) '+ @.query
print @.tsqlStmt
-- execute dynamically the TSQL statement
exec (@.tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
End
GO
"William Wang[MSFT]" wrote:

> Hi,
> Could you please post the exact text of the stored
> procedure? You can use sp_helptext.
> You may also want to test this script to see if there is
> any problem:
> sp_dropserver 'EXCELSOURCE', 'droplogins'
> go
> --Replace 'E:\test.xls' appropriately
> sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
> @.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
> 'E:\test.xls' , @.provstr = 'Excel 5.0'
> go
> Insert ExcelSource...[ExcelTable$] ( A,B,C )
> select convert(varchar(10),ProductId), ProductName,
> Convert (varchar(20),UnitPrice)
> from Northwind..Products
> go
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> <eliassal@.online.nospam>
> <532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
> phx.gbl!TK2MSFTNGXA03.phx.gbl
> microsoft.public.sqlserver.tools:26922
> server, then tries to
> error. Authentication
> (varchar(20),UnitPrice)
> application.
> exclusively by another
> 'Microsoft.Jet.OLEDB.4.0'
> Authentication failed.].
> is on my laptop. So
>
|||Hi,
Your script looks good and it works correctly on my test
machine. Based on my research, this issue can occur
because the login used to connect to the SQL Server does
not have enough permission. Please add the following
statement to your SP defination (below EXEC
sp_addlinkedserver):
EXEC sp_addlinkedsrvlogin 'ExcelSource',
'false',NULL,'ADMIN',NULL
then drop the existing SP and create a new SP to test
the problem.
Feel free to let me know if this resolves your problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUZDYE2sZAiZVxlSiqUFE3MWVkzdg==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>References:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
<K8U2ePMGFHA.2840@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Exporting ToExcel file
>Date: Tue, 22 Feb 2005 10:37:04 -0800
>Lines: 168
>Message-ID:
<6D98A325-8651-4FD5-AC3E-ADDE2258B7C1@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT FEED01.
phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26924
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello, here is the SP :
>--
>CREATE proc sp_write2Excel
>(
>@.fileName varchar(100),
>@.NumOfColumns tinyint,
>@.query varchar(200)
>)
>--Obligation : create an empty Excel file with a fixed
name and place on the
>server
>/*
>Usage
>exec sp_write2Excel
> -- Target Excel file
> 'c:\temp\NorthProducts.xls' ,
> -- Number of columns in result
> 3,

> -- The query to be exported
> 'select convert(varchar(10),ProductId),
> ProductName,
> Convert (varchar(20),UnitPrice) from
Northwind..Products'
>
>*/
>AS
>Begin
> declare @.dosStmt varchar(200)
> declare @.tsqlStmt varchar(500)
> declare @.colList varchar(200)
> declare @.charInd tinyint
> set nocount on
> -- construct the columnList A,B,C ...
> -- until Num Of columns is reached.
> set @.charInd=0
> set @.colList = 'A'
> while @.charInd < @.NumOfColumns - 1
> begin
> set @.charInd = @.charInd + 1
> set @.colList = @.colList + ',' + char(65 +
@.charInd)
> end
> -- Create an Empty Excel file as the target
file name by copying the
>template Empty excel File
> set @.dosStmt = ' copy E:\Dev\sql\empty.xls ' +
@.fileName
> exec master..xp_cmdshell @.dosStmt
> -- Create a "temporary" linked server to that
file in order to
>"Export" Data
> EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0',
>'Microsoft.Jet.OLEDB.4.0', @.fileName, NULL, 'Excel 5.0'
> -- construct a T-SQL statement that will
actually export the query
>results
> -- to the Table in the target linked server
> set @.tsqlStmt = 'Insert
ExcelSource...[ExcelTable$] ' + ' ( ' +[vbcol=seagreen]
>@.colList + ' ) '+ @.query
> print @.tsqlStmt
> -- execute dynamically the TSQL statement
> exec (@.tsqlStmt)
> -- drop the linked server
> EXEC sp_dropserver 'ExcelSource'
> set nocount off
>End
>GO
>
>"William Wang[MSFT]" wrote:
is[vbcol=seagreen]
,[vbcol=seagreen]
and[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.[vbcol=seagreen]
an[vbcol=seagreen]
2000
>

Exporting to MS Access from Query Analyser

Hi folks
Ive a simple doubt (I hope) ... is it possible export the results from a simple query to a ms access file using just the query analyser? Im using SQL Server 2000 and MS Access XP
thanks for your help!!!Why not use DTS?

Or...why not set Access up as a linked Server...|||Thanks for your answer Brett. Ill put a terminal with query analyser and write the script for user, he will just change some parameters.

I dont want the final user using enterprise manager

I tried to link tables from MS Access and run this query (is more friendly to final users), but exists a big performance diference.|||you probably tried to run it in the jet engine in access

define the query as a "pass through" query, and it will run in sql server

Query > SQL Specific > Pass-Through

the performance will be the same as in sql server because, it's, um, running in sql server

then the results will be brought straight into access

"no fuss, no muss, no DTS"|||Originally posted by r937
you probably tried to run it in the jet engine in access

define the query as a "pass through" query, and it will run in sql server

Query > SQL Specific > Pass-Through

the performance will be the same as in sql server because, it's, um, running in sql server

then the results will be brought straight into access

"no fuss, no muss, no DTS"

I think he's going down to Access...not up to SQL Server...|||"down to access"? as in export the results from a simple query to a ms access file

what is it about an access pass-through query that is incompatible with this?

a pass-through query runs in sql server and returns the results to access

that is what the original poster seemed to be trying to do|||Nothing....Just got all turned around...

Think that s/he needed to push to access...not pull from sql server...

If it needs to be pushed, then what?|||Originally posted by Brett Kaiser
If it needs to be pushed, then what?
then the system architect needs a good talking to, push went out with the nineties (remember Pointcast?)

if i'm a user, i do *not* want stuff inserted into my MDB when i'm not doing it

okay, maybe DTS could push an excel file, and i might decide if i want to import it

but pushing to MDB? is that even possible?|||Yeah, well, I was think Access as a linked server...

And what's replication (not that I use it) if not a push...granted their called subscribers...|||Hi guys

Ill explain my problem: Ive a table with 40 milion lines and the users needs to run some querys in this table (like choose a vendor, or date range, etc).

The most friendly interface for them is MS Access or MS Excel, so I need to get this data from SQL but manipulate in one of these programs.

R937: I tried to use Pass-Through in MS Access query and the results cames faster, but its slow comparing with results in query analyser interface (7 minutes x 1:20 minute).

If I could export the results to MS Access or Excel file from query analyser, Ill working faster.|||the only way reason that i can think of why a pass-through query is taking so much longer than running in sql server, is the time required to transfer the results -- how many rows is the query returning?

there could also be other reasons, like a config option (i'm guessing) in sql server that lets QA or EM queries have plenty of cycles per second, whereas queries submitted in other ways (e.g. odbc) get low priority|||Back with 2200 lines in result.

is it possible set the time required to transfer results?|||no idea

you need a DBA

which i'm not, sorry