SSMS Help

SQL Server Tools General

Index ‹ SQL Server

- Previous
  - 1
    - Connection Error: (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe. This error usually indicates that the server closed the connection, e.g. during login verification. The best is to check for any messages from SQL Server correlated to the time of the failure - either in the Application EventLog or in the ERRORLOG file (by default located in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory). There is a chance they provide more information to identify the root cause of the failure. Tag: SSMS Help
  - 2
    - Security required to run deployment utility I forgot to add that we are using the deployment utility. Tag: SSMS Help
  - 3
    - sql query I hope the following expression will help you Code Snippet Select Cast ( Case When Datepart ( Hour , getdate ()) % 12 = 0 Then 12 Else Datepart ( Hour , getdate ()) % 12 End as varchar ) + ':00' + Case When Datepart ( Hour , getdate ()) >= 12 Then ' PM' Else ' AM' End Tag: SSMS Help
  - 4
    - Debug tsql There is no "debug" of stored procs or in query analyzer. You will need to cut it down, exectue it line by line and use print statements, etc to see what is going on. Are you getting an error If you post the stored proc here, someone might help you. Tag: SSMS Help
  - 5
    - Calling a package at Runtime Use precedence constraints between the task that reads the file and three Execute Package tasks. Store the filename in a variable and use that in an expression on the precedence constraint. Make the expression dependent on the variable being equal to the appropriate file name. Tag: SSMS Help
  - 6
    - define a default value ?? maybe something like: Code Snippet create table dbo.yourTable ( anInteger int default (0) ) Give a look to books online. Tag: SSMS Help
  - 7
    - Irritating issue in Reporting Services 2005 SP2. Possibly you could post a screenshot using www.photobucket.com or something similar. I can't really picture what you're saying. Tag: SSMS Help
  - 8
    - Accuracy of Host column on SS 2000 Enterprise Manager Current Activity Process Info Screen Can you check the mAC Address for that host and find on the network Tag: SSMS Help
  - 9
    - Way to find registered classes/functions for the Assemblies added in Analysis Service. Have you not found this in SQL Server Management Studio. Look under the object explorer for Analysis Services. I can see two folders under the SSAS2005 server: Databases and Assemblies. Regards Thomas Ivarsson Tag: SSMS Help
  - 10
    - Problem with AuthenticationExtension I assume you've redeployed the dll to the report server / manager bin directory. Have you tried debugging and stepping into the code Tag: SSMS Help
  - 11
    - Help with finding last record! PJ: Would you please provide sample data and intended results Dave Tag: SSMS Help
  - 12
    - SAC Command Line utility doesn't start SQLBrowser Service I've found that you can set it up to start the SQLBrowser service using command line arguments (SQLBROWSERAUTOSTART=1) to the sql server express install. It still seems odd that I can't start the SQLBrowser service with the tool that is provided to do so when it is set to Disabled. Tag: SSMS Help
  - 13
    - Which Data Flow task to use? So you want data to be your column names Not going to happen. At least not with out-of-the-box tools in SSIS. Tag: SSMS Help
  - 14
    - Reporting Services 2000 Installation Problems. Hello every1. I have been trying to install Reporting Services 2000 on a Windows Server 2003 machine with SQL Server 2000 and Framework 2.x for a couple of days now. When i run Reporting Services setup it tells me that Asp.Net is not installed or is not running, i know this could be caused becouse i have Asp.Net 2.x running instead of 1.x, but what ever i do, changing all IIS settings and website settings so the server uses Asp.Net 1.x the problem still exists, is there any solution besides a reinstallition and installing RS2000 be4 the Framework 2.0 Tag: SSMS Help
  - 15
    - 64-bit Separate Install?? 64bit SQL Server is a separate install, and even IA64 vs. x64 are also separate files . Reporting Services is still 32bit, as well as most of the client tools, including Management Studio. With the installation media. Using Windows Explorer, right click on setup.exe, select [Properties], and click on the [Version] tab. Comments 'should' indicate which version you are inspecting. Tag: SSMS Help
  - 16
    - Is there 1 default instance per OS Server or per SQL Server? There can only be one default instance per OS. Be carefull when installing SQL 2005 on a SQL 2000 machine. It's very easy to upgrade the existing instance without releasing it. Tag: SSMS Help
- Next
  - 1
    - Creating an Windows Rdl on the fly using .net programming Have you looked at Report Builder where you create the data model for the user and they can create their own reports Tag: SSMS Help
  - 2
    - Datamigration to MS CRM Ok, I describe my wishes. I have got one database with some tables. That is my source DB. Now I wanna migrate this data from source DB to a dest. DB. But I need to transform the data cuz the structure of these tables are not the same. For Example: source tables: 1st table: id | name | idlastn 2nd table: id | lastname. (id and idlastn are in relationship) dest tables: id | name | lastname. So i have to move data to dest table with the assumption "Where 1sttable.idlastn = 2ndtable.id" in the source DB. Tag: SSMS Help
  - 3
    - SQL server replication connection problem The engine interrogates the server registrations within the instance (sys.servers) and looks for the name that you are specifying. If it doesn't find this, you'll see this message. You can possibly add the entry into the subscriber using sp_addserver. You could also put an entry in the hosts file pointing at it. The hosts file is located at ..<windows>\system32\drivers\etc. The format is pretty straightforward and documented within the file. I would caution doing this those, because if the IP address of the subscriber changes, you will need to edit the hosts file on the server for replication to be able to find it. Tag: SSMS Help
  - 4
    - Aggregation Types Hello! Can this blog post help you: http://blogs.conchango.com/christianwade/archive/2005/04/07/1252.aspx , HTH Thomas Ivarsson Tag: SSMS Help
  - 5
    - the procedure 'sp_addrolemember' cannot be executed within a transaction As defined in BOL, sp_addolemember cannot be executed in a user-defined transaction ( http://msdn2.microsoft.com/en-us/library/ms187750.aspx , see Remarks section); but I am guessing that your customer didn’t initiated an explicit transaction, therefore most likely there is an implicit transaction ( http://msdn2.microsoft.com/en-us/library/ms187807.aspx ). Other than making sure that there is no transaction active before you try calling sp_addrolemember, I don’t think there is any workaround. Thanks, -Raul Garcia SDE/T SQL Server Engine Tag: SSMS Help
  - 6
    - Get Culture for Multilanguage in Reporting Service I have had a lot of experience on this subject in the past month or so, first I just have a couple of questions regarding your parameters. Are you serializing your report parameters and passing it to the server or are you just sending the client to the SRS If you are sending the parameters from the application you can simply send the language as a parameter to the report and use it in the expression like this: Mynamespace.RSLanguage.MultiLanguage.MyReports.LocalizeRM.GetString(stringIdentifier, Parameters!Language). This was the route I took, because my application has a session variable for the language that is set by the user to override the current cultureUI thread of the application. If this is not the case I would suggest that you use the User!Language, though I have not tried this method. Hope this helps, Nick Tag: SSMS Help
  - 7
    - using case statement to determine Order by field and direction (asc or desc) when using Row_number You have to use the following syntax, Code Snippet Declare @COLTOSORTBY as varchar ( 100 ); Declare @DIR as varchar ( 10 ); Set @COLTOSORTBY = 'id' ; Set @DIR = 'asc' ; with myCTE AS ( Select id , name , xtype , row_number () over ( order by case when ( @DIR = 'ASC' ) then case when @COLTOSORTBY = 'id' then id end end , case when ( @DIR = 'ASC' ) then case when @COLTOSORTBY = 'name' then name end end , case when ( @DIR = 'ASC' ) then case when @COLTOSORTBY = 'xtype' then xtype end end , case when ( @DIR <> 'ASC' ) then case when @COLTOSORTBY = 'id' then id end end desc , case when ( @DIR <> 'ASC' ) then case when @COLTOSORTBY = 'name' then name end end desc , case when ( @DIR <> 'ASC' ) then case when @COLTOSORTBY = 'xtype' then xtype end end desc ) as Rid from Sysobjects ) select * from myCTE Tag: SSMS Help
  - 8
    - Reporting Services 2005 with SQL 2000 Database Any ideas Tag: SSMS Help
  - 9
    - Deleting data bloats log file The deletes are recorded in the Transaction Log, so that in case of needing to rebuild a 'crashed' database, you have a complete record or every action. However, if you are just trying to clean out tables, you 'might' be able to use the TRUNCATE command. TRUNCATE TABLE MyTable If you are just removing part of the data, then you could change the database recovery type to 'Simple', and the log will be truncated as soon as the deletes are complete. Tag: SSMS Help
  - 10
    - Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers" While both servers are running they must have unique hostname and IP. Thus, it would be necessary to create a linked server on the primary for the "standby" server. This would allow the primary to access/push the data to the standby server until such time you need to "promote" the standby to be the primary. To promote the standby, it would be as simple as rename it. The following article should help with that. http://msdn2.microsoft.com/en-us/library/ms143799.aspx Please note that, you can no longer allow to modify system tables in sql2k5. Tag: SSMS Help
  - 11
    - running ssis package with ssis run time compoenents and sql server 2000... spattewar wrote: I have similar concerns. Do you have any links or infromation pages that we can read through and provide it to our DBAs. About what exactly Tag: SSMS Help
  - 12
    - export table to excel using enterprise manager from client manager... there is export task wizard... you can easily achieve this..... mandip Tag: SSMS Help
  - 13
    - CTE behaviour in SQL 2005 I know your post is a few months; however, I figured I'd post a response incase you or anyone else is interested in a workaround. I came across the same problem with CTE (common table expressions) and sorting. this code below may work. I don't have adventureWorks installed so my code may cause errors if used exactly. Debugging shouldn't be a problem: Code Snippet USE AdventureWorks; GO DECLARE @SortExpression Varchar(50) Set @SortExpression = 'SalesPersonID ASC'; DECLARE @sql1 varchar(4000) SET @sql1 = 'SELECT RowNumber, SalesPersonID, NumberOfOrders, MaxDate FROM ( SELECT ROW_NUMBER() OVER(Order by ' + @SortExpression + ') RowNumber, SalesPersonID, COUNT(*), MAX(OrderDate) FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ) tbl1' exec sp_executesql @sql1 If you're using a DataSource in .NET and want to implement paging, sorting, AND filtering (for searching or limiting data) then please read on. sp_executesql also supports bind variables. SQL will will reuse the cached execution plan, this SQL won't recalculate the execution plan each time the page, sort, or filter condition(s) change. This procedure below will do the same as query above, let you page the data (if you have many salesPersonIDs), and will will let you limit which SalesPersonID you're looking at... all with bind variables for faster query execution using sp_executesql. Code Snippet CREATE PROCEDURE [dbo].[usp_getSalesOrders] @SalesPerson int, @startRowIndex int, @maximumRows int, @SortExpression varchar(20) AS BEGIN SET NOCOUNT ON; --if a sortExpression isn't passed in, give it a default IF len(isnull(@SortExpression, '') = 0 set @SortExpression = 'SalesPersonID ASC' DECLARE @sql nvarchar(4000) SET @sql = 'SELECT SalesPersonID, NumberOfOrders, MaxDate FROM ( SELECT ROW_NUMBER() OVER(Order by ' + @SortExpression + ') RowNumber, SalesPersonID, COUNT(*), MAX(OrderDate) FROM Sales.SalesOrderHeader with(nolock) WHERE (SalesPersonID = @SalesPerson OR @SalesPerson IS NULL) GROUP BY SalesPersonID ) t WHERE t.RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1' exec sp_executesql @sql, N'@SalesPerson int,@startRowIndex int,@maximumRows int', @NumOfOrders, @startRowIndex, @maximumRows; END You can further optimize this, and I welcome comments as it'll help to improve my own code. Hope this helps! Nick Tag: SSMS Help
  - 14
    - Dynamically determine Linked Servers Linked servers are listed in the sys.servers table (or sysservers in SQL 2000). Tag: SSMS Help
  - 15
    - SQL Problems Here is the skeleton of how, I'll let you flesh it out... Code Snippet CREATE TABLE #FileListTable -- List of sql Files to run ( RowID int IDENTITY , FileName varchar(100) , PassCount int DEFAULT 0 ) DECLARE @FileToRun varchar(100), @SQLCmd varchar(1000), @bcpCmd varchar(1000), @CurrentRow int, @TotalRows int SELECT @FileToRun = '', @SQLCmd = 'dir c:\Sale\*.* /b ', @CurrentRow = 1 -- Use BULK INSERT to load the FileList INSERT INTO #FileListTable EXECUTE master.dbo.xp_cmdshell @SQLCmd DELETE FROM #FileListTable WHERE ( FolderName IS NULL OR FolderName = 'Output' ) SELECT @TotalRows = count(1) FROM #FileListTable WHILE ( @CurrentRow <= @TotalRows ) BEGIN SELECT @FileToRun = FileName FROM #FileListTable WHERE RowID = @CurrentRow SELECT @BCPCmd = 'BULK INSERT PosMaster FROM c:\Sale\' + @FileToRun + 'WITH ( FirstRow = 1, FieldTerminator = '','', + RowTerminator = ''\n'')' EXECUTE master.dbo.xp_cmdshell @BCPCmd SET @CurrentRow = ( @CurrentRow + 1 ) END Tag: SSMS Help
  - 16
    - Multiple web viewers and AsyncRendering true Solved my own question... private void SetDefaultViewerProperties(ReportViewer rptViewer) { rptViewer.AsyncRendering = true; rptViewer.Height = 200; rptViewer.Width = 700; rptViewer.ShowToolBar = true; rptViewer.ZoomPercent = 100; rptViewer.Visible = true; } It needed height... Tag: SSMS Help

MyoZaw

Hi,

I'm not sure this is the right discussion room that I'm looking for my problem. In fact, the problem is very simple though I don't know how to deal with it. Big Smile

I accidentatly clicked some buttons on my SSMS IDE. And, my IDE seems bad. I uncheck 'Dockable' click box of SSMS's Object Explorer.

And, is there anybody who know how to put that back to the original format

Thanks.

Myo Zaw

Top

Re: SSMS Help

David Frommer

Under the Window Menu, select 'Reset Window Layout'

Top

Re: SSMS Help

MyoZaw

Thanks David,

It did help me out a lot. And, now, my SSMS is in workable condition.

Cheers!!

Myo Zaw

Top