Insert/Update sql commands not saving to DB

Here is my problem :
I issue an insert statement to the db. While I am getting a return value of 1 (1 row was affected) the values never show up into the db when I open the DB in access. However, I can see the data when it does an SQL select inside the program. So for instance, I do an insert into ORDER values (1, 12, 5.99). (1 = item ID, 12 = quantity, 5.99 = price). I then do a select * from Order, and I get those values back. When I open the DB in access, in between doing the insert and the select, I dont see the values there either. It is like it is making a temporary copy of the DB in memory during the execution only. When I close the program and re-F5, the data is no longer there. Maybe we need some kind of commit transaction? What am I doing wrong? I am using VB.Net 2005/MS Access 2003. Here is the relevant code :
Private m_Connection As OleDbConnection
''' <summary>
''' Defines the path to the database.
''' </summary>
''' <remarks></remarks>
#If CONFIG = "Debug" Then
Public Const DB_PATH As String = "DBs\DB_Test.mdb"
#ElseIf CONFIG = "Release" Then
Public Const DB_PATH As String = "DBs\DB_Production.mdb"
#End If
Sub connect(ByVal p_path As String) Implements IPartyDBase.connect
Dim connect_string As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & p_path
m_Connection = New OleDbConnection(connect_string)
m_Connection.Open()
End Sub
Sub someSub(ByVal stock As StockClass)
Dim tempString
Dim command As OleDbCommand
command = m_Connection.CreateCommand
command.CommandType = CommandType.Text
tempString = "Insert into Stock VALUES (" & stock.ID & ", "
tempString = tempString & stock.Quantity & ", "
tempString = tempString & stock.Price & ")"
Dim tempInt as Integer
command.CommandText = tempString
tempInt = command.ExecuteNonQuery
If Not tempInt = 1 Then
Throw New Exception("Bad addStockToDB into Stock " & tempInt)
End If
End Sub
Sub anotherSub
p_dbase.connect(PartyDBaseAccess.DB_PATH)
p_dbase.someSub()
p_dbase.close()
End Sub
Edit : During execution, looking under bin/debug/DBs, there is a copy of the database that has all the transactions I did during execution... but the actual DB isnt being updated/copied over.
Ok, the problem was that the path was not implicit, and it was overwriting the DB in /bin/debug/DBs... so changing the DB attributes to never copy worked, and opening the file in /bin/debug/DBs instead of the place where it was copying from.

Insert Trigger - How To

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

Maybe you can try CLR integration in SQL2005 Create a stored procedure from an assembly?which?generate?the?password,?and?then?call?the?stored?procedure?in?the?insert?trigger?on?the?table.?Take?a?look?at?this?article:Using CLR Integration in SQL Server 2005

But if you just want some random string, why not try?T-SQL new_id() funciton?sql

Insert Trigger

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

You can create a dll which contains a method to call System.Web.Security.Membership.GeneratePassword(12,4) and return the generated password. Then add the dll to SQL assemblies and create a CLR reference function for that assembly.?For?more?information?you?can?refer?to:
Using CLR Integration in SQL Server 2005

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 statement which uses a return value from an SP as an insert value

I'm quite stuck with this:

I have an import table called ReferenceMatchingImport which contains
data that has been sucked from a data submission. The contents of
this table have to be imported into another table ExternalReference
which has various foreign keys.

This is simple but one of these keys says that the value in
ExternalReference.CompanyRef must be in the CompanyReference table.
Of course if this is an initial import then it will not be so as part
of my script I must insert a new row into CompanyReference and
populate ExternalReference.CompanyRef with the identity column of this
table.

I thought a good idea would be to use an SP which inserts a new row
and returns @.@.Identity as the value to insert. However this doesn't
work as far as I can tell. Is there a approved way to perform this
sort of opperation? My code is below.

Thanks.

ALTER PROCEDURE SP00ReferenceMatchingImport
AS

/*
Just some integrity checking going on here
*/

INSERT ExternalReference
(
ExternalSourceRef,
AssetGroupRef,
CompanyUnitRef,
EntityTypeCode,
CompanyRef, --this is the unknown ref which is returned by the sp
ExternalReferenceTypeCode,
ExternalReferenceCompanyReferenceMapTypeCode,
StartDate,
EndDate,
LastUpdateBy,
LastUpdateDate
)
SELECT rmi.ExternalDataSourcePropertyRef,
rmi.AssetGroup,
rmi.CompanyUnit,
rmi.EntityType,
SP01InsertIPDReference rmi.EntityType, --here I'm trying to run the
sp so that I can use the return value as the insert value
1,
1,
GETDATE(),
GETDATE(),
'RefMatch',
GETDATE()
FROM ReferenceMatchingImport rmi
WHERE rmi.ExternalDataSourcePropertyRef NOT IN (
SELECT ExternalSourceRef
FROM ExternalReference
)Chris Gilbert (chris_q2@.hotmail.com) writes:
> I have an import table called ReferenceMatchingImport which contains
> data that has been sucked from a data submission. The contents of
> this table have to be imported into another table ExternalReference
> which has various foreign keys.
> This is simple but one of these keys says that the value in
> ExternalReference.CompanyRef must be in the CompanyReference table.
> Of course if this is an initial import then it will not be so as part
> of my script I must insert a new row into CompanyReference and
> populate ExternalReference.CompanyRef with the identity column of this
> table.
> I thought a good idea would be to use an SP which inserts a new row
> and returns @.@.Identity as the value to insert. However this doesn't
> work as far as I can tell. Is there a approved way to perform this
> sort of opperation? My code is below.

The best strategy is to use a staging table, and it seems that
ReferenceMatchingImport is this sort of table. So add a column to this
table with the CompanyRef, and before you insert into the
ExternalReference, you create the new company references, and then update
that value in ReferenceMatchingImport.

There are other possible techniques as well, but all boils down to that
you have to get the references before you INSERT. You cannot INSERT into
one table and then with the left hand insert into another table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns955DF29863C85Yazorman@.127.0.0.1>...
> The best strategy is to use a staging table, and it seems that
> ReferenceMatchingImport is this sort of table. So add a column to this
> table with the CompanyRef, and before you insert into the
> ExternalReference, you create the new company references, and then update
> that value in ReferenceMatchingImport.
> There are other possible techniques as well, but all boils down to that
> you have to get the references before you INSERT. You cannot INSERT into
> one table and then with the left hand insert into another table.

Thankyou Erland,

That was enough to make me give up on the approach above and resign
myself to using a cursor. I have done what you suggested and added a
column to the staging table and run the import as above but after
creating the CompanyRefs. The code is below for anyone refering to
this thread.

Erland, is this the approach you were suggesting? Is there a way to
avoid using a cursor?

Thanks, Chris

DECLARE @.ExternalDataSourcePropertyRef VARCHAR(250)
DECLARE @.CompanyRef BIGINT

DECLARE rm_cursor CURSOR FOR
SELECT rmi.ExternalDataSourcePropertyRef
FROM ReferenceMatchingImport rmi
WHERE rmi.CompanyRef IS NULL

OPEN rm_cursor
FETCH NEXT FROM rm_cursor INTO @.ExternalDataSourcePropertyRef

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC @.CompanyRef = SP01InsertCompanyReference 5
UPDATE ReferenceMatchingImport
SET CompanyRef = @.CompanyRef
WHERE ExternalDataSourcePropertyRef = @.ExternalDataSourcePropertyRef

FETCH NEXT FROM rm_cursor INTO @.ExternalDataSourcePropertyRef
END

CLOSE rm_cursor
DEALLOCATE rm_cursor|||Chris Gilbert (chris_q2@.hotmail.com) writes:
> That was enough to make me give up on the approach above and resign
> myself to using a cursor. I have done what you suggested and added a
> column to the staging table and run the import as above but after
> creating the CompanyRefs. The code is below for anyone refering to
> this thread.
> Erland, is this the approach you were suggesting? Is there a way to
> avoid using a cursor?

Probably. Although you make things difficult with using an IDENTITY
column on the CompanyRef table. That makes it more difficult to insert
many rows and know what the keys are. Better is to have artificial key
that you roll your own. Then you could do something like:

CREATE TABLE #extrefs (externalref whatever_type NOT NULL PRIMARY KEY,
ident int IDENTITY UNIQUE,
internalref int NULL)

INSERT #extrefs (externalref)
SELECT DISTINCT externalref FROM ReferenceMatchingImport

SELECT @.maxid = coalesce(MAX(id), 0) FROM RefTable

INSERT RefTable(id, externalref)
SELECT @.maxid + ident, externalref
FROM #extrefs e
WHERE NOT EXISTS (SELECT *
FROM RefTable r
WHERE r.externalref = e.externalref)

UPDATE #extrefs
SET internalref = r.id
FROM #extrefs e
JOIN RefTable r ON r.externalref = e.externalref

Here I am making wild assumptions on how you tables looks like, since I
don't have that information.

> EXEC @.CompanyRef = SP01InsertCompanyReference 5

Side note: in my opinion the return value of a stored procedure should
only be used to indicate status. To return data, use output parameters
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Insert row in table with Identity field, and get new Identity back

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?

TIA,

barkingdog

P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

If you need to do this for every row, then using identities with an SSIS is not a good idea. You cannot get the new identity back until the row is committed, but that would mean committing one row at a time in SSIS. Even then, you only get back the last identity - which may not be what you expect if a parallel process has added a row between you commiting your row and asking for it's identity.

The best way is to use a script to generate a key in the data flow. In that way, you will know what the key value for each row is in advance and it can be inserted (thanks to multicast) into different tables at once, guaranteeing referential integrity.

Donald

|||

Donald,

When you wrote "You cannot get the new identity back until the row is committed, but that would mean committing one row at a time in SSIS."

When I run a normal SSIS package that reads from a file and writse to a database isn't one row being committed at a time? Or does SSIS save as many rows as possible in, say a memory buffer, and then commit then all at once?

TIA,

barkindog

|||

Strictly speaking it is the provider that handles commits, not SSIS.

The Fastload option on the OLEDB provider allows you to set batch sizes from 1 to "the entire data load in one batch."

If you do not use Fast Load, then one row at a time is sent.

The OLEDB command component also processes one row at a time.

However, in all these cases, the problem is not the performance of handling one row at a time (although that is a real factor) - it is also that you cannot get back the identity for the row you have just committed.

The pattern in SQL Server (and in most rdbms's) is that you can get the last identity issued. It is tempting to think that having just posted a row, the last identity issued must be for that row. Many a design has foundered on that assumption, as just the teensiest smidgin of parallelism soon throws that process out of synchronization.

I much prefer issuing keys in advance in the ETL process - you can do so much with them, with great performance and guaranteed integrity.

Donald

|||

Regarding "Many a design has foundered on that assumption, as just the teensiest smidgin of parallelism soon throws that process out of synchronization."

1. If my job is the only one updating the table with the Identity column , and I'm not running multiple copies of my job, then I presume that parallellism can't happen to me. Or does SSIS do things "in the background" that could cause a smidgin of parallelism, even for my particular case?

2. Later on I will need to re-run my job with new data. Then I have to read the current value of the Identity from the table, add 1 to it, and begin with that value. Your argument about parallelism makes me wonder if the only way to accurately read the identity value from a table is to make sure no other app updates that table. (That sure puts a dent in the possibility of scaling out horizontally with servers.)

TIA,

barkingdog

|||

1. The OLEDB command destination may send a command for the second row before the first has completed. Our buffer architecture is designed to maximise the potential for pipeline parallelism.

2. The only way to guarantee that the last identity you read is the last one you inserted, is to be able to guarantee that no process has written to the table since your process.

We do have a design pattern for highly parallel key generation that may (but may not) be in the next version . Either way there will be a paper on this at some point.

The best strategy is to know your keys in advance - by generating them in your data integration process. That way, you have complete control.

Donald

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
>
>