Geeks With Blogs

News Copyright © 2008-2013 Paula DiTallo
Ask Paula! ...bringing you notes from the field... SQL sample queries, stored procedures, SQL server, mySQL, Oracle, DB2, Cache related issues
Sql Server: Orphaned user name; Can't login, User name disappeared from my database
This usually happens when you restore a database. For example, you restore a copy of production database X to your QA server. In essence, you have overwritten the user info of that specific database with what exists in production. This creates an orphan user -- where you have no login associated with a user in that database on a server that once associated that user with the old copy of the database.Here is what to do fix the issue:1. Validate that what you think is the problem is the problem. Do ......

Posted On Monday, June 30, 2014 3:32 PM

SQL Server: Restoring a backup generates this error: Operating system error 5(Access is denied.).
There are several reasons for this. The most common reason is that the account you are using while logged into mssql through ssms does not have access to the drive definition you are using as your source. Network drives are a good example of an access conflict.Assuming you have access to the drive, try redirecting the network drive to a local drive letter via xp_cmdshell (Note: be sure use of xp_cmdshell is enabled) .exec master..xp_cmdshell 'net use Z: "\\BackupServerXX\<share... ......

Posted On Thursday, June 26, 2014 3:32 PM

SQL Server: How do I generate the table schema and populate it with inserts in a script?
In SSMS, there's a Generate Script utility (read: only available under version 2008 and up) . Here are the steps you would need to take to make use of the utility: Right click on the database you're interested in and go to Tasks -> Generate ScriptsSelect the tables and/or any other objects you'd like in order to get them into the script.Navigate to Set scripting options. Click on Advanced.Under the General category, navigate to Type of data to scriptSelect the Schema and Data option to get the ......

Posted On Tuesday, May 20, 2014 5:46 PM

Sql Server: Select Count(*) throws the error: Arithmetic overflow error converting expression to data type int
Try using count_big(*) instead-- this is defined as bigint instead of int.In this example, I am looking for how many 100s of millions of rows exist by product type where each row is roughly 300 bytes wide to ultimately obtain how many gigabytes of data I need to provision.select x.ProductType, count_big(*) as ProdCount, (count_big(*)*300) as TotalBytes, ((count_big(*)*300)/1073741... as Gigabytes from fdwintegration.etl.fuelpric... x inner join fdwintegration.etl.FuelProd... y on x.ProductIndicator ......

Posted On Monday, April 21, 2014 5:01 PM

How do I save frequently used SQL Statements in SQL Server?
One convenient way is to make use of the template feature (aka: Template Explorere) in SSMS.

1. Navigate to the View menu in SSMS
2. Select Template Explorer->right click on SQL Server Tempaltes.
3. Click New and create folder; use to add/edit templates.


Posted On Monday, October 7, 2013 12:27 PM

Which DMVs are most helpful? Is there a list of them?
As with most questions asked about monitoring/assessing/admini... MS SQL Server installations, the answer is "it depends".There are many other blogs/resources/etc. online that can get into the specifics--think of this mini-blog as a snake-bit kit! :) Overall, to get to a list of objects that fall under the DMV (Dynamic Management Views) category, type this:-- This will show you the views and the functions of all dmv objectsSELECT name, type, type_descFROM sys.system_objects WHERE name LIKE 'dm_%'-- ......

Posted On Friday, September 27, 2013 7:23 PM

How can I tell when a stored proc (or function) in sql server was last modified?
You're really looking of the modify_date in the sys.objects table where the type is either a stored proc or a function. This works on a database by database level -- to elevate this search to the server level, you'll have to use the loop sproc sp_msforeachdb SELECT name, create_date, modify_date FROM sys.objectsWHERE type = 'P' -- change 'P' to 'FN' if you're looking for a function and modify_date between cast('2013-09-10' as date) and cast('2013-09-20' as date) ......

Posted On Friday, September 20, 2013 5:22 PM

How do I change the value of a column in a CASE statement and eliminate NULLs?
In t-sql/mssql, if you have a known NULL value you can issue a statement like:SELECT ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDateFROM DrviersThe ISNULL function is replacing any nulls found in the BirthDate column to a default value of 1/1/1970.In other situations, you may not have just NULL values, but other possibilities as well. SELECT ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate, CASE WHEN BusinessPhone IS NULL OR BusinessPhone = '' THEN 'Unknown' ELSE BusinessPhone END as BusinessPhoneFROM ......

Posted On Thursday, September 19, 2013 9:57 AM

How do I do a SELECT TOP in SQL for DB2 on an iSeries (Power7) ?
This answer depends on what version of SQL you're running. A FETCH clause will work for all. Here's an example:SELECT FLBILC, FLDATE, FLTCK#, FLCST# FROM PRDMETRO.FLHISTAF WHERE FLBILC = 'CMD' ORDER BY FLBILC FETCH FIRST 10 ROW ONLY;The equivalent in T-SQL would be:SELECT TOP 10 FLBILC, FLDATE, FLTCK#, FLCST# FROM PRDMETRO.FLHISTAF WHERE FLBILC = 'CMD'; ......

Posted On Wednesday, September 11, 2013 5:19 PM

SQL Server Management Studio: Why do I get "Saving changes is not permitted" ?
This happens when you're in the SSMS gui making changes to the table that either affect the behavior of an index, cause existing data to be truncated/nulled, etc. This can also happen you try to change the order of columns, or change datatypes. If your credentials allow it, you can get past this message in order to truly shoot yourself in the foot! :)Navigate to Tools --> OptionsExpand DesingersClick on Table and Database DesignersCheck the box Prevent Saving changes that require table to be re-created ......

Posted On Wednesday, August 28, 2013 3:00 PM

Why can't I connect to SSIS through management studio?
The microsoft integration service is secure by default. The DBA has to grant access to the service in order for it to connect successfully through ssms. To grant access to the Integration Services service Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node. Right-click Microsoft SQL Server Integration Services 11.0, and then click ......

Posted On Monday, August 26, 2013 10:44 AM

SQL Server: I need to get to a remote server and management studio is broken. How can I login without ssms?
Try using osql . It can be a life saver. Here's an example of the most common usage:osql -E -S myMSSQLServerName /* This uses windows authentication to connect to a server */osql -? /* lists all the switches available to use with osql */Once you see the SQL prompt (1> ) , you can interact with the server.1> USE myDatabaseName2> GO3> SELECT col1, col24> FROM myTable5> GO6> 7> EXIT Here's a link or two to get you started. http://msdn.microsoft.com/e... ......

Posted On Wednesday, March 20, 2013 8:06 PM

SQL Server: Is there a query I can use that generates similar output to Activity Monitor?
Yes! This query will return very similar information to what activity monitor returns--including the text of the query the process is running (when applicable). -- Query version of Activity Monitor SELECT SessionId = s.session_id, UserProcess = CONVERT(CHAR(1), s.is_user_process), LoginInfo = s.login_name, DbInstance = ISNULL(db_name(r.database_id), N''), TaskState = ISNULL(t.task_state, N''), Command = ISNULL(r.command, N''), App = ISNULL(s.program_name, N''), WaitTime_ms = ISNULL(w.wait_duration_ms, ......

Posted On Wednesday, March 20, 2013 7:13 PM

SQL Server Agent:: I Can't Edit a Job Step because of the error "Creating an instance of the COM component with CLSID from the IClassFactory failed"
The immediate workaround to this problem is to open up a new SSMS session, then navigate to the SQL Agent Job you want to edit. At that point, you will be able to make the edit and save the changes.For more information on why this error occurs and the possible release(s) fixes, check out this url:http://connect.microsof... ......

Posted On Monday, March 18, 2013 7:05 PM

SSIS: I get the error The column data for column "Column x" overflowed the disk I/O buffer
If you are reading a flat file, you are missing a delimiter somewhere and SSIS is trying to parse for the next value--so it thows an i/o buffer error. Look in the file for the missing delimiter. If the file is too big for that, try chopping up the file up into smaller versions until you find the line with the missing delimiter.

Posted On Friday, March 15, 2013 5:34 PM

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.

 

 

Posted On Tuesday, March 12, 2013 9:27 PM

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; ......

Posted On Thursday, November 3, 2011 2:28 AM

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 ......

Posted On Monday, October 31, 2011 2:42 PM

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 ......

Posted On Wednesday, October 26, 2011 3:46 PM

How do I get access to DB2 from my .NET applications?
You have at least 2 choices: Source 1: SQL Server 2008 Feature Pack http://www.microsoft.com/do... ... this includes Microsoft OLEDB Provider for DB2 Source 2: Available from IBM, is the IBM I Access pack http://www-03.ibm.com/syste... Remember, whichever one you use, you will need to specifiy which libraries under the detail/advance settings in ODBC ......

Posted On Tuesday, September 13, 2011 2:08 PM

SQL Server: UPDATE Statement Basics
The last thing anyone wants to do is mess up an UPDATE statement! Here's a quick refresher for those of you using SQL Server: Simplest case (a single row, in a single table with a single known value in a column): UPDATE YourTableName SET Column1 = ‘NewValue’ WHERE SameOrOtherColumn = ‘OldValue’ Most common case (multiple rows, using another table as the source data with multiple columns): UPDATE YourTableName SET ColumnX = OtherTable.Column1, ColumnY = OtherTable.Column2 FROM OtherTable WHERE YourTableName.Column1 ......

Posted On Monday, August 29, 2011 3:30 PM

SQL Server: How Can I search for a specific text value in all the stored procedures?
To quickly look through your stored procedure objects for a text value in a database instance, do the following: use [Metro] go SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%whatyouarelookingfor%' AND ROUTINE_TYPE = 'PROCEDURE' Now, this is no replacement for a configuration management repository, but it will do in a pinch. BTW, remember this sql statement does NOT transcend all databases on the server, just a single db instance ......

Posted On Monday, August 29, 2011 3:01 PM

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 ......

Posted On Tuesday, January 13, 2009 8:54 AM

How do I restore a SQL Server database from a backup *.bak file?
If you aren't sure what is actually in the backup file, or even if you think you are sure--execute this statement from a fresh query window: RESTORE FILELISTONLY FROM DISK = 'X:\MDD\Clients\Lg\H4\H4L.bak' The information about the mdf/ldf files will show up looking like this: In this example, I was restoring a database to a different server with different filegroups, directory setups, etc.--so I had to 'MOVE' the location paths to represent the drives I had available. If I were restoring to the same ......

Posted On Monday, July 11, 2011 7:34 PM

How Do I Change a Database Instance Name in SQL Server?
Ten minutes ago I created a scratch db instance with a name I mistyped. Not good! Fortunately, I have admin privileges so I can change it in my lab environment. If you're an admin AND no other users are accessing the DB instance, here's what you can do: USE master go EXEC sp_renamedb 'MetroWrongName', 'MetroRightName' GO If someone is using the instance, there is a workaround. Do this: USE master go -- flag the instance for single user only EXEC sp_dboption 'MetroWrongName','Single User', True GO ......

Posted On Friday, May 27, 2011 4:38 PM

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 * ......

Posted On Wednesday, May 4, 2011 12:41 PM

How do I change the owner of my table in SQL Server?
Go to the ms sql server database instance that has the table object and open a new query window. Execute the system stored proc called sp_changeobjectowner. The 2 parameters it needs to run are: (1) the current owner+object name and (2) the target owner. Take a look at the example below: exec sp_changeobjectowner 'METRO\paula.EmployeeContact', 'dbo' When the stored proc is completed, refresh the instance. The table will then be owned by dbo. In this example, the table would display as dbo.EmployeeContact ......

Posted On Wednesday, August 27, 2008 10:40 AM

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, ......

Posted On Tuesday, February 22, 2011 5:01 PM

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 ......

Posted On Friday, November 5, 2010 11:36 AM

Why does "use db" with a hyphen throw "Incorrect syntax near '-'" ?
I think this SQL Server x.x oddity goes back to the old Sybase 4 days! If you want to use a database name that's been dubbed something like 'paula-objects' and type out: use paula-objects then execute the request, you'll get the error message: Incorrect syntax near '-' just type this instead: use [paula-objects] when you execute the request, the syntax error will clear up ......

Posted On Thursday, July 31, 2008 9:26 AM

Does SQL Server have an "ALL_TAB_COLUMNS" feature?
Yes! For those Oracle X.Xers finding themselves working in the SQL Server 2005/2008 world these days, you'll be happy to know all you need to do is: 1. Open up a new execute query window. 2. Type the following: use FavoriteDbInstance SELECT table_name,column_name FROM information_schema.columns WHERE column_name LIKE '%whateverYouWant%' That's it!:-) Your table names with the column name you are searching for should show up ......

Posted On Thursday, July 31, 2008 9:10 AM

How do I list every table and every column in the table in SQL Server?
The sys.tables and sys.columns objects will return this information for you. The following SQL statement will bring bring back all the colunns in all the tables ordered by the table name then the column name. 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 ORDER BY schema_name, table_name; ......

Posted On Sunday, October 10, 2010 10:32 PM

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%' ......

Posted On Sunday, October 10, 2010 10:27 PM

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/... ......

Posted On Wednesday, September 8, 2010 7:16 PM

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) ......

Posted On Friday, May 14, 2010 3:26 PM

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 ......

Posted On Monday, November 23, 2009 6:04 AM

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'

Posted On Friday, August 7, 2009 5:21 AM

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 ......

Posted On Thursday, August 6, 2009 4:13 PM

A 'Get it Done Now!' Approach for transforming SQL Server Data to XML Data
First, for all of the MS SQL Server database purists out there that will complain vehemently against this method of extraction/formatting because of the special characters (SEE: http://www.webmonkey.com/re... that may (will likely) surface and need to be escaped, or that there's a better way to do this such as utilizing the XML Schema Collection/XML Explicit features available with SQL Server, etc. -- I recognize your concern, however, you'll have to admit, this is still (low-tech/mid-90's ......

Posted On Friday, May 29, 2009 11:36 AM

SQL Update Statement - using SubSelect
This is a simple, but helpful example of changing a boolean or bit flag in a table to 'True' when the condition in the subSelect is met. In this context, the SQL statement finds all the cases where a sales agent's email exists in the Metro_SalesForce table and exists in the related table Metro_SalesForceMult. The Metro_SalesForceMult table contains the USA state assignments only for sales agents representing more than a single state. UPDATE Metro_SalesForce SET multipleStates = 'True' WHERE Metro_SalesForce.email ......

Posted On Friday, May 29, 2009 10:34 AM

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'

Posted On Thursday, March 26, 2009 7:13 AM

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', ......

Posted On Wednesday, March 11, 2009 9:38 AM

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 ......

Posted On Tuesday, March 11, 2008 11:17 AM

Copyright © Paula DiTallo | Powered by: GeeksWithBlogs.net | Join free