Problem in Datatype Date Convertion

Hi,

My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.

With Regards

Satish

What is the full error message? It should tell you in which component the error is occurring.

-Jamie

sql

Problem in crosstab column

Hi All

I am using crosstab,in the column i have feilds like 'General-Appolo','Medical-Appolo','shopping'.....

in that in crosstab need to get only the fields which consists the name like 'appolo'

dont want the feilds with out having name like 'appolo'

Thanks a lotIn the suppress event of the field write this
not {Field}="Appolo"|||Thanks,

But it is check for only exact name is "Apollo"

But i want not like "%apollo%'
(not like functionality of sql server)|||Then try

not Instr({Field},"Appolo",1)>0

Problem in concatinating two paramters to update one column

Using gridview to display the data and sql server 2000

I havea column in the database say departtime of datetime datatype thatcntains the date and time resp(09/19/2007 9:00 PM). I am separating thedate and time parts to display in two different textboxes saytxt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by usingthe convert in sqldatasource. Now i need to update the column in thedatabase and i am using Updatecommand with parameters in aspx lkeupdatecommand = "Update table set departtime = @.departtime" . How cani update my column as datetime by getting the data from 2 texboxes asnow i have 2 textboxes displaying data for single column means if useredit the data in txt1 as(10/19/2007) then on click of update i need topopulate the column daparttime as (10/19/2007 9:00 PM).

Please let me know if you have any questions.

Hello Nick,

What you need is the DateTime.Parse method. See:http://msdn2.microsoft.com/en-us/library/system.datetime.parse(VS.71).aspx

This will create a datetime field of your two text fields.

Jeroen Molenaar.

sql

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIA
Change
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA
|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegro ups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.
sql

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIAChange
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegroups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIAChange
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegroups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.

Problem in Adding column in table

Dear Friends
I am getting the following problem while adding a column
in the replication table.
Please suggest.
Problem:
Code:
sp_repladdcolumn [ @.source_object = ] 'OTHALLOCATION'
, [ @.column = ] 'CCICAPPNUM' ]
[ , [ @.typetext = ] 'NVARCHAR(50)' ]
[ , [ @.publication_to_add = ] 'GEMREPLGNETINFOTECH' ]
[ , [ @.schema_change_script = ] 'NVARCHAR(1000)' ]
[ , [ @.force_invalidate_snapshot = ]
force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ]
force_reinit_subscription ]
Your earlier reply would be a great help.
Best regards
Sharad
Did you forget to paste the error message?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Sharad" <niitmalad@.yahoo.co.in> wrote in message
news:06dd01c48b52$703f6b00$a501280a@.phx.gbl...
Dear Friends
I am getting the following problem while adding a column
in the replication table.
Please suggest.
Problem:
Code:
sp_repladdcolumn [ @.source_object = ] 'OTHALLOCATION'
, [ @.column = ] 'CCICAPPNUM' ]
[ , [ @.typetext = ] 'NVARCHAR(50)' ]
[ , [ @.publication_to_add = ] 'GEMREPLGNETINFOTECH' ]
[ , [ @.schema_change_script = ] 'NVARCHAR(1000)' ]
[ , [ @.force_invalidate_snapshot = ]
force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ]
force_reinit_subscription ]
Your earlier reply would be a great help.
Best regards
Sharad
sql

Problem in a derived column

Dear Friends,

I have an derived column in an ETL, that use the DateDiff function.

The experssion of the derived column is:

DATEDIFF("d",RKData,MaturityDate) / 365

The problem is that the result always come as an integer, and in fact the result of the expression usually has decimal numbers.. :-(

I defined the new colum of derived column as doube-precison float [DT_R8]....

Someone help me?

Thanks!

PedroCGD wrote:

Dear Friends,

I have an derived column in an ETL, that use the DateDiff function.

The experssion of the derived column is:

DATEDIFF("d",RKData,MaturityDate) / 365

The problem is that the result always come as an integer, and in fact the result of the expression usually has decimal numbers.. :-(

I defined the new colum of derived column as doube-precison float [DT_R8]....

Someone help me?

Thanks!

Try:
(DT_NUMERIC,7,4)DATEDIFF("d",RKData,MaturityDate) / (DT_NUMERIC,7,4)365

That should give you 4 decimal places. Adjust the precision and scale accordingly, if you need.|||

THANKS!!

I changed to (DT_NUMERIC,7,2)DATEDIFF("dd",RKData,MaturityDate) / (DT_NUMERIC,7,2)365 and it works!!

Thanks!!!!!!

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
mark
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark
|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!
|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J

>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>
|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
mark

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
markWhat medium are you using to transfer the data and from
what source.
Do the field sizes of the to and from match ?
J
>--Original Message--
>having a problem with one single field
>its coming up with the error message "data for source
column 2 ('general
>notes') is too large for the specified buffer size"
>how would i fix this ?
>thanks
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> > What medium are you using to transfer the data and from
> > what source.
> >
> > Do the field sizes of the to and from match ?
> >
> > J
> >
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J
>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>> What medium are you using to transfer the data and from
>> what source.
>> Do the field sizes of the to and from match ?
>> J
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
marksql

problem importing access field into mssql

having a problem with one single field
its coming up with the error message "data for source column 2 ('general
notes') is too large for the specified buffer size"
how would i fix this ?
thanks
mark"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> What medium are you using to transfer the data and from
> what source.
> Do the field sizes of the to and from match ?
> J
>
im converting from access using DTS - this is the only field i've had
trouble with
the original field is a memo field which im trying to transfer into a sql
nvarchar field with 2000 chars - there is a lot of data in the original
source tho - and its not nice data!
cheers
mark|||Try NTEXT as destination field instead of NVARCHAR one
"mark" <mark@.remove.com> wrote in message news:1Q4qc.90$LH3.0@.newsfe1-win...
> "Julie" <anonymous@.discussions.microsoft.com> wrote in message
> news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
> im converting from access using DTS - this is the only field i've had
> trouble with
> the original field is a memo field which im trying to transfer into a sql
> nvarchar field with 2000 chars - there is a lot of data in the original
> source tho - and its not nice data!|||Hello,
According to the Access help a memo field can store up to
65000 + character.
The nvarchar(2000) can store up to 1000 characters (look
up unicode in BOL). Try changing the SQL Server datatype
to a text or binary.
J

>--Original Message--
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:e07f01c43c15$e5c4db80$a101280a@.phx.gbl...
>im converting from access using DTS - this is the only
field i've had
>trouble with
>the original field is a memo field which im trying to
transfer into a sql
>nvarchar field with 2000 chars - there is a lot of data
in the original
>source tho - and its not nice data!
>cheers
>mark
>
>.
>|||"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:e12301c43c1d$dbca1a70$a101280a@.phx.gbl...
> Hello,
> According to the Access help a memo field can store up to
> 65000 + character.
> The nvarchar(2000) can store up to 1000 characters (look
> up unicode in BOL). Try changing the SQL Server datatype
> to a text or binary.
> J
>
thanks everyone for the help, converting to ntext did the trick!
mark

problem getting right properites

I'm having problems getting the right properties out.
I get the launch date in a column, but that overwrites
with the cube like, [Zone Id].[A].[B].[C]
it inserts the launch date, where the C column should be. In the 2nd query
below, I can get both the date and the value of C, but it gives me double
the rows, and it's in the same column. 'launch date' is a property of [C]
WITH
SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
Id].&[14].&[74].children,
[Zone].CurrentMember.Properties("launch date"))'
SELECT {[Measures].[data1],[Measures].[data2]} ON COLUMNS,
{[ld]} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY
ON ROWS
from [All Data]
--
If I use this query, I get double the rows.
WITH SET Top5 AS
' Descendants( [Zone Id].&[14].&[74], 1)'
SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
Id].&[14].&[74].children,
[Zone].CurrentMember.Properties(" launch date"))'
SELECT {[Sends],[Measures].[S Sends]} ON COLUMNS,
{[Top5],[ld]}
ON ROWS
from [All Data]sorry I found it, I just gotta add
DIMENSION PROPERTIES [Zone].[Campaign Launch Date]
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:Odi1UoD%23EHA.2596@.tk2msftngp13.phx.gbl...
> I'm having problems getting the right properties out.
> I get the launch date in a column, but that overwrites
> with the cube like, [Zone Id].[A].[B].[C]
> it inserts the launch date, where the C column should be. In the 2nd
query
> below, I can get both the date and the value of C, but it gives me double
> the rows, and it's in the same column. 'launch date' is a property of [C]
> WITH
> SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
> Id].&[14].&[74].children,
> [Zone].CurrentMember.Properties("launch date"))'
> SELECT {[Measures].[data1],[Measures].[data2]} ON COLUMNS,
> {[ld]} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY
> ON ROWS
> from [All Data]
> --
> If I use this query, I get double the rows.
> WITH SET Top5 AS
> ' Descendants( [Zone Id].&[14].&[74], 1)'
> SET ld AS 'CreatePropertySet([Zone Id].&[14].&[74], [Zone].[Zone
> Id].&[14].&[74].children,
> [Zone].CurrentMember.Properties(" launch date"))'
> SELECT {[Sends],[Measures].[S Sends]} ON COLUMNS,
> {[Top5],[ld]}
> ON ROWS
> from [All Data]
>
>

Problem exporting to a .pdf

I have a column that contains a field I use for grouping.
The field prints in both the page header and footer, but not the detail section.

Hidden for the column is true.
Hidden for the textbox (field) is false
DataElementOutput for the textbox (field) is Output.
DataElementStyle for the textbox is ElementNormal ( I also tried AttributeNormal but no joy)

Everything works except when I export to acrobat, in which case the field does not print in the page header or footer.

If column is hidden, how will it ever work and print in any format?

What exactly is your problem?

|||

What version of Reporting Services are you using? I would expect the field to be present in PDF on SQL Server 2005 SP2.

Does exporting to TIFF work? Can you please file a bug on http://connect.microsoft.com and include a copy of your RDL?

Thank you.

|||Also noticed that the group field displays when the page is rendered on screen but does not print. Am using sp2. Is this a bug?|||>>What version of Reporting Services are you using?
SS 2005 SP2
>>Does exporting to TIFF work?
Nay

problem dropping columns

One of our developers accidentally added a 'rowguid' column to all of our
tables (mssql 2000). I'm trying to write a script that will drop this
column from all the tables; however, I've run into a problem where I can't
drop them because there are dependant contraints/indexes. The following
code is what I have so far. Is there's a way to identify and drop all
dependancies on this column first?
DECLARE @.TableName sysname
DECLARE @.ColumnName sysname
DECLARE RowGuidColumnList CURSOR
FOR select t.name, c.name
FROM sysobjects t
JOIN syscolumns c
ON (c.id = t.id and t.type = 'U')
WHERE c.name = 'rowguid'
order by t.name, c.name
OPEN RowGuidColumnList
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Removing rowguid column from ' + @.TableName
execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
END
CLOSE RowGuidColumnList
DEALLOCATE RowGuidColumnList
Thanks, DougYou can find information about dependencies of some
particular column from system tables:
sysconstraints,
syscolumns,
sysobjects
Take a look about this tables in BOL.
Regards
----
All information provided above AS IS
>--Original Message--
>One of our developers accidentally added a 'rowguid'
column to all of our
>tables (mssql 2000). I'm trying to write a script that
will drop this
>column from all the tables; however, I've run into a
problem where I can't
>drop them because there are dependant
contraints/indexes. The following
>code is what I have so far. Is there's a way to identify
and drop all
>dependancies on this column first?
>DECLARE @.TableName sysname
>DECLARE @.ColumnName sysname
>DECLARE RowGuidColumnList CURSOR
>FOR select t.name, c.name
>FROM sysobjects t
> JOIN syscolumns c
> ON (c.id = t.id and t.type = 'U')
>WHERE c.name = 'rowguid'
>order by t.name, c.name
>OPEN RowGuidColumnList
>FETCH NEXT FROM RowGuidColumnList
>INTO @.TableName, @.ColumnName
>WHILE @.@.FETCH_STATUS = 0
>BEGIN
> PRINT 'Removing rowguid column from ' + @.TableName
> execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' +
@.ColumnName)
> FETCH NEXT FROM RowGuidColumnList
> INTO @.TableName, @.ColumnName
>END
>CLOSE RowGuidColumnList
>DEALLOCATE RowGuidColumnList
>
>Thanks, Doug
>
>.
>

Problem dropping a column from a table in a publication

Hi,
I create a column in a table that is in a merge publication with this code
USE JMI
GO
sp_repladdcolumn @.source_object='ztest',@.column='dateDepart',@.typet ext='
datetime NULL DEFAULT getDate()' ,@.publication_to_add='JMI_articles'
GO
I realise that a made a mistake and i want to drop the column
So i try this code
USE JMI
GO
sp_repldropcolumn @.source_object='ztest',@.column='dateDepart'
GO
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'JMI_articles' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__ztest__dateDepar__66D9612E' is dependent on column
'dateDepart'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN dateDepart failed because one or more objects access
this column.
It is possible to drop a column from a replication ?
Yes, it is possible, however in this case it looks like other tables are
dependent on this column, so you it looks like you have to drop the
constraint before you do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GC" <GC@.discussions.microsoft.com> wrote in message
news:DEFC493F-23E9-40C3-816D-6ADFE710581C@.microsoft.com...
> Hi,
> I create a column in a table that is in a merge publication with this code
> USE JMI
> GO
> sp_repladdcolumn @.source_object='ztest',@.column='dateDepart',@.typet ext='
> datetime NULL DEFAULT getDate()' ,@.publication_to_add='JMI_articles'
> GO
> I realise that a made a mistake and i want to drop the column
> So i try this code
> USE JMI
> GO
> sp_repldropcolumn @.source_object='ztest',@.column='dateDepart'
> GO
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'JMI_articles' because schema replication is performed.
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__ztest__dateDepar__66D9612E' is dependent on column
> 'dateDepart'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN dateDepart failed because one or more objects
> access
> this column.
>
> It is possible to drop a column from a replication ?
>
>
>
>

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.
This is similar to the problem in KB Article 816780
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780
|||Ok, then explain why when the index is removed the problem goes away?
|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David
|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas

"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT
'.
This is similar to the problem in KB Article 816780The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780|||Ok, then explain why when the index is removed the problem goes away?|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem Deleteing Records with Indexed Computed Column

Got the following error when trying to delete records from a table that
contained and Indexed Computed column.
"System.Data.SqlClient.SqlError: DELETE failed because the following SET
options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.
This is similar to the problem in KB Article 816780The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:ED599EDB-3612-485E-8839-1B282A43C186@.microsoft.com...
> Got the following error when trying to delete records from a table that
> contained and Indexed Computed column.
> "System.Data.SqlClient.SqlError: DELETE failed because the following SET
> options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER,
ARITHABORT'.
> This is similar to the problem in KB Article 816780|||Ok, then explain why when the index is removed the problem goes away?|||As you don't include the message you're replying to I can't tell whether
you're replying to my reply. Here's what I previously posted that will
explain why the problem goes away if you remove an index over a computed
column:
<begin>
The issue the KB article is referring to was with some shipping code. The
issue you're seeing is because you need to set the SET options correctly
before issuing the delete. From BOL 'SET' topic:
When creating and manipulating indexes on computed columns or indexed views,
the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
Hope this helps.
<end>
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
> Ok, then explain why when the index is removed the problem goes away?
>
An index on a computed column has all the same restrictions as an indexed
view.
Think of it this way. All sessions connecting to your database should have
these options:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER,
ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option
NUMERIC_ROUNDABORT must be set to OFF.
If they don't then some features of the database will be unavailable, and
they may not be able to change data.
David|||The restriction for the ANSI-COMPLIANT SET OPTIONS is only when creating
Indexes on Computed Columns and Views. If you never create these indexes,
then clients are not REQUIRED to connect using the set options; however, it
is recommended that clients ALWAYS connect with these options set and then
modify individual statements or batches as required regardless if the
extended functionality is used.
Sincerely,
Anthony Thomas
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:74948FCE-9171-4F10-B596-0A366AC6805C@.microsoft.com...
Ok, then explain why when the index is removed the problem goes away?

Problem Creating One-To-Many Relationship

Hi guys,

I'm testing some stuff. I have two tables, table1 has a primary key and table2 only has a column. When I try to create a relationship between them, SQL Server complains.

Here's my code:

Alter Table Table1
Add Constraint FK_T1 Foreign Key (T1) References Table2(T1)

And the error:

There are no primary or candidate keys in the referenced table 'Table2' that match the referencing column list in the foreign key 'FK_T1'.

Thanks
DarkneonOK guys, I found what was wrong.

I should go and kill myself for posting this question, now that I realize how stupid it is :o

Problem copying data from column in same table

I have a column that I would like to copy data to. The column definitions a
re exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my co
lumn remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it affec
ted.
Thanks, IrisYour SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||That's correct, I'd rather update. I'll have to start back over since my te
st table now has all those null values in it like Jim mentioned. Thanks.
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||That's correct :)
Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Thats what it should show. You are updating every existing row, and populat
ing CustomerID1 with the value from CustomerID.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:ut408SeHGHA.
3408@.TK2MSFTNGP12.phx.gbl...
I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||At a minimum you should have certain fields defined as not null (CustomerID
for example). There should also be some way of identifying unique records,
even in a history table (although not in an audit table). Usually when I ha
ve worked with history tables they would have a date field that would be par
t of the primary key. When multiple entries were allowed on the same date,
some sort of sequence field was used to insure uniqueness.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:OYAFhQeHGHA.
528@.TK2MSFTNGP12.phx.gbl...
Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris