Sql Case sensitivity: int

Showing posts with label int. Show all posts

Thursday, March 29, 2012

Change local variable inside query

/*Given*/

CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)

/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.

This doesn't compile of course, but is there
a way to accomplish the same thing?
*/

DECLARE @.ndx int

SET @.ndx = 1

SELECT

(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT

FROM _T1sub a

/*Output would look like this:*/

FKplusWT
----
11
22
33

/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/

thanks, Otto PorterOn Sat, 02 Oct 2004 12:20:48 -0600, Otto Porter wrote:

>I know, I can get this output just by adding
>FK+WT. This is not about that.
>This is about setting vars inside a query

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

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

Thursday, March 22, 2012

change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

Sunday, March 11, 2012

Change condition if the first doesn't exists

Have two tables:

code:

CREATE TABLE [Table1]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Number] [varchar] (50) NOT NULL ,
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
DEFAULT (getdate()),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table2]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Table1Id] [int] NOT NULL ,
[LingoId] [int] NOT NULL ,
[Header] [nvarchar] (150) NOT NULL ,
[Description] [ntext] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
(
[Table1Id]
) REFERENCES [Table1] (
[Id]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

What I would like is that if LingoId = 2 in the query below doesn't exists
than it should fall thru and use the values where LingoId = 1. LingoId = 1
always exists for each Table2.Id. Is this doable?

code:

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = 2) --Something should happen here I quess.

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Header ELSE
T2_1.Header END) as Header,
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Description ELSE
T2_1.Description END) as Description
FROM
dbo.Table1
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 2) T2_2
ON dbo.Table1.Id = T2_2.Table1Id
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 1) T2_1
ON dbo.Table1.Id = T2_1.Table1Id
HTH, Jens Suessmeyer.|||Got an answer elsewhere that did the trick.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||First, you are filtering the result with values from table2. That will
effectively be an INNER JOIN. Did you mean it like that?
If you want to use only one query then something like this could be good
enough:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = case when exists (select table1.id from table1
inner join table2 on table1.id = table2.id where table2.LingoID = 2) then 2
else 1 end)
MC
"Senna" <
Senna@.discussions.microsoft.com>
wrote in message
news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||The solution, as I mention above, looked liked this:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
dbo.Table2.LingoId = 2
or (dbo.Table2.LingoId = 1
and not exists
(select *
from table2 cn
where cn.table1id = dbo.Table2.Table1Id
and cn.lingoid = 2))
ps. Thank for your time and answer Jens.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||Yes, it was an inner join. :)
See the other post with the solution I went with. Thanks anyway for your
time and effort.
"MC" wrote:

> First, you are filtering the result with values from table2. That will
> effectively be an INNER JOIN. Did you mean it like that?
> If you want to use only one query then something like this could be good
> enough:
> SELECT
> dbo.Table1.Id,
> dbo.Table1.Number,
> dbo.Table1.[TimeStamp],
> dbo.Table2.Header,
> dbo.Table2.Description
> FROM
> dbo.Table1
> LEFT OUTER JOIN
> dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
> WHERE
> (dbo.Table2.LingoId = case when exists (select table1.id from table1
> inner join table2 on table1.id = table2.id where table2.LingoID = 2) then
2
> else 1 end)
>
> MC
>
> "Senna" <Senna@.discussions.microsoft.com> wrote in message
> news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
>

Change column from datetime to int

I want to change a column from datetime to int. I'm using this T-SQL:
ALTER TABLE WebPages
ALTER COLUMN EndDate int;
I get this result:
Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type datetime to data type int,
table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
function to run this query.
If I use Enterprise Manager the change happens without error. However, I
need to do this conversion as part of a larger script so I need to do it in
code. Can anyone tell me what EM is doing behind the scenes that allows this
to succeed? Thank you!Hi Ron
Please always state what version you are using.
I assume you are using SQL 2000 since you referred to Enterprise Manager. If
you trace what SQL Server is doing when you change datetime to int in EM,
you will see that it is actually recreating the entire table, selecting from
the old table using convert for the EndDate column, inserting into a new
table, dropping the original table and renaming the new table to the old
name. All indexes and triggers need to be rebuilt. This can be quite a
time-consuming process for a large table, but it is do-able. Just not with a
single statement.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23LP8GFhVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>I want to change a column from datetime to int. I'm using this T-SQL:
> ALTER TABLE WebPages
> ALTER COLUMN EndDate int;
> I get this result:
> Server: Msg 260, Level 16, State 1, Line 1
> Disallowed implicit conversion from data type datetime to data type int,
> table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
> function to run this query.
> If I use Enterprise Manager the change happens without error. However, I
> need to do this conversion as part of a larger script so I need to do it
> in
> code. Can anyone tell me what EM is doing behind the scenes that allows
> this
> to succeed? Thank you!
>
>

Change column from datetime to int

I want to change a column from datetime to int. I'm using this T-SQL:
ALTER TABLE WebPages
ALTER COLUMN EndDate int;
I get this result:
Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type datetime to data type int,
table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
function to run this query.
If I use Enterprise Manager the change happens without error. However, I
need to do this conversion as part of a larger script so I need to do it in
code. Can anyone tell me what EM is doing behind the scenes that allows this
to succeed? Thank you!
Hi Ron
Please always state what version you are using.
I assume you are using SQL 2000 since you referred to Enterprise Manager. If
you trace what SQL Server is doing when you change datetime to int in EM,
you will see that it is actually recreating the entire table, selecting from
the old table using convert for the EndDate column, inserting into a new
table, dropping the original table and renaming the new table to the old
name. All indexes and triggers need to be rebuilt. This can be quite a
time-consuming process for a large table, but it is do-able. Just not with a
single statement.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23LP8GFhVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>I want to change a column from datetime to int. I'm using this T-SQL:
> ALTER TABLE WebPages
> ALTER COLUMN EndDate int;
> I get this result:
> Server: Msg 260, Level 16, State 1, Line 1
> Disallowed implicit conversion from data type datetime to data type int,
> table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
> function to run this query.
> If I use Enterprise Manager the change happens without error. However, I
> need to do this conversion as part of a larger script so I need to do it
> in
> code. Can anyone tell me what EM is doing behind the scenes that allows
> this
> to succeed? Thank you!
>
>

Change column from datetime to int

I want to change a column from datetime to int. I'm using this T-SQL:
ALTER TABLE WebPages
ALTER COLUMN EndDate int;
I get this result:
Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type datetime to data type int,
table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
function to run this query.
If I use Enterprise Manager the change happens without error. However, I
need to do this conversion as part of a larger script so I need to do it in
code. Can anyone tell me what EM is doing behind the scenes that allows this
to succeed? Thank you!Hi Ron
Please always state what version you are using.
I assume you are using SQL 2000 since you referred to Enterprise Manager. If
you trace what SQL Server is doing when you change datetime to int in EM,
you will see that it is actually recreating the entire table, selecting from
the old table using convert for the EndDate column, inserting into a new
table, dropping the original table and renaming the new table to the old
name. All indexes and triggers need to be rebuilt. This can be quite a
time-consuming process for a large table, but it is do-able. Just not with a
single statement.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23LP8GFhVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>I want to change a column from datetime to int. I'm using this T-SQL:
> ALTER TABLE WebPages
> ALTER COLUMN EndDate int;
> I get this result:
> Server: Msg 260, Level 16, State 1, Line 1
> Disallowed implicit conversion from data type datetime to data type int,
> table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
> function to run this query.
> If I use Enterprise Manager the change happens without error. However, I
> need to do this conversion as part of a larger script so I need to do it
> in
> code. Can anyone tell me what EM is doing behind the scenes that allows
> this
> to succeed? Thank you!
>
>

Sunday, February 19, 2012

CDBVariant

Hi

got small problem. I got three rows in a table

Index (int)
Product (varchar)
Price (float)

I could successfully connect to the database. I also get the values and store them in a CDBVariant type. I can't put the values into List Control Box... insertItem always return -1. It ask for LPCTSTR type.

Anyone can help me please, hanging around here know for hours?

Please
Thx

CODE

{
CDBVariant value;
char sql_statement [2048] = "";

//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("DSN=Beauty"),CDatabase::noOdbcDialog);

//CRecordset object "rs" created to access and manipulate database records.
CRecordset rs(&db);
strcpy(sql_statement,_T("SELECT * FROM TestTabelle"));
rs.Open(CRecordset::forwardOnly,sql_statement);

//Get quantity from Database
int n = rs.GetODBCFieldCount( );

while(!rs.IsEOF())
{
for( int i = 0; i < n; i++ )

{

rs.GetFieldValue("index",value);
m_Buy_List.InsertItem(i,LPCTSTR(value.m_lVal)); Won't work at all

rs.GetFieldValue("product",value.m_pstring);
m_Buy_List.SetItemText(i,2,LPCTSTR(value));Getting weird String for example "Soap" and ListBox shows "Aq3"

rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_fltVal) ); Won't accept float

rs.MoveNext( );
}
}Perhaps you would get better results on a C++ forum...

If you know that the values must be strings, and you are retrieving each one explicitly, why not just create three variables (strings), and use those. Seems like it would be a lot easier and less trouble prone.

If you are concerned about NULLs (which you should be), change your SQL statement to use the ISNULL function on each of the fields you are dealing with:

SELECT ISNULL(index,0) AS INDEX, ISNULL(product,'') AS PRODUCT, ISNULL(price,0) AS PRICE FROM TestTabelle|||thx

i figured another way out... getFieldValue isn't accepting strings so i converted them in char :)

weird... someday i will find the official way... i will change it then :)

iam new into c++ and ms sql... learning by doing :)

Thursday, February 16, 2012

Category and Subcategory Problem

Hi everyone, I am having trouble with a particular problem with SQL. I have a table that defines product categories like so:

Id (int)
Text varchar(50)
ParentId (int)

It holds all our categories, with subcategories having the appropriate ParentId relating to the above category. I am trying to write a stored procedure that takes in a single Id, and finds out all the related subcategories and subcategories all the way down the tree. I need to produce a resultset with a single column of Id's of all the subcategories etc

For example if the table had the following records:

8 - General - 1
9 - Academic - 1
10 - Science - 1
11 - History - 8
12 - Maths - 8
13 - English - 9
14 - Spanish - 9
15 - England - 13

So if I was to feed in Id 9 the resulting table that I want is like this

9
13
15

My problem is that there isn't a defined number of subcategory levels. General has only 1 subcategory level, but Academic has 2 subcategory levels.

The only part solution I have found was this:

CREATE TABLE #Categories (
CategoryId int)

insert #Categories (CategoryId)
select Id
from Category as c1
where c1.ParentId=8 or c1.Id=8

however it only brings back the first level of subcategories. I was intending to loop this over and over however because of the inconsistent number of levels I can't put in a predefined number of loops.

I have never really faced a problem like this before. I am pretty new to T-SQL and am not sure if there is an easy way to overcome this, but any help would be very much appreciated. I was pretty much ready to pull out my hair yesterday trying to solve this .

I am using SQL Server Express 2005 on Windows Server 2003

Thanks in advance for any help,

Dylan

Please take a look at the link below:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You can use recursive CTEs in SQL Server 2005 to write the queries. You can encapsulate those in views or inline TVFs.

|||Thanks that worked great, exactly what I needed.

Sunday, February 12, 2012

Casting Int to Varchar

I want to cast an In to varchar with a specific number of decimal places. So 10 will come across as 10.00.
Is there an easy way of doing this?Would you be llooking for something like this?

Code:
------------------------------
select cast(cast(10 as money) as varchar(10))
select cast(cast(10 as numeric(12,4)) as varchar(10))
------------------------------|||I think this is what Paul was trying to show:

select cast(cast(id as decimal(5,2)) as varchar(10)) from table

Where id and table are defined by you. The decimal parameters would be determined by your maximum integer.

Casting char to int but not causing error

Hi. In the where clause of my query I will be converting a char column to an
int to match it to another int column. But the char could contain characters
and not convert to an int. In that case I don't want the query to fail but
simply not do the match. Is that possible?
select * from A, B where cast ( A.CharCol as int ) = B.IntCol
Thanks.
McG

y
[url]http://mcg

y.blogspot.com[/url]Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('441')
INSERT INTO #Test VALUES ('55a')
SELECT CAST(col AS INT) FROM #Test
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '55a' to a column of data type
int.
SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
You can visit at Aaron's web site www.aspfaq.com to find a script as
alternative to ISNUMERIC() function
"McG

y" <anon@.anon.com> wrote in message
news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
> Hi. In the where clause of my query I will be converting a char column to
> an
> int to match it to another int column. But the char could contain
> characters
> and not convert to an int. In that case I don't want the query to fail but
> simply not do the match. Is that possible?
> select * from A, B where cast ( A.CharCol as int ) = B.IntCol
> Thanks.
> --
> McG

y
> [url]http://mcg

y.blogspot.com[/url]
>
>|||Thanks Uri. Unfortunately your example does not work for me. I am not
selecting the char column as an int but rather using it in the where clause
as an int.
Thankfully I have just figured it out myself using the AND clause as
follows - try with A set to 'a' then A set to '1':
declare @.A as char (1)
set @.A = 'a'
select
1
where
( isnumeric ( @.A ) = 1 )
and
( cast ( @.A as int ) = 1 )
McG

y
[url]http://mcg

y.blogspot.com[/url]
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('441')
> INSERT INTO #Test VALUES ('55a')
> SELECT CAST(col AS INT) FROM #Test
> --Server: Msg 245, Level 16, State 1, Line 1
> --Syntax error converting the varchar value '55a' to a column of data
type
> int.
> SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
>
> You can visit at Aaron's web site www.aspfaq.com to find a script as
> alternative to ISNUMERIC() function
>
> "McG

y" <anon@.anon.com> wrote in message
> news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
to
but
>|||Hi
Actually ,I only tried to give you an idea for solving the problem.
"McG

y" <anon@.anon.com> wrote in message
news:eDZ8cA2wFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri. Unfortunately your example does not work for me. I am not
> selecting the char column as an int but rather using it in the where
> clause
> as an int.
> Thankfully I have just figured it out myself using the AND clause as
> follows - try with A set to 'a' then A set to '1':
> declare @.A as char (1)
> set @.A = 'a'
> select
> 1
> where
> ( isnumeric ( @.A ) = 1 )
> and
> ( cast ( @.A as int ) = 1 )
>
> --
> McG

y
> [url]http://mcg

y.blogspot.com[/url]
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> type
> to
> but
>|||Hi Uri,
isNumeric() doesn't work in all cases. For example:
select isnumeric('34e5') -- return 1
select cast('34e5' as int) -- return error
Back to your problem, try this:
select * from A, B
where case when A.CharCol not like '%^[0-9]%' then null else cast (
A.CharCol as int ) end = B.IntCol|||Thanks for that clarification!
McG

y
[url]http://mcg

y.blogspot.com[/url]
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Tam
I you read my post carefully , you would see what I wrote at the ned of the
post
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Uri,
indeed I didn't read your post carefully. My post was meant to the
original poster ( = McG

y), but I thoguth it was you ;)
cheers,

Cast Problem

If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01") from the submitted form.

SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,
OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTime
FROM ClinicalSpecimen
WHERE (SpecimenID = CAST('7575-01' AS VARCHAR(50)))
ORDER BY SpecimenID DESC

However, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found... Can someone please explain what I am missing here...

MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

*"AccessionPresent" is the value of the text field retrieved from the form.

I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.

Thank you for any or all assistance !!!

Looks like you have a blank value that you are trying to convert to int. Check for NULL/Blanks in your application before you CAST as varchar.

|||

Instead of:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

Try:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST('" & AccessionPresent &"' AS VARCHAR(50))"

Remove the Paren's, also as shark said, do something like if string.isnullorempty(AccessionPresent) then put a dummy value their or whatever

|||

Try this (use this query in your MySQL):

1SELECT *2FROM ClinicalSpecimen3WHERE SpecimenID =CAST(4ISNULL(5 ('" & AccessionPresent & "')6 , -1-- you will get -1 for Null cases7 )8AS VARCHAR(50)9 )1011-- I splited the query to make it more readable (you can put it in one line)

Hope this will help.

Good luck.

|||

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't.

Try:

Dim conn as new SqlConnection(...)

Dim cmd as new SqlCommand("SELECT * FROM ClinicalSpecimen WHERE CAST(SpecimenID AS varchar(50))=@.SpecimenID",conn)

cmd.Parameters.Add("@.SpecimenID",SqlDbType.varchar).Value=AccessionPresent

Note, this also removes the SQL Injection problem you had.

|||

Motley:
You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

|||

ndinakar:
Motley:
You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......
Good catch Motley.

Yes, good catch Motley Yes

How we did not realise that!! Embarrassed

cast inside check constraint??

I get an error when I try to do cast inside check constraint like the
following:
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1, 1) NOT NULL ,
[month] [int] NOT NULL ,
[year] [int] NULL
)
GO
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid],
cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
)
Please help.why can't you just use a unique constraint on (mytblid, month, year)?
Anyway, you can use a computed column
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1, 1) NOT NULL ,
[month] [int] NOT NULL ,
[year] [int] NULL,
[mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
varchar(4))
)
GO
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid], [mon_slash_yr]
)
go
drop table [dbo].[mytbl]
go|||This is not a check constraint. You specified a unique constraint. Casts
are allowed in check constraints. Unique constraints can only contain
column names.
Please specify what you want to achieve, because unless you are using
IDENTITY_INSERT ON, all rows in this table will be unique regardless of
the month/year setting.
Gert-Jan
sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.|||Thanks
"Alexander Kuznetsov" wrote:

> why can't you just use a unique constraint on (mytblid, month, year)?
> Anyway, you can use a computed column
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL,
> [mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
> varchar(4))
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid], [mon_slash_yr]
> )
> go
> drop table [dbo].[mytbl]
> go
>|||that's a unique constraint, not a check constraint
just make it on all three columns
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid],
[month],
[year]
)
sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.|||sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.
I guess this is what you need:
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1,1) NOT NULL
CONSTRAINT pk_mytbl PRIMARY KEY ,
[month] [int] NOT NULL ,
[year] [int] NOT NULL
)
GO
ALTER TABLE dbo.mytbl
ADD CONSTRAINT myconstraint
UNIQUE NONCLUSTERED ([month], [year])
GO
David Portas
SQL Server MVP
--

Friday, February 10, 2012

CAST error with nvarchar to int (Error converting data type nvarchar to int)

I am receiving an error with a transact query when performing a CAST from an nvarchar to int. For example:

SELECT myField1, CAST(myField2 as int) FROM tbl_myTable

MSDN article describe that this casting error "Error converting data type nvarchar to float" is caused by an invalid non-numeric entry.

Is there a way to perform an in-line test to perform a check to determine a course of action? For example:

SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Are above expression, or something similar to it, even possible in transact-SQL?

Thank you,
Each column in a select stmt can only be one data type. Why do you need to check each row to see if it is an int or not?|||Due to requirements, it is necessary to "transform" data from nvarchar into int (in a massive sweep).

I think I have been able to figure this out using CAST

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASECAST isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||

CAST and CONVERT are the same, CAST is just the standard.

Code Snippet

SELECT field1, CASE WHEN IsNumeric(myField2) = 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM table

anything other than NULL and int wont work though.|||Sorry for a bit of a typo, meant to write the following:

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASE isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||yes, that works fine|||Thank you to everyone who replied!

Cast and Convert

I am trying to convert an INT to a Date value with is currently working with
the following query
SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
However, the Date format is YYYY-MM-DD Time
Can I use Convert and Cast in the same statement. I really needs the dates
in DD/MM/YY format
Any help glady appreciated.
ThanksHow does the INT look like?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>I am trying to convert an INT to a Date value with is currently working with the following query
> SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
> However, the Date format is YYYY-MM-DD Time
> Can I use Convert and Cast in the same statement. I really needs the dates in DD/MM/YY format
> Any help glady appreciated.
> Thanks
>|||19740121
So this should display 21/01/1974
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uuamrmpDFHA.228@.tk2msftngp13.phx.gbl...
> How does the INT look like?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
> news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>>I am trying to convert an INT to a Date value with is currently working
>>with the following query
>> SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
>> However, the Date format is YYYY-MM-DD Time
>> Can I use Convert and Cast in the same statement. I really needs the
>> dates in DD/MM/YY format
>> Any help glady appreciated.
>> Thanks
>>
>|||So the format is, as integer, YYYYMMDD. That is nice, as we can CAST it to CHAR(8) and that format
is one of the formats which is language independent regarding datetime conversions from string to
datetime. Try below:
SELECT CONVERT(char(10), CAST(CAST(INV_DT AS char(8)) AS datetime), 103)
I.e., cast int to string, then string to datetime, then datetime to string (using a conversion
code).
The question is, of course, why you store dates as int instead of datetime... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:OC4m8qpDFHA.2824@.tk2msftngp13.phx.gbl...
> 19740121
> So this should display 21/01/1974
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uuamrmpDFHA.228@.tk2msftngp13.phx.gbl...
>> How does the INT look like?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
>> news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>>I am trying to convert an INT to a Date value with is currently working with the following query
>> SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates), 103)
>> However, the Date format is YYYY-MM-DD Time
>> Can I use Convert and Cast in the same statement. I really needs the dates in DD/MM/YY format
>> Any help glady appreciated.
>> Thanks
>>
>>
>|||Thanks it has worked.. I am sure I tried this :-) Luckily I didn't design
this application. I don't know why they chose INT instead of a datetime
field. :-(
Cheers
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:epNDnLqDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> So the format is, as integer, YYYYMMDD. That is nice, as we can CAST it to
> CHAR(8) and that format is one of the formats which is language
> independent regarding datetime conversions from string to datetime. Try
> below:
> SELECT CONVERT(char(10), CAST(CAST(INV_DT AS char(8)) AS datetime), 103)
> I.e., cast int to string, then string to datetime, then datetime to string
> (using a conversion code).
> The question is, of course, why you store dates as int instead of
> datetime... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
> news:OC4m8qpDFHA.2824@.tk2msftngp13.phx.gbl...
>> 19740121
>> So this should display 21/01/1974
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uuamrmpDFHA.228@.tk2msftngp13.phx.gbl...
>> How does the INT look like?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
>> news:eanimipDFHA.4052@.TK2MSFTNGP15.phx.gbl...
>>I am trying to convert an INT to a Date value with is currently working
>>with the following query
>> SELECT CONVERT (DATETIME,(select CAST(INV_DT as char(8))from dates),
>> 103)
>> However, the Date format is YYYY-MM-DD Time
>> Can I use Convert and Cast in the same statement. I really needs the
>> dates in DD/MM/YY format
>> Any help glady appreciated.
>> Thanks
>>
>>
>>
>

cast ( host_name() as int )

hi. i'm new to sql server administration.
i need to migrate a table that has field (from memory)
uid INT DEFAULT CAST(HOST_NAME() AS INT)
the current table has field values that r integers
new table of same structure is blank
trying to export the data to new table i get error saying some thin
like
cannot convert RIYAZM (computer_name) to int
seems to be converting the host_name value to int without actually
checking that there is an actual integer supplied!!!
how to solve this?
thanx
riyazWhy would you want to have a machine name in an int columns which also is na
med uid? Talk to the one
who did this data model, as it seems screwed up. Short story is that you can
not input anything which
isn't castable to an int into an int column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<rmanchu@.gmail.com> wrote in message news:1146991157.328130.266300@.g10g2000cwb.googlegroups
.com...
>
> hi. i'm new to sql server administration.
> i need to migrate a table that has field (from memory)
> uid INT DEFAULT CAST(HOST_NAME() AS INT)
> the current table has field values that r integers
> new table of same structure is blank
> trying to export the data to new table i get error saying some thin
> like
> cannot convert RIYAZM (computer_name) to int
> seems to be converting the host_name value to int without actually
> checking that there is an actual integer supplied!!!
> how to solve this?
> thanx
> riyaz
>|||(rmanchu@.gmail.com) writes:
> hi. i'm new to sql server administration.
> i need to migrate a table that has field (from memory)
> uid INT DEFAULT CAST(HOST_NAME() AS INT)
> the current table has field values that r integers
> new table of same structure is blank
> trying to export the data to new table i get error saying some thin
> like
> cannot convert RIYAZM (computer_name) to int
> seems to be converting the host_name value to int without actually
> checking that there is an actual integer supplied!!!
> how to solve this?
As Tibor said, this is completely wretched. Maybe the person who
designed the database worked from the assumption that all machine
names were numeric.
Or the memory you are working from is not correct. There is a host_id()
function, maybe that is what is in the default constraint.
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|||had a talk with the programmer. he's using WSID=# in the connection
string to set the int, different values for different users.
the original tables are fine => it contains ints in those fields.
the method does seem wierd, i guess its a short cut to do something
internal.
but i don't see a reason why the export will not work!
the field contain ints => implies the default value should not be
invoked! just insert the existing value.
perhaps my thinking is wrong.
thanx
riyaz|||the Enterprise Manager - during connect to the SQL Server - does not
allow additional options to be set?
i'd like to test using the setting WSID=#
riyaz|||You can connect using OSQL or SQLCMD, specifying a name for the machine usin
g one of the
command-line switches.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<rmanchu@.gmail.com> wrote in message news:1147059568.367462.322520@.i40g2000cwc.googlegroups
.com...
> the Enterprise Manager - during connect to the SQL Server - does not
> allow additional options to be set?
> i'd like to test using the setting WSID=#
> riyaz
>|||(rmanchu@.gmail.com) writes:
> had a talk with the programmer. he's using WSID=# in the connection
> string to set the int, different values for different users.
> the original tables are fine => it contains ints in those fields.
> the method does seem wierd, i guess its a short cut to do something
> internal.
> but i don't see a reason why the export will not work!
> the field contain ints => implies the default value should not be
> invoked! just insert the existing value.
> perhaps my thinking is wrong.
I've never used the export stuff in Enterprise Manager, so I have
no idea what it is up to. But I agree that it sounds funny that rhe
default gets in the way.
Could it be that columns are not in the same order in the source and
target tables? (This is just wild speculation.)
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|||columns r in the same order.
have given up on an automated migration
am using enterprise manager to import to a tmp "results" table in the
new database, one table at a time.
after that
insert into newdb_table (columnnames)
select columnnames from results
it works => default values do NOT get in the way but to say its tedious
is ... is an understatement!
thanx
riyaz

Thursday, March 29, 2012

Change local variable inside query

Thursday, March 22, 2012

change default value of column

Sunday, March 11, 2012

Change condition if the first doesn't exists

Change column from datetime to int

Change column from datetime to int

Change column from datetime to int

Sunday, February 19, 2012

CDBVariant

Thursday, February 16, 2012

Category and Subcategory Problem

Sunday, February 12, 2012

Casting Int to Varchar

Casting char to int but not causing error

Cast Problem

cast inside check constraint??

Friday, February 10, 2012

CAST error with nvarchar to int (Error converting data type nvarchar to int)

Cast and Convert

Cast and Convert

Cast and Convert

cast ( host_name() as int )

Sql Case sensitivity

Blog Archive

About Me