Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0',
Query=select * from Person'].
--
Message posted via http://www.sqlmonster.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via SQLMonster.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.
> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer..|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via SQLMonster.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...
> Thanks for the feedback. The setup we have is a DBF and a DBT ...
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase memo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Thursday, March 29, 2012
Converting DBF to SQL Server
Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0
',
Query=select * from Person'].
Message posted via http://www.droptable.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via droptable.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.
> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer
.
Table, I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot locate the requested Xbase mem
o
file.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaR
owset:
:GetRowset returned 0x80004005: ].
Hopefully this sheds some light on my issue?
Thanks again,
Michael
Cindy Winegarden wrote:[vbcol=seagreen]
>Hi Michael,
>First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
>FoxPro OLE DB data provider, downloadable from
>msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
>follows:
>-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC fil
e
>is present in the directory) use only the path to the directory and put
>double quotes around it if it has spaces. Something like '"C:\My
>Directory\"'
>-- If a DBC files is present the @.DataSrc value needs to point to the DBC
>file as below.
>EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_NORTHWIND', -- Your linked server name here
> @.srvproduct=N'Visual FoxPro 9', -- can be anything
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
>9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
>Then, you can query your tables like:
>Select * From YourLinkedServer...YourTable
>-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
>if your data source points to a DBC file just use the table name you want t
o
>select from.
>
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via droptable.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...
> Thanks for the feedback. The setup we have is a DBF and a DBT ...
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase m
emo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0
',
Query=select * from Person'].
Message posted via http://www.droptable.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via droptable.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.
> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer
.
Table, I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot locate the requested Xbase mem
o
file.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaR
owset:
:GetRowset returned 0x80004005: ].
Hopefully this sheds some light on my issue?
Thanks again,
Michael
Cindy Winegarden wrote:[vbcol=seagreen]
>Hi Michael,
>First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
>FoxPro OLE DB data provider, downloadable from
>msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
>follows:
>-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC fil
e
>is present in the directory) use only the path to the directory and put
>double quotes around it if it has spaces. Something like '"C:\My
>Directory\"'
>-- If a DBC files is present the @.DataSrc value needs to point to the DBC
>file as below.
>EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_NORTHWIND', -- Your linked server name here
> @.srvproduct=N'Visual FoxPro 9', -- can be anything
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
>9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
>Then, you can query your tables like:
>Select * From YourLinkedServer...YourTable
>-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
>if your data source points to a DBC file just use the table name you want t
o
>select from.
>
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via droptable.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...
> Thanks for the feedback. The setup we have is a DBF and a DBT ...
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase m
emo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].
Tuesday, March 27, 2012
Converting Crystal Reports 8.5 XML output to RDL....
All--
We have several dozen Crystal (8.5) Reports that we would like to port to SSRS 2005. I believe that I can save these reports as XML data files with an accompanying schema. We are not interested in the data, but would like to be able to preserve the reports' layout from one reporting platform to another. I believe I might be able to write an XSLT transformation to convert the Crystal XML output into RDL, but this is not a trivial task. (We would like to avoid, at all costs, manually redesigning the reports in SSRS, but, if it comes to that, we will have to bite the bullet.) Has such an XSLT transformation already been created, and, if so, would anyone care to share their work?
TIA,
mattyseltz in NY
You could try this service for $25 per report:
http://rpttosql.com/
cheers,
Andrew
Converting Crystal Reports 8.5 XML output to RDL....
All--
We have several dozen Crystal (8.5) Reports that we would like to port to SSRS 2005. I believe that I can save these reports as XML data files with an accompanying schema. We are not interested in the data, but would like to be able to preserve the reports' layout from one reporting platform to another. I believe I might be able to write an XSLT transformation to convert the Crystal XML output into RDL, but this is not a trivial task. (We would like to avoid, at all costs, manually redesigning the reports in SSRS, but, if it comes to that, we will have to bite the bullet.) Has such an XSLT transformation already been created, and, if so, would anyone care to share their work?
TIA,
mattyseltz in NY
You could try this service for $25 per report:
http://rpttosql.com/
cheers,
Andrew
Converting Crystal Reports
Hi,
I am working on converting Crystal Reports to RDL files. I want to convert
my reports to an RDL file, so I can use Reporting Services in MS SQL and
Visual Studio.Net- instead of Crystal. I have two questions:
I have done a lot of research into this and it seems there are paid services
such as Hitachi consulting that will do this and http://www.rpttosql.com.
However, my company is not willing to pay for such services. So what I have
done, is convert the Crystal Report to XML using Crystal 9.
1. Now I want to convert that XML file to an RDL file? How can I do this?
Any direct links or methods will be very helpful.
2. Also, is their an online utility that will do this? Any direct links
will be very helpful.
I have downloaded a program that can convert the Crystal Reports to XML.
However, Crystal 9 can do the same. I am still at the same situation in
question one- I can't convert the XML file to an RDL. Advise is kindly
requested.
Thanks,
RickAn RDL file is an xml file bases on the schema for rdl (which most likely
stands for report definition language, just a guess). To see what it looks
like create a simple report. Then go View, Code. What you see is the XML
file. The schema for it is at the top:
http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition
If you compare the xml from Crystal you will see that they are nothing
alike. Now Crystal has similar concepts so for some things there might be a
one to one correspondence but not for everything. It is a non-trivial task
to convert (hence why Hitachi is charging for it).
Most people doing this have the Crystal report up and then copy and paste
things like text, the query definition etc.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jjrr007" <jjrr007@.discussions.microsoft.com> wrote in message
news:A62BF888-87FF-4E64-98D3-B8E540AB7D52@.microsoft.com...
> Hi,
> I am working on converting Crystal Reports to RDL files. I want to
convert
> my reports to an RDL file, so I can use Reporting Services in MS SQL and
> Visual Studio.Net- instead of Crystal. I have two questions:
> I have done a lot of research into this and it seems there are paid
services
> such as Hitachi consulting that will do this and http://www.rpttosql.com.
> However, my company is not willing to pay for such services. So what I
have
> done, is convert the Crystal Report to XML using Crystal 9.
> 1. Now I want to convert that XML file to an RDL file? How can I do this?
> Any direct links or methods will be very helpful.
> 2. Also, is their an online utility that will do this? Any direct links
> will be very helpful.
> I have downloaded a program that can convert the Crystal Reports to XML.
> However, Crystal 9 can do the same. I am still at the same situation in
> question one- I can't convert the XML file to an RDL. Advise is kindly
> requested.
> Thanks,
> Rick
>|||In addition to Bruces response, I had heard that Hitachi folks made a
conversion tool that their Consultants use to do this quickly...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OYbTXZhDFHA.936@.TK2MSFTNGP12.phx.gbl...
> An RDL file is an xml file bases on the schema for rdl (which most likely
> stands for report definition language, just a guess). To see what it looks
> like create a simple report. Then go View, Code. What you see is the XML
> file. The schema for it is at the top:
> http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition
> If you compare the xml from Crystal you will see that they are nothing
> alike. Now Crystal has similar concepts so for some things there might be
> a
> one to one correspondence but not for everything. It is a non-trivial task
> to convert (hence why Hitachi is charging for it).
> Most people doing this have the Crystal report up and then copy and paste
> things like text, the query definition etc.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jjrr007" <jjrr007@.discussions.microsoft.com> wrote in message
> news:A62BF888-87FF-4E64-98D3-B8E540AB7D52@.microsoft.com...
>> Hi,
>> I am working on converting Crystal Reports to RDL files. I want to
> convert
>> my reports to an RDL file, so I can use Reporting Services in MS SQL and
>> Visual Studio.Net- instead of Crystal. I have two questions:
>> I have done a lot of research into this and it seems there are paid
> services
>> such as Hitachi consulting that will do this and http://www.rpttosql.com.
>> However, my company is not willing to pay for such services. So what I
> have
>> done, is convert the Crystal Report to XML using Crystal 9.
>> 1. Now I want to convert that XML file to an RDL file? How can I do
>> this?
>> Any direct links or methods will be very helpful.
>> 2. Also, is their an online utility that will do this? Any direct links
>> will be very helpful.
>> I have downloaded a program that can convert the Crystal Reports to XML.
>> However, Crystal 9 can do the same. I am still at the same situation in
>> question one- I can't convert the XML file to an RDL. Advise is kindly
>> requested.
>> Thanks,
>> Rick
>|||You can generate your RDL/XML files programmatically w/o XML or VS, using the
RDL reader/writer:
http://www.rdlcomponents.com
Thanks
Jerry
"Wayne Snyder" wrote:
> In addition to Bruces response, I had heard that Hitachi folks made a
> conversion tool that their Consultants use to do this quickly...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OYbTXZhDFHA.936@.TK2MSFTNGP12.phx.gbl...
> > An RDL file is an xml file bases on the schema for rdl (which most likely
> > stands for report definition language, just a guess). To see what it looks
> > like create a simple report. Then go View, Code. What you see is the XML
> > file. The schema for it is at the top:
> > http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition
> >
> > If you compare the xml from Crystal you will see that they are nothing
> > alike. Now Crystal has similar concepts so for some things there might be
> > a
> > one to one correspondence but not for everything. It is a non-trivial task
> > to convert (hence why Hitachi is charging for it).
> >
> > Most people doing this have the Crystal report up and then copy and paste
> > things like text, the query definition etc.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "jjrr007" <jjrr007@.discussions.microsoft.com> wrote in message
> > news:A62BF888-87FF-4E64-98D3-B8E540AB7D52@.microsoft.com...
> >> Hi,
> >>
> >> I am working on converting Crystal Reports to RDL files. I want to
> > convert
> >> my reports to an RDL file, so I can use Reporting Services in MS SQL and
> >> Visual Studio.Net- instead of Crystal. I have two questions:
> >>
> >> I have done a lot of research into this and it seems there are paid
> > services
> >> such as Hitachi consulting that will do this and http://www.rpttosql.com.
> >> However, my company is not willing to pay for such services. So what I
> > have
> >> done, is convert the Crystal Report to XML using Crystal 9.
> >>
> >> 1. Now I want to convert that XML file to an RDL file? How can I do
> >> this?
> >> Any direct links or methods will be very helpful.
> >>
> >> 2. Also, is their an online utility that will do this? Any direct links
> >> will be very helpful.
> >>
> >> I have downloaded a program that can convert the Crystal Reports to XML.
> >> However, Crystal 9 can do the same. I am still at the same situation in
> >> question one- I can't convert the XML file to an RDL. Advise is kindly
> >> requested.
> >>
> >> Thanks,
> >> Rick
> >>
> >
> >
>
>
I am working on converting Crystal Reports to RDL files. I want to convert
my reports to an RDL file, so I can use Reporting Services in MS SQL and
Visual Studio.Net- instead of Crystal. I have two questions:
I have done a lot of research into this and it seems there are paid services
such as Hitachi consulting that will do this and http://www.rpttosql.com.
However, my company is not willing to pay for such services. So what I have
done, is convert the Crystal Report to XML using Crystal 9.
1. Now I want to convert that XML file to an RDL file? How can I do this?
Any direct links or methods will be very helpful.
2. Also, is their an online utility that will do this? Any direct links
will be very helpful.
I have downloaded a program that can convert the Crystal Reports to XML.
However, Crystal 9 can do the same. I am still at the same situation in
question one- I can't convert the XML file to an RDL. Advise is kindly
requested.
Thanks,
RickAn RDL file is an xml file bases on the schema for rdl (which most likely
stands for report definition language, just a guess). To see what it looks
like create a simple report. Then go View, Code. What you see is the XML
file. The schema for it is at the top:
http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition
If you compare the xml from Crystal you will see that they are nothing
alike. Now Crystal has similar concepts so for some things there might be a
one to one correspondence but not for everything. It is a non-trivial task
to convert (hence why Hitachi is charging for it).
Most people doing this have the Crystal report up and then copy and paste
things like text, the query definition etc.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jjrr007" <jjrr007@.discussions.microsoft.com> wrote in message
news:A62BF888-87FF-4E64-98D3-B8E540AB7D52@.microsoft.com...
> Hi,
> I am working on converting Crystal Reports to RDL files. I want to
convert
> my reports to an RDL file, so I can use Reporting Services in MS SQL and
> Visual Studio.Net- instead of Crystal. I have two questions:
> I have done a lot of research into this and it seems there are paid
services
> such as Hitachi consulting that will do this and http://www.rpttosql.com.
> However, my company is not willing to pay for such services. So what I
have
> done, is convert the Crystal Report to XML using Crystal 9.
> 1. Now I want to convert that XML file to an RDL file? How can I do this?
> Any direct links or methods will be very helpful.
> 2. Also, is their an online utility that will do this? Any direct links
> will be very helpful.
> I have downloaded a program that can convert the Crystal Reports to XML.
> However, Crystal 9 can do the same. I am still at the same situation in
> question one- I can't convert the XML file to an RDL. Advise is kindly
> requested.
> Thanks,
> Rick
>|||In addition to Bruces response, I had heard that Hitachi folks made a
conversion tool that their Consultants use to do this quickly...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OYbTXZhDFHA.936@.TK2MSFTNGP12.phx.gbl...
> An RDL file is an xml file bases on the schema for rdl (which most likely
> stands for report definition language, just a guess). To see what it looks
> like create a simple report. Then go View, Code. What you see is the XML
> file. The schema for it is at the top:
> http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition
> If you compare the xml from Crystal you will see that they are nothing
> alike. Now Crystal has similar concepts so for some things there might be
> a
> one to one correspondence but not for everything. It is a non-trivial task
> to convert (hence why Hitachi is charging for it).
> Most people doing this have the Crystal report up and then copy and paste
> things like text, the query definition etc.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jjrr007" <jjrr007@.discussions.microsoft.com> wrote in message
> news:A62BF888-87FF-4E64-98D3-B8E540AB7D52@.microsoft.com...
>> Hi,
>> I am working on converting Crystal Reports to RDL files. I want to
> convert
>> my reports to an RDL file, so I can use Reporting Services in MS SQL and
>> Visual Studio.Net- instead of Crystal. I have two questions:
>> I have done a lot of research into this and it seems there are paid
> services
>> such as Hitachi consulting that will do this and http://www.rpttosql.com.
>> However, my company is not willing to pay for such services. So what I
> have
>> done, is convert the Crystal Report to XML using Crystal 9.
>> 1. Now I want to convert that XML file to an RDL file? How can I do
>> this?
>> Any direct links or methods will be very helpful.
>> 2. Also, is their an online utility that will do this? Any direct links
>> will be very helpful.
>> I have downloaded a program that can convert the Crystal Reports to XML.
>> However, Crystal 9 can do the same. I am still at the same situation in
>> question one- I can't convert the XML file to an RDL. Advise is kindly
>> requested.
>> Thanks,
>> Rick
>|||You can generate your RDL/XML files programmatically w/o XML or VS, using the
RDL reader/writer:
http://www.rdlcomponents.com
Thanks
Jerry
"Wayne Snyder" wrote:
> In addition to Bruces response, I had heard that Hitachi folks made a
> conversion tool that their Consultants use to do this quickly...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OYbTXZhDFHA.936@.TK2MSFTNGP12.phx.gbl...
> > An RDL file is an xml file bases on the schema for rdl (which most likely
> > stands for report definition language, just a guess). To see what it looks
> > like create a simple report. Then go View, Code. What you see is the XML
> > file. The schema for it is at the top:
> > http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition
> >
> > If you compare the xml from Crystal you will see that they are nothing
> > alike. Now Crystal has similar concepts so for some things there might be
> > a
> > one to one correspondence but not for everything. It is a non-trivial task
> > to convert (hence why Hitachi is charging for it).
> >
> > Most people doing this have the Crystal report up and then copy and paste
> > things like text, the query definition etc.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "jjrr007" <jjrr007@.discussions.microsoft.com> wrote in message
> > news:A62BF888-87FF-4E64-98D3-B8E540AB7D52@.microsoft.com...
> >> Hi,
> >>
> >> I am working on converting Crystal Reports to RDL files. I want to
> > convert
> >> my reports to an RDL file, so I can use Reporting Services in MS SQL and
> >> Visual Studio.Net- instead of Crystal. I have two questions:
> >>
> >> I have done a lot of research into this and it seems there are paid
> > services
> >> such as Hitachi consulting that will do this and http://www.rpttosql.com.
> >> However, my company is not willing to pay for such services. So what I
> > have
> >> done, is convert the Crystal Report to XML using Crystal 9.
> >>
> >> 1. Now I want to convert that XML file to an RDL file? How can I do
> >> this?
> >> Any direct links or methods will be very helpful.
> >>
> >> 2. Also, is their an online utility that will do this? Any direct links
> >> will be very helpful.
> >>
> >> I have downloaded a program that can convert the Crystal Reports to XML.
> >> However, Crystal 9 can do the same. I am still at the same situation in
> >> question one- I can't convert the XML file to an RDL. Advise is kindly
> >> requested.
> >>
> >> Thanks,
> >> Rick
> >>
> >
> >
>
>
Sunday, March 25, 2012
Converting Access database to SQL Server w/ flat files
I recently received a large Access database that I needed to do a lot
of analysis on. Since I prefer working with SQL Server (to put things
mildly) and I dislike writing VB/VBA code, I thought I'd share my
solution. I know someone will find this useful when searching the
archives.
There are probably much more efficient ways of doing this, but this
solution works well enough for me. There are three functions here that
simplify converting an Access schema to SQL Server and loading the data
into that schema. The first function generates all of the CREATE TABLE
statements for SQL Server. There may be some data types off the list,
since I only used the ones I needed for this conversion. Also, there
are no data contraints or indexes defined. The second dumps all the
data in all the tables to pipe-delimitted flat files and the third
generates all the BULK INSERT statements to load the data into SQL
Server. While I could've left the table names as they were and used
brackets or quotes, I hate writing them in my SQL, so I decided to
replace bad characters with underscores. I hope I've saved someone some
time here.
-Alan
'By Alan Samet 3/8/2006
'Use this code at your own risk. I claim no liability. If it works for
you and you share it
'please give me credit. If it messes things up, take the credit
yourself.
'
'There are three functions you need to use:
' GenerateSqlServerSchema -- creates the schema for SQL Server
' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
' GenerateBulkInsertStatements -- generates the BULK INSERT
statements
'
'All paths need to be fully-qualified. Where a directory is an
argument,
'don't append the ending backslash (\)
Public Function GetSqlServerDataType(accessDataType As Integer, size As
Integer) As String
Select Case accessDataType
Case dbInteger: '3
GetSqlServerDataType = "INT"
Case dbLong: '4
GetSqlServerDataType = "BIGINT"
Case dbSingle, dbDouble: '6, 7
GetSqlServerDataType = "FLOAT"
Case dbCurrency: '5
GetSqlServerDataType = "MONEY"
Case dbDate: '8
GetSqlServerDataType = "DATETIME"
Case dbText: '10
GetSqlServerDataType = "VARCHAR(" & size & ")"
Case Else
Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
End Select
End Function
Function CleanName(name As String)
'Dim c As New Collection
'c.Add "_", "-"
'c.Add "_", " "
'c.Add "", "(R)"
'c.Add "_", "/"
'Dim s As Variant
CleanName = name
'For Each s In c
' CleanName = Replace(CleanName, s, c(s))
'Next
CleanName = Replace(CleanName, "-", "_")
CleanName = Replace(CleanName, " ", "_")
CleanName = Replace(CleanName, "(R)", "")
CleanName = Replace(CleanName, "/", "_")
CleanName = Replace(CleanName, "_&", "_And")
CleanName = Replace(CleanName, "1st", "First")
CleanName = Replace(CleanName, "2nd", "Second")
CleanName = Replace(CleanName, "3rd", "Third")
CleanName = Replace(CleanName, "4th", "Fourth")
End Function
Public Function GenerateSqlServerSchema(databasePath As String) As
String
Dim db As Database
Set db = OpenDatabase(databasePath)
'Dim types(50) As Boolean
Dim t As TableDef
Dim c As Field
Dim createTableSql As String
Dim fullScript As String
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
'Debug.Print t.Name
createTableSql = "CREATE TABLE " & CleanName(t.name) &
vbCrLf & "(" & vbCrLf
Dim bIsFirst As Boolean
bIsFirst = True
For Each c In t.Fields
If Not bIsFirst Then createTableSql = createTableSql &
","
bIsFirst = False
createTableSql = createTableSql & vbTab &
CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
vbCrLf
'types(c.Type) = True
Next
createTableSql = createTableSql & ")"
fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
& vbCrLf & vbCrLf & "GO"
End If
Next
GenerateSqlServerSchema = fullScript
' Dim i As Integer
' For i = 0 To UBound(types)
' If types(i) Then Debug.Print i
' Next
End Function
Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
String)
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
Dim i As Integer
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
Output As #1
Dim values() As String
ReDim values(t.Fields.Count - 1) As String
With t.OpenRecordset(dbOpenForwardOnly)
While Not .EOF
For i = 0 To .Fields.Count - 1
values(i) = Nz(.Fields(i).Value, "")
Next
Print #1, Join(values, "|")
.MoveNext
Wend
End With
Close #1
End If
Next
db.Close
End Sub
Public Function GenerateBulkInsertStatements(databasePat
h As String,
rootExportPath As String) As String
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
GenerateBulkInsertStatements = GenerateBulkInsertStatements
& _
"RAISERROR('Loading " & CleanName(t.name) & "', 10,
10)" & vbCrLf & _
"BULK INSERT " & CleanName(t.name) & " FROM '" & _
rootExportPath & "\" & CleanName(t.name) & _
".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
& vbCrLf
End If
Next
db.Close
End FunctionIs there a reason you didn't just use Data Transformation Services?
You could just create the package and if necessary even run
the package from your vbscript.
Why go through the trouble of doing all this manually?
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141871152.130312.288310@.i39g2000cwa.googlegroups.com...
>I recently received a large Access database that I needed to do a lot
> of analysis on. Since I prefer working with SQL Server (to put things
> mildly) and I dislike writing VB/VBA code, I thought I'd share my
> solution. I know someone will find this useful when searching the
> archives.
> There are probably much more efficient ways of doing this, but this
> solution works well enough for me. There are three functions here that
> simplify converting an Access schema to SQL Server and loading the data
> into that schema. The first function generates all of the CREATE TABLE
> statements for SQL Server. There may be some data types off the list,
> since I only used the ones I needed for this conversion. Also, there
> are no data contraints or indexes defined. The second dumps all the
> data in all the tables to pipe-delimitted flat files and the third
> generates all the BULK INSERT statements to load the data into SQL
> Server. While I could've left the table names as they were and used
> brackets or quotes, I hate writing them in my SQL, so I decided to
> replace bad characters with underscores. I hope I've saved someone some
> time here.
> -Alan
> 'By Alan Samet 3/8/2006
> 'Use this code at your own risk. I claim no liability. If it works for
> you and you share it
> 'please give me credit. If it messes things up, take the credit
> yourself.
> '
> 'There are three functions you need to use:
> ' GenerateSqlServerSchema -- creates the schema for SQL Server
> ' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
> ' GenerateBulkInsertStatements -- generates the BULK INSERT
> statements
> '
> 'All paths need to be fully-qualified. Where a directory is an
> argument,
> 'don't append the ending backslash (\)
> Public Function GetSqlServerDataType(accessDataType As Integer, size As
> Integer) As String
> Select Case accessDataType
> Case dbInteger: '3
> GetSqlServerDataType = "INT"
> Case dbLong: '4
> GetSqlServerDataType = "BIGINT"
> Case dbSingle, dbDouble: '6, 7
> GetSqlServerDataType = "FLOAT"
> Case dbCurrency: '5
> GetSqlServerDataType = "MONEY"
> Case dbDate: '8
> GetSqlServerDataType = "DATETIME"
> Case dbText: '10
> GetSqlServerDataType = "VARCHAR(" & size & ")"
> Case Else
> Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
> End Select
> End Function
> Function CleanName(name As String)
> 'Dim c As New Collection
> 'c.Add "_", "-"
> 'c.Add "_", " "
> 'c.Add "", "(R)"
> 'c.Add "_", "/"
> 'Dim s As Variant
> CleanName = name
> 'For Each s In c
> ' CleanName = Replace(CleanName, s, c(s))
> 'Next
> CleanName = Replace(CleanName, "-", "_")
> CleanName = Replace(CleanName, " ", "_")
> CleanName = Replace(CleanName, "(R)", "")
> CleanName = Replace(CleanName, "/", "_")
> CleanName = Replace(CleanName, "_&", "_And")
> CleanName = Replace(CleanName, "1st", "First")
> CleanName = Replace(CleanName, "2nd", "Second")
> CleanName = Replace(CleanName, "3rd", "Third")
> CleanName = Replace(CleanName, "4th", "Fourth")
> End Function
> Public Function GenerateSqlServerSchema(databasePath As String) As
> String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> 'Dim types(50) As Boolean
> Dim t As TableDef
> Dim c As Field
> Dim createTableSql As String
> Dim fullScript As String
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> 'Debug.Print t.Name
> createTableSql = "CREATE TABLE " & CleanName(t.name) &
> vbCrLf & "(" & vbCrLf
> Dim bIsFirst As Boolean
> bIsFirst = True
> For Each c In t.Fields
> If Not bIsFirst Then createTableSql = createTableSql &
> ","
> bIsFirst = False
> createTableSql = createTableSql & vbTab &
> CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
> vbCrLf
> 'types(c.Type) = True
> Next
> createTableSql = createTableSql & ")"
> fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
> & vbCrLf & vbCrLf & "GO"
> End If
> Next
> GenerateSqlServerSchema = fullScript
> ' Dim i As Integer
> ' For i = 0 To UBound(types)
> ' If types(i) Then Debug.Print i
> ' Next
> End Function
> Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
> String)
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> Dim i As Integer
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
> Output As #1
> Dim values() As String
> ReDim values(t.Fields.Count - 1) As String
> With t.OpenRecordset(dbOpenForwardOnly)
> While Not .EOF
> For i = 0 To .Fields.Count - 1
> values(i) = Nz(.Fields(i).Value, "")
> Next
> Print #1, Join(values, "|")
> .MoveNext
> Wend
> End With
> Close #1
> End If
> Next
> db.Close
> End Sub
> Public Function GenerateBulkInsertStatements(databasePat
h As String,
> rootExportPath As String) As String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> GenerateBulkInsertStatements = GenerateBulkInsertStatements
> & _
> "RAISERROR('Loading " & CleanName(t.name) & "', 10,
> 10)" & vbCrLf & _
> "BULK INSERT " & CleanName(t.name) & " FROM '" & _
> rootExportPath & "\" & CleanName(t.name) & _
> ".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
> & vbCrLf
> End If
> Next
> db.Close
> End Function
>|||Well, for one, I've never had any use for DTS. From my understanding,
you have to use that cumbersome GUI to make DTS packages. With the
exception of well-done tools like Interface Builder on OSX, I don't
like using a GUI to develop my applications -- especially when I can't
work with code. I've been able to do anything that DTS can do using SQL
Server Agent and script (preferably Python). The code that I just
posted only took a couple hours to write, so it really wasn't that much
work, aside from the unpleasantness of working with Access and VBA.
Also, in this situation, I don't want my SQL Server touching this
Access database. Since there are a lot of objects that I haven't looked
at in this database, I was running it on a virtual machine, pulling the
data out and then loading the data onto my development machine. This
method totally isolates the two.
-Alan|||No offense, but I have to agree with Robbe - DTS may have been a better
choice. The GUI tools reduce the time it takes to build stuff and
encapsulates all sorts of error checking etc. While it took a couple
of hours for you to write this manually, someone familiar with DTS
would probably accomplish the same result as the code posted in about 5
to 10 minutes. (And with fewer debugging steps - unless like most of
us you write perfect code all the time. LOL!)|||The Access Upsizing Wizard is also a real easy to use tool. The last
time I used it was probably 6 or 8 yrs ago... surely it is even better
now though. Some documentation here:
http://support.microsoft.com/defaul...B;EN-US;Q325017|||I do write perfect code all the time. It's why I dislike being forced
to use Microsoft's development tools so much.
-Alan
of analysis on. Since I prefer working with SQL Server (to put things
mildly) and I dislike writing VB/VBA code, I thought I'd share my
solution. I know someone will find this useful when searching the
archives.
There are probably much more efficient ways of doing this, but this
solution works well enough for me. There are three functions here that
simplify converting an Access schema to SQL Server and loading the data
into that schema. The first function generates all of the CREATE TABLE
statements for SQL Server. There may be some data types off the list,
since I only used the ones I needed for this conversion. Also, there
are no data contraints or indexes defined. The second dumps all the
data in all the tables to pipe-delimitted flat files and the third
generates all the BULK INSERT statements to load the data into SQL
Server. While I could've left the table names as they were and used
brackets or quotes, I hate writing them in my SQL, so I decided to
replace bad characters with underscores. I hope I've saved someone some
time here.
-Alan
'By Alan Samet 3/8/2006
'Use this code at your own risk. I claim no liability. If it works for
you and you share it
'please give me credit. If it messes things up, take the credit
yourself.
'
'There are three functions you need to use:
' GenerateSqlServerSchema -- creates the schema for SQL Server
' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
' GenerateBulkInsertStatements -- generates the BULK INSERT
statements
'
'All paths need to be fully-qualified. Where a directory is an
argument,
'don't append the ending backslash (\)
Public Function GetSqlServerDataType(accessDataType As Integer, size As
Integer) As String
Select Case accessDataType
Case dbInteger: '3
GetSqlServerDataType = "INT"
Case dbLong: '4
GetSqlServerDataType = "BIGINT"
Case dbSingle, dbDouble: '6, 7
GetSqlServerDataType = "FLOAT"
Case dbCurrency: '5
GetSqlServerDataType = "MONEY"
Case dbDate: '8
GetSqlServerDataType = "DATETIME"
Case dbText: '10
GetSqlServerDataType = "VARCHAR(" & size & ")"
Case Else
Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
End Select
End Function
Function CleanName(name As String)
'Dim c As New Collection
'c.Add "_", "-"
'c.Add "_", " "
'c.Add "", "(R)"
'c.Add "_", "/"
'Dim s As Variant
CleanName = name
'For Each s In c
' CleanName = Replace(CleanName, s, c(s))
'Next
CleanName = Replace(CleanName, "-", "_")
CleanName = Replace(CleanName, " ", "_")
CleanName = Replace(CleanName, "(R)", "")
CleanName = Replace(CleanName, "/", "_")
CleanName = Replace(CleanName, "_&", "_And")
CleanName = Replace(CleanName, "1st", "First")
CleanName = Replace(CleanName, "2nd", "Second")
CleanName = Replace(CleanName, "3rd", "Third")
CleanName = Replace(CleanName, "4th", "Fourth")
End Function
Public Function GenerateSqlServerSchema(databasePath As String) As
String
Dim db As Database
Set db = OpenDatabase(databasePath)
'Dim types(50) As Boolean
Dim t As TableDef
Dim c As Field
Dim createTableSql As String
Dim fullScript As String
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
'Debug.Print t.Name
createTableSql = "CREATE TABLE " & CleanName(t.name) &
vbCrLf & "(" & vbCrLf
Dim bIsFirst As Boolean
bIsFirst = True
For Each c In t.Fields
If Not bIsFirst Then createTableSql = createTableSql &
","
bIsFirst = False
createTableSql = createTableSql & vbTab &
CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
vbCrLf
'types(c.Type) = True
Next
createTableSql = createTableSql & ")"
fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
& vbCrLf & vbCrLf & "GO"
End If
Next
GenerateSqlServerSchema = fullScript
' Dim i As Integer
' For i = 0 To UBound(types)
' If types(i) Then Debug.Print i
' Next
End Function
Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
String)
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
Dim i As Integer
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
Output As #1
Dim values() As String
ReDim values(t.Fields.Count - 1) As String
With t.OpenRecordset(dbOpenForwardOnly)
While Not .EOF
For i = 0 To .Fields.Count - 1
values(i) = Nz(.Fields(i).Value, "")
Next
Print #1, Join(values, "|")
.MoveNext
Wend
End With
Close #1
End If
Next
db.Close
End Sub
Public Function GenerateBulkInsertStatements(databasePat
h As String,
rootExportPath As String) As String
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
GenerateBulkInsertStatements = GenerateBulkInsertStatements
& _
"RAISERROR('Loading " & CleanName(t.name) & "', 10,
10)" & vbCrLf & _
"BULK INSERT " & CleanName(t.name) & " FROM '" & _
rootExportPath & "\" & CleanName(t.name) & _
".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
& vbCrLf
End If
Next
db.Close
End FunctionIs there a reason you didn't just use Data Transformation Services?
You could just create the package and if necessary even run
the package from your vbscript.
Why go through the trouble of doing all this manually?
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141871152.130312.288310@.i39g2000cwa.googlegroups.com...
>I recently received a large Access database that I needed to do a lot
> of analysis on. Since I prefer working with SQL Server (to put things
> mildly) and I dislike writing VB/VBA code, I thought I'd share my
> solution. I know someone will find this useful when searching the
> archives.
> There are probably much more efficient ways of doing this, but this
> solution works well enough for me. There are three functions here that
> simplify converting an Access schema to SQL Server and loading the data
> into that schema. The first function generates all of the CREATE TABLE
> statements for SQL Server. There may be some data types off the list,
> since I only used the ones I needed for this conversion. Also, there
> are no data contraints or indexes defined. The second dumps all the
> data in all the tables to pipe-delimitted flat files and the third
> generates all the BULK INSERT statements to load the data into SQL
> Server. While I could've left the table names as they were and used
> brackets or quotes, I hate writing them in my SQL, so I decided to
> replace bad characters with underscores. I hope I've saved someone some
> time here.
> -Alan
> 'By Alan Samet 3/8/2006
> 'Use this code at your own risk. I claim no liability. If it works for
> you and you share it
> 'please give me credit. If it messes things up, take the credit
> yourself.
> '
> 'There are three functions you need to use:
> ' GenerateSqlServerSchema -- creates the schema for SQL Server
> ' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
> ' GenerateBulkInsertStatements -- generates the BULK INSERT
> statements
> '
> 'All paths need to be fully-qualified. Where a directory is an
> argument,
> 'don't append the ending backslash (\)
> Public Function GetSqlServerDataType(accessDataType As Integer, size As
> Integer) As String
> Select Case accessDataType
> Case dbInteger: '3
> GetSqlServerDataType = "INT"
> Case dbLong: '4
> GetSqlServerDataType = "BIGINT"
> Case dbSingle, dbDouble: '6, 7
> GetSqlServerDataType = "FLOAT"
> Case dbCurrency: '5
> GetSqlServerDataType = "MONEY"
> Case dbDate: '8
> GetSqlServerDataType = "DATETIME"
> Case dbText: '10
> GetSqlServerDataType = "VARCHAR(" & size & ")"
> Case Else
> Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
> End Select
> End Function
> Function CleanName(name As String)
> 'Dim c As New Collection
> 'c.Add "_", "-"
> 'c.Add "_", " "
> 'c.Add "", "(R)"
> 'c.Add "_", "/"
> 'Dim s As Variant
> CleanName = name
> 'For Each s In c
> ' CleanName = Replace(CleanName, s, c(s))
> 'Next
> CleanName = Replace(CleanName, "-", "_")
> CleanName = Replace(CleanName, " ", "_")
> CleanName = Replace(CleanName, "(R)", "")
> CleanName = Replace(CleanName, "/", "_")
> CleanName = Replace(CleanName, "_&", "_And")
> CleanName = Replace(CleanName, "1st", "First")
> CleanName = Replace(CleanName, "2nd", "Second")
> CleanName = Replace(CleanName, "3rd", "Third")
> CleanName = Replace(CleanName, "4th", "Fourth")
> End Function
> Public Function GenerateSqlServerSchema(databasePath As String) As
> String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> 'Dim types(50) As Boolean
> Dim t As TableDef
> Dim c As Field
> Dim createTableSql As String
> Dim fullScript As String
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> 'Debug.Print t.Name
> createTableSql = "CREATE TABLE " & CleanName(t.name) &
> vbCrLf & "(" & vbCrLf
> Dim bIsFirst As Boolean
> bIsFirst = True
> For Each c In t.Fields
> If Not bIsFirst Then createTableSql = createTableSql &
> ","
> bIsFirst = False
> createTableSql = createTableSql & vbTab &
> CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
> vbCrLf
> 'types(c.Type) = True
> Next
> createTableSql = createTableSql & ")"
> fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
> & vbCrLf & vbCrLf & "GO"
> End If
> Next
> GenerateSqlServerSchema = fullScript
> ' Dim i As Integer
> ' For i = 0 To UBound(types)
> ' If types(i) Then Debug.Print i
> ' Next
> End Function
> Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
> String)
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> Dim i As Integer
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
> Output As #1
> Dim values() As String
> ReDim values(t.Fields.Count - 1) As String
> With t.OpenRecordset(dbOpenForwardOnly)
> While Not .EOF
> For i = 0 To .Fields.Count - 1
> values(i) = Nz(.Fields(i).Value, "")
> Next
> Print #1, Join(values, "|")
> .MoveNext
> Wend
> End With
> Close #1
> End If
> Next
> db.Close
> End Sub
> Public Function GenerateBulkInsertStatements(databasePat
h As String,
> rootExportPath As String) As String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> GenerateBulkInsertStatements = GenerateBulkInsertStatements
> & _
> "RAISERROR('Loading " & CleanName(t.name) & "', 10,
> 10)" & vbCrLf & _
> "BULK INSERT " & CleanName(t.name) & " FROM '" & _
> rootExportPath & "\" & CleanName(t.name) & _
> ".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
> & vbCrLf
> End If
> Next
> db.Close
> End Function
>|||Well, for one, I've never had any use for DTS. From my understanding,
you have to use that cumbersome GUI to make DTS packages. With the
exception of well-done tools like Interface Builder on OSX, I don't
like using a GUI to develop my applications -- especially when I can't
work with code. I've been able to do anything that DTS can do using SQL
Server Agent and script (preferably Python). The code that I just
posted only took a couple hours to write, so it really wasn't that much
work, aside from the unpleasantness of working with Access and VBA.
Also, in this situation, I don't want my SQL Server touching this
Access database. Since there are a lot of objects that I haven't looked
at in this database, I was running it on a virtual machine, pulling the
data out and then loading the data onto my development machine. This
method totally isolates the two.
-Alan|||No offense, but I have to agree with Robbe - DTS may have been a better
choice. The GUI tools reduce the time it takes to build stuff and
encapsulates all sorts of error checking etc. While it took a couple
of hours for you to write this manually, someone familiar with DTS
would probably accomplish the same result as the code posted in about 5
to 10 minutes. (And with fewer debugging steps - unless like most of
us you write perfect code all the time. LOL!)|||The Access Upsizing Wizard is also a real easy to use tool. The last
time I used it was probably 6 or 8 yrs ago... surely it is even better
now though. Some documentation here:
http://support.microsoft.com/defaul...B;EN-US;Q325017|||I do write perfect code all the time. It's why I dislike being forced
to use Microsoft's development tools so much.
-Alan
Thursday, March 22, 2012
Converting 2000 rdl files to 2005
Hi, how do I go about converting 2000 rdl files to 2005 format?
I have searched on the web and people recommend opening up the
existing rdl files in report designer or create a new microsoft report
project from visual studio 2005.
My problem is that when I open up the rdl files in 2005 I just have
the XML contents and no designer support. Also, I looked to create a
new report project from the File-->New-->Project prompt in VS2005 but
I do not have a Report Project available to me...
I'm probably missing something basic here so any help appreciated :)
Thanks
MarkusThe difference in the rdl files are minimal. Change the 2003 namespace to:
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
and there are few other properties that can be added or changed once you
figure out the missing VS2005 components.
Those properties or elements are: InteractiveWidth like:
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
and InteractiveHeight like:
<Width>10.125in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
The converse is true when you design a report in VS2005 and need to go back
to VS2003 for SQL Server 2000. I always just comment out InteractiveHeigth
and InteractiveWidth and change the "2005/01" to "2003/10".
Rob.
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||I think you have not installed the developper tools of SQL Server 2005
install the client tools from the SQL Server CD and check the options to
insure you install the client components for RS.
after this, Visual Studio will convert the reports automatically.
but an RS2000 report can be executed and rendered by reporting services
without any change.
<MarkusJNZ@.gmail.com> wrote in message
news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||On Apr 30, 1:41 pm, "Jeje" <willg...@.hotmail.com> wrote:
> I think you have not installed the developper tools of SQL Server 2005
> install the client tools from the SQL Server CD and check the options to
> insure you install the client components for RS.
> after this, Visual Studio will convert the reports automatically.
> but an RS2000 report can be executed and rendered by reporting services
> without any change.
> <Markus...@.gmail.com> wrote in message
> news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
>
> > Hi, how do I go about converting 2000 rdl files to 2005 format?
> > I have searched on the web and people recommend opening up the
> > existing rdl files in report designer or create a new microsoft report
> > project from visual studio 2005.
> > My problem is that when I open up the rdl files in 2005 I just have
> > the XML contents and no designer support. Also, I looked to create a
> > new report project from the File-->New-->Project prompt in VS2005 but
> > I do not have a Report Project available to me...
> > I'm probably missing something basic here so any help appreciated :)
> > Thanks
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help; I went to install the client tools and I was
told that I already had them installed and that the install would not
go ahead as I was not changing anything on my local machine :-/
Any ideas welcome
Thanks
Markus|||Hi, I tried to do the install again and this was the message I
received
"Build version check:
Your upgrade is blocked because the existing component is a later
version than the version you are trying to upgrade to. To modify an
existing SQL Server component, go to Add or Remove Programs in Control
Panel, select the component you would like to modify, and then click
Change/Remove."
Thanks again
Markus|||Hi, is your rdl's files, created using report builder 2000? I get a doubt
that the designer doesn't shows means, just some simple reports are created
using RB and saved as reports. Pl confirm.
Amarnath
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||sorry, first time I see this message.
you have to check which applications are already installed, maybe you can
just change the installed components of an existing SQL 2005 installation.
else try to uninstall all your components and try again.
<MarkusJNZ@.gmail.com> wrote in message
news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
> Hi, I tried to do the install again and this was the message I
> received
> "Build version check:
> Your upgrade is blocked because the existing component is a later
> version than the version you are trying to upgrade to. To modify an
> existing SQL Server component, go to Add or Remove Programs in Control
> Panel, select the component you would like to modify, and then click
> Change/Remove."
> Thanks again
> Markus
>|||On Apr 30, 9:19 pm, "Jeje" <willg...@.hotmail.com> wrote:
> sorry, first time I see this message.
> you have to check which applications are already installed, maybe you can
> just change the installed components of an existing SQL 2005 installation.
> else try to uninstall all your components and try again.
> <Markus...@.gmail.com> wrote in message
> news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
>
> > Hi, I tried to do the install again and this was the message I
> > received
> > "Build version check:
> > Your upgrade is blocked because the existing component is a later
> > version than the version you are trying to upgrade to. To modify an
> > existing SQL Server component, go to Add or Remove Programs in Control
> > Panel, select the component you would like to modify, and then click
> > Change/Remove."
> > Thanks again
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help everyone. It looks like I will have to
uninstall and then reintall MSSQL 2005 on my local machine :/
Thanks again
Markussqlsql
I have searched on the web and people recommend opening up the
existing rdl files in report designer or create a new microsoft report
project from visual studio 2005.
My problem is that when I open up the rdl files in 2005 I just have
the XML contents and no designer support. Also, I looked to create a
new report project from the File-->New-->Project prompt in VS2005 but
I do not have a Report Project available to me...
I'm probably missing something basic here so any help appreciated :)
Thanks
MarkusThe difference in the rdl files are minimal. Change the 2003 namespace to:
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
and there are few other properties that can be added or changed once you
figure out the missing VS2005 components.
Those properties or elements are: InteractiveWidth like:
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
and InteractiveHeight like:
<Width>10.125in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
The converse is true when you design a report in VS2005 and need to go back
to VS2003 for SQL Server 2000. I always just comment out InteractiveHeigth
and InteractiveWidth and change the "2005/01" to "2003/10".
Rob.
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||I think you have not installed the developper tools of SQL Server 2005
install the client tools from the SQL Server CD and check the options to
insure you install the client components for RS.
after this, Visual Studio will convert the reports automatically.
but an RS2000 report can be executed and rendered by reporting services
without any change.
<MarkusJNZ@.gmail.com> wrote in message
news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||On Apr 30, 1:41 pm, "Jeje" <willg...@.hotmail.com> wrote:
> I think you have not installed the developper tools of SQL Server 2005
> install the client tools from the SQL Server CD and check the options to
> insure you install the client components for RS.
> after this, Visual Studio will convert the reports automatically.
> but an RS2000 report can be executed and rendered by reporting services
> without any change.
> <Markus...@.gmail.com> wrote in message
> news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
>
> > Hi, how do I go about converting 2000 rdl files to 2005 format?
> > I have searched on the web and people recommend opening up the
> > existing rdl files in report designer or create a new microsoft report
> > project from visual studio 2005.
> > My problem is that when I open up the rdl files in 2005 I just have
> > the XML contents and no designer support. Also, I looked to create a
> > new report project from the File-->New-->Project prompt in VS2005 but
> > I do not have a Report Project available to me...
> > I'm probably missing something basic here so any help appreciated :)
> > Thanks
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help; I went to install the client tools and I was
told that I already had them installed and that the install would not
go ahead as I was not changing anything on my local machine :-/
Any ideas welcome
Thanks
Markus|||Hi, I tried to do the install again and this was the message I
received
"Build version check:
Your upgrade is blocked because the existing component is a later
version than the version you are trying to upgrade to. To modify an
existing SQL Server component, go to Add or Remove Programs in Control
Panel, select the component you would like to modify, and then click
Change/Remove."
Thanks again
Markus|||Hi, is your rdl's files, created using report builder 2000? I get a doubt
that the designer doesn't shows means, just some simple reports are created
using RB and saved as reports. Pl confirm.
Amarnath
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||sorry, first time I see this message.
you have to check which applications are already installed, maybe you can
just change the installed components of an existing SQL 2005 installation.
else try to uninstall all your components and try again.
<MarkusJNZ@.gmail.com> wrote in message
news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
> Hi, I tried to do the install again and this was the message I
> received
> "Build version check:
> Your upgrade is blocked because the existing component is a later
> version than the version you are trying to upgrade to. To modify an
> existing SQL Server component, go to Add or Remove Programs in Control
> Panel, select the component you would like to modify, and then click
> Change/Remove."
> Thanks again
> Markus
>|||On Apr 30, 9:19 pm, "Jeje" <willg...@.hotmail.com> wrote:
> sorry, first time I see this message.
> you have to check which applications are already installed, maybe you can
> just change the installed components of an existing SQL 2005 installation.
> else try to uninstall all your components and try again.
> <Markus...@.gmail.com> wrote in message
> news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
>
> > Hi, I tried to do the install again and this was the message I
> > received
> > "Build version check:
> > Your upgrade is blocked because the existing component is a later
> > version than the version you are trying to upgrade to. To modify an
> > existing SQL Server component, go to Add or Remove Programs in Control
> > Panel, select the component you would like to modify, and then click
> > Change/Remove."
> > Thanks again
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help everyone. It looks like I will have to
uninstall and then reintall MSSQL 2005 on my local machine :/
Thanks again
Markussqlsql
converting .dbf files to sql server 2000
Hi..
I want to convert .dbf files to sql server 2000 tables.. without using any tools. I need to create a different structure for sql server tables other than contains in the .dbf files. May be the dbf files contain only 3 columns. but i need 5 columns and some calculations to determine the values of some fields to insert into sql server table...
i need to code this using c# in asp.net.. can u help me?
thanks in advance..
Fraijo
A .dbf file is Character and Number data types but the chart below is all the different types yo need to convert that to so I don't see how you can do it without ETL(extraction transformation and loading) tool. So create a DTS package to move your data. Hope this helps.
.NET Framework Type
ADO.NET Database Type
SQL Data Type
String
Varchar
Varchar()
String
Nvarchar
Nvarchar()
String
NChar
Nchar()
String
NText
NText
String
Text
Text
Double
BigInt
Float
DateTime
DateTime
Datetime
DateTime
SmallDateTime
Smalldatetime
Int
Int
Int
Int64
BigInt
Bigint
Int16
SmallInt
smallint
Byte[]
Binary
Binary()
Byte[]
Image
Image
Byte[]
VarBinary
Varbinary()
Byte
TinyInt
Tinyint
Bool
Bit
Bit
Decimal
Decimal
Decimal
Decimal
Money
Money
Decimal
SmallMoney
SmallMoney
Float
Float
Float
Guid
UniqueIdentifier
Uniqueidentifier
Real
Real
Real
Hi..
Thanks for ur reply.. but how can i access the .dbf files/tables from ASP.NET?
What are the procedures used to get the values from a .dbf file/table? the connection string. and driver and the things to connect
Hope get reply soon..
with regards
Fraijo
|||Assuming your dbf files are for FoxPro the links below is all I have got and I cannot tell you anything about it because I have never used it. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfoxgen7/html/usingaspnetwithvfp7.asp
http://forums.asp.net/853129/ShowPost.aspx
Tuesday, March 20, 2012
Convert word doc to XML and store and retrive form SQL 2000
Hi All,
i want to store the word document files as xml files in the sql server 2000 . then i have to retrive the XML Files in to aspx pages. the document files size are 5 mb , 6 mb like that . plz give the solution that how to do it .
Thanks in Advance .
Regards ,
raja
i want to store the word document files as xml files in the sql server 2000 . then i have to retrive the XML Files in to aspx pages. the document files size are 5 mb , 6 mb like that . plz give the solution that how to do it .
Thanks in Advance .
Regards ,
raja
why do they need to be stored in a database? can't they just be stored as files - maybe index them in the database?
Regards
|||I agree, why destroy those data caches with big useless blobs?
Thursday, March 8, 2012
convert stored procedures returned output to varchar from int?
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(
Syntax error converting the varchar value 'ABC' to a column of data type int.
SQL file
======================
DECLARE @.S AS VARCHAR(1000)
EXEC @.S = PDT.DBO.SP_RPT
'ABC'
SELECT CONTENT = @.S -- this is the value I am trying to pass as content of the email
EXEC PRODUCTION.DBO.SENDEMAIL'xxx@.hotmail.com', 'Notification', @.S
======================
Stored Procedure
======================
CREATE procedure sp_RPT
( @.array varchar(1000) )
AS
DECLARE @.content AS VARCHAR(2000)
SET @.content = 'RPT: ' + @.array + ' loaded successfully '
SET @.array = 'ABC'
RETURN CONVERT(VARCHAR(1000),@.array)
GO
try
cast( @.array as varchar(1000))
Hope this help
|||Don't use RETURN values in that manner. Either use an output parameter, or a resultset. RETURN values should be used as a control channel, not as a data channel; RETURN's purpose is to terminate control of a stored procedure immediately and also to report on how control was terminated (0 = normal/good, anything else is abnormal/bad).Friday, February 24, 2012
Convert MS Excel to XML and then insert all data records into MS SQL
Hi,
I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.
If you are using excel, you don't need to transform to xml. You can load data directly from excel file.
INSERT YourTable(...)
SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]
1) Save your excel file in XML format
2) create YourTable(xml_excel xml)
3) Run
INSERT YourTable(xml_excel)
select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)
|||Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:
1) Upload Excel file (Will be grateful if users can directly import.)
2) System Import data from specific data column in Excel to MS SQL table
A pre-define Excel workbook is provided for users.|||
I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.
Is there a way to convert on a server?
Thanks
|||Kenneth,
Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.
Scott Shorter
FarPoint Technologies
Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.
Regards,
Shiva
|||See http://www.360data.nl/EN/Docs/080123_XML.aspx for an example parsing Excel-generated XML into a SQL db.Convert MS Excel to XML and then insert all data records into MS SQL
Hi,
I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.
If you are using excel, you don't need to transform to xml. You can load data directly from excel file.
INSERT YourTable(...)
SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]
1) Save your excel file in XML format
2) create YourTable(xml_excel xml)
3) Run
INSERT YourTable(xml_excel)
select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)
|||Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:
1) Upload Excel file (Will be grateful if users can directly import.)
2) System Import data from specific data column in Excel to MS SQL table
A pre-define Excel workbook is provided for users.|||
I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.
Is there a way to convert on a server?
Thanks
|||Kenneth,
Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.
Scott Shorter
FarPoint Technologies
Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.
Regards,
Shiva
Convert MS Excel to XML and then insert all data records into MS SQL
Hi,
I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.
If you are using excel, you don't need to transform to xml. You can load data directly from excel file.
INSERT YourTable(...)
SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]
1) Save your excel file in XML format
2) create YourTable(xml_excel xml)
3) Run
INSERT YourTable(xml_excel)
select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)
|||Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:
1) Upload Excel file (Will be grateful if users can directly import.)
2) System Import data from specific data column in Excel to MS SQL table
A pre-define Excel workbook is provided for users.|||
I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.
Is there a way to convert on a server?
Thanks
|||Kenneth,
Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.
Scott Shorter
FarPoint Technologies
Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.
Regards,
Shiva
Sunday, February 19, 2012
Convert integer to date
Hi,
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in th
e
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out'
Kind regards,
Michel MolsHi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a =E9crit :
> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date in=
the
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
>=20
> Can anyone help me out'
>=20
> Kind regards,
>=20
> Michel Mols
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in th
e
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out'
Kind regards,
Michel MolsHi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a =E9crit :
> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date in=
the
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
>=20
> Can anyone help me out'
>=20
> Kind regards,
>=20
> Michel Mols
Convert integer to date
Hi,
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in the
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out?
Kind regards,
Michel Mols
Hi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a crit :
> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date inthe
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
> Can anyone help me out?
> Kind regards,
> Michel Mols
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in the
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out?
Kind regards,
Michel Mols
Hi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a crit :
> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date inthe
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
> Can anyone help me out?
> Kind regards,
> Michel Mols
Tuesday, February 14, 2012
Convert from Clarion to MS SQL Server or Access
Hello!
Somebody knows how to convert the data from Clarion (tps) to mdb or mdf files?
Any variants?
Thanks to all!
I don't know much about Clarion, well, nothing.There seems to be plenty of ODBC drivers for it though.
You could then use SSIS to import the data into SQL. You cannot as such "convert" anything to mdf. You can import it into SQL though.
http://www.google.co.uk/search?q=clarion+odbc+drivers&sourceid=navclient-ff&ie=UTF-8&rlz=1B3GGGL_enGB213GB213|||Crispin, thanks for the time, given to my question.
Friday, February 10, 2012
Convert Crystal to RDL?
Hi All -
I have heard that Hitachi has created a toll called RDL 2.0 that will take
Crystal .rpt files and convert them to RDL - has anyone else heard\tried this
product?
Thanks
JimMore info on the FAQ
http://www.ReportingServicesFAQ.com/Crystal.asp
Jim wrote:
> Hi All -
> I have heard that Hitachi has created a toll called RDL 2.0 that will take
> Crystal .rpt files and convert them to RDL - has anyone else heard\tried this
> product?
> Thanks
> Jim|||I checked their site and did not find a cue that they have a tool for
converting pdf to rdl. They only supply a service for converting. You upload
your pdf files then they return you in a few days time rdl version.
Eralper
http://www.kodyaz.com
"Jim" wrote:
> Hi All -
> I have heard that Hitachi has created a toll called RDL 2.0 that will take
> Crystal .rpt files and convert them to RDL - has anyone else heard\tried this
> product?
> Thanks
> Jim
I have heard that Hitachi has created a toll called RDL 2.0 that will take
Crystal .rpt files and convert them to RDL - has anyone else heard\tried this
product?
Thanks
JimMore info on the FAQ
http://www.ReportingServicesFAQ.com/Crystal.asp
Jim wrote:
> Hi All -
> I have heard that Hitachi has created a toll called RDL 2.0 that will take
> Crystal .rpt files and convert them to RDL - has anyone else heard\tried this
> product?
> Thanks
> Jim|||I checked their site and did not find a cue that they have a tool for
converting pdf to rdl. They only supply a service for converting. You upload
your pdf files then they return you in a few days time rdl version.
Eralper
http://www.kodyaz.com
"Jim" wrote:
> Hi All -
> I have heard that Hitachi has created a toll called RDL 2.0 that will take
> Crystal .rpt files and convert them to RDL - has anyone else heard\tried this
> product?
> Thanks
> Jim
Subscribe to:
Posts (Atom)