Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

sql

Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

CR Ver 10 String to Memo Field

How can I convert a String Field to a Memo Field so I can display more then 255 characters on my report? I've tried the "Can Grow" option and it's not working.......After setting the can grow option, did you increase the field height?

Coverting varchar to numeric

I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
--
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com

Coverting varchar to numeric

I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.com
The CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com

Coverting varchar to numeric

I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com

Coverting mixed case data to UPPERCASE

I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.upper (COLUMNNAME) ?|||I tried your suggestion and it fixed my first issue, but now I have to figure out how to force the data to be uppercase whenever the user inserts/updates a client name. I would like to do this in a BEFORE trigger. Can anyone suggest a way for me to do that. Thanks.|||ahhhhh...smell the Oracle background...

nope INSTEAD of AND AFTER Triggers...

Have you thought about a constraint?|||User updating the column through the application or through QA ??
if application then
update table_name set column_name = upper(variable_here) will do

if you want a trigger
see instead of triggers in BOL ...

Coverting float to datetime

For some reason if I do a convert to a datetime, the time ends up being 2
days off. For example if I do a cast or convert of 38027 I get 2004-02-12.
If I put that same value in excel and make the field date, it shows
2004-02-10. The 10th is the correct date and its making me crazy trying to
figure out what the problem is. I have tried using decimal as opposed to
float and I still get the same results. Any ideas out there?James,
Two days difference come because of two reasons:
1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
while Excel as 1
2. Excel incorrectly assumes 1900 is a leap year, as described at
http://support.microsoft.com/defaul...NoWebContent=1.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> For some reason if I do a convert to a datetime, the time ends up being 2
> days off. For example if I do a cast or convert of 38027 I get
2004-02-12.
> If I put that same value in excel and make the field date, it shows
> 2004-02-10. The 10th is the correct date and its making me crazy trying
to
> figure out what the problem is. I have tried using decimal as opposed to
> float and I still get the same results. Any ideas out there?
>|||But I am using Excel XP and Excel is the one that is reporting the date
correctly. If I do select convert(float,getdate()) and paste that number in
excel, convert the field to date, it show it as two days ago. Something is
amiss.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> James,
> Two days difference come because of two reasons:
> 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> while Excel as 1
> 2. Excel incorrectly assumes 1900 is a leap year, as described at
>
http://support.microsoft.com/defaul...NoWebContent=1.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
2
> 2004-02-12.
> to
to
>|||Why do you expect that converting to some strange float representation will
result in a meaningful value? Especially assuming that two products will use
the same rules when converting from some strange float value to datetime?
Excel and SQL Server uses different internal representation of datetime.
This is why you never should rely on the internal representation of the
datetime datatype. Always use the supported character representation. (IMO,
SQL Server shouldn't even allow for such CAST or CONVERT...)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> But I am using Excel XP and Excel is the one that is reporting the date
> correctly. If I do select convert(float,getdate()) and paste that number
in
> excel, convert the field to date, it show it as two days ago. Something
is
> amiss.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
>
http://support.microsoft.com/defaul...NoWebContent=1.
being
> 2
trying
> to
>|||Thanks for nothing. That was real helpful.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||Agree with Tibor, just to add - have you checked the link in the bullet 2.
of my previous answer? MS says there is bug in Execl. Chek on some calendar,
1900 really was not a leap year.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||I was trying to pass the message that using anything other than a character
representation will eventually lead to these type of situations. AFAIK, SQL
Server doesn't even document what conversion from datetime to float will be.
In other words, they could change that in the next version.
My apologies if the post came out as unhelpful or rude. That was not my
intent.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:%23DVtmd89DHA.1268@.TK2MSFTNGP12.phx.gbl...
> Thanks for nothing. That was real helpful.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> will
> use
datetime?
> (IMO,
>
http://groups.google.com/groups?oi=...ublic.sqlserver
date
> number
Something
wrote
> in
> datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
shows
> opposed
>

Coverting float to datetime

For some reason if I do a convert to a datetime, the time ends up being 2
days off. For example if I do a cast or convert of 38027 I get 2004-02-12.
If I put that same value in excel and make the field date, it shows
2004-02-10. The 10th is the correct date and its making me crazy trying to
figure out what the problem is. I have tried using decimal as opposed to
float and I still get the same results. Any ideas out there?James,
Two days difference come because of two reasons:
1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
while Excel as 1
2. Excel incorrectly assumes 1900 is a leap year, as described at
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> For some reason if I do a convert to a datetime, the time ends up being 2
> days off. For example if I do a cast or convert of 38027 I get
2004-02-12.
> If I put that same value in excel and make the field date, it shows
> 2004-02-10. The 10th is the correct date and its making me crazy trying
to
> figure out what the problem is. I have tried using decimal as opposed to
> float and I still get the same results. Any ideas out there?
>|||But I am using Excel XP and Excel is the one that is reporting the date
correctly. If I do select convert(float,getdate()) and paste that number in
excel, convert the field to date, it show it as two days ago. Something is
amiss.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> James,
> Two days difference come because of two reasons:
> 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> while Excel as 1
> 2. Excel incorrectly assumes 1900 is a leap year, as described at
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > For some reason if I do a convert to a datetime, the time ends up being
2
> > days off. For example if I do a cast or convert of 38027 I get
> 2004-02-12.
> > If I put that same value in excel and make the field date, it shows
> > 2004-02-10. The 10th is the correct date and its making me crazy trying
> to
> > figure out what the problem is. I have tried using decimal as opposed
to
> > float and I still get the same results. Any ideas out there?
> >
> >
>|||Why do you expect that converting to some strange float representation will
result in a meaningful value? Especially assuming that two products will use
the same rules when converting from some strange float value to datetime?
Excel and SQL Server uses different internal representation of datetime.
This is why you never should rely on the internal representation of the
datetime datatype. Always use the supported character representation. (IMO,
SQL Server shouldn't even allow for such CAST or CONVERT...)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> But I am using Excel XP and Excel is the one that is reporting the date
> correctly. If I do select convert(float,getdate()) and paste that number
in
> excel, convert the field to date, it show it as two days ago. Something
is
> amiss.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > James,
> >
> > Two days difference come because of two reasons:
> > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> > while Excel as 1
> > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Associate Mentor
> > Solid Quality Learning
> > More than just Training
> > www.SolidQualityLearning.com
> >
> > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > For some reason if I do a convert to a datetime, the time ends up
being
> 2
> > > days off. For example if I do a cast or convert of 38027 I get
> > 2004-02-12.
> > > If I put that same value in excel and make the field date, it shows
> > > 2004-02-10. The 10th is the correct date and its making me crazy
trying
> > to
> > > figure out what the problem is. I have tried using decimal as opposed
> to
> > > float and I still get the same results. Any ideas out there?
> > >
> > >
> >
> >
>|||Thanks for nothing. That was real helpful.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > But I am using Excel XP and Excel is the one that is reporting the date
> > correctly. If I do select convert(float,getdate()) and paste that
number
> in
> > excel, convert the field to date, it show it as two days ago. Something
> is
> > amiss.
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > James,
> > >
> > > Two days difference come because of two reasons:
> > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
datetime),
> > > while Excel as 1
> > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Associate Mentor
> > > Solid Quality Learning
> > > More than just Training
> > > www.SolidQualityLearning.com
> > >
> > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > For some reason if I do a convert to a datetime, the time ends up
> being
> > 2
> > > > days off. For example if I do a cast or convert of 38027 I get
> > > 2004-02-12.
> > > > If I put that same value in excel and make the field date, it shows
> > > > 2004-02-10. The 10th is the correct date and its making me crazy
> trying
> > > to
> > > > figure out what the problem is. I have tried using decimal as
opposed
> > to
> > > > float and I still get the same results. Any ideas out there?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Agree with Tibor, just to add - have you checked the link in the bullet 2.
of my previous answer? MS says there is bug in Execl. Chek on some calendar,
1900 really was not a leap year.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > But I am using Excel XP and Excel is the one that is reporting the date
> > correctly. If I do select convert(float,getdate()) and paste that
number
> in
> > excel, convert the field to date, it show it as two days ago. Something
> is
> > amiss.
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > James,
> > >
> > > Two days difference come because of two reasons:
> > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
datetime),
> > > while Excel as 1
> > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Associate Mentor
> > > Solid Quality Learning
> > > More than just Training
> > > www.SolidQualityLearning.com
> > >
> > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > For some reason if I do a convert to a datetime, the time ends up
> being
> > 2
> > > > days off. For example if I do a cast or convert of 38027 I get
> > > 2004-02-12.
> > > > If I put that same value in excel and make the field date, it shows
> > > > 2004-02-10. The 10th is the correct date and its making me crazy
> trying
> > > to
> > > > figure out what the problem is. I have tried using decimal as
opposed
> > to
> > > > float and I still get the same results. Any ideas out there?
> > > >
> > > >
> > >
> > >
> >
> >
>|||I was trying to pass the message that using anything other than a character
representation will eventually lead to these type of situations. AFAIK, SQL
Server doesn't even document what conversion from datetime to float will be.
In other words, they could change that in the next version.
My apologies if the post came out as unhelpful or rude. That was not my
intent.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:%23DVtmd89DHA.1268@.TK2MSFTNGP12.phx.gbl...
> Thanks for nothing. That was real helpful.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> > Why do you expect that converting to some strange float representation
> will
> > result in a meaningful value? Especially assuming that two products will
> use
> > the same rules when converting from some strange float value to
datetime?
> > Excel and SQL Server uses different internal representation of datetime.
> > This is why you never should rely on the internal representation of the
> > datetime datatype. Always use the supported character representation.
> (IMO,
> > SQL Server shouldn't even allow for such CAST or CONVERT...)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > > But I am using Excel XP and Excel is the one that is reporting the
date
> > > correctly. If I do select convert(float,getdate()) and paste that
> number
> > in
> > > excel, convert the field to date, it show it as two days ago.
Something
> > is
> > > amiss.
> > >
> > >
> > > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si>
wrote
> in
> > > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > > James,
> > > >
> > > > Two days difference come because of two reasons:
> > > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
> datetime),
> > > > while Excel as 1
> > > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > > >
> > > > --
> > > > Dejan Sarka, SQL Server MVP
> > > > Associate Mentor
> > > > Solid Quality Learning
> > > > More than just Training
> > > > www.SolidQualityLearning.com
> > > >
> > > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > > For some reason if I do a convert to a datetime, the time ends up
> > being
> > > 2
> > > > > days off. For example if I do a cast or convert of 38027 I get
> > > > 2004-02-12.
> > > > > If I put that same value in excel and make the field date, it
shows
> > > > > 2004-02-10. The 10th is the correct date and its making me crazy
> > trying
> > > > to
> > > > > figure out what the problem is. I have tried using decimal as
> opposed
> > > to
> > > > > float and I still get the same results. Any ideas out there?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

covert from string to integer

Are there any function to conver a string to integer?
like it can convert a string "16" to a number 16=CInt("16") should work
or
=Int32.Parse("16")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:eE3Z0DQnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Are there any function to conver a string to integer?
> like it can convert a string "16" to a number 16
>

Covert Connection from Access to SQL

---

Edited by haidar_bilal - Please place your code within< code > and < /code > tag. Thank you.

---

I've got to convert the following functions so it connects to the SQL server that uses this connection string: ("server=(local);database=BCA;Trusted_Connection=yes")

The code below works fine to an Access db. I know I've got to change all the Ole objects below to SQL objects to connect to SQL server. But try as I might, I'm still not getting them right. Can anyone help by showing me how to change the code? Please just take the code and substitute the SQL connection strings for the Access string. That way even I could understand it. Sigh.

Public Class Functions
Public Function GetCommandObject(ByVal strCommand As String, ByVal strPath As String, ByVal ExecuteIt As Boolean) As Data.OleDb.OleDbCommand
Dim objConnection As Data.OleDb.OleDbConnection = GetConnectionObject(strPath)
objConnection.Open()
Dim objCommand As New Data.OleDb.OleDbCommand(strCommand, objConnection)
If ExecuteIt = False Then
Return objCommand
Else
objCommand.ExecuteNonQuery()
End If
objConnection.Close()
End Function
Public Function GetConnectionObject(ByVal strPath As String) As Data.OleDb.OleDbConnection
Dim objConnection As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath)
Return objConnection
objConnection.Close()
End Function
Public Function GetReaderObject(ByVal strCommand As String, ByVal strPath As String) As Data.OleDb.OleDbDataReader
Dim objCommand As Data.OleDb.OleDbCommand = GetCommandObject(strCommand, strPath, False)
Dim objReader As Data.OleDb.OleDbDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return objReader
objReader.Close()
End Function
End Class

www.connectionstrings.com show the connection strings for SQLClient:

Standard Security:

"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
- or -
"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"
(booth connection strings produces the same result)

Trusted Connection:

"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
- or -
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
(booth connection strings produces the same result)

(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
Connect via an IP address:

"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Declare the SqlConnection:

C#:
using System.Data.SqlClient;
SqlConnection oSQLConn = new SqlConnection();
oSQLConn.ConnectionString="my connectionstring";
oSQLConn.Open();

VB.NET:
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString="my connectionstring"
oSQLConn.Open()

Which one you use depends upon how you want to login. You can use a username and password, or you can use a Trusted Connection, in which case, the ASPNET user on the Web Server must be a user in SQL Server (this is awkward if you are running SQL Server on a machine other than the Web Server.

Couple of question about SQL 2000

Hi

I've bin using MySQL and now trying to convert it on to SQL 2000 and i've got a couple of questions

1. Ive got a SQL Script of a database generated by MySQL, can i run this script stright into SQL 2000 and if so how do i go about it ??

2. In my MySQL i have a Gender field which data type is Enum ('M','F'). How do i recreate this type of field in SQL 2000.

Thanks1) Go to start programs/microsoft sql server/query analyzer. Connect to your DB and run the code

2) I would use a bit field, a bit field can be 0 or 1, you can handle the translation in the front end.

HTH|||Hi thanks for your help

just to be a pain, if i wanted 3 or 4 options would the field type be varchar and i'd have the sort the options out in my application

is this right

thanks again

Originally posted by rhigdon
1) Go to start programs/microsoft sql server/query analyzer. Connect to your DB and run the code

2) I would use a bit field, a bit field can be 0 or 1, you can handle the translation in the front end.

HTH|||It's not going to compile...you need to determine the differences and develop a migration plan..

Do a google...

http://www.databasejournal.com/features/mssql/article.php/3087841