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 working. Show all posts
Showing posts with label working. 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
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 alpha-numeric values
Hello, all. I have come across a dilemma working with SQL server 2005. My
company has a parts database. They want to be able to select a range of
parts based on the part number. Problem is that the part numbers are mixed
with non-numeric characters and special characters. Obviously convert will
not work in the scenario. An example part number:
123-304-10000A
Is there a way to get this included in a range search say from 100 to 200?
I wrote a proc that strips out the non-numeric characters but it is way too
slow with over 140000 parts in the table.
Any help would be appreciated.
Here is one way to strip out the non-numeric characters and to filter on the
numeric part only. In production replace the reference to the system table
master..spt_values with real utility table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx).
CREATE TABLE Parts (
part_no VARCHAR(14) PRIMARY KEY);
INSERT INTO Parts VALUES ('123-304-10000A');
INSERT INTO Parts VALUES ('AAA-BBB-00100A');
INSERT INTO Parts VALUES ('AAA-CCC-00150A');
WITH CleanParts (part_no, num_part_no)
AS
(SELECT part_no, CAST(
(SELECT SUBSTRING(part_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(part_no)
AND SUBSTRING(part_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS BIGINT)
FROM Parts)
SELECT part_no
FROM CleanParts
WHERE num_part_no BETWEEN 100 AND 200;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
sqlsql
company has a parts database. They want to be able to select a range of
parts based on the part number. Problem is that the part numbers are mixed
with non-numeric characters and special characters. Obviously convert will
not work in the scenario. An example part number:
123-304-10000A
Is there a way to get this included in a range search say from 100 to 200?
I wrote a proc that strips out the non-numeric characters but it is way too
slow with over 140000 parts in the table.
Any help would be appreciated.
Here is one way to strip out the non-numeric characters and to filter on the
numeric part only. In production replace the reference to the system table
master..spt_values with real utility table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx).
CREATE TABLE Parts (
part_no VARCHAR(14) PRIMARY KEY);
INSERT INTO Parts VALUES ('123-304-10000A');
INSERT INTO Parts VALUES ('AAA-BBB-00100A');
INSERT INTO Parts VALUES ('AAA-CCC-00150A');
WITH CleanParts (part_no, num_part_no)
AS
(SELECT part_no, CAST(
(SELECT SUBSTRING(part_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(part_no)
AND SUBSTRING(part_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS BIGINT)
FROM Parts)
SELECT part_no
FROM CleanParts
WHERE num_part_no BETWEEN 100 AND 200;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
sqlsql
Converting alpha-numeric values
Hello, all. I have come across a dilemma working with SQL server 2005. My
company has a parts database. They want to be able to select a range of
parts based on the part number. Problem is that the part numbers are mixed
with non-numeric characters and special characters. Obviously convert will
not work in the scenario. An example part number:
123-304-10000A
Is there a way to get this included in a range search say from 100 to 200?
I wrote a proc that strips out the non-numeric characters but it is way too
slow with over 140000 parts in the table.
Any help would be appreciated.Here is one way to strip out the non-numeric characters and to filter on the
numeric part only. In production replace the reference to the system table
master..spt_values with real utility table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx).
CREATE TABLE Parts (
part_no VARCHAR(14) PRIMARY KEY);
INSERT INTO Parts VALUES ('123-304-10000A');
INSERT INTO Parts VALUES ('AAA-BBB-00100A');
INSERT INTO Parts VALUES ('AAA-CCC-00150A');
WITH CleanParts (part_no, num_part_no)
AS
(SELECT part_no, CAST(
(SELECT SUBSTRING(part_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(part_no)
AND SUBSTRING(part_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS BIGINT)
FROM Parts)
SELECT part_no
FROM CleanParts
WHERE num_part_no BETWEEN 100 AND 200;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
company has a parts database. They want to be able to select a range of
parts based on the part number. Problem is that the part numbers are mixed
with non-numeric characters and special characters. Obviously convert will
not work in the scenario. An example part number:
123-304-10000A
Is there a way to get this included in a range search say from 100 to 200?
I wrote a proc that strips out the non-numeric characters but it is way too
slow with over 140000 parts in the table.
Any help would be appreciated.Here is one way to strip out the non-numeric characters and to filter on the
numeric part only. In production replace the reference to the system table
master..spt_values with real utility table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx).
CREATE TABLE Parts (
part_no VARCHAR(14) PRIMARY KEY);
INSERT INTO Parts VALUES ('123-304-10000A');
INSERT INTO Parts VALUES ('AAA-BBB-00100A');
INSERT INTO Parts VALUES ('AAA-CCC-00150A');
WITH CleanParts (part_no, num_part_no)
AS
(SELECT part_no, CAST(
(SELECT SUBSTRING(part_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(part_no)
AND SUBSTRING(part_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS BIGINT)
FROM Parts)
SELECT part_no
FROM CleanParts
WHERE num_part_no BETWEEN 100 AND 200;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Converting Access qrys with logical expressions
I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;
Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?
Thanks in anticipation
Terry Bell"Terry Bell" <dreadnought8@.hotmail.com> wrote in message
news:923537d6.0409142346.301c9c3@.posting.google.co m...
> I'm upsizing an Access database. Got the data converted, working on
> the front end, converting queries to views, but having trouble
> converting queries that use logical expressions like the following:
> SELECT OrderId,
> Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> FROM OrderDetails
> INNER JOIN Items
> ON (OrderDetails.ClientId = Items.ClientId)
> AND (OrderDetails.ItemId = Items.ItemId)
> WHERE (NOT (SitesCustomerTypeId = 2
> AND ExpressBackorder =True
> AND OrderUrgency = 1 ))
> GROUP BY OrderId;
> Can someone suggest a strategy to achieve the same result, ie
> OrderId,ReadBackOrderItems that I can use in further joins?
> Thanks in anticipation
> Terry Bell
Are you asking how to rewrite the SUM expression? I don't know exactly what
the syntax above means, so this is a guess:
sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
end * -1)
If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
to create your tables and populate some sample data, along with the result
you expect to see from your query.
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<41480077$1_2@.news.bluewin.ch>...
> "Terry Bell" <dreadnought8@.hotmail.com> wrote in message
> news:923537d6.0409142346.301c9c3@.posting.google.co m...
> > I'm upsizing an Access database. Got the data converted, working on
> > the front end, converting queries to views, but having trouble
> > converting queries that use logical expressions like the following:
> > SELECT OrderId,
> > Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> > FROM OrderDetails
> > INNER JOIN Items
> > ON (OrderDetails.ClientId = Items.ClientId)
> > AND (OrderDetails.ItemId = Items.ItemId)
> > WHERE (NOT (SitesCustomerTypeId = 2
> > AND ExpressBackorder =True
> > AND OrderUrgency = 1 ))
> > GROUP BY OrderId;
> > Can someone suggest a strategy to achieve the same result, ie
> > OrderId,ReadBackOrderItems that I can use in further joins?
> > Thanks in anticipation
> > Terry Bell
> Are you asking how to rewrite the SUM expression? I don't know exactly what
> the syntax above means, so this is a guess:
> sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
> end * -1)
> If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
> to create your tables and populate some sample data, along with the result
> you expect to see from your query.
> Simon
Thanks very much Simon you have given me the direction I needed.
For the record, here's my full converted code - with some side errors
fixed
SELECT Q845UndeliveredOrderDetails.OrderId, SUM(CASE WHEN
BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS ReadyBackOrderItems
FROM Q845UndeliveredOrderDetails INNER JOIN
Items ON (Q845UndeliveredOrderDetails.ClientId =
Items.ClientId) AND (Q845UndeliveredOrderDetails.ItemId =
Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2 AND ExpressBackorder = 1 AND
OrderUrgency = 1))
GROUP BY Q845UndeliveredOrderDetails.OrderId;
So:
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems ... in Access
SQL
becomes
SUM(CASE WHEN BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS
ReadyBackOrderItems ... in SQL
I also note that in Access you can say something like
WHERE IsBackOrder
and it evaluates IsBackOrder as a logical expression
whereas in sql server we need to say
WHERE IsBackorder = 1
Is that right?
Then I guess I need to think about NULL too ...
Also I notice in the query analyser it comes up with a message saying
it can't understand the CASE statement, but I can ignore that, can I,
as it seems to go ahead and execute the query anyway?
Once again thanks a million this has saved me lots of time
Terry Bell|||<snip
> I also note that in Access you can say something like
> WHERE IsBackOrder
> and it evaluates IsBackOrder as a logical expression
> whereas in sql server we need to say
> WHERE IsBackorder = 1
> Is that right?
Not quite - there is no Boolean data type in MSSQL, so how to evaluate
'true' or 'false' depends on the data type you've chosen. One common
solution is to use the bit data type, with 1 for true and 0 for false, in
which case your code above is correct (assuming true = 1).
> Then I guess I need to think about NULL too ...
Yes - this is one reason why you often see requests for DDL (CREATE TABLE
etc.), as this makes it clear which columns allow NULL and which don't.
Something that seems to work fine may fail when NULLs are involved, so you
need to code for them if the data model allows them.
> Also I notice in the query analyser it comes up with a message saying
> it can't understand the CASE statement, but I can ignore that, can I,
> as it seems to go ahead and execute the query anyway?
I have no idea without seeing the full error, but perhaps this is error 8153
"Warning: Null value is eliminated by an aggregate or other SET operation."?
If so, it's just a warning that the column you SUMmed on contains NULL data.
> Once again thanks a million this has saved me lots of time
> Terry Bell
You're welcome.
Simon|||Generally just copy and paste from Access to Query Analyser. Check the
query runs correctly and then add CREATE PROCEDURE blah blah to the top
and run. This turns the script in to a stored procedure and loads it in
to the current database.
You might want to move all the restrictions to the WHERE clause other
wise you can get some interesting results if you are not very careful.
Sum((BackOrderQtyAvailable)* -1)
(BackOrderQtyAvailable > 0)
Adrian
Terry Bell wrote:
> I'm upsizing an Access database. Got the data converted, working on
> the front end, converting queries to views, but having trouble
> converting queries that use logical expressions like the following:
> SELECT OrderId,
> Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> FROM OrderDetails
> INNER JOIN Items
> ON (OrderDetails.ClientId = Items.ClientId)
> AND (OrderDetails.ItemId = Items.ItemId)
> WHERE (NOT (SitesCustomerTypeId = 2
> AND ExpressBackorder =True
> AND OrderUrgency = 1 ))
> GROUP BY OrderId;
> Can someone suggest a strategy to achieve the same result, ie
> OrderId,ReadBackOrderItems that I can use in further joins?
> Thanks in anticipation
> Terry Bell|||On Thu, 16 Sep 2004 17:27:35 +0200, Simon Hayes wrote:
>"Terry Bell" <dreadnought8@.hotmail.com> wrote:
>>
>> Also I notice in the query analyser it comes up with a message saying
>> it can't understand the CASE statement, but I can ignore that, can I,
>> as it seems to go ahead and execute the query anyway?
> I have no idea without seeing the full error, but perhaps this is error 8153
> "Warning: Null value is eliminated by an aggregate or other SET operation."?
> If so, it's just a warning that the column you SUMmed on contains NULL data.
I don't think it's a null error -- I think he was editing his query in MS
Access's query editor, in an ADP file, rather than using SQL Server's Query
Analyzer. I've gotten that error from MS Access myself.
As Terry said, Access goes ahead and executes it anyway. It just can't
parse it properly to represent it in the graphical query editor.
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
Tuesday, March 20, 2012
Converted Access database gives problems due to field names
Hi
I have been working with asp.net against an Access database (created by someone else several years ago). Now I have to move the database to run under MSSQL Server. I successfully updated it via the Access migration tool, and it's running fine under MSDE.
However, many of the fields in one table are 'named' as 'numbers' - eg 101, 102, 34 etc. Therefore SQL commands such as:
update Mytable set 101 = "test" where ID = 2
fail with Incorrect syntax near '101'.
I would never set up such a database structure myself, but I am stuck with it. Is there any way to make MSSQL commands work with this sort of naming?
Thanks for any help you can give.
LeeYou could try:
update Mytable set [101] = "test" where ID = 2
I have not tried this with fields that are all numbers, but I expect it should work.sqlsql
convert year from mm/dd/yyyy to mm/dd/yy
I am working with a chart. I want to display the year on the X axis as mm/dd/yy. I have tried entering yy, etc into the format code field and have had no luck.
Any comments are appreciated.
Reporting Services uses similar formatting commands to Excel. You should be able to use MM/dd/yy.
If that doesn't work, another way would be to create a function that would return the values in a string format and parse the string. Something like:
public function getDate(dt as string) as string
string dt = left(dt,6) & right(dt,2)
return dt
end function
cheers,
Andrew
|||Thanks! I was able to get this to work
CONVERT(varchar(12), field_name, 10) AS Date
Monday, March 19, 2012
convert UTC date
hi- i'm working with a database that stores dates in UTC format. does
anyone know of, or have, a function to convert from this format?
tia,
jtWell, what is your offset from UTC?
DECLARE @.offset TINYINT;
SET @.offset = ?;
SELECT DATEADD(HOUR, @.offset, datetimeColumn) FROM table;
If you participate in daylight savings time, you will be much better off
using a calendar table.
http://www.aspfaq.com/2519
And yes, I need to update the article to account for the change in DST
timeframes here in the US, that take effect this year IIRC.
A
"JTL" <jliautaud@.hotmail.com> wrote in message
news:OBHy0SuFGHA.1192@.TK2MSFTNGP11.phx.gbl...
> hi- i'm working with a database that stores dates in UTC format. does
> anyone know of, or have, a function to convert from this format?
> tia,
> jt
>|||ok- thanks for the help-
this database stores the number of seconds that have passed since 1/1/1970,
as an integer field. so how do i convert that to the current date?
jt
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23bQMScuFGHA.3532@.TK2MSFTNGP14.phx.gbl...
> Well, what is your offset from UTC?
> DECLARE @.offset TINYINT;
> SET @.offset = ?;
> SELECT DATEADD(HOUR, @.offset, datetimeColumn) FROM table;
> If you participate in daylight savings time, you will be much better off
> using a calendar table.
> http://www.aspfaq.com/2519
> And yes, I need to update the article to account for the change in DST
> timeframes here in the US, that take effect this year IIRC.
> A
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:OBHy0SuFGHA.1192@.TK2MSFTNGP11.phx.gbl...
>|||http://www.aspfaq.com/2451
As an aside, you may want to use BIGINT, if you ever want to store dates
beyond 2038-01-18.
A
"JTL" <jliautaud@.hotmail.com> wrote in message
news:%23YXLk0uFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> ok- thanks for the help-
> this database stores the number of seconds that have passed since
> 1/1/1970, as an integer field. so how do i convert that to the current
> date?
> jt
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23bQMScuFGHA.3532@.TK2MSFTNGP14.phx.gbl...
>|||thank you- big help!
jt
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upmrY3uFGHA.2704@.TK2MSFTNGP15.phx.gbl...
> http://www.aspfaq.com/2451
> As an aside, you may want to use BIGINT, if you ever want to store dates
> beyond 2038-01-18.
> A
>
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:%23YXLk0uFGHA.3856@.TK2MSFTNGP12.phx.gbl...
>
anyone know of, or have, a function to convert from this format?
tia,
jtWell, what is your offset from UTC?
DECLARE @.offset TINYINT;
SET @.offset = ?;
SELECT DATEADD(HOUR, @.offset, datetimeColumn) FROM table;
If you participate in daylight savings time, you will be much better off
using a calendar table.
http://www.aspfaq.com/2519
And yes, I need to update the article to account for the change in DST
timeframes here in the US, that take effect this year IIRC.
A
"JTL" <jliautaud@.hotmail.com> wrote in message
news:OBHy0SuFGHA.1192@.TK2MSFTNGP11.phx.gbl...
> hi- i'm working with a database that stores dates in UTC format. does
> anyone know of, or have, a function to convert from this format?
> tia,
> jt
>|||ok- thanks for the help-
this database stores the number of seconds that have passed since 1/1/1970,
as an integer field. so how do i convert that to the current date?
jt
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23bQMScuFGHA.3532@.TK2MSFTNGP14.phx.gbl...
> Well, what is your offset from UTC?
> DECLARE @.offset TINYINT;
> SET @.offset = ?;
> SELECT DATEADD(HOUR, @.offset, datetimeColumn) FROM table;
> If you participate in daylight savings time, you will be much better off
> using a calendar table.
> http://www.aspfaq.com/2519
> And yes, I need to update the article to account for the change in DST
> timeframes here in the US, that take effect this year IIRC.
> A
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:OBHy0SuFGHA.1192@.TK2MSFTNGP11.phx.gbl...
>|||http://www.aspfaq.com/2451
As an aside, you may want to use BIGINT, if you ever want to store dates
beyond 2038-01-18.
A
"JTL" <jliautaud@.hotmail.com> wrote in message
news:%23YXLk0uFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> ok- thanks for the help-
> this database stores the number of seconds that have passed since
> 1/1/1970, as an integer field. so how do i convert that to the current
> date?
> jt
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23bQMScuFGHA.3532@.TK2MSFTNGP14.phx.gbl...
>|||thank you- big help!
jt
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upmrY3uFGHA.2704@.TK2MSFTNGP15.phx.gbl...
> http://www.aspfaq.com/2451
> As an aside, you may want to use BIGINT, if you ever want to store dates
> beyond 2038-01-18.
> A
>
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:%23YXLk0uFGHA.3856@.TK2MSFTNGP12.phx.gbl...
>
convert update from access to sql and have an error.
I am trying to convert code I have working for access to work with SQL.
fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting.
And here is the stack trace (which I don't know how to read except for the line the error is on)
here is my code.
MichaelSQL uses named parameters. Try:
That did take care of the error, but it is not updating. Maybe it is updating its just updating what was already in the database and now the new data. Is there a way to see if the new data in the textbox is present? When I do a
Thanks
Michael|||Just tried this and it updated.
So my new data is not getting to the right place.
So what am i missing?
Thanks
Michael|||You need to have your data binding only take place when IsPostback is false:
fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting.
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'.
And here is the stack trace (which I don't know how to read except for the line the error is on)
[SqlException: Line 1: Incorrect syntax near '?'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
goodellweb.adm_contact.editNow_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\webroot\goodellweb\adm\adm_contacts.aspx.vb:306
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1315
here is my code.
Thanks
Dim editSQL As String = "Update tbEmail Set fldName=?, fldEmail=? Where ID=?"
Dim SqlConn As New SqlConnection(ConnStr)
Dim Cmd As New SqlCommand(editSQL, SqlConn)
Cmd.Parameters.Add(New SqlParameter("@.fldName", nameEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.fldEmail", emailEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.recNum", recNum))SqlConn.Open()
Try
Cmd.ExecuteNonQuery()
Finally
SqlConn.Close()
End Try
Response.Write("recNum " & recNum & " <br>")
MichaelSQL uses named parameters. Try:
|||hanks douglas
Dim editSQL As String = "Update tbEmail Set fldName=@.fldName, fldEmail=@.fldEmail Where ID=@.recNum"Dim SqlConn As New SqlConnection(ConnStr)
Dim Cmd As New SqlCommand(editSQL, SqlConn)
Cmd.Parameters.Add(New SqlParameter("@.fldName", nameEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.fldEmail", emailEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.recNum", recNum))
That did take care of the error, but it is not updating. Maybe it is updating its just updating what was already in the database and now the new data. Is there a way to see if the new data in the textbox is present? When I do a
Response.Write("email " & emailEdit.Text & " <br>") at the end of the code it shows the data in the database. Should it be the new data?Thanks
Michael|||Just tried this and it updated.
Cmd.Parameters.Add(New SqlParameter("@.fldName", "test"))
So my new data is not getting to the right place.
So what am i missing?
Thanks
Michael|||You need to have your data binding only take place when IsPostback is false:
If IsPostback=false then
' data bind ONLY here
End If
What you do otherwise is re-read the data from the database and "update" with the data from the database.
Sunday, March 11, 2012
convert the format of a Date Time String transformation....
Im working with a breaking system and I wont to convert the (FROM) datetime column to accept just time like (4:00:00 AM) without the date (7/23/2004) but it doesn't have column format like the access ??
I found something in the SQL help :
I found something in the SQL help :
How to convert the format of a Date Time String transformation (Enterprise Manager)
To convert the format of a Date Time String transformation
1.On the Transformations tab of the Transform Data Task Properties or Data Driven Query Task Properties dialog box, click the Source column containing the date or time to be modified, and then click the Destination column where you want the modified string to be placed.
2.Do one of the following:
?If there is a mapping arrow connecting the two columns, click Delete, and then click New.
?If there is no mapping arrow, click New.
3.In the Create New Transformation dialog box, click DateTime String.
4.Click the General tab, and then click Properties.
5.In the Date Format list, select the format you want.
6.Click Naming to display the Calendar Names dialog box, where you can select long or short day or month names and the A.M. and P.M. designators you want.
7.In the Language list, select the language you want, and then click Set Language Defaults.
But unfortunately I didn't find the "Transformations tab" I look a lot in the SQL Enterprise Manager
Do anybody work with the Transformations or at least know where is it please ?You're barking up the wrong tree there. SQL Server has no time only data format. The best you can do is strip the time element off and store it with a fixed date, say 1900-01-01
Thursday, March 8, 2012
Convert SqlDataSource to String.
I'm working with an SqlDataSource, and I'm just wondering how to get the data to a String if I'm sure the select statement will only return 1 piece of data. Any ideas?
useExecuteScalar() method of your sql command. it will return the first column of the first row in the result set return by your select query.
Hi,
You can try to use sqldatasource.select with DataView. Try the codes below, i think it should be able to solve your problems
SqlDataSource1.SelectCommand="Your Select Command Here";DataView dv = SqlDataSource1.Select(DataSourceSelectArguments.Empty);string mystr = dv.Table.Rows[0][your colum number].ToString();
Now the string "mystr" should be what you are looking after
Hope my suggestion helps
Convert SQL in ASP 3.0 site to UPDATE ntext
Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql ="SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.com
Sorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com
|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.droptable.co m...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql ="SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.com
Sorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com
|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.droptable.co m...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com
Convert SQL in ASP 3.0 site to UPDATE ntext
Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.dr
optable.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.dr
optable.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com
Convert SQL in ASP 3.0 site to UPDATE ntext
Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
--
Message posted via http://www.sqlmonster.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
--
Message posted via http://www.sqlmonster.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.SQLMonster.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.sqlmonster.com
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
--
Message posted via http://www.sqlmonster.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
--
Message posted via http://www.sqlmonster.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.SQLMonster.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.sqlmonster.com
Saturday, February 25, 2012
Convert NULL to zero
One table I am working with has several money columns. Some are null. I need
to add the columns, but the result is NULL since some of the columns are
NULL.
How can I write the query to convert to zeroes?
Thanks.SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
FROM Sometable
--
David Portas
--
Please reply only to the newsgroup
--
"Paul" <nospam@.please.com> wrote in message
news:%23nSQbLK1DHA.2336@.TK2MSFTNGP09.phx.gbl...
> One table I am working with has several money columns. Some are null. I
need
> to add the columns, but the result is NULL since some of the columns are
> NULL.
> How can I write the query to convert to zeroes?
> Thanks.
>|||Thank you. I'll try that.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> FROM Sometable
> --
> David Portas
> --
> Please reply only to the newsgroup|||You can also do it with ISNULL:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
--
Rohtash Kapoor
http://www.sqlmantra.com
"Paul" <nospam@.please.com> wrote in message
news:OtDfAjK1DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thank you. I'll try that.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> > SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> > FROM Sometable
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
>|||Since the SUM aggregate ignores NULLs anyway you will save some processor
cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with the
specific case where all values in the column are NULL.
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
COALESCE is an ANSI Standard SQL function which is why I usually prefer to
use it in preference to the proprietary ISNULL function.
--
David Portas
--
Please reply only to the newsgroup
--|||You are very right.
Instead of this:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
We should write:
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
However, I prefer to use ISNULL than COALESCE because I can type it quickly
(...just kidding..)
--
Rohtash Kapoor
http://www.sqlmantra.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:9O-dnaPUQsGe32aiRVn-vA@.giganews.com...
> Since the SUM aggregate ignores NULLs anyway you will save some processor
> cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with
the
> specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Since the SUM aggregate ignores NULLs anyway you will save some
> processor cycles by putting ISNULL (or COALESCE) outside the SUM. This
> deals with the specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
On the other hand, if you do this and run with ANSI_WARNINGS enabled, you
will get the message "Warnings: null eliminated from aggregate".
(ANSI_WARNINGS is enabled for default, unless you are using DB-Library.)
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
to add the columns, but the result is NULL since some of the columns are
NULL.
How can I write the query to convert to zeroes?
Thanks.SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
FROM Sometable
--
David Portas
--
Please reply only to the newsgroup
--
"Paul" <nospam@.please.com> wrote in message
news:%23nSQbLK1DHA.2336@.TK2MSFTNGP09.phx.gbl...
> One table I am working with has several money columns. Some are null. I
need
> to add the columns, but the result is NULL since some of the columns are
> NULL.
> How can I write the query to convert to zeroes?
> Thanks.
>|||Thank you. I'll try that.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> FROM Sometable
> --
> David Portas
> --
> Please reply only to the newsgroup|||You can also do it with ISNULL:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
--
Rohtash Kapoor
http://www.sqlmantra.com
"Paul" <nospam@.please.com> wrote in message
news:OtDfAjK1DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thank you. I'll try that.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> > SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> > FROM Sometable
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
>|||Since the SUM aggregate ignores NULLs anyway you will save some processor
cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with the
specific case where all values in the column are NULL.
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
COALESCE is an ANSI Standard SQL function which is why I usually prefer to
use it in preference to the proprietary ISNULL function.
--
David Portas
--
Please reply only to the newsgroup
--|||You are very right.
Instead of this:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
We should write:
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
However, I prefer to use ISNULL than COALESCE because I can type it quickly
(...just kidding..)
--
Rohtash Kapoor
http://www.sqlmantra.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:9O-dnaPUQsGe32aiRVn-vA@.giganews.com...
> Since the SUM aggregate ignores NULLs anyway you will save some processor
> cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with
the
> specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Since the SUM aggregate ignores NULLs anyway you will save some
> processor cycles by putting ISNULL (or COALESCE) outside the SUM. This
> deals with the specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
On the other hand, if you do this and run with ANSI_WARNINGS enabled, you
will get the message "Warnings: null eliminated from aggregate".
(ANSI_WARNINGS is enabled for default, unless you are using DB-Library.)
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Sunday, February 19, 2012
Convert hexadecimal value to real data type
Hi ,
I want to convert hexadecimal to numeric data type. Using directly Cast or Convert function is not working.
Please suggest an alternative how to retrieve the numeric value of binary data .
Thanks in advance
Regards
Srinivas Govada
Srinivas,
Please show us what does not work for you. The following is
fine:
declare @.n numeric(10,2)
set @.n = 1.23
select cast(@.n as varbinary(20))
-- returns 0x0A0200017B000000
select cast(0x0A0200017B000000 as numeric(10,2))
If you are trying to convert binary representations of
[float] values to [float], you can use this code:
declare @.b binary(8)
set @.b = 0xC094D954FB549F95
declare @.s bit
declare @.e smallint
declare @.m float
set @.s = case when substring(@.b,1,1) >= 0x80 then 1 else 0 end
set @.e = (substring(@.b,1,2)&32752)/16-1022
set @.m = cast(substring(@.b,6,3) as int)/2097152e0/536870912e0
+ (substring(@.b,2,4)&268435455)/536870912e0+0.5e0
select case when @.s = 1 then -1e0 else 1e0 end * @.m * power(2e0,@.e)
Steve Kass
Drew University
www.stevekass.com
Srinivas Govada@.discussions.microsoft.com wrote:
> Hi ,
>
> I want to convert hexadecimal to numeric data type. Using directly Cast
> or Convert function is not working.
>
> Please suggest an alternative how to retrieve the numeric value of
> binary data .
>
> Thanks in advance
>
> Regards
>
> Srinivas Govada
>
>
>
>
Friday, February 10, 2012
convert datetime
Hi im working on a simple function. I have a table with two columns
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as
a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
endStart with this and modify accordingly:
create function dbo.MyFunc
(
@.date datetime
, @.time datetime
)
returns datetime
as
begin
return (
select
dateadd (ss, datediff (ss, '19000101', @.time), @.date)
)
end
go
select dbo.MyFunc ('2006-01-22', '19:00')
go
drop function dbo.MyFunc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pietro" <pietro@.discussions.microsoft.com> wrote in message
news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
Hi im working on a simple function. I have a table with two columns
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as
a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
end|||Hi Tom, thanks a lot, it works perfectly...I have just one more problem I
cant find it out...
in my table, all the values, which I need to use as a time is like 1.1.1900
9:15:00
how can I separate just the time to use it in the function?
Tom Moreau p_?e:
> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from anothe
r
> one - and then to join it and save it in the variable (which is declered a
s
> a
> datetime). Please, can somebody help me or advice another waz how to get i
t?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr wher
e
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr whe
re
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>|||ive tried again, its working nice. Thanks a lot
Tom Moreau p_?e:
> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from anothe
r
> one - and then to join it and save it in the variable (which is declered a
s
> a
> datetime). Please, can somebody help me or advice another waz how to get i
t?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr wher
e
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr whe
re
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>|||All "time-only" datetime's have a date portion of 1900-01-01.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pietro" <pietro@.discussions.microsoft.com> wrote in message
news:959F23F1-45CF-4FC2-B078-87A4651C8DD1@.microsoft.com...
Hi Tom, thanks a lot, it works perfectly...I have just one more problem I
cant find it out...
in my table, all the values, which I need to use as a time is like 1.1.1900
9:15:00
how can I separate just the time to use it in the function?
Tom Moreau p_?e:
> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from
> another
> one - and then to join it and save it in the variable (which is declered
> as
> a
> datetime). Please, can somebody help me or advice another waz how to get
> it?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr
> where
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr
> where
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as
a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
endStart with this and modify accordingly:
create function dbo.MyFunc
(
@.date datetime
, @.time datetime
)
returns datetime
as
begin
return (
select
dateadd (ss, datediff (ss, '19000101', @.time), @.date)
)
end
go
select dbo.MyFunc ('2006-01-22', '19:00')
go
drop function dbo.MyFunc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pietro" <pietro@.discussions.microsoft.com> wrote in message
news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
Hi im working on a simple function. I have a table with two columns
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as
a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
end|||Hi Tom, thanks a lot, it works perfectly...I have just one more problem I
cant find it out...
in my table, all the values, which I need to use as a time is like 1.1.1900
9:15:00
how can I separate just the time to use it in the function?
Tom Moreau p_?e:
> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from anothe
r
> one - and then to join it and save it in the variable (which is declered a
s
> a
> datetime). Please, can somebody help me or advice another waz how to get i
t?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr wher
e
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr whe
re
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>|||ive tried again, its working nice. Thanks a lot
Tom Moreau p_?e:
> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from anothe
r
> one - and then to join it and save it in the variable (which is declered a
s
> a
> datetime). Please, can somebody help me or advice another waz how to get i
t?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr wher
e
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr whe
re
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>|||All "time-only" datetime's have a date portion of 1900-01-01.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pietro" <pietro@.discussions.microsoft.com> wrote in message
news:959F23F1-45CF-4FC2-B078-87A4651C8DD1@.microsoft.com...
Hi Tom, thanks a lot, it works perfectly...I have just one more problem I
cant find it out...
in my table, all the values, which I need to use as a time is like 1.1.1900
9:15:00
how can I separate just the time to use it in the function?
Tom Moreau p_?e:
> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from
> another
> one - and then to join it and save it in the variable (which is declered
> as
> a
> datetime). Please, can somebody help me or advice another waz how to get
> it?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr
> where
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr
> where
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>
convert dates
dear experts,
i'm working in a website in asp.net where all the data is coming from stored procedures, i have also a callendar, now what i want is that when a client click a date i want to send this value to the stored procedure and the query the database according to that.
i have a day, week, month selection, i did the following in C#
date1(if the client click on a day)
= Calendar1.SelectedDate.ToShortDateString(); this one is simple
but i want to know how to convert the date for the week and the date for the month so the query knows which date and the query the database according to that date.
for the week selection i have just the value stored in DateW = (just the week number) and for the month DateM = (month number 3) how can i convert these dates in sqlserver so the query knows for example if a month selected is march, then the C# value will be 3 and i want the query knows that this is march(from 01-03-2007 till 31-03-2007)?!!!
any suggestions will be very appreciated.
thanks experts.Don't send the bits and pieces of the date string to SQL Server. Convert them to a valic datetime value in C# and then send that to the sproc.|||Hi,
For the first date and the last date of a month check the article at http://www.kodyaz.com/content/FirstAndLastDaysOfMonth.aspx
But also the following select may help you for both month and week
SELECT
firstdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
lastdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) ,
firstdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0),
lastdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
Eralper
http://www.kodyaz.com|||thanks for the quick reply,
actually it can not be converted from int to datetime in C# because in C# it is Gregorian calendar which gives you just the week number and the month number, what i want is if there is a way to take this value which is dateM = 3 which is march and convert it in the stored procedure so the query will know that this is march?!!!!
thanks|||Hi,
Do you mean you have only the month and the year parameters?
If so, please check the following sql code,
declare @.y int, @.m int
set @.m = 12
set @.y = 2007
SELECT
firstdate_month =
CAST(
CAST(@.y as varchar(4)) + '/' + CASE WHEN @.m > 9 THEN CAST(@.m as varchar(2)) ELSE '0' + CAST(@.m as char(1)) END + '/01'
as datetime
),
lastdate_month =
DATEADD(dd,-1,
CAST(
CAST(
CASE WHEN @.m < 12 THEN @.y ELSE @.y + 1 END as varchar(4)
) +
'/' +
CASE
WHEN (@.m+1 < 10) THEN '0' + CAST(@.m+1 as char(1))
WHEN (@.m+1 > 9 and @.m+1 < 12) THEN CAST(@.m+1 as varchar(2))
ELSE '01'
END +
'/01'
as datetime
)
)
Eralper
http://www.kodyaz.com|||yes it is something like that, but i already have the query for the database and two parameters, the daystamp is one of them. how can i combine the SP i have and the one you give me? plus how do i need to do the week date, in C# i have just the week number, how can i tell to the SP which date to query the database for example dateW = 13 which is the week 13 of the year?!!
thanks my friend for the help,
good looking|||Hi,
I prepared the following script for the week problem. This is a little bit different when compared with the month version.
But this has to be modified for dates if the week is not in the current year.
declare @.w int
set @.w = 1
SELECT
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
),
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
)
Eralper
http://www.kodyaz.com|||thanks man for the help,
last question :) i have already a stored procedure that have 2 parameters one of these parameter is the date, how can i combine the sp i already have with the 2 SP you gives me, the @.w is the one i need to give to my stored procedure to query the date, so how can i send the value from the C# code convert it with your sp and then feed it to the my sp to query the database according to the converted date?!!!
thanks
i'm working in a website in asp.net where all the data is coming from stored procedures, i have also a callendar, now what i want is that when a client click a date i want to send this value to the stored procedure and the query the database according to that.
i have a day, week, month selection, i did the following in C#
date1(if the client click on a day)
= Calendar1.SelectedDate.ToShortDateString(); this one is simple
but i want to know how to convert the date for the week and the date for the month so the query knows which date and the query the database according to that date.
for the week selection i have just the value stored in DateW = (just the week number) and for the month DateM = (month number 3) how can i convert these dates in sqlserver so the query knows for example if a month selected is march, then the C# value will be 3 and i want the query knows that this is march(from 01-03-2007 till 31-03-2007)?!!!
any suggestions will be very appreciated.
thanks experts.Don't send the bits and pieces of the date string to SQL Server. Convert them to a valic datetime value in C# and then send that to the sproc.|||Hi,
For the first date and the last date of a month check the article at http://www.kodyaz.com/content/FirstAndLastDaysOfMonth.aspx
But also the following select may help you for both month and week
SELECT
firstdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
lastdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) ,
firstdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0),
lastdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
Eralper
http://www.kodyaz.com|||thanks for the quick reply,
actually it can not be converted from int to datetime in C# because in C# it is Gregorian calendar which gives you just the week number and the month number, what i want is if there is a way to take this value which is dateM = 3 which is march and convert it in the stored procedure so the query will know that this is march?!!!!
thanks|||Hi,
Do you mean you have only the month and the year parameters?
If so, please check the following sql code,
declare @.y int, @.m int
set @.m = 12
set @.y = 2007
SELECT
firstdate_month =
CAST(
CAST(@.y as varchar(4)) + '/' + CASE WHEN @.m > 9 THEN CAST(@.m as varchar(2)) ELSE '0' + CAST(@.m as char(1)) END + '/01'
as datetime
),
lastdate_month =
DATEADD(dd,-1,
CAST(
CAST(
CASE WHEN @.m < 12 THEN @.y ELSE @.y + 1 END as varchar(4)
) +
'/' +
CASE
WHEN (@.m+1 < 10) THEN '0' + CAST(@.m+1 as char(1))
WHEN (@.m+1 > 9 and @.m+1 < 12) THEN CAST(@.m+1 as varchar(2))
ELSE '01'
END +
'/01'
as datetime
)
)
Eralper
http://www.kodyaz.com|||yes it is something like that, but i already have the query for the database and two parameters, the daystamp is one of them. how can i combine the SP i have and the one you give me? plus how do i need to do the week date, in C# i have just the week number, how can i tell to the SP which date to query the database for example dateW = 13 which is the week 13 of the year?!!
thanks my friend for the help,
good looking|||Hi,
I prepared the following script for the week problem. This is a little bit different when compared with the month version.
But this has to be modified for dates if the week is not in the current year.
declare @.w int
set @.w = 1
SELECT
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
),
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
)
Eralper
http://www.kodyaz.com|||thanks man for the help,
last question :) i have already a stored procedure that have 2 parameters one of these parameter is the date, how can i combine the sp i already have with the 2 SP you gives me, the @.w is the one i need to give to my stored procedure to query the date, so how can i send the value from the C# code convert it with your sp and then feed it to the my sp to query the database according to the converted date?!!!
thanks
Subscribe to:
Posts (Atom)