How to pass parameter values to Stored Procedure using Openquery ?
DECLARE @CenterNumber nvarchar(8)
DECLARE @CenterName nvarchar(100)
DECLARE @tblLeads table(
LeadCount int)
SET @CenterNumber = '98454152'
SET @CenterName = 'neck'
[code]....
Hi
I am trying to execute a stored procedure from another stored procedure by passing parameters. any idea please.
I have tried with EXECUTE PROC dbo.dxGetMemberActivityData (@author) RETURNING_VALUES RESULT
but I am getrting incorrect sytax near PROC
Looking to pass in the @targetDbName into the Open Query.
The target DB is PostGres and requires 2 single quotes around the dataset name.
I have tried many possible variations using the '+ @variableName +'
USE JonathanDB
declare @dqzDateVer int;
declare @targetDbName varchar(25);
select @targetDbName = DB_NAME();
select @dqzDateVer = dqz_date_ver FROM OPENQUERY(ofr_meta_db, 'select dqz_date_ver from ofr_registry.dataset_feed_state where dataset_name=''JonathanDB'' and state = ''Done'' order by row_iddesc limit 1');
print @dqzDateVer ;
print @targetDbName;
I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.
Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.
Anybody run into something like this?
Rob
I have a stored procedure that I use to return Purchase Orders from our PO system. It returns the data rows for PO's that match the criteria passed in (including the page to show etc.) + it returns two output params, Number of rows and Number of Pages.
Using query analyzer I can confirm the query works exactly as we want. I cannot however seem to get the data out to our ASP.net app.
Here is a function that I use in one of my classes:
<code>
Function fnListPOsByCoordinatorIDPaged(ByVal strCoordinatorID As String, ByVal intPOStatusID As Int16, _
ByVal intUserTypeID As Int16, ByVal intArchived As Int16, _ByVal intPageNum As Int32, ByVal intPerPage As Int32, _ByVal strConn As String) As SqlClient.SqlDataReader
Dim dr As SqlClient.SqlDataReader
SqlConnection1.ConnectionString = strConn
prListPOByCoordinatorPaged.Parameters("@CoordinatorID").Value = strCoordinatorIDprListPOByCoordinatorPaged.Parameters("@POStatusID").Value = intPOStatusIDprListPOByCoordinatorPaged.Parameters("@UserTypeID").Value = intUserTypeIDprListPOByCoordinatorPaged.Parameters("@Archived").Value = intArchivedprListPOByCoordinatorPaged.Parameters("@PageNum").Value = intPageNumprListPOByCoordinatorPaged.Parameters("@PerPage").Value = intPerPage
SqlConnection1.Open()dr = prListPOByCoordinatorPaged.ExecuteReader(CommandBehavior.CloseConnection)
Me.Pages = prListPOByCoordinatorPaged.Parameters("@Pages").ValueMe.Rows = prListPOByCoordinatorPaged.Parameters("@Rows").Value
If Me.Rows / intPerPage > Me.Pages Then Me.Pages = Me.Pages + 1End If
Return dr
prListPOByCoordinatorPaged.Dispose()SqlConnection1.Close()SqlConnection1.Dispose()
End Function
</code>
It does not crash, it returns my data reader with the correct records. Unfortunately my property values are returned as 0. They should have values.
Anyone know how to do this?
Thanks.
i have result set from such query :
resultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable | System.Data.SqlServerCe.ResultSetOptions.Updatable;
queryText =
SELECT agvb_id as ID
,agvb_cat_id as Category
,agv_brn_id as Brand
,agvb_brn_name as BrandName
,agv_brn_area as Area
,agv_brn_sku_qty as SkuQuantity
,agv_brn_qty as Quantity
,agv_orderliness as Orderliness
,agvb_importance as Importance
FROM AGENTS_VISITS_BRANDS
WHERE (AGENTS_VISITS_BRANDS.agvb_agv_id = @agv_id)
AND (AGENTS_VISITS_BRANDS.agvb_cat_id = @dad_id)
ORDER BY agvb_brn_name
if i open resultSet with such qury it looses its updatable option.
If i remove from where 1 param (no mater which) everything runs ok.
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
HeyLet's say I have some stored procedure that takes as a param userId and it's simple SELECT statement. I want to assign this param and value in my code how to do it ???Jarod
View 2 Replies View RelatedCan I pass a parameter to a DTS package?
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
Is it possible?
Thanks in advance,
Nishi
Hello,
Basically I am trying to do an order by statement using variables.
I have the SQL:
IF @SortOrder = 'ASC'
BEGIN
SELECT DISTINCT D.Code as 'T1', D.CompanyName as 'T2',
S.SpendAmount as 'T3', E.SpendAmount as 'T4',
E.SpendAmount - S.SpendAmount as 'T5',
Case When S.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/S.SpendAmount) * 100
When S.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
When E.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/E.SpendAmount) * 100
When E.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
END as 'T6'
FROM DivisionData D,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @StartYear and Month = @StartMonth and Division = @Division) S,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @EndYear and Month = @EndMonth and Division = @Division) E
WHERE D.Division = @Division
AND S.Code = D.Code
AND E.Code = D.Code
END
I have the params @Crit1 @Crit2 .... to @Crit6. These parameters come from a web form and can have the values 'T1' 'T2'... to 'T6' They correspond to the field names I have put in my select statement.
What I want to do is use these params in an Order By statement, enabling the user to select how the fields are ordered.
I have tried using something along the lines of:
ORDER BY
CASE WHEN @Crit1 = 'T1'
THEN 'T1' END
This works as long as I dont use the word DISTINCT in my select statement, otherwise I get the error:
Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I do need the distinct... I was wondering if there is a simple way to get round this, or if anyone can point me in the right direction or a article/example of this?
Many thanks
Andrew Rayner
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.
Kudos to y'all!!! In the syntax...
SELECT * FROM OPENQUERY (linked server name, 'SQL Syntax')
...I'm having problems with the " ' " symbol. The SQL Syntax inside the OPENQUERY has a condition in the WHERE clause that has to be enclosed in apostophes. If it's a numeric value, I think that it doesn't need to be enclosed in apostrophes. But because the values are letters, it has to be enclosed in apostrophes. is there any way to work around this problem? I'm fetching data from an Oracle linked server by the way...
Adding numerical field to filters in openquery is as easy as typing thenumber but I am having major problems using strings.I have tried a few solutions including the usual concantenations but nosolution. Is there a simple way of including strings with the filtersfor a query below, possibly without declaring variables and using thestring directly in the code? I have included one of the solutions I wasgiven..Thankyou for any help ...DECLARE @SQL VARCHAR(8000) DECLARE @strVar VARCHAR(1000)SET @strVar = '22'SET @SQL = 'select * FROM OPENQUERY(ISERIES,"SELECT OOLINE.OBWHLO, OOLINE.OBCUNO, OCUSMA.OKCUNM, OOLINE.OBORNO,OOLINE.OBPONR, OOLINE.OBITNO, OOLINE.OBDWDZ, OOLINE.OBORQA,OOLINE.OBATV3, OOLINE.OBATV4,OOLINE.OBATV5, OOLINE.OBROUT, OOHEAD.OADLSP, OOHEAD.OADSTX,OCUSAD.OPCUNM, OCUSAD.OPCUA1, OCUSAD.OPCUA2, OCUSAD.OPCUA3,OCUSAD.OPCUA4, MHDISH.OQDLIX, OOHEAD.OAFACI, CFACIL.CFFACN,OOHEAD.OARGDT, OOHEAD.OAPRTX, mitbal.MBPUIT, mitbal.MBSUWH,OOHEAD.OARESP, OOHEAD.OARGTM, OOLINE.OBORST, mitbal.MBOPLC,(OOLINE.OBATV0) As mark_no,(OOHEAD.OACUOR) As po_no,OOLINE.OBATV6) Ascust_bund_ID,OOLINE.OBFACI,(OOLINE.OBRORN) As DO_no, MHDISH.OQDSDTFROMmvxcdtprod.OOHEAD oohead INNER JOIN mvxcdtprod.OCUSAD ocusad ONOOHEAD.OACONO = OCUSAD.OPCONOAND OOHEAD.OACUNO = OCUSAD.OPCUNOAND OOHEAD.OAADID = OCUSAD.OPADIDINNER JOIN mvxcdtprod.CFACIL cfacil ON OOHEAD.OACONO = CFACIL.CFCONOAND OOHEAD.OADIVI = CFACIL.CFDIVIAND OOHEAD.OAFACI = CFACIL.CFFACIINNER JOIN mvxcdtprod.OCUSMA ocusma ON OOHEAD.OACONO =OCUSMA.OKCONO AND OOHEAD.OACUNO = OCUSMA.OKCUNOINNER JOIN mvxcdtprod.OOLINE ooline ON OOHEAD.OACONO =OOLINE.OBCONO AND OOHEAD.OAORNO = OOLINE.OBORNOINNER JOIN mvxcdtprod.MITBAL mitbal ON OOLINE.OBCONO =MITBAL.MBCONO AND OOLINE.OBWHLO = MITBAL.MBWHLOAND OOLINE.OBITNO = MITBAL.MBITNOINNER JOIN mvxcdtprod.MITMAS mitmas ON OOLINE.OBCONO =MITMAS.MMCONO AND OOLINE.OBITNO = MITMAS.MMITNOLEFT OUTER JOIN mvxcdtprod.MHDISL mhdisl ON MHDISL.URRIDN =OOLINE.OBORNOAND MHDISL.URRIDL/100 = OOLINE.OBPONRAND MHDISL.URCONO = OOLINE.OBCONOLEFT OUTER JOIN mvxcdtprod.mhdish mhdish ON OOLINE.OBCONO =MHDISH.OQCONOAND MHDISL.URDLIX = MHDISH.OQDLIXWHERE mitbal.MBOPLC = 3 and OOLINE.OBORST = (''''' + @strVar +''''')'') 'EXEC(@SQL)
View 1 Replies View RelatedI've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?
Dim strWhere
strWhere = " WHERE dbo.""User"".UID IS NOT NULL "
If Not request.querystring("EmployerID") = "" Then
strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'"
End If
If Not request.querystring("AccountNumber") = "" Then
strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'"
End If
If Not request.querystring("LastName") = "" Then
strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'"
End If
If Not request.querystring("FirstName") = "" Then
strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'"
End If
DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr"))
DBCommand = New OleDbDataAdapter _
("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)
Thanks in advance?
Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @@IDENTITY) as input for some of the other procedures.
I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...
CREATE PROCEDURE addTemplateDetail
@TemplateID int,
@GroupNameID int,
@SubGroupNameID int=null,
@MethodID int,
@AnalyteID int
AS
DECLARE @TemplateGroupNameID int
DECLARE @TemplateMethodID int
SET @TemplateGroupNameID=(EXEC addTemplateGroupName @TemplateID, @GroupNameID)
SET @TemplateMethodID=(EXEC addTemplateMethod @TemplateGroupNameID, @SubGroupNameID, @MethodID)
EXEC addTemplateAnalyte(@TemplateMethodID,@AnalyteID)
I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.
I have a long running trigger that makes calls to several tables in order to get values for a list of parameters before doing my final INSERT statement into a different table.
One of my parameters is for a local language translation of a particular word which is stored in a table. The problem is - I do not know the name of the table until runtime when I dynamically build the name as follows:
DECLARE @SQL nVarChar(200)
SET @SQL = 'SELECT @Translation = nvcLocalEventDescription FROM Monitoring.dbo.tblSignalTemplate'
+ CAST(@MonitoringCentreID AS nVarChar) + ' WHERE nvcEventDescription = "' + @EventDescription + '"'
EXECUTE Management.dbo.usp_parmsel_LocalEventDescription @SQL, @LocalEventDescription OUTPUT
If there is a MonitoringCentreID of 1234, then there will be a table named tblSignalTemplate1234 - which will contain a nvcLocalEventDescription field containing the value that I am after. Here is the code for the stored procedure...
CREATE PROCEDURE [dbo].[usp_parmsel_LocalEventDescription]
@strSQL nVarchar(150),
@Translation nVarChar(100) OUTPUT
AS
EXECUTE sp_executesql @strSQL
GO
The error I get is "Must declare the variable '@Translation'" - which has thrown me a little as it declared on the 3rd line of the stored proc.
Anyone got any ideas where I am going wrong, or as usual, is there a simpler way ?
Steve.
Hi... I am trying to display a parameter in my report.. the parameter can have up to 5 chocies... if all 5 are checked I want to display all 5..
I know how to trick it and use: Parameters!Country.Value(0)&Parameters!Country.Value(1) etc
Is there a way to do this that I dont have to have from (0) to (5)..
another thing... when you only choose 2 of the 5 params the rest show errors.. (#Error)
Thanks for help... and Happy Reporting !!
Hi
I have an arraylist that contains the names of those userid's that i need to check in an online db to check out if they are online now.If the UserId column of the SqlTable matches any of the name of that of the arraylist, then i need to import the values of two corresponding fields say age, Nick etc.I would be very grateful to anyone who could kindly tell me how to do this in an Sql Query i.e How to actually send an arraylist into sql query or any other way around this problem.
Say the arraylist to be verified against the table is:public ArrayList BuddyList = new ArrayList();
Thank You.
Regards.
How do I pass the user’s LoginName into the WHERE clause of a SQL query?
I created the following query, but I don’t know how to get the user’s LoginName and pass its value into Param1. Nothing I try works.
SELECT property.propertyid, property.shortdesc, property.shortdesclink, property.text, property.UserID, [User].UserID AS Expr1, [User].Name FROM property INNER JOIN [User] ON property.UserID = [User].UserID WHERE ([User].Name = @Param1)
Hi all,
Can somebody tell me how I can pass column name of a table to a stored proc.
Here's something close to what I'm trying to do....
Declare @colname varchar(30)
set @colname = "TITLE"
select name, id
from contacts
where contacts.@colname = "TITLE"
Regards
Uday
What is the easiest way to pass a value into a variable...
TSQL...
declare @@test int
--select count(*) from authors
select count(*) into @@test from authors
I am looking for any help or hints on how to pass a query into or run a query as sp_send_cdosysmail as the body.
I need some way other than using an attachment to return the results of a query from sp_send_cdosysmail as the body. Any good points in a direction would help!
Hi All,
I have problem creating query. here is the scenario
I have a table EMPLOYEE , the fields are
Employee Name, month1,month2,month3,..month12
thomas,100,1200,400...1200
wilson,500,1100,500...1000
carol,200,500,200... 3400
I am creating report using visual studio reporting services
I am passsing the report parameter to the query, but i could not the actuall result
MY PARAMETER is 2 ( varrialble is gmonval)
I am writing the query like,
select empname,"month"+Ltrim(@gmonval) from employee
the result which am getting is
thomas,month2
wilson,month2
carol,month2
it suppose to be
thomas,1200
wilsom,1100
carol,500
how to get the above result. please help
Regards
Saleem
Hi
I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted.
Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas.
Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.
This is not obvious to me...
As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...
Ok, this means when you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos) you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...
Is this correct?
Is there any other way to approach the problem?
Thanks a lot. -julio
Hi,
Is there a way to pass the column name as a query parameter?? If I use '@Question', like below, I get an error. If I change it to the actual name of the column 'Question1', it works fine. However, I need it to be dynamic so I can pass the column name [Question1, Question2, Question3, etc...] in and not have to define this for each question.
Doesn't Work!!
Code:
SELECT
1.0 * SUM(CASE WHEN @ColumnName > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN @ColumnName = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN @ColumnName < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'
FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID
WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)
Works, but I need to pass in the column name dynamically.
Code:
SELECT
1.0 * SUM(CASE WHEN Question1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good',
1.0 * SUM(CASE WHEN Question1 = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK',
1.0 * SUM(CASE WHEN Question1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor'
FROM tableA AS A INNER JOIN
tableB AS B ON A.SessionID = B.SessionID
WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)
Frustration has gotten the best of me on this one. Can anyone help?
I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work.
Private Sub Command44_Click()
Me.SS.SetFocus
Dim strSQL As String
Dim strSSecurity As String
strSSecurity = Me.SS
strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity"
DoCmd.RunSQL strSQL
End Sub
It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string)
But it doesn't
Anyone with any ideas?
Thanks
Dan
I have a query :Exec 'Select * From Receiving Where Code In (' + @pCode + ')'@pCode will contain more than one string parameter, eg : A1, A2, A3How can i write that parameters, I try use :set @pCode='A1','A2','A3'but get an error : Incorrect syntax near ','Please help meThanks
View 10 Replies View RelatedHello,
I'm using
IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
to extract data from AS400 and copy them to SQLServer2005. It's my first experience with exptracting data from AS400.
I need to pass in SSIS a parameter to the extract query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > ?
I'm naming the parameter in the mapping as 1 but geetting an error
[as400 [1]] Error: The SQL command requires a parameter named ""00001"", which is not found in the parameter mapping.
I have tried 00001, '00001', ("00001" is not accepted from SSIS) but getting still this error.
A query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > '1071205'
works OK.
Can anybody help me ?
thanks in advance
Hi all,
I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.
The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.
I have successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query
************************************************** *********************8
CREATE PROCEDURE qse_check_label_projectid
@projectid char(18)
AS
Select * from openquery(MRTEST32,'SELECT
Project_id
FROM
DBO.MRT_PROJECT
WHERE
Project_id = @PROJECTID ')
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!
Hello Everyone
Can someone please confirm, wether it is possible to execute a stored procedure in a sybase/or any other database from within an open query function in a SQL 7 database.
I know that we can execute a dynamic SQL statement, what about a stored procedure in the linked server. Can we execute that with parameters??
THANKS
Hi,
SQL2K+SP4
When I load up Query Analyzer and click File -> Open to open up a query
file, the open file dialog is always sorted by - i don't know
alphabetically.
How can I set the default to open up with files ordered alphabetically?
Thanks
Helena