DataBase Driven Blender: Creating Objects in Blender from DB Data

Introduction

Currently I am working in a project team installing a particle accelerator. Like with every other complex machinery this involves a large amount of cabling works to connect all kinds of devices with all kinds of cables and cable types. These cable types can roughly be grouped according to their function: power cables, Ethernet cables, interlock cables, etc. Quite often these different groups of cables are laid by different teams or even different companies along the same cable paths in a short period of time. Hence, it is crucial that these teams work coordinately and according to a transparen plan so they do not interfere with each other during their works.
As I am a big fan of 3D graphics I wanted to create 3D visualizations of the cable paths assigned to the various cable types and because I am a big fan of Blender, I wanted to create the required images there. The output of my efforts look like this:

What you see here is a look into the "cave" (a room created by large concrete blocks shielding radiation) where the accelerator will be installed. The 3D-Model data of the concrete blocks and the cable support structures is coming from CATIA data provided by our mechanical engineering deprtments - another article of mine describes the export from CATIA and import into Blender. The grating-like structures carried by orange support structures are cable trays where the cables are to be laid, the colored tube structures are the cable paths for the different able groups. The dimension along the horizontal axis is roughly 20m.

Environment

Initially, the data on cable trays and routes came from 2D Autocad drawings provided by engineers. Based on these drawings I created a MS SQL Server database model holding data on cable routes, including the x,y,z node coordinates of the cable paths and the definition of path segments which can be added to a end-to-end cable path from start device to an end device.
The database model has the following structure:

This database structure can be accessed via a view combining the data:

SELECT        dbo.CableRoutePathIDs.RoutePathID, dbo.CableRoutePaths.SegmentSortOrder, dbo.CableRoutePaths.RouteSegmentID, dbo.CableRoutePathIDs.RoutePathUser,                        dbo.CableRoutePathIDs.Description, dbo.CableRouteSegments.Start_NodeID,
                         dbo.CableRouteNodes.GsiQuadrantX * 720 + dbo.CableRouteNodes.DistToGsiQuadrantX_cm AS StartX,
                         dbo.CableRouteNodes.GsiQuadrantY * 720 + dbo.CableRouteNodes.DistToGsiQuadrantY_cm AS StartY, dbo.CableRouteNodes.HeightOverFloor_cm AS StartZ,
                         dbo.CableRouteSegments.End_NodeID, CableRouteNodes_1.GsiQuadrantX * 720 + CableRouteNodes_1.DistToGsiQuadrantX_cm AS EndX,
                         CableRouteNodes_1.GsiQuadrantY * 720 + CableRouteNodes_1.DistToGsiQuadrantY_cm AS EndY, CableRouteNodes_1.HeightOverFloor_cm AS EndZ,
                         dbo.CableRouteNodes.GSICoordX_m AS StartX2, dbo.CableRouteNodes.GSICoordY_m AS StartY2, CableRouteNodes_1.GSICoordX_m AS EndX2,
                         CableRouteNodes_1.GSICoordY_m AS EndY2
FROM            dbo.CableRouteNodes INNER JOIN
                         dbo.CableRouteSegments ON dbo.CableRouteNodes.NodeID = dbo.CableRouteSegments.Start_NodeID INNER JOIN
                         dbo.CableRouteNodes AS CableRouteNodes_1 ON dbo.CableRouteSegments.End_NodeID = CableRouteNodes_1.NodeID RIGHT OUTER JOIN
                         dbo.CableRoutePathIDs INNER JOIN
                         dbo.CableRoutePaths ON dbo.CableRoutePathIDs.RoutePathID = dbo.CableRoutePaths.RoutePathID ON
                         dbo.CableRouteSegments.PathSegmentID = dbo.CableRoutePaths.RouteSegmentID

Last but not least I needed a python script accessing the database and creating the tube-shaped objects along the paths given by the definitions of the cable paths. The view provides the following data:

• [0] RoutePathID: the identifier of the cable route path (1 per cable group)
• [1] SegmentSortOrder: the cable follows the path along a certain chain of path segments
• [2] RouteSegmentID: identifier of current path segment
• [3] RoutePathUser: as described above, each of the path is assigned to different cable groups which are under the supervision of a team
• [4] Description: long text description of the path segment
• [5] StartNodeID: Start node of path segment
• [6,7;10,11] Start/End GSIQuadrantX/Y: the accelerator facility area is segmented in to squares of 720 x 720 cm providing a coordinate system with specific 0-point
• [9] EndNodeID: End node of path segment
• [8;12] Start/End HeightOverFloor_cm: z-coordinate
• [13-16]Start/End  GSICoordX/Y_m: absolute distance to 0-point in x-direction

Blender Script

One challenge I had to overcome during the development of the script was the access to the python module PYODBC which provides an easy to use API to access databases, among which the MS SQL Server. I documented the solution I found - and which works pretty well in my case - in another blog post.
The Blender script implementation looks as follows:

import sys
pyodbcPath = 'E:\\Progs\\Anaconda\\envs\\python342\\Lib\\site-packages'
systemPaths = sys.path
checkResult = [s for s in systemPaths if pyodbcPath in s]
#check if path to pyodbc exists in sys and add if needed
if (checkResult == []):
    sys.path.append(pyodbcPath)
   
import pyodbc
import bpy
from mathutils import Vector
import bmesh
def createBevelObject(parentID):
    bpy.ops.curve.primitive_nurbs_circle_add(radius=0.05,layers=(False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,True))
    bevelObj = bpy.context.selected_objects[0]
    return (bevelObj)
dbConnection = pyodbc.connect('DRIVER={SQL Server};SERVER=<DBSERVERNAME>;DATABASE=>DBNAME>;UID=<DBUSERNAME>;PWD=<DBPASSWORD>')
dbCursor = dbConnection.cursor()
queryStatement = "SELECT * FROM dbo.CablesPathsDefinitionsWithOwners ORDER BY RoutePathID, SegmentSortOrder"
splineCounter = 0
oldPathID = ""
vertexList = []
scaleFactor = 100.0
outText = ""
bevelObject = createBevelObject(oldPathID)
for dbRow in dbCursor.execute(queryStatement):
    currentPathID = dbRow[0]
    if (oldPathID != currentPathID):
        if (len(vertexList) != 0): #vertexList must not be empty
            # Finalize curve creation with given list of vertices
            bpy.context.scene.objects.link(objectData)
            polyline = curveData.splines.new('POLY')
            polyline.points.add(len(vertexList)-1)
            for vertexCounter in range(len(vertexList)):
                x,y,z = vertexList[vertexCounter]
                outText = outText + oldPathID + ";" + str(vertexCounter) + ";x:" + str(x) + ";y:" + str(y) + ";z:" + str(z) + ";" + "\n"
                polyline.points[vertexCounter].co = (x,y,z,1)          
        vertexList = []
        if (dbRow[6] != None and dbRow[7] != None and dbRow[8] != None and dbRow[10] != None and dbRow[11] != None and dbRow[12] != None):
            # if oldPath != newPath create new curve object and fill with vertices afterwards
            # Prepare curve creation
            curveData = bpy.data.curves.new(currentPathID, type='CURVE')
            curveData.dimensions = '3D'          
            #curveData.bevel_depth = 1 / scaleFactor
            # Create new curve
            objectData = bpy.data.objects.new(currentPathID, curveData)
            curveData.bevel_object = bevelObject
            objectData.location = (0,0,0)
            oldPathID = currentPathID
            # add coordinates to vertexList
            if (dbRow[13] != None and dbRow[14] != None):
                x = float(dbRow[13])
                y = float(dbRow[14])        
            else:  
                x = float(dbRow[6]) / scaleFactor
                y = float(dbRow[7]) / scaleFactor
            z = float(dbRow[8]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            vertexList.append(Vector((x,y,z)))
            if (dbRow[16] != None and dbRow[15] != None):
                x = float(dbRow[15])
                y = float(dbRow[16])              
            else:
                x = float(dbRow[10]) / scaleFactor
                y = float(dbRow[11]) / scaleFactor
            z = float(dbRow[12]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)          
            vertexList.append(Vector((x,y,z)))
    else:
        if (dbRow[6] != None and dbRow[7] != None and dbRow[8] != None and dbRow[10] != None and dbRow[11] != None and dbRow[12] != None):
            # add coordinates to vertexList - coordinates are not neccesarily well aligned in polyline
            # First set of coordinates from DB
            if (dbRow[13] != None and dbRow[14] != None):
                x = float(dbRow[13])
                y = float(dbRow[14])        
            else:  
                x = float(dbRow[6]) / scaleFactor
                y = float(dbRow[7]) / scaleFactor
            z = float(dbRow[8]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            theVector = Vector((x,y,z))
            if (theVector not in vertexList): # check if entry already exists in list
                vertexList.append(theVector)
            # Second set of coordinates from DB
            if (dbRow[16] != None and dbRow[15] != None):
                x = float(dbRow[15])
                y = float(dbRow[16])              
            else:
                x = float(dbRow[10]) / scaleFactor
                y = float(dbRow[11]) / scaleFactor
            z = float(dbRow[12]) / scaleFactor
            x = round(x,2)
            y = round(y,2)
            z = round(z,2)
            theVector = Vector((x,y,z))
            if (theVector not in vertexList):
                vertexList.append(theVector)
dbConnection.close()

This is more or less it. Despite the fact that the xyz-coordinate data needs to be very correct and aligned with the other 3D model data coming from CATIA (following the s**t-in s**t-out paradigm), the script works pretty well and creates the desired bundles of Bezier curves in blender beveled with corresponding objects.

I hope you this post helps you with your own project, keep on geekin'!
Your WolfiG

SQL Server DML Update Trigger for Multiple Rows

Background

Currently I am working on a tool creating cable labels according to some specification. In this tool  I need to perform a complex concatenation of values from about 10 columns depending on the fill status of these rows and update the label column with the concatenated value in SQL Server 2012. The concatenation should be performed in case of a single row update and of a multi-row update via UPDATE <table> SET col1=value1, ..., colN=valueN
The weapon of choice was a DML database trigger AFTER UPDATE. As I hard some hard time to achieve my goal I want to share the code with the community, maybe you can get something out of it for your own project.

Table Structure

The cable label consists mainly of location information of the start and end points of a cable (components/devices connected by the cable and the room where the device can be found). My table has a structure like this, with fields not required for the trigger operation omitted:

• KeyField int, primary key
• CONCAT_VALUE varchar(1000)
• [...]
• NUMBER int
• START_COMP_ID varchar(100)
• START_COMP_IS_NOMEN bit
• START_AREA_ID varchar(50)
• START_ROOM_NO varchar(200)
• START_LEVEL_ID varchar(10)
• START_BLD_ID varchar(50)
• END_COMP_ID varchar(100)
• END_COMP_IS NOMEN bit
• END_AREA_ID varchar(50)
• END_ROOM_NO varchar(200)
• END_LEVEL_ID varchar(10)
• END_BLD_ID varchar(50)
• FUNCTION_ID varchar(10)
• [...]

Database Trigger

The database trigger consists of two main parts: one executed if a single line has been changed (@@ROWCOUNT = 1, mostly for manual input in the DB editor) and one for multi-row update (@@ROWCOUNT > 1, e.g. in the case of UPDATE table SET <some column> = <new value>).

In the first case one can use IF statements checking if some column is NULL directly via selection by the primary key column. An example:

DECLARE @Buffer varchar(255) 

[...] 

IF NOT (SELECT START_COMP_ID FROM table WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

   @Buffer = <concatenation> 

[...]

UPDATE table SET CONCAT_VALUE = @Buffer WHERE KeyField =(SELECT KeyField FROM inserted); 

In case of multi-row updates I used an approach with DB cursor which loops over the INSERTED table. The IF statement has then to be based on the value coming from the cursor:

DELCARE @KeyField int

DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR SELECT KeyField FROM inserted 

[...]

OPEN tableCursor 

FETCH NEXT FROM tableCursor INTO @KeyField

WHILE @@FETCH_STATUS = 0 

  BEGIN 

    IF NOT (SELECT START_COMP_ID FROM table WHERE 

    KeyField = @KeyField) IS NULL

    [...] 

    UPDATE table SET CONCAT_VALUE = @Buffer WHERE              KeyField = @KeyField; 

    END 

Complete Code of Trigger

Here is the complete code of the trigger if you want to study it in detail:

USE [myDB]

GO

/****** Object:  Trigger [dbo].[TriggerName]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[TriggerName] ON [dbo].[myTable] 

AFTER UPDATE 

AS 

DECLARE @START_COMP_ID varchar(100);

DECLARE @START_LOCATION varchar(255);

DECLARE @START_RACK varchar(255);

DECLARE @END_COMP_ID varchar(100);

DECLARE @END_LOCATION varchar(255);

DECLARE @END_RACK varchar(255);

DECLARE @FUNCTION varchar(255);

DECLARE @Buffer varchar(255);

DECLARE @KeyField int

DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR

SELECT KeyField FROM inserted

BEGIN

IF @@ROWCOUNT = 0

    RETURN

SET NOCOUNT ON;

SET @Buffer = '';

-- Execute only if one line has been changed

If (SELECT COUNT(*) FROM inserted) = 1

  BEGIN

-- ID part for component/technical place

    IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted) IS NULL

-- Component ID is not according to nomenclature

      IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))= 1

        SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + '>';

      ELSE

        SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KABEL_ID=(SELECT KABEL_ID FROM inserted));

    ELSE

    SET @START_COMP_ID = '';

    IF NOT (SELECT ACCNOMEN_FUNCTION FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @CONCAT_VALUE = '[' + (SELECT CONCAT_VALUE FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    ELSE

      SET @ACCNOMEN_FUNCTION = '[-';

    IF NOT (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @CONCAT_VALUE = @CONCAT_VALUE + '.' + (SELECT NUMBER FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) + ']';

    ELSE

      SET @CONCAT_VALUE = @CONCAT_VALUE + ']';

-- ID part of location/building

    IF NOT (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))        IS NULL 

      SET @START_LOCATION = (SELECT START_BLD_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    ELSE

      SET @START_LOCATION = ''; 

    IF NOT (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL

      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_LEVEL_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    IF NOT (SELECT START_RAUM_NR FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted))      IS NULL

      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_ROOM_NO FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    IF NOT (SELECT START_FLAECHE_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @START_LOCATION = @START_LOCATION + '.' + (SELECT START_AREA_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

-- Rack and stuff

    IF NOT (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @START_RACK = '#' + (SELECT START_RACK FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    ELSE 

      SET @START_RACK = '';

      IF NOT (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

        SET @START_RACK = @START_RACK + '.' + (SELECT START_FRAME_ID FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

    IF NOT (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted)) IS NULL

      SET @START_RACK = @START_RACK + '.b' +                                                          (SELECT START_SOCKET FROM myTable WHERE KeyField=(SELECT KeyField FROM inserted));

----------------------------------------------------------

-- END component

----------------------------------------------------------

[...] similar statements as above, omitted for better reading

-- Final assembly of label tag string and DB update 

SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' + @END_KOMP_ID + @END_LOCATION + @END_RACK;

-- Insert statements for procedure here

IF @Buffer <> ''

  BEGIN                                                                                             UPDATE CABLES_LIST SET CONCAT_VALUE = @Buffer WHERE KeyField=(SELECT KeyField FROM inserted);                                                                                   END

END                                                                                             ------------------------------------------------------------------------------------------------- Multi Row Part                                                                             ------------------------------------------------------------------------------------------------

ELSE

  BEGIN

    OPEN tableCursor

    FETCH NEXT FROM tableCursor INTO @KeyField

    WHILE @@FETCH_STATUS = 0

      BEGIN

      -- ID part for component/technical place

        IF NOT (SELECT START_COMP_ID FROM myTable WHERE KeyField = @KeyField) IS NULL

      -- component ID is not according to nomenclature

          IF (SELECT START_COMP_IS_NOMEN FROM myTable WHERE KeyField = @KeyField) = 1

            SET @START_COMP_ID = '<' + (SELECT START_COMP_ID FROM myTable WHERE KeyField =                  @KeyField) + '>';

          ELSE

            SET @START_COMP_ID = (SELECT START_COMP_ID FROM myTable WHERE KeyField =                        @KeyField);

        ELSE

          SET @START_COMP_ID = '';

        IF NOT (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField) IS NULL

          SET @FUNCTION = '[' + (SELECT FUNCTION FROM myTable WHERE KeyField = @KeyField);

        ELSE

                                                                                            [...] the structure of the omitted code follows the code for the 1 row update. Statements of the type KeyField=(SELECT KeyField FROM inserted) are always replaced by KeyField = @KeyField   

SET @Buffer = @START_COMP_ID + @CONCAT_VALUE + @START_LOCATION + @START_RACK + '/' +

  @END_COMP_ID + @END_LOCATION + @END_RACK;

-- Update table and loop to next row of INSERTED table

IF @Buffer <> ''

Begin

UPDATE CABLES_LIST SET ID_ACCNOMEN = @Buffer, QR_CODE_TEXT = 'CAB:' + @Buffer WHERE KABEL_ID = @CableID;

END

FETCH NEXT FROM tableCursor INTO @CableID

END

END

END

Unique ID-Generator using SQL Server

Background

In a project of mine I had the challenge to generate IDs which are profixes of file names of QM documents we want to store on a file server. These IDs have to be unique and have the follwoing structure:

<area of application, char(4)>-<document type char(3)>-<responsible unit, char(4)>-

<running number, char(5)>-...... (user title)

The parts "area of application", "document type" and "responsible unit" are prescribed by guidelines, the challenge was to build some central tool where users can "pick" new ID which has to be unique.

DB-Tables

I build four DB-tables:

ApplicationAreas with columns

• ApplicationAreaID (char4), PK)
• Description (nvarchar(100))

DocumentTypes with columns:

• DocumentTypeID (char(3), PK)
• Description (nvarchar(100))

ResponsibleAreas with columns:

• ResponsibleArea (char(4), PK)
• Description (nvarchar(100))

DocumentIDs with columns:

• ApplicationArea (char(4), PK, FK to ApplicationAreas.ApplicationArea)
• DocumentType (char(3), PK, FK to DocumentTypes.DocumentType
• ResponsibleArea (char(4), PK, FK to ResponsibleAreas.ResponsibleArea)
• RunningNumber (int, PK)

Stored Procedure

My first attempt was to make column DocumentIDs.RunningNumber an identity column and to calculate the number automatically via the table. Unfortunately, this approach failed, as the identity functionality does not take into account the multi-column primary key and simply counts up the number with every new entry.

Finally I choose to take an approach via a stored procedure which gives a string output with the automatically determined Document-ID. Here the code:

USE [theDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[pickDocumentID]

@ApplicationArea char(4),

@DocumentType char(3),

@ResponsibleUnit char(4)

AS

BEGIN

DECLARE @counter int

DECLARE @counterchar char(5)

DECLARE @theOutput nvarchar(255)

DECLARE @theLength int

SET NOCOUNT ON;

        -- Retrieve highest existing entry for given values

SET @counter = (SELECT MAX(RunningNumber) FROM DocumentIDs

WHERE

ApplicationArea = @ApplicationArea AND

DocumentType = @DocumentType AND

ResponsibleUnit = @ResponsibleUnit);

        -- If there is no entry for the given combination of first three

        -- columns initialize @counter

IF @counter IS NULL

SET @counter = 0;

SET @counter = @counter + 1;

        -- write newly determined Id to DB

INSERT INTO DocumentIDs

        (ApplicationArea, DocumentType, ResponsibleUnit, RunningNumber)

VALUES

        (@ApplicationArea, @DocumentType, @ResponsibleUnit, @counter)

-- Output for Document ID ready to use

SET @counterchar = CONVERT(char(5),@counter);

SET @theLength = LEN(@counterchar);

SET @counterchar = CASE

WHEN @theLength = 1 THEN '0000' + @counterchar

WHEN @theLength = 2 THEN '000' + @counterchar

WHEN @theLength = 3 THEN '00' + @counterchar

WHEN @theLength = 4 THEN '0' + @counterchar

WHEN @theLength = 5 THEN @counterchar

END

SET @theOutput = @ApplicationArea + '_' +

                         @DocumentType + '_' +

                         @ResponsibleUnit + '_' +

                         @counterchar ;

SELECT @theOutput;

END

So, this was it dear reader. I hope you could get something out of my post and you enjoyed.

SQL - Calling Stored Procedure with Comma Separated List in Input Parameter

Background

Currently I am programming on an ASP.NET web application with a data connection to a MS SQL Server 2008 R2 database. I am fetching data always via stored procedures. Today I came across the problem that I wanted to program a procedure where I have to do a SELECT with a WHERE column IN (...) with an unknown number of arguments in the IN statement.
Hence I was looking for a way to transfer a comma separated list as string parameter into the WHERE IN argument.

First Try

The straight forward approach I tried looked like this:

CREATE PROCEDURE [dbo].[MyProcedure]
@inputList nvarchar(MAX) <- comma separated list of values (string)
AS
BEGIN
      SELECT * FROM myTable
      WHERE
      colName IN (@idList)
END
GO

Unfortunately, this approach does not work. You can inject a list with on list element which will return some reasonable result, but as soon as you call this with list with more than one argument, this fails, as the argument is not interpreted as WHERE IN ('arg1','arg2') but as WHERE IN ('arg1,arg2').

Solution

After some research I stumbled over a Code Project thread which was the key to solve the problem. To have a more ready-to-use description I wrote this blog. With the help of the above thread I wrote the following stored procedure:

CREATE PROCEDURE [dbo].[myWorkingProcedure]
@inputList nvarchar(MAX)
AS
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SetPoint INT
DECLARE @VALUE nvarchar(50)
CREATE TABLE #tempTab (id nvarchar(50) not null)
BEGIN
SET NOCOUNT ON;
WHILE PATINDEX('%,%',@idList) > 0 <-- Drive loop while commata exist in the input string
BEGIN
SELECT  @SP = PATINDEX('%,%',@idList) <-- Determine position of next comma
SELECT  @VALUE = LEFT(@idList , @SP - 1) <-- copy everything from the left into buffer
SELECT  @idList = STUFF(@idList, 1, @SP, '') <-- throw away the stuff you copied
INSERT INTO #tempTab (id) VALUES (@VALUE) <-- put value in buffer table
END
INSERT INTO #tempTab (id) VALUES (@idList) <-- insert last value in the input list to buffer
BEGIN
SELECT * FROM myTable
WHERE
myColumn IN (SELECT id FROM #tempTab) <-- Do the select
DROP TABLE #tempTab <-- throw buffer table away
END
END
GO

Conclusion

With the help of the above code it is possible to perform dynamic SELECT WHERE IN statements with an unknown number of arguments. I hope this post helps you to overcome your own problems with transactional SQL


all the best
WolfiG

How to Set Up MS SQL Server and IIS 7.5 for ASP.NET 4.5 Application

Background

I am currently developing an ASP.NET 4.5 application with connection to a database on MS SQL Server 2008 R2. I am using some dropdown-box controls on one screen which I configured to connect directly to the DB. To run the application in our company's intranet I deployed it on my local Windows 7 Professional PC, where I was running IIS 7.5 as web server. As development environment I was using MS Visual Studio Express 2012 for Web.

Everything worked fine as long as I was developing in Visual Studio, where I was able to start the application on Chrome and localhost without problems. The trouble started when I deployed my web application to IIS. In principle it worked, I was able to run the application through port 80 after I opened the corresponding port in the fire wall. However, the system crashed as soon I was calling a screen with dropdown-boxes connected to the SQL Server.

The error I got on the dump-screen was the following:

"Server error in application /MyApplication"

"Login Failed for user 'MyDomainName\MyPCName$'"

[...]

"[SQLException (0x80131904): Login failed for user 'MyDomainName\MyPCName$' ....

Attempts

I started to read loads of articles and tried the following things:

• Introduction of a dedicated user for the login on the SQL Server together with Windows-Authentication didn't work - and why should it?
• Switching from Windows Authentication to SQL Server Authentication: I convinced our SQL Server admin to switch the Authentication mode from Windows to SQL Server & Windows. However, this was a non-option in the end as I was not the owner of the server and a re-start of the server was prohibited.
• IIS impersonation: didn't work as this requires SQL Server Authentication (see above)

Solution

In the end I became curious about the fact that the error message which stated something about 'MyDomainName\MyComputerName$'. I created in SQL Server a user 'MyDomainName\MyComputerName' using Windows Authentication mode. This caused the following error message

Create failed for Login 'MyDomainName\MyPCName' [...] Server, Error: 15401

Finally, I found that typing 'MyDomainName\MyPCName$' worked. Furthermore you have to set the following settings:

• The General Settings look like this:

• In the database you want to use create a user with the same name MyDomainName\MyPCName$

• In the Section "User Mapping" of the Login Properties (Section SQL Server Instance --> Security --> Login Properties make the following settings

I hope this article helped you. If not, please send me comments to improve it.

Reference: "Login failed for user <user>"; http://msdn.microsoft.com/en-us/library/ab4e6cky(v=vs.80).aspx