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 : )
Showing posts with label stored. Show all posts
Showing posts with label stored. 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 : )
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 : )
Wednesday, March 28, 2012
How to change table names (Table, Table1, Table2, etc.) returned from SP
Hi all,
How do I customize table names returned from the stored procedure?
For example, I have a stored procedure that is something like this:
SELECT * FROM Employee
SELECT * FROM Employer
SELECT * FROM HealthInsurance
This SP returns multiple tables to the VB.NET application. Now, the names of
the tables retuned in the dataset are: Table, Table1, and Table2. Is there a
way to customize those 3 names so the Table is "Employee", Table1 is
"Employer", Table2 is "HealthInsurance"?
I would then get the Employee data by calling:
ds.Tables("Employee")
instead of:
ds.Tables("Table")
Thanks for your time
Goran Djuranovic
hi Goran,
Goran Djuranovic wrote:
> Hi all,
> How do I customize table names returned from the stored procedure?
> For example, I have a stored procedure that is something like this:
> SELECT * FROM Employee
> SELECT * FROM Employer
> SELECT * FROM HealthInsurance
> This SP returns multiple tables to the VB.NET application. Now, the
> names of the tables retuned in the dataset are: Table, Table1, and
> Table2. Is there a way to customize those 3 names so the Table is
> "Employee", Table1 is "Employer", Table2 is "HealthInsurance"?
> I would then get the Employee data by calling:
> ds.Tables("Employee")
> instead of:
> ds.Tables("Table")
you have to customize the in memory dataset provided by ADO.Net client side,
naming your datatable accordingly to your needs as "TableX" is the default
provided by ADO.Net when no personal provided value is available, so the
TableMapping method come to hand like
Dim da As New OleDbDataAdapter(strSQL, strConn)
da.TableMappings.Add("Tabl1e", "Customers")
Dim ds As New DataSet()
da.Fill(ds)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
How do I customize table names returned from the stored procedure?
For example, I have a stored procedure that is something like this:
SELECT * FROM Employee
SELECT * FROM Employer
SELECT * FROM HealthInsurance
This SP returns multiple tables to the VB.NET application. Now, the names of
the tables retuned in the dataset are: Table, Table1, and Table2. Is there a
way to customize those 3 names so the Table is "Employee", Table1 is
"Employer", Table2 is "HealthInsurance"?
I would then get the Employee data by calling:
ds.Tables("Employee")
instead of:
ds.Tables("Table")
Thanks for your time
Goran Djuranovic
hi Goran,
Goran Djuranovic wrote:
> Hi all,
> How do I customize table names returned from the stored procedure?
> For example, I have a stored procedure that is something like this:
> SELECT * FROM Employee
> SELECT * FROM Employer
> SELECT * FROM HealthInsurance
> This SP returns multiple tables to the VB.NET application. Now, the
> names of the tables retuned in the dataset are: Table, Table1, and
> Table2. Is there a way to customize those 3 names so the Table is
> "Employee", Table1 is "Employer", Table2 is "HealthInsurance"?
> I would then get the Employee data by calling:
> ds.Tables("Employee")
> instead of:
> ds.Tables("Table")
you have to customize the in memory dataset provided by ADO.Net client side,
naming your datatable accordingly to your needs as "TableX" is the default
provided by ADO.Net when no personal provided value is available, so the
TableMapping method come to hand like
Dim da As New OleDbDataAdapter(strSQL, strConn)
da.TableMappings.Add("Tabl1e", "Customers")
Dim ds As New DataSet()
da.Fill(ds)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, March 26, 2012
How to change owner of stored procedure in MS SQL 2000?
For MS SQL 2000, how to change owner of stored procedure? Many thanksOn Thu, 26 Jul 2007 08:16:03 -0700, ikbea
<ikbea@.discussions.microsoft.com> wrote:
>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT|||Have a look at sp_changeobjectowner
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks
<ikbea@.discussions.microsoft.com> wrote:
>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT|||Have a look at sp_changeobjectowner
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks
How to change owner of stored procedure in MS SQL 2000?
For MS SQL 2000, how to change owner of stored procedure? Many thanksOn Thu, 26 Jul 2007 08:16:03 -0700, ikbea
<ikbea@.discussions.microsoft.com> wrote:
>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT|||Have a look at sp_changeobjectowner
--
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks
<ikbea@.discussions.microsoft.com> wrote:
>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT|||Have a look at sp_changeobjectowner
--
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks
How to change owner of stored procedure in MS SQL 2000?
For MS SQL 2000, how to change owner of stored procedure? Many thanks
On Thu, 26 Jul 2007 08:16:03 -0700, ikbea
<ikbea@.discussions.microsoft.com> wrote:
>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT
|||Have a look at sp_changeobjectowner
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks
On Thu, 26 Jul 2007 08:16:03 -0700, ikbea
<ikbea@.discussions.microsoft.com> wrote:
>For MS SQL 2000, how to change owner of stored procedure? Many thanks
Answered in .programming by someone else. Please do not multi-post.
Roy Harvey
Beacon Falls, CT
|||Have a look at sp_changeobjectowner
Andrew J. Kelly SQL MVP
"ikbea" <ikbea@.discussions.microsoft.com> wrote in message
news:1A9E516A-7522-4C73-94B9-2E2160605EFB@.microsoft.com...
> For MS SQL 2000, how to change owner of stored procedure? Many thanks
Wednesday, March 21, 2012
How to change date formats in stored procedure
I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.
thanks
mikeLook up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
Why do you think you need to convert it to short date? Are you trying to truncate the value?|||I am inserting a date value into a table and I dont want the timestamp portion included.|||Thanks! I figured it out using the convert function|||A very similar question was answered yesterday.|||Heck, cascred, this is one of those questions that gets asked every WEEK.
musicmikem, this is a more efficient method of truncating a datatime value, if less intuitive: dateadd(d, datediff(d, 0, [YourDate]), 0)|||Weekly? Hell sometimes it's hourly|||Weekly? Hell sometimes it's hourly Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.|||Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.
You make things so complicated. Why didn't you just say that today the question will be asked at:
create table Numlist (num int identity(1,1) not null primary key)
go
insert Numlist default values
while scope_identity() < 24 insert numlist default values
go
select dateadd(hh, num, '10/4/2005') from numlist
go
drop table numlist
Bill|||Because as any good DBA knows, that method requires a Brain Scan instead of a Clock Seek.|||I'm actually in favor of the simpler:SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS bBonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP|||Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP
I like it. I have never seen this approach before.
You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.
Bill|||You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.Gold star!
Old Unix machines (especially the DEC ones) used to do nearly everything in octal. Three full octets (00-27 octal is 0-23 decimal) will exactly hold all of the hours in a day.
-PatP|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?
Hey. Be careful what you suggest. Things weren't pretty in the days before a relational DBMS came along. We used to do this stuff in COBOL ... without SQL! There are some scars, but not from hernias.|||try dis one.
select convert(varchar,datefield,101) from tablename
thanks
mikeLook up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
Why do you think you need to convert it to short date? Are you trying to truncate the value?|||I am inserting a date value into a table and I dont want the timestamp portion included.|||Thanks! I figured it out using the convert function|||A very similar question was answered yesterday.|||Heck, cascred, this is one of those questions that gets asked every WEEK.
musicmikem, this is a more efficient method of truncating a datatime value, if less intuitive: dateadd(d, datediff(d, 0, [YourDate]), 0)|||Weekly? Hell sometimes it's hourly|||Weekly? Hell sometimes it's hourly Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.|||Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.
You make things so complicated. Why didn't you just say that today the question will be asked at:
create table Numlist (num int identity(1,1) not null primary key)
go
insert Numlist default values
while scope_identity() < 24 insert numlist default values
go
select dateadd(hh, num, '10/4/2005') from numlist
go
drop table numlist
Bill|||Because as any good DBA knows, that method requires a Brain Scan instead of a Clock Seek.|||I'm actually in favor of the simpler:SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS bBonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP|||Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP
I like it. I have never seen this approach before.
You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.
Bill|||You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.Gold star!
Old Unix machines (especially the DEC ones) used to do nearly everything in octal. Three full octets (00-27 octal is 0-23 decimal) will exactly hold all of the hours in a day.
-PatP|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?
Hey. Be careful what you suggest. Things weren't pretty in the days before a relational DBMS came along. We used to do this stuff in COBOL ... without SQL! There are some scars, but not from hernias.|||try dis one.
select convert(varchar,datefield,101) from tablename
How to change Connectionstring in SQLCLR project?
Hi,
I have created a SQLCLR (database) using C#.. named SQLCLRtest
The Connection string is stored in SQLCLRtest.csproj file
here is code of csproj file
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<AssemblyOwner>
</AssemblyOwner>
</PropertyGroup>
<ProjectExtensions>
<VisualStudio>
<FlavorProperties GUID="{c252feb5-a946-4202-b1d4-9916a0590387}">
<DatabaseConnection Name="Data Source=DBserver;Initial Catalog=TestDB;Integrated Security=True" Provider="{91510608-8809-4020-8897-FBA057E22D54}" ConnectionString="01000000D08C9DDF0115D1118C7A00C04FC297EB01000000A2744997FFD51E459D0421E51E830EF30000000002000000000003660000A8000000100000005B59ACF96DA2A587CBFA27595B0F245E0000000004800000A000000010000000BB1D5F562AC3FE7F56F8E57C36E0E7A4B0000000CE828F399233A389D95E2D99B2CAA64DE5F5A19EF0CBB716D195DF60EE38B58B0C07674E2F80538C02ED27200C79A71B0F6F9177E598089CDA95B8DDEEF966A958C6EDE4E72CABBC39941FEED534E3384EF3A4B4A51704726BF5D43F2C3C9BD674885B9675FECD86E54498ED9E1957FCD7DCF0CE8ED99C8529FD9234EB4E760FDD6819E3E42A7771E0A5B18452C01C13976C0DDDF1B5B87D75F0490762C6A004AD093A3DF9210F7D03371D67E4901EB51400000005557E36590040C06F796463ABFEC165D2E60750" />
</FlavorProperties>
</VisualStudio>
</ProjectExtensions>
</Project>
Problem:
I have an sp which start an external process, It is working at my local machine but does not work at remote server.
FACTS:
Local machine has SQLExpress (SQL version) while remote machine has SQL Enterprise ..
SQL service is running under the System Account at both machines..
I have enabled the Sql server Service to interact with desptop, so that it can start a process in GUI mode.
I thin there is something in connectionstring which does not allow the application to connect to server with appropriate rights.
How I can chage this setting .. is it possible to write this info manually..
Please, comments
Thanks
You should be able to change the connection string from the database tab in project properties. When you click on browse, It will allow you to choose from different existing connections or add a new reference.
Thanks,
-Vineet
|||Muna,
Did Vineet's post solve your problem? The reply is correct to my knowledge.
Derek
Monday, March 19, 2012
How to catch stored procedure execution time?
Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:
Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.
ZYT
The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||Below batch query might help you to determine the performance in secounds
declare @.startproc datetime
declare @.endproc datetime
declare @.time integer
select @.startproc = getdate()
exec <stored procedure>
select @.endproc = getdate()
select @.time = DATEDIFF(second, @.startproc, @.endproc)
print str(@.time)|||Pat:
Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.
ZYT
Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!
Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.
I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!
As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).
The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!
-PatP|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.
Thank
ZYT
I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning
As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...
Logging the sproc call from code would not give you a true length due to other resources
I find that log so useful in so many ways
What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.
Well that's pure bull sheet
Blame it on profiler
How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:
By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.
Thanks
ZYT
No experience but Scenario 1in the paper describes exactly this.|||Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH|||Pootle:
I got it.
SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)
can work. or refer http://support.microsoft.com/kb/270599.
Thanks everyone. This is a good post.
ZYT
Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:
Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.
ZYT
The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||Below batch query might help you to determine the performance in secounds
declare @.startproc datetime
declare @.endproc datetime
declare @.time integer
select @.startproc = getdate()
exec <stored procedure>
select @.endproc = getdate()
select @.time = DATEDIFF(second, @.startproc, @.endproc)
print str(@.time)|||Pat:
Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.
ZYT
Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!
Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.
I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!
As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).
The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!
-PatP|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.
Thank
ZYT
I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning
As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...
Logging the sproc call from code would not give you a true length due to other resources
I find that log so useful in so many ways
What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.
Well that's pure bull sheet
Blame it on profiler
How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:
By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.
Thanks
ZYT
No experience but Scenario 1in the paper describes exactly this.|||Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH|||Pootle:
I got it.
SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)
can work. or refer http://support.microsoft.com/kb/270599.
Thanks everyone. This is a good post.
ZYT
Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH
How To Catch Output from SP / Function
I want to catch
the resultset from a stored procedure
or
a table output parameter from a stored procedure
or
a table output from a function
into
a varchar variable.
Any tip?
Not possible?
(it is for mailing the result from a query)
/k"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
You cannot have a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
Not Possible.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||You cannot do that.
You can catch the output from a SP into a #Table. Thats the max you can do.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
> I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||On Fri, 25 Feb 2005 09:35:12 +0100, kurt sune wrote:
(snip)
>Any tip?
>Not possible?
Hi Kurt,
Roji and Vinod already informed you that what you want is not possible.
>(it is for mailing the result from a query)
But if that's what you want, then you should check out xp_sendmail in
Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
the resultset from a stored procedure
or
a table output parameter from a stored procedure
or
a table output from a function
into
a varchar variable.
Any tip?
Not possible?
(it is for mailing the result from a query)
/k"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
You cannot have a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
Not Possible.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||You cannot do that.
You can catch the output from a SP into a #Table. Thats the max you can do.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
> I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||On Fri, 25 Feb 2005 09:35:12 +0100, kurt sune wrote:
(snip)
>Any tip?
>Not possible?
Hi Kurt,
Roji and Vinod already informed you that what you want is not possible.
>(it is for mailing the result from a query)
But if that's what you want, then you should check out xp_sendmail in
Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Monday, March 12, 2012
How to carry forward inventory balances
I have data stored in a table in the following format.
Product# Month Year Inventory Balance
For example the inventory balances for a Product A exists in the
table as follows:
Month Qty on Hand
======= ===========
001.2004 120
004.2004 235
006.2004 89
011.2004 42
003.2005 980
======= ===========
Now if the user is trying to report all inventory balances as of
001.2005 even though I do not have a value in the cube for product A I
expect the system to go and pick the last know stock on hand which is
is 42 the known stock on hand qty in 011.2004
Please advice how I can implement these kind of query to solve this
problem. Bottom line what I expect is for a given product A if there is
data in periods 1, 5 and 9 the system carry forward the balance in period
1 to periods 2,3,4 similarly the balance in period 5 must be carried
over to periods 6,7 & 8 similarly the balance in period 9 must be carried
forward to period 10, 11 and 12
Please help me coding the query SQL or the algorithm to arrive at this
kind of reporting.
Thanks
KarenTry,
use northwind
go
create table t (
colA datetime,
quantity int
)
go
insert into t values('20040101', 120)
insert into t values('20040401', 235)
insert into t values('20040601', 89)
insert into t values('20040701', 42)
insert into t values('20050301', 980)
go
select top 12
identity(int, 1, 1) as number
into
number
from
sysobjects as a
go
create view my_view
as
select
*
from
(
select
ltrim(col_year) + right('00' + ltrim(number.number), 2) + '01'
from
(
select distinct
year(colA)
from
t
) as a(col_year)
cross join
number
) as ym(col_date)
left join
t
on t.colA = ym.col_date
go
select
a.col_date,
coalesce(a.quantity, b.quantity)
from
my_view as a
left join
my_view as b
on a.quantity is null and b.col_date = (select max(c.colA) from t as c
where c.colA < a.col_date and c.quantity is not null)
go
drop view my_view
go
drop table number, t
go
AMB
"Karen Middleton" wrote:
> I have data stored in a table in the following format.
> Product# Month Year Inventory Balance
>
> For example the inventory balances for a Product A exists in the
> table as follows:
>
> Month Qty on Hand
> ======= ===========
> 001.2004 120
> 004.2004 235
> 006.2004 89
> 011.2004 42
> 003.2005 980
> ======= ===========
>
> Now if the user is trying to report all inventory balances as of
> 001.2005 even though I do not have a value in the cube for product A I
> expect the system to go and pick the last know stock on hand which is
> is 42 the known stock on hand qty in 011.2004
> Please advice how I can implement these kind of query to solve this
> problem. Bottom line what I expect is for a given product A if there is
> data in periods 1, 5 and 9 the system carry forward the balance in period
> 1 to periods 2,3,4 similarly the balance in period 5 must be carried
> over to periods 6,7 & 8 similarly the balance in period 9 must be carried
> forward to period 10, 11 and 12
>
> Please help me coding the query SQL or the algorithm to arrive at this
> kind of reporting.
>
> Thanks
> Karen
>
Product# Month Year Inventory Balance
For example the inventory balances for a Product A exists in the
table as follows:
Month Qty on Hand
======= ===========
001.2004 120
004.2004 235
006.2004 89
011.2004 42
003.2005 980
======= ===========
Now if the user is trying to report all inventory balances as of
001.2005 even though I do not have a value in the cube for product A I
expect the system to go and pick the last know stock on hand which is
is 42 the known stock on hand qty in 011.2004
Please advice how I can implement these kind of query to solve this
problem. Bottom line what I expect is for a given product A if there is
data in periods 1, 5 and 9 the system carry forward the balance in period
1 to periods 2,3,4 similarly the balance in period 5 must be carried
over to periods 6,7 & 8 similarly the balance in period 9 must be carried
forward to period 10, 11 and 12
Please help me coding the query SQL or the algorithm to arrive at this
kind of reporting.
Thanks
KarenTry,
use northwind
go
create table t (
colA datetime,
quantity int
)
go
insert into t values('20040101', 120)
insert into t values('20040401', 235)
insert into t values('20040601', 89)
insert into t values('20040701', 42)
insert into t values('20050301', 980)
go
select top 12
identity(int, 1, 1) as number
into
number
from
sysobjects as a
go
create view my_view
as
select
*
from
(
select
ltrim(col_year) + right('00' + ltrim(number.number), 2) + '01'
from
(
select distinct
year(colA)
from
t
) as a(col_year)
cross join
number
) as ym(col_date)
left join
t
on t.colA = ym.col_date
go
select
a.col_date,
coalesce(a.quantity, b.quantity)
from
my_view as a
left join
my_view as b
on a.quantity is null and b.col_date = (select max(c.colA) from t as c
where c.colA < a.col_date and c.quantity is not null)
go
drop view my_view
go
drop table number, t
go
AMB
"Karen Middleton" wrote:
> I have data stored in a table in the following format.
> Product# Month Year Inventory Balance
>
> For example the inventory balances for a Product A exists in the
> table as follows:
>
> Month Qty on Hand
> ======= ===========
> 001.2004 120
> 004.2004 235
> 006.2004 89
> 011.2004 42
> 003.2005 980
> ======= ===========
>
> Now if the user is trying to report all inventory balances as of
> 001.2005 even though I do not have a value in the cube for product A I
> expect the system to go and pick the last know stock on hand which is
> is 42 the known stock on hand qty in 011.2004
> Please advice how I can implement these kind of query to solve this
> problem. Bottom line what I expect is for a given product A if there is
> data in periods 1, 5 and 9 the system carry forward the balance in period
> 1 to periods 2,3,4 similarly the balance in period 5 must be carried
> over to periods 6,7 & 8 similarly the balance in period 9 must be carried
> forward to period 10, 11 and 12
>
> Please help me coding the query SQL or the algorithm to arrive at this
> kind of reporting.
>
> Thanks
> Karen
>
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 out param?
I'd like to execute a stored procedure that needs 5 parameters. One of
them is an OUTPUT parameter. What does the
exec sp_name...
code, executed in QA SQL Server 2000, look like for this? Also, if the
OUTPUT parameter is declared last in the spoc, how can I call it by
name as the first parameter in my exec code?
Thanks,
BrettAssuming you have 5 parameters & the 5th one is an OUTPUT paramter, you can
call the procedure like:
EXEC usp @.p1, @.p2, @.p3, @.p4, @.p5 OUT
SELECT @.p5 ;
I know what you posted is just a sample, but in case hadn't noted, avoid
using sp_ prefix for stored procedures sicen they have certain unfavorable
implications.
Anith
them is an OUTPUT parameter. What does the
exec sp_name...
code, executed in QA SQL Server 2000, look like for this? Also, if the
OUTPUT parameter is declared last in the spoc, how can I call it by
name as the first parameter in my exec code?
Thanks,
BrettAssuming you have 5 parameters & the 5th one is an OUTPUT paramter, you can
call the procedure like:
EXEC usp @.p1, @.p2, @.p3, @.p4, @.p5 OUT
SELECT @.p5 ;
I know what you posted is just a sample, but in case hadn't noted, avoid
using sp_ prefix for stored procedures sicen they have certain unfavorable
implications.
Anith
how to call webservice from tsql?
Hello!
From an stored proc I want to call an existing web-service.
How to do that?
Must it be done using CLR-procs or is there some way in TSQL?
Greetings
Bjorn
Are you really need this...
CLR is one of the best way to invoke your web service. But i never tested it.
If you use sql server 2000 then the MSXML2.XMLHTTP object & sp_OACreate sp is one of the solution for this requirement.
|||Here the sample code,
Declare @.Object as Int;
Declare @.ResponseText as Varchar(8000);
Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @.Object OUT;
Exec sp_OAMethod @.Object, 'open', NULL, 'get',
'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)
'false'
Exec sp_OAMethod @.Object, 'send'
Exec sp_OAMethod @.Object, 'responseText', @.ResponseText OUTPUT
Select @.ResponseText
Exec sp_OADestroy @.Object
|||Hi!
Sorry not writing, that I'm using SS2005...
Makes this some difference?
/Bjorn
|||The Query (given below) can be used in both versions...
For SQL Server 2005 you have to enable the "OLE Automation" option.
Start-> Programs - > Microsoft SQL Server 2005 -> Configuration Tools ->SQL Server Surface Area Configuration
Click the link Surface Area Configuration for Features
Expand the DB -> Database Engine
Select OLE Automation & Check the checkbox Enable OLE Automation..
After the settings completed execute the code...
|||Hi!
I have to use post, but how do I get it to work?
I was told I should look at this:
POST /xyzproxy/alarminsert.asmx HTTP/1.1
Host: 192.168.1.111
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/TestWebService"
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns
<soap:Body>
<TestWebService xmlns="http://tempuri.org/" />
</soap:Body>
</soap:Envelope>
How do I send a request like that using TSQL?
/Bjorn
How to call Stored Procedures?
How to call store procedures in VB 6.0 application?You will probably have to use ADO or a similar databaseaccess technique. Look it up in the online help, it should explain most of what you need to do much clearly than I can.
:o
:o
How to call stored procedure to table?
Hi....
I have problem and I need your help
I stored a procedure in the Projects Folder in my computer
and I want to return the procedure result in a column inside table
How I can do that???
thank you
Please any one can help me??????????
|||Hi,
From your description, you have mentioned that you want to return the procedure result in a column.
Well, do you mean you want to select a column from your database with several rows, and then get these rows from your stored procedure, right?
If so, you can execute a procedure that return rows. To execute a stored procedure that returns row, you can run a TableAdapter query that is configured to run a stored procedure (for example, CustomersTableAdapter.Fill(CustomersDataTable)).
If your application does not use TableAdapters, call the ExecuteReader method on a command object, setting its CommandType property to StoredProcedure. ("Command object" refers to the specific command for the .NET Framework Data Provider that your application is using. For example, if your application is using the .NET Framework Data Provider for SQL Server, the command object would be SqlCommand.)
For more information, see
http://msdn2.microsoft.com/en-us/library/d7125bke(VS.80).aspx
Thanks.
how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?Inside your first Stored Procedure:
exec <name of SP to run> <Eventual Parameters this SP requires>
Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.
Subscribe to:
Posts (Atom)