Order By Using @Params

Oct 31, 2007

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

View 6 Replies


ADVERTISEMENT

Report Run OK W/ Default Params But Not Changed Params

Mar 25, 2008

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

View 5 Replies View Related

SqlDataSource Params ?

Jan 27, 2006

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 Related

Can DTS Take Input Params?

Oct 13, 1999

Can 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

View 1 Replies View Related

Conditional Input Params To SP

Feb 2, 2004

I'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?

View 3 Replies View Related

EXECuting Sp's Within Sp's (using Return Params)

Feb 1, 2005

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.

View 1 Replies View Related

Dynamic SQL && Output Params

Feb 7, 2005

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.

View 2 Replies View Related

Execute An SP By Passing Params From Another SP

May 7, 2008

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

View 2 Replies View Related

Displaying Params In Report

Feb 6, 2007

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 !!

View 1 Replies View Related

Array Params To Stored Procedures?

Jul 13, 2004

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

View 3 Replies View Related

Passing Params To SP Using OPEN QUERY

Oct 8, 2014

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]....

View 3 Replies View Related

Please Help! Cannot See Values In Output Params From SQL Server 2000 Using C# / ASP.NET

May 26, 2004

Hi,

I've got the following C# function to add a customer record to the database. The record gets added without any problems but the OUTPUT PARAMETER (Parameter[10]) is always NULL and I can't see why. I'm also using the Microsoft Data Application Block.

Here's the C# function:

public void SaveCustomer(int customerId,string customerName,string address1,
string address2,
string town,
string county,
string postcode,
string webSiteAddress,
string mainTelNo,
string mainFaxNo)

{
try
{
SqlParameter[] parameters = DA.SqlHelperParameterCache.GetSpParameterSet(this.ConnectionString,"UpdateCustomer");
parameters[0].Value = customerId;
parameters[1].Value = customerName;
parameters[2].Value = address1;
parameters[3].Value = address2;
parameters[4].Value = town;
parameters[5].Value = county;
parameters[6].Value = postcode;
parameters[7].Value = webSiteAddress;
parameters[8].Value = mainTelNo;
parameters[9].Value = mainFaxNo;
parameters[10].Direction = ParameterDirection.Output;

int RetVal = DA.SqlHelper.ExecuteNonQuery(this.Connection,"UpdateCustomer",parameters);

if (RetVal > 0)
{
int Key = Convert.ToInt32(parameters[10].Value.ToString());
}

}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString(), ex);
}

}


And here's the SQL Server 2000 Stored Procedure:


CREATE PROCEDURE UpdateCustomer

@CustomerId int,
@CustomerName varchar(100),
@Address1 varchar(50),
@Address2 varchar(50),
@Town varchar(30),
@County varchar(30),
@PostCode varchar(10),
@WebSite varchar(50),
@MainTelNo varchar(15),
@MainFaxNo varchar(15),
@ReturnValue int OUTPUT

AS

IF @CustomerId = 0
BEGIN
INSERT INTO Customer
(CustomerName, Address1, Address2, Town, County, Postcode, WebSite, MainTelNo, MainFaxNo)
VALUES(@CustomerName, @Address1, @Address2, @Town, @County, @Postcode, @WebSite, @MainTelNo, @MainFaxNo)

IF @@ERROR = 0
BEGIN
SELECT @ReturnValue = @@IDENTITY
END
ELSE
BEGIN
SELECT @ReturnValue = -1
END

END
ELSE
BEGIN
UPDATE Customer
SETCustomerName = @CustomerName,
Address1 = @Address1,
Address2 = @Address2,
Town = @Town,
County = @County,
Postcode = @Postcode,
WebSite = @WebSite,
MainTelNo = @MainTelNo,
MainFaxNo = @MainFaxNo
WHERECustomerId = @CustomerId

IF @@ERROR = 0
BEGIN
SELECT @ReturnValue = @CustomerId
END
ELSE
BEGIN
SELECT @ReturnValue = -1
END
END



Any help would be appreciated as it's starting to drive me mad now!!

View 7 Replies View Related

Getting Data (rows) And Output Params From A Query

May 15, 2005

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.

View 1 Replies View Related

SP: Number Of Params Does Not Match Table Definition

Jun 20, 2000

I am getting an insert error with the following SP. I don't have to pass the CampID because it is an IDENTITY field. The error says "number of supplied values does not match table definition."

Do I pass in the CampID to the SP and allow nulls? Thanks in advance

Nathan


CREATE PROCEDURE sp_CampReg1
@UserNamevarchar(15),
@Passwordvarchar(15),
@CampNamevarchar(50),
@Hostvarchar(50),
@Directorvarchar(25),
@Contactvarchar(25),
@Addressvarchar(30),
@Cityvarchar(25),
@Statevarchar(20),
@Zipvarchar(15),
@Countryvarchar(20) = NULL,
@Phonevarchar(20) = NULL,
@AlternatePhonevarchar(20) = NULL,
@Faxvarchar(20) = NULL,
@ContactEmailvarchar(20),
@AdminEmailvarchar(20),
@URLvarchar(50) = NULL,
@CampTypeint,
@CampProfileText =NULL,
@CampIDintOUTPUT

AS

INSERT INTO TempCampSignup
VALUES
(
@UserName,
@Password,
@CampName,
@Host,
@Director,
@Contact,
@Address,
@City,
@State,
@Zip,
@Country,
@Phone,
@AlternatePhone,
@Fax,
@ContactEmail,
@AdminEmail,
@URL,
@CampType,
@CampProfile
)

SELECT @CampID = @@IDENTITY

View 1 Replies View Related

User Functions As Params To Stored Procs

Mar 19, 2003

Can I use the result of a scalar function as the parameter for a stored procedure? ie

exec [dbo].[usp_insert_into_table]
@integer = [dbo].[uf_getAnIDfromName]('PLAYER')

where the @integer parameter expects an integer and the user function uf_getAnIDFromName returns an integer related to the 'PLAYER' name.

View 2 Replies View Related

Problem With OUTPUT Params In Stored Procedure

Jul 23, 2005

Hi all!Running the code below in SQL-analyzeer (or through dbExpress) results in NULL.As one might guess I would like the result to be 1. What is wrong? I.e, whywont the result of the SP come back to the caller?CREATE PROCEDURE test@val INTEGER OUTASSELECT @val = 1GODECLARE @val INTEGEREXEC test @valSELECT @val

View 1 Replies View Related

Statement For Returning Stored Procedure Params

Oct 27, 2005

Hi all,I'd like to put together a SQL statement that will take the name of astored procedure as a param, and return that SP's parameters.I'm writing a test application, and I'd like to wrte a generator tosave myself some time, but I can't seem to figure out how to get theparams from a SP. Any help would be appreciated.Thanks in advance,Craig

View 2 Replies View Related

Using Variables As Input Params In WebService Task

Jul 24, 2007

Hi,



I have a SSIS package with a Sequence which Contains a Webservice Task, in the input section of this task i want to pass a User Variable as Parameter for my webmethod. but it doesn´t work, it allways sends the variable definition as string "@[User::Filename]". so i searched Microsoft Technet how to pass User Variables in Webservice Tasks and found this site: http://technet.microsoft.com/en-us/library/ms187617.aspx



which says :



"
Variable


Select the check boxes to use variables to provide inputs. "

but there is no such checkbox on the input page of my Webservice Task... there is just the Value column which i can edit... but as mentioned before when i try to set the value to a variable it doesn work



i tried the following strings in the value column:



@[User::Filename]

@Filename

User::Filename

@User::Filename



any ideas?



thanks for your help

bye

as

View 5 Replies View Related

Datetime Params Quit Working When I Switched From Ws To Wcf

Mar 15, 2007

hi
HHello!, the following 3 webmethods still work after i switched them to WCF but now it never accepts my parameters. i didn't change anything in reporting services, but i have a feeling it's not sending the params to the service anymore.

is there an operation contract attribute change i need to make to require the datetime be provided by RS?
here's the schema provided by my serviec wsdl (the relevant portion)


- <xs:element name="EeoByAssignmentReport">


- <xs:complexType>


- <xs:sequence>


<xs:element minOccurs="0" name="start" type="xs:dateTime" />

<xs:element minOccurs="0" name="end" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>

- <xs:element name="EeoByAssignmentReportResponse">


- <xs:complexType>


- <xs:sequence>


- <xs:element minOccurs="0" name="EeoByAssignmentReportResult" nillable="true">


- <xs:complexType>


- <xs:annotation>


- <xs:appinfo>


<ActualType Name="DataSet" Namespace="http://schemas.datacontract.org/2004/07/System.Data" xmlns="http://schemas.microsoft.com/2003/10/Serialization/" />
</xs:appinfo>
</xs:annotation>

- <xs:sequence>


<xs:element ref="xs:schema" />

<xs:any />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>

- <xs:element name="EeoByPersonReport">


- <xs:complexType>


- <xs:sequence>


<xs:element minOccurs="0" name="start" type="xs:dateTime" />

<xs:element minOccurs="0" name="end" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>

- <xs:element name="EeoByPersonReportResponse">


- <xs:complexType>


- <xs:sequence>


- <xs:element minOccurs="0" name="EeoByPersonReportResult" nillable="true">


- <xs:complexType>


- <xs:annotation>


- <xs:appinfo>


<ActualType Name="DataSet" Namespace="http://schemas.datacontract.org/2004/07/System.Data" xmlns="http://schemas.microsoft.com/2003/10/Serialization/" />
</xs:appinfo>
</xs:annotation>

- <xs:sequence>


<xs:element ref="xs:schema" />

<xs:any />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>

- <xs:element name="EeoCountsReport">


- <xs:complexType>


- <xs:sequence>


<xs:element minOccurs="0" name="start" type="xs:dateTime" />

<xs:element minOccurs="0" name="end" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>

- <xs:element name="EeoCountsReportResponse">


- <xs:complexType>


- <xs:sequence>


- <xs:element minOccurs="0" name="EeoCountsReportResult" nillable="true">


- <xs:complexType>


- <xs:annotation>


- <xs:appinfo>


<ActualType Name="DataSet" Namespace="http://schemas.datacontract.org/2004/07/System.Data" xmlns="http://schemas.microsoft.com/2003/10/Serialization/" />
</xs:appinfo>
</xs:annotation>

- <xs:sequence>


<xs:element ref="xs:schema" />

<xs:any />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

View 1 Replies View Related

Default Params In E-Mail Delivered Reports

Oct 26, 2006

Hi all,

[Reporting Services 2005]

I have reports delivered by email subscription. My "date from - to" parameters are set to "default" as "=Today()" and "=Now()". Report is delivered fine but I have problems with "link" to that report (attached to email). RS don't add my default parameter values to this link so every time user click this link, report is generated with new "date from" and "date to" and I want to generate exact the same report (same parameters) as in the email.

Is there a way to send default parameter values as static values in email delivered report link ?

Thanks

Maciej

View 4 Replies View Related

Calling Stored Proc With Default Params From .NET Not Working.

Mar 20, 2006

All,
I have the following :
ALTER PROCEDURE [dbo].[sp_FindNameJon]

@NameName varchar(50)='',
@NameAddress varchar(50)='',
@NameCity varchar(50)='',
@NameState varchar(2)='',
@NameZip varchar(15)='',
@NamePhone varchar(25)='',
@NameTypeId int=0,
@BureauId int,
@Page int=1,
@Count int=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlString nvarchar(3000),@SelectClause nvarchar(1000), @FromClause nvarchar(1000),@WhereClause nvarchar(1000)
DECLARE @ParentSqlString nvarchar(4000)
DECLARE @Start int, @End int
INSERT into aaJonTemp values (@Page, 'here2', @NameCity);
 
And inside of aaJonTemp, I have the following :




NULL
here2
NULL

NULL
here2
NULL
 
How is this possible?  If @Page or @NameCity is NULL, how come it doesn't default to a value in the stored proc?
 
Thx
jonpfl

View 1 Replies View Related

SqlCeResultSet Looses Updatable Option If Query Has 2 Params

Feb 26, 2008

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.

View 1 Replies View Related

Problem While Setting Global Params Using DTEXec Utiltiy

Jul 31, 2007






Hi pals,

I am new to SSIS.I am getting a problem while setting the global parameters.
I created a package with a single task i.e an Execute SQL task.The associated sql is
"delete from src_emp where deptno=?" and i added a new user defined variable as "dno" i.e "user:no"
which of String type with scope=<package name>.The name of my package is "test.dtsx" which is a Structured File
save in my d: directory. I harded code the value in SSIS package desiner i.e in BIDS and tested the functionality,
it is working fine. But when i remove the hard coded value and when i tried to call the dts package using the command line
utility DTExec, i am getting. Below are the different ways i tried to set the global parameters.



trial 1:
---------

D:>dtexec /f test.dtsx /SET "Package.Variables[User:no].Properties[value]";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 11:06:51 AM
Warning: 2007-07-31 11:06:53.00
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "Package.Variables[User:no].Properties[value]".
This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set Package.Variables[User:no].Properties[value] value to 20.
Started: 11:06:51 AM
Finished: 11:06:53 AM
Elapsed: 1.094 seconds

trial 2:
---------

D:>dtexec /f test.dtsx /SET "Package.Variables[User:no].value";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 11:08:04 AM
Warning: 2007-07-31 11:08:05.99
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "Package.Variables[User:no].value". This occurs
when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set Package.Variables[User:no].value value to 20.
Started: 11:08:04 AM
Finished: 11:08:05 AM
Elapsed: 1.078 seconds

trial 3:
---------
D:>dtexec /f test.dtsx /SET "PackageExecute SQLTask.Variables[User:no].value";"20"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 11:09:56 AM
Warning: 2007-07-31 11:09:57.58
Code: 0x80012017
Source: Aug
Description: The package path referenced an object that cannot be found: "PackageExecute SQLTask.Variables[User:no].val
ue". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set PackageExecute SQLTask.Variables[User:no].value value to 20.
Started: 11:09:56 AM
Finished: 11:09:57 AM
Elapsed: 1.062 seconds


Any suggestions would be appreciated.
Thanks and Regards,
Manu

View 1 Replies View Related

Dataset Using Stored Proc With Multi Select Params

Aug 7, 2007

I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.

In the data tab I'm currently using "text" for command type and :





Code Snippet

declare @sql nvarchar(2000)

set @sql = '
EXEC [Monitor] '' + @p_OfferStatus + '''

exec sp_executesql @sql, N'@p_OfferStatus VARCHAR(100)', @p_OfferStatus = @p_OfferStatus
when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?

View 4 Replies View Related

Reporting Services :: Getting Execution And Session Timeout Params

May 12, 2015

We run std 2008 r2.  I've never been able to get to my ssrs server thru ssms as u can see as part of the question at 

[URL] ..... but I've found workarounds to this point by knowing how to look at the rs db itself and by having access to the server itself thru rdp with an admin acct.

I'm looking at an error from a user that reminds me of what we used to call session timeouts.  They would occur when the user has walked away from his desk long enough for ssrs to delete refs to the report he just ran.

I logged into the server itself hoping to find both exec and session timeout params via the rs config tool but did not see them there. What the config file is called and where i'd likely find it?  Or perhaps a query I could use to see what the current values are?

View 8 Replies View Related

Accessing Data From Basic SQL Select * From TableName WHERE Search Params

Nov 5, 2007

I am learing VWD as I build a site and while I can use the various controls (GridView, FormView) to set up operations and retrieve/display database information, I need to be able to codebehind to do SQL operations for retrieving data to fill paper forms and to modify the database. After I retrieve data from the database, I need to do a lot of computation befor I build a paper quote that is emailed to a customer. I have written a subroutine to which I pass my sql query statement and I can add a new entry and edit an existing entry. The problem is a simple query to read an existing database entry.
If I write a simple command : Cmd = SELECT PartNumber, PartName, PartsAvailable FROM Inventory WHERE PartNumber = "P7226" , the command is executed but the return data falls on the floor somewhere as I dont know how to code to put the results in an array or in fact, have any idea on how to find the returned data. Can anyone help with this simple(?) problem?  My sub that handles the sql command is shown below.
I have also been looking for the way to code in VB to cause existing controls like GridViews to access, edit, and insert but I havent been able to find a paper or tutorial. If anyone has seen this information, it would be helpful also.Protected Sub CallSQL(ByVal Cmd As String)
Dim conn As New SqlConnection("Data Source=.SQLExpress;AttachDbFileName=C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True") 'Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True)Dim sql As String = String.Empty
sql = Cmd
'Open the connection to the database and execute he SQL
conn.Open()Dim command As New SqlCommand(sql, conn)
command.ExecuteNonQuery()
conn.Close()
End Sub
 
 

View 6 Replies View Related

Cannot Pass Params (data Type System.guid) To Subreport?

Feb 19, 2007

Hello experts,

I am trying to create a report including a subreport in VS.2003 Reporting services. The subreport itself runs properly, if I give it a parameter, but when I try to run the main (parent) report, I get the obvious message Subreport could not be shown.

The output windows returns a message:

The value expression used in textbox €˜AccountID€™ returned a data type that is not valid.

The AccountID field is a system.guid. I tried to "cast" it as a text, but I got another error message that the system.guid cannot be explicitly converted.

Does anyone know how to get around this?

Thank you very much.

Ravie.

View 1 Replies View Related

Testing A Stored Procedure With Output Params In SQL Server Managment Studio

Sep 25, 2007

I have an SP like this (edited for brevity):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TESTING_SP]
@Username MediumText,
@Password MediumText,
@UserKey int OUTPUT,
@RoleKey int OUTPUT,
@UserGroupKey int OUTPUT,

AS

BEGIN

SELECT
@UserKey = UserKey
FROM UserProfile
WHERE Username = @UserName
AND [Password] = @Password
END

I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error:

Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0
Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied.

How do I set up the output parameters and then select the values in MS for testing purposes?

Thanks a ton for helping a noob.

View 2 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved