Cursors vs. Queries: saved

Showing posts with label saved. Show all posts

Thursday, March 8, 2012

Culture in MSSQL 2005 express edition

i am trying to add xml to the xml data type column. The xml contains both
english and hebrew characters. English characters are saved well, but hebrew
saved as "''?" how can i fix it?Try converting the xml to varbinary(max) before inserting. From BOL:
Text Encoding
SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved from
the server comes out in UTF-16 encoding. If you want a different encoding,
you have to perform the required conversion on the retrieved data.
Sometimes, the XML data may be in a different encoding. If it is, you have
to use care during data loading. For example:
a.. If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to
an XML column, variable, or parameter without any problems.
b.. If the encoding is not Unicode and is implicit, because of the source
code page, the string code page in the database should be the same as or
compatible with the code points that you want to load. If required, use
COLLATE. If no such server code page exists, you have to add an explicit XML
declaration with the correct encoding.
c.. To use an explicit encoding, use either the varbinary() type, which
has no interaction with code pages, or use a string type of the appropriate
code page. Then, assign the data to an XML column, variable, or parameter.
Example: Explicitly Specifying an Encoding
Assume that you have an XML document, vcdoc, stored as varchar(max) that
does not have an explicit XML declaration. The following statement adds an
XML declaration with the encoding "iso8859-1", concatenates the XML
document, casts the result to varbinary(max) so that the byte representation
is preserved, and then finally casts it to XML. This enables the XML
processor to parse the data according to the specified encoding "iso8859-1"
and generate the corresponding UTF-16 representation for string values.
SELECT CAST(
CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY
(MAX))
AS XML)
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <michael@.discussions.microsoft.com> wrote in message
news:19B65A8D-E246-46EB-A85B-C517D1830BAD@.microsoft.com...
> i am trying to add xml to the xml data type column. The xml contains both
> english and hebrew characters. English characters are saved well, but
hebrew
> saved as "''?" how can i fix it?|||Thanks a lot
"David Barber [MS]" wrote:

> Try converting the xml to varbinary(max) before inserting. From BOL:
> Text Encoding
> SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved fr
om
> the server comes out in UTF-16 encoding. If you want a different encoding,
> you have to perform the required conversion on the retrieved data.
> Sometimes, the XML data may be in a different encoding. If it is, you have
> to use care during data loading. For example:
> a.. If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to
> an XML column, variable, or parameter without any problems.
>
> b.. If the encoding is not Unicode and is implicit, because of the sourc
e
> code page, the string code page in the database should be the same as or
> compatible with the code points that you want to load. If required, use
> COLLATE. If no such server code page exists, you have to add an explicit X
ML
> declaration with the correct encoding.
>
> c.. To use an explicit encoding, use either the varbinary() type, which
> has no interaction with code pages, or use a string type of the appropriat
e
> code page. Then, assign the data to an XML column, variable, or parameter.
>
> Example: Explicitly Specifying an Encoding
> Assume that you have an XML document, vcdoc, stored as varchar(max) that
> does not have an explicit XML declaration. The following statement adds an
> XML declaration with the encoding "iso8859-1", concatenates the XML
> document, casts the result to varbinary(max) so that the byte representati
on
> is preserved, and then finally casts it to XML. This enables the XML
> processor to parse the data according to the specified encoding "iso8859-1
"
> and generate the corresponding UTF-16 representation for string values.
>
> SELECT CAST(
> CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY
> (MAX))
> AS XML)
>
> --
> David Barber [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <michael@.discussions.microsoft.com> wrote in message
> news:19B65A8D-E246-46EB-A85B-C517D1830BAD@.microsoft.com...
> hebrew
>
>

Sunday, February 19, 2012

CSV Scheduled Report

Friday, February 17, 2012

CSV Scheduled Report

I need to run a report from my DB to run every night and save the results as
a CSV file. This file will need to be saved to a specified location to be
imported to another vendor DB. I created the query inside of a Stored
Procedure and scheduled the SP to execute using the Service Agent (Jobs) and
save the results to a .CSV file. The file is created but on the top of the
file there's always the time date and step of the job and also the CSV format
is not correct the information needs to be in their own column so that the
vendor DB can import the data to their DB. How can I schedule this job so
that the time, date and Job step does not show on the results file and also
how can i make sure that the format of the CSV file is correct. When I tun
the same query in analyzer it creates all my columns with the headings
correclty.
Thanks in advance for any help.Carlos
Look at jobs in the BOL
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--Run
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Ppass'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> I need to run a report from my DB to run every night and save the results
as
> a CSV file. This file will need to be saved to a specified location to be
> imported to another vendor DB. I created the query inside of a Stored
> Procedure and scheduled the SP to execute using the Service Agent (Jobs)
and
> save the results to a .CSV file. The file is created but on the top of
the
> file there's always the time date and step of the job and also the CSV
format
> is not correct the information needs to be in their own column so that
the
> vendor DB can import the data to their DB. How can I schedule this job so
> that the time, date and Job step does not show on the results file and
also
> how can i make sure that the format of the CSV file is correct. When I
tun
> the same query in analyzer it creates all my columns with the headings
> correclty.
> Thanks in advance for any help.|||I don't want to create another table. I already have the query that I need
to run against my DB and the results need to be saved to a .csv file. My
problem is that the .CSV data is all over the columns and also the top on the
file always shows the time date and the step that took to run the job.
The vendor appliaction is always monitoring a specif directory for the
formated .csv file to import to their app. So i need to find a way so that
when my stored procedure when scheduked to run needs to output the results to
a .csv file with the data in the right columns and without the time, date and
step number of the job.
Thanks
"Uri Dimant" wrote:
> Carlos
> Look at jobs in the BOL
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --Run
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
>
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Ppass'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > I need to run a report from my DB to run every night and save the results
> as
> > a CSV file. This file will need to be saved to a specified location to be
> > imported to another vendor DB. I created the query inside of a Stored
> > Procedure and scheduled the SP to execute using the Service Agent (Jobs)
> and
> > save the results to a .CSV file. The file is created but on the top of
> the
> > file there's always the time date and step of the job and also the CSV
> format
> > is not correct the information needs to be in their own column so that
> the
> > vendor DB can import the data to their DB. How can I schedule this job so
> > that the time, date and Job step does not show on the results file and
> also
> > how can i make sure that the format of the CSV file is correct. When I
> tun
> > the same query in analyzer it creates all my columns with the headings
> > correclty.
> >
> > Thanks in advance for any help.
>
>|||Carlos
You might want to look at xp_execresultset SP. If I remember well OJ has a
great article about such kind of queries.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> I don't want to create another table. I already have the query that I
need
> to run against my DB and the results need to be saved to a .csv file. My
> problem is that the .CSV data is all over the columns and also the top on
the
> file always shows the time date and the step that took to run the job.
> The vendor appliaction is always monitoring a specif directory for the
> formated .csv file to import to their app. So i need to find a way so
that
> when my stored procedure when scheduked to run needs to output the results
to
> a .csv file with the data in the right columns and without the time, date
and
> step number of the job.
> Thanks
> "Uri Dimant" wrote:
> > Carlos
> > Look at jobs in the BOL
> > create table ww
> > (
> > col1 int,
> > col2 varchar(50),
> > col3 varchar (50)
> > )
> >
> > insert into ww values (47,'ReadyShip','(503)888-999')
> > insert into ww values (48,'MyShipper','(503)1212-454')
> > insert into ww values (49,'ReadyShip','(45)888-999')
> > insert into ww values (50,'MyShipper','(545)1212-454')
> > --Run
> > bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
> >
> >
> > exec master..xp_cmdshell 'BCP northwind..ww IN
> > d:\test1.txt -c -C850 -SServer -Usa -Ppass'
> >
> >
> >
> >
> > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
message
> > news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > > I need to run a report from my DB to run every night and save the
results
> > as
> > > a CSV file. This file will need to be saved to a specified location
to be
> > > imported to another vendor DB. I created the query inside of a Stored
> > > Procedure and scheduled the SP to execute using the Service Agent
(Jobs)
> > and
> > > save the results to a .CSV file. The file is created but on the top
of
> > the
> > > file there's always the time date and step of the job and also the CSV
> > format
> > > is not correct the information needs to be in their own column so
that
> > the
> > > vendor DB can import the data to their DB. How can I schedule this
job so
> > > that the time, date and Job step does not show on the results file and
> > also
> > > how can i make sure that the format of the CSV file is correct. When
I
> > tun
> > > the same query in analyzer it creates all my columns with the headings
> > > correclty.
> > >
> > > Thanks in advance for any help.
> >
> >
> >|||Who's OJ? and where can i look for such article on these kind of queries. I
could not get your sample to work.
Thanks again for your help.
"Uri Dimant" wrote:
> Carlos
> You might want to look at xp_execresultset SP. If I remember well OJ has a
> great article about such kind of queries.
>
> EXEC master..xp_execresultset N'SELECT
> ''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
> Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
> "C:\Temp\''+CAST(Employeeid AS
> VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
> FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
> ',N'Northwind'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> > I don't want to create another table. I already have the query that I
> need
> > to run against my DB and the results need to be saved to a .csv file. My
> > problem is that the .CSV data is all over the columns and also the top on
> the
> > file always shows the time date and the step that took to run the job.
> >
> > The vendor appliaction is always monitoring a specif directory for the
> > formated .csv file to import to their app. So i need to find a way so
> that
> > when my stored procedure when scheduked to run needs to output the results
> to
> > a .csv file with the data in the right columns and without the time, date
> and
> > step number of the job.
> >
> > Thanks
> >
> > "Uri Dimant" wrote:
> >
> > > Carlos
> > > Look at jobs in the BOL
> > > create table ww
> > > (
> > > col1 int,
> > > col2 varchar(50),
> > > col3 varchar (50)
> > > )
> > >
> > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > insert into ww values (50,'MyShipper','(545)1212-454')
> > > --Run
> > > bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
> > >
> > >
> > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > d:\test1.txt -c -C850 -SServer -Usa -Ppass'
> > >
> > >
> > >
> > >
> > > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
> message
> > > news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > > > I need to run a report from my DB to run every night and save the
> results
> > > as
> > > > a CSV file. This file will need to be saved to a specified location
> to be
> > > > imported to another vendor DB. I created the query inside of a Stored
> > > > Procedure and scheduled the SP to execute using the Service Agent
> (Jobs)
> > > and
> > > > save the results to a .CSV file. The file is created but on the top
> of
> > > the
> > > > file there's always the time date and step of the job and also the CSV
> > > format
> > > > is not correct the information needs to be in their own column so
> that
> > > the
> > > > vendor DB can import the data to their DB. How can I schedule this
> job so
> > > > that the time, date and Job step does not show on the results file and
> > > also
> > > > how can i make sure that the format of the CSV file is correct. When
> I
> > > tun
> > > > the same query in analyzer it creates all my columns with the headings
> > > > correclty.
> > > >
> > > > Thanks in advance for any help.
> > >
> > >
> > >
>
>|||Carlos
Sorry , try search on internet OJ+sql Server' ,i don't have a time right
now.
I posted yesterday one example about how it could be used.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:5C6A6F57-DE0F-4213-92FF-88C0DC3A61CB@.microsoft.com...
> Who's OJ? and where can i look for such article on these kind of queries.
I
> could not get your sample to work.
> Thanks again for your help.
> "Uri Dimant" wrote:
> > Carlos
> > You might want to look at xp_execresultset SP. If I remember well OJ
has a
> > great article about such kind of queries.
> >
> >
> >
> > EXEC master..xp_execresultset N'SELECT
> > ''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders
WHERE
> > Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
> > "C:\Temp\''+CAST(Employeeid AS
> > VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
> > FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
> > ',N'Northwind'
> >
> >
> >
> >
> > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
message
> > news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> > > I don't want to create another table. I already have the query that I
> > need
> > > to run against my DB and the results need to be saved to a .csv file.
My
> > > problem is that the .CSV data is all over the columns and also the top
on
> > the
> > > file always shows the time date and the step that took to run the job.
> > >
> > > The vendor appliaction is always monitoring a specif directory for the
> > > formated .csv file to import to their app. So i need to find a way so
> > that
> > > when my stored procedure when scheduked to run needs to output the
results
> > to
> > > a .csv file with the data in the right columns and without the time,
date
> > and
> > > step number of the job.
> > >
> > > Thanks
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Carlos
> > > > Look at jobs in the BOL
> > > > create table ww
> > > > (
> > > > col1 int,
> > > > col2 varchar(50),
> > > > col3 varchar (50)
> > > > )
> > > >
> > > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > > insert into ww values (50,'MyShipper','(545)1212-454')
> > > > --Run
> > > > bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
> > > >
> > > >
> > > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > > d:\test1.txt -c -C850 -SServer -Usa -Ppass'
> > > >
> > > >
> > > >
> > > >
> > > > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in
> > message
> > > > news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> > > > > I need to run a report from my DB to run every night and save the
> > results
> > > > as
> > > > > a CSV file. This file will need to be saved to a specified
location
> > to be
> > > > > imported to another vendor DB. I created the query inside of a
Stored
> > > > > Procedure and scheduled the SP to execute using the Service Agent
> > (Jobs)
> > > > and
> > > > > save the results to a .CSV file. The file is created but on the
top
> > of
> > > > the
> > > > > file there's always the time date and step of the job and also the
CSV
> > > > format
> > > > > is not correct the information needs to be in their own column so
> > that
> > > > the
> > > > > vendor DB can import the data to their DB. How can I schedule
this
> > job so
> > > > > that the time, date and Job step does not show on the results file
and
> > > > also
> > > > > how can i make sure that the format of the CSV file is correct.
When
> > I
> > > > tun
> > > > > the same query in analyzer it creates all my columns with the
headings
> > > > > correclty.
> > > > >
> > > > > Thanks in advance for any help.
> > > >
> > > >
> > > >
> >
> >
> >

CSV file saved as an Excel file - error message

I hope someone can help me with this - I started receiving this error message in the past month or so when I open a csv report and save it as an Excel file in a folder I use on my VPN and in My Documents. It does not show up when I save it to my Desk Top.

I have Microsoft Office Student and Teacher and Office XP Professional installed on my notebook. I tried to uninstall Office XP and it would not let me. Something about a "patch could not be opened......"

The error message is as follows:

Header: .NET-BroadcastEventWindow.2.0.0.0.33c0d.0.EXCEL.EXE-Application Error

Excel error message The instruction at 0x0beab865 referenced memory at "0x00000008"

The memory could not be "read".

Click ok to terminate the program.

I hope someone could please help me with this I received 60 - 80 csv files a week and everythime I save on I get this pop up message!

Thank you!

Leslie

This is an SSIS (SQL Server Integration Services) forum. You might have better success in the Office forums: http://www.microsoft.com/office/community/en-us/default.mspx

Thursday, March 8, 2012

Culture in MSSQL 2005 express edition

Sunday, February 19, 2012

CSV Scheduled Report

Friday, February 17, 2012

CSV Scheduled Report

CSV file saved as an Excel file - error message

Cursors vs. Queries

Blog Archive

About Me