I have a database table and it has a column called ttt_id which has an identity seed. Now the Id value is 2845746 and if I insert the new record the value would be 2845747. But I want to change that to 2865740 instead of 2845747. How can do that?
Thanks.Check out DBCC CHECKIDENT in BOL. It should give you everything you need.|||-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT <TableName> ON
GO
INSERT INTO <TableName> (<FieldName>) VALUES(2845747)
GO
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Friday, March 30, 2012
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
sql
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )
sql
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
How to change the Default value of a Column
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )
How to change the default no. of errorlogs from 7 to new value?
How to change the default no. of errorlogs from 7 to new value?This is a multi-part message in MIME format.
--=_NextPart_000_0D2C_01C393E9.9D7E29A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In EM, click on Management. Right-click on SQL Server Logs->Configure.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Samir" <samir@.yahoo.com> wrote in message =news:07dd01c39407$d988a9c0$a001280a@.phx.gbl...
--=_NextPart_000_0D2C_01C393E9.9D7E29A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
In EM, click on Management. =Right-click on SQL Server Logs->Configure.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Samir" wrote in message news:07dd01c39407$d9=88a9c0$a001280a@.phx.gbl...
--=_NextPart_000_0D2C_01C393E9.9D7E29A0--
--=_NextPart_000_0D2C_01C393E9.9D7E29A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In EM, click on Management. Right-click on SQL Server Logs->Configure.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Samir" <samir@.yahoo.com> wrote in message =news:07dd01c39407$d988a9c0$a001280a@.phx.gbl...
--=_NextPart_000_0D2C_01C393E9.9D7E29A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
In EM, click on Management. =Right-click on SQL Server Logs->Configure.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Samir"
--=_NextPart_000_0D2C_01C393E9.9D7E29A0--
Wednesday, March 28, 2012
How to change the caption of parameter at run time
Hi,
I would like to change the caption of parameter based upon the other
parameter value in RS 2000 / 2005.
Thankstry using conditional logic
eg
=iif(Parameters!FieldName.Value = testvalue, value if true , value if
false)|||oops posted to wrong thread - sorry
andygadget wrote:
> try using conditional logic
> eg
> =iif(Parameters!FieldName.Value = testvalue, value if true , value if
> false)
I would like to change the caption of parameter based upon the other
parameter value in RS 2000 / 2005.
Thankstry using conditional logic
eg
=iif(Parameters!FieldName.Value = testvalue, value if true , value if
false)|||oops posted to wrong thread - sorry
andygadget wrote:
> try using conditional logic
> eg
> =iif(Parameters!FieldName.Value = testvalue, value if true , value if
> false)
Monday, March 26, 2012
How to change query timeout?
I'm reporting from a web service that takes so long to return that Reporting
Services times out... how can I increase the timeout value when querying for
data?
TIA - ekkisDont try to increase the timeout, instead optimize your query or use stored
proc.
Amarnath
"ekkis" wrote:
> I'm reporting from a web service that takes so long to return that Reporting
> Services times out... how can I increase the timeout value when querying for
> data?
> TIA - ekkis|||perhaps I didn't explain myself correctly. I am reporting from a web service
so there are no stored procedures involved and the "query" is simply the
request to fetch data from the web service.
I have no control over the foreign server so I need to increase the timeout
value. How can I do that?
Services times out... how can I increase the timeout value when querying for
data?
TIA - ekkisDont try to increase the timeout, instead optimize your query or use stored
proc.
Amarnath
"ekkis" wrote:
> I'm reporting from a web service that takes so long to return that Reporting
> Services times out... how can I increase the timeout value when querying for
> data?
> TIA - ekkis|||perhaps I didn't explain myself correctly. I am reporting from a web service
so there are no stored procedures involved and the "query" is simply the
request to fetch data from the web service.
I have no control over the foreign server so I need to increase the timeout
value. How can I do that?
Wednesday, March 21, 2012
How to change data in a crystal report
Hi all,
I have developed a report where i have field called Required. It has a value either 1 or 0 in database. Now I want to display 'Required' if the value is 1 and 'Not Required' if the value is 0. Plz help me to do so...
Thanks in Advance..
PrashanthHi all,
I came to know that this problem can be sorted out by writing a formula. But I dont know how to write a formula. Anybody help me...
IIF (({HY_PDC_PARAMETER_SET_DTL.REQUIRED}= '1'),'Required' ,'NotRequired' )
When I write so its displayin Required for all rows...
Thanks in Advance,
Prashanth.M|||Hi
there's nothing wrong with the formula u have written,
but if the field is numeric type then you don't need the quotes and if it is a bit data type then you need to check the field with true or false.
You can also try the formula in this way :
if {table.Required}=1 then 'Required' else 'Not Required'
I have developed a report where i have field called Required. It has a value either 1 or 0 in database. Now I want to display 'Required' if the value is 1 and 'Not Required' if the value is 0. Plz help me to do so...
Thanks in Advance..
PrashanthHi all,
I came to know that this problem can be sorted out by writing a formula. But I dont know how to write a formula. Anybody help me...
IIF (({HY_PDC_PARAMETER_SET_DTL.REQUIRED}= '1'),'Required' ,'NotRequired' )
When I write so its displayin Required for all rows...
Thanks in Advance,
Prashanth.M|||Hi
there's nothing wrong with the formula u have written,
but if the field is numeric type then you don't need the quotes and if it is a bit data type then you need to check the field with true or false.
You can also try the formula in this way :
if {table.Required}=1 then 'Required' else 'Not Required'
how to change color of a field if it is duplicate..........
hi guys
how can i change the fore color of a field value if there is a duplicate of it.
thanks in advance but only for those who help me (Lolz) ;)
Sillytry like
in format editor in the font tab write a formula on color [x+2]
IF (field1) = PREVIOUS(field1) THEN
RGB(255,0,0)
ELSE
RGB(0,0,0)
Gragi|||hi grag
thanks for ur reply but problem in my case is that
the duplicate value can reside any where in the list.
how can i change the fore color of a field value if there is a duplicate of it.
thanks in advance but only for those who help me (Lolz) ;)
Sillytry like
in format editor in the font tab write a formula on color [x+2]
IF (field1) = PREVIOUS(field1) THEN
RGB(255,0,0)
ELSE
RGB(0,0,0)
Gragi|||hi grag
thanks for ur reply but problem in my case is that
the duplicate value can reside any where in the list.
Monday, March 19, 2012
How to cast the value in C# resulted from max() command in SQL Server Developer
Could anyone help of how to cast the value in C# resulted from max() command. To complicate the matter, from the query result I see in the Microsoft SQL Server Management Studio, if there are records the value is number. But if there are no records, the value is NULL. How do I handle these two possible different conditions?
I have tried:
- stringtest = (string)reader["MaxOrderID"];
- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);
- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();
all failed. And what do I do if the value is NULL. And also how do I do to cope with these two different possible conditions?
For example, I have the following code:
command.CommandText ="Select max(OrderID) as 'MaxOrderID' from [Order]";command.CommandType =CommandType.Text;
command.Connection = conn;
command.Connection.Open();
reader = command.ExecuteReader();
reader.Read();
? orderID = ?reader["MaxOrderID"];
Select MAX(ISNULL(OrderID,0) as MaxOrderID from [Order] try this statment and retest your three statments again...
- stringtest = (string)reader["MaxOrderID"];
- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);
- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();
|||I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.
I can see that it should be 0.
|||hi dedyandy,
dedyandy:
I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.
what you are getting is correct, max will return value if there's any record in table else it wont return anything i.e. its a null. you can either use 1 as default value in case there are no records or in case you've procedure then you can check something like
if @.@.Rowcount = 0 Select 1 as 'MaxOrderID'
thanks,
satish.
|||
Thanks Satish. Yes, I will use Count function first. If there are records, I will use AVG function, else return 1.
Andy.
|||
cheers
thanks,
Satish.
How to cast empty string
I want to replace a column value with a null if the string is empty. I would have thought this simple expression would do it:
RTRIM([FromContractSymbol]) == "" ? NULL(DT_STR, 0, 1252) : (DT_STR, 6, 1252)FromContractSymbol
Yet, I get the following error:
For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operators. The expression "...see above..." has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation.
The expression works if I replace the NULL(DT_STR, 0, 1252) with say "A" and the expression works on other non-string columns. (As in "NULL(DT_I1) : (DT_I1)100")
The error does explain how to solve it. Although you have specified the type for the NULL you have to cast it.
So if you change your line to
RTRIM([FromContractSymbol]) == "" ? (DT_STR, 6, 1252)NULL(DT_STR, 6, 1252) : (DT_STR, 6, 1252)FromContractSymbol
It should work
How to CASE a SmallInt to a Varchar value
Why does this syntax work for bit but not for smallint ?
-- This works OK - Setting = BIT
CASE (dbo.tblAssessment.Setting)
WHEN 1 THEN 'Internal'
WHEN 0 THEN 'External'
END AS Setting,
-- This fails - error converting value 'N/A' to column of datatype smallint
-- Credit = SMALLINT
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN 'N/A'
ELSE dbo.tblAssessment.Credit
END AS Credit,
Thanks.Hi
It is expecting to return a smallint as one of the ELSE's in a smallint.
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN CONVERT(CHAR(10), 'N/A' )
ELSE CONVERT(CHAR(10), dbo.tblAssessment.Credit)
END AS Credit
Regards
Mike
"hals_left" wrote:
> Why does this syntax work for bit but not for smallint ?
>
> -- This works OK - Setting = BIT
> CASE (dbo.tblAssessment.Setting)
> WHEN 1 THEN 'Internal'
> WHEN 0 THEN 'External'
> END AS Setting,
> -- This fails - error converting value 'N/A' to column of datatype smallin
t
> -- Credit = SMALLINT
> CASE (dbo.tblAssessment.Credit)
> WHEN 101 THEN 'N/A'
> ELSE dbo.tblAssessment.Credit
> END AS Credit,
> Thanks.
>
-- This works OK - Setting = BIT
CASE (dbo.tblAssessment.Setting)
WHEN 1 THEN 'Internal'
WHEN 0 THEN 'External'
END AS Setting,
-- This fails - error converting value 'N/A' to column of datatype smallint
-- Credit = SMALLINT
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN 'N/A'
ELSE dbo.tblAssessment.Credit
END AS Credit,
Thanks.Hi
It is expecting to return a smallint as one of the ELSE's in a smallint.
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN CONVERT(CHAR(10), 'N/A' )
ELSE CONVERT(CHAR(10), dbo.tblAssessment.Credit)
END AS Credit
Regards
Mike
"hals_left" wrote:
> Why does this syntax work for bit but not for smallint ?
>
> -- This works OK - Setting = BIT
> CASE (dbo.tblAssessment.Setting)
> WHEN 1 THEN 'Internal'
> WHEN 0 THEN 'External'
> END AS Setting,
> -- This fails - error converting value 'N/A' to column of datatype smallin
t
> -- Credit = SMALLINT
> CASE (dbo.tblAssessment.Credit)
> WHEN 101 THEN 'N/A'
> ELSE dbo.tblAssessment.Credit
> END AS Credit,
> Thanks.
>
Monday, March 12, 2012
How to capture the correct identity value
I have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.
blumonde
Have you tried this?
SELECT SCOPE_IDENTITY()
INSERT INTO Table1 ...
INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID
SELECT Table2ID,Table1ID
FROM Table2
WHERE Table2ID=SCOPE_IDENTITY()
|||Thanks for your response, gentlemen.
I tried 'Select @.getKEY = @.@.IDENTITY' right after the first insert and it seems to work pretty good so far. I hope I am doing it the right way. However, I am not sure my method is consistant when several users inserting at the same time.
I think it is better to use 'scope_identity' and follow Motley's method above. I think it can handle multi-tasking better than my method.
Motley, what is the Select below for? I have to use it after the second insert?
SELECT Table2ID,Table1ID
FROM Table2
WHERE Table2ID=SCOPE_IDENTITY()
blumonde
|||
Motley:
INSERT INTO Table1 ...
INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID
SELECT Table2ID,Table1ID
FROM Table2
WHERE Table2ID=SCOPE_IDENTITY()
Motley, what is the Select below for? I have to use it after the second insert? Thanks.
SELECT Table2ID,Table1ID
FROM Table2
WHERE Table2ID=SCOPE_IDENTITY()
blumonde
|||The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.|||
The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.
Thank you.
blumonde
How to capture return value from 'execute'
declare @.table varchar(100);
declare @.q varchar(100);
declare @.key bigint;
select @.table = 'key_table';
select @.q = 'select key from ' + @.table;
select @.key = exec(@.q); -> not working.
Check in books online; you'll find that the string execute version of the EXEC command does not provide the same ability to capture a return value as does the execution of a stored procedure. Sorry.|||In that case, can you suggest an alternative for the my requirement. I want to get the return value of a select statement constructed dynamically.
Thanks,
|||create a temp table and use this type
insert into #tempTable
exec ( @.yourExecString )
Also, understand that using the EXEC ( @.yourExecString ) syntax might leave you subject to SQL INJECTION attacks.
|||Use sp_executesql instead EXEC(...). You can use output paraeters with this sp.
declare @.table sysname;
declare @.q nvarchar(100);
declare @.key bigint;
select @.table = N'key_table';
select @.q = 'select @.key = key from dbo.[' + @.table + N']';
exec sp_executesql @.q, N'@.key bigint output', @.key output;
select @.key
go
Be careful with sql injection.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
Friday, March 9, 2012
How to call report via url in RSReportHost.exe
I am calling my reports via URL, but it presents the report in IE with the url so the users might change the parameters value in IE addressbar. but i see RSReportHost.exe does'nt show the url at all.
Is it possible to show or call a report via URL using RSReportHost.exe
I am using VS 2003 with SQL RS 2005, i don't have a reportviewer control available to use.
Please help guys.
Thank you fery much.
RSReportHost is a desktop application designed for debugging reports. So, the short answer to your question is no. Moreover, URL access is not a very secure way to request reports. The Report ViewerASP.NET control does a reasonable job to hide the parameter values during reposts but it is not foolproof as well. Regardless of the fact that you don’t use the ASP.NET Report, RS 2005 URL addressability uses it behind the scenes by redirecting the user to the ReportViewer.aspx page. So, one way to mitigate the security risk with parameters is to request the report without parameters and rely on the Report Viewer to handle them. Stricter security requirements may require the web application to handle the parameters on the server side.
|||Thank you very much Teo.
But VS 2003 does'nt have a reportviewer to use with RS 2005.
Microsoft only released the viewer with vs 2005.
I have a very big project fully developed using vs 2003 and lot of state and federal construction projects are using the tool. and also we have integrated third party controls which specifically uses framework 1.1. i don't have an open option to switch to vs 2005 soon might have to wait until all the migration related issues gets cleared.
my current options are to use render webservices to load reports via pdf streams. which i am using. are there any other methods available using vs 2003 and sql rs 2005 please help.
Thank you once again. i read about you on google a lot.
|||Just to clarify, I was referring to the server ReportViewer.aspx page which the user is automatically redirected to with URL addressability. So, if the user clicks on a URL link which points to a report (e.g. http://localhost/reportserver?%2fAdventureWorks+Sample+Reports%2fCompany+Sales&rs:Command=Render) , the server will redirect her to that page which uses the ReportViewer ASP.NET Report Viewer control behind the scenes. Therefore, you don’t have to upgrade to VS.NET 2005 at all.
|||Thks Teo, But the problem with URL is the users can manipulate the url parameters by themselves in the IE address bar. I don't want the users manipulate the url's.
|||
True. You have different options to address this depending on the security risk.
1. If you think that hacking the report URL goes beyond the skills of your users, you may be fine with the ReportViewer parameter hiding.
2. You can use the user identity (User!UserID) to filter the parameter available values and/or report data (row-level security).
3. You can configure the Report Server to be accessable from your application server only and not directly from the end users.
4. You can use a trusted account between your application server and Report Server. All report requests will go under this account and you will secure the report catalog accordingly. These security policies will prevent the end users from requesting reports directly.
Wednesday, March 7, 2012
How to call a function from a column formula in my MS SQL table
Good day!
What is the syntax on calling a function from a column formula in an MS SQL table.
I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?
SELECT dbo.FunctionName([Parameter1, Parameter2])
But i can't save the table, "Error validating the formula".
Pls. help
Thanks a lot.<edit> Never mind, I misunderstood what you are doing.
I'm afraid I have no advice.|||It may or may not work depending on what you are trying to do. You can use a udf and define the result as simply:
DEFAULT (dbo.udfMyFunction('SomeParam','OtherParam'))
However, SomeParam and OtherParam must be constants or system functions (like suser_sname() or host_name()). They can't be names of columns in your table.
Regards,
hmscott|||Or perhaps he want a computed column:
create table foo (
id int,
hash as dbo.getHash(id),
...
);
What is the syntax on calling a function from a column formula in an MS SQL table.
I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?
SELECT dbo.FunctionName([Parameter1, Parameter2])
But i can't save the table, "Error validating the formula".
Pls. help
Thanks a lot.<edit> Never mind, I misunderstood what you are doing.
I'm afraid I have no advice.|||It may or may not work depending on what you are trying to do. You can use a udf and define the result as simply:
DEFAULT (dbo.udfMyFunction('SomeParam','OtherParam'))
However, SomeParam and OtherParam must be constants or system functions (like suser_sname() or host_name()). They can't be names of columns in your table.
Regards,
hmscott|||Or perhaps he want a computed column:
create table foo (
id int,
hash as dbo.getHash(id),
...
);
How to Calculate Sum for distinct values in MDX
Hi ALL,
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.
I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.
Data example
P_Code Cus Prpty_Id Mrkt Val
3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000
Distinct Sum $60,000
Thanks in Advance
BrijeshWhat about SELECT SUM(DISTINCT Mrkt)
FROM tbl
GROUP BY P_Code|||Nope: http://weblogs.sqlteam.com/jeffs/archive/2007/07/31/60274.aspx
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.
I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.
Data example
P_Code Cus Prpty_Id Mrkt Val
3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000
Distinct Sum $60,000
Thanks in Advance
BrijeshWhat about SELECT SUM(DISTINCT Mrkt)
FROM tbl
GROUP BY P_Code|||Nope: http://weblogs.sqlteam.com/jeffs/archive/2007/07/31/60274.aspx
Friday, February 24, 2012
How to calculate category value - perhaps use subqueries?
Hello all - I am trying to create a view that will calculate a category fiel
d
value based on several conditions regarding another field.
i.e. having a table PayCodes (employeeID, paycode)
employeeID paycode
--
1 01
1 02
1 02S
1 03S
1 71
2 01
2 02S
2 71
3 02
3 03H
4 01
4 02
I need to create a view that will output employeeID and overtimeType where
overtimeType = 1 if an employeeID has 02S or 02H
overtimeType = 2 if an employeeID has 03S or 03H
overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
So given the above table, the view should return:
employeeID overtimeType
--
1 3
2 1
3 2
4 0
Any ideas?
Thanks in advance!!
Hellman.On Tue, 4 Oct 2005 10:59:03 -0700, Hellman wrote:
(snip)
Hi Hellman,
I just posted a reply to your question in the .mseq group.
Please post your questions to one group only. And if you really feel
that a question fits two groups, use the crossposting ability of your
software to post one message to both groups at once, so that others will
see if there's already a reply in the other group, and we have all
reactions in one thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
d
value based on several conditions regarding another field.
i.e. having a table PayCodes (employeeID, paycode)
employeeID paycode
--
1 01
1 02
1 02S
1 03S
1 71
2 01
2 02S
2 71
3 02
3 03H
4 01
4 02
I need to create a view that will output employeeID and overtimeType where
overtimeType = 1 if an employeeID has 02S or 02H
overtimeType = 2 if an employeeID has 03S or 03H
overtimeType = 3 if an employeeID has (02S or 02H) and (03S or 03H)
overtimeType = 0 if an employeeID has none of 02S, 02H, 03S, 03H
So given the above table, the view should return:
employeeID overtimeType
--
1 3
2 1
3 2
4 0
Any ideas?
Thanks in advance!!
Hellman.On Tue, 4 Oct 2005 10:59:03 -0700, Hellman wrote:
(snip)
Hi Hellman,
I just posted a reply to your question in the .mseq group.
Please post your questions to one group only. And if you really feel
that a question fits two groups, use the crossposting ability of your
software to post one message to both groups at once, so that others will
see if there's already a reply in the other group, and we have all
reactions in one thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)