Does The .. Expression In Stored Procedure Work In SSRS 2000 ?
Mar 13, 2007
Hi,
I am not understanding this part of the problem. I am currently reusing a stored procedure that has a ".." as part of the select statement.
I can't put the select statement up here due to privacy but I have found the error where the error states the following:-
Invalid Column Prefix: AM, invalid table name.
I noticed that part of the select statement was the following:-
AM..Field1
I tried executing this stored procedure in the query analyzer and it works fine, but when I tried executing it in SSRS, it gives me the error. After searching through the internet for possible causes, I found out that it was the ".." was giving the error. Anyone knows why ? I found out that it was supposed to bypass any users and permissions to the table.
Thakns !
Bernard
View 1 Replies
ADVERTISEMENT
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Jun 6, 2007
I want to use Navigation to click-through to another report.
However, the source report is an MDX summary report, and the report I'm calling needs the entire Customer MDX tuple as a parameter. (the entire customer tuple is not available in my cube)
The text box has enough information to identify the customer, but not the entire tuple.
I want to pass the text box contents to a SP which returns the MDX string, and pass that to the new report.
I do not see how using another data source can help me accomplish this.
Basically, I am wondering is there a way I can have an expression similar to
=EXECUTE(spX, parm=txtbox.Value)
Thanks In Advance for any help...
View 1 Replies
View Related
Nov 19, 2007
Hi,
I have a stored procedure that return 0 or 1 row and 10 columns. In my subsequent queries I only need 1 column from those 10 columns. Is there any better way other than creating or declaring temp table and than making a select from that table.
So I am looking int CTE to execute stored procedure and than make a selection from CTE, but CTE does not allow me to execute stored procedure. Is there any other better way of acheiving this.
This is what I want to change:
Declare @Col Varchar(10)
Decalre @TempTable(Col1 Int, Col2 Varchar(10), Col3 Varchar(10), Col4 Varchar(10))
Insert into @TempTable
Exec Procedure @Paramter
select @Col = col2 from @TempTable
INTO
With CTE(Col2, Col3, Col4) AS
(
Exec Procedure @Paramter)
select @Col = col2 from @TempTable
Thanks
Punu
View 6 Replies
View Related
Nov 15, 2006
Hi All
Please review this vb6 code (the stored procedure was added by aspnet_regsql.exe)
Thanks
Guy
With CMD .CommandText = "aspnet_Users_CreateUser" Call .Parameters.Refresh .Parameters(1).Value = "812cb465-c84b-4ac8-12a9-72c676dd1d65" .Parameters(2).Value = "myuser" .Parameters(3).Value = 0 .Parameters(4).Value = Now() Call RS.Open(CMD) End With
The error I get is :Invalid character value for cast specification.
This is the stored procedure code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[aspnet_Users_CreateUser]
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
IF( @UserId IS NULL )
SELECT @UserId = NEWID()
ELSE
BEGIN
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
RETURN 0
END
View 1 Replies
View Related
Dec 10, 2007
I am trying to pass a value from one stored procedure into another. I have got the following stored procedures:
Stored Procedure 1:ALTER PROCEDURE test2
(@Business_Name nvarchar(50)
)
AS
BEGINDECLARE @Client_ID INT
SET NOCOUNT ON;
INSERT INTO client_details(Business_Name) VALUES(@Business_Name) SELECT @Client_ID = scope_identity()
IF @@NESTLEVEL = 1
SET NOCOUNT OFF
RETURN @Client_ID
END
Stored Procedure 2 - Taking Client_ID from the proecedure test2ALTER PROCEDURE dbo.test3
(
@AddressLine1 varchar(MAX),
@Client_ID INT OUTPUT
)
ASDECLARE @NewClient_ID INT
EXEC test2 @Client_ID = @NewClient_ID OUTPUT
INSERT INTO client_premises_address(Client_ID, AddressLine1) VALUES(@Client_ID, @AddressLine1)
SELECT @NewClient_ID
When I run this proecedure I get the following error:
FailedProcedure or function 'test2' expects parameter '@Business_Name', which was not supplied. Cannot insert the value NULL into column 'Client_ID', table 'C:INETPUBWWWROOTSWWEBSITEAPP_DATASOUTHWESTSOLUTIONS.MDF.dbo.client_premises_address'; column does not allow nulls. INSERT fails. The statement has been terminated.
However If I run Stored Procedure Test2 on its own it runs fine
Can anyone help with this issue? Is there something that I have done wrong in my stored procedures?
Also does anyone know If I can use the second stored procedure in a second webpage, but get the value that was created by running the stored proecdure in the previous webpage?
View 9 Replies
View Related
Apr 19, 2004
here is code i have
private void btnGetCustomers_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection ();
conn.ConnectionString = " Data Source=(local); Initial Catalog=Northwind; Integrated Security=SSPI ";
SqlCommand cmd = conn.CreateCommand ();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "procCustomers";
// add the input parameters and set their values
cmd.Parameters.Add(new SqlParameter("@ContactName", SqlDbType.VarChar, 50));
cmd.Parameters["@ContactName"].Value = txbxContactName.Text;
conn.Open ();
SqlDataReader dr = cmd.ExecuteReader ();
if (dr.HasRows == true)
{
dgrdMyDataGrid.DataSource = dr;
dgrdMyDataGrid.DataBind ();
}
else
Response.Write("Nothing found.");
// clean up
dr.Close ();
conn.Close ();
}
and here is the stored procedure:
CREATE PROCEDURE procCustomers
@ContactName nvarchar(50)
AS
SELECT * FROM customers
WHERE customers.contactname LIKE @ContactName
ORDER BY customers.contactname ASC
GO
no compile errors, it just returns nothing even if there is supposed to be a match... what am doing wrong?
View 1 Replies
View Related
Jul 30, 2005
Hi, i encounter a problem in the .NET. I have
write the stored prodeure for the registering function and it works
perfectly in the SQL Server. But when i called the stored procedure
from the .NET, it cannot work. Can someone pls help? Thanks!
Stored Procedure Codes:
CREATE PROCEDURE spAddProfile(@MemNRIC CHAR(9), @MemName
VARCHAR(30), @MemPwd VARCHAR(15), @MemDOB DATETIME, @MemEmail
VARCHAR(80), @MemContact VARCHAR(8))
AS
IF EXISTS(SELECT MemNRIC FROM DasMember WHERE MemEmail = @MemEmail)
--RETURN -200
IF EXISTS(SELECT MemEmail FROM DasMember WHERE MemNRIC = @MemNRIC)
RETURN -201
IF EXISTS(SELECT DATEDIFF(YEAR, @MemDOB, GETDATE())
FROM DasMember
WHERE DATEDIFF(YEAR, @MemDOB, GETDATE()) < 18)
RETURN -202
INSERT INTO DasMember(MemNRIC, MemName, MemPwd, MemDOB, MemEmail,
MemContact, MemDateJoin) VALUES (@MemNRIC, @MemName, @MemPwd, @MemDOB,
@MemEmail, @MemContact, GETDATE())
IF @@ERROR <> 0
RETURN @@ERROR
SET @MemNRIC = @@IDENTITY
RETURN
DECLARE @status int
EXEC @status = spAddProfile 'S1234567J', 'Kim', 'kim', '1986-01-01', 'kim@hotmail.com', '611616161'
SELECT 'Status' = @status
When called from .NET, it cannot works.. These are the codes:
Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim connStr As String =
System.Configuration.ConfigurationSettings.AppSettings("SqlConnection.connectionString")
Dim dbConn As New SqlConnection
dbConn.ConnectionString = connStr
Try
dbConn.Open()
Dim dbCmd As New SqlCommand
dbCmd.Connection = dbConn
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.CommandText = "spAddProfile"
Dim dbParam As SqlParameter
dbParam = dbCmd.Parameters.Add("@return", SqlDbType.Int)
dbParam.Direction = ParameterDirection.ReturnValue
dbParam = dbCmd.Parameters.Add("@MemNRIC", SqlDbType.Char, 9)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtNRIC.Text
dbParam = dbCmd.Parameters.Add("@MemName", SqlDbType.VarChar, 30)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtName.Text
dbParam = dbCmd.Parameters.Add("@MemPwd", SqlDbType.VarChar, 15)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtPwd.Text
dbParam = dbCmd.Parameters.Add("@MemDOB", SqlDbType.DateTime)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtDOB.Text
dbParam = dbCmd.Parameters.Add("@MemEmail", SqlDbType.VarChar, 80)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtEmail.Text
dbParam = dbCmd.Parameters.Add("@MemContact", SqlDbType.VarChar, 8)
dbParam.Direction = ParameterDirection.Input
dbCmd.ExecuteNonQuery()
Dim status As Integer
status = dbCmd.Parameters("@return").Value
If status = -201 Then
lblError.Text = "NRIC already exists!"
ElseIf status = -202 Then
lblError.Text = "You must be at least 18 years to sign up!"
Else
lblError.Text = "Success"
End If
Catch ex As SqlException
lblError.Text = ex.Message
Catch ex As Exception
lblError.Text = ex.message
End Try
End Sub
End Class
Can someone pls help? Thanks a lot!! I appreciated it very much!!
View 7 Replies
View Related
Oct 26, 2000
Hi
When I tried to execute a procedure it give me message:
"Too many arguments were supplied for procedure sp2_ge_ag15_01"
I am doing:
exec sp2_ge_ag11_01 306,'NOVO VELHO',Null,
'1968-04-15','M',90,.17,'novo@com.br','teste'
It has 9 parameters
the begin of the my procedure is:
CREATE procedure dbo.sp2_ge_ag11_01(@Pcd_ficha integer,
@Pnm_ficha varchar(60),
@Pcd_pac integer = null,
@Pdt_nas datetime,
@Psx_pac varchar(01),
@Ppes_pac smallint,
@Palt_pac float,
@Pemail1 varchar(50),
@Pobs varchar(254))
AS
etc...
What Happen
thank you in advance
View 3 Replies
View Related
Sep 28, 2000
Do anybody aware of any problems with the way that sql 7.0 converting stored procedures from sql 6.5...thanks
View 1 Replies
View Related
Sep 8, 1999
I have a stored procedure that works on my development server but when I placed it on the server that is to be the prodcuction server i get the following response:
output ----------------
The name specified is not recognized as an internal or external command, operable program or batch file.
This is the procedure:
CREATE PROCEDURE sp_OutputClaim @OutFile varchar(255), @CurAns char(8) AS
declare @CmdLine varchar(255)
select FieldX into ##TempTable from Table where FieldY = @CurAns
select @CmdLine = "exec master..xp_cmdshell 'bcp tempdb.dbo.##TempTable out
c:est" + @OutFile + " /Uxx /Pxxx /Sxx /f c:estcp.fmt'"
exec (@CmdLine)
drop table tempdb..##TempTable
Thanks for any help !!!!
Rob
View 1 Replies
View Related
May 6, 2006
Hello
I have restored a SQL 2000 backup of my database, ever since my web applications are unable to use any of the stored procedures.
I get the following error:
Could not find stored procedure 'xxx'.
If I use enterprise manager and go to the procedures tab the procedure appears there.
Any ideas what is up?
PS: The username has been changed, before the backup it was "user1" now its something else. For some reason it still shows that the procedure object is owned by "user1". Could this be the problem?
Thanks in advance.
View 1 Replies
View Related
Jul 22, 2006
Hello, I am trying to make this.
CREATE PROCEDURE [dbo].[P_SEL_ALLPERSONAS]
@nmpersona int,
@sortorder varchar(20)
AS
BEGIN
select nmpersona, dsprimernombre, dssegundonombre,
dsprimerapellido, dssegundoapellido
from personas
order by @sortorder
END
But I got this error. Please help
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
View 5 Replies
View Related
Mar 9, 2007
Hi,
I was trying to create a simple SP that return a single value as follows:CREATE PROCEDURE IsListingSaved@MemberID INT,@ListingID INTASIF EXISTS (SELECT [Member_ID] FROM [Member_Listing_Link] WHERE [Member_ID] = @MemberID AND [Listing_ID] = @ListingID) Return 1ELSE Return 0GOWhen I try it out in the Tableadapter's preview table, I get the correct result (1, where the entries exist). However, in the BLL, I tried to get the value as:Dim intResult as IntegerintResult = CType(Adapter.IsListingSaved(intMemberID, intListingID), Integer). However, this always returns 0 (when it should be returning 1). P.S. Curiously, breakpoints skipped the VS generated code for the adapter. What could be the problem? Thanks,Wild Thing
View 3 Replies
View Related
Mar 13, 2007
All:
I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update.
When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#.
When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening?
Here is the basic shell of my SP:
CREATE PROCEDURE [dbo].[spHeader_InsertUpdate]
@FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime
AS
Declare @rtncode int
IF NOT EXISTS(select * from HeaderTable where FormID=@FID)
Begin begin transaction
--Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE
Begin begin transaction
--Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID;
--Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end
End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks,
Blue.
View 5 Replies
View Related
Jun 9, 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:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.
SQLStatement: dbo.sp_ml_location_load @system_cd='03', @location_type_cd=Store;
However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.
I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
Here is my set up, what is wrong here:
I Created these Variables:
Name Scope Data Type Value
system_cd Locations String '03'
location_type_cd Locations String Store
I added these parameter mappings in the Execute SQL Task
Variable Name Direction Data TypeParameter NameParameter Size
User::system_cd Input NVARCHAR@system_cd -1
User::location_type_cd Input NVARCHAR@location_type_cd -1
I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,
It runs the package successfully but it does not put the data into the table.
View 2 Replies
View Related
Feb 20, 2008
I have a stored procedure with the following:
CREATE TABLE #t1 (... ...);
WITH temp AS (....)
INSERT INTO #t1
SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...
This stored procedure works fine in Management Studio.
I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.
What does the error message mean and what have I done wrong?
Thanks.
View 5 Replies
View Related
Jun 4, 2008
Hi,
I have a report which contains several queries - one of which is a stored procedure which creates a Temp table in the database. This temp table is then used by the other queries. The temp table provides the lowest granularity of data, and the subsequent queries aggregate it at different levels in order to produce the report charts and matrix's.
The issue is that when I run the report, the stored procedure does not create the temp table before the other queries start using it. In fact, I need to run the report, and then refresh it in order to get the report to pull in the correct data (1st run populates the temp table, refresh then allows the queries to use it.).
Is there a way to force the execution of the stored procedure before the other queries run?...I don't want to create stored procedures for each query, because the intitial creation of the Temp table is quite slow (5seconds), and to do this for each data subset would be very resource intensive.
Thanks
Kevin.
View 10 Replies
View Related
Aug 21, 2015
I have a stored procedure (sproc) running in Firebird Database. Now I try to use this sproc to build a report in the SSRS 2012 (SSDT) Report Designer.
After I connect to the dataset, and when I choose the Query Type as 'Stored Procedure', and type the name of my sproc, and then go to the Query Designer, and click ! (F5) , I get the following error:
TITLE: Microsoft SQL Server Report Designer
------------------------------
An error occurred while executing the query.
ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 1 [sproc name]
ADDITIONAL INFORMATION:
ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 1 [sproc name] (OdbcFb)
------------------------------
BUTTONS:
OK
------------------------------
how to resolve this? If its a T-SQL sproc, then it will be no issue; in this case, Firebird database connects to the SSRS via a 32-bit System ODBC connection.
When I proceed and click ok for everything, I get this error (after I exit the Query Designer), in the Dataset Properties tab:
"Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."
The ODBC connection between SSRS and Firebird works fine. In fact I am able to run many queries as ad-hoc ones, but when I use the queries as sprocs, then this issue pops up.
how to handle Firebird stored procedures in SSRS.
View 2 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Dec 10, 2007
HI,
I tried to run the following query.
Code Block
WITH YesterdayCTE AS
(
SELECT type = 'Members Joined Yesterday'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
(
SELECT type = 'Members Joined Last Month'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
YearlyCTE AS
(
SELECT type = 'Members Joined Last year'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
combinedCTE AS
(
SELECT * FROM YesterdayCTE
UNION ALL
SELECT * FROM MonthlyCTE
UNION ALL
SELECT * FROM YearlyCTE
)
SELECT *
, Members = Borrowers + Depositors
FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer)
===================================
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
Please can anyone help me to make this work?
Thanks
View 7 Replies
View Related
Sep 20, 2007
I have a VS2005 aspx page using the ReportViewer to view a SSRS 2005 Report, all running locally on a Vista Business laptop.
I have an error because the stored procedure that it executes does not have the rights to Drop or Create Tables.
I can Preview it fine in VS2005 and I can go to ReportManager and run it OK.
This all worked fine on my XP laptop, so I am guessing that it is the Account used by IIS(7) to get to the ReportServer.
Does anyone have any clues on this?
Does it use NT AUTHORITYIUSR or NT AUTHORITYNETWORK SERVICE, etc.
Thanks in advance
GC
Here's the message and the Tables do exist
An error has occurred during report processing.
Query execution failed for data set 'BranchHeadCountExistStartFinishTotalDS'.
Cannot drop the table 'ExistingStaff', because it does not exist or you do not have permission. Cannot drop the table 'Starters', because it does not exist or you do not have permission. Cannot drop the table 'Finishers', because it does not exist or you do not have permission. Cannot drop the table 'TotalFinish', because it does not exist or you do not have permission. Cannot drop the table 'StartFinish', because it does not exist or you do not have permission. The specified schema name "dbo" either does not exist or you do not have permission to use it.
View 1 Replies
View Related
Sep 9, 2015
I have created a query in which I have used first_value, lead, lag windows functions. I tried using the query in SSRS query text, but for some reason it could not load. Then I created a stored procedure and implemented that query in stored proc.
The problem is that I am able to execute the stoured proc from sql server studio, but SSRS is not able to load data.
View 5 Replies
View Related
Feb 13, 2008
Sql date base help 1 do not now where to start I am trying simple to run the sql database I have in expression web which I built within visual web developer 2005. I thought. I would upload it in with expression web in the website using ftp and it would run on the server. No can anybody help the web site uses all ASP.NET pages , I have everything tested and working right on my computer, I have three simple data bases I up date through master details forms pages I run on my computer and not on the server.The data is of names addresses, ex and I display them with a grid view control on the web site pages. I used visual web developer 2005 to do this.And I do not use ASP.NET 2.0 Membership, Role and Profile features. I tested it on a server got a server error connecting to sql server 2005. I think I need sql server 2005 installed on the server and is the sql server 2005 just a program for and to run sql data basesAt this point I’m lost.I do not know how it works Do not know anything about sql data bases other than the above.I need a simple explanation no how to get the sql data base to work on the web site and a quick explanation on how all this works. In the Recipe: Deploying a SQL Database to a Remote Hosting Environment (Part 1) It said unfortunately there hasn't historically been a super-easy way to accomplish this. Copying the .aspx files and compiled assemblies to the remote system is pretty easy (just FTP or copy them up). I coped the data base files by ftp but what are compiled assemblies? The release candidate of a new SQL Server Hosting Toolkit is this, what I need and how do I use it.The toolkit allows you to work with SQL Express, SQL Server 2000, and SQL Server 2005 databases locally and then easily transfer your schema and data and install themWhat is a schema? And how do I use it to setup and recreate the database contents - both schema and data - on the remote hosted site. Bill
Deploying a SQL data base to a server have looked at the sql server videos from Microsoft and now can create a fantastic data base but cannot get a simple signal table data base published and running on a sql server 2005. Why is it so difficult to get help or a video or publication from Microsoft on the most important things? What good is the most fantastic data base if you cannot get it up and running it was the same for a simple piece of script to get an asp web form to post the results of a form as an e-mail took me 3 months to scrap together the script to make it work? How this with the data base. As I have explained do not know the process never down it, first time too build and try to put a data base on a website.
If you publish to sql express all you need to do is ftp it up to the server and it will work I think.
But it’s not so simple with SQL server 2005 it’s much more complicated. So I have installed the new publishing wizard in Microsoft for visual web developer 2005 to help get it running on the server. This was the simplest way I think. Have three data bases the data bases are so simple as they only have one table each.
Does anybody now of a video or article that explains from the start how to get a simple data base up and running no SQL server 2005 for a beginner that explains in detail the possess.
View 2 Replies
View Related
Mar 9, 2007
Hello all,
I had editted this post to ask a better question.
I had noticed that certain expressions won't work in details. Is this true ?
when I tried using an IIF expression, I always get a blank or an empty field even if it was a text.
could someone clarify this ? I don't know whether it is just me or is it a limitation.
Sincerely,
Bernard Ong
View 5 Replies
View Related
Jul 9, 2015
My SSRS report never finishes running for some reason.
If I run the same SP with the same parameters from SSMS it runs in 0.5 sec (returns 8-10 rows).
When running from report manager, I see extremely high CPU Time and Disk IO values, no blocking, no waiting at all.
View 4 Replies
View Related
Jul 10, 2015
I have a stored procedure which returns a result set as follows:
(Headers)Total,WV1,WV2,WV3,WV4,WV5.....
(Example data) "Some total name",1,2,3,4,5.....
The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").
What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report.
View 9 Replies
View Related
Dec 7, 2007
Any idea why this expression would not work in SSRS?
Based on a expression on a group textbox I get 0 records that match:
=Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0)
I've evaluated in the proc & there should be a count of 29 records.
I found a work around by using this in the stored proc & I get my record count:
ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END
and changed the expression in SSRS to this & it works:
=sum(Fields!ACCRUAL_CODE.Value)
Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?
View 3 Replies
View Related
Jun 2, 2015
I have a requirement to display each student Fitness test results in the form of a Box plot chart with High Whisker , Low whisker , Low box,high box , Mean and Median in a stored procedure since test areas are dynamic. But , i have never done this before.
Test Type
TestSeq
Test Date
Student ID
TestArea
Test Result
[code]...
View 2 Replies
View Related
Dec 13, 2006
Greetings,
I have an expression in a precedence constraint that is returning false when it should return true. This is the expression that returns false:
((5500 / 9990) * 100) > 10
The following expression returns true. I did the division (5500 / 9990) myself and substituted the resulting value:
((0.55055055055055055055055055055055) * 100) > 10
Why is the first expression returning false? I'm stuck in the mud up to my axles on this and I know I'll probably feel like a fool when I learn the answer...
Thanks,
BCB
View 3 Replies
View Related
Jul 6, 2007
Hello,
I have to build dynamic sql statement in an SQL task.
The SQL statement is way over 4000 char.
The expression builder complains the length of the expression.
Any work around to this limitation?
Thanks a lot!
View 4 Replies
View Related
May 9, 2007
Hi There,
I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?
dateadd("dd", -1, datediff("dd", 0, getdate()))
Thanks.
View 8 Replies
View Related
Feb 4, 2008
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 2 Replies
View Related
