hidden views

SQL Server Tools General

Index ‹ SQL Server

- Previous
  - 1
    - Is there Limitations to Importing data to SQL Server Express Hi Bill, SOL Server express is an evaluation copy. You need the full version. Please see this thread......... http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=157773&SiteID=1 Tag: hidden views
  - 2
    - Multi-database Multi-server I think your are fine. RS report can access tables from multiple databases at once or you will also be able to write a report that aims one database, but it changes depending on, for example, user preference. Tag: hidden views
  - 3
    - DB does not autogrow I saw exactly the same problem years ago with a large DB. The problem is that the SQL statement that is causing the Auto grow is timing out, so the grow rolls back ! Next statement does the same thing. Never keep the default 10%, always set to some size that can be acheived in under 10 seconds. I use 100Mb on data and 10mb on transaction. You should not really be relying on auto-grow, a DBA should monitor it and manually grow during off-peak time. Tag: hidden views
  - 4
    - Want Text box font to be inherited from list Hello, Not sure if this will help, but every report in srs is an xml file, right click on the rdl file and click view code (xml) You can use CSS (cascading style sheet) with xml, an example is: An xml file w/out css: http://www.w3schools.com/xml/cd_catalog.xml The CSS: http://www.w3schools.com/xml/cd_catalog.txt An xml file w/ css: http://www.w3schools.com/xml/cd_catalog_with_css.xml Taken from: http://www.w3schools.com/xml/xml_display.asp Tag: hidden views
  - 5
    - new SSRS install, works great until I try to access Oracle datasource - System.Data.OracleClient requires Oracle client software re: new SSRS install, works great until I try to access Oracle datasource - System.Data.OracleClient requires Oracle client software From my desktop the report accessing an Oracle 9i db with no problem. I've deployed other reports accesing sql server with no problem. Now I deploy the report and try to access my Oracle source and get: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater On the server in question, I know they've installed the oracle client. I can access the db from sql plus with no problem. As the user that I access ssrs credentials, i remote login and am able to write to oracle home.. C:\oracle\ora10g\NETWORK\ADMIN i've confirmed my database is in tnsnames and it works from sqlplus. Tag: hidden views
  - 6
    - copying tables DTS vs SSIS - speed!!! Use the "Table or view - fast load" option in your OLE DB Destinations. Tag: hidden views
  - 7
    - using historical attributes with SCD transform on a table with a PK You're going to need more fields in that table to make it work... Where is your current record indicator (either start and end date fields or a current indicator field) And ID shouldn't be the PK of the table as that is the business key, designed not to change. You should have a separate PK field in that table -- either an (gasp!) identity field, or a self-generated surrogate key field. This allows for the business key to never change (it shouldn't!), but still allows for new records to be inserted because the business key isn't part of the table's primary key constraint. Tag: hidden views
  - 8
    - How to validate the parameter values? There is no way to validate and inform the user if the parameter value is not in a given range. Though you can restrict the values that user can select for the parameter by providing pre-defined available values to the parameter, either from query or non-queried. Shyam Tag: hidden views
  - 9
    - How do I go about placing photographs in SQL? Not sure if this is the best place for the question, but check into the VARBINARY(MAX) datatype for image storage. Simone Tag: hidden views
  - 10
    - Average of children Give me the SQL script that generate your tables to understand more exactly what you pretend! regards! Tag: hidden views
  - 11
    - Query Time Limit? It prevents query from executing based on the cost rather than controlling resources. BOL 'If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely. "Query cost" refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration. Tag: hidden views
  - 12
    - Personal Web Developer Kit SQL Error In the default installation, SQL Server installs with very tight security. Please refer to these articles for help in setting the correct configuration parameters. Configuration - Configure SQL Server 2005 to allow remote connections http://support.microsoft.com/default.aspx scid=kb;EN-US;914277 http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx Configuration -Connect to SQL Express from "downlevel clients" http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx Configuration -Connect to SQL Express and ‘Stay Connected’ http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html Configuration - Guideline for Connectivity Question Posting http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=362498&SiteID=1 Tag: hidden views
  - 13
    - SQL Server - using a cursor to find and sum login/logout times You can do this without a cursor, but this method does require that you don't have bad data. Let me explain... If EVERY login has a corresponding logout and the date/times match up nicely, you can use set based operations to perform this query. What I present below will create a table variable to store your data. If this method works for you, simply remove the @Temp variable and change the subsequent query to pull the data from your table. -- Setting up some sample data. Declare @Temp Table(UserAction VarChar(20), UserName VarChar(20), ActionTime DateTime) Insert Into @Temp Values('login', 'user1', '2007-02-15 10:21:35.590') Insert Into @Temp Values('login', 'user2', '2007-02-15 10:21:36.100') Insert Into @Temp Values('logout','user1', '2007-02-15 10:22:45.100') Insert Into @Temp Values('login', 'user3', '2007-02-15 10:23:23.100') Insert Into @Temp Values('logout','user2', '2007-02-15 10:27:54.436') Insert Into @Temp Values('logout','user3', '2007-02-15 10:30:03.237') -- Actual query would begin here. Declare @Actions Table(RowId Integer Identity(1,1) Primary Key Clustered, UserAction VarChar(20), UserName VarChar(20), ActionTime DateTime) Insert Into @Actions (UserAction, UserName, ActionTime) Select UserAction, UserName, ActionTime From @Temp Order By UserName, ActionTime Select A.UserName, B.ActionTime - A.ActionTime From @Actions As A Inner Join @Actions As B On A.UserName = B.UserName And A.RowId = B.RowId - 1 And A.UserAction = 'Login' And B.UserAction = 'Logout' Tag: hidden views
  - 14
    - Query performance problems with join on UDF-based computed column By default, the engine does not know anything about the udf (i.e. has no statistics). So, bad plan can be generated. To avoid this, I suggest that you create your udf with schemabinding and persist your computed column. Tag: hidden views
  - 15
    - Pointers to the best documentation on star joins and the optimiser The star query optimization in SQL Server is index based optimization. The following resource will tell you basic of the query optimization for the star queries, and also how to figure out whether the final query plan benefits from the star join optimization. Chapter 14 and looking for 'star queries'. http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/default.mspx mfr=true http://blogs.msdn.com/sqlqueryprocessing/archive/2007/04/09/how-to-check-whether-the-final-query-plan-is-optimized-for-star-join.aspx In order to understand the spool issue further, if possible, please post the statistics XML you got from the query plan with eager spool. Usually, that would be caused by an underestimation of the cardinalities and the optimizer believes store the data in tempdb and reuse it later is a cheaper solution. Tag: hidden views
  - 16
    - Multiple Select statements + Stored Procedure At a time you can only bind one resultset. if you need both the resultset on your page, you need to have 2 datagrid view. If you use dataset, grid1.DataSource = dataset.Tables[0]; grid2.DataSource = dataset.Tables[1]; If you use datareader, grid1.DataSource = datareader; datareader.NextResultSet(); grid2.DataSource = datareader; Tag: hidden views
- Next
  - 1
    - Login Failed for <domain name>\<Machine Name> Use a Service account for your report server web identity and app pool in form <domain> \username make sure this user is an admin on DB and on the web server and is also a Member of the IIS_WPG group in the web server gatharia Tag: hidden views
  - 2
    - adding client server aliases Unfortunately, as you noticed, the functionality to import a list is missing. If you think it would be a useful addition, please let your wishes/needs be known at: Suggestions for SQL Server http://connect.microsoft.com/sqlserver Tag: hidden views
  - 3
    - Profiler trace bind variables As I indicated in your previous thread, Profiler will provide you the opportunity to log the entire SQL statement, replacing the parameters with the values. For @parameter1 = 25, @parameter2 = 3; insert into x values ( 25, 3 ) etc. And I still think you are 'barking up the wrong tree' to seriously be considering Profiler as a Auditing tool. Tag: hidden views
  - 4
    - aspnet_wp.exe crashing intermittently After so many attempts, the problem came out to be with spoolsv.exe. This is the windows print spooler service. As soon as a print command is given from any application, the spoolsv.exe used to consume a part of the non-paged pool of Kernel Memory. But the real culprit I think is the HP TCP/IP Port, which is configured for the HP Printer. The problem was found out by adding the pooled and non-pooled columns in the windows task manager. As soon as the print is spooled the non paged pool is consumed and is not released. And goes on accumulating until it reaches near to 253 MB and ultimately the scenario happens. There is a bug in MS Winsock Library, which might be causing the HP Driver to show this kind of behaviour. Anyway the problem is temporarily solved by restarting the Print Spooler Service. Tag: hidden views
  - 5
    - Problem for Calling A Stored Procedure, Please help. Hi,xxd If you wanna get data from database without using dataset. maybe you can try function. By the below case, the SQL substring can't add the local varity into the sentance. It was because the "exec sp_executesql " will be the other Transcation. Tag: hidden views
  - 6
    - Data Type Range Not quite. Once the limit is reached...crash An identity column creates a constraint that does not allow duplicates and also an index to remember the last number generated. Use int not tinyint and drop the field and recreate it if you need to. Adamus Tag: hidden views
  - 7
    - A CLR SVF works on my DB instance but not on the other one Can you debug the procedure I would use something like the Pipe.Send command to send some debbuging information if you are not able to debug the procedure right on the server. Its hard to tell where the problem is based on, so debugging using SQLPipe should be a good alternative for you. Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: hidden views
  - 8
    - Scheduled Profiler Hi, You should schedule a t-sql task with SQL Server Agent. Use the sp_trace_create to create a trace, sp_trace_setevent to set up your events for monitoring and sp_trace_setstatus to start, stop or pause a trace. I hope it helps. Regards, Janos Tag: hidden views
  - 9
    - I can't get SQL Server 2005 Enterprise Edition working Enterprise Edition (EE) requires a server operation system. http://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx Make sure that you installed SQL Server Express (sucessfully) by checking the Services running on the machine. Look for services name "SQL Server*" Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: hidden views
  - 10
    - Default Constraint on columns Pros and Cons? My opinion is use them when its appropriate. For example, if you had a BIT field such as "OkToSendLotsOfSpamTo" then you may wish to have a default on this to say default(0) as in opt out. However, for data such as a persons gender char(1) it makes no sense to have a default. HTH! Tag: hidden views
  - 11
    - Exporting reports to PDF hii do you want the visual studio report what ever you developed to in sql server 2005 to be exported into pdf format then just see the preview of your report. then in that same page one botton is there just before to the fontsize called the export bottom. just see the drop down list to what you wanted to export your report ,you can choose there. you can see there that you can export your report to tiff format,web archive format,pdf format and excel format also. hope you have asked this then it will work fine for you thanks and regards mahasweta Tag: hidden views
  - 12
    - PIVOT sql_variant into underlying dataypes Jim: Is this the transformation you are looking for: create table dbo.alphabet ( A tinyint, -- Needs to be changed B tinyint, -- Needs to be changed C tinyint, -- Needs to be changed Letters char(1), LetterValue sql_variant ) go insert into dbo.alphabet values (1, 2, 3, 'D', 29) insert into dbo.alphabet values (2, 2, 2, 'E', convert (datetime, '2/20/7')) insert into dbo.alphabet values (3, 2, 1, 'F', 'This is a test.') insert into dbo.alphabet values (4, 5, 6, 'F', 'Just another test.') insert into dbo.alphabet values (5, 5, 5, 'D', 30) go --select * from alphabet select A, B, C, cast ( as integer) as , cast ( as datetime) as , cast ([F] as varchar) as [F] from dbo.alphabet pivot ( max(LetterValue) for Letters in ( , ,[F]) ) alphabetPivot -- A B C D E F -- ---- ---- ---- ----------- ----------------------- ------------------ -- 1 2 3 29 NULL NULL -- 2 2 2 NULL 2007-02-20 00:00:00.000 NULL -- 3 2 1 NULL NULL This is a test. -- 4 5 6 NULL NULL Just another test. -- 5 5 5 30 NULL NULL Tag: hidden views
  - 13
    - SSIS - Best way to copy files from one directory to another Linda, this post should address your problem: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html then you can use package configuration to set the path value to the variables involved: Tag: hidden views
  - 14
    - Help exporting data from .csv file to Sql server Look at the REPLACE function. Also, aren't you specifying (") as the text qualifier in your file setup As for converting the string True/False to boolean, you can use conditionals in a derived column. [Column] = "True" 1 : 0 You might just be able to cast the column, though I'm not sure. You'll have to test that out. Tag: hidden views
  - 15
    - ReportServer DB and ReportServerTempDB databases What data specifically are you trying to clean up How are you measuring performance to know that this is the root of your problem Tag: hidden views
  - 16
    - admin or other role In Books Online, look up [fixed server roles] and [fixed database roles]. Those roles are predefined. You can also create your own roles (which only has permissions that you grant to them.) Full server administration = sysadmin. Single database administration = db_owner. Tag: hidden views

MyraK

hi ,
i have created views in database Test, in sqlServer.when i had to export Test to another database Test1, the views become hiddden.
i can't see the views and when i try to create them again, i get this msg:
There is already an object named 'view1' in the database.
so how can i resolve this problem

thanks,

Re: hidden views

Madhu K Nair

when u export view from one database to another the view is created as table in the target database. To migrate view , create script of the view and run that script in Target DB... ... do not select view in Export wizards only select tables...

if you are getting View1 already exists then :-

Check in Tables there will be a table called View1 , first drop that and run the script for view1 to create...

Madhu

Re: hidden views

Satya SKJ

When you choose to Generate script for the database objects, then it should have that value to drop the object before creating it, follow as suggested.

Re: hidden views

MK13

Well, thanks!

As u said, the views were created as tables in the target DB. So i droped them and made a new script for the views.

Myra

Re: hidden views

MK13

thanks for ur help!

myra