SQL Server

SQL server -T-SQL questions and examples.
Lessons once learned and now recorded.

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 »