Sending User Variables In
May 23, 2006How do I set user variables from C# when executing a package? I am using the using Microsoft.SqlServer.Dts.Runtime; namespace.
Thanks,.
Marty
ADVERTISEMENT
How Find User Is Sending A Packet Every .02 Sec To MS SQL?
Jul 23, 2005I have 2 users that their client software must be going crazy.. theyare sending packets every .02 seconds to the db server... I know thisbecause I stuck a sniffer on teh traffic.. but now i just need to knowwhat user is doing this (all traffic is encrypted.. so i couldn’tsniff out that.. i could only get an IP).any ideas?--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict232446.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=805276
View 8 Replies View RelatedSending User Defined Column Names To A View
Oct 19, 2006I want to pass user-defined column names to views or procedures
I am using SQL Server 2005 Express.
I tried this.
CREATE PROCEDURE @userDefinedColumn varChar(10) = 'My_Column'
select @userDefinedColumn, count(ID) as [Total Records] from My_Table
GROUP BY @userDefinedColumn
This was not accepted. It won't allow a user defined parameter in the group by.
But I need to pass user defined parameters to views as well.
User Defined Variables
Jan 5, 2007I am running a procedure that declares a table with a user defined variable called ZipCodes8000. This datatype is on the systypes table as a valid datatype that we created. Here is the code and the error that is returned. Can anyone help me understand why I receive the error that I do.
Code:
DECLARE @SortPlanBinZipCodeRanges TABLE
(
SortPlanBinZipCodeRangeIDINT IDENTITY (1, 1) NOT NULL,
SortPlanBinID INT NULL,
SortPlanID INT NULL,
BinTypeID TINYINT NULL,
BinFlagTypeID TINYINT NULL,
BinNumber INT NULL,
ZipCodeRanges ZipCodes8000 NULL
)
error:
Msg 2715, Level 16, State 7, Line 1
Column or parameter #7: Cannot find data type ZipCodes8000.
Thank you in advance.
Todd
DTS Global Variables Behaviour With &> 1 User
Jun 22, 2004Hi all
I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...
The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?
The dts package is from a sp using the dtsrun utility (passes in global variables).
I know that a second DTS will start before the first has finished but what i am not sure about is what will happen to the Global Variables when the second DTS package starts - i.e. will starting a second instance of the package with different variables have an effect on the first DTS's global variables while it is running?
I hope the answer is "No Tom, it work fine!"
Local Variables In User Defined Functions
Mar 24, 2004I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?
View 4 Replies View RelatedStoring Variables From A User-defined Function
Jul 20, 2005I've looked all over but can't find a solid answer. I've got afunction that runs from a View and when the function runs the firsttime it is calculating a Temperature for a group of Formulas. What Iwant to do is calculate this temperature in the UDF on the first passand store it somewhere (table, global variable etc.). On everyexecution after that I'd like to use the value stored so I don't haveto recalculate the value again for the specific group defined.I've looked at storing the data in a table from the UDF but UDF'sdon't support dynamic SQL statements and can't run a stored procedure.The key here is we have a view that calls a UDF (user-definedfunction). Using SQL 2000.Any ideas would be very helpful. Thanks.
View 2 Replies View RelatedUsing User Variables In Derived Column Transformation
Apr 24, 2006My derived column transformation with a user variable expression displays the user variable's default and not its assignned value.
Background: I built a script component that seems to effectively assign a new value to a user variable. I use local variables within the component, make the assignment in the PostExecute subroutine, and check by writing the user variable to a messagebox there.
In the derived column transformation I create a new column and insert the user variable in its expression field, add a data viewer, and send it to a data destination. The data viewer shows the user variable default and not the assignned value.
I replaced the user variable with a system variable in the expression field and that works fine.
Have folks successfully used this scenario before? Any ideas? Thanks.
User Variables In SSIS Package Scope
May 8, 2008I have created two User variables at Package level (OldRunID{Int32} and NewRunID{Int32}). I am using two simple ExecuteSQL Tasks to get a Single Row Resultset return (each) and loading them (on Result Set Page). Using the Constraint and Expression Flow controls, I have found that they load to True (-1) instead of the 25 and 50, respectively, that I would expect. I then wrote a Script task to evaluate their values and find that I get zero for Dts.Variables.Count. - Why am I not loading the variables correctly and why can I not access them in the Script task?
View 18 Replies View RelatedGood SSIS Example Tutorial - User Variables
Jun 30, 2005Hi There
View 13 Replies View RelatedUser Variables And SQL Agent - Jobs - Set Values
Aug 21, 2007From the microsoft technet How to: run a package using a SQL server agent job (http://technet.microsoft.com/en-us/library/ms139805.aspx):
Click the Set Values tab to map properties and variables to values.
Note:
The property path uses this syntax: Package<container name>.<property name>. Depending on the package structure, a container may include other containers, in which case nested containers are separated by a back slash (). For example, PackageMyForeachLoopMySequenceMyExecuteSQLTask.Description.
If this is the syntax for writing the container.property value, how do you write the variables?
User Variable Doesn't Appear On Variables Collection???
Sep 7, 2006I've created a user variable but when I'm going through its variable collections for that TaskHost appears all the system users but not mine.
Why??
?th.variables.count
26
When I have 27 including the mine.
How To Use User Defined Variables As Table Names In SSIS
Dec 4, 2007I am a SSIS novice, and need help to do the following:
Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.
I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
In short can any one tell me a correct way of using a table name as variable in
Data Access Mode : Table name or view name variable ?
One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query
exec( 'select * from ' + @table)
or just
select * from @table
Any suggestions will be appreciated.
Comparing And Rounding User Input Variables To Table Values?
Oct 24, 2006HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
View 9 Replies View RelatedExecute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)
Sep 4, 2006 Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?
Jan 24, 2006Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed
Dec 7, 2006This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Sending Out An SOS
Apr 1, 2008Thanks in advance...
What I have to do is take orders and generate a report that gives the daily totals of the orders The dates will be on the vertical and the subclass titles on the horizontal: Output
date SO PS Total
1/1/08 5 4 9
1/2/08 3 2 5
1/3/08 5 4 9
mtd 13 10 23
ytd 13 10 23
The SO orders for example can have a diff status each day and I will
need to count this .
1 - how would I pull the orders that I want. I have pulled all credit work orders in but I only want the ones from 2008. Then once I pull them in how do I get the daily info., ie what records fell into the categories for that day. Do I need a loop, cursor, case statement? I also need to count the # of any give group as above. I am new at this and really do not know where to proceed.
see table below
CREATE TABLE #CREDIT_WO (
WO_Noint,
WO_Status varchar (1),
WO_Classvarchar (30),
WO_Subclassvarchar (30),
WO_Create_Datedatetime,
WO_Sch_Datedatetime,
WO_Post_Datedatetime,
WO_Statevarchar (2)
)
INSERT INTO #CREDIT_WO
(WO_NO, WO_Status, WO_Subclass, WO_Create_Date, WO_Sch_Date, WO_Post_Date, WO_State)
SELECT wowo_wono, wowo_status, wtyp_wo_class, wowo_wo_date, wowo_sch_date, wowo_post_date, wowo_state
FROM WO_WO with(nolock)
inner join wo_type with (nolock)
on wowo_wo_type = wtyp_wo_type
WHERE wo_class = 'Credit'
and subclass in ('so', 'ps')
and wowo_wo_date between @Start_date and @End_date
Sending Mail Through SP
Mar 28, 2008I have written a stored procedure which should send mail from the stored procedure in sql server 2005. Here is the SP
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER Procedure [dbo].[sp_SMTPMail]
@SenderName varchar(100),@SenderAddress varchar(100),@RecipientName varchar(100),@RecipientAddress varchar(100),@Subject varchar(200),@Body varchar(8000),@MailServer varchar(100) = 'smtp.mail.tspl.com'
AS
SET nocount on
declare @oMail int --Object referencedeclare @resultcode int
EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
if @resultcode = 0BEGINEXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserverEXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderNameEXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @SubjectEXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
EXEC sp_OADestroy @oMailEND
SET nocount offexec sp_smtpmail 'ramana','venkataramanaiah.uppalapati@tspl.com','rajan','ramana.u@gmail.com','test','This is a sample message'
when i execute the SP by giving the following input parameters like
exec <spname> 'rajan', 'rajan.crp@tspl.com', 'venkat', 'venkat@tspl.com', 'test', 'Test Message'
It ran successfully but the recepient venkat@tspl.com did'nt receive any mail from rajan.crp@tspl.com
I used the following commands for configuring OLE Automation Procedures
exec sp_configure 'Show
Sending Reminders
May 20, 2008Hi all,
In my web application, I need to send reminders to a list of persons in that database to fill out a form , once in every month.
Suppose a person have not filled the form, then agaian afetr 15 days need to send a reminder only to him . And if he doesnt fill it again I need to sned another reminder after 10 days . ...
In one word .. i need to snooze that person who have not complted the form continously and the end of month, even if doesn't complete, I need to tell him tto contact the Admin.
How should I do it in my application. ?
How to schedule a task periodically???
Please Help.
Your Help will be very much Appreciated.
Thanks in advance.
Sending SQL Email
Jun 20, 2005I have developed a B2B Application which is successfully running, now the problem is that my client says that he need a process which sends report of total orders on daily bases.
I am using SQL Server and think that SQL Mail is a good idea, I can find many articles about which needs Outlook for that which is much difficult on the web server. Remember I need to send email only not to receive.
Thanks, any reply will be highly appreciated.
Sending Mail
Apr 27, 2000I am having a problem with SqlServer 7.0 sending mail when the sqlserver account is not logged into the machine and mail is open. If I log off of the box and an alert sends mail it just sits in the outbox of the mail client until I log in. Is this the way it is supposed to be or am I doing something wrong. I am using the Inbox as my client.
Thanks
Tom Goltl
Sending An Email From Within An SP
Jan 8, 2001The following SP adds a record to a table. I want to then send an email from within this SP to notify someone that their login is blocked. I can get their email address easily with a simple select but how does the xp_sendmail procedure actually fit in ?? I've tried adding it in just before the raiserror part but it doesn't work ? The raiserror part is used to trigger an alert to notify me but i need to also notify the client. Can anyone see what i am doing wrong please. Many thanks.
CREATE PROCEDURE usp_log_BlockedLogin_ins
@LoginName varchar(50),
@IP_Address varchar(15),
@Failed_Pswd varchar(50)
AS
BEGIN
DECLARE @LoginID int
SET @LoginID = (SELECT fa_loginID FROM tbl_Login WHERE fs_LoginName = @LoginName)
INSERT into tbl_Login_Audit_Trail
(
fi_LoginID,
fs_IP_Address,
fd_DateTime,
fb_Blocked,
fs_Failed_LoginName,
fs_Failed_Password,
fb_FailedAttempt
)
VALUES
(
@LoginID,
@IP_Address,
current_timestamp,
1,
NULL,
@Failed_Pswd,
1
)
END
BEGIN
RAISERROR (50001,10,1) WITH LOG -- this is used to trigger an alert
END
GO
Sending Email
Mar 9, 2001I want to send an email to all the addresses that are contained in a table. I have tried using xp_sendmail, but I just get an error saying that xp_sendmail cannot be found.
Any ideas???
Many Thanks
Sending Email
Apr 25, 2008Ok I am going to try and explain this best as possible.
I have a report that gets everyones name who has not filled in attendance for a class. what they want is when the report is ran an email gets sent out. so I have written the sql to create a temp table that will get the distinct email.... so lets say the table consists of
jacob.ostop@synergit.com
xxxx@yyyy.com
gagsag@shsna.com
what i need to do now is get those three address and add them to a string that would be in the format of
jacob.ostop@synergit.com ; xxxx@yyyy.com ; gagsag@shsna.com
so that i can put that in the TO field of my email.
my problem is that i dont know how to get them each 1 at a time and put in semi colons..
can someone please help me!
Help Sending Email
Jun 23, 2006My code below works fine when run from my pc (changed all the values forobvious reasons). The code is placed inside a DTS task via VBS scripting.But when I try to run directly from the server where sqlserver is installed,the script fails.I have SMTP running, but there is no outlook installed.Can someone please advise what I am missing.ThanksBobSet objEmail = CreateObject("CDO.Message")objEmail.From = "send@test.com"objEmail.To = "receive@test.com"objEmail.Subject = "TEST SUBJECT"objEmail.AddAttachment "\server est.csv"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "SERVER_NAME"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "userpwd"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25objEmail.Configuration.Fields.UpdateobjEmail.Sendset objEmail = nothing
View 3 Replies View RelatedSending Messages With ADO.NET 1.0
Mar 22, 2006Hi, after some tooling round, googling, hair-pulling etc i have some prototypes of a simple C#.NET 1.1 console app which can now post a message to my yukon SB service i set up. It calls an internal activation stored proc to store the message in a simple table.
The problem i had was in the connection string "... Asynchronous Processing=true". The run-time throws an exception moaning about the Asynchronous Processing=true bit. So i just omitted it completely... Seems to work fine and as expected. I can post a message, my app exits, and the dummy stored proc (which i deliberately made enter a loop updating the table thousands of times to slow it down and lock the table) completed several seconds later proving the asychronousity of it (i issued a "select count(*) from X" query to wait for the write lock to be released, so i knew when the stored proc had finished and released its lock).
So, i guess my question is, firstly what is the "Asynchronous Processing=true" bit for in the connection string if we can just leave it out? And can anyone see any issues with what ive done?
Sending Null Value
Mar 6, 2007Hello. A question please. How can I send a null value from vb.net to a datetime field of Sqlserver ?, This fields allows null values.
Thanks...
Sending SQL Mail
Jul 22, 2006Hi,
I am using SQL Server 2005. I have configured my email on Database Mail. when I am trying to send email using xp_sendmail I am getting problem error as:
Msg 17925, Level 16, State 1, Line 0
xp_startmail: failed with mail error 0x80040111
I am using following sql statement
master.dbo.xp_startmail 'myaccount','mypwd'
master.dbo.xp_sendmail 'tomail1@mail.co.in','test'
where myaccount and mypwd is my password on my email profile.'tomail1@mail.co.in' is the destination email address..
thanks
Sending Automated Sql Emails
Mar 12, 2007Hi
I am using asp.net framwork 2 with sql 2005 express edition. Can anyone please provide me any sample of sending automated emails (like welcoming people when they register to my website, or if their personal details have been updated....). or any e-book/website ref will do. I am not very expert in sql/db and smtp (or other email) stuffs. So please help me.
With thanks
Sending Databases/tables ?
Dec 21, 2007Hello,
Let's say I created some tables in SQL server using the designer. Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table? Or how would I send somebody else the tables?
Thanks.
Sending E-mail On SP Action
Jun 18, 2008Hello all,
I've run into a situation which I'm not sure which option to use. I have a web-based app that needs to send an email to a user once an auction has ended (the user being the winning bidder). The backend of the app is SQL 2005. I'm trying to figure out how to get this to work but I'm not sure if I should be using Database Mail or if I should be using a windows service on the web server that pings the database to check if the auction has ended and if the user has been notified. Can anyone shed some light as to which way I should be doing this? Additionally, I'm planning on formatting the style of the mail message so it'll need to be in HTML format and not plain text.
Any help is greatly appreciated.
Regards,
Axel