Drillthrough (beginner)

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - Date time Hello all I am having a hard time in solving this My application gives me a date in the format Ex: Tue Oct 16 13:23:04 CDT 2007 coming to Oracle db as a string This date(String) is going to SQL Server into three differnt Colunm Col1 will have : 2007 - 10 - 16 Col2 will have : 1:23:04 (Military time has been changed to 12 hr format) Col3 will have : PM (Since its was 13 hour it PM) The format I am getting from application is giving me trouble. How can I do this Please advice! Tag: Drillthrough (beginner)
  - 2
    - Error The set in the WHERE clause cannot contain multiple measures I won't bother spinning my wheels on it but my thinking is that it is a bug in the behavior of the query. Because the query works fine when the MEASURE is moved to the axis (eg "SELECT ON 0"), there is no reason the two hierarchies of product should conflict with each other when the MEASURE is in the slicer (ie. WHERE). Tag: Drillthrough (beginner)
  - 3
    - Microsoft.SqlServer.Dts.Pipeline.BlobColumn Ok, I must be extra-dense today. But the light bulb just came on. The problem is that the Microsoft.SqlServer.Dts.Pipeline.BlobColumn class inherets its implementation of the ToString method from the Object base class. This means that it just returns the name of the class: "Microsoft.SqlServer.Dts.Pipeline.BlobColumn." As you can probably imagine, this would handily explain the MessageBox message you've reported. So... You actually need to use the BlobColumn.GetBlobData method instead: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.blobcolumn.getblobdata.aspx Enjoy! Tag: Drillthrough (beginner)
  - 4
    - Excel 2007 DataMining Add-in Database on SQL Server 2005 destroyed by using SQL Server Managment Studio? Hello, i have made some Data Mining Model Examples in Excel 2007 (not temporarily!). They where there after leaving an re-opening Excel. I have used them several times. Then I want to look, if I can see them also via SQL Server Managment Studio in the Analysis Services. There where nothing in the DMAddInDB in Analysis Services. And after this, in Excel my DataMining Models have disappeared and all Models i have made since this disappeared also. Perhaps I have destroyed the database. But will this happen every time Can I share Data Mining Models I have made with Excel with Projects in SQL Server Analysis Services Thanks Berenice Tag: Drillthrough (beginner)
  - 5
    - Select Statement i would like to select only those that have statusdates that are later than statusdates with statusky 'A'. Table1 PaperID StatusID StatusDate StatusKey 0001 4566 2003-09-03 00:00:00.000 D 0001 4222 2003-09-03 00:00:00.000 C 0001 4132 2003-09-01 00:00:00.000 A 0002 4222 1999-04-14 00:00:00.000 C 0002 4132 1999-04-14 00:00:00.000 A 0003 4132 1986-08-03 00:00:00.000 A 0003 4566 1986-07-29 00:00:00.000 D 0004 4135 1999-08-27 00:00:00.000 A 0004 2000 1999-08-30 00:00:00.000 C And i would like to have a result of : PaperID StatusID StatusDate StatusKey 0001 4566 2003-09-03 00:00:00.000 D 0001 4222 2003-09-03 00:00:00.000 C 0001 4132 2003-09-01 00:00:00.000 A 0004 4135 1999-08-27 00:00:00.000 A 0004 2000 1999-08-30 00:00:00.000 C I do not want to select 0003 because it has the correct data (statusdate with statusky 'A' is later than the other status dates) as well as 0002 because the dates are the same. What is the best query to use thanks. Tag: Drillthrough (beginner)
  - 6
    - remove SQLserver2005 problem hi all, what is the best way to remove SQLSERVER2005 from my computer running XP-sp2 when i try to remove from tha ADD and Remove program from the control panel it's start the wizard but going to stuck.. thankx Tag: Drillthrough (beginner)
  - 7
    - insufficient access rights error when doing backup i have followed the same methods ..registry updates and restarted the system ..using windows xp prof. sp2 with sql server 2005.. i can take backup from query analyzer..i want take backup from GUI... still error shows "property backup directory not available for settings microsoft.sqlserver.management.smo.settings this property may not exist for this object or may not be retrievable due to insufficient access rights "..pl. Tag: Drillthrough (beginner)
  - 8
    - FileProperty and sp_spaceused Hi All I am trying to execute the following set of commands: select fileproperty('master','SpaceUsed')*8; select sum(reserved)*8 from master.dbo.sysindexes where indid in (0,1,255); exec sp_spaceused ; I am getting different values from fileproperty and the sp_spaceused. I am wondering why this is happening. I am sure that there is a valid reason behind this. Can anyone explain why this is happening I also tried using DBCC updateusage('master') with count_rows and sp_updatestats. Thanks Vijay Tag: Drillthrough (beginner)
  - 9
    - Vista SQL 2005 SP2 client to Windows XP SQL 2005 (non-sp2) workstation. (Take 2 w/ Workaround) This issue has not been "resolved" as I can tell. But there is a workaround w/o using sql authentication (hate sql authentication) http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1528018&SiteID=1 I found out that it doesnt matter which combination you use for the server as long as the client is Vista... If you are using a windows account, lets say "jondoe" on your vista box acting as a sql management studio client to a sql server on an xp or w2k3 box using windows authentication.... IT WILL work if both the vista box and the xp/w2k3 box BOTH have "jondoe" accounts, and both "jondoe" account passwords are the same!!! SOOOooo.... Apparently when vista runs the program under jondoe (when you launch the shortcut) it runs it under that context (no-duh) but EVEN if you establish a network connection (using net use) beforehand with alternate credentials (lets say on the xp/w2k3 box the user is jonsmith) and then use sql management studio you are assuming that it will just use those trusted credentials.... you are wrong... it wont anymore in vista by default. WTF Why because apparently sql management studio 2005 no longer is allowed to retrieve stored credentials to impersonate jonsmith when connecting to the xp/w2k3 box.... this is where the infamous "no user is associated with this trusted connection". I also got this error with Visual Studio 2005 Web Express Edition... hmmm... a correlation i see SOOOoooo... Workaround 1 (more secure): Create the windows account "jonsmith" on your vista box, set the password to the same exact password for the server's user account. then run the sql management studio application under the jonsmith account using "runas". Result: it works just as it supposed to... using windows authentication. Workaround 2 (less secure): Create the account "jondoe" on the xp/w2k3 box, set the password to the same exact password for the server's user account. then run the sql management studio application under the jonsmith account using "runas". Result: it works just as it supposed to... using windows authentication. Workaround 3: Use sql authentication. works like a charm but not as secure as 1 and 2. FYI: these apply to workgroup connections. domain computers are exempt cause security is handled by domain controllers. I think there must be a security setting (registry option) that would allow this process to work w/o the workaround, but I have yet to figure it out... I'm like a pitbull sniffing around for clues and the only leads i have is that its vista SSPI and that if there is an option, it would be in local security policy AND/OR undocumented registry option. Note: I hate workarounds unless there is a clear cut reason why we have to do it this way. Either because there was a design oops or security consideration when accounts are hijacked.... either way anyone from Microsoft have an explanation of this Oh... firewall enabled/disabled doesnt matter, all systems patched up the yinyang and what nots do not change the end result. All I did was create matching accounts on both ends (using either workaround 1 or 2) and it worked just fine. SOOOOoooo... my question is..... why can't SQLM2K5 MS and VSWD2K5 express grab the existing alternate credentials that i've established (through net use) and run with the ball BTW: I've tried using that "Alert Me" option below. Doesn't work. page takes a nose dive on the POST. Tag: Drillthrough (beginner)
  - 10
    - Unable to create maintenance plan When i tried to create a maintenance plan for my db - it's just a full db backup task, it gave a weird error "Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)." Any idea what is wrong is there anywhere i can find details about the error Tag: Drillthrough (beginner)
  - 11
    - SP2 Report Builder and default save location I don't really understand why this default location was changed. In what I would assume to be a typical world, ad-hoc reports are saved into a user's My Reports directory, so having the default save location of the root directory forced only one level of navigation (to my reports). Now I have to expose (set security on) my models directory (not just individual models) to users and from there they have to know to navigate out of this directory (where they do not have permissions to save reports, as it is a models directory....) to the root, and then into their My Reports folder. This doesn't seem intuitive at all. Ideally I'd be able to set the default location to My Reports but that is not possible if I understand correctly. Why was this change made Tag: Drillthrough (beginner)
  - 12
    - Create Subscription For Rpt Service with Windows integrated security !!! Not sure I understand. When you say multiple copies of a report, you lose me. I have a report that has stored credentials so that the subscription will work. Unfortunately this also means that anyone can log on to the server and view the report. How do stop everyone from viewing the report Tag: Drillthrough (beginner)
  - 13
    - Problem Configuring Reporting Service Hey there, im having a problem that a few people seemed to have had already. The problem is again related to the Web Service Identity. Windows Server 2000, IIS 5.0, SQL Server Express 2005 Adv, and Microsoft.Net 2.0 after installing SQL Server Express 2005 when i launch the Reporting Service Configuration it could not connect to the Server as the Reporting Service was not running. In order to get the Reporting Service running i had to modify the rsreportserver.config file in the C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer directory. Here i modified the added the following <WebServiceAccount>IWAM_SKYREV</WebServiceAccount> this was due to no SKYREV\ASPNET account being available on the server machine. (based on this article http://support.microsoft.com/default.aspx/kb/911846 ) Once i changed this i could now get the Reporting Service running and access the configuration tool. So now we come to my current problem. In the Web Service Identity it shows an exclamation. Here the ASP.net as SKYREV\ASPNET and when i attempt to apply this i get the following error: ReportServicesConfigUI.WMIProvider.WMIProviderException: The account name is not valid. Specify an account in the form domain\alias. at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool) Any one have any ideas for me... Tag: Drillthrough (beginner)
  - 14
    - How to return multiple values from stored procedures How to return multiple values from stored procedures to reports in sql server 2005 Tag: Drillthrough (beginner)
  - 15
    - Cannot see Local Database template in Add New Item Dialog box Per MSDN instructions I downloaded the folliwng: .NET Framework 2.0, SQL Server 2005, SQL Server Compact 3.5, and Visual Basic Expess Edition. When following the tutorial to create a database, I was never able to see the Local Database template. I am taking the programming lessons in VB, and I am up to Creating Your First Datatbase. It is a requirement that SQL Server Compact 3.5 be installed. Which I have done. Tag: Drillthrough (beginner)
  - 16
    - SqlDataSourceEnumerator.Instance; not returning all instances Add the servers to file hosts located in Windows\System32\Drivers\Etc Tag: Drillthrough (beginner)
- Next
  - 1
    - DBCC TableResults Anyone know where I can find the "TableResults" table definition in order to create a table for the DBCC command I can't seem to find it in BOL... Tag: Drillthrough (beginner)
  - 2
    - How can I get AS 2005 to use more than 2GB? I'm currenlty running AS 2005 on a Windows 2003 STD edition 32bit. I have added the /3GB switch in the boot.ini to that the server will use all 4GB. Unfortunately AS only appears to see 2GB based on the following error Memory error: The operation cannot be completed because the memory quota estimate (4095MB) exceeds the available system memory (1996MB). As you can see my the error AS only thinks the system has 1996MB of memory. I can confirm that there is 4GB of memory in the server and the OS can see 4GB memory Thanks for your help Tag: Drillthrough (beginner)
  - 3
    - AS2005 Cube Excel 2007 Filtering Issue > It would be interesting to know about the features in Excel 2007 that are disabled before SSAS2005 SP2 I agree that it would be interesting to know. This question belongs to the Excel forum, however. Also, in couple of months when everybody has upgraded to SP2, this will become a moot point. > Are these new features being enabled by an update on the client I beleive it's enough to upgrade AS server only. Tag: Drillthrough (beginner)
  - 4
    - Apparent Memory Leaks in ODBC We have created a multithreaded application that reads result sets from MS SQL Server (both 2000 and 2005). The MS SQL Server app and our app reside on the same machine. We are using the latest version of ODBC32.DLL (3.526.1830.0) and SQLSRV32.DLL (2000.86.1830). Our application is written in C++ using the Visual C++ 6.0 compiler and libraries. Our app runs as a service, therefore the apparent memory leak is a real problem. Our app needs to run on a server in a closet without human intervention. We are kicking off many user threads that each can read from the database tables. Each of the reads from the database occurs within a critical section to minimize the threads stepping on each other. The ODBC interface class follows all the steps defined in the ODBC application developers documention (see code below). We see or app memory steadily increasing over time (we used PerfMon to monitor Private Bytes and Virtual Bytes, per ODBC documentation). If we terminate the threads which are retreiving the result sets, the memory drops back to the level noted prior to starting the threads. The code below is admittedly inefficient, however, it should not leak memory when accessing the database. Note that it works very well, returns the result sets that we expect exactly. SQLHENV henv = SQL_NULL_HENV; SQLHDBC hdbc = SQL_NULL_HDBC; SQLHSTMT hstmt1 = SQL_NULL_HSTMT; SQLRETURN retCode; SQLSMALLINT sColCount = 0; CODBCInterfaceColumnList lColumnList; CString strDSN; CString strUID = _T("AccountName"); CString strPWD = _T("Password"); CString strServer; CString strDatabase; CString strDebugMsg; if(!CreateDSN( pRecordList->m_strDefaultODBCConnect, strDSN, strDatabase, strUID, strPWD, strServer)) { strExceptionMsg.Format( _T("Cannot create DSN from connect string %s"), pRecordList->m_strDefaultODBCConnect); bReturn = true; return bReturn; } if(pRecordList->m_strDefaultODBCConnect.IsEmpty()) { bReturn = true; return bReturn; } // Allocate the environment handle retCode = SQLAllocHandle(SQL_HANDLE_ENV,NULL, &henv); if(retCode != SQL_ERROR && retCode != SQL_INVALID_HANDLE) { // Set the environment to ODBC Version 3.0 retCode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER); if(retCode != SQL_SUCCESS) { GetErrorMsgs(hdbc, SQL_HANDLE_ENV, strDebugMsg); } if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO) { // Allocate a ODBC connection handle handle retCode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO) { retCode = SQLConnect( hdbc, (UCHAR*)(LPCTSTR)strDSN, SQL_NTS, (UCHAR*)(LPCTSTR)strUID, SQL_NTS, (UCHAR*)(LPCTSTR)strPWD, SQL_NTS); if(retCode != SQL_SUCCESS) { GetErrorMsgs(hdbc, SQL_HANDLE_DBC, strDebugMsg); OutputDebugString(strDebugMsg); } if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO) { // Allocate a statement handle retCode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1); if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO) { // Fixup the select statement retCode = SQLPrepare( hstmt1, (UCHAR*)(LPCTSTR)pRecordList->m_strSelectStatement, pRecordList->m_strSelectStatement.GetLength()); if(retCode == SQL_SUCCESS) { retCode = SQLExecute(hstmt1); if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO) { retCode = SQLNumResultCols(hstmt1,&sColCount); if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO) { if(sColCount > 0) { if(lColumnList.DescribeColumns(hstmt1, strExceptionMsg)) { if(lColumnList.BindColumns(hstmt1, strExceptionMsg)) { int i = 0; while((retCode = SQLFetch(hstmt1)) != SQL_NO_DATA) { if(retCode != SQL_SUCCESS) { GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg); break; } else { // Class to hold the result set. CTableColumnList* pColList = new CTableColumnList(); lColumnList.PopulateColumnListObject(pColList); pRecordList->AddColumnListRecord(pColList); } } } } } } } else { GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg); } } else { GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg); } retCode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1); } else { GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg); } retCode = SQLDisconnect(hdbc); } else { GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg); } } retCode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); } } retCode = SQLFreeHandle(SQL_HANDLE_ENV, henv); We are at our wits end, not sure what to do next. Any help in sorting this out will be very, VERY much appreciated. Tag: Drillthrough (beginner)
  - 5
    - reportviewer issue I am using the reportview in vb2005 to run a report that is on our report server. It just keeps says its processing and does not come up with any results. Any thoughts why this is happening and how I can fix Tag: Drillthrough (beginner)
  - 6
    - From and Thru Date parameters For some reason (I first did this report several months ago then broke it when I changed my cube structure) I was passing the parameters with paranthesis around them: ="(" & code.FromDate(Pa....) & ")". I don't remember why I did that and removing them fixed it. Thanks!!!! Tag: Drillthrough (beginner)
  - 7
    - Case expressions may only be nested to level 10 I'm not doing anything that isn't shown in my SQL statement there (Other than a SELECT and FROM and WHERE). However, nothing unusual. Now if I run that SQL with say 8 statements then it works just fine. I've read that this appears to be an issue and that others have run into it before. In some cases they wrapped the whole thing in a stored procedure on the linked server or their server and ran it that way. In my case I'm executing this against a linked server and can't make a sproc there. I'll play with the examples provided above and see if those will address the issue for me or not. Thanks! Tag: Drillthrough (beginner)
  - 8
    - conditions in where clause Hi I am upsizing the access database to SQL 2005. I currently converting Access Query's to SQL stored procs and functions but i have got stuck on one issue. In access there is a update statement that has an IIF in the where clause, i have tried replicating this in T-SQL buy using case statement but it does not work. in access the where looks some thing like this Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1) Any one know how i can best replicate this behavior Dagz Tag: Drillthrough (beginner)
  - 9
    - Stored Proc creation script in T-Sql Thanks.. That was helpful.. Can i also check whether the proc already exists before creating the script something like this: set @sp = @logDBName + '..sp_executesql' SET @STR = ' SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[sptemp]'') AND OBJECTPROPERTY(id, N''IsProcedure'') = 1) DROP PROCEDURE [dbo].[sptemp] CREATE PROC sptemp @id int as select * from orders where id=@id go ' EXEC @sp @STR Tag: Drillthrough (beginner)
  - 10
    - Report Builder and optional parameters We are talking about Report Builder, a small application designed to allow end users to create ad hoc reports based on data models. RB has serious limitations in the way the report parameters are handled. You cannot explicitly create parameters, but only set "Prompt" flag for filter conditions. You can see "Prompt" when you right-click on the name of the expression in the Filter Data window. I'm new to SSRS and may be mistaking, but I think if you have RS2005, you should also have Prompt option. Dunno about RS2000 though. Denis Tag: Drillthrough (beginner)
  - 11
    - Error - SQL Server requires Encryption On Hello All, I have a commercial application built for Pocket PC's that connects to SQL Server 2005 via TCP/IP over wireless networks. In installations for our sales people we installed SQL 2005 Express on their notebooks for demonstrations. In one of these installs we are getting the following error message: "An error occurred - SQL Server requires Encryption On". We do not use encrypted connections and I have verified encryption is turned off in the Options tab in SQL 2005 login screens. Here is the connection string we use in the app.config file on the Pocket PC's: <add key="connStr" value="Data Source=192.168.0.19,1433;Initial Catalog=SQL0018;User ID = User01;Password=PW01"/> Other apps on the notebook are connecting to this same SQL Server without any issues. Thank you in advance for any help here, Jack Tag: Drillthrough (beginner)
  - 12
    - Applying snap shot at subscriber from DVD on SQL 2000 I want to send snap shots by DVD to a SQL2000 server from SQL2005. This config is replicating fine using trans replication with the publisher and distibuter on the source SQL2005 server. I am using push subscription to the SQL2000 server. I now need to create snap shots on the SQL 2005 server that I can send to the SQL2000 server using DVD. I know how to create the snap shot on the SQL2005 server but I cannot find any way to use it on the SQL2000 target server. Can anyone tell me how to do this Thanks Phil Tag: Drillthrough (beginner)
  - 13
    - How can i encrypt or mask a column in the database How can i encrypt or mask a column in the database Any help will be appreciated Regards Karen Tag: Drillthrough (beginner)
  - 14
    - Maximum size of the SSIS String Variable? I don't believe there is a limit on the string variable size. As mentioned, there is a 4000 character limit in the expression editor, and I believe the limit for NVARCHAR fields for the SQL Native Client provider is also 4000 (above that it has to be NTEXT). Tag: Drillthrough (beginner)
  - 15
    - Enterprise Manager returns [MS Visual Database Tools] - The parameter is incorrect when returning all rows When I try to return all rows from a view or table by selecting Open Table|Return All Rows from the context menu, I get a dialog in the middle of EM stating: An unexpected error happened during this operation. [MS Visual Database Tools] - The parameter is incorrect. Any ideas Tag: Drillthrough (beginner)
  - 16
    - DataFlow Update? Yes. It's committing every row that passes through it. This is a very expensive component, and should be avoided in favor of Matthew's suggestion. Load your updates to a staging table, and then issue a one-time update SQL statement using that table as the source of the changes. I personally avoid the OLE DB Command. Tag: Drillthrough (beginner)

tenchyz

I have created two reports. One links to the other with a drillthrough. The linking works fine, but no values show up in the fields besides the group titles and column titles. Why is this happening

The user is given a list of Counties with values. The Counties(in a matrix) are clickable for a drillthrough of Ages and Genders in the Counties. Looks something like this:

Years

Counties Total Crashes Fatalities

Cook 85 98

Manchester 2850 258

Sagamon 987 526

So when a user clicks a county, such as "Cook", this is displayed

Years

Age Gender Total Crashes Fatalities

0-8 Female 25 8

Male 55 15

9-15 Female 850 258

Male 185 35

16-22 Female 87 526

Male 10 35

But I am getting this in :

Years

Age Gender Total Crashes Fatalities

0-8 Female

Male

9-15 Female

Male

16-22 Female

Male

Whats going on Can anyone make any suggestions Just to let you know its based off of an Analysis Services Cube and not relational tables.

Re: Drillthrough (beginner)

Alison Jolly

Does you second report have a county parameter and, if so, have you linked the County field in the first report to the parameter in the second report

- Ali

Re: Drillthrough (beginner)

tenchyz

Yes, my second report has a county report paramater, and i did the hyperlink the first report parameter to the second. (Right clicked county>Properties>Navigation>Jump to Report>Parameters>CrashCounty=Fields!CrashCounty.value).

It seemed to be linking, but its not displaying hardly any values. I have this report seperate with out the linking and its filled with values. Did i forget something