Best Practices (Forms/Letters)

I have a rather large number of already existing forms and letters (in paper
format) that I need to recreate in Reporting Services. After reading a few
posts with regards to formatting etc I have noted that several recommend not
using Text Boxes/Labels but rather tables.
As I'm very new to RS could somebody please advise as to the best possible
way to go about creating these.
Often there is a few lines of text then something that needs to be populated
from the database then another few lines of text etc.
Any help on this would be greatly appreciated.On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
<NatJohnson@.discussions.microsoft.com> wrote:
>I have a rather large number of already existing forms and letters (in paper
>format) that I need to recreate in Reporting Services. After reading a few
>posts with regards to formatting etc I have noted that several recommend not
>using Text Boxes/Labels but rather tables.
>As I'm very new to RS could somebody please advise as to the best possible
>way to go about creating these.
>Often there is a few lines of text then something that needs to be populated
>from the database then another few lines of text etc.
>Any help on this would be greatly appreciated.
>
Nat,
Can you explain more about what you are trying to do (ignoring what
the appropriate software might or might not be).
On the face of it, I suspect that Reporting Services may not be the
optimal solution. But I would prefer to better understand your
objective. Forms, for example, in my terminology are used to collect
data from users. I assume that is not part of your aim?
Andrew Watt
MVP - InfoPath|||The company I work for are creating an application which includes an SQL
database. I have to use RS for the reports that are required. Most of the
reports are the traditional style (eg lineflow looking) but others are
letters or forms that the system will automatically print out if a certain
condition is met.
The forms that will be printed will have parts that will be populated from
the database. The web team for the project are creating the online forms. I
have to make my forms similar to theirs for printing out as once the parts
are populated from db they need to be printed so a user can take them to a
site for hand completion.
Most of the letters that need to be printed out need to get data from the
db. And will be automatically printed by the system when a condition is met.
The normal reports that need to be created are not the problem. My question
really relates to the forms and letters and the formatting of these.
Let me know if this helps you anymore or not.
cheers
Nat
"Andrew Watt [MVP - InfoPath]" wrote:
> On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
> <NatJohnson@.discussions.microsoft.com> wrote:
> >I have a rather large number of already existing forms and letters (in paper
> >format) that I need to recreate in Reporting Services. After reading a few
> >posts with regards to formatting etc I have noted that several recommend not
> >using Text Boxes/Labels but rather tables.
> >
> >As I'm very new to RS could somebody please advise as to the best possible
> >way to go about creating these.
> >
> >Often there is a few lines of text then something that needs to be populated
> >from the database then another few lines of text etc.
> >
> >Any help on this would be greatly appreciated.
> >
> Nat,
> Can you explain more about what you are trying to do (ignoring what
> the appropriate software might or might not be).
> On the face of it, I suspect that Reporting Services may not be the
> optimal solution. But I would prefer to better understand your
> objective. Forms, for example, in my terminology are used to collect
> data from users. I assume that is not part of your aim?
> Andrew Watt
> MVP - InfoPath
>|||You should be able to render to PDF and print the PDF documents.
If the documents are to be completed by hand that ought to work for
you.
Almost certainly there will be some tweaking involved to get the
appearance that everybody is happy with.
Andrew Watt
MVP - InfoPath
On Mon, 4 Apr 2005 13:05:03 -0700, "Nat Johnson"
<NatJohnson@.discussions.microsoft.com> wrote:
>The company I work for are creating an application which includes an SQL
>database. I have to use RS for the reports that are required. Most of the
>reports are the traditional style (eg lineflow looking) but others are
>letters or forms that the system will automatically print out if a certain
>condition is met.
>The forms that will be printed will have parts that will be populated from
>the database. The web team for the project are creating the online forms. I
>have to make my forms similar to theirs for printing out as once the parts
>are populated from db they need to be printed so a user can take them to a
>site for hand completion.
>Most of the letters that need to be printed out need to get data from the
>db. And will be automatically printed by the system when a condition is met.
>The normal reports that need to be created are not the problem. My question
>really relates to the forms and letters and the formatting of these.
>Let me know if this helps you anymore or not.
>cheers
>Nat
>"Andrew Watt [MVP - InfoPath]" wrote:
>> On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
>> <NatJohnson@.discussions.microsoft.com> wrote:
>> >I have a rather large number of already existing forms and letters (in paper
>> >format) that I need to recreate in Reporting Services. After reading a few
>> >posts with regards to formatting etc I have noted that several recommend not
>> >using Text Boxes/Labels but rather tables.
>> >
>> >As I'm very new to RS could somebody please advise as to the best possible
>> >way to go about creating these.
>> >
>> >Often there is a few lines of text then something that needs to be populated
>> >from the database then another few lines of text etc.
>> >
>> >Any help on this would be greatly appreciated.
>> >
>> Nat,
>> Can you explain more about what you are trying to do (ignoring what
>> the appropriate software might or might not be).
>> On the face of it, I suspect that Reporting Services may not be the
>> optimal solution. But I would prefer to better understand your
>> objective. Forms, for example, in my terminology are used to collect
>> data from users. I assume that is not part of your aim?
>> Andrew Watt
>> MVP - InfoPath

Best image format to use?

I wan to insert our company logo on some of the srs reports, could someone
recommend the best format to use? I want the image to look clean when
printed as well.On May 14, 5:11 pm, Chris Patten
<ChrisPat...@.discussions.microsoft.com> wrote:
> I wan to insert our company logo on some of the srs reports, could someone
> recommend the best format to use? I want the image to look clean when
> printed as well.
Usually JPEG/JPG and GIF are the best to use due to smaller size (in
comparison to MPEG, etc).
Regards,
Enrique Martinez
Sr. Software Consultant|||I've had problems with gifs, especially on pdf exporting. jpeg seems
to work passably.

Best image format for screen & print?

We are having real problems getting external report (from a URL) images of a
good quality consistently for both screen and PDF print.
Can anyone advise as to the best image format, sizing option, resolution,
etc. for SQL 2K reporting services SP1?
Thanks,
ChrisChris,
I find it best to design to the printed page size.
I'm using A4 landscape generally, I guess it would apply equally well
to US Letter.
I set the page width to 29.7cm, page height to 21cm - you need to make
sure this is set right so that the PDF document is paginated correctly.
I use margins of 0.5cm all round (NB:Some printers may not be able to
handle margins this small all the way round).
The report body is then set to 28.7cm (page width - left & right
margin). If you ever make this wider, i.e. by increasing the width of a
table beyond this, it will start splitting onto 2 pages in PDF. The
report body MUST fit inside the printable width, this is the common
cause of blank pages printing out.
I have found that these reports translate well for HTML display on a
1024x768 res. screen. The only caveat is the page header scrolling off
the screen. If you limit the number of rows per page to suit the screen
display, you'll end up with loads of empty space on the printed pages!
Hope that helps,
Chris.
Chris Lewis wrote:
> We are having real problems getting external report (from a URL)
> images of a good quality consistently for both screen and PDF print.
> Can anyone advise as to the best image format, sizing option,
> resolution, etc. for SQL 2K reporting services SP1?
> Thanks,
> Chris|||Thanks Chris.
Can you advise on the types of images that you embed or reference in your
reports - jpegs, gifs, etc. This is what we are having real difficulty
with - our images either look good on screen (HTML) *or* in PDF - we don't
seem to be able to get both to look good with the same image?
Thanks,
Chris
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uCbIRZoEFHA.3992@.tk2msftngp13.phx.gbl...
> Chris,
> I find it best to design to the printed page size.
> I'm using A4 landscape generally, I guess it would apply equally well
> to US Letter.
> I set the page width to 29.7cm, page height to 21cm - you need to make
> sure this is set right so that the PDF document is paginated correctly.
> I use margins of 0.5cm all round (NB:Some printers may not be able to
> handle margins this small all the way round).
> The report body is then set to 28.7cm (page width - left & right
> margin). If you ever make this wider, i.e. by increasing the width of a
> table beyond this, it will start splitting onto 2 pages in PDF. The
> report body MUST fit inside the printable width, this is the common
> cause of blank pages printing out.
> I have found that these reports translate well for HTML display on a
> 1024x768 res. screen. The only caveat is the page header scrolling off
> the screen. If you limit the number of rows per page to suit the screen
> display, you'll end up with loads of empty space on the printed pages!
> Hope that helps,
> Chris.
> Chris Lewis wrote:
> > We are having real problems getting external report (from a URL)
> > images of a good quality consistently for both screen and PDF print.
> >
> > Can anyone advise as to the best image format, sizing option,
> > resolution, etc. for SQL 2K reporting services SP1?
> >
> > Thanks,
> >
> > Chris
>|||I find GIFs seem to scale better, but if your images are predominately
photographic JPEG will probably perform better. I design them so that
they are shown at their native size on the screen which means they are
normally scaled up a bit on PDF. The trick is to avoid images being
scaled up heavily from their original size. This might mean larger
image files, but you don't get something for nothing!
I'm generally using images as backgrounds, but if you're using the
image control then avoid setting the 'Sizing' property to 'Fit' or 'Fit
Proportional' use 'AutoSize' or 'Clip'. That way you avoid rescaling.
If that's not possible then always use 'Fit Proportional'.
Regards,
Chris.
Chris Lewis wrote:
> Thanks Chris.
> Can you advise on the types of images that you embed or reference in
> your reports - jpegs, gifs, etc. This is what we are having real
> difficulty with - our images either look good on screen (HTML) or in
> PDF - we don't seem to be able to get both to look good with the same
> image?
> Thanks,
> Chris
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:uCbIRZoEFHA.3992@.tk2msftngp13.phx.gbl...
> > Chris,
> > I find it best to design to the printed page size.
> > I'm using A4 landscape generally, I guess it would apply equally
> > well to US Letter.
> > I set the page width to 29.7cm, page height to 21cm - you need to
> > make sure this is set right so that the PDF document is paginated
> > correctly.
> >
> > I use margins of 0.5cm all round (NB:Some printers may not be able
> > to handle margins this small all the way round).
> > The report body is then set to 28.7cm (page width - left & right
> > margin). If you ever make this wider, i.e. by increasing the width
> > of a table beyond this, it will start splitting onto 2 pages in
> > PDF. The report body MUST fit inside the printable width, this is
> > the common cause of blank pages printing out.
> >
> > I have found that these reports translate well for HTML display on a
> > 1024x768 res. screen. The only caveat is the page header scrolling
> > off the screen. If you limit the number of rows per page to suit
> > the screen display, you'll end up with loads of empty space on the
> > printed pages!
> >
> > Hope that helps,
> >
> > Chris.
> >
> > Chris Lewis wrote:
> >
> > > We are having real problems getting external report (from a URL)
> > > images of a good quality consistently for both screen and PDF
> > > print.
> > >
> > > Can anyone advise as to the best image format, sizing option,
> > > resolution, etc. for SQL 2K reporting services SP1?
> > >
> > > Thanks,
> > >
> > > Chris
> >|||Thanks again Chris, I'll go away and try out your suggestions.
Chris
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:#YOJZwoEFHA.1392@.tk2msftngp13.phx.gbl...
> I find GIFs seem to scale better, but if your images are predominately
> photographic JPEG will probably perform better. I design them so that
> they are shown at their native size on the screen which means they are
> normally scaled up a bit on PDF. The trick is to avoid images being
> scaled up heavily from their original size. This might mean larger
> image files, but you don't get something for nothing!
> I'm generally using images as backgrounds, but if you're using the
> image control then avoid setting the 'Sizing' property to 'Fit' or 'Fit
> Proportional' use 'AutoSize' or 'Clip'. That way you avoid rescaling.
> If that's not possible then always use 'Fit Proportional'.
> Regards,
> Chris.
> Chris Lewis wrote:
> > Thanks Chris.
> >
> > Can you advise on the types of images that you embed or reference in
> > your reports - jpegs, gifs, etc. This is what we are having real
> > difficulty with - our images either look good on screen (HTML) or in
> > PDF - we don't seem to be able to get both to look good with the same
> > image?
> >
> > Thanks,
> >
> > Chris
> >
> > "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> > news:uCbIRZoEFHA.3992@.tk2msftngp13.phx.gbl...
> > > Chris,
> > > I find it best to design to the printed page size.
> > > I'm using A4 landscape generally, I guess it would apply equally
> > > well to US Letter.
> > > I set the page width to 29.7cm, page height to 21cm - you need to
> > > make sure this is set right so that the PDF document is paginated
> > > correctly.
> > >
> > > I use margins of 0.5cm all round (NB:Some printers may not be able
> > > to handle margins this small all the way round).
> > > The report body is then set to 28.7cm (page width - left & right
> > > margin). If you ever make this wider, i.e. by increasing the width
> > > of a table beyond this, it will start splitting onto 2 pages in
> > > PDF. The report body MUST fit inside the printable width, this is
> > > the common cause of blank pages printing out.
> > >
> > > I have found that these reports translate well for HTML display on a
> > > 1024x768 res. screen. The only caveat is the page header scrolling
> > > off the screen. If you limit the number of rows per page to suit
> > > the screen display, you'll end up with loads of empty space on the
> > > printed pages!
> > >
> > > Hope that helps,
> > >
> > > Chris.
> > >
> > > Chris Lewis wrote:
> > >
> > > > We are having real problems getting external report (from a URL)
> > > > images of a good quality consistently for both screen and PDF
> > > > print.
> > > >
> > > > Can anyone advise as to the best image format, sizing option,
> > > > resolution, etc. for SQL 2K reporting services SP1?
> > > >
> > > > Thanks,
> > > >
> > > > Chris
> > >
>

Best Export format for long term compatibility

We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
Thanks
Delimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks
|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:

> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>
|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:

Best Export format for long term compatibility

We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> > We have a couple of databases about 5G each and around 300 tables in each
> > databases.We have to keep a yealry backup for 6 years. Also we might be
> > converting to Oracle in about 2 years. If we want to store the SQL Server
> > tables information in a compatible format that would be restorable in a
> > couple of years, what would be the recommended Export format ?
> > Thanks
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>> Delimited ASCII text is just about the only format that never seems to go
>> out of style...
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
>> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>> > We have a couple of databases about 5G each and around 300 tables in
>> > each
>> > databases.We have to keep a yealry backup for 6 years. Also we might be
>> > converting to Oracle in about 2 years. If we want to store the SQL
>> > Server
>> > tables information in a compatible format that would be restorable in a
>> > couple of years, what would be the recommended Export format ?
>> > Thanks

Best Export format for long term compatibility

We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:

> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>

best datatype to save password

Hi,

what is the best datatype to save user's passwordin an encrypted format? Is there any ready datatype for that or i have to send the password enrypted to the database?

Thanks..

If you send it in clear text to the server and then checks for it anyone that has access to the connection between your application and server would be able to have a look at the password. The best practive would be to use a recognized one way hashing algorithm and send only that over the connection.

This way it will be up to your application to hash and match password and snooping on the line will be less interesting for hackers.

|||

dose this mean SQL Server dosen't have a ready encrypted datatype?

if yes, what would be the best way to encrypt if i am using C#?

thanks.

|||

SHA256 would probably be stronger than most expect.
http://msdn2.microsoft.com/en-us/library/system.security.cryptography.sha256.aspx

Store it as varbinary(32)

|||

An SHA256 hash (or any hash, for that matter) of the password alone is completely insecure unless strong passwords or pass phrases are used. Although SHA256 is technically a "one-way" transformation, in reality, it is easy to decode if the plain text is just a word. All that's necessary is to search for the hashed password in a dictionary of the SHA256 hashes of the million most common words. SHA256 is a reasonable choice as a message digest to signal unauthorized changes to the message, but it is not intended or useful for encoding single words. Steve Kass Drew University Andreas Johansson@.discussions.microsoft.com wrote:
> SHA256 would probably be stronger than most expect.
> http://msdn2.microsoft.com/en-us/library/system.security.cryptography.sh
> a256.aspx
>
> Store it as varbinary(32)
>
>

|||

Can nothing but agree, it is important to use strong passwords.

http://en.wikipedia.org/wiki/Password_strength

Best Data type to hole phonenumber

Whats the best type to hold a phone number in this format with a column in my sql table...should i use varchar? or something else....any help would be great!!

Is this phone number US-only or is it international?

Thanks

Waseem

|||its only US|||

Take a look at this example

http://www.rampant-books.com/t_super_sql_128_user-defined_data_types.htm

Hope this helps

Being DateTime Formats aware when accessing database

Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.
I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.
|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.
|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegr oups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>
|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.

> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegro ups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>
|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.
|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,
|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegr oups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>

Being DateTime Formats aware when accessing database

Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.

> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/in...e.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of adva
ntages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>

Being DateTime Formats aware when accessing database

Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many Thanks
On Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed
|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegro ups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx