Inserting a Control Record into a Flat Text File through SSIS

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

Yes, I would guess that you can do exactly this using the header section.

You can set it dynamically by putting an expression on the [<Flat File Destination Name>].[Header] property of the parent data-flow task.

-Jamie

|||

Ok, I see that this can work, but looking at the available variables, functions for expressions, I do not see how I would get the data inserted from another text file (table) already created into this second one.

Truly not trying to be dense here, just "can't seem to see the forest for the trees."

Thanks.

|||

That's a bit of a different requirement. You may be hampered by the fact that the maximum length of the result of an expression can only be 4000 chars

The way to do it would probably be to build the text up programatically in a script task.

-Jamie

sql

Inserting 2 tables with Pk/Fk

Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.

Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)

I'll be executing SQL statements from my service - INSERTs, etc...

What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.

Is there a way I can insert into both tables with one SQL statement?

Thanks!! Curt.First, I recommend that your service call a stored procedure to make this happen, and not issue an ad-hoc query. the sproc would do both inserts, you'd just call it with the values you need to put in Stuff and MoreStuff. So as far as your service is concerned, both inserts happen in "one statement". Within the sproc it's still two inserts though.

Second, in your sproc after your insert into tableB, you can call SCOPE_IDENTITY() to get the identity value that was just inserted. use this value as the fk in tableA when you do the insert there.

take a look at SCOPE_IDENTITY() in BOL. @.@.IDENTITY is a related beast, but SCOPE_IDENTITY() is preferred since it's scoped, as the name implies.

Edit: since I have my roots in C++ as well, thought I would add this: leaving your tables open to ad-hoc queries from client apps is like designing a class in C++ where all the fields are public. If your table structure changes, you have to recompile and redeploy your service. You wouldn't want to do that would you? :)

I think of sprocs as analogous to the public member functions on a class. use them to control how clients are allowed to manipulate the private fields (your tables), and make all fields (tables) private.|||jezemine, yeah I guess I should have qualfied that a bit more... We are in fact planning to put that into a sproc a little later on. As I mentioned, I'm not really a sql server pro and sprocs are on my list of items to conquer... Right now we just need to get something up and running to help prove concept. Thanks for the tips, though! Perhaps I'll conquer that beast sooner than I thought! :)|||ok, but remember that prototype code sometimes has a way of "sticking" :)

Inserted/deleted table.

Hi,

I am currently working on a MS SQL server 2000.

I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted -- are not accessible. anyone knows why? And is there any other way to do this?

Thankspost your t-sql code that you used to access the inserted/deleted tablessql

Insert/Update statements or Stored Procs

When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanksStored procs...but who's going to write them?|||Use ADO from VB for insert and update|||Originally posted by Brett Kaiser
Stored procs...but who's going to write them?

wouldn't i just code the Insert/Update statement within the stored proc, then pass the value's to the stored proc. That sounds like alot of parameters to be dealing with for larger tables.|||Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.|||Originally posted by Brett Kaiser
Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.

Thanks Brett, one more quick question. Within the stored proc, i need to check if the record already exists before inserting or updating. Can you paste a small code sample to give me an idea of how i would ideally do that.

thanks alot|||I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP|||USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'
GO

CREATE PROC mySproc99
@.Action Char(1)
, @.Col1 int
, @.Col2 char(1) = Null
AS
-- File: {\\tsstrv03\ESolutions}:
-- Date: May 1st, 2002
-- Author: Brett Kaiser
-- Server:
-- Database: TaxReconDB
-- Login: sa
-- Description: myTable99 Maint sproc
--
--
-- The stream will do the following:
--
-- 1.
--
-- Tables Used: myTable99
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------- ---- ------ ------ ------ ------
-- Ledger_Detail 76779 17160 KB 17040 KB 64 KB 56 KB
-- ATS_SignOff_Entity 3316 512 KB 504 KB 16 KB -8 KB
-- tblAcct_LedgerBalance 11691 3848 KB 3792 KB 8 KB 48 KB
--
--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 05/23/2002 1. Initial release
--
--
--

Declare @.error_out int, @.Result_Count int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int, @.RC int

SET NOCOUNT ON

SELECT @.rc = 0

BEGIN TRAN

IF @.Action NOT IN ('S','I','U','D')
BEGIN
SELECT @.Error_Loc = 1
SELECT @.Error_Message = 'Incorrect Request. Must be S,I,U or D. Paramter was: "' + @.Action + '"'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END

IF @.Action = 'S'
BEGIN
SELECT Col1, Col2 FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 2
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'myTable99 Returned zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'D'
BEGIN
DELETE FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 3
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 3
SELECT @.Error_Message = 'An Attempted DELETE from myTable99 affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'I'
BEGIN
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 4
SELECT @.Error_Message = 'An Attempted INSERT to myTable99 did not insert anything'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'U'
BEGIN
UPDATE myTable99 SET Col2=@.Col2 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 5
SELECT @.Error_Message = 'An Attempted UPDATE of myTable99 Affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

COMMIT TRAN

mySproc99_Exit:

SET NOCOUNT OFF

RETURN @.rc

mySproc99_Error:

ROLLBACK TRAN

IF @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
IF @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

SELECT @.rc = -1

RAISERROR @.Error_Type @.Error_Message

GOTO mySproc99_Exit
GO

DECLARE @.RC int

EXEC @.RC = mySproc99 'X',1,'A'

SELECT @.RC

EXEC @.RC = mySproc99 'S',4

SELECT @.RC

EXEC @.RC = mySproc99 'I',5,'E'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'U',5,'F'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'D',5

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'I',4,'F'

SELECT @.RC
GO

DROP PROC mySproc99
GO
DROP TABLE myTable99
GO|||Originally posted by Pat Phelan
I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP
Actually, any communication with the server should be done through stored procedure, including SELECT.|||Originally posted by rdjabarov
Actually, any communication with the server should be done through stored procedure, including SELECT. I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||here's the man of so many virtues|||Originally posted by ms_sql_dba
here's the man of so many virtues

You sure s/he's a man?

Pat, you lost me...

we're talking about an app right? Not ad-hoc/dba maint issues? right?|||Originally posted by ms_sql_dba
here's the man of so many virtues Are you accusing me of having virtues ? I may wear many hats, but that is due to having a huge head. It has nothing to do with virtues of any kind!

-PatP|||Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)

suave is the only word I can think of...

You must be a ladies man....

:D

Does anyone use anything like the template posted..or is it 1 sproc per operation?|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?) Heck, I thought that confusion was a consequence of working with databases!

Nah, I don't really have any of those titles, but they sounded cool next to my actual titles (International super-spy, Bon-Vivant, and Ultra-cool geek about town). I'll try to behave better from now on!

-PatP|||Originally posted by Brett Kaiser
suave is the only word I can think of... Nope, Suave is one of our DataWarehousing consultants. He lives somewhere in Jersey and flies out to come play when we need him.
Originally posted by Brett Kaiser
You must be a ladies man.... Just one lady, although I do flirt outrageously. I used to send people around the bend when I'd dial our last TAM and ask "So how are you, other than obviously devastatingly gorgeous?" To which she'd often reply "Gee, you've just GOT to call more often."
Originally posted by Brett Kaiser
Does anyone use anything like the template posted..or is it 1 sproc per operation? Nope, to me that smacks of bad design. At least in my book, cross-tabs should be done on the client side or in the data warehouse, not from an OLTP system.

-PatP|||That's all VERY funny...

but what do you mean cross tabs?

And I definetly have to use that line...

Well, at least on the wife anyway...|||Originally posted by Brett Kaiser
but what do you mean cross tabs?
Whoops! Brain fart on my part, wrong thread!

-PatP|||International super-spy? You took my title!!! I demand it back...or a cig!|||You're really having trouble with the cig thing, but it is worth the fight. There aren't enough "bright boys" around, and we can't afford to loose any!

Anywho, the title isn't exclusive. When they awarded me the title, none of the previous users lost their permission to use it!

-PatP|||>For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand".

Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools. You better get your facts right...pb8 > 9704 would "compile" and give SQL error codes whenever embedded sql is given in code and it adds to ease of use for developers...[no doubt agrees for SP approch being the better one].

moreover, PB supports all 4 levels of dynamic SQL superbly and I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

my 2 cents

WS [wizardofnet-at-yahoo]

Originally posted by Pat Phelan
I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||Originally posted by mell
I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...


[as he types falling out of chair]
Really?
[/as he types falling out of chair]|||Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN|||Originally posted by MCrowley
Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN

Ya lost me on that one...you mean make 4 out of the one I posted?

It's all a matter of methodolgy...

Pick 1 and stick eith it...no thinking involved...same thing for naming comventions...

make it so you don't have to look anything up...

But I like the part about not knowing the names of columns or tables...

must make for some very interesting code...no?|||Originally posted by mell
Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools.[wizardofnet-at-yahoo] 'splain dis one again for me... In the scenario I described, you don't have SELECT permissions, so you can't see any tables. You can't open the DataWindow painter. You can't generate any dynamic SQL...

What exactly can you do again?

-PatP|||Hey Lucy.....what did you did you do with the permissions this time?|||Yep. As near as I understand, when a procedure runs for the first time with it's first parameters, a query plan is born. SQL Server tries to use that query plan for each successive run of the stored procedure. Writing an all in one procedure is good if the procedure is not run very often, but for a website where a stored procedure can be run many many times, you don't want to wait around for the query optimizer to try to figure out it needs to recompile all of a sudden. Clear as mud?|||woooooosh...

and huh?

wouldn't the plan just stay in cache?

Got to get that internals book...|||Here is a classic example. Get on a server that has been around and been backing up databases regularly. Then make this stored procedure:

create procedure testproc (@.start int, @.end int)
as

select *
from msdb..backupset
where backup_set_id > @.start
and backup_set_id < @.end
go

Then run this:
testproc 1, 2

Get the execution plan, then run this:
testproc 1, 40000

and check out that execution plan

It should use the index for both, even though a table scan would be better for the second query.

EDIT: Hmm. Having trouble with the reverse of the logic in this example. I can not get the stored proc to do anything but use the index. Anyway, complex queries can get hit pretty hard by this fact. Something to keep in mind.|||Originally posted by Sammy_S
When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

thanks

It's definitely better to use sprocs. In this way you separate the different layers in your application (something, which you may have missed to consider during the development). Later if you need changes you will need just to change the sprocs, without any modifications in the VB code. Also consider that the sprocs syntax is being validated during creation and they're compiled. So in all cases it's better to use them instead of raw hard-coded statements.

Martin Markov

Insert trigger not working...

can someone shed some light on this? i want this to work on update and
insert, but it only works on update. When i run a simple insert
statement, i get this
<root>The specified statement did not generate any data</root>
Here is the trigger:
CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
FOR UPDATE, INSERT
--This trigger is used to export an xml file with system_qty, part_name
AS
DECLARE @.trans_date datetime,
@.part_name varchar(50),
@.Q varchar (255),
@.file varchar (255)
--IF NOT UPDATE(system_qty)
--RETURN
SELECT @.trans_date =trans_date FROM Inserted
SELECT @.part_name = part_name From Inserted
SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
@.query = @.Q,
@.templatefile = 'c:\scripts\template1.tpl'On 8 May 2006 15:26:56 -0700, lytung@.gmail.com wrote:

>can someone shed some light on this? i want this to work on update and
>insert, but it only works on update. When i run a simple insert
>statement, i get this
> <root>The specified statement did not generate any data</root>
Hi lytung,
Not a definitive answer, but I have some comments below:

>Here is the trigger:
> CREATE TRIGGER trg_UpdateQty ON [fastpic].[FP_INVTRANS]
>FOR UPDATE, INSERT
(snip)

>SELECT @.trans_date =trans_date FROM Inserted
>SELECT @.part_name = part_name From Inserted
This can give unexpected results for multi-row updates or multi-row
inserts. Since the trigger is fired only once per statement execution,
the inserted pseudo-table will hold several rows. You might well get
@.trans_date from one row and @.part_name from another. Plus, you probably
want to execute the trigger's logic for all affected rows.

>SELECT @.Q = 'select system_qty, part_name FROM [fastpic].[FP_INVTRANS2]
>as InvTrans WHERE part_name ='''+@.part_name + ''' and trans_date ='''
>+ Convert(varchar(20), @.trans_date, 120) + ''' for xml auto, elements'
Use style 126 for the converstion of @.trans_date. Style 120 is not
guaranteed to be unambiguous in all locale settings.

>Exec sp_makewebtask @.outputfile ='c:\updatePart.xml',
>@.query = @.Q,
>@.templatefile = 'c:\scripts\template1.tpl'
Add a PRINT @.Q statement before, after or instead of this statement for
debugging purposes. Or insert @.Q into a table, if you are testing from a
front-end that doesn't expose the output of PRINT. Check if the SQL that
is generated matches your expecations.
Hugo Kornelis, SQL Server MVP

Insert Trigger not getting row data for email body

hello,

need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data information in the email if I update the row.

This is for 2005 SQL

Any direction would be greatly appreaciated

OneIDesigned

Maybe the code for the trigger is incorrect?

|||

Here is the trigger code

<code>

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERTRIGGER [dbo].[TestimonalEmailInsert]

ON [dbo].[CMRC_Testimonal]

AFTERINSERT

AS

BEGIN

SETNOCOUNTON;

declare @.TestimonalIDint

declare @.FullNamenvarchar(150)

declare @.FullNameNewnvarchar(150)

declare @.CompanyNamenvarchar(150)

declare @.CompanyNameNewnvarchar(150)

declare @.Emailnvarchar(150)

declare @.EmailNewnvarchar(150)

declare @.Testimonalvarchar(2000)

declare @.TestimonalNewvarchar(2000)

declare @.messagevarchar(2000);

SELECT @.TestimonalID= [dbo].[CMRC_Testimonal].TestimonalID,

@.FullName= [dbo].[CMRC_Testimonal].FullName

FROM [dbo].[CMRC_Testimonal]WHERE [dbo].[CMRC_Testimonal].TestimonalID=(SELECT TestimonalIDFROM Inserted)

Set @.message='Testimonial ID '+ltrim(@.TestimonalID)+' has been updated'+

' previous First Name is '+ @.FullName+' and the new Full Name is '+ @.FullNameNew

EXEC msdb.dbo.sp_send_dbmail

@.profile_name='MSQLMAILProfile',

@.recipients='myemail@.myemail.com,

@.body= @.message,

@.subject='Testimonal has been inserted';

END

</code>

Hope this helps

|||

Do you use a Stored Procedure to do the insert? If so, you can use Visual Studio to step into the procedure and then into the trigger. This will allow you to debug the trigger and see if any of the variables are being set.

Also, you should change your sql statement to use a join to the inserted table.

|||

Basically I found that the problem was related to having <NULL> in some of the rows of column data that I was trying to insert into the body of the email. If one of the colums had a <NULL> the entire body of the email would not show up.

The work around I am currently using is to add default information at the table level so each record will column will have data. "Not Supplied"

I would like to configure the trigger to insert "" into the body if <NULL> exist, but I have not found any code to resolve the issue at the trigger level.

Thanks

|||

OneIdesigned:

The work around I am currently using is to add default information at the table level so each record will column will have data. "Not Supplied"

That isn't really a good idea. Null is different to "Not Supplied" as it means it is an unknown quantity so you should leave it as Null if the user hasn't supplied it.

OneIdesigned:

I would like to configure the trigger to insert "" into the body if <NULL> exist, but I have not found any code to resolve the issue at the trigger level.

Use the IsNull function.

insert stored procedure result into temporary table ?

I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.

Here is an example:

-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END

-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc

-- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))

-- this call will succeed
INSERT INTO testTable EXEC testProchow about defining your temp table before inserting into it?|||I'd really prefer to not create a hard dependency on the exact columns returned from the procedure. For instance, if I add another column to the resultset of the procedure, then the 'INSERT INTO EXEC' call will fail unless the predefined table for it is also updated.
I'm basically making a passthough procedure which will call another procedure and return the results as XML. So the base procedure may be called directly or via the passthrough.|||It is considered poor programming practice to use "SELECT *", which is essentially what you are doing when you don't pre-define your table layout.

If you add a column to your stored procedure, then your statement SHOULD fail. That ensures that you review your code to catch any other problems that might arise from the schematic change, and that is what the "testing" phase of development is all about.|||I don't think you know enough about what I'm doing to make that statement blindman. I am making a procedure that is just a passthough call to another procedure. The base procedure that is being called is where all the work is done. The passthrough procedure only exists because some clients will be calling the procedure via HTTP and expecting XML results rather than calling the procedure 'directly' via ODBC or JDBC. If I need to make a change to the base procedure, I don't want to have to also make that change to the passthough version. That creates more dependencies and unnecessary maintenance.
Ideally, a better design might be to add a parameter to the base procedure to tell it whether the results should be returned as XML or not... But I need more restrictive control over the HTTP-XML version for security reasons and it would be cleaner to go the passthrough route.|||I don't think you know enough about what I'm doing to make that statement blindman. I am making a procedure that is just a passthough call to another procedure. The base procedure that is being called is where all the work is done. The passthrough procedure only exists because some clients will be calling the procedure via HTTP and expecting XML results rather than calling the procedure 'directly' via ODBC or JDBC.That's exactly what I thought you were doing. Good coding practice calls for enumerating your datasets.

Insert Statment not working

I know this is a sin in dbforums to jump forums to ask other forum questions, but I just had to do it....

it's actually related to foxpro dbf tables. Here is the case:

I am opening this existing DBF file in Microsoft Visual Fox Pro 6.0 .

In the command window, select, update and even including delete statements works .

What is getting on my nerves is the "insert" statement. It always prompts "syntax error". But the @.#$@.#$ error message just didn't help much.

The funny thing is, if I use the "Append Mode" and add data directly via the GUI, it works!.

Here is the insert statement, just a very simple one:

<code> INSERT INTO ASSET (ACCNO) values '2000/141'</code>

You can reply me here , or go to the real thread to reply me if you can help out..thanks

http://www.dbforums.com/t1058508.htmlINSERT INTO ASSET (ACCNO) values ('2000/141') ?|||Thanks! that work!!!......

Insert statement not working

I have this statement buried in a sproc:

INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID)
SELECT PLAN_SHIP.[YEAR], PLAN_SHIP.BOD_INDEX, 1
FROM PLAN_SHIP LEFT JOIN PLAN_DEMAND ON
PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR]
AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX
WHERE PLAN_DEMAND.BOD_INDEX IS NULL

When I run the sproc in QA, the statements returns records to the grid, but does not insert them into the table. If I just copy the statement into QA and execute it, it works fine.

I'm sure it's something obvious (but not to me).

Any help would be much appreciated.Post the code for your sproc. Either a logical data flow error is preventing the statement from being executed, or you have something commented out of your production code.|||I tried to post the sproc, but I'm limited to posting 10K characters.
The sproc is about 15K.

However...
I commented out everything else in the sproc, execpt this one statement, and I still get the same thing.|||Humor me. Create this procedure:
CREATE PROCEDURE MYTEST
AS
BEGIN

INSERT INTO PLAN_DEMAND
([YEAR],
BOD_INDEX,
SCEN_ID)
SELECT PLAN_SHIP.[YEAR],
PLAN_SHIP.BOD_INDEX,
1
FROM PLAN_SHIP
LEFT JOIN PLAN_DEMAND
ON PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR]
AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX
WHERE PLAN_DEMAND.BOD_INDEX IS NULL

END
Execute it, and let me know what you get.|||Works perfectly... as- I'm sure- you expected.

hmmm...
Since the 2 statements are identical, the problem must lie elsewhere in the sproc. But then, why didn't it work when I commented out everything but this statement? Since the sproc is really just a series of insert/updates, I'll try posting one section at a time to a new sproc until I see the problem again.|||Do you have GO statements in your sproc? Those are command terminators rather than SQL commands, and are not affected by commenting.|||I have 1 go statement, at the very end of the sproc.|||OK Blind dude... Here's what I found:

I tried to parse my original statement to look like yours, just for grins.
After converting the first the first line from this:
INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID)

to this:
INSERT INTO PLAN_DEMAND
([YEAR], BOD_INDEX, SCEN_ID)

I got: ILLEGAL SYNTAX NEAR [YEAR]

Everything looked ok, so I deleted the line: INSERT INTO PLAN_DEMAND
and just retyped it. Works fine now. Must've been some illegal character
between DEMAND and ([YEAR])

Thanks for your help

Insert single Parent and multiple Children

I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table).

My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table.

Thanks for any help

You may use triggers, I mean you can create triggers on the main table, in which you can insert records to other tables. You can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||I don't see how a trigger will help. I need to have a way to add the parent and all its children at once. How will a trigger help?

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advanceUse Profiler to trace the statements sent to sql server. Start by using the
duration template and filter for duration greater than, let us say, 1000
milliseconds or whatever amount you decide. See BOL for more information
about "Profiler".
AMB
"RG" wrote:

> I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
> application written accessing this SQL Server 7 db. Unfortunately, throug
h a
> series of workstation upgrades, the source code was lost.
> Recently, the performance of table inserts degraded significantly. I am
> trying figure out as to what is causing it.
> I am guessing there is some sort of locking problem going on.
> Is there a utility which allows you to trace ODBC activity?
> If anyone knows of any other utility or method to diagnose the problem, I
> would greatly appreciate help.
> Thanks in advance|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
How can I find the query behind this cursor?
Thanks
"Alejandro Mesa" wrote:
> Use Profiler to trace the statements sent to sql server. Start by using th
e
> duration template and filter for duration greater than, let us say, 1000
> milliseconds or whatever amount you decide. See BOL for more information
> about "Profiler".
>
> AMB
> "RG" wrote:
>|||See if this helps:
Server Side Cursors and ADO Cursor Types
http://www.sqlteam.com/item.asp?ItemID=11842
AMB
"RG" wrote:
> Thanks for your help.
> It appears the following statement is a problem
> sp_cursorfetch 422977628, 2, 0, 1.
> How can I find the query behind this cursor?
> Thanks
> "Alejandro Mesa" wrote:
>|||It looks like this is it. It also appears that if you don't have the source
,
there is not much you could do on the database side to improve this
performance.
"Alejandro Mesa" wrote:
> See if this helps:
> Server Side Cursors and ADO Cursor Types
> http://www.sqlteam.com/item.asp?ItemID=11842
>
> AMB
> "RG" wrote:
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advance
If you think there is some locking, blocking going on then
you likely want to monitor this in SQL Server. You can start
by using the system stored procedures sp_lock, sp_who2 and
querying master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Thu, 17 Feb 2005 08:39:09 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
>application written accessing this SQL Server 7 db. Unfortunately, through a
>series of workstation upgrades, the source code was lost.
>Recently, the performance of table inserts degraded significantly. I am
>trying figure out as to what is causing it.
>I am guessing there is some sort of locking problem going on.
>Is there a utility which allows you to trace ODBC activity?
>If anyone knows of any other utility or method to diagnose the problem, I
>would greatly appreciate help.
>Thanks in advance

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advance
my 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon
|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:

> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND i
f
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advancemy 1st guess would be index fragmentation.
1. do your tables have a clustered index (They should have in most cases)
2. do your indexes (if present) have a fill factor of Less Than 100% and
Greater than 0% (they should if you are inserting and updating a lot AND if
the clustered index is NOT an identity or some other monotomically
incrementing value)
3. are your indexes (if Present) fragmented ?
(Check by using DBCC ShowContig (TableName)
Then look at the "Scan Density" Number. It should be >=80% or
performance will start to degrade
Cheers,
Greg Jackson
PDX, Oregon|||Thanks for your help.
It appears the following statement is a problem
sp_cursorfetch 422977628, 2, 0, 1.
It takes over a second to execute it.
How can I find the query behind this cursor?
Thanks
"pdxJaxon" wrote:
> my 1st guess would be index fragmentation.
> 1. do your tables have a clustered index (They should have in most cases)
> 2. do your indexes (if present) have a fill factor of Less Than 100% and
> Greater than 0% (they should if you are inserting and updating a lot AND if
> the clustered index is NOT an identity or some other monotomically
> incrementing value)
> 3. are your indexes (if Present) fragmented ?
> (Check by using DBCC ShowContig (TableName)
> Then look at the "Scan Density" Number. It should be >=80% or
> performance will start to degrade
>
>
> Cheers,
> Greg Jackson
> PDX, Oregon
>
>
>

Insert Performance Degradation

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
application written accessing this SQL Server 7 db. Unfortunately, through
a
series of workstation upgrades, the source code was lost.
Recently, the performance of table inserts degraded significantly. I am
trying figure out as to what is causing it.
I am guessing there is some sort of locking problem going on.
Is there a utility which allows you to trace ODBC activity?
If anyone knows of any other utility or method to diagnose the problem, I
would greatly appreciate help.
Thanks in advanceIf you think there is some locking, blocking going on then
you likely want to monitor this in SQL Server. You can start
by using the system stored procedures sp_lock, sp_who2 and
querying master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Thu, 17 Feb 2005 08:39:09 -0800, "RG"
<RG@.discussions.microsoft.com> wrote:

>I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC
>application written accessing this SQL Server 7 db. Unfortunately, through
a
>series of workstation upgrades, the source code was lost.
>Recently, the performance of table inserts degraded significantly. I am
>trying figure out as to what is causing it.
>I am guessing there is some sort of locking problem going on.
>Is there a utility which allows you to trace ODBC activity?
>If anyone knows of any other utility or method to diagnose the problem, I
>would greatly appreciate help.
>Thanks in advance