SELECT Statement Works In SQL Server Management Studio But Not In SSRS
Mar 14, 2008
Hi,
I'm attempting to extract some yearly average figures from our DB. I've written a SELECT statement in SQL Server MS which returns exactly what I need:
SELECT YEAR, CAllSource, AVG(CallTotal) AS [Average calls per day]
FROM (SELECT COUNT(CallID) AS CallTotal, DATEPART(YEAR, Recvddate) AS Year, CASE WHEN CallSource IN ('Auto Ticket', 'Email')
THEN 'Email' WHEN CallSource IN ('Phone') THEN 'Phone' ELSE 'Other' END AS CallSource
FROM Calllog where
DATEPART(MONTH, Recvddate) = 3
AND DATEPART(dw, RecvdDate) NOT IN ('7', '1')
GROUP BY RecvdDATE, callsource) as sub
GROUP BY YEAR, CallSource
ORDER BY YEAR, CallSource
The problem is that when I attempt to use this in SSRS I get the following error:
"sub.Year is not a recognised DATEPART Option".
Now as you can see, "sub.Year" is not even mentioned in the expression. However I noticed that when running the query, SSRS automatically adds "sub." before the YEAR in the section highlighted in yellow above. a) Why is it doing this, and b) does anyone know of a workaround/fix?
WHERE (student_id LIKE '%') AND (register_id LIKE '%') AND (register_group LIKE '%') AND
week_no = Case @Week when null Then '#' Else @Week End
the idea is if they don't enter a number it would bring up all records. This works in mangaement studio but c# brings up the error sql server doesn't support udt on excecution of the sql. This is sql server 2000. Any ideas why this doesn't work and how to fix it.
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is?
I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime.
I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor.
Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher?
expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid.
Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)".
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue) at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages) at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()
I have an ASP.NET/ADO.NET page i wrote. It was based off of the development database during most development, and it switched over seamlessly to the production database.
Except for yesterday, when i went back to edit the page. It simply stopped returning results on my queries. To see if it was caused by bad values being passed to the sqldatasource, i tried it in the "Configure Data Source..." window, and it returns zero rows. When passing all of the exact same parameters to the same procedure in the same DB engine, even using the same login information, it works.
Just not in my ADO.NET app anymore...
Can anyone tell me what the hell could be going on here, please? I already rebuilt the page from an older version this morning, being very minimal on changes, and the last thing i changed before it stopped working was the connection string... *BUT* I have triple-checked that against the canon string we have always used in every working copy.
I'm losing a lot of hair over this . Any help would be greatly appreciated.
I have an SQL statement that, when run through SQL Server management studio works fine. However, when I run it on my ASP page, it doesn’t update the data! I have tried both as a stored procedure and as a simple commandText update statement. All I do is simly update the value of a column based on another column – nothing particularly complex: update customer set dateChanged = System.DateTime.Now.ToString("dd-MMM-yyyy"), CURRSTAT = 'Active',custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END,cust_Changing_To = NULLFROM customers_v WHERE CURRSTAT = 'Changing'
As you can see, nothing that complex. The line that is causing the problem is the case: custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END all it does is if another nullable integer column is not null, sets it to the value of that column, else it retains its existing value. Like I say, this works in Management studio, but I cannot get it to execute programatticaly from an asp page. No exceptions are being thrown, it just doesn’t update the data. Any ideas? Thanks
I have a stored procedure which is run through MS Access (yuck). It does not appear to fail, but it does not populate certain tables, and is also rather complex. I did not write it, and am trying my best to fix it. However, when I run the same procedure with the same parameters from within Management Studio (database state is the same, I restore from backup before trying out each execution) it populates the tables correctly. I have profiled both executions (from MS and from Access) and it is running with the same NTUsername, and thus the same permissions. The process used to work and then some changes were made to the DB which seem to have broken it from Access. As far as I know, the Access code has not changed, although it may have done so. The proc call from Access uses the same parameters, and does not throw an error, although it does not appear to close the connection to the DB (I'm looking into this). Access uses an ODBC connection to connect to SQL Server.
Hi,I have an interesting scenario. I have a SQL Server 2000 Standard Edition instance running on Computer A. I'm trying to access it through computers B and C.Computer B has Sql Server 2005 Express Edition installed and I was able to use its Management Studio and connect to instance on Computer A.Computer C has SQL Server 2000 Standard Edition installed. When I try to connect to the instance on Computer A, I get connection failed message.I checked some settings. Both TCP/IP and Named pipes are enabled on the instance in A. The TCP/IP port is set to 1433 for both client and server.Please give me some ideas as to how I can solve this problem. -Thanks
I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:
declare @starttime as datetime declare @endtime as datetime declare @timezone as integer declare @date as datetime
set @timezone = 1 set @date = '5/1/2007'
set @starttime = dateadd(hh, @timezone, @date) set @endtime = dateadd(d, 1, @starttime)
select @Starttime, @endtime from site
Error Message:
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).
Hi, I have serious trouble trying to log in to SSRS using Management Studio. I have installed SSRS to existing named instance of SQL 2005 than I have configured it using Reporting Services Configuration tool. At this point some thing was not OK as initialization button is grayed out (although it indicates that initialization went OK). At this point everything seems to be OK the reporting site is available through web browser. Alas when I€™m trying to log in into reporting services using Management Studio I get error €œUnable to connect to remote server. No connection could be made because the target machine actively refused it (System)€? I was trying different authentication methods and different configuration. I was trying to set up permissions to DCOMs as suggested on some topics on this forum. Finally I got to SQL Server Surface Area Configuration and when I go to Surface Area Configuration Features and click on Reporting Services, Windows Integrated Security I€™m getting error €œThe request failed with HTTP status 403: Forbidden (System.Web.Services)€? and than whole Surface Area Configuration Features is closing. At this point I had no further idea what is going on and what more I could try to make it work. Any help greatly appreciated.
I have installed and configured a 2nd instance of SSRS 2005 Standard Edition SP1 on a Windows Server 2003 Standard Edition SP2. The 2nd instance is using it's own database and virtual directories under the default website. All configuration steps ran without error. I have no problem accessing the 2nd instance virtual directories via IE. When I connect to Reporting Services via Management Studio, it gives me no options for which instance to access and connects to the default instance. How do I connect to a 2nd named instance of SSRS via Management Studio? I've found no documentation in BOL related to this.
I have a pretty complex SQL statement that looks like this: SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null". Why does it do this? What would i change to prevent this from happening?
I have SSRS in Visual Studio. I created a query that works fine in SQL Server Management Studio, but when pasted into Visual Studio I get the error "An expression of non-boolean type specified in a context where a condition is expected, near '('.
Here is the query. Can anyone help on why this isn't working? Thanks.
SELECT CASE WHEN MONTH(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = MONTH(GETDATE()) AND YEAR(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = YEAR(GETDATE()) THEN dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount ELSE 0 END AS CurrentMonth, CASE WHEN SubString(dbo.MAS_CCS_GL_Account.Account,1,3) = '400' THEN 'ALEDO' ELSE ' ' END AS Location, dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate, dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber, dbo.MAS_CCS_AR1_CustomerMaster.CustomerName, dbo.MAS_CCS_ARO_InvHistoryDetail.DetailSeqNumber, dbo.MAS_CCS_ARO_InvHistoryDetail.LineType, dbo.MAS_CCS_GL_Account.Account, dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount FROM dbo.MAS_CCS_AR1_CustomerMaster, dbo.MAS_CCS_ARN_InvHistoryHeader, dbo.MAS_CCS_ARO_InvHistoryDetail, dbo.MAS_CCS_GL_Account WHERE dbo.MAS_CCS_AR1_CustomerMaster.CustomerNumber = dbo.MAS_CCS_ARN_InvHistoryHeader.CustomerNumber AND dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber = dbo.MAS_CCS_ARO_InvHistoryDetail.InvoiceNumber AND dbo.MAS_CCS_ARO_InvHistoryDetail.SOGLSalesAcct = dbo.MAS_CCS_GL_Account.AccountKey
I have a problem with a prepared statement against SS2005 SP2 that I do not fully understand. I have a query that is prepared and executed from a java application that looks similar to this:
SELECT TOP 1 str.ID FROM bigtable1 big1 LEFT OUTER JOIN bigtable2 big2 ON (big1.ID = big2.ID) LEFT OUTER JOIN htable h ON (big2.HID = h.HID AND h.DIRECTSW=1) WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT LID FROM LAS WHERE LASID = ? )) AND big2.COMPLID <> 2 AND big2.COMPLID <> 3 AND ISNULL(big2.date1, big2.date2) BETWEEN ? AND ?
When run as a prepared statement this runs in about 27 seconds. When run under the query component of Management Studio it runs in about 1 second. Before each run I am clearing the buffers and the prepared statement cache. When running these I have looked at the execution plan from profiler and they both look the same. If I change the
WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT LID FROM LAS WHERE LASID = ? ))
to INNER JOIN it runs ~1 second in both environments.
Any pointers would be welcome to explain the disrepency.
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running
TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED
TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED
TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1025 *:*
UDP 0.0.0.0:1030 *:*
UDP 0.0.0.0:3456 *:*
UDP 0.0.0.0:4500 *:*
UDP 81.105.102.47:123 *:*
UDP 81.105.102.47:1900 *:*
UDP 81.105.102.47:5353 *:*
UDP 127.0.0.1:123 *:*
UDP 127.0.0.1:1086 *:*
UDP 127.0.0.1:1900 *:*
Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.
The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:
Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)
I would expect this as the DNS has not been advised to encrypt the conection.
This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.
This is on a XP machine trying to connect to the remote webhosting company via the internet.
I can ping the server
I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled
I do not have any aliases set up
No I do not force encryption
I wonder if you have any further suggestions to this problem?
Hello to all, i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me. I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418: declare @IDM int; declare @IDO varchar(8000); set @IDM = 3418; set @IDO = '3430' select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 )) but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM)) I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks I use MS SQL 2005 Server Management Stadio Express Thanks a million and Best regards Sha
In sql 2000 one could right-click a table (under Enterprise Manager) and have it return the "top n" rows in the table. That feature seems to be missing from Management Studio. Am I correct or is it just hiding somewhere?
Hi All, I am new to SQL Server and having trouble using SQL Server Management Studio. I am unable to select the Database Engine in management studio. I am able to see the instance of default database engine (MSSQLServer) running in Reporting Services manager as well as in Surface area configuration manager, but it is not visible in the drop down list in Management Studio's "Select Database Engine" menu. I had removed Sql server 2005 earlier ( I was able to select the database engine in Management Studio then). But when I installed it again, I was unable to install the Sql Server Tools (it said that my Upgrade is blocked). So, I cleaned the Windows Registry of all keys containing 'Sql'. After this I tried installing it again and successfully installed Sql Server 2005 + ALL TOOLS. But this time I am unable to select the database engine in management studio. Thanks and Regards to ALL
I have recently installed the SQL Server Management Studio Express but I do not find Management Tools in order to create scheduled backups and shrinking of the databases. I was under the impression that this should be included in the Management Studio. I use the SQL 2005 Express for smaller customers who run the SQL on a desktop unit. I need a way to backup the data to a server machine for backup purposes. I have uninstalled and reinstalled to no avail.
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
I am new to visual studio and I am still not sure of all its components and features.
I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio?
I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional?
I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool.
How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase.
Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology.
My goodness, it seems I still have so much to learn.
How do i get the database that i am using in visual studio into my SQL server management studio? i need to create some scripts to create stored procedures on a live server.
Hi all,I'm having a big problem here. I can't seem to connect to a database of MSSQL Server 2005... Here is what I've did:1. Under the Server Explorer>Data Connections. Right click and choose "Add Connection..."2. Data Source was "Microsoft SQL Server Database File". Click OK.3. I browse to the database in the MSSQL.2/MSSQL/Data folder.4. Click on the "Advanced" button, and change the default DataSource from .SQLEXPRESS to .MSSQLSERVER. Click OK5. Received this error:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) Only .SQEXPRESS that I was able to connect to... i've tried the solution from Microsoft, opened SQL Server 2005 Surface Area Configuration and enabled "Local and remote connections", checked the option "Using both TCP/IP and named pipes" but it didn't help. This is frustrating because I can't get the Membership provider, login to work on my host. Please help. Thank you very much,Kenny.
I have a report problem. I'm using a parameter to dynamically control visibility for two tables. If the parameter is set to one value, I want to switch one of the tables to invisible, if the parameter is set to another, I want the other table to be invisible instead.
This all works fine in Visual Studio. When I publish it to my report server, the visibility controls no longer function and both tables always display. Any ideas here?
I have an acctest server (XX-ATS-ASSHP102) running SSRS, SQL DB Engine & SSAS. I'm trying to configure NATIVE SQL Reporting Services to use a data source with Windows Integrated mode towards a SSAS cube on the same server. When I use the servername (or 127.0.0.1) it works perfectly. But when I use the DNS name bidb.acctest.internal it fails with the error "The connection either timed out or was lost.". The profiler on SSAS says "anonymous login" so the Kerberos login is broken at some point.The weird thing is that this works perfectly in production environment, but I've missed something in the ACCTEST environment.The SPN for the serviceaccount running SSAS has correctect SPN:s:
The SSRS account has delegation with "any service". No account has the "sensitive" flag.DNS names are A-records, except the hostname for the SSRS site which is a c-name, but it's identical in production.The "impersonate client after login" and "replace token" permissions are correct in the policy.The rsreportserver.config says:
   <Authentication>       <AuthenticationTypes>          <RSWindowsNTLM/>       </AuthenticationTypes>       <RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>       <RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>       <EnableAuthPersistence>true</EnableAuthPersistence>    </Authentication>
I have SQL server 2005 developer edition installed on my machine. I was expecting something like Enterprise manager to manage databases but nothing is installed by default. Ive found SQL server management studio express and installed it, but Ive also seen references to SQL Server Management Studio. Are there any differences between the two , or are they the same and are just described differently ?? Im a bit confused, Microsoft should make this clear.
Hey everyone, Would someone please tell me why the new SQL Server Managment Studio comes with hardly not frills? What happened to the SQL Enterprise Manager in SQL Server 2000? It had DTS, SQL Agent Job and step viewing, Cool Wizards.. All that great stuff. but with this new Sql Server Management Studio with 2005 all I can do is pretty much look at the data and write queries and create and modify tables. Am I missing something? I did download an external tool which was called an import/export wizard but it sucks compaired to the DTS in SQL 2000. I did notice when I open my Sql Server Management Studio "Express" that it is the Express version... Is there a difference? Thanks for any help.
i have SQL Server 2005 dev edition and i wanted to use SQL Server Management Studio so is it installed or i need to install the SQL Server Management Studio express edition ??? thx
