Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype = of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column = 'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects = access this column.
I can change the datatype without any problems from either Enterprise = Manager or the new Sql Server Management Studio Express. I am working with = a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio = Express. I would much prefer to use an automatic update script.
TIA
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich NietzscheOk, a bit of an update. I have discovered that the error is due to a =DEFAULT constraint on the columns in question. Problem is, this constraint =has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =detect the DEFAULT constraints, remove them, alter the column and add the =constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =it in a T-SQL script.
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche|||This is why you always should name your constraints. Here's an example on how to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT constraint on the
columns in question. Problem is, this constraint has system generated name, so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically detect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it in a T-SQL script.
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Tibor Karaszi wrote:
>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =constraint reference.
The example you posted does not work with 2000. I guess the system tables =follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =suits my immediate needs.
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche

Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype =
of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column =
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects =
access this column.
I can change the datatype without any problems from either Enterprise =
Manager or the new Sql Server Management Studio Express. I am working with =
a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio =
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
Ok, a bit of an update. I have discovered that the error is due to a =
DEFAULT constraint on the columns in question. Problem is, this constraint =
has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =
detect the DEFAULT constraints, remove them, alter the column and add the =
constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =
it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
|||This is why you always should name your constraints. Here's an example on how to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT constraint on the
columns in question. Problem is, this constraint has system generated name, so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically detect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Tibor Karaszi wrote:

>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =
constraint reference.
The example you posted does not work with 2000. I guess the system tables =
follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =
write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =
suits my immediate needs.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =
though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
sql

Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype =
of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column =
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects =
access this column.
I can change the datatype without any problems from either Enterprise =
Manager or the new Sql Server Management Studio Express. I am working with =
a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio =
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich NietzscheOk, a bit of an update. I have discovered that the error is due to a =
DEFAULT constraint on the columns in question. Problem is, this constraint =
has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =
detect the DEFAULT constraints, remove them, alter the column and add the =
constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =
it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche|||This is why you always should name your constraints. Here's an example on ho
w to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.
TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT
constraint on the
columns in question. Problem is, this constraint has system generated name,
so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically d
etect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it
in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Tibor Karaszi wrote:

>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =
constraint reference.
The example you posted does not work with 2000. I guess the system tables =
follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =
write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =
suits my immediate needs.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =
though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this?
Any help would be appreciated.
Thanks
Vishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this?
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help Required Regarding Formatting of Fields

Hello,
I am using SQL SERVER Reporting Services 2005. I am having a formatting problem. The problem is that I hav a textbox in which numeric field is coming. I want to show the currency format of the Regional Setting of the user pc. I have set the Language setting of the textbox to Default. Now when I am changing the Regional setting from US to UK then in the criteria section the datetime criteria (calendar's value) is changed according to the UK setting, but in the report section the selected date time is not changed (coming in US setting). And the currecy format is also not changing to UK format. Any help ?

Regards,

FurqanSet the format to =User!Language instead of default. I think that will work

help regarding this search

//and i m getting this error

Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int.

====================================

can anyone help me in this i m doing a search in a form ( for ex. u search for clientID n it'll come up in search result n once u select that it'll fill in the form) if anyone knw how to do this plz help me. thank u

Protected

Sub cmdSelect2_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles cmdSelect2.ClickDim sIDAsString

sID = lstResults.SelectedValue

Dim iIDAsInteger' iID = Int32.Parse(sID)'' If iID > 0 ThenDim selectSQLAsString

selectSQL =

"SELECT * FROM contactinfo "

selectSQL &=

"WHERE ClientID=@.ClientID"Dim cmdAsNew SqlCommand(selectSQL, conSR)

cmd.Parameters.AddWithValue(

"@.ClientID", iID)Dim readerAs SqlDataReader

Try

conSR.Open()

reader = cmd.ExecuteReader()

reader.Read()

' Fill the controls.'txtCaseid.Text = reader("Caseid").ToString()

txtClientID.Text = reader(

"ClientID").ToString()

txtFirstname.Text = reader(

"Client_Fname").ToString()

txtLastname.Text = reader(

"Client_Lname").ToString()

txtRace.Text = reader(

"Race").ToString()

txtCounty.Text = reader(

"County_of_origin").ToString()

txtGender.Text = reader(

"Gender").ToString()

txtState.Text = reader(

"State").ToString()

txtReligion.Text = reader(

"Religion").ToString()

txtContactID.Text = reader(

"ContactID").ToString()'reader.Close()'' enable_fields()

lblResults.Text =

""Catch exAs Exception'lblResults.Text = "Error inserting record"

lblResults.Text =

"Error inserting record"

lblResults.Text = ex.Message

Finally

conSR.Close()

EndTry

lblID.Text = sID

phID.Visible =

True

txtID.Text = sID

txtAct.Text =

"edit"

cmdSubmit.Text =

"Update"

lblCurrentAct.Text =

"Update Existing Record"'Else'If iID = 0 Then' lblResults.Text = "Client not found in Contact Information."'Else' lblResults.Text = "Please select a contact from the list."''' End IfEndSub

Hello my friend,

The field you wish to search by has non-numeric data in it so use a string to pass as the parameter instead of an int. You pass in iID so it crashes. Use a string variable instead and you should be fine.

Kind regards

Scotty

Help regarding storing and retrieval of files in sql server

Hi,
Thanks in advance.

I need help(Tutorials or online links) regarding storing and retrieval of files in Sql server (BLOB) using ASP.net and C#.
Secondly,Is it possible to search file in BLOB using SQL server Full text search service.TryKB 309158 -- How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET.

And yes, SQL Server 2000 full text search offers BLOB filtering.

Terri

help regarding SQL connection

i am developing a web application which requires a DB to store various data fields.
Though i am familiar with both web development languages and SQL, I am having difficulty connecting DB to my web pages(jsp). Can any one help with ODBC/JDBC settings required?

regards
sumit dagar

What kind of difficulties you are experiencing?

For an intro how to use JDBC to connect to SQL server see:

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

|||i wanted to know if I have to change control panel->Administrative Tools->ODBC data sources settings.If yess then what would these settings be.

regards
Sumit Dagar

Help regarding sorting report

I have created als.rdlc file for my report it has data from the tableLS ( eg. Month , year, target, actual, etc ), and i have export thatLS.rdlc file in myreport.aspx by using ReportViewer from the toolbox. So when i complie that report.aspx it pull up the report. It pulls up all the data from LS but what i want is sort them by YEAR then MONTH, I Dont know how to do it.? Can any one help me???

Try adding an ORDER BY to the SELECT. If that does not work, change to using a stored procedure.|||I dont know how would you sort it? when i run the report it pulls up the whole database table , so how would i put a search in that?? I am not familer with sorting much.|||

Just change
SELECT A, B, C FROM TABLE

to
SELECT A, B, C FROM TABLE ORDER BY ID

Help regarding passing parameters in SQL and Lookup

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.

2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.

Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?

Please suggest a solution for this too.

Thanks in advance.

Regards,

Aman Anand

aman anand wrote:

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

It'd be alot easier to work out what's going on if you told us:

The full error message|||

Jamie,

Thanks for your reply. My query is mentioned below:

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, ?, GETDATE(), GETDATE(), ?, ?, 0, 0, GETDATE(), GETDATE(), 'LR')

Audit table is being maintained to keep track of ETL parameters.

MASTER_SEQ_ID

SOURCE_RECORD_COUNT and

LOAD_RECORD_COUNT

are the 3 variables defined as int32 and the scope is at package level.

They are mapped to 3 parameters which are also defined with datatype as int.

error is something like it says unable to parse the parameters in the query!

will look at the links mentioned in your reply.

thanks again.

Regards,

Aman

|||

aman anand wrote:

error is something like it says unable to parse the parameters in the query!

I meant to copy and paste the error message!

But regardless, you should be able to achieve this using an expression. The links I provided earlier will help.

-Jamie

|||

Jamie,

Sorry for not copy pasting the error before here goes the entire stuff.

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, @.P_MASTER_SEQ_ID, GETDATE(), GETDATE(), @.P_SOURCE_RECORD_COUNT, @.P_LOAD_RECORD_COUNT, 0, 0, GETDATE(), GETDATE(), 'LR')

Error:

TITLE: SQL Task

The query failed to parse. Must declare the scalar variable "@.P_MASTER_SEQ_ID".

Variables have been defined as int64 and while mapping them to parameters datatype has been given as Long.

Regards,

Aman

|||

Again, try using an expression.

-Jamie

|||

Sometimes it works even though the parser says opposite

Another solution is to build the complete query in a varaible

Turn Evaluate as an expression to True and build it like

"SELECT Dato, Kurs from [" + @.[User::Kurstype] + "$] WHERE (NOT (Kurs IS NULL))"

|||

Hi,

Thanks for your invaluable replies jamie and cgpl.

I tried another workaround for my problem. Used ADO.NET connection manager for the query which was posing problems and passed the parameters as @.P_......,

Now this issue has been sorted.

Thanks again.

Regards,

Aman

Help regarding Parameters selections in reporting services 2005.

Hi Experts,
I am working on Reporting services 2005 and i am new for this
software.
I have some reports, and some reports contains more than one
parameters.
Now my problem starts...
My client want reports with more and more parameters, and
at the same time he does not want to select all of them
He is saying that he may or may not select all the parameters.
and if not select all the parameters then still reports should be
generated.
for his selected parameters only.
Suppose i have one report with 4 parameters and first parameter is
Country
second one is states, third one is city, fourth one coustmers name
etc.
How i can implement this.
Now he is saying that if he select only one parameters in country and
leave others,
then report should be generated for all the coustemers for that
country.
Please help me...
Regards
DineshCheck this thread:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/43cddb294583ab2c/dc7146eb95e741bb?lnk=gst&q=all+union+parameter&rnum=3#dc7146eb95e741bb
Hope it helps.
On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi Experts,
> I am working on Reporting services 2005 and i am new for this
> software.
> I have some reports, and some reports contains more than one
> parameters.
> Now my problem starts...
> My client want reports with more and more parameters, and
> at the same time he does not want to select all of them
> He is saying that he may or may not select all the parameters.
> and if not select all the parameters then still reports should be
> generated.
> for his selected parameters only.
> Suppose i have one report with 4 parameters and first parameter is
> Country
> second one is states, third one is city, fourth one coustmers name
> etc.
> How i can implement this.
> Now he is saying that if he select only one parameters in country and
> leave others,
> then report should be generated for all the coustemers for that
> country.
> Please help me...
> Regards
> Dinesh|||On Mar 30, 5:12 am, "Alphonse" <amphysv...@.gmail.com> wrote:
> Check this thread:http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Hope it helps.
> On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> > Hi Experts,
> > I am working on Reporting services 2005 and i am new for this
> > software.
> > I have some reports, and some reports contains more than one
> > parameters.
> > Now my problem starts...
> > My client want reports with more and more parameters, and
> > at the same time he does not want to select all of them
> > He is saying that he may or may not select all the parameters.
> > and if not select all the parameters then still reports should be
> > generated.
> > for his selected parameters only.
> > Suppose i have one report with 4 parameters and first parameter is
> > Country
> > second one is states, third one is city, fourth one coustmers name
> > etc.
> > How i can implement this.
> > Now he is saying that if he select only one parameters in country and
> > leave others,
> > then report should be generated for all the coustemers for that
> > country.
> > Please help me...
> > Regards
> > Dinesh
Also, I would suggest setting up the report parameters w/a default
value of 'none selected' as part of the dataset that populates the
parameters (most likely w/a union statement in the dataset query).
That way the user can select only what is wanted and the other
parameters automatically use 'none selected.' Then design the stored
procedure/query that populates the report to handle the 'none
selected' accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant