Passing The Same Data To Every SP
Jan 17, 2008
Hi,
I have a slight problem. I would like to pass some form of out of band data to every SP when called using ADO.NET.
A little more info is needed I think. I need to pass a unique identifier to ever SP my application calls and have that identifier available to use by the SP.
As my application has many many SP's in use I am trying to avoid adding an extra parameter to each and every one. I was really hoping for the ADO command object to have a property I could use to pass 'user defined' data to the database engine.
For example:
My SP's write to an audit trail table. The audit trail needs to record the user that executed the SP, this user might not be the same user that logged on to the database (depends on the connection string really). So, instead of adding an extra param to each SP, I want to access some data from the ADO command that was used to call the SP.
Can anyone help, or have I just confused everyone?
Graham
View 4 Replies
ADVERTISEMENT
Nov 1, 2005
OK, it's the first of the month...that must mean it's time for another dumb question!
View 10 Replies
View Related
Apr 8, 2007
hello everybody, i hope u can help me with this... i want to pass the data entered in a web page to a stored procedure so as to store the data in a database... am using three layered architecture... how can i do this.... thanks in advance.............
View 1 Replies
View Related
Feb 25, 2008
Hi
Can anyone help with this. Is it possible to pass data from a sub-report to the main report. I know this was possible in Crystal.
I currently have VS2005 but may be able to justify a move to 2008 if it can do it.
Thanks
View 10 Replies
View Related
Jan 13, 2006
Hi
Does anyone know what would be the best technique to use for passing constants into data flows shapes?
For example if I had a lookup that required a static value to be passed into it as part of a concatenated key etc...
Cheers
Al
View 1 Replies
View Related
Jul 11, 2007
Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?
Thanks,
View 17 Replies
View Related
Apr 27, 2006
Hi,
I've read the various posts and articles regarding this matter, but I seem to have problems getting to work:
In my control flow, I start by declaring a variable named "LastJobLedgerEntryID", to identify the records I need to add to the stage. From there I would like to use this variable in the source component in my dataflow, i.e.:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > " + @[User::LastJobLedgerEntryID]
But this fails? I should note that the variable LastJobLedgerEntryID is stored as a int32, and with the default value of 0
Could someone please help me with this?
Thanks in advance!
View 5 Replies
View Related
Apr 9, 2008
I have a stored proc that I am trying to use for sorting and paging below is a portion of it. When I hard code "Stan" into the Where clause I get all the records back that where posted by "Stan" but when I try to pass in "Stan" as a variable it tells me something about an invalid column name.
Here is the working version. When I hard code "Stan" into the Where Clause it works. I am using 2 single quotes on each side. See Where clause.
SET @sql ='SELECT [ThreadName],[PostID],[PostTypeID],[LanguageID],[PostAccessID],[UserID],[ThreadID],[PostParentID],[VoteSummaryID],
[Subject],[Body],[PostAuthor],[PostDate],[IsApproved],[TotalViews],[FormattedBody],[IPAddress],[PostCount],[ArticleCount],
[TrackbackCount],[IsSticky],[StickyDate]
FROM
(SELECT t.[ThreadName], p.[PostID],p.[PostTypeID],p.[LanguageID],p.[PostAccessID],p.[UserID],p.[ThreadID],p.[PostParentID],p.[VoteSummaryID],
p.[Subject],p.[Body],p.[PostAuthor],p.[PostDate],p.[IsApproved],p.[TotalViews],p.[FormattedBody],p.[IPAddress],p.[PostCount],p.[ArticleCount],
p.[TrackbackCount],p.[IsSticky],p.[StickyDate],ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
FROM syl_Posts p
INNER JOIN syl_Threads t ON t.[ThreadID] = p.[ThreadID]
WHERE t.[PostAuthor] = ''Stan'')
AS syl_TPInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
Not working when I try to pass in a variable into the Where clause.
SET @sql ='SELECT [ThreadName],[PostID],[PostTypeID],[LanguageID],[PostAccessID],[UserID],[ThreadID],[PostParentID],[VoteSummaryID],
[Subject],[Body],[PostAuthor],[PostDate],[IsApproved],[TotalViews],[FormattedBody],[IPAddress],[PostCount],[ArticleCount],
[TrackbackCount],[IsSticky],[StickyDate]
FROM
(SELECT t.[ThreadName], p.[PostID],p.[PostTypeID],p.[LanguageID],p.[PostAccessID],p.[UserID],p.[ThreadID],p.[PostParentID],p.[VoteSummaryID],
p.[Subject],p.[Body],p.[PostAuthor],p.[PostDate],p.[IsApproved],p.[TotalViews],p.[FormattedBody],p.[IPAddress],p.[PostCount],p.[ArticleCount],
p.[TrackbackCount],p.[IsSticky],p.[StickyDate],ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
FROM syl_Posts p
INNER JOIN syl_Threads t ON t.[ThreadID] = p.[ThreadID]
WHERE t.[PostAuthor] = ' + @PostAuthor + ')
AS syl_TPInfo
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
View 21 Replies
View Related
Jun 19, 2006
All,
Is it possible to passing variable at row level within a data flow? If so, what transformation should use?
Thanks
View 6 Replies
View Related
Sep 21, 2007
I need to convert a Crystal report that contains three subreports. Each of these subreports uses a different stored procedure to obtain data. The main report uses data from these subreports to calculate averages in the footer etc.
How do i get the data contained in these subreports so i can use it in my main report??
Thanks in advance
View 4 Replies
View Related
May 22, 2007
I am running a sql task which will pass table as object variable to the result set
I have a for each loop container which is used to loop for all the servers. I use two of the parameters to establish connection string in the for each loop task from the reasult set variables.
Now my next step is a data flow task (Data Reader Source) where i have to run a query but the table name and column names are dynamic and i dont see an option to call variables.
can someone tell me if this is possible (var1,var2,... are variables in the package scope)
select var1, var2 from var3.var4
where var5 = 'y'
View 9 Replies
View Related
Feb 25, 2004
Hi,
I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.
The exact error message that occurs is:
ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.
The error occurs in the following code line:
.parameters.Append cmd.CreateParameter ("@EMedical", advarwchar, adParamInput)
I need to know what to write instead of advarwchar?
Thanks in advance
View 1 Replies
View Related
Apr 24, 2007
Hi,
I've been asked to set up a data driven subscription for a number of reports which use multi value parameters. For example, show me all transactions against the following departments: IT, Building Services, Accounts.
As an interactive report it's simple, the user just selects the relevant departments, but as a data driven subscription I can't seem to find the correct format to pass the selections through.
Has anyone tried this before?
Thanks,
Dan
View 1 Replies
View Related
Jun 13, 2007
I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.
When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.
I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.
Has anyone else experienced this issue and do you have a resolution?
Thanks in advance for your assistance.
View 5 Replies
View Related
May 21, 2007
Hi guys,
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
- despesaTableAdapter (from myDataSetTableAdapter)
- despesaBindingSource (Windows.Forms.BindingSource)
- RelatorioDataSet (from myDataSet)
- relatorioDataSet1 (from myDataSet)
Any help will be very appreciated.
Tks and rgds,
Luis Antonio
View 1 Replies
View Related
Jul 24, 2007
I have created a custom data extension.
I am using this custom data extension from BI Studio.
Que1: I want to create report parameters from BI studio and capture them in my Data Extension.
Que2: Every report parameter has so many properties, like hidden, internal, default value etc, how do capture that information in my data extension, the parameters only give me name and value.
Que3: I know I can capture command Text. So My command can be Select * From tablename where fieldname= @myFieldname. Once I get this commandText in my data extension, How do extract parameters of the command?
Que4: If I have any custom attributes in my RDL file, Can I capture them in my Data Extension ?
Any help is appreciated...
View 1 Replies
View Related
Jul 2, 2015
I have a sp where query is as below.
DECLARE @ServerCIName varchar(5000)
SELECT * FROM dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.
But @ServerName contains multiple values and it is dynamic (not constant).
How do I query the data?
View 6 Replies
View Related
Jul 16, 2009
I am building a webapp that calls an SSRS instance to display a report based on another servers web service.I can make a call from the ssrs server using this in the RDL:
<Query>
<Method Namespace="http://machine/webservices" Name="HelloUser">
<Parameters>
<Parameter Name="user">
<DefaultValue>Craig</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>
This works no problem. but.. I want to have the user parameter "Craig" be a parameter passed in from the web app. If it were a regular SQL data source you would put @user in the query. How do you do it with XML text queries?
View 5 Replies
View Related
Nov 29, 2010
I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.
Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:
<Query>
<Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">
<Parameters>
<Parameter Name="Criteria" Type="XML">
<DefaultValue>
[code]....
View 17 Replies
View Related
Jun 23, 2015
Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General Tab:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;
Parameter Mapping:
Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10
Variables:
location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table
Sample Data:
SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8
STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.
I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.
View 6 Replies
View Related
Mar 18, 2008
I'm running into this error message when passing in a few records in particular to a function, the only difference I could find is that these recods have about 60k characters on the field that I'm passing to a function.
is there a max lenght for passing to a function?
select function ( field) as results
It's been working fine until today and all of the related fields are declared as nvarchar(max)
Thank you.
View 5 Replies
View Related
Sep 1, 2014
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
View 9 Replies
View Related
Apr 24, 2007
Help!
Attached proc is working fine. But how should I pass the value of @LexNexID without manul key in the value. How can I call this proc. to update comment in table tblSEMS_FTP_FINAL automatically
Thk
CREATE PROCEDURE DBO.SP_SEMS_ADD_COMMENT (
@lexnexid int,
@commentstring varchar(4000)
)
AS
DECLARE @Comment varchar(4000)
--DECLARE @CommentString varchar(4000)
--DECLARE @LexNexID INT
--SET @LexNexID = 1
SET @CommentString = ''
DECLARE Test CURSOR
FOR
SELECT
convert(varchar(4000),Comment) COMMENT
FROM dbo.TBLSEMS_FTP
WHERE LEXNEXID = @LexNexID
OPEN Test
FETCH NEXT FROM Test
INTO @Comment
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CommentString = @CommentString + @Comment + '{CR}{LF}' + ' '
FETCH NEXT FROM Test
INTO @Comment
END
CLOSE Test
DEALLOCATE Test
--PRINT @CommentString
update tblSEMS_FTP_FINAL
set comment = @CommentString
where lexnexid=@LexNexID
GO
View 6 Replies
View Related
Mar 31, 2007
Hi Guys,
Im trying to pass a value from a C# page to the corresponding ASP page.
I want to use this value in my SQL Query on the asp page within a Details View.
Ive made the variable public in c# page.
And im trying to concatenate it to my sql query within the <asp: SqlDataSource.
Thanks.
View 12 Replies
View Related
Jul 8, 2007
A beginner's question I'm sure. When visitors have logged into my site, how do I then use their UserName as a parameter in an SQL WHERE clause so that I can pull up their detailed information. I'm using VB if additional code is required.
Thanks in advance.
View 2 Replies
View Related
Mar 14, 2008
Hi,
how to pass paremeters in sqldataadapter? here iam checking authentication using sqlcommand and datareader
like that if iam checking authentication using dataset and sqldatareader how to use parameters?
SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz;data source=server");
cn.Open();
// string str = "select username from security where username='" + TextBox1.Text + "'and password='" + TextBox2.Text + "'";
string str = "select username from security where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(str, cn);
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
Session["username"] = TextBox1.Text;
FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, false);
}
else
{
Label1.Visible = true;
Label1.Text = "Invalid Username/Password";
}
-------------------------------------------------------------------------------------
like that if iam checking authentication using dataset and sqldatareader how to use parameters?SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz; data source=server");
cn.Open();ds = new DataSet();
string str = "select username from security where username=@username, password=@password";da = new SqlDataAdapter(str, cn); //how to pass parameters for that
da.Fill(ds, "security");
Response.Redirect("dafault2.aspx");
View 2 Replies
View Related
Jan 2, 2004
I have a DDL with two values: 0 and 1. When I use
sqlcommand.Parameters.Add(New SqlParameter("@fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@fld").Value = System.Boolean.Parse(DropDownList1.SelectedValue)
or
sqlcommand.Parameters.Add(New SqlParameter("@fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@fld").Value = System.Convert.ToBoolean(DropDownList1.SelectedValue)
to insert the selected value in a sql2k database BIT field I am given this error:
System.FormatException: String was not recognized as a valid Boolean.
How can I solve this problem?
View 1 Replies
View Related
Apr 27, 2004
Hi,
i m tring to pass my xml string to my sql server but i receive empty value even tho my xml have it's value.
here's my code,
insert user(userName)
select xml_user
from OPENXML(@xmlHandle,'/NewDataSet',1)
with (xml_user nvarchar(20) 'table/user_name')
my XML code
<NewDataSet>
<table>
<user_name>dorris</user_name>
</table>
</NewDataSet>
Please advice on this matter.Thanks
View 1 Replies
View Related
Jun 16, 2004
Hi,
i manage to passs an xml string to sql server based on the reference site(http://www.eggheadcafe.com/articles/20030627c.asp by Robbe Morris). From his way, i can only pass XML in this format
<cart>
<order id="10">
<prod id="1" qty="1"/>
<prod id="2" qty="3"/>
</order>
</cart>
Note : the value is stores as attribute (<prog id="1") . I am just wonder can i actaully pass my xml string to sql Server with this format
<cart>
<order id="10">
<prod>
<id>1</id>
<qty>3</qty>
</prod>
</order>
</cart>
and how can i read the value from this format in my stored procedure?
Regards,
Dorris
View 5 Replies
View Related
Oct 18, 2005
Hi,I have a problem while passing a date value to datefunction.My query is like that ..declare @todate varchar(20),@FromDate varchar(20)set @todate='18/10/2005'set @FromDate = '11/10/2005'print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@todate)), 0)But i getting the error like this..Server: Msg 242, Level 16, State 3, Line 4The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Can any body help me ..Thanks and RegardsAJOSH
View 1 Replies
View Related
Jan 18, 2002
Hi,
I have a stored proc as follows:
Create stored procedure sp_test
@Cno nvarchar (1000) AS
SELECT * FROM Contracts WHERE Cno IN (@Cno)
How do I actually pass the parameters to the stored proc?
sp_test "'cn01','cn02','cn03','cn04'" doesn't work.
In VB.NET this doesn't work either:
dim s as string = "'cn01','cn02','cn03','cn04'"
cmd.parameters.add(new sqlparameter("@Cno",nvarchar,1000))
cmd.parameters("@Cno").value = s
I don't want to parse the string inside the proc and then Exec the parsed string.
TIA
Jeremy Holt
View 5 Replies
View Related
Sep 3, 2004
I would like to pass the name of a table in my update query. It goes something like :
set @Table = 'TableA'
UPDATE
@Table
SET
Company_id = @Company_id
However I get a syntax error when I try to pass the table name like this.
View 1 Replies
View Related
Jun 21, 2007
What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.
SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHEREcontact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)
View 2 Replies
View Related
