Wednesday, March 28, 2012
problem with join
key from table1 is nvarchar column, key from table2 is varchar column, so
when they join, the performance is very bad, however, when I change both
them to varchar and join them together, the performance is much better.
but the problem is I don't want to change the column definition, I still
want to improve the query. is it possible?When column datatypes are different in comparison side, SQL Server has to
internally convert it for you and that's why your query is performing slower
.
This is by design and is the expected.
You can use CAST funtion and see if it helps which I highly doubt.
"Britney" wrote:
> hi, I have two tables join with each other,
> key from table1 is nvarchar column, key from table2 is varchar column, s
o
> when they join, the performance is very bad, however, when I change both
> them to varchar and join them together, the performance is much better.
> but the problem is I don't want to change the column definition, I still
> want to improve the query. is it possible?
>
>
Friday, March 23, 2012
Problem with Incrementing variable
@.[User::INCREMENT] == @.[User::INCREMENT] + 1
Is anything wrong with this statement..bcoz iam getting 0 or the value which i assigned (1000)to all the rows.
Can you pls suggest some solution ?
Niru
Yeah, that won't do what you want. if you want to generate contiguous IDs then look here: http://www.sqlis.com/default.aspx?37
-Jamie
|||Thanks for the quick reply Jamie.If I have 4 incrementing fileds in a table ,can I create 4 variables with in a single script and map those variables to respective fileds (Or) I have to create seperate script for each and every variable?
Thanks
Kumar
|||
You can do it all in one script.
-Jamie
sqlproblem with IN (@intParameter) in a stored procedure
I want to pass a list of integer values as a parameter for a stored procedure where the criteria column is an integer
I understand what the problem is but I cannot seem to find a solution, I can't pass it as a varchar because the column is an int and I can't pass it as an in because of the commas in the list
SELECTAppId, ApplicationName, NextPage, AppData, GrpLogo, DeptLogin
FROMdbo.TblApplication
WHERE(AppIdIN(@.intParameter))
ORDER BYSortOrder, ApplicationName
any help please
This question has been answered a few times, with sample code in the solution. Please search in these forums. I am sure you will find it.|||Search for the Split function.|||
thanks for that I found304221 Terri Morton's bit did the trick
|||I did as you suggested and found 304221 from Terri Morton's did just what I wanted
Many Thanks
problem with identity column value
create table test
(
id bigint identity(1000,1) NOT NULL,
x int
)
set IDENTITY_INSERT test on;
insert into test (id,x) values (10,1);
set IDENTITY_INSERT test off;
-- identity value is OK = 1000, but:
set IDENTITY_INSERT test on;
insert into test (id,x) values (2000,1);
set IDENTITY_INSERT test off;
-- identity value isn't OK = 2000, but I need identity value = 1000
-- I know solution, but it is to slow:
DECLARE @.id_seq bigint;
SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
set IDENTITY_INSERT test on;
insert into test (id, x) values (2000, 1);
set IDENTITY_INSERT test off;
DBCC CHECKIDENT ('test', RESEED, @.id_seq);
-- do you know some faster and better solution? Thankssilber wrote:
> Example:
> create table test
> (
> id bigint identity(1000,1) NOT NULL,
> x int
> )
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (10,1);
> set IDENTITY_INSERT test off;
> -- identity value is OK = 1000, but:
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (2000,1);
> set IDENTITY_INSERT test off;
> -- identity value isn't OK = 2000, but I need identity value = 1000
> -- I know solution, but it is to slow:
> DECLARE @.id_seq bigint;
> SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
> set IDENTITY_INSERT test on;
> insert into test (id, x) values (2000, 1);
> set IDENTITY_INSERT test off;
> DBCC CHECKIDENT ('test', RESEED, @.id_seq);
> -- do you know some faster and better solution? Thanks
I don't quite understand the problem. Apparently you want to reset the
current IDENTITY value after inserting some data with IDENTITY_INSERT
on. The only reason I can imagine performance would be in issue here is
if you had to issue DBCC CHECKIDENT on a frequent basis during
user-transactions. But if you had to do that on a frequent basis then
I'd say you would be better off not having the column as an IDENTITY at
all.
Alternatively, you could use only negatives for the IDENTITY_INSERT
values and use positives for the incrementing IDENTITY value. That way
the increment won't be affected.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Problem with IDENTITY column
I have a problem with a table with an IDENTITY column. In order to illustrate my problem I have created a small table according to:
CREATE TABLE [dbo].[W_Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) NULL ,
[Age] [int] NULL ,
[OwnerID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[W_Person] WITH NOCHECK ADD
CONSTRAINT [PK_W_Person] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
When I create two new records, the field ID is assigned the next available value.
So far everything works fine.
THE PROBLEM:
If I insert a record with the following SQL statements:
SET IDENTITY_INSERT W_Person ON
INSERT INTO W_Person (ID, Name, Age, OwnerID)
VALUES (8000, 'John Johnson', 30, 2)
SET IDENTITY_INSERT W_Person OFF
and then deletes the same record. As a next step I tries to insert a new record and let SQL Server generate a new id, the new id is assigned the value of 8001 instead of 3.
Please, can anybody help me on this one!
//Peterhi peterand,
the identity-seed is set to 8000 after your insert. so the next value will be 8001...
if you want to re-seed, take a look at bol "DBCC CHECKIDENT"
markus
Wednesday, March 21, 2012
Problem with Hide Duplicates?
checkbox is checked, the hidden values are still counted in the total.
I don't want the hidden values to be counted in the total. Can someone
tell me if this is how Hide Duplicates is supposed to work and/or
suggest a possible workaround?I am wondering why no one has answered this question as I too am
interested in an explanation of this behavior.
I was under the impression that Microsoft moderated this newsgroup and
made some sort of promise to respond to these kinds of questions.
Please help!|||Hi, Lee.
The way hide duplicates works is that it doesn't show a duplicate value in
the table, but this value is still part of the dataset. So when you do the
total of the field, it doesn't look at the table it just looks at the dataset
and sums the data. Therefore it counts all the data.
A possible workaround is, if all of the values are the same, then take the
first one.
If not, it is a lot more difficult. First try to change your dataset to
retrieve only unique values, if possible. Otherwise, custom code will
probably be needed. Probably build an array of unique values. The last
solution I have not tested and I'm not sure how it would actually work.
I hope this helps.
"Lee" wrote:
> When I put a total beneath a column for which the "Hide Duplicates"
> checkbox is checked, the hidden values are still counted in the total.
> I don't want the hidden values to be counted in the total. Can someone
> tell me if this is how Hide Duplicates is supposed to work and/or
> suggest a possible workaround?
>|||Replace Count(...) with CountDistinct(...)
GeoSynch
"Anton" <Anton@.discussions.microsoft.com> wrote in message
news:A95DC535-C2D4-4FF1-AC59-C7A75DA6834A@.microsoft.com...
> Hi, Lee.
> The way hide duplicates works is that it doesn't show a duplicate value in
> the table, but this value is still part of the dataset. So when you do the
> total of the field, it doesn't look at the table it just looks at the dataset
> and sums the data. Therefore it counts all the data.
> A possible workaround is, if all of the values are the same, then take the
> first one.
> If not, it is a lot more difficult. First try to change your dataset to
> retrieve only unique values, if possible. Otherwise, custom code will
> probably be needed. Probably build an array of unique values. The last
> solution I have not tested and I'm not sure how it would actually work.
> I hope this helps.
> "Lee" wrote:
>> When I put a total beneath a column for which the "Hide Duplicates"
>> checkbox is checked, the hidden values are still counted in the total.
>> I don't want the hidden values to be counted in the total. Can someone
>> tell me if this is how Hide Duplicates is supposed to work and/or
>> suggest a possible workaround?
>>
Tuesday, March 20, 2012
Problem with FOR XML clause
I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.
<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>
Writing XML file using DataSet.WriteXml() method persists the same column as binary format
<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2
I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.
Thanks
Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm
For more details.
Jeff Derstadt - MSFT
Problem with FOR XML clause
I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.
<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>
Writing XML file using DataSet.WriteXml() method persists the same column as binary format
<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2
I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.
Thanks
Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm
For more details.
Jeff Derstadt - MSFT
Monday, March 12, 2012
Problem with expression window in derived column
Hi,
i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.
let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.
Can any one asssit in this regard how to achive it? and is it possible to use IF and CASE statement in this dervived column tranformation?
Sreenivas
Select the Add New column option, call it ColB or whatever, then use an expression like this -
ColA == 0 ? "GOOD" : "BAD"
This uses the conditional operator, as documented in Books Online - http://msdn2.microsoft.com/en-us/library/d38e6890-7338-4ce0-a837-2dbb41823a37(SQL.90).aspx
Logicaly it reads like this -
If ColA Equals 0 Then
Return "GOOD"
Else
Return "BAD"
End If
Problem with enterprise manager ( image attached )
I have been having a problem for a couple of weeks, for some reason the
"Create Date" column of both the tables and SP views appear with weird
characters (as shown in the attached image) , This happen both on a local
server as well as on a remote server.
When I connect to the remote server using TS and see the same view the
"Create Date" column appear normally.
What could have happen? and even it's not a big issue I would like to know
how to solve it.
Thanks,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
Did you by chance install YUKON Beta on the same box ?
"Ignacio Machin ( .NET/ C# MVP )" wrote:
> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to know
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>
|||Nop,
I haven't installed it, I have VS.NET 2005 installed , though.
Cheers,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:F4BA581B-F664-469D-8C7B-301E72D72292@.microsoft.com...[vbcol=seagreen]
> Did you by chance install YUKON Beta on the same box ?
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
local[vbcol=seagreen]
know[vbcol=seagreen]
|||Probably that is the problem..
"Ignacio Machin ( .NET/ C# MVP )" wrote:
> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to know
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>
Problem with enterprise manager ( image attached )
I have been having a problem for a couple of weeks, for some reason the
"Create Date" column of both the tables and SP views appear with weird
characters (as shown in the attached image) , This happen both on a local
server as well as on a remote server.
When I connect to the remote server using TS and see the same view the
"Create Date" column appear normally.
What could have happen? and even it's not a big issue I would like to know
how to solve it.
Thanks,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of TransportationDid you by chance install YUKON Beta on the same box ?
"Ignacio Machin ( .NET/ C# MVP )" wrote:
> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to kno
w
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>|||Nop,
I haven't installed it, I have VS.NET 2005 installed , though.
Cheers,
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:F4BA581B-F664-469D-8C7B-301E72D72292@.microsoft.com...[vbcol=seagreen]
> Did you by chance install YUKON Beta on the same box ?
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
>
local[vbcol=seagreen]
know[vbcol=seagreen]|||Probably that is the problem..
"Ignacio Machin ( .NET/ C# MVP )" wrote:
> Hi,
> I have been having a problem for a couple of weeks, for some reason the
> "Create Date" column of both the tables and SP views appear with weird
> characters (as shown in the attached image) , This happen both on a local
> server as well as on a remote server.
> When I connect to the remote server using TS and see the same view the
> "Create Date" column appear normally.
> What could have happen? and even it's not a big issue I would like to kno
w
> how to solve it.
>
> Thanks,
> --
> Ignacio Machin,
> ignacio.machin AT dot.state.fl.us
> Florida Department Of Transportation
>
>
Friday, March 9, 2012
Problem with enterprise manager ( image attached )
I have been having a problem for a couple of weeks, for some reason the
"Create Date" column of both the tables and SP views appear with weird
characters (as shown in the attached image) , This happen both on a local
server as well as on a remote server.
When I connect to the remote server using TS and see the same view the
"Create Date" column appear normally.
What could have happen? and even it's not a big issue I would like to know
how to solve it.
Thanks,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of TransportationNop,
I haven't installed it, I have VS.NET 2005 installed , though.
Cheers,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:F4BA581B-F664-469D-8C7B-301E72D72292@.microsoft.com...
> Did you by chance install YUKON Beta on the same box ?
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
> > Hi,
> >
> > I have been having a problem for a couple of weeks, for some reason the
> > "Create Date" column of both the tables and SP views appear with weird
> > characters (as shown in the attached image) , This happen both on a
local
> > server as well as on a remote server.
> > When I connect to the remote server using TS and see the same view the
> > "Create Date" column appear normally.
> >
> > What could have happen? and even it's not a big issue I would like to
know
> > how to solve it.
> >
> >
> > Thanks,
> >
> > --
> > Ignacio Machin,
> > ignacio.machin AT dot.state.fl.us
> > Florida Department Of Transportation
> >
> >
> >
Problem with Dynamic SQL !
I have a procedure with an input parameter & output parameter. The input parameter value is a table name which has an identity column. The procedure will set the next value of the identity column to the output parameter. I stuck with the dynamic sql. Here it goes...
---------------------------------------
CREATE TABLE seqtest(nextVal NUMERIC(38) IDENTITY(1000000,1),dummyCol TINYINT);
create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
EXECUTE( 'SELECT '+ @.nv +' = id from ' + @.seqName )
SET @.nextVal = @.nv
END
---------------------------------------
after i created the table & procedure, i executed the below code:
---------------------------------------
DECLARE @.nextVal1 int
EXECUTE NextVal 'seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1
---------------------------------------
but it says
Msg 170, Level 15, State 1, Server SWISSQL-WIN2K, Line 1
Line 1: Incorrect syntax near '='.
(return status = 0)
Can anyone point out where i went wrong?
JakeDECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||Hi Eniqma,
It didn't solve my prob. same error...
The problem is in the select statement
EXECUTE( 'SELECT '+ @.nv +' = nextVal from ' + @.seqName )
It says Incorrect syntax near '='.
any idea?
Jake|||Oops ... i forgot ... you cannot create a dynamic string inside an execute statement
You will have to do something like
create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )
select @.query ='SELECT '+ @.nv +' = id from ' + @.seqName
EXECUTE(@.query )
SET @.nextVal = @.nv
END|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )
select @.nextval= @.@.identity
END
DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )
select @.nextval= scope_identity()
END
DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1
You should really be using scope_identity()
And from what I think what you are trying to achieve , it would not matter even if you used a identity column in your original table ...|||Hi eniqma, when the procedure is executed, it will say
Msg 245, Level 16, State 1, Server SWISSQL-WIN2K, Line 9
Syntax error converting the varchar value 'SELECT ' to a column of data type int.
as int is concatenated with string.
@.@.IDENTITY approach guides me to the solution. but i didn't use @.@.IDENTITY as it returns the last identity value generated for any table in the current session, across all scopes.
I used IDENT_CURRENT('table_name') as it returns the last identity value generated for a specific table in any session and any scope.
so here is the final procedure
---------------------------------------
alter procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
SET @.nextVal = IDENT_CURRENT(@.seqName)
END
---------------------------------------
thanks eniqma & harshal for your time & help.
Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.
But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
any clue why it didn't work?
Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.
But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
any clue why it didn't work?
Jake
I think u have already answered the question.
But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
since the select and insert are not in the scope i think it wont work.|||Hmm ... you are right harshal ... never thought about that
Jake .. mind clarifying why you are going through all this when you could have done with a indentity column in the table for which you are generating a sequence ...|||i thought the dynamic sql execution would happen in the current scope. it puzzles me... so what actually happens is dynamic sql execution happens in a different scope than where it is called. may be i have to understand the execute statement further...
thanks for the clarification, harshal.
Jake|||oh! sorry, i missed that... i am trying to automate the SQL stored procedures conversion from Oracle to SQL Server. as you know Oracle has sequence & SQL Server doesn't.. That's why i trying to simulate sequence in SQL Server...
Thanks for your interest.
Jake|||Oh ... so thats what you are trying to do ...
Talking about scope ... its the same in sql as in other languages ...
If you called a stored procedure B inside a stored procedure A then the variables declared in sproc B get out of scope as soon as it returns control to sproc A. Similar with EXEC ... think of it as a stored procedure which executes what ever is passed to it and returning the result.|||now i understand, enigma... glad to see your reply.
Thanks,
Jake|||Without reading this little lot too deeply, the answer to the original question is:
1: Look at the spaces before and after you + signs '+ @.n +' needs to be:
' + @.n + '
2: You have to explicilty convert the int variable to a string (sorry, thats the vb in me coming out) varchar before you can add it to one.
E.G. 'I am a varchar ' + CAST(@.IntVariable AS Varchar(250)) + ' The rest of the varchar string'
Otherwise you get the converting int to varchar error.
Have fun
Best regards
Steve
P.S. Don't forget the spaces when breaking strings and inserting variables (before and after). Use Print CAST(@.SqlString as Varchar(250)) to check your Sequel statement for errors.
Saturday, February 25, 2012
problem with delete statement
Hi guys,
i need some help on this issue, i have a table with a nvarchar type column with primary key constraint. I have made a procedure which deletes record from this table using the primary key. My primary key is TxnID, and when i ran the following statement:
DELETE from mytable where TxnID = '119DA-117440520'
it deleted the record with the id '119DA-1174405208'
I dont want this to happen, i only want to delete records with the exact IDs that i provide. Can any one tell me how i should do that?
Thanks in advance.
sorry! i managed to fix it...actually the width was not defined properly which was causing problem
sorry again
problem with datetime datatype..
however, you can have a date with a midnight time, e.g. 2006-05-29 00:00:00
use dateadd(d,datediff(d,0,getdate()),0)
moving thread to microsoft sql server forum (if you're using some other database, please be sure to mention it)
Problem with DateTime column
I faced this strange problem with sql server.
I have datetime column and storing value from asp.net. If the user doesnt enter any date, then we dont want any value to be stored in the database. But when we checked SQL Server, it default takes this value "01/01/1900". When users clicks on edit button, this value is fetched from database and stored in front end.
I dont want this value to be stored in database if i didnt provide value. But in 1 particular page, there are 5 different date columns. I am not sure about how to go ahead. I tried the following way, but again ended up with same problem.
insert into tablename......values (..., txtFromDate.text, txtToDate.text,...).
Since value is not there, '' is sent to database and above said value is stored. i want NULL to be stored in teh database when user didnt specify any value.
i didnt encounter this problem in Oracle.
appreciate your reply.
In the table design window for this table put a check in the column for allow nulls for the datetime field and make sure that you have not set the field with a default value. Also validate the textbox before data insertion or update like so.
if(txtFromDate.Text.Trim(' ') != "")
(
Place your database insertion code.
}
This will make sure that the textbox actually has something in it before you insert the value. Also you should have to convert the value of the textbox to a datetime before the data is inserted like this.
yadayadayada = Convert.ToDateTime(txtFromDate.Text);
I hope that this helps.
|||
hi,
thanks for your reply. I thought of doing it like check the textbox value. But the problem is i am having 5 date control in that particular page.
So the condition for NULL checking, i need to try out with all probability. I believe, in that case, i should use around 25 if statement with each statement checking for all 5 condidtion.
Is there any other way to doing it.
rgds
ramu
Nick|||hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.|||hi all,
once again, thanks for your reply.
since it was getting complicated and time was main constraint for me, so i tried it in the following way.
If the value is null, i let it get stored as 01/01/1900 itself.
but while fetching record for edit/view, i am checking those date columns for for above. If yes, i display null value, otherwise the stored value is displayed.
the above one temporarily solved my problem.
based on your solution, i think iif will solve my issue.
rgds
ramu|||
ndinakar wrote:
hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.
Maybe its because of the way its being used? I always pass dbNull.value to my stored procs if the value is blank....
Nick|||It works for all columns except datetime columns. Leaving datetime columns empty will throw errors.
Monday, February 20, 2012
Problem with datatypes...
Dear Friends,
I have a problem with datatypes....
The error is "Cannot convert between unicode and non-unicode..."
The column in source is nvarchar(32) and in the destination is varchar(32)... ok...
But in the DataReader Source in the SQLCommand I've converted the column into varchar(32) with
convert(varchar(32), Equities.Equities_Name) as CL_FOLDER_ID
Do you have any tip?
Thanks!Look at the actual output column for the Source, and you will see that that is DT_WSTR, the unicode string type in SSIS. The DR source does not support non-unicode strings, everything becomes a .Net string in the ADO.Net provider world, and and then SSIS will always treat these as DT_WSTR. If you used the OLE-DB Source through, you get support for both DT_STR and DT_WSTR, mapped from nvarchar and varchar respectively.
So in summary since your source will always give you DT_WSTR, you will need to use a conversion transform inside the pipeline, see Derived Column or Data Conversion, either will do.
|||Darren,
I used the conversion, but I i tryied to use in SQL command, but if it's not possible, OK... I will use the conversion transform...
Thanks!!
Problem with dataset
My data from this dataset works until I get to a column that has no value in it then it starts not showing the value in the three columns after the no value column. This is my code to handle the no value column.
if ((myDataset.Tables[0].Rows[i][j].ToString()) == "")
I'm not sure of the C# syntax, but try:
if ((myDataset.Tables[0].Rows[i][j] is DBNull)
|||It ended up that because of a javascript issue it was entering a bunch of spaces in that column and therefore the column wasn't null or "" empty and I couldn't check the length cause of the spaces. So I replaced " " with "" (2 spaces with 0 spaces) then checked if length is less than 2. It worked perfect.