News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud


sqlserver


SQL Server error: "Failed to create AppDomain "master.sys[runtime].224". Exception has been thrown by the target of an invocation"

In a nutshell this is due to an incompatibility between .NET and Windows. The most likely candidate for the cause would be an upgrade to either Windows or .NET. Overall, check the Windows and SQL Server patch levels. The CLR/.NET that is affected is Microsoft.SqlServer.Types. To poke around to see what else might be at risk, issue these queries from master:SELECT * FROM sys.dm_clr_appdomains;SELECT * FROM sys.dm_clr_loaded_assemblie... * FROM sys.assemblies WHERE principal_id <> 4 AND ......

SQL Server: How do I get the size of a database?

use [databaseName]goSELECT database_name = DB_NAME(database_id) , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))FROM sys.master_files WITH(NOWAIT)WHERE database_id = DB_ID() -- for current db GROUP BY database_id ......

SQL Server: Find existing temp tables on a server.

-- query the server to examine all the existing temp tables

select name, object_name(object_id) As ObjName,*
 from tempdb.sys.objects
 where name like '#%'

SQL Server: How do look at the dataypes/lengths of columns in a temp table?

To view that information, there are 2 primary ways:select * from tempdb.INFORMATION_SCHEMA.C... where table_name like '#MyTempTable%'select * from tempdb.sys.columns where object_id = object_id('tempdb..#mytempt... ......

SQL SERVER: How do I find all the tables that have had an update in a database?

This query will bring back the last user update to every table in the database you're connected to.use [dbname]goSELECT last_user_update, t.name FROM sys.dm_db_index_usage_stats us JOIN sys.tables t ON t.object_id = us.object_id WHERE database_id = db_id()and cast(last_user_update as date) >= cast('2018-05-09' as date)order by last_user_update desc ......

SQL Server: How do I pull the ASCII value for each character in a column name?

This is a handy script to cycle through every character in a column to determine what each ascii value is. This is especially useful when a string match isn't matching. Often times, there is a hidden space, etc. DECLARE @counter int = 1;--DECLARE @asciiString varchar(10) = 'AA%#& '; DECLARE @asciiString varchar(100) SELECT @asciiString = [ColumnName] FROM schema.TableName where ColumnName like '%Something%'WHILE @counter <= DATALENGTH(@asciiString) BEGIN SELECT CHAR(ASCII(SUBSTRING(@ascii... ......

SQL Server: Why is it taking so long to take a database offline?

There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s). In the future, use this command:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

SQL Server: Why is it taking so long to take a database offline?

There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s). In the future, use this command:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

SQL Server: How can I get a distinct count(*) with multiple columns?

To get a count(*) of distinct column combinations, do the count(*) over the distinct select statement.Example:SELECT count(*) FROM (SELECT DISTINCT ColumnA, ColumnB, ColumnC FROM YourTable ) x ......

I get this message: profile name is not valid [SQLSTATE 42000] (Error 14607) using sp_send_dbmail. Why?

The most likely reason is that your profile has not been configured, or you are using an incorrect name under the @profile_name parameter. To determine your profile settings, use this query:SELECT [profile_id] ,[name] ,[description] ,[last_mod_datetime] ,[last_mod_user] FROM [msdb].[dbo].[sysmail_profile] If after you have checked the results of this query and you are using the correct profile name, check how you are passing the parameter values.Don't do this:exec msdb.dbo.sp_send_dbmail @subject, ......

SQL Server: How do I start an agent job on a remote server?

This sql script will start an agent job on a remote server. If you're running this as a step in another agent job, keep in mind that the job you are running it from will be determined to be successful, even if the remote job fails--as this is an asynchronous kick off only.declare @returnCode int declare @JobName varchar(300) declare @ServerName varchar(200) declare @query varchar(8000) declare @cmd varchar(8000) set @JobName = 'TheJobNameYouWantToRun' set @ServerName = 'TheRemoteServerWhereTheJobIs' ......

sql server: Why can't I use the xp_cmdshell in SSMS?

You will need to enable the feature first.Follow these steps:EXEC sp_configure 'show advanced options', 1GO-- this updates whatever the currently configured value for advanced optionsRECONFIGUREGO-- Now enable the command shellEXEC sp_configure 'xp_cmdshell', 1GO--update the currently configured value with xp_cmdshell setting update.RECONFIGUREGO ......

SQL Server: Why is it taking so long for SQL Server to take my database OFFLINE?

If you are using the SSMS GUI, you may not be aware that behind the scenes, you are really issuing an ALTER DATABSE command. It is likely that another process (or processes) were accessing the database you want to take offline.If you're a DBA, or have sysadmin privileges, issue an sp_who2 command -- looking for the ALTER DATABSE process logged to you. Kill the process. Once the process has been killed off, issue the ALTER DATABSE command yourself.To take the database offline:USE masterGOALTER DATABASE ......

How do I set a trace in SQL Server?

Think of this as a lightweight alternative to SQL profiler. Under the hood of SQL profiler, there exists SQL Trace -- which provides a collection of stored procedures to generate trace info. Cut and paste the t-script below to see how it works.-- Pay attention to what the server settings for traces look like first:select * from sys.tracesgo-- you are looking to make sure there isn't already a trace file set somewhere-- In any case you will need to create a new trace, make sure the @tracefile doesn't ......

SQL Server 2012: How do I set "Edit top" rows to a different number?

right click the results or your "Edit Top 999 Rows" query in SSMS. There's an option called: "Pane -> SQL" If you look at it, this show you the sql--you can edit that and narrow down the rows you are interested in editing even further.

 

 

How do I remove a named instance of sql server 2008 on the same mssql server?

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans... mso-ascii-font-family:Calibri; ......

How do I create and access another sql server db using linked server in SQL Server?

For a full explanation and step-by-step guide to setup a linked server through Sql Management Studio (SMS), check out this reference: http://www.databasejournal.... Here it is in a nutshell: If you are setting up a linked server for another sql server 2005/2008 box, just remember to (1) name the Linked server the same name as its network name, (2) select and provide under the security option ,"Be made ......

How do I get a listing of all DB instances and table names where a column name occurs in MS SQL Server?

I've been there! You are at a client site and you know you need certain data elements but aren't certain how many databases on a given server ...or ... which tables the data elements you are interested in might appear. Here's a single statement when executed from any SMS query connection that will get you there: EXEC sp_msforeachdb ' DECLARE @pattern nvarchar(100) SET @pattern = ''%elementName%'' IF EXISTS(SELECT 1 FROM [?].information_schema.columns WHERE column_name LIKE @pattern) BEGIN SELECT ......

How do I read the SQL for a View in T-SQL?

The easiest way in MS SQL 2005 and up is to use the SQL Management Studio, go to Views, highlight the name of the view you are interested in, right click, select Script View as/Create to/Clipboard. open up notepad or your prefered editor and paste the contents of your clipboard. The SQL used to create the view should show up in your editor. If this doesn't work, or if you are working with older versions of SQL Server, look at the system objects as follows: use [YouDatabseInstanceName] go select * ......

How do I Delete a View? How do I Create a View?

Before I create views, I generally work out what I want to retrieve in my SELECT statement ahead of time so I'll just have to cut and paste the query. The example below is done in T-SQL/Sybase format, however for Oracle and MySQL, just place a semi-colon ';' at the end of your statement and remove the 'GO' command. To drop (delete) an existing view: DROP VIEW vw_rpt_metroBestCustomers GO To create a view: CREATE VIEW vw_rpt_metroBestCustomers ( CustomerName, OfficeNum, City, StateOrProv, Country, ......

In SQL Server, How Do I List all the Constraints by Table or by Column Name?

This one will order the contraints by table: Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name] From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab On Tab.[ID] = Sysobjects.[Parent_Obj] Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID] order by Tab.[Name] This one will order the contraints by column: Select ......

How do I find all instances of a column name in my SQL Server database?

The objects that help you find this info in SQL Server are in sys.tables and sys.columns SELECT tbl.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, col.name AS column_name FROM sys.tables AS tbl INNER JOIN sys.columns col ON tbl.OBJECT_ID = col.OBJECT_ID WHERE col.name LIKE '%YourColumnNameHere%' ORDER BY schema_name, table_name; This will bring back every table that has a reference to '%YourColumnNameHere%' ......

How do I Debug my SQL Server Stored Procedures?

If you don't have the Visual Studio IDE to work with, you can go the old fashioned route with issuing PRINT statements. If you do have the Visual Studio IDE available, execute the following steps: open visual studio navigate to server explorer create/open a connection to your database right click on the stored proc you want to work with and choose "Step into stored procedure" For more details, or for a more visual example with the instructions, navigate to this url: http://www.dotnetfunda.com/... ......

How do I Fix SQL Server error: Order by items must appear in the select list if Select distinct is specified.

There's more than one reason why you may receive this error, but the most common reason is that your order by statement column list doesn't correlate with your intended column-to-retrieve list when you happen to be using DISTINCT. This is usually easy to spot. A more obscure reason may be that you are using a function around one of the selected columns --but omitting to use the same function around the same selected column name in the order by statement. Here's an example: select distinct upper(columnA) ......

How do I move a table from one schema to another in SQL Server?

This should work for SQL Server 2005/2008-- ALTER SCHEMA SchemaNameForTarget TRANSFER SchemaNameOfSource.MyTableName example: ALTER SCHEMA RiskEvaluation TRANSFER dbo.ConvertibleDebenture ......

How do I create an Index that is NOT the primary on my tables?

To create a index on a single column, do this:

CREATE INDEX  IDX_FERC ON FERC(FercName)

In some cases, you may have a need to combine columns to hasten known unique combinations. This is knonw as a composite key. An example:

CREATE INDEX IDX_OperatingCompanyFERC ON FERC(OpCompanyID,FercName)


How do I convert an integer to a string in SQL Server?


If you want to reformat data, use the function convert as shown:

select convert(varchar, cast(18 as integer)) as 'a number'

SQL Server: Why do I get "multi-part identifier could not be bound" ?

Let's say you've developed a stored procedure that assigns a count value to a variable which looks like this: SELECT @HasDefaultShipTo = count(*) FROM StakeHolderLoc_Assoc WHERE assoc.StakeHldrID = @StkhldrID When you execute to compile you will see the error: Msg 4104, Level 16, State 1, Procedure MetroDevETL_1, Line 80 The multi-part identifier "assoc.StakeHldrID" could not be bound. The reason this error appears is because you have forgotten to associate/bind the table to "assoc". The corrected ......

How do I select a value from a table into a variable in SQL Server?

Here's an example...

DECLARE @LocID

SELECT @LocID = LocationID
 FROM Locations
  WHERE LocationName = 'WAREHOUSE X'

Does SQL server have an encryption function?

Yes. The functions are PWENCRYPT and PWCOMPARE. This basically allows the ability to encrypt a value on an insert/update and offer a comparison of the value on a select. There isn't a decryption function available. Here are a few T-SQL statements to illustrate how pwencrypt/pwcompare work: create table #MetroTest ( UserLogIn varchar(10), UserPass nvarchar(256) ) insert #MetroTest (UserLogIn,UserPass) values ( 'MaryMary', PWDENCRYPT('QuiteContrary')) select UserLogIn, password = 'QuiteContrary', PWDCOMPARE('QuiteContrary', ......

Why am I getting the error: SQL Network Interfaces: The Local Security Authority cannot be contacted.[SQL Native Client]Cannot generate SSPI context

The full error message looks like this: [SNAC] “[SQL Native Client]SQL Network Interfaces: The Local Security Authority cannot be contacted.[SQL Native Client]Cannot generate SSPI context” [MDAC] “Cannot generate SSPI context”; [.Net1.0/2.0]” Failed System.Data.SqlClient.SqlEx... Cannot generate SSPI context” When this message occurs--especially when the same access 20 minutes ago worked, chances are you've logged off of your primary network. An example would be that at work you were using an ......

Can I insert multiple rows with a single insert statement?

Yes! .....(Now, we're not talking about bulk inserts--which is a completely different task... we're just talking about a small number of rows that need to get inserted into a relatively static table).. Typically folks do something like this to quickly add rows into a reference table: INSERT INTO ReasonType (DisplayName,Description) VALUES ('Delay','Reason for a project delay') GO INSERT into ReasonType (DisplayName,Description) VALUES ('Cancellation','Reaon for a project cancellation') GO ...but ......

Is there a "DUAL" database equivalent in SQL Server?

There isn't a "DUAL" exactly, but you can test things out in SQL Server in a similar way for example in Oracle to get today's date/time you would type:

SQL> SELECT sysdate from DUAL;

To do the same thing in MS SQL Server type:

SELECT getdate()

The Northwind DB is missing! What do I do?

As long as you (or your colleagues) don't have any serious test data you've been working on that needs recovering, it's pretty simple to generate a fresh instance! First, search for the SQL Server script that builds the demo database under \Mssql\Install. The name of the script is called: instnwnd.sql . If the script isn't found there, an alternative place to look is under the Visual Studio SDK path(s), for example: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Samples\Setup . If the script ......