Inserting a 0

Hi,
Im having trouble with the money data type, for instance I have a column that calculates a price but it will output the price as 470.2 instead of 470.20 which is how I want it displayed on a web page.

Anyone know how to automatically insert a zero on the end of the price?

THanksNoone knows how to insert zeros on the end of numbers??|||I'm gettin '470.2000'
from this simple query I ran from query analyser

declare @.dollar as money
set @.dollar=470.2
select @.dollar

I can't understand why your only getting 470.2. Maybe you can write your calculation query for us to figure?|||I figured it out but thanks anyhow : )sql

Inserted Rows count from SSIS not like table Rows Count

Hi all

i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................

any ideas

So from the error output you are getting 59 123 019 rows? How are you counting this?
What are you using to insert the rows?|||Mapping from values i get and columns on distination table|||How are you counting the rows inserted?|||

I figuer out the Reasone my lookup error output configure to ignore the error and the error was "can't insert duplicated key" i change the error output to insert duplicated rows in flat file but i don't test it yet , i will hint with updates , hope that's resolve the problem

wish me luck

Phil

I Count rows from distination table "Select Count "

|||

Hosam Abdel Wahab wrote:

Phil

I Count rows from distination table "Select Count "

I mean, how are you counting the rows inserted from within SSIS?

But, yes, it sounds like you are on the correct path to figuring out your problem.

INSERT...SELECT and OUTPUT question

Why would this syntax be valid and the second one is not (below)?
begin tran
USE AdventureWorks
GO
DECLARE @.MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @.MyTableVar
--OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800 and 810;
--Display the results of the table variable.
SELECT ProductID, ProductName, PhotoID, ProductModelID
FROM @.MyTableVar;
GO
rollback
This is not valid. Why not? Am I missing something?
Is it that only with UPDATE/DELETE other fields from JOIN can be in output ?
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales money NOT NULL
);
GO
INSERT INTO dbo.EmployeeSales
OUTPUT INSERTED.EmployeeID,
INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales,
e.EmployeeID
SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.1
0
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GOFarmer (someone@.somewhere.com) writes:
> Why would this syntax be valid and the second one is not (below)?
> DELETE Production.ProductProductPhoto
> OUTPUT DELETED.ProductID,
> p.Name,
> p.ProductModelID,
> DELETED.ProductPhotoID
> INTO @.MyTableVar
> FROM Production.ProductProductPhoto AS ph
> JOIN Production.Product as p ON ph.ProductID = p.ProductID
> WHERE p.ProductID BETWEEN 800 and 810;
>...
> INSERT INTO dbo.EmployeeSales
> OUTPUT INSERTED.EmployeeID,
> INSERTED.LastName,
> INSERTED.FirstName,
> INSERTED.CurrentSales,
> e.EmployeeID
> SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD,
> sp.SalesYTD * 1.10
> FROM HumanResources.Employee AS e
>...
The syntax diagram in Books Online gives us:
<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
In the comments section we find:
from_table_name
Is a column prefix that specifies a table included in the FROM clause
of a DELETE or UPDATE statement that is used to specify the rows to
update or delete.
Thus, Books Online clearly says that you cannot use e.EmployeeID in the
OUTPUT clause of an INSERT statement.
Then remains the question why it is so. We look at the syntax diagram
for INSERT:
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
[ TOP ( expression ) [ PERCENT ] ]
[ INTO]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
[; ]
Note here that the SELECT statement appears in this grammar as a
derived table. A derived table has the property, that it does not
see things outside of if, and the outside cannot look in.
In looser terms, we can simply say that the OUTPUT clause is part of
the INSERT clause in a way that the SELECT statement is not, and thus
does not have visibility of what is in the SELECT statement.
For DELETE or UPDATE it's a different matter as the FROM clause are
part of the DELETE and UPDATE statments themselves.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you,
very good explanation on your part. I see it now. I should have read more
carefully.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD66F91C3BYazorman@.127.0.0.1...
> Farmer (someone@.somewhere.com) writes:
> The syntax diagram in Books Online gives us:
> <column_name> ::=
> { DELETED | INSERTED | from_table_name } . { * | column_name }
> In the comments section we find:
> from_table_name
> Is a column prefix that specifies a table included in the FROM clause
> of a DELETE or UPDATE statement that is used to specify the rows to
> update or delete.
> Thus, Books Online clearly says that you cannot use e.EmployeeID in the
> OUTPUT clause of an INSERT statement.
> Then remains the question why it is so. We look at the syntax diagram
> for INSERT:
> [ WITH <common_table_expression> [ ,...n ] ]
> INSERT
> [ TOP ( expression ) [ PERCENT ] ]
> [ INTO]
> { <object> | rowset_function_limited
> [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
> }
> {
> [ ( column_list ) ]
> [ <OUTPUT Clause> ]
> { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
> | derived_table
> | execute_statement
> }
> }
> | DEFAULT VALUES
> [; ]
> Note here that the SELECT statement appears in this grammar as a
> derived table. A derived table has the property, that it does not
> see things outside of if, and the outside cannot look in.
> In looser terms, we can simply say that the OUTPUT clause is part of
> the INSERT clause in a way that the SELECT statement is not, and thus
> does not have visibility of what is in the SELECT statement.
> For DELETE or UPDATE it's a different matter as the FROM clause are
> part of the DELETE and UPDATE statments themselves.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Farmer, try this one
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL
);
GO
CREATE TABLE #Temp ( EmployeeID int not null,
LastName nvarchar(20) NOT NULL)-
INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
SELECT e.EmployeeID, c.LastName
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
select * from #Temp
go
"Farmer" <someone@.somewhere.com> wrote in message
news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Thank you,
> very good explanation on your part. I see it now. I should have read more
> carefully.
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns976FD66F91C3BYazorman@.127.0.0.1...
>|||Thanks
You have missed my point though. This does not work and this can be a field
from a JOIN table from FROM statement.
OUTPUT e.EmployeeID, INSERTED.LastName INTO #Temp
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:O$1jqNeNGHA.3936@.TK2MSFTNGP12.phx.gbl
..
> Farmer, try this one
>
> IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
>
> DROP TABLE dbo.EmployeeSales;
>
> GO
>
> CREATE TABLE dbo.EmployeeSales
>
> ( EmployeeID nvarchar(11) NOT NULL,
>
> LastName nvarchar(20) NOT NULL
>
> );
>
> GO
>
> CREATE TABLE #Temp ( EmployeeID int not null,
>
> LastName nvarchar(20) NOT NULL)-
>
> INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
>
> OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
>
> SELECT e.EmployeeID, c.LastName
>
> FROM HumanResources.Employee AS e
>
> INNER JOIN Sales.SalesPerson AS sp
>
> ON e.EmployeeID = sp.SalesPersonID
>
> INNER JOIN Person.Contact AS c
>
> ON e.ContactID = c.ContactID
>
> WHERE e.EmployeeID LIKE '2%'
>
> ORDER BY c.LastName, c.FirstName;
>
>
>
> select * from #Temp
>
> go
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
>
>

Insert XML RAW's output to a table

Under SQL 2000 i would like to convert and XML RAW output to text type
by using "convert" and/or insert the data into a table, is this
posible?
i.e. this statement runs ok under SQL 2k5 but fails under SQL 2k
SELECT CONVERT( text, (SELECT * FROM MyTable FOR XML RAW) )
SQL2000 reports:
-- Msg 170, Level 15, State 1, Line 1
-- Line 1: Incorrect syntax near 'XML'.
Thanks in advance.
Rod wrote:

> i.e. this statement runs ok under SQL 2k5 but fails under SQL 2k
> SELECT CONVERT( text, (SELECT * FROM MyTable FOR XML RAW) )
> SQL2000 reports:
> -- Msg 170, Level 15, State 1, Line 1
> -- Line 1: Incorrect syntax near 'XML'.
Have you tried to select into a variable first and then convert that
variable?

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||This can't be done on the server in SQL Server 2000. You would have to use
a client connection to select the data and then push it back to the server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OhRh%23m%23vHHA.4796@.TK2MSFTNGP04.phx.gbl...
> Rod wrote:
>
> Have you tried to select into a variable first and then convert that
> variable?
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||On 6 jul, 23:09, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> This can't be done on the server in SQL Server 2000. You would have to use
> a client connection to select the data and then push it back to the server.
>
Found this to be true, under SQL Server 2000 it's not possible to use
XML RAW inside a subquery, trying to do it will throw an error.
(still, the same unmodified
clause may work under SQL Server 2005)
Thanks

Insert XML RAW's output to a table

Under SQL 2000 i would like to convert and XML RAW output to text type
by using "convert" and/or insert the data into a table, is this
posible?
i.e. this statement runs ok under SQL 2k5 but fails under SQL 2k
SELECT CONVERT( text, (SELECT * FROM MyTable FOR XML RAW) )
SQL2000 reports:
-- Msg 170, Level 15, State 1, Line 1
-- Line 1: Incorrect syntax near 'XML'.
Thanks in advance.Rod wrote:

> i.e. this statement runs ok under SQL 2k5 but fails under SQL 2k
> SELECT CONVERT( text, (SELECT * FROM MyTable FOR XML RAW) )
> SQL2000 reports:
> -- Msg 170, Level 15, State 1, Line 1
> -- Line 1: Incorrect syntax near 'XML'.
Have you tried to select into a variable first and then convert that
variable?
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||This can't be done on the server in SQL Server 2000. You would have to use
a client connection to select the data and then push it back to the server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OhRh%23m%23vHHA.4796@.TK2MSFTNGP04.phx.gbl...
> Rod wrote:
>
> Have you tried to select into a variable first and then convert that
> variable?
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||On 6 jul, 23:09, "Roger Wolter[MSFT]" <rwol...@.online.microsoft.com>
wrote:
> This can't be done on the server in SQL Server 2000. You would have to us
e
> a client connection to select the data and then push it back to the server
.
>
Found this to be true, under SQL Server 2000 it's not possible to use
XML RAW inside a subquery, trying to do it will throw an error.
(still, the same unmodified
clause may work under SQL Server 2005)
Thankssql

INSERT the OUTPUT of update statement - a neat trick that doesn't work?

Here's the code
ALTER procedure [dbo].[BalanceUpdate]
As
declare @.DateX datetime
set @.DateX = CONVERT(varchar(10),dateadd(hour,6,getda
te()),120)
--INSERT INTO Payment (Summ, UserID, Reason, RelatedOrderID, DT)
UPDATE [User] SET Balance = Balance - Cost, PaidThru = DATEADD(month,
1, ISNULL(PaidThru, @.DateX))
OUTPUT PayPlan.Cost, inserted.ID, 'Monthly payment blah blah blah',
null, @.DateX
FROM [User]
INNER JOIN PayPlan ON Payplan.ID = PayplanID
WHERE (PaidThru <= @.DateX or PaidThru is null) AND (Cost = 0 OR
(Balance >= Cost)) and Confirmed = 1
You see the commented insert statement - in theory it should work, in
practice it says syntac error. I tried surrounding the update with
SELECT * FROM (...) tmp but it's still syntax error.
Is there any way to make this trick work? I don't want to write an
ugly cursor! Woops. Problem solved, nm|||Could you elaborate? Where was the syntax error?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1182716922.462969.87000@.k79g2000hse.googlegroups.com...
> Woops. Problem solved, nm
>

INSERT the OUTPUT of update statement - a neat trick that doesn't work?

Here's the code
ALTER procedure [dbo].[BalanceUpdate]
As
declare @.DateX datetime
set @.DateX = CONVERT(varchar(10),dateadd(hour,6,getdate()),120)
--INSERT INTO Payment (Summ, UserID, Reason, RelatedOrderID, DT)
UPDATE [User] SET Balance = Balance - Cost, PaidThru = DATEADD(month,
1, ISNULL(PaidThru, @.DateX))
OUTPUT PayPlan.Cost, inserted.ID, 'Monthly payment blah blah blah',
null, @.DateX
FROM [User]
INNER JOIN PayPlan ON Payplan.ID = PayplanID
WHERE (PaidThru <= @.DateX or PaidThru is null) AND (Cost = 0 OR
(Balance >= Cost)) and Confirmed = 1
You see the commented insert statement - in theory it should work, in
practice it says syntac error. I tried surrounding the update with
SELECT * FROM (...) tmp but it's still syntax error.
Is there any way to make this trick work? I don't want to write an
ugly cursor!
Woops. Problem solved, nm
|||Could you elaborate? Where was the syntax error?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1182716922.462969.87000@.k79g2000hse.googlegro ups.com...
> Woops. Problem solved, nm
>

Insert stored procedure with output parameter

Hello everyone.

I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.

Someone does know how to do this?

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

|||

Thanks a lot!

While you posted this I solved it out using SELECT @.@.Identity

Is there any diference with the solution you gave me?

|||

Quote from BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

Konstantin Kosinsky wrote:

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

Insert sp_spaceused output into table

Hello,
I need to get database space space usage information into one table and then
format the output in one report.
Im using the sp_spaceused stored procedure to get this information but
because it sends the output separate in two blocks i cant insert it into one
table.
I send you what im doing but it doesnt function.
create table dbsize
(
database_name varchar(128),
database_size varchar(18),
[unallocated space] varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
insert into dbsize exec sp_spaceused
Can you help me?
Thanks and best regards
You could try gathering the information you need from the system tables.
-Argenis
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:796CCE66-1418-4072-9DFA-3AD3F63E95DF@.microsoft.com...
> Hello,
> I need to get database space space usage information into one table and
then
> format the output in one report.
> Im using the sp_spaceused stored procedure to get this information but
> because it sends the output separate in two blocks i cant insert it into
one
> table.
> I send you what im doing but it doesnt function.
> create table dbsize
> (
> database_name varchar(128),
> database_size varchar(18),
> [unallocated space] varchar(18),
> reserved varchar(18),
> data varchar(18),
> index_size varchar(18),
> unused varchar(18)
> )
> insert into dbsize exec sp_spaceused
> Can you help me?
> Thanks and best regards

Insert sp_spaceused output into table

Hello,
I need to get database space space usage information into one table and then
format the output in one report.
Im using the sp_spaceused stored procedure to get this information but
because it sends the output separate in two blocks i cant insert it into one
table.
I send you what im doing but it doesnt function.
create table dbsize
(
database_name varchar(128),
database_size varchar(18),
[unallocated space] varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
insert into dbsize exec sp_spaceused
Can you help me?
Thanks and best regardsYou could try gathering the information you need from the system tables.
-Argenis
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:796CCE66-1418-4072-9DFA-3AD3F63E95DF@.microsoft.com...
> Hello,
> I need to get database space space usage information into one table and
then
> format the output in one report.
> Im using the sp_spaceused stored procedure to get this information but
> because it sends the output separate in two blocks i cant insert it into
one
> table.
> I send you what im doing but it doesnt function.
> create table dbsize
> (
> database_name varchar(128),
> database_size varchar(18),
> [unallocated space] varchar(18),
> reserved varchar(18),
> data varchar(18),
> index_size varchar(18),
> unused varchar(18)
> )
> insert into dbsize exec sp_spaceused
> Can you help me?
> Thanks and best regards

Insert sp_spaceused output into table

Hello,
I need to get database space space usage information into one table and then
format the output in one report.
Im using the sp_spaceused stored procedure to get this information but
because it sends the output separate in two blocks i cant insert it into one
table.
I send you what im doing but it doesnt function.
create table dbsize
(
database_name varchar(128),
database_size varchar(18),
[unallocated space] varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
insert into dbsize exec sp_spaceused
Can you help me?
Thanks and best regardsYou could try gathering the information you need from the system tables.
-Argenis
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:796CCE66-1418-4072-9DFA-3AD3F63E95DF@.microsoft.com...
> Hello,
> I need to get database space space usage information into one table and
then
> format the output in one report.
> Im using the sp_spaceused stored procedure to get this information but
> because it sends the output separate in two blocks i cant insert it into
one
> table.
> I send you what im doing but it doesnt function.
> create table dbsize
> (
> database_name varchar(128),
> database_size varchar(18),
> [unallocated space] varchar(18),
> reserved varchar(18),
> data varchar(18),
> index_size varchar(18),
> unused varchar(18)
> )
> insert into dbsize exec sp_spaceused
> Can you help me?
> Thanks and best regards

Insert output of sp_helpdb {dbname} in only one table

Hi,
The output of sp_helpdb {database name} is return in two blocks.
It is possible to join this outpu into only one table?
Thanks,
Regards
Hi
Please don't post the same question within an hour in the same group.
Create a Temporary Table, and then do an INSERT INTO, using EXECUTE. Check
BOL for all the output fields that sp_HelpDB will return as it vaires based
on parameters:
CREATE TABLE #DB
(
Col1,
..
)
INSERT INTO #DB
EXECUTE ('sp_HelpDB')
SELECT * FROM #DB
Regards
Mike
"CC&JM" wrote:

> Hi,
> The output of sp_helpdb {database name} is return in two blocks.
> It is possible to join this outpu into only one table?
> Thanks,
> Regards
|||Thanks Mike but the question was if i execute the sp_helpdb followed by the
database name the output returns two different blocks of information and i
cant insert these two different blocks into the same table.
If i only want to use sp_helpdb...perfect
create table hdb
(
name nvarchar(24),
db_size nvarchar(13),
owner nvarchar(24),
dbid smallint,
created char(11),
status varchar(340),
compatibility_level tinyint,
)
insert into hdb exec sp_helpdb
select * from hdb
But if i want to insert sp_helpdb database_name into the table i supose that
i need to create the other fields with the table to insert the other block of
information, but its shown to me an error:
ex:
create table hdb
(
name nvarchar(24),
db_size nvarchar(13),
owner nvarchar(24),
dbid smallint,
created char(11),
status varchar(340),
compatibility_level tinyint,
name2 nchar(128), -- i put name2 because name already exists
fileid smallint,
[file name] nchar(260),
filegroup nvarchar(128),
size nvarchar(18),
maxsize nvarchar(18),
growth nvarchar(18),
usage varchar(9)
)
insert into hdb exec sp_helpdb database_name
select * from hdb
ERROR:
Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
Insert Error: Column name or number of supplied values does not match table
definition.
I dont know how can i do this.
Thanks and best regards
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Please don't post the same question within an hour in the same group.
> Create a Temporary Table, and then do an INSERT INTO, using EXECUTE. Check
> BOL for all the output fields that sp_HelpDB will return as it vaires based
> on parameters:
> CREATE TABLE #DB
> (
> Col1,
> ..
> )
> INSERT INTO #DB
> EXECUTE ('sp_HelpDB')
> SELECT * FROM #DB
> Regards
> Mike
> "CC&JM" wrote:
|||To insert the output of a stored procedure into a table, the requirement is
that the procedure only return one result set. So sp_helpdb <dbname> does
not qualify.
You can modify the code of sp_helpdb to write your own procedure, and insert
into a table within that new procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:8F0140FA-EC6F-489A-845B-F8FEC74D89A5@.microsoft.com...[vbcol=seagreen]
> Thanks Mike but the question was if i execute the sp_helpdb followed by
> the
> database name the output returns two different blocks of information and i
> cant insert these two different blocks into the same table.
> If i only want to use sp_helpdb...perfect
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> )
> insert into hdb exec sp_helpdb
> select * from hdb
> But if i want to insert sp_helpdb database_name into the table i supose
> that
> i need to create the other fields with the table to insert the other block
> of
> information, but its shown to me an error:
> ex:
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> name2 nchar(128), -- i put name2 because name already exists
> fileid smallint,
> [file name] nchar(260),
> filegroup nvarchar(128),
> size nvarchar(18),
> maxsize nvarchar(18),
> growth nvarchar(18),
> usage varchar(9)
> )
> insert into hdb exec sp_helpdb database_name
> select * from hdb
> ERROR:
> Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
> I dont know how can i do this.
> Thanks and best regards
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Kalen,
How would I modify the code of a Stored procedure? Where do I get the source
code for it?
Fred
"Kalen Delaney" wrote:

> To insert the output of a stored procedure into a table, the requirement is
> that the procedure only return one result set. So sp_helpdb <dbname> does
> not qualify.
> You can modify the code of sp_helpdb to write your own procedure, and insert
> into a table within that new procedure.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:8F0140FA-EC6F-489A-845B-F8FEC74D89A5@.microsoft.com...
>
>

Insert output of sp_helpdb {dbname} in only one table

Hi,
The output of sp_helpdb {database name} is return in two blocks.
It is possible to join this outpu into only one table?
Thanks,
RegardsHi
Please don't post the same question within an hour in the same group.
Create a Temporary Table, and then do an INSERT INTO, using EXECUTE. Check
BOL for all the output fields that sp_HelpDB will return as it vaires based
on parameters:
CREATE TABLE #DB
(
Col1,
.
)
INSERT INTO #DB
EXECUTE ('sp_HelpDB')
SELECT * FROM #DB
Regards
Mike
"CC&JM" wrote:

> Hi,
> The output of sp_helpdb {database name} is return in two blocks.
> It is possible to join this outpu into only one table?
> Thanks,
> Regards|||Thanks Mike but the question was if i execute the sp_helpdb followed by the
database name the output returns two different blocks of information and i
cant insert these two different blocks into the same table.
If i only want to use sp_helpdb...perfect
create table hdb
(
name nvarchar(24),
db_size nvarchar(13),
owner nvarchar(24),
dbid smallint,
created char(11),
status varchar(340),
compatibility_level tinyint,
)
insert into hdb exec sp_helpdb
select * from hdb
But if i want to insert sp_helpdb database_name into the table i supose that
i need to create the other fields with the table to insert the other block o
f
information, but its shown to me an error:
ex:
create table hdb
(
name nvarchar(24),
db_size nvarchar(13),
owner nvarchar(24),
dbid smallint,
created char(11),
status varchar(340),
compatibility_level tinyint,
name2 nchar(128), -- i put name2 because name already exists
fileid smallint,
[file name] nchar(260),
filegroup nvarchar(128),
size nvarchar(18),
maxsize nvarchar(18),
growth nvarchar(18),
usage varchar(9)
)
insert into hdb exec sp_helpdb database_name
select * from hdb
ERROR:
Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
Insert Error: Column name or number of supplied values does not match table
definition.
I dont know how can i do this.
Thanks and best regards
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Please don't post the same question within an hour in the same group.
> Create a Temporary Table, and then do an INSERT INTO, using EXECUTE. Check
> BOL for all the output fields that sp_HelpDB will return as it vaires base
d
> on parameters:
> CREATE TABLE #DB
> (
> Col1,
> ..
> )
> INSERT INTO #DB
> EXECUTE ('sp_HelpDB')
> SELECT * FROM #DB
> Regards
> Mike
> "CC&JM" wrote:
>|||To insert the output of a stored procedure into a table, the requirement is
that the procedure only return one result set. So sp_helpdb <dbname> does
not qualify.
You can modify the code of sp_helpdb to write your own procedure, and insert
into a table within that new procedure.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:8F0140FA-EC6F-489A-845B-F8FEC74D89A5@.microsoft.com...[vbcol=seagreen]
> Thanks Mike but the question was if i execute the sp_helpdb followed by
> the
> database name the output returns two different blocks of information and i
> cant insert these two different blocks into the same table.
> If i only want to use sp_helpdb...perfect
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> )
> insert into hdb exec sp_helpdb
> select * from hdb
> But if i want to insert sp_helpdb database_name into the table i supose
> that
> i need to create the other fields with the table to insert the other block
> of
> information, but its shown to me an error:
> ex:
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> name2 nchar(128), -- i put name2 because name already exists
> fileid smallint,
> [file name] nchar(260),
> filegroup nvarchar(128),
> size nvarchar(18),
> maxsize nvarchar(18),
> growth nvarchar(18),
> usage varchar(9)
> )
> insert into hdb exec sp_helpdb database_name
> select * from hdb
> ERROR:
> Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
> I dont know how can i do this.
> Thanks and best regards
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Kalen,
How would I modify the code of a Stored procedure? Where do I get the source
code for it?
Fred
"Kalen Delaney" wrote:

> To insert the output of a stored procedure into a table, the requirement i
s
> that the procedure only return one result set. So sp_helpdb <dbname> does
> not qualify.
> You can modify the code of sp_helpdb to write your own procedure, and inse
rt
> into a table within that new procedure.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:8F0140FA-EC6F-489A-845B-F8FEC74D89A5@.microsoft.com...
>
>

Insert output of sp_helpdb {dbname} in only one table

Hi,
The output of sp_helpdb {database name} is return in two blocks.
It is possible to join this outpu into only one table?
Thanks,
RegardsHi
Please don't post the same question within an hour in the same group.
Create a Temporary Table, and then do an INSERT INTO, using EXECUTE. Check
BOL for all the output fields that sp_HelpDB will return as it vaires based
on parameters:
CREATE TABLE #DB
(
Col1,
..
)
INSERT INTO #DB
EXECUTE ('sp_HelpDB')
SELECT * FROM #DB
Regards
Mike
"CC&JM" wrote:
> Hi,
> The output of sp_helpdb {database name} is return in two blocks.
> It is possible to join this outpu into only one table?
> Thanks,
> Regards|||Thanks Mike but the question was if i execute the sp_helpdb followed by the
database name the output returns two different blocks of information and i
cant insert these two different blocks into the same table.
If i only want to use sp_helpdb...perfect
create table hdb
(
name nvarchar(24),
db_size nvarchar(13),
owner nvarchar(24),
dbid smallint,
created char(11),
status varchar(340),
compatibility_level tinyint,
)
insert into hdb exec sp_helpdb
select * from hdb
But if i want to insert sp_helpdb database_name into the table i supose that
i need to create the other fields with the table to insert the other block of
information, but its shown to me an error:
ex:
create table hdb
(
name nvarchar(24),
db_size nvarchar(13),
owner nvarchar(24),
dbid smallint,
created char(11),
status varchar(340),
compatibility_level tinyint,
name2 nchar(128), -- i put name2 because name already exists
fileid smallint,
[file name] nchar(260),
filegroup nvarchar(128),
size nvarchar(18),
maxsize nvarchar(18),
growth nvarchar(18),
usage varchar(9)
)
insert into hdb exec sp_helpdb database_name
select * from hdb
ERROR:
Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
Insert Error: Column name or number of supplied values does not match table
definition.
I dont know how can i do this.
Thanks and best regards
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Please don't post the same question within an hour in the same group.
> Create a Temporary Table, and then do an INSERT INTO, using EXECUTE. Check
> BOL for all the output fields that sp_HelpDB will return as it vaires based
> on parameters:
> CREATE TABLE #DB
> (
> Col1,
> ..
> )
> INSERT INTO #DB
> EXECUTE ('sp_HelpDB')
> SELECT * FROM #DB
> Regards
> Mike
> "CC&JM" wrote:
> > Hi,
> >
> > The output of sp_helpdb {database name} is return in two blocks.
> > It is possible to join this outpu into only one table?
> >
> > Thanks,
> > Regards|||To insert the output of a stored procedure into a table, the requirement is
that the procedure only return one result set. So sp_helpdb <dbname> does
not qualify.
You can modify the code of sp_helpdb to write your own procedure, and insert
into a table within that new procedure.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:8F0140FA-EC6F-489A-845B-F8FEC74D89A5@.microsoft.com...
> Thanks Mike but the question was if i execute the sp_helpdb followed by
> the
> database name the output returns two different blocks of information and i
> cant insert these two different blocks into the same table.
> If i only want to use sp_helpdb...perfect
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> )
> insert into hdb exec sp_helpdb
> select * from hdb
> But if i want to insert sp_helpdb database_name into the table i supose
> that
> i need to create the other fields with the table to insert the other block
> of
> information, but its shown to me an error:
> ex:
> create table hdb
> (
> name nvarchar(24),
> db_size nvarchar(13),
> owner nvarchar(24),
> dbid smallint,
> created char(11),
> status varchar(340),
> compatibility_level tinyint,
> name2 nchar(128), -- i put name2 because name already exists
> fileid smallint,
> [file name] nchar(260),
> filegroup nvarchar(128),
> size nvarchar(18),
> maxsize nvarchar(18),
> growth nvarchar(18),
> usage varchar(9)
> )
> insert into hdb exec sp_helpdb database_name
> select * from hdb
> ERROR:
> Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
> I dont know how can i do this.
> Thanks and best regards
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Please don't post the same question within an hour in the same group.
>> Create a Temporary Table, and then do an INSERT INTO, using EXECUTE.
>> Check
>> BOL for all the output fields that sp_HelpDB will return as it vaires
>> based
>> on parameters:
>> CREATE TABLE #DB
>> (
>> Col1,
>> ..
>> )
>> INSERT INTO #DB
>> EXECUTE ('sp_HelpDB')
>> SELECT * FROM #DB
>> Regards
>> Mike
>> "CC&JM" wrote:
>> > Hi,
>> >
>> > The output of sp_helpdb {database name} is return in two blocks.
>> > It is possible to join this outpu into only one table?
>> >
>> > Thanks,
>> > Regards|||Kalen,
How would I modify the code of a Stored procedure? Where do I get the source
code for it?
Fred
"Kalen Delaney" wrote:
> To insert the output of a stored procedure into a table, the requirement is
> that the procedure only return one result set. So sp_helpdb <dbname> does
> not qualify.
> You can modify the code of sp_helpdb to write your own procedure, and insert
> into a table within that new procedure.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:8F0140FA-EC6F-489A-845B-F8FEC74D89A5@.microsoft.com...
> > Thanks Mike but the question was if i execute the sp_helpdb followed by
> > the
> > database name the output returns two different blocks of information and i
> > cant insert these two different blocks into the same table.
> > If i only want to use sp_helpdb...perfect
> >
> > create table hdb
> > (
> > name nvarchar(24),
> > db_size nvarchar(13),
> > owner nvarchar(24),
> > dbid smallint,
> > created char(11),
> > status varchar(340),
> > compatibility_level tinyint,
> > )
> > insert into hdb exec sp_helpdb
> > select * from hdb
> >
> > But if i want to insert sp_helpdb database_name into the table i supose
> > that
> > i need to create the other fields with the table to insert the other block
> > of
> > information, but its shown to me an error:
> >
> > ex:
> >
> > create table hdb
> > (
> > name nvarchar(24),
> > db_size nvarchar(13),
> > owner nvarchar(24),
> > dbid smallint,
> > created char(11),
> > status varchar(340),
> > compatibility_level tinyint,
> > name2 nchar(128), -- i put name2 because name already exists
> > fileid smallint,
> > [file name] nchar(260),
> > filegroup nvarchar(128),
> > size nvarchar(18),
> > maxsize nvarchar(18),
> > growth nvarchar(18),
> > usage varchar(9)
> > )
> >
> > insert into hdb exec sp_helpdb database_name
> >
> > select * from hdb
> > ERROR:
> > Server: Msg 213, Level 16, State 7, Procedure sp_helpdb, Line 175
> > Insert Error: Column name or number of supplied values does not match
> > table
> > definition.
> >
> > I dont know how can i do this.
> > Thanks and best regards
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> Please don't post the same question within an hour in the same group.
> >>
> >> Create a Temporary Table, and then do an INSERT INTO, using EXECUTE.
> >> Check
> >> BOL for all the output fields that sp_HelpDB will return as it vaires
> >> based
> >> on parameters:
> >>
> >> CREATE TABLE #DB
> >> (
> >> Col1,
> >> ..
> >> )
> >>
> >> INSERT INTO #DB
> >> EXECUTE ('sp_HelpDB')
> >>
> >> SELECT * FROM #DB
> >>
> >> Regards
> >> Mike
> >>
> >> "CC&JM" wrote:
> >>
> >> > Hi,
> >> >
> >> > The output of sp_helpdb {database name} is return in two blocks.
> >> > It is possible to join this outpu into only one table?
> >> >
> >> > Thanks,
> >> > Regards
>
>

Insert output of procedure inside table

Hello,
Can you tell me how can i put the output if the procedure sp_helpdb inside
one table?
Thanks and best regardsAnswered in thread: "RE: Insert output of sp_helpdb {dbname} in only one table"
"CC&JM" wrote:
> Hello,
> Can you tell me how can i put the output if the procedure sp_helpdb inside
> one table?
> Thanks and best regards

Insert output of procedure inside table

Hello,
Can you tell me how can i put the output if the procedure sp_helpdb inside
one table?
Thanks and best regards
Answered in thread: "RE: Insert output of sp_helpdb {dbname} in only one table"
"CC&JM" wrote:

> Hello,
> Can you tell me how can i put the output if the procedure sp_helpdb inside
> one table?
> Thanks and best regards

Insert output of procedure inside table

Hello,
Can you tell me how can i put the output if the procedure sp_helpdb inside
one table?
Thanks and best regardsAnswered in thread: "RE: Insert output of sp_helpdb {dbname} in only on
e table"
"CC&JM" wrote:

> Hello,
> Can you tell me how can i put the output if the procedure sp_helpdb inside
> one table?
> Thanks and best regards