converting default check constraint messages to friendly ones

I'm trying to set up a table that will convert default SQL error messages
for check constraints into friendly front end messages. What I'm having
trouble with is how to pick apart the default message so I can do this. Is
the error stored anywhere that I can look at it's parts? Any suggestions on
a good strategy for this sort of thing would be appreciated.
Thanks,
KeithFriendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e80rgIRTFHA.1404@.TK2MSFTNGP09.phx.gbl...
> I'm trying to set up a table that will convert default SQL error messages
> for check constraints into friendly front end messages. What I'm having
> trouble with is how to pick apart the default message so I can do this. Is
> the error stored anywhere that I can look at it's parts? Any suggestions
> on
> a good strategy for this sort of thing would be appreciated.
> Thanks,
> Keith
>|||Thanks, OJ. But what code? I know how to do that in stored procedures but in
Check Constraints, where is there a code? I created a constriant that
prevents zero length strings on a column in a table as follows:
Len(LTrim(RTrim(CustName))) > 0
And the message that is returned is:
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK_tblCustomers_CustName'. The conflict occurred in database 'WidgetsInc',
table 'tblCustomers', column 'CustName'.
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'
If I can always be guaranteed that the message will start with "Database
error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'" then I suuppose I can parse it out. But I was hoping there
would be an easier way to do it.
Keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:OKznpORTFHA.2548@.TK2MSFTNGP14.phx.gbl...
Friendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj|||Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%235ONvURTFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Thanks, OJ. But what code? I know how to do that in stored procedures but
> in
> Check Constraints, where is there a code? I created a constriant that
> prevents zero length strings on a column in a table as follows:
> Len(LTrim(RTrim(CustName))) > 0
> And the message that is returned is:
> Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
> statement conflicted with COLUMN CHECK constraint
> 'CK_tblCustomers_CustName'. The conflict occurred in database
> 'WidgetsInc',
> table 'tblCustomers', column 'CustName'.
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'
> If I can always be guaranteed that the message will start with "Database
> error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'" then I suuppose I can parse it out. But I was hoping there
> would be an easier way to do it.
> Keith
>|||Thanks, OJ. No, I did understand. Just that there's no error number
returned to the client side (using Delphi). Just the text I showed you
below. I'll investigate this further in the Borland NGs. Thanks
again. -keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:egat8fRTFHA.2908@.TK2MSFTNGP10.phx.gbl...
Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj

Converting Datetime to Date

I need to convert a datetime, coming from a database table, to a date. How
do I do this within my report?
--
DonIs this just for display or do you really want to convert to date for further
manipulation as a date?
If it is only for display, use =format(Fields!MyDateField.Value,
"MMM-dd-yyyy") or something like it.
If it is for further manipulation as a date, use
=CDate(Fields!MyDateField.Value)
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> I need to convert a datetime, coming from a database table, to a date. How
> do I do this within my report?
> --
> Don|||This is just for display. I have a related problem. In this report I have
multiple datasets. With the multiple datasets If I drag a dataset field onto
the report designer and look at the default expression for the field it looks
like this:
=First(Fields!DateSigned.Value, "AppDetailDS")
The "AppDetailDS" being the dataset name.
I tryed modifying 2 different ways as follows:
=Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
=Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
I cant seem to get the syntax correct when modifying the expression. I get
errors when I try to preview.
Also, I have tried using immediate if's(iif) and cant get that to work. I
have no problem in a report with only one dataset.
Any thoughts.
"Charles Kangai" wrote:
> Is this just for display or do you really want to convert to date for further
> manipulation as a date?
> If it is only for display, use =format(Fields!MyDateField.Value,
> "MMM-dd-yyyy") or something like it.
> If it is for further manipulation as a date, use
> =CDate(Fields!MyDateField.Value)
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Don" wrote:
> > I need to convert a datetime, coming from a database table, to a date. How
> > do I do this within my report?
> >
> > --
> > Don|||Remove the "AppDetailDS" from your Format formula below, then capitalize the
"mmm" to "MMM". You only need two parameters for the Format function.
You should just have
=Format(Fields!DateSigned.Value,"MM-dd-yyyy")
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> This is just for display. I have a related problem. In this report I have
> multiple datasets. With the multiple datasets If I drag a dataset field onto
> the report designer and look at the default expression for the field it looks
> like this:
> =First(Fields!DateSigned.Value, "AppDetailDS")
> The "AppDetailDS" being the dataset name.
> I tryed modifying 2 different ways as follows:
> =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> I cant seem to get the syntax correct when modifying the expression. I get
> errors when I try to preview.
> Also, I have tried using immediate if's(iif) and cant get that to work. I
> have no problem in a report with only one dataset.
> Any thoughts.
> "Charles Kangai" wrote:
> > Is this just for display or do you really want to convert to date for further
> > manipulation as a date?
> >
> > If it is only for display, use =format(Fields!MyDateField.Value,
> > "MMM-dd-yyyy") or something like it.
> >
> > If it is for further manipulation as a date, use
> > =CDate(Fields!MyDateField.Value)
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> >
> > "Don" wrote:
> >
> > > I need to convert a datetime, coming from a database table, to a date. How
> > > do I do this within my report?
> > >
> > > --
> > > Don|||If I remove the "AppDetailDS", When I preview, I get an error: "the value
expression for the textbox 'DateSigned' uses an aggregate expression without
a scope. A scope is required for all aggregates use outside of a data region
unless the report contains exactly one data set.
"Charles Kangai" wrote:
> Remove the "AppDetailDS" from your Format formula below, then capitalize the
> "mmm" to "MMM". You only need two parameters for the Format function.
> You should just have
> =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> HTH
> Charles Kangai, MCT, MCDBA
> "Don" wrote:
> > This is just for display. I have a related problem. In this report I have
> > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > the report designer and look at the default expression for the field it looks
> > like this:
> > =First(Fields!DateSigned.Value, "AppDetailDS")
> > The "AppDetailDS" being the dataset name.
> >
> > I tryed modifying 2 different ways as follows:
> > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> >
> > I cant seem to get the syntax correct when modifying the expression. I get
> > errors when I try to preview.
> >
> > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > have no problem in a report with only one dataset.
> >
> > Any thoughts.
> >
> > "Charles Kangai" wrote:
> >
> > > Is this just for display or do you really want to convert to date for further
> > > manipulation as a date?
> > >
> > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > "MMM-dd-yyyy") or something like it.
> > >
> > > If it is for further manipulation as a date, use
> > > =CDate(Fields!MyDateField.Value)
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > >
> > >
> > > "Don" wrote:
> > >
> > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > do I do this within my report?
> > > >
> > > > --
> > > > Don|||Try to not put textboxes outside of data regions. My suggestion is that you
use containers such as list or table data region to put your textboxes in.
The Format function does not have a scope parameter, so it should work. The
First function you are using is an aggregate function, so it may need a scope
parameter.
But the first thing you need to do is to place a list or table data region
on your screen. Bind it to a dataset using the Properties dialog, then add
textboxes inside of it.
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> If I remove the "AppDetailDS", When I preview, I get an error: "the value
> expression for the textbox 'DateSigned' uses an aggregate expression without
> a scope. A scope is required for all aggregates use outside of a data region
> unless the report contains exactly one data set.
> "Charles Kangai" wrote:
> > Remove the "AppDetailDS" from your Format formula below, then capitalize the
> > "mmm" to "MMM". You only need two parameters for the Format function.
> > You should just have
> > =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Don" wrote:
> >
> > > This is just for display. I have a related problem. In this report I have
> > > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > > the report designer and look at the default expression for the field it looks
> > > like this:
> > > =First(Fields!DateSigned.Value, "AppDetailDS")
> > > The "AppDetailDS" being the dataset name.
> > >
> > > I tryed modifying 2 different ways as follows:
> > > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> > >
> > > I cant seem to get the syntax correct when modifying the expression. I get
> > > errors when I try to preview.
> > >
> > > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > > have no problem in a report with only one dataset.
> > >
> > > Any thoughts.
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > Is this just for display or do you really want to convert to date for further
> > > > manipulation as a date?
> > > >
> > > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > > "MMM-dd-yyyy") or something like it.
> > > >
> > > > If it is for further manipulation as a date, use
> > > > =CDate(Fields!MyDateField.Value)
> > > >
> > > > HTH
> > > >
> > > > Charles Kangai, MCT, MCDBA
> > > >
> > > >
> > > >
> > > > "Don" wrote:
> > > >
> > > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > > do I do this within my report?
> > > > >
> > > > > --
> > > > > Don

converting datetime formats and layouts?

Opening DimTime table of AdventureWorksDW sample database in MS SQL Server Management Studio shows me values in FullDateAlternatKey like

01.07.2001 0:00:00

select fulldatealternatekey from dimtime
gives me the results like
2001-07-01 00:00:00.000

(1) Why is it?
(2)How can I "SELECT fulldatealternatekey" in different format like 9/3/2001 0:00
(that is given in SampleCurrencyData.txt ?
Well, I am trying to follow Integration Service Tutorial, Lesson1 (SQL Server 2005 Books Online) and have yet another format of datetime like 9/3/2001 0:00 in SampleCurrencyData.txt and receive the type mismatch in part "To add and configure the DateKey Lookup transformation" (8.In the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey.)

Flat File Connection Manager Editor --> Advanced --> DataType gives me following formats:
- file_timestamp [DT_FILETIME]
- database date [DT_DBDATE]
- database time [DT_DBTIME]
- database timestamp [DT_DBTIMESTAMP]
- date [DT_DATE]
- file timestamp [DT_FILETIME]

(3)
BOL2005 give me general description of these date-times but how do I govern separation delimitoers in them (., /, -)?

That is the recommended way to store dates in SQL Server. The best way to represent dates in your application is to extract them as-is from the database, and format them using the application language's built-in string formatting functions. Here is a list of them for C# -http://blog.stevex.net/index.php/string-formatting-in-csharp/|||

Really, my problem has nothing to do with C# or storing dates by ME (?!) since I am trying to reproduce SSIS tutorial (as I mentioned it more specifically above).

I found some related posts to my problem:
http://forums.asp.net/thread/1688990.aspx
converting datetime formats and layouts?

ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

Though neither of tholutions helped me!

|||I see that I cited this same post.
Instead, one more post that describes the same problem bnut had not helped me is

collation or local sensitive settings or other configuration properties
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180740&SiteID=1

Converting dates.

Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?

It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)

i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?

SELECT * FROM Table WHERE ISDATE(DateCol)=0

??

SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return

what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2

mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed

unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...

:)|||sounds like the attitude of a certain large software company which shall remain nameless...

yea, they drill it into you, it takes a while to feel clean again. :)

did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.

How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.

I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.

There are few ways to update your DATETIME columns...

1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.

Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).

By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.

converting date/time to just date?

I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).

What I want is to do the following:

Say you have these records:

person | date-time
---+--------
jim | 06/02/2004 00:05:52
jim | 06/02/2004 05:06:21
jim | 06/02/2004 05:46:21
jim | 06/15/2004 11:26:21
jim | 06/15/2004 11:35:21
dave | 06/04/2004 09:35:21
dave | 06/04/2004 11:05:21
dave | 06/06/2004 10:34:21
dave | 06/08/2004 11:37:21

I'd like the results to count how many days and return

person | days
---+---
jim | 2
dave | 3

How would I do this?

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@.12jabber.com <--jabber IM ]On Tue, 22 Jun 2004 15:27:52 -0000, Sugapablo wrote:

>I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).
>What I want is to do the following:
>Say you have these records:
>person | date-time
>---+--------
>jim | 06/02/2004 00:05:52
>jim | 06/02/2004 05:06:21
>jim | 06/02/2004 05:46:21
>jim | 06/15/2004 11:26:21
>jim | 06/15/2004 11:35:21
>dave | 06/04/2004 09:35:21
>dave | 06/04/2004 11:05:21
>dave | 06/06/2004 10:34:21
>dave | 06/08/2004 11:37:21
>I'd like the results to count how many days and return
>person | days
>---+---
>jim | 2
>dave | 3
>How would I do this?

Hi Sugapablo,

SELECT person,
COUNT(DISTINCT CONVERT(CHAR(8), date-time, 114)) AS days
FROM YourTable
GROUP BY person
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting date to Varchar? and Varchar to Date?

I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'

What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?

I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar

This should give you an idea of how to handle these conversions:

DECLARE @.MyDateTimeValue datetime
SET @.MyDateTimeValue = '2006-03-26 00:00:00.000'

SELECT convert( varchar(10), @.MyDateTimeValue, 101 )

-
03/26/2006

SELECT cast( '03/26/2006' AS datetime )

2006-03-26 00:00:00.000

Converting DataSet to new SQL Table

Hello Everyone,

I have a dataset that I created from an external database using an ODBC connection. I would like to take that dataset and create a new table in an SQL 2005 database. Can anyone point me in the right direction. The problem that I am having right now is getting the object types etc.

Thank you!!

The first method is to use ExecuteNoneQuery in ADO.NET to create you table, try the links below for code samples. Hope this helps.

http://forums.asp.net/thread/1385209.aspx

http://www.functionx.com/csharp/adonet/Lesson04.htm

Converting Data Types

I have a table with a field of Char(10) Data type

this field contains records of work time Attendance in a decimal format

Ex. I attend today for 8.30 this means eighthours and thirty mintutes

the main problem faced me to make some calculations on those records (sum, subtract, etc)

so I want to convert the data from char type to decimal or real using the next code but it doesn't work

select (cast (satreg, decimal) + cast (satot, decimal)) from timecard

can u please help me in the main issue how to sum char type records or at least how to convert them to decimal

Regards

create table test

(

time char(10)

)

insert into test (time) values ('8.30')

insert into test (time) values ('9.30')

insert into test (time) values ('10.30')

selectconvert(decimal(10,2), time )

fromtest|||

this can help:

it breaks your 8.30 to 8 and 30

SELECT substring(test,0, (patindex('%.%',test))),substring(test,(patindex('%.%',test)+1),len(test)) from test

Converting Data

Hi
I am converting data from old DB to NEW DB
In the OLD table fields like "PhoneNumber" the data enterd are [ 657 985-986, (03)-987-543, 675(89)00, ect]
Is their any function in sql where I can get rid of all those spaces and () and - between the numbers as my new field is only numbers and with out space
Otherwise I have to clean them up manually as I have 1000000 records

cheers

hi koese,

as far as i know there's no direct function to help you out during migration or converting. an alternative i think of is first you let your column type to be varchar in new db.

then after migrating run a update i know it will take time but that will definately work.

then you can use a command likeSELECT REPLACE('abcdefghicde','cde','xxx')

thanks,

satish.

sqlsql

Converting Data

Good Day,
I am new SQL Server 2000 and have a dbase III application to convert. I am trying to import data from dbase III to a SQL table but I don't want <NULL> to show in the field. I am having trouble with the ALTER TABLE command.

Can some tell me how to import dbase III data with NULL or how I can alter the columns data to remove NULL?

Any suggestions would be greatly appreciated.

Regards,
RNettlesYou can import dbase III data many ways. It depends on the level of control that you need and coding for example. Here are some options to consider:

1. Use a Data Transformation Services package (Import/Export Wizard)
2. Define a linked server for the dbase III data and use heterogeneous queries from TSQL directly
3. Export data from dbase to text files and use bcp or BULK INSERT

As for changing the values from NULL, you can update them in SQL by choosing some appropriate default value. But the real question is if you want to model unknown value in your schema. This depends on the functionality that you require. You can use UPDATE to change NULL values:

update tbl
set col1 = 0
where col1 is null;

converting boolean to bit

I have a control with checkboxes. The checkbox.checked property returns a boolean value. If I want to insert a record into a table that has a corresponding column of type bit, how do I do it?

I tried

CType(myChkBox.checked,String)

, which returns the strings "True" or "False". But, I get the error

The name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

I then tried

"CAST(" & CType(myChkBox.checked,String) & ",bit)"

, but got the same error.

I can write a function that will return a 1 or a 0 but that seems ludicrous. Surely there must be a more elegant way to use the result of a checkbox in an SQL statement.

Thanks
Martindoes this work ?

IIf(CheckBox1.Checked = True, 1, 0)

|||There is, you should be using parameters not on-the-fly sql strings.|||pkr, I agree that I should be using parameters, and I will get around to it before I finalise this control. I am not in front of my code at the moment, do you think that

dim prmChecked as new SqlParameter("@.myParam",SqlDbType.Bit)
prmChecked = myChkBox.checked

will work? According to the doco, the SqlDataType Bit takes an unsigned integer of 1 or 0. So, I would still have the same problem.

ndinaker, thanks for your suggestion. Is this the way its usually done?

Martin|||The checked flag is a boolean. Even *if* it doesn't automatically convert boolean to bit, it's trivial to convert boolean to int.

Converting Binary Image to Readable Text

I have a table that contains the following two columns:

BITS (image(16))
BIT_LENGTH (int(4))

When I look at the table, I see "OLE Object" in the BITS column. What
syntax should I use in a SELECT statement to convert the binary image
info contained in "BITS" into simple text that I can read? What role
does the BIT_LENGTH field play?An image column can store any binary data, so you need to know what the
data represents before you can display it - it could be a Word
document, a PDF, an MP3 etc. Do you already know exactly what the
column is storing ('text' is the normal data type for large amounts of
text data)? As for BIT_LENGTH, it could mean anything, depending on the
data model - there's no need for an image column to have an associated
length column with it.

It sounds a little like you've just taken over a database from someone
else? If so, you might want to review this chapter from the SQL2000
Resource Kit, which provides more detailed information about storing
and retrieving BLOBs:

http://www.microsoft.com/technet/pr...art3/c1161.mspx

Simon

Converting an integer field into an Identity field

I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?

TIA,

Barkingdog

There is no TSQL statement to change a non-identity field to an identity field, even the designer will do strange things behind the scenes, like creating a new table copying the data to the new one, renaming the new and dropping the old table. SO you either do the same in your TSQL statements or use the gUI which does all the steps for you behind the scenes.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

converting access to sql

is there any quick n easy way to convert an old access database to sql
yes i know it sounds easy and it isnt!
the access table was poorly designed - eg 1 table 50-60 fields
i need to transfer the data to sql - which has been redesigned with multi
tables - any easy way to do this ?
thanks
mark
Hi,
USE DTS tools in sql server to do this.
In DTS you can create multiple transformations to transfer the data in one table in MS access to multiple normalized table in SQL Server.
Otherwise move the data into SQL server single table and then write DMLS to transfer to multiple tables.
Thanks
Hari
MCDBA
-- mark wrote: --
is there any quick n easy way to convert an old access database to sql
yes i know it sounds easy and it isnt!
the access table was poorly designed - eg 1 table 50-60 fields
i need to transfer the data to sql - which has been redesigned with multi
tables - any easy way to do this ?
thanks
mark
|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
> Hi,
> USE DTS tools in sql server to do this.
> In DTS you can create multiple transformations to transfer the data in one
table in MS access to multiple normalized table in SQL Server.
> Otherwise move the data into SQL server single table and then write DMLS
to transfer to multiple tables.
> Thanks
> Hari
> MCDBA
> -- mark wrote: --
>
what if the column names are slightly different ? - can i import into a
table that already exists ?
thanks
mark
|||Hi,
In the transformation you can map the source coulumn with the destination
column.
So if you have emp_number in MS Access and emp_no in SQL server, inside
transformation you can map each other.
Thanks
Hari
MCDBA
"mark" <mark@.remove.com> wrote in message
news:fmImc.74$TO3.69@.newsfe3-win.server.ntli.net...[vbcol=seagreen]
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
one
> table in MS access to multiple normalized table in SQL Server.
> to transfer to multiple tables.
> what if the column names are slightly different ? - can i import into a
> table that already exists ?
> thanks
> mark
>
|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OsH$OiBNEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In the transformation you can map the source coulumn with the destination
> column.
> So if you have emp_number in MS Access and emp_no in SQL server, inside
> transformation you can map each other.
> Thanks
> Hari
> MCDBA
>
thanks ive got it sussed now!
mark

converting access to sql

is there any quick n easy way to convert an old access database to sql
yes i know it sounds easy and it isnt!
the access table was poorly designed - eg 1 table 50-60 fields
i need to transfer the data to sql - which has been redesigned with multi
tables - any easy way to do this ?
thanks
markHi
USE DTS tools in sql server to do this
In DTS you can create multiple transformations to transfer the data in one table in MS access to multiple normalized table in SQL Server.
Otherwise move the data into SQL server single table and then write DMLS to transfer to multiple tables
Thank
Har
MCDB
-- mark wrote: --
is there any quick n easy way to convert an old access database to sq
yes i know it sounds easy and it isnt
the access table was poorly designed - eg 1 table 50-60 field
i need to transfer the data to sql - which has been redesigned with mult
tables - any easy way to do this
thank
mar|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
> Hi,
> USE DTS tools in sql server to do this.
> In DTS you can create multiple transformations to transfer the data in one
table in MS access to multiple normalized table in SQL Server.
> Otherwise move the data into SQL server single table and then write DMLS
to transfer to multiple tables.
> Thanks
> Hari
> MCDBA
> -- mark wrote: --
>
what if the column names are slightly different ? - can i import into a
table that already exists ?
thanks
mark|||Hi,
In the transformation you can map the source coulumn with the destination
column.
So if you have emp_number in MS Access and emp_no in SQL server, inside
transformation you can map each other.
Thanks
Hari
MCDBA
"mark" <mark@.remove.com> wrote in message
news:fmImc.74$TO3.69@.newsfe3-win.server.ntli.net...
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
> >
> > Hi,
> >
> > USE DTS tools in sql server to do this.
> > In DTS you can create multiple transformations to transfer the data in
one
> table in MS access to multiple normalized table in SQL Server.
> >
> > Otherwise move the data into SQL server single table and then write DMLS
> to transfer to multiple tables.
> >
> > Thanks
> > Hari
> > MCDBA
> > -- mark wrote: --
> >
> what if the column names are slightly different ? - can i import into a
> table that already exists ?
> thanks
> mark
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OsH$OiBNEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In the transformation you can map the source coulumn with the destination
> column.
> So if you have emp_number in MS Access and emp_no in SQL server, inside
> transformation you can map each other.
> Thanks
> Hari
> MCDBA
>
thanks ive got it sussed now!
mark

Converting a varchar to int

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.

But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9

A classic violation of first normal form:

http://en.wikipedia.org/wiki/First_..._ single_field
If at all possible, change your table to look like this:

Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

and then this simply becomes

SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)

That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.|||I can't change the actual table, but I can create a stored proc that
inserts it correctly into another table. I didn't even think to do
that (**duh**)! Thank you very much for your assistance!

Ed Murphy wrote:

Quote:

Originally Posted by

Patti wrote:
>

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

>
A classic violation of first normal form:
>
http://en.wikipedia.org/wiki/First_..._ single_field
>
If at all possible, change your table to look like this:
>
Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9
>

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

>
and then this simply becomes
>
SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)
>
That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)
>
SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'
>
Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.

|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Seems like some % are missing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This might work:
SELECT Loan#
FROM Table1
WHERE patindex('%[2,13,1]%',Codes) 0

Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9
>
I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/
>
Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.
>
But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.

|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>SELECT Loan#
>FROM Table1
>WHERE '-'+Codes+'-' like '-2-'
> OR '-'+Codes+'-' like '-13-'
> OR '-'+Codes+'-' like '-1-'

>
Seems like some % are missing.

Yes, of course you're right, should be

WHERE '-'+Codes+'-' like '%-2-%'
OR '-'+Codes+'-' like '%-13-%'
OR '-'+Codes+'-' like '%-1-%'

but the approach of "use a stored procedure to copy the data to a
better-normalized table" is probably better. (Oh, and that new
table should probably have an index on the Code column.)

converting a text field to number

I have a table with over a million rows and one of the fields contains
amounts of money in text format.
What is the most efficient way of converting this field to a number
format that I can sum on?

Regards,
CiarnHi Ciaran,

Before converting the type of the col, make sure that there are no
invalid values in that column. You can pull them out by

SELECT colName FROM tabName WHERE IsNumeric(colName) = 0

Alter your column type by

ALTER TABLE tableName ALTER COLUMN colName NUMERIC

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Nazeer Oasis (nazeerpp@.indiatimes.com) writes:
> Before converting the type of the col, make sure that there are no
> invalid values in that column. You can pull them out by
> SELECT colName FROM tabName WHERE IsNumeric(colName) = 0
> Alter your column type by
> ALTER TABLE tableName ALTER COLUMN colName NUMERIC

Unfortunately, this may still fail, since IsNumeric will approve of
values than converts to float or money, but not to numeric. Also, I
say that it's extremely bad practice to say numeric without specifying
scale and precision. You get some defaults, but these may not be what
you expect.

As for the original query, the easy way is:

SELECT SUM(convert(int, textcol)) FROM tbl
or SELECT SUM(convert(money, textcol)) FROM tbl

But this will of course fail if there are strings that does not convert.

If all data is integer, that is the text is undelimited and there are
no decimals, then it's pretty easy to test:

textcol NOT LIKE '%[0-9]%'

If the text can delimiters and decimals, it can become quite hairy
to filter.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> If all data is integer, that is the text is undelimited and there are
> no decimals, then it's pretty easy to test:
> textcol NOT LIKE '%[0-9]%'

This is wrong. I forgot a ^:

textcol NOT LIKE '%[^0-9]%'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>sqlsql

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?
convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com. ..
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>