-
You can browse available SQL Instances on network by choosing " <Browse for More..> " from Server Name drop-down list in " Connect to Server " dialog box in Management Studio: As you can see from the screen shot, all of my instances are visible on the network. If I want to hide...
Posted to
Vishal Gajjar's Blog
by
Vishal Gajjar
on
08-23-2011
Filed under:
Filed under: SQL FAQ, SQLServer, SQL, Tips & Tricks, Database, Sql And Me, Undocumented, Management Studio, SQLServer 2008 R2, scripts, SqlAndMe, Vishal Gajjar, Query, Vishal, Sql&Me, SQL 2008 R2, SQL Server, Extended procedures, sql browser hide, undocumented procedures, change registry value to hide instance, HideInstance, xp_instance_regwrite, hide instance, hide sql instance, xp_instance_regread
-
sys.dm_db_persisted_sku_features lists all features which are utilized by the database. Features specific to Enterprise/Developer edition are: - Compression, - Partitioning, - TDE and CDC These features are available only on Enterprise/Developer editions of SQL Server. You cannot attach or restore databases...
Posted to
Vishal Gajjar's Blog
by
Vishal Gajjar
on
08-22-2011
Filed under:
Filed under: SQL FAQ, SQLServer, SQL, Tips & Tricks, Database, Sql And Me, SQLServer 2008 R2, Partitioning, Management Views and Functions, Backup, SQLServer 2008, Backup & Recovery
-
Continuing from my last post on SSIS Control flow task in which we talk lot about For Each Loop Container: MSBI # 23 – SSIS # 10 – Control Flow Tasks # 5 – For Each Loop Container Task « (B)usiness (I)ntelligence Mentalist Now lets learn each and every aspects of sequence container task We are covering...
-
I have publised my posts related to database backup history, restore history and also cleanup for the same. For the database backup we are performing using the maintenance plans or wizard. We cal also performing this database activity by scripts automatically same as maintenance plans. Visit full database...
Posted to
sqlideas
by
Paresh Prajapati
on
08-20-2011
Filed under:
Filed under: sql, tsql, sql server, ms sql, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, SQL Scripts, backup
-
PERCENT_RANK() returns the position of a row within the result set. In contrast to RANK() function, PERCENT_RANK() ranks rows between 0 and 1, both inclusive. Computation formula used by PERCENT_RANK(): (RANK() – 1) / (Number of Rows – 1) where, RANK() is the rank of the row within the result...
-
Problem: When we run in the issue of something wrong or incorrect with stored procedure and suppose the stored procedure has 1000 lines long and very complex code for custom logic inside it, what you do? Then for above such issue, we put the SQL trace and get the procedure parameters and manually run...
Posted to
sqlideas
by
Paresh Prajapati
on
08-17-2011
Filed under:
Filed under: sql, sql server 2005, tsql, sql server, ms sql, ms sql server, variable, SET, sql server denali, #SQL Server, mssql, #sql, sql server 2011, database, sql server general, SQL Scripts, SQL new features, debug
-
CUME_DIST() calculates relative position of a value relative to a group of values. The value returned by CUME_DIST() is > 0 and <= 1 , which represents percentage of number of rows with value less than (for ascending order) or equal to current row. For example, consider below data: Year Month Amount...
-
ROWS/RANGE allows you to change the frame size within a partition. Valid arguments for ROWS are: UNBOUNDED PRECEDING Starts the window at first row of the partition UNBOUNDED FOLLOWING Ends the window at last row of the partition CURRENT ROW Starts or Ends the window at current row N PRECEDING Starts...
-
Because of some security policies we need to require our code to be safe from Users who are going to use SQL Server database and objects of them and some outside threat. We have different different users to access the database objects or used for application. We have so many ways to encrypt data, but...
Posted to
sqlideas
by
Paresh Prajapati
on
08-15-2011
Filed under:
Filed under: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, sql server denali, #SQL Server, mssql, #sql, sql server 2011, database, sql server general, SQL Scripts, encryption
-
SSMS allows you copy query results with column headers. However, if you copy results by choosing "Copy" or Ctrl + C, by default it does not copy headers. Management Studio 2008 allows you to copy data with headers by choosing " Copy with Headers " from Edit or Context Menu (or Ctrl...
-
xp_servicecontrol is an undocumented extended stored procedure, which can be used to start/stop or check the state of Windows services. It has the following syntax: xp_servicecontrol @Action = N'Action' @ServiceName = N'Service Name' Arguments: @Action can be any of the following: start...
-
FIRST_VALUE() and LAST_VALUE() are new analytic function introduced in SQL Server "Denali". As the name suggests FIRST_VALUE() returns first value in an ordered set of values, and LAST_VALUE() returns the last value from an ordered set of values. For example, SELECT EmployeeID, FirstName, MiddleName...
Posted to
Vishal Gajjar's Blog
by
Vishal Gajjar
on
08-12-2011
Filed under:
Filed under: SQL FAQ, SQLServer, SQL, SQL "Denali", TSQL, Database, Sql And Me, Analytic Functions, BRH, Tricks, Tips &
-
Finding duplicate rows in a table can be done easily by using ROW_NUMBER() function. You can number each similar rows by using PARTITION BY clause. For example, consider the below table: EmployeeID FirstName MiddleName LastName ———– ———- ———–...
Posted to
Vishal Gajjar's Blog
by
Vishal Gajjar
on
08-11-2011
Filed under:
Filed under: SQL FAQ, SQLServer, SQL, TSQL, Database, Sql And Me, Common Table Expressions, System Functions, BRH, Tricks, Tips &
-
I ran through one issue while working in one of the stored procedure and using SELECT clause while variable assignment. And i suffered this issue in live production environment and due to this it was killing the Server CPU. Finally i got the issue and resolved it. The issue was It did not take care of...
Posted to
sqlideas
by
Paresh Prajapati
on
08-10-2011
Filed under:
Filed under: sql, sql server 2008, sql server 2005, tsql, sql server, ms sql, ms sql server, t-sql, SET, #SQL Server, sql server 2011, database, sql server general, SQL Scripts, sql errors, SQL Server Problems
-
You can use sys.dm_exec_requests DMV to list all requests. This view also contains a hash map of the SQL text – sql_handle, which can be passed to DMF sys.dm_exec_sql_text to get the query text: SELECT REQUEST.session_id, REQUEST.start_time, QUERY.text FROM sys.dm_exec_requests REQUEST CROSS APPLY...