SQL Server 2008

SQL Server
T-SQL Questions and Examples
Lessons Deserved to Be Recorded and Studied

SQL DB2: Concatenate TEXT from multiple records

2017/06/30
By
Modified: 2017/06/25
Concatenation Road in Winter

    Scenario   Here is a recipe on how to concatenate a field from multiple records into one field. There are two pre-conditions for this method to work: First. Your source file needs to have an equivalent of a line number field.  This should be a numeric field indicating the sequence in which to concatenate the records.  This field doesn’t not need to be  consecutive. Second. The difference between lowest and highest line numbers should be a reasonably low number.  In this example the max difference is 11, and it means that at the very maximum we would need to concatenate 11 text fields together.   Step One Generate a driver file with MIN…

Read more »


Update Field Based on a Field in Another Table

2017/06/02
By
Modified: 2017/04/30

This article contains 3 examples: - T-SQL; – MS Access; – AS/400.   This example shows a standard T-SQL syntax tested on MS SQL 2005 server: update P set P.CertMemberLevelID = Z.CertLevelTo from   UPM_MemberPermissionMask P INNER JOIN zzzCertRegistryUpdate Z ON P.MemberID = Z. Cert2ID Here we are updating one filed CertMemberLevelID in table UPM_MemberPermissionMask based on field Cert2ID in table zzzCertRegistryUpdate. This next example was generated using MS Access 2007 syntax: UPDATE CRPDTA_F1201 INNER JOIN CatCode7 ON CRPDTA_F1201.FANUMB = CatCode7.XXNUMB SET CRPDTA_F1201.FAFA7 = [CatCode7].[XXFA7] A field FAFA7 in CRPDTA_F1201 is being updated. Table  CatCode7 contains 2 fields: – ID Link field XXNUMB and – Update values field XXFA7 SQL statement JOINs two…

Read more »

How to Get SQL Table Field Structure?

2017/05/15
By
Modified: 2017/04/30
SQL-2008-R2

Sometimes you need to display or print a SQL table structure. This article provides a SQL statement that allows you to do that. We are using MS SQL 2008 Studio interface.

Read more »

The EXECUTE permission was denied on the object ‘aspnet_CheckSchemaVersion’, database ‘YourDB’, schema ‘dbo’

2017/05/09
By
Modified: 2017/04/30

ASP.NET page returns this erorr: The EXECUTE permission was denied on the object ‘aspnet_CheckSchemaVersion’, database ‘YourDB’, schema ‘dbo’. (1) First try runing this command/wizard: %WinDir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe   (2) If this doesn’t work, try this query:  -- Add user ASPNET to DB role USE YourDB GO sp_addrolemember 'aspnet_Membership_FullAccess', 'ASPNET' These steps should resolve your issue.

Read more »

Add a FILESTREAM feature to an existing SQL D/B

2017/05/07
By
Modified: 2017/04/30
SQL 2008 SP2 FILESTREAM Problem

SQL Server 2008 allows to store binary data, if Database has a FILESTREAM feature

Read more »

Moving Old ASP Sites to 2008 Server, IIS 7, and SQL 2008

2017/04/13
By
Modified: 2017/03/03
Moving Old ASP Sites to 2008 Server, IIS 7, and SQL 2008

It was time to move many sites from W2K3 Server (Windows 2003 Server) and SQL 2000 Server to a new W2K8 Server (Windows 2008 Server) and SQL 2008 Server.  And if I ever have to do it again, I will check with these notes. The greatest secret in technical life (and may be wider) is to change before you have to.  Anticipate and change before circumstances force you.  For a long time I was planning this migration, but I didn’t expect to go through so many hurdles and error messages.  Luckily, I found solutions that seem logical and clean.  So, let’s go into step-by-step details. COPY DATABASE AND SITES – Backup…

Read more »

How to Compare SQL SmallDateTime Fields for Equality

2017/04/01
By
Modified: 2017/03/03
MS SQL Server 2008 R2

It is meaningless to compare for equality (==) two fields of type SmallDateTime, because they contain time component and they are hardly ever the same. Here are some interesting ways around this problem to check, if two SmallDateTime fields have the same date component.

Read more »

Allow to Pass Optional Parameters to a Stored Procedure

2017/03/28
By
Modified: 2017/03/03
Mount Carrigain NH 1427m

This article shows how to use string parameters to make selection on Boolean fields. What is it you need to select "true", "false" of "ALL" records. How WHERE statement will look like in that case?

Read more »

SQL – Count combinations of values in several columns

2017/03/24
By
Modified: 2017/03/23
Life is a Passage

You have a large table (in my case it has 8,697,647 records) with an Asset Number and a Business Unit, and you would like to know if any asset has record with multiple Business Units. You can count records of unique Asset/BU combinations, but this is not what you want: SELECT FLNUMB, FLMCU, COUNT(FLAID) AS COUNT FROM JDE_CRP.CRPDTA.F1202 GROUP BY FLNUMB, FLMCU ORDER BY FLNUMB, FLMCU This query will return results similar to these: FLNUMB FLMCU COUNT 84865 12000 66 84866 12000 66 84867 12000 75 84867 12704 18 84867 12705 28 84868 12000 75 84868 12715 22 84868 12717 32 84869 12000 66 84870 12000 75 84870 12535 40 84871…

Read more »

How to Re-Install SQL Server 2008 Evaluation

2017/03/20
By
Modified: 2017/03/03

You have 180 days to evaluate.  You can check number of days till expiration:   open your SQL Server Management Studio and widen first line in Help — About box. What if your trial is about to expire, and you need just a few more days to evaluate? You can switch to a free Express edition, or you can follow this cumbersome procedure.   =01= Un-Install SQL Server 2008 Drop all your custom databases Run your Maintenance Plan one last time Save all your maintenance plans (this step was not tested) Go to Control Panel – Programs and Features Uninstall Microsoft SQL Server 2008 – Remove Check Rules – Next Select Features –…

Read more »