tabel scan

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - Log File path in Configuration file does not work. I'm not sure about this but it COULD be that the log fails validation before the config gets applied. Try setting it in the package to c:\temp\SalesforceConnect.log or something similar and see what happens. -Jamie Tag: tabel scan
  - 2
    - How to compare values in different rows? In order to best get this to work you need another row to explicitly sequence the data -- such as ROW_ID or something. The otherwise, you can use Transact SQL extensions, but without an explicit sequence of some kind, the results will be volatile / unpredictable. It would probably be best to avoid the extensions and stick to a CTE here if you can. Code Snippet declare @ex table(id int, v1 int, v2 int) insert @ex select 1, 2, 8 union all select 2, 7, 10 union all select 3, 11,15 union all select 4, 13,17 --select * from @ex select a.id, a.v1, a.v2 from @ex a join @ex b on a.id - 1 = b.id and a.v1 <= b.v2 /* id v1 v2 ----------- ----------- ----------- 2 7 10 4 13 17 */ Tag: tabel scan
  - 3
    - Why is taking so long to open/render the reports for the first time? I can't believe nobody has the answer to this question! Let me rephrase my question. When I open a report for the first time, it takes up to a minute to show it on the browser!! Why If there is no activity, Report Server goes to a sleep mode or something Tag: tabel scan
  - 4
    - Table design question Your query performance is a function of the available indexes chosen by the query optimizer. These choices are made based on the current "statistics" of the indexes. That is, how is the data in the table distributed Does the query span so many rows that a table-scan is necessary In order to see how the QO plans to run the query, use SS Management Studio and the Query Analyzer (Show Actual Query Plan) to see what plan is to be used. Using ad-hoc queries that are materially different each time is problematic as it forces the engine to recompile the query, but this might be a tiny fraction of the time used to execute the query and return the product. See chapter 2 of my book "Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)" for more details. Tag: tabel scan
  - 5
    - Inconsistencies in FOR XML and Typed XML Schemas I'm writing an extract procedure that extracts data into nested XML. This XML is then passed to another SQL 2005 database that needs to consume it. However, this is not working and below is why. I'm hoping someone has a solution. One of the fields being extracted is datetime. As I've found and read, this causes problems. However, the bigger problem is with FOR XML. The statement extracts dates in the format: 2007-02-07T18:51:29.890 A typed XML schema requires there to be a time zone on the end in the format: 2007-02-07T18:51:29.890Z In order to validate that the XML is still valid from one database to the next, I need to create a typed schema, then load the incoming XML file into a column using that schema. As I'm sure you can tell by now, it fails. There are hundreds of articles on the fact that is must be valid and can't use sqltype:datetime but rather xsd:DateTime, but why doesn't FOR XML extract it in the right format to begin with My issue is in the inconsistency in the process. I should be able to extract data using FOR XML and import it into a typed XML schema column WITH NO CHANGES OR ISSUES with the extracted data! I should also be able to do this all with T-SQL and not use any other method (including XQuery and convert) to manipulate the XML first. Below is a script that can be used to validate my process. use tempdb go create xml schema collection dbo . testschema as N '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="t"> <xs:complexType> <xs:sequence> <xs:element name="CurDate" type="xs:dateTime" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>' GO create table temp ( MyXML xml ( DOCUMENT testschema )) GO -- use DEFAULT settings for generating xml: note that the date IS NOT extracted with 'Z' select CurDate from ( select getdate () curDate ) t for xml auto , type , elements /* Resulting XML document without schema looks as follows: <t><CurDate>2007-02-07T18:51:29.890</CurDate></t> */ GO DECLARE @order nvarchar ( MAX ) SELECT @order = '<t><CurDate>2007-02-07T18:51:29.890</CurDate></t>' insert temp values ( @order ) -- fails with this error: --XML Validation: Invalid simple type value: '2007-02-07T18:51:29.890'. Location: /*:t[1]/*:CurDate[1] GO -- add the 'Z', everything works DECLARE @order nvarchar ( MAX ) SELECT @order = '<t><CurDate>2007-02-07T18:51:29.890Z</CurDate></t>' insert temp values ( @order ) Tag: tabel scan
  - 6
    - Sql Query to Details about Database Column Names/Description Code Snippet SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable' Table size Code Snippet sp_spaceused 'Mytable' Tag: tabel scan
  - 7
    - Create Report.rdlc in vb.net dynamically Its just and XML file so sure you can. You can use either the textwriter or xmltextwriter classes among other ways. I suppose the real question is what you want to do with it afterwards. Tag: tabel scan
  - 8
    - SSEE Reporting Services - Can't get Report manager to work Did you try with http://localhost/reportsmanager Tag: tabel scan
  - 9
    - Some questions about indices You should only create indexes that help queries that you are doing frequently. SQL Server supports two types of indexes: clustered indexes and non-clustered indexes. In a clustered index, the actual data is sorted in the leaf levels of the index based on the columns that are part of the index. In non-clustered indexes, the data is not stored in the leaf leaves. Based on your description given above, I would create a clustered index with the columns StartDate, IsComplete, and two non-clustered indexes, one on ID and one on GUID. Note that this is just general advice. I would recommened that you try out different indexes, and see how performance benefits (or degrades). Thanks, Tag: tabel scan
  - 10
    - Restoring SQL database with failed backup if you have copies of .mdf and ldf file you can attach that file. read about sp_attach_db in BOL. Madhu Tag: tabel scan
  - 11
    - Filter multivalue DateTime parameter Use the "In" operator in your Filter tab and under Value select your parameter, which is an array in your case because it is multi-valued. Keep your parameter as DateTime datatype. For example, if your field name is Date1 and your parameter name is Parameter1, do this in the Filter tab of your dataset: Under Expression, type =Fields!Date1.Value Under Operator, select "In" Under Value, type =Parameters!Parameter1.Value Shyam Tag: tabel scan
  - 12
    - Login failed for user '(null)' I would check with the third party app. It seems like they are using an account that isn't authorized in SQL Server. It all depends on how they have implemented their connection. Tag: tabel scan
  - 13
    - Is it possible to export a database model from visio to Sql Server 2005? Not sure if this is what you mean but you can't export the data model from Visio into SQL Server database diagrams. -Sue Tag: tabel scan
  - 14
    - Configuration Order Philip Coupar wrote: I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages. It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package. Does anyone have a solution for this Are indirect configs an option I have never used sql configs but I believe you can store the location of the configs in an environment variable. In this situation all you need to do is update an environment variable. -Jamie Tag: tabel scan
  - 15
    - scheduled task not running Any chance of seeing the command Initial thoughts might be if the file is locked or opened somewhere else. Does the user that the SQL Server Agent run under has permissions to access the file Have you looked at the job history for any clues Tag: tabel scan
  - 16
    - How do it right? Your queue is closed, and it might be closed because you have a poison message. Add TRY...CATCH error handling to your code and record any errors received to an error log table. First, however, you will need to enable your queue: ALTER QUEUE WITH Status = ON. If you are using activation, which I assume that you are: ALTER QUEUE WITH Status = ON, ACTIVATION(Status = ON) information on poison messages: http://technet.microsoft.com/en-us/library/ms171592(SQL.90).aspx Tag: tabel scan
- Next
  - 1
    - Cannot Load SQLSpars.dll A few customers have reported this issue but we haven't tracked down the bug or a workaround yet. If you can post a link to your Setup log files, I'll try to convince a developer to look at them and offer advice. Paul Tag: tabel scan
  - 2
    - Strange thing in All examples at MSDN Full name of object in SQL Server is ServerName.DatabaseName.SchemaName.ObjectName. In MSDN samples Purchasing is a schema name. Also, you could use AdventureWorks.Purchasing.ProductVendor and AdventureWorks is a database name. You could download last version of samples database here - http://www.codeplex.com/MSFTDBProdSamples Tag: tabel scan
  - 3
    - SQL server database services option greyed out It could be you are installing the Enterprise edition which you cannot install on XP or IIS is not installed so SQL Server installer is skipping all dependent components which was only Reporting services. The link below is Microsoft System requirement page for SQL Server 2005. Hope this helps. http://www.microsoft.com/sql/prodinfo/sysreqs/default.mspx Tag: tabel scan
  - 4
    - Where do I buy SQL Server Management Studio? Hi Jeffery, http://msdn2.microsoft.com/en-us/express/bb410792.aspx and much more. CU tosc Tag: tabel scan
  - 5
    - Sorting a table with numbers and letters Is the table structured like this 1 a 2 b or like this 1a1 2b2 c3c If it's the first case you should be able to sort the table by creating two queries, selecting just the letters and just the numbers and then unioning the two queries together, or possibly using a case statement in the order by clause of the query. For the list itself you can try a similar fix using two lists, showing just the letters in the first list & just the numbers in the 2nd list, and then sorting by just letters & just numbers. Hope this helps. Andrew Tag: tabel scan
  - 6
    - Error with SMO.TransferData() (Exception from HRESULT: 0xC0011008) Try to set the connection up mannually. So instead of using the connection string: Dim SqlCon_Master As SqlClient.SqlConnection = New SqlClient.SqlConnection( My .Settings.LM_Master_SQLConStr) Use the server.(Parameters) such as userid, password, ect. I ran into the same issue a while back with the transfer, once I set my connection parameters to my server manually this corrected the problem. Here is an example in c# that I wrote maybe it will help. The vb conversion should be straightforward: Code Snippet //Two Server SMO objects to hold source and destination Server server; Server Source; try { //Cannot use the SqlConnection class as this locks out DTO from accessing the login method. //It appears DTO needs to be able to set the login properties which the SqlConnection locks it //out of doing. Below commented methods will not work in this instance. //SqlConnection conn = new SqlConnection("Data Source=.\sqlserver;integrated security = true"); //Server server = new Server(new ServerConnection(conn)); //Destination DataBase connected in an SMO manner using serverconnection ServerConnection connec = new ServerConnection(".\SqlServer"); //Set secure login connec.LoginSecure = true; server = new Server(connec); //Create target database. Transfer method does contain a create command but have not figured //out how to get this to work. Easier to use the SMO method at this time. Database dbo =new Database(server,"AdventureWorksClone"); dbo.Create(); //Source Database connected in an SMO manner using serverconnection ServerConnection conn = new ServerConnection(".\SQLServer"); //Set secure login conn.LoginSecure = true; Source = new Server(conn); //Set source to existing database that is to be copied Database db = Source.Databases["AdventureWorks"]; //Establish transfer passing in source database Transfer t = new Transfer(db); t.CopyAllObjects = true; t.DropDestinationObjectsFirst = true; t.CopySchema = true; t.CopyData = true; //Don't know if this is still needed as it has been set in an SMO manner t.DestinationLoginSecure = true; //Destination Server created above t.DestinationServer = server.Name; //Could not get this to work yet //t.CreateTargetDatabase = true; //Newly created database on destination - Clone not quite right word as in ADO uses clone //to indicate copying of schema only - not the case here t.DestinationDatabase = "AdventureWorksClone"; t.Options.IncludeIfNotExists = true; //This will copy all of the data and relations //Alternatively could use t.scripttransfer() to copy just the schema //SqlBulkCopy might be faster but might not make a copy of entire database t.TransferData(); //Release server objects server = null; Source = null; //Give verification that code is complete Console.WriteLine("Executed"); Console.ReadLine(); } catch (Exception ex) { Console.Out.WriteLine(ex); Console.ReadLine(); server = null; Source = null; } } } Hope this helps a little, sorry the code is in c#. Aaron Tag: tabel scan
  - 7
    - Can't uninstall SQL Server 2005 or install .NET 3.0 64bit after installing Office 2007 Well I had no response on the 64bit NET forum so I am posting here !! Windows Automatic Update has just failed to install .NET 3.0 (x64) WAU decided for itself that I needed this update Add Remove Programs reports the following (x64) programs ... .NET Framework 2.0 .NET Framework 2.0 SDK MS SQL SERVER 2005 VS2005 64bit prerequisites VS2005 64bit Remote Debugger MSinfo32 reports OS Name Microsoft(R) Windows(R) Server 2003 Standard x64 Edition Version 5.2.3790 Service Pack 2 Build 3790 Other OS Description R2 OS Manufacturer Microsoft Corporation System Name DEV-1 System Manufacturer Dell Inc. System Model Dell DXC051 System Type x64-based PC Processor EM64T Family 15 Model 6 Stepping 4 GenuineIntel ~3392 Mhz Processor EM64T Family 15 Model 6 Stepping 4 GenuineIntel ~3392 Mhz BIOS Version/Date Dell Inc. A05, 31/03/2006 BTW this is a fresh install because I was completely and utterly unable to uninstall SqlServer2005 which was necessitated by foolishly installing Office 2007 (Office 2003 being no longer available!) Tag: tabel scan
  - 8
    - Whats the different between SQL Express & SQL Server 2005? HI, 1. yes , Refer http://www.microsoft.com/sql/editions/express/redistregister.mspx 2. Refer http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx 3. Yes Hemantgiri S. Goswami Tag: tabel scan
  - 9
    - SQL Server Management Studio Express on Vista Home Premium (Error on attaching and detaching .mdf) Hi Glen - Is your user account actually a member of BUILTIN\Administrators For example, when you use 'Run as Administrator' do you have to supply a different set of credentials The errors you're getting seem to indicate that the user account being passed doesn't have the appropriate permissions. Could you check the list of Logins in the Security tab in Management Studio and let us know which accounts are provisioned and what server roles each Login belongs to Mike Tag: tabel scan
  - 10
    - SUM returns incorrect value in SQL Server Mobile Query ... Bug?? ... I edited the above post and included screenshots of the issue I am experiencing. Thanks again. Tag: tabel scan
  - 11
    - Blocking in Analysis Services You are in the wrong forum. Moving to Reporting Services forum first. Tag: tabel scan
  - 12
    - converting varchar to int Hello Raj, What should '3.1.7.4.3.9.' be, ayou wanting to remove the .'s to have it be 317439 Try this: =cInt(Replace(Fields!Field1.Value , "." , "" )) Jarret Tag: tabel scan
  - 13
    - Error - The report server cannot decrypt the symmetric key? It cropped because VS.NET 2005 did aspnet_regiis.exe. Assuming that you have backed up your encryption key (you did right ), you can use the Reporting Services Configuration utility to restore it. If no backup, do: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RsKeyMgmt.exe" -d Then, go to the Report Manager and reset the connection string in all data sources. Tag: tabel scan
  - 14
    - need to determine which db is pricipal Hi, i find the solution !!!! on the mirroring properties just push on FAILOVER !!!! WELL DONE BILL Tag: tabel scan
  - 15
    - Different ways to set DATEFIRST You 'could' set the default login Language to 'British'. But that would also make the default date format to be dmy instead of the US based mdy. At your 'own risk', you may wish to explore changing the datefirst value (from 7 to 1) in the sys.syslanguages table. Tag: tabel scan
  - 16
    - custom url not recognized i fixed it, in order to get it to fill out property i used the .tostring method for the field Tag: tabel scan

shamen

Hi All,

When an end user view a report using report manager or a custom build web site, would query of the report scan the tables in original data base In this case it is a OLTP data base.

Thanks

Top

Re: tabel scan

Adam Tappis - IMGroup

That depends on the design of your table and your query.

Reporting services just submits the query to the relational database engine. It's the databse engine that's responsible for deciding on how it returns the data to reporting services.

Top

Re: tabel scan

shamen

How can I make sure when somebody views a report, it does not scan tables of the data base If I use remote mode in report viewer, can i achieve this

Thanks

Top

Re: tabel scan

Adam Tappis - IMGroup

Like I said, that it totally dependent on the design of your table and your query e.g. the indexes on the table and the where clause of your query.

This really is a database question and not a reporting services question.

Top