Passing object properties to variables

Hi.

I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?

Any ideas would be appreciated. Thanks!

One way you could accomplish this is by using a Script task.

You can access the properties of all the connections in your Connection Manager through the Dts ScriptObjectModel .

For example, the following code grabs the ConnectionString of my "currentFolder" connection object, and stores it in the "myConnectionString" variable.

PublicSub Main()

Dim value AsString

value = Dts.Connections("currentFolder").ConnectionString

Dts.Variables("myConnectionString").Value = value

'MsgBox(Dts.Variables("myConnectionString").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

Be sure to include your variable (ie. User::myConnectionString) in the ReadWriteVariables property of your script task.

Is this what you meant?

|||Yes, this is exactly what I meant. Thanks!

Passing NULL-value into package variables

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

I don't think you can pass nulls through the commandline dtexec or dtexecui simply because a DBNull is an object. One option you can pursue is to pass it using a console app written in VB or C#.

This link shows one of the ways of doing that.|||

Dennis_v_E wrote:

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

Its not possible.

One way around it may be to have a boolean variable called IsDatetimeNull which you set to TRUE or FALSE from the command-line.

Then, you put an expression on your datetime variable which sets it to NULL(DT_DBTIMESTAMP) if IsDatetimeNull==TRUE.

Something like that anyway. You get the idea.

-Jamie

|||

Thanx,

To bad it cannot be done simple. But i get the idea.
Maybe i put a feature request in Connect.

Dennis

sql

passing multiple values from parent to child package

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

Kevin Rodgers wrote:

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so?

Your perception is right.

Kevin Rodgers wrote:

The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

I may not be understanding you correctly; but I think with either 'parent package variables' or 'XML configuration file' you still need to create an entry in package configuration organizer for every property you want to override; so no difference there. The only difference is that in a XML file, yes you're right, multiple object-properties values can be defined.

Kevin Rodgers wrote:

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

you mean to make the parent package 'aware' of the variables available in every child package...not sure how good that would be; but you can submit a suggestion to Microsoft:

http://connect.microsoft.com/feedback/default.aspx?SiteID=68

|||

Kevin Rodgers wrote:

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

The XML config file allows you to specify lots of configurations. A configuration is for a single property only.

Kevin Rodgers wrote:

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

That would mean the parent package needs to have some knowledge of what is in the pckage it is calling. That doesn't really fit with the concept of abstraction - which is what the ability to do parent-child packages is all about really (in my mind anyway). However that isn't a complete justification and I can see why this would be useful - perhaps you should submit the request at Connect?

Hope that helps.

-Jamie

Passing Global Variables from a Execute Package Task

I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?Package 1:
Edit your Execute Package Task, and click on the "Outer Package Global Variables" tab. Select all the Global Variables you want available to Package 2. Save and close.

Package 2:
Edit your ActiveX Script Task and use the code below to access the variable:
DTSGlobalVariables("<global variable name>").Value|||Package1 doesnt have an Execute Package Task, Package2 does...

Package1:
ActiveX Script --> Connection1 -TransformData-> Connection2

Package2:
Exec Package1 -Success-> Send Success Email
'-Failure-> ActiveX (msgbox DTSGlobalVariables("sErrorMessage").Value) --> Send Failure Email|||Ooookay.. so substitute 2 for 1 and 1 for 2... done...|||Thats what I figured when you posted, but I tried that and got nothing.....I might have edited the reply since you looked at it...

When I run Package1 by itself I can see in the Package1 Properties that the Global Variable is being set properly from Package1.

The Execute Package Task in Package2 has the Outer Package Global Variable selected, but when I reference it with DTSGlobalVariables("sErrorMessage").Value in ActiveX Code it shows the variable as being empty.|||Just a update on this:

Seppuku was nice enough the help me via AIM. So far we have come to the conclusion that there is no builtin way for a child to pass data to the parent except maybe through file drops, or writing to a db table.|||Will attempt having the child write to a common table for communication...and will post the results here.

passing form variables to populate Report Param..

Is this possible? All of our users are required to login to our website in order to access their applications they have particular access to. is there a way to pass the same from variable to the first Report parameter. Currently i have it set up (development environment) that all report parameters are visible when we go to the web page to access report. I only want certain parameters available based on who is logged on, but i do not want to build 20 different reports to accomplish this.
Any help would be greatly appreciatedInstead of creating many different report you may take a look at creating
linked reports. Linked reports carry their own security settings, parameter
settings but share the same report definition. For example, you can provide
default value for a parameter and declare it read-only on a linked report.
Users who have access to this linked report won't be able to set the value
of that parameter.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Derek M" <DerekM@.discussions.microsoft.com> wrote in message
news:A332BCCD-D844-4B9C-B9B0-33C7698CAA62@.microsoft.com...
> Is this possible? All of our users are required to login to our website
in order to access their applications they have particular access to. is
there a way to pass the same from variable to the first Report parameter.
Currently i have it set up (development environment) that all report
parameters are visible when we go to the web page to access report. I only
want certain parameters available based on who is logged on, but i do not
want to build 20 different reports to accomplish this.
> Any help would be greatly appreciated

passing datetime variables into a bcp statement

Hi

I posted a question a while back about passing dates through a BCP SQL statement and received the answer that they should look as follows

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

Now I need to do it differently and I have declared date variables and set the values and now i want to place the varaible names into the statement but i am receiving errors such as cannot convert character to datetime and once again i am looking for the correct way to type the bcp statement

I have the following example

Declare @.EndDate Datetime

Declare @.StartDate DateTime

Declare @.FilePath varchar (250)

Declare @.ServerName varchar (250)

Declare @.sql varchar(8000)

SET @.EndDate = '05/01/2007'

SET @.StartDate = '06/01/2007'

SET @.FilePath = 'C:\test.txt'

SET @.ServerName = 'SQLEXPRESSSERVERPATH'

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor @.EndDate, @.StartDate " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

I have tried

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor '' + @.EndDate+ '', '' + @.StartDate + ''" queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

And many many other variations but am mystified as to the correct format.

Can anyone help?

Syvers

Try:

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor ' + @.EndDate + ', ' + @.StartDate + ' " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

|||

Code Snippet

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''' + convert(varchar(10), @.EndDate, 101) + ''', '''+ convert(varchar(10), @.StartDate, 101) + '''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

|||

Thanks Dale, my thinking was not on all cylinders this morning -had to rush out for a meeting.

|||

Team work!

|||Thank you for your help, works great now.

Passing datetime variable to stored proc as parameter

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

As a stopgap, I'm building the exec statement in an expression - converting the dates to single quoted strings.

I would still like to see the correct way to pass date parameters, so if anybody can help it would be much appreciated.

Thankee.

Mike

|||

Any chance your passing the value in as type "DBTimestamp"? Try changing the parameter to type DATE

This looks like a bug to me:

setup:

1. Extract date from query (SSIS will show type as DBTIMESTAMP..)

2. Pass that same date taken from SQL Server as a parameter of type DBTimestamp back into the same SQL Server (using same connection) and it will error with message like you have above.

I tried changing the stored procedure to take nvarchar(2000) so I could see what it was passing for the DBTimestamp parameter to SQL and it was passing '' (empty set.. not to be confused with NULL)

Passing Date Variables

I am a newbie and need help. I am creating an intranet site for my company
and my SQL2000 database has a column named transdate. I want to provide the
user with the ability to run a canned report depending on a date range
entered in a web form (Start Date / End Date).
How would I pass these form entries to my SQL select statement? I am using
ASP VBScript
I'm thinking...
Select *
from DB where transdate >= ' and transdate <= '
Your help is greatly appreciated!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
You can use a parameterized SQL statement, specifying '?' as parameter
markers:
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = "2006-05-01"
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = "2006-05-31"
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615867ef9c88a@.uwe...
>I am a newbie and need help. I am creating an intranet site for my company
> and my SQL2000 database has a column named transdate. I want to provide
> the
> user with the ability to run a canned report depending on a date range
> entered in a web form (Start Date / End Date).
> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
> I'm thinking...
> Select *
> from DB where transdate >= ' and transdate <= '
> Your help is greatly appreciated!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Thanks Dan, I'll give it a shot
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||Obviously I am not advanced enough to get this? I need to pass the dates
from my Web form to the embedded SQL statement in Dreamweaver. I am using
multiple recordsets that require these same date ranges. In ACCESS it is eas
y
because you can create the form and reference it. Is there anything like thi
s
in SQL?
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
SQL Server is basically just the back-end database engine. Unlike SQL
Server, Access also includes an IDE so that you can develop a 'rich client'
GUI for your users. The Access database engine isn't a client/server DBMS
because Access runs in the client process and, in the case of a multi-user
application, the Access database file is shared among multiple Access
instances. With SQL Server, it is the database engine is shared and only
that SQL Server instance accesses the database files.
I know next to nothing about Dreamweaver so I can't provide detailed help.
I don't know what an 'embedded SQL statement in Dreamweaver' is. I assume
this part of server-side code (ASP or ASP.NET) that is generated by the
Dreamweaver IDE. I would expect that the IDE would provide some method to
parameterize the SQL statement, map to your form variables and associate
with a SQL Server database connection.
don't know if this will help but below is an ASP VBScript snippet that can
execute a SQL statement based on the date range. I would expect Dreamweaver
would generate something similar.
<!-- include ADO constants -->
<!-- METADATA
TYPE="typelib"
UUID="00000200-0000-0010-8000-00AA006D2EA4"
-->
<%
Set connection = CreateObject("ADODB.Connection")
connection,Open "Provider=SQLOLEDB;Data Source=MyDbServer;Integrated
Security=SSPI"
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable " & _
"WHERE transdate >= ? AND transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = Request("fromDate")
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = Request("toDate")
Set results = command.Execute
While results.EOF = False
'process row here
results.MoveNext
Loop
results.Close
connection,Close
&>
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:61bd9dcc71568@.uwe...
> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
> Dan Guzman wrote:
> --
> Message posted via http://www.webservertalk.comsql

Passing & Using variables in Stored Procs for table names

Hi all
SQL 2k, win 2kpro, vbscript asp
I'm just getting into stored procs and coming on quite good imho :)
but I'm stuck on a little problem
I want to pass a stored proc a variable eg "G001_"
create procedure dbo.myproc
@.prefix vchar (20)
as
I want to use the "prefix within the stored proc on table names I'm
wanting to grant access for a user on several tables with this prefix
eg
grant select on @.prefix+tablenameOne to UserName
grant select on @.prefix+tablenameTwo to UserName
grant select on @.prefix+tablenameThree to UserName
grant select, update on @.prefix+tablenamefour to UserName
grant select on @.prefix+tablenamefive (col1, col2) to UserName
Would look like this if fixed:
grant select on G001_tablenameOne to UserName
grant select on G001_tablenameTwo to UserName
etc.
but I cant seem to get this to work... How can I do this?
The tables will be created from a different stored proc and then I
want to grant users to be able to use them tables
thanks for any help.
Alyou will have to execute the 'GRANT' statment using EXEC.
Ex:
exec ('grant select on ' + @.prefix + ' tablenameOne to UserName')
--
-Vishal
"Harag" <harag@.softhome.net> wrote in message
news:5175kv4u71063eekbo7i46bjl03u0ok7um@.4ax.com...
> Hi all
> SQL 2k, win 2kpro, vbscript asp
> I'm just getting into stored procs and coming on quite good imho :)
> but I'm stuck on a little problem
> I want to pass a stored proc a variable eg "G001_"
>
> create procedure dbo.myproc
> @.prefix vchar (20)
> as
>
> I want to use the "prefix within the stored proc on table names I'm
> wanting to grant access for a user on several tables with this prefix
> eg
> grant select on @.prefix+tablenameOne to UserName
> grant select on @.prefix+tablenameTwo to UserName
> grant select on @.prefix+tablenameThree to UserName
> grant select, update on @.prefix+tablenamefour to UserName
> grant select on @.prefix+tablenamefive (col1, col2) to UserName
> Would look like this if fixed:
> grant select on G001_tablenameOne to UserName
> grant select on G001_tablenameTwo to UserName
> etc.
> but I cant seem to get this to work... How can I do this?
> The tables will be created from a different stored proc and then I
> want to grant users to be able to use them tables
> thanks for any help.
> Al

pass variables to the sql

Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SMYou can do this by saving all your update statments to .sql or .txt file.
so when ever u need to run the update just call the .sql or .txt file from Sql prompt

SQl>@.xx.sql or
SQl>@.c:\orawin\bin\aa.txt; Make sure you call the file from correct place.

If you want the values to be changed every time.U can do this as follws

UPDATE PS_TEST_TBL
SET TESTFIELD1 = &TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A; &TEST1A for numeric values and '&TEST1A' for char

So the system will prompt :Enter value for TEST1A

Originally posted by meelagupta
Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SM|||Another way would be to create a stored procedure with the update statements, and pass the values in as parameters.

Pass variables to an OPENQUERY

Hi all,

Does anyone know if it is possible to pass variabes to an openquery statement?

I am trying to do something like this:

OPENQUERY(LinkServer, 'SELECT column1, column2 FROM table WHERE column3 ='' + @.@.variable1 +'' AND column4 = '' +@.@.variable2 +'')

Many thanks in advance,

-Vicky

Hi vicky,

If you are using SS 2005, then take a look to EXEC in BOL. You can use the "at linked_server_name" feature and use also parameters. If you are using 2000, then you will have to deal with dynamic sql.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

AMB

|||Thanks a lot!. This was helpful.

Pass Variables from Trigger to SP ?

Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.

The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).

Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?

TIASomething like:

CREATE TRIGGER trg_mytrigger ON tbl_my_table INSEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = callid FROM inserted

--At this point, you can just to the delete from here, rather than calling a seperate sp_, so here are both ways:
EXEC sp_delete_rows_by_callid @.callid

--or
DELETE tbl_my_table WHERE callid = @.callid

--then do the insert - (you may want to be more explicit with field names here)

INSERT tbl_my_table
SELECT * FROM inserted

--end of trigger

sp_code:

CREATE PROCEDURE sp_delete_rows_by_callid
@.callid INT
AS
DELETE tbl_my_table WHERE callid = @.callid

-b|||Thanks bpdWork. I have modified the trigger as you suggested but I get an ODBC 3146 database error. Can you see where I may have got the syntax wrong. Thx.

CREATE TRIGGER trg_mytrigger ON callshistory INSTEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = fkey FROM inserted as ins where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')

DELETE hold_complete WHERE fkey = @.callid

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')|||I believe this error means Invalid Object Name, so the problem is probably one of your table names.

I'm not sure you can alias the "inserted" keyword. Try it without that.|||The syntax from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED was used in my original trigger which allowed the duplicates so I know that bit works. I also tried the SP option you suggested and got no error message but no rows were deleted or inserted.

Can the inserted table be used outside of the original trigger for the SP ? When does it get deleted ?|||It's only around for the virtual instance the trigger is fired...once the trigger is done, it's gone...|||So should this work ? (BTW it don't.)

CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DECLARE @.callid varchar

SELECT @.callid = fkey FROM inserted

delete from hold_complete where addeddt <> (select max(addeddt)from hold_complete) and fkey = @.callid|||delete from hold_complete where addeddt NOT IN (select max(addeddt)from hold_complete) and fkey = @.callid|||I'm afraid that didn't delete the dupe rows either. Is there anyway that a message can be printed that will show the @.callid variable to make sure that's being captured properly|||You need to understand that the virtual tables will hold ALL of the affected rows...not just 1...your code will only grab the last one from the table...

you need to think set based...

And if you trying to DELETE dups AFTER the INSERT, I would think you would gewt a dup key error, and the trans would rollback and the trigger won't fire...

Assuming you have a pk contraint that is...

Also I would recommend removing the ISOLATION LEVEL code...

If you check @.@.ERROR in the sproc that is doing the INSERT you'll catch the error there...

You can then do an ipdate if you want to change the values...

I wouldn't do this in a trigger (Unless of course you don't have control over the DML, then I would)...|||CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DELETE
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.|||My Bad:

DELETE hold_complete
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Originally posted by Bracksboy
Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.

Cascading triggers?

Also it's not a matter of how likely an event can happen...it's whether it can or it can't...there are no colors here...it's either black or white...

It's not a matter of IF, it's a matter of WHEN

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...|||Originally posted by Brett Kaiser

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...

I don't have a constraint on callid and I don't think it will work with this. If I understand constraints correctly if I set one on callid it won't allow an insert where that callid already exists. Is that correct ?

The inserted data is not going to always have a unique callid so I just need to store the latest row of data and remove all previous rows with that callid. The rows that are inserted are tracking details from a call logging system that meet certain criteria and this can have multiple rows with the same callid. From this data I have a VB app that emails confirmation and then sets dates for chasers and automated closure. I need to delete the existing rows so only the latest info is used in the app.

It's all very new to me.|||You could always just do the delete first, then do an insert.|||but that's the whole point of a realtional database...

You should check to see if the row exists first...if it does grab it, and update it...

Then there's the problem if the row doesn't exist, you start enetering info, someone slides the key while you're typing and get it in...

You need to error handle and check @.@.Error to see if the execution was successful or not....

no MOO's about it...|||Thanks for your help guys but had a complete rethink and I have the application sorting the records I want using the following select statement in the ADO connection :

SELECT * FROM hold_complete a
WHERE exists (SELECT fkey FROM hold_complete b
GROUP BY fkey HAVING max(b.addeddt) = a.addeddt)
ORDER by fkey

Pass variables across forms, then insert into database

Hey Guys:

-- Not sure if this should be moved to webforms forum, or if it belongs here --

Alright, I have been dealing with this issue for a few days now, and have found a few solutions but they all seem to throw different errors so I figured I'd ask here.

What i am trying to do is have a webform where user enter data, and have the data passed across forms, then displayed and inserted into a database on another form. THe first for has an asp:rangevalidator control dymamicly built so I cannot simply take of the tags and use the old style.

Eventually the user will be directed to a paypal form, and upon successful completion be redirected to the page with the insert command within it, but for now, passing it to a second page for review, then inserting it will work.

I am not sure how to accomplish this, a tutorial or a code example would be great!! I have though about panels, creating public objects, etc, but all the solutions I have found have one issue or another when I attempt to create them.

I'm using asp.net 1.1, VB.net and SQL server.

Thanks,
Brian Sierakowskiyou can store the data in some temp tables ( actual tables but we call them temp coz they hold data temporarily) in your DB as the user goes through each page and eventually move them to the actual tables after your final page. this way when the user needs to go back you can always pull the data out of the table. so you would just need to get the autonumber id from the first page and keep it in memory and pass it over to the next page so the subsequent page can update the table using the id.

this is one of the ways you can do..

hth