Error with linked server using Microsoft.Jet.OLEDB.4.0

I started at this company, and the jobs were pretty complex and undocumented. One of the procs checks on txtfiles that SSIS creates by using a linked server that uses Microsoft.Jet.OLEDB.4.0 and points to its own C drive. Using that, the job does a count on each table, and compares it to its totals. Maybe unnecessarily complex.

It was working properly till last Friday, when I started getting the error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "textserver"
returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "textserver".

The query used is:

SELECT COUNT(*) FROM textserver...[textfile#txt]

I've tried rebooting the server, and recreating the linked server to no avail.

Has anyone seen this issue before?

how to get sum of each column new records in sql server

Hi Friends I have small doubt in sql server please tell me how to achive this task in sql server

productname |Level|  January  | Feburary | March | total
  Rin       | L1  |   10      | 20       |  30   | 60
  Rin       | L2  |   5       | 10       |  10   | 25
  Rin       | L3  |  20       | 5        |  5    | 30
  Pen       | L1  |  5        | 6        |  10   | 21 
  Pen       | L2  |  10       | 10       | 20    | 40
  Pen       | L3  | 30        |10        | 40    | 80

based on above table data I want output like below

productname |Level|  January  | Feburary | March | total
  Rin       | L1  |   10      | 20       |  30   | 60
  Rin       | L2  |   5       | 10       |  10   | 25
  Rin       | L3  |  20       | 5        |  5    | 30
RinTotal    |All  |  35       |35        |  45   |115
  Pen       | L1  |  5        | 6        |  10   | 21 
  Pen       | L2  |  10       | 10       | 20    | 40
  Pen       | L3  | 30        |10        | 40    | 80
PenTotal   | All  | 45        |26        | 70    |141

I tried like bellow query

select productname,level,sum(january)as January,sum(Feburary)as Feburary),Sum(march)as March,Sum(total)as total
from test
union
select * from test 

but its not given exact output .please tell me query how to achive this task in sql server.

SQL query for parent child with no relationship

I need urgent help regarding a query that I want to create. For instance, let's say I have this table:

Description Level Is_Active

---------------------------------------------------- (1)Metallic industry products 1 1 (2)+ Various metal products 2 1 (3)++ Other metal products 3 1 (1)Rubber and plastic products 1 1 (2)+ Rubber products 2 1 (2)+ Other rubber products 2 1 (3)++ Other product types 3 1

where level specifies the relationship. The records in the table are set to reproduce a tree structure. What I'm trying to do is a query which selects all the parents with children from this table that are active. If, for instance, the Is_Active column for Metallic industry products is set to 0, I don't want to display it and it's children (arious metal products and other metal products). The same for Various metal products, if it's not active, don't display it and it's children. I tried joining with the same table or using the WITH function but saddly I can't find a solution.

Running total column with derived starting point

I have a table like below with a primary key of rootID and I want to update the column DaysRunningTotal with a running total of DaysBetween, but ground zero will be the value of rootID which has a HIGHLIGHT value of 1 - and there will only ever be one instance of this. The DaysBetween has been derived from a datetime column So after running an update script the table will look like the second one below. What script will do this please?

rootID  HIGHLIGHT   DaysRunningTotal    DaysBetween
1       0           NULL                0
2       1           NULL                3
3       0           NULL                4
4       0           NULL                212
5       2           NULL                0
6       0           NULL                0
7       0           NULL                0
8       0           NULL                18
9       0           NULL                0
10      0           NULL                112


rootID  HIGHLIGHT   DaysRunningTotal    DaysBetween
1       0           -3                  0
2       1            0                  3
3       0            7                  4
4       0            219                212
5       2            219                0
6       0            219                0
7       0            219                0
8       0            237                18
9       0            237                0
10      0            349                112

Limit the results of a query

i have this SQL code which obtains information about SharePoint sites, it returns every subsite and sites inside those i need to fix this query in a way that only returns the first subsite and not the rest for example, here i need only the info from EURACI and ignore the rest of the sites under that subsite

SELECT A.fullurl, A.title, D.[tp_Login], D.[tp_Title], D.[tp_Email] FROM webs A
INNER JOIN [GroupMembership] B ON B.[SiteID] = A.[SiteId]
INNER JOIN [Groups] C ON C.[ID] = B.[GroupID]
INNER JOIN [USERINFO] D ON D.[tp_ID] = B.[MemberID]
WHERE C.[Title] LIKE '%Owners%' and D.Tp_IsActive = '1'
GROUP BY A.fullurl, A.title, D.[tp_Login], D.[tp_Title], D.[tp_Email]
ORDER BY fullurl

any ideas on how can i achive this?

Django-pyodbc mssql error for dumpdata

I am trying to perform a sudo ./manage.py dumpdata on a mssql db from my django project, but keep getting:

Traceback (most recent call last):
  File "./manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/base.py", line 338, in execute
    output = self.handle(*args, **options)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/commands/dumpdata.py", line 158, in handle
    stream=self.stdout)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/serializers/__init__.py", line 128, in serialize
    s.serialize(queryset, **options)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/serializers/base.py", line 52, in serialize
    for obj in queryset:
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/commands/dumpdata.py", line 150, in get_objects
    for obj in queryset.iterator():
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/db/models/query.py", line 265, in iterator
    for row in compiler.results_iter():
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 701, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 785, in execute_sql
    cursor = self.connection.cursor()
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/db/backends/__init__.py", line 165, in cursor
    cursor = self.make_debug_cursor(self._cursor())
  File "/opt/bitnami/python/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 230, in _cursor
    autocommit=autocommit)
pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

I have done the configuration mentioned in http://ift.tt/1OQOzmr, and I get all the test working (FreeTDS & ODBC).

Here is my current settings.py database config:

DATABASES = {
    ...
    'vcoe':
        {
            'ENGINE' : 'sql_server.pyodbc',
            'NAME' : 'VOIP',
            'USER' : '******',
            'PASSWORD' : '******',
            'OPTIONS' : {
                'driver': 'FreeTDS',
                'dsn': 'VCOE',
            }
        }
}

DATABASE_ENGINE = 'sql_server.pyodbc'
DATABASE_NAME = 'VOIP'
DATABASE_USER = '******'
DATABASE_PASSWORD = '******'
DATABASE_OPTIONS = {
    'driver': 'FreeTDS',
    'dsn': 'VCOE', # ODBC DSN name defined in your odbc.ini
}

When I try and run ./manage.py test I get:

Traceback (most recent call last):
  File "./manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/commands/test.py", line 50, in run_from_argv
    super(Command, self).run_from_argv(argv)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/commands/test.py", line 71, in execute
    super(Command, self).execute(*args, **options)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/base.py", line 338, in execute
    output = self.handle(*args, **options)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/core/management/commands/test.py", line 88, in handle
    failures = test_runner.run_tests(test_labels)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/test/runner.py", line 147, in run_tests
    old_config = self.setup_databases()
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/test/runner.py", line 109, in setup_databases
    return setup_databases(self.verbosity, self.interactive, **kwargs)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/test/runner.py", line 299, in setup_databases
    serialize=connection.settings_dict.get("TEST", {}).get("SERIALIZE", True),
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/db/backends/creation.py", line 362, in create_test_db
    self._create_test_db(verbosity, autoclobber)
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/db/backends/creation.py", line 455, in _create_test_db
    with self._nodb_connection.cursor() as cursor:
  File "/opt/bitnami/python/lib/python2.7/site-packages/django/db/backends/__init__.py", line 167, in cursor
    cursor = utils.CursorWrapper(self._cursor(), self)
  File "/opt/bitnami/python/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 181, in _cursor
    raise ImproperlyConfigured('You need to specify NAME in your Django settings file.')
django.core.exceptions.ImproperlyConfigured: You need to specify NAME in your Django settings file.

I have tried to remove the DATABASES = {...} section and only leave the DATABASE_XXX but get:

django.core.exceptions.ImproperlyConfigured: settings.DATABASES is improperly configured. Please supply the ENGINE value. Check settings documentation for more details.

Im currently using on client:

• Ubuntu 14.04.1 LTS
• Django 1.7.8
• pyodbc==3.0.10
• django-pyodbc==0.2.8

Server I'm reaching is:

• Microsoft Server 2003 running: MSSQL 2005

Here is /etc/odbcinst.ini:

[FreeTDS]
    Description = TDS driver (Sybase/MS SQL)
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    CPTimeout =
    CPReuse =

Double checked path, all files are present

Here is my /etc/odbc.ini:

[ODBC Data Sources]
ODBCNAME = Microsoft SQL Server

[VCOE]
Driver = FreeTDS
Description = VOIP Legacy Database
Trace = No
Servername = VOIP
Database = VOIP

[Default]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

here is my /etc/freetds/freetds.conf:

[VOIP]
host = 142.117.139.***
port = 1433
tds version = 8.0

How to combine multiple named Columns as a Row and sum of it

I need a query in SQL to combine several rows in the same table.

L_Life_Amount  M_LifeAmount    L_Health_Amount    M_Health_amount
100                 200             300                 400
200                 300             400                 600
300                 400             500                 700

and I need to bring the below result set

InsuranceType  L_Amount     M_Amount    L_amount-M_amount   
Life amount    600          900         -300
Health amount   1200        1700        -500

Total           1800        2600        -800

Here I need to sum the values of L_life_Amount(L_Amount) and sum of M_Life_amount(M_Amount) and display it as Life amount

the same way I need to do sum for L_Helath_Amount(L_Amount) and sum of M_Health_Amount(M_Amount) and display it as Health Amount

Finally I make the diff betweeen L_amount and M-amount and make a total of it.

Difference in the checksum SQL statements

What is the difference between the 2 different statements below? Please explain the output.

 SELECT CHECKSUM(CONVERT(NVARCHAR,30))

Result : 51136012

 DECLARE @AA NVARCHAR
SET @AA= CONVERT(NVARCHAR,30)
SELECT CHECKSUM(@AA)

Result: 38

Concatenate two columns based upon another column in SQL Server 2005

I have database as follows

A     B
1   ereee
2   ereee
2   sdfsd
3   nere
1   sdfsd

I want to have a data which will be as follows

A     B
1   ereee,sdfsd
2   ereee,sdfsd
3   nere

what is the query?

Need specific string in SQL Server

I need your help guys.

I am trying to get specific word from the whole string in SQL Server.

I have following string:

'ABC, XYZ'

Need Output : ABC

Now I need only ABC out of whole string. I tried with Substring and Trim function but couldn't get appropriate output.

Could anyonel please help me?

Thank in advance!

Are SQL DMV Queries Ideal as Stored Procedure?

I recently bumped into this answer: http://ift.tt/1h7502D which suggests A DMV query to retrieve information regarding deadlock.

I'm planning to make this query a stored procedure and call it (using ADO.NET) in my .NET Application whenever it executes and use the output as a log of my application.

Do you think this set-up is ideal? Or would it cause performance overheads and what not to my application?

Note: I can't use SQL Profiler for my production environment so I'm planning to use this query inside my application.

SQL Query based on occurrence of records

After a long time, I am getting a chance to post a SQL Server question here.

I have a table variable as shown below, in SQL Server 2005. This table is populated by a stored procedure written by some other team.

This is a order processing system. Each order can be accomplished by multiple processes by various departments, based on the OPRouteCode.

Taking example for OrderNo = 2, it has two OPRouteCode - but both these OPRouteCodes are using the same processes by same departments. They are considered equivalent OPRouteCodes.

On the other hand, for example OrderNo = 1, the processes and departments vary; hence they are not equivalent.

What is the best way to select only orders that has non-equivalent OPRouteCodes.

Note: If there is only one OPRouteCode, it is considered as equivalent only. Non-equivalence come only if there are more than one OPRouteCode.

What is the best SQL Server query to get this result? I couldn't write anything working after hours of effort.

DECLARE @OrderProcess TABLE (OrderNo Int, 
                             OPRouteCode VARCHAR(5), 
                             Department VARCHAR(10), 
                             Process VARCHAR(20) )

--Order = 1 OPRouteCode =  '0023'
INSERT INTO @OrderProcess
SELECT 1,'0023' ,'103','Receive'
UNION ALL
SELECT 1,'0023' ,'104','Produce'
UNION ALL
SELECT 1,'0023' ,'104','Pack'
UNION ALL
SELECT 1,'0023' ,'105','Ship'

--Order = 1 OPRouteCode =  '0077'
INSERT INTO @OrderProcess
SELECT 1,'0077' ,'103','Receive'
UNION ALL
SELECT 1,'0077' ,'104','Produce'
UNION ALL
SELECT 1,'0077' ,'105','Ship'

--Order = 2 OPRouteCode =  '0044'
INSERT INTO @OrderProcess
SELECT 2,'0044' ,'105','Receive'
UNION ALL
SELECT 2,'0044' ,'106','Ship'

--Order = 2 OPRouteCode =  '0055'
INSERT INTO @OrderProcess
SELECT 2,'0055' ,'105','Receive'
UNION ALL
SELECT 2,'0055' ,'106','Ship'

Table Variable

Expected Output

Something similar to merge in SQL Server 2005?

Using 2 tables I need to delete distinct, and insert new records.

I've table1 and table2. First 3 records are the same in both tables, but last 2 records are different. So first of all I need to delete different records and after that add new records.

I use SQL Server 2005, so MERGE function doesn't exist yet. How could I resolve this without using MERGE?

Thank you very much!

create table #Table1 (F1 char(5),F2 char(5),F3 char(5))
insert into #Table1
select 'AAA','BBB','CCC' union all
select 'AAA','BBB','CCC' union all
select 'AAA','BBB','CCC' union all
select 'AAA','BBB','DDD' union all
select 'AAA','BBB','EEE'

SELECT * FROM #Table1

create table #Table2 (F1 char(5), F2 char(5), F3 char(5))

insert into #Table2
   select 'AAA','BBB','CCC' union all
   select 'AAA','BBB','CCC' union all
   select 'AAA','BBB','CCC' union all
   select 'AAA','BBB','FFF' union all
   select 'AAA','BBB','GGG' 

SELECT * FROM #Table2

unable to call stored procedre from jdbc

below is my JDBC code for connecting to DB and call a stored porcedure. i have installed the sqljdbc4v in src folder onf my project and the class path is also set to my bin folder and src folder.

Connection con = null;
    CallableStatement proc_stmt = null;
    ResultSet rs = null;

    try {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        con = DriverManager.getConnection("jdbc:http://sqlserverMYSERVER;databaseName=MYDATABASE", "USERID", "PASSWORD");

        proc_stmt = con.prepareCall("{ call generateID(?) }");

        proc_stmt.setString(1, "employee");
        rs = proc_stmt.executeQuery();

        if (rs.next()) {
            int employeeId = rs.getInt(1);
            System.out.println("Generated employeeId: " + employeeId);
        } else {
            System.out.println("Stored procedure couldn't generate new Id");
        }
    } catch (ClassNotFoundException ex) {
        ex.printStackTrace();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {

        try {

            rs.close();
            proc_stmt.close();
            con.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

i keep on getting the below error java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver at java.net.URLClassLoader$1.run(Unknown Source) at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Unknown Source) at StoredProcedure1.main(StoredProcedure1.java:18) Exception in thread "main" java.lang.NullPointerException at StoredProcedure1.main(StoredProcedure1.java:41)

please help..

An error occurred while preparing the query for linked server - SQL Server + Teradata

In SQL Server I have defined the following Job agent and the following step:

INSERT INTO dse.dwh_log
SELECT *
FROM
OPENQUERY(DWH_Production, 
'
select
    cast(cast(LogonDate as format ''yyyy-mm-dd'') as char(10)) || '' '' ||
    cast(cast(LogonTime as format ''99:99:99.999'') as char(12)) as LogonTime,
    UserName,
    substr(StatementText,1,8000) as RequestText
from
    p_sys_ms_logging.accesslog_hst
where      statementtype = ''select''
and databasename  in (''CDR'', ''cdr30_targetdb'')
and statementtext is not null
and logondate > current_date - 2')

The error message I am getting is:

Executed as user: XYZ. An error occurred while preparing the query select cast(cast(LogonDate as format 'yyyy-mm-dd') as char(10)) || ' ' || cast(cast(LogonTime as format '99:99:99.999') as char(12)) as LogonTime, UserName, substr(StatementText,1,8000) as RequestText from p_sys_ms_logging.accesslog_hst where statementtype = 'select' and databasename in ('CDR', 'cdr30_targetdb') and statementtext is not null and logondate > current_date - 2"

for execution against OLE DB provider "MSDASQL" for linked server "DWH_Production".
[SQLSTATE 42000] (Error 7321).
NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.

The duration is about an hour, but the query should run in around 5 minutes. I am retrieving the data from the Teradata database, and transferring them to SQL Server.

Does anyone know why am I getting this message (on Google I did not find anything that could help me), and what I should do to fix it?

SQL Server 2005 Error 18456

I have installed Sql Serevr 2005 and I am trying to connect database engine from sql server management studio. But i am getting an error as follows:

TITLE: Connect to Server

Cannot connect to ADMIN-PC.

---

ADDITIONAL INFORMATION:

Login failed for user 'admin-PC\admin'. (Microsoft SQL Server, Error: 18456)

Technical details:

Server Name: ADMIN-PC Error Number: 18456 Severity: 14 State: 1 Line Number: 65536

Please help me in resolving this error. Thank u.

sql query to select data from excel

I get this error :

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

when executing this query in SQL Server 2005:

SELECT * 
INTO #TEMP1 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=E:\Students.xls',
                'SELECT * FROM [Sheet1$]')

How to use nested If statements in SQL trigger

I'm trying to learn SQL triggers to automatically handle events in my database but I'm having some problems with execution.

If I run the following code:

declare @userid numeric(18,0);
declare @username nvarchar(max);
set @userid = 400
execute GetUserNameFromID @userid,@username output
select @username

which calls the following stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetUserNameFromID 
    -- Add the parameters for the stored procedure here
    @UserID numeric(18,0),
    @UserName nvarchar(MAX) OUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @UserName = u.name from Users u where ID=@UserID
END
GO

I get a nice result 'sometestuser'

But when calling it from my trigger it fails to return a value from the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[CheckIfUserHasNoItemsLeft] on [dbo].[Items] for update
As

Begin
set nocount on 

declare @inactive_user nvarchar(50);
declare @userid numeric(18,0);
declare @username nvarchar(MAX);

if update(InactiveUser)
    set @inactive_user = (select InactiveUser from inserted)
    if @inactive_user <> null
        set @userid = (select CID from inserted)
        execute GetuserNameFromID @userid,@username output
        if @username <> null        
            insert into tasks (Task) values ('The last item for ' + @username + ' has been marked inactive, check if this user should now be also marked inactive.')
End

InactiveUser is the name of the app user who has marked this item inactive, it is what I am using as a check to see if the item has been set inactive rather than create an additional boolean column just for this purpose.

I'm sure it's something simple but information on If...Then statements for SQL seems to be limited and a lot of answers suggest using Case but the query editor gives me errors about incorrect syntax no matter which way I try to do it that way.

As I'm learning I'm more than happy for someone to show me a completely new way of handling this if what I've done is wrong or bad design. I'm hoping to create a set of triggers that will add items to the tasks table for administrators to check when user accounts appear to be stale and other maintenance checks etc.

I am using SQL server 2005.

Thank you.

Selecting SUM of specific values but not altering report

For my report I have several different product codes. These product codes make up two different sections. 5870, 5730, and 5990 are one section (top half) and 5780 and everything less than 5700 make up another section (bottom half). I already have a code that combines 5780 and everything less than 5700 and that is:

SUM(SUM(PL.OriginalQuantity_Stk)) OVER
       (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
                           THEN PC.ProductCategoryCode
                      END)
       ) 

A sample of this output is:

Product Category Code | Weight |
5870                  | 100    |
5730                  | 400    |
5990                  | 200    |
5780                  | 50     | 
1111                  | 50     |  
2222                  | 175    |  
3333                  | 500    |  
4444                  | 125    |  

5870 = 100
5730 = 400
5990 = 200
Other= 900

That code does exactly what I need it to do for that part of the report. However, that code also generates the SUM of the other product codes which is great for that part of the report but for another calculation, I only need to use the product codes 5780 and everything less than 5700.

Right now I am trying to use the code:

(CASE WHEN PC.ProductCategoryCode = 5780 OR PC.ProductCategoryCode < 5700 THEN Sum(Sum(PL.OriginalQuantity_Stk)) OVER(PARTITION BY PC.ProductCategoryCode)
ELSE 0
 END)

This makes the irrelevant product code's weight equal to zero but it doesn't take the total SUM of the product code's weight I need.

An example output of what I need now is:

Product Category Code | Weight |
5870                  | 100    |
5730                  | 400    |
5990                  | 200    |
5780                  | 50     | 
1111                  | 50     |  
2222                  | 175    |  
3333                  | 500    |  
4444                  | 125    |  

5870 = 0
5730 = 0
5990 = 0
Other= 900

It doesn't necessarily have to equal 0, but I only need the 900 value because I will have to use that 900 for other calculations in this report. I hope that makes sense. Let me know if I need to clarify more.

I am using Microsoft SQL Server 2005.

Whole Code:

SET NOCOUNT ON; 
DECLARE @Credits Decimal(19,8);
DECLARE @Price Decimal(19,8);
DECLARE @Labor Decimal(19,8);
DECLARE @Packaging Decimal(19,8);
DECLARE @Overhead Decimal(19,8);

SET @Credits = 41000;
SET @Price = 1.57;
SET @Labor  = 0;
SET @Packaging  = 0;
SET @Overhead  = 0;

SELECT 
     CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings' 
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1] 
END AS [Description]
   , SUM(SUM(PL.OriginalQuantity_Stk)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
) AS [Total Weight]
   , Sum(CASE WHEN [PC].[ProductCategoryCode] <> 5870 THEN [PL].[OriginalQuantity_Stk] 
WHEN [PC].[ProductCategoryCode] <> 5730 THEN [PL].[OriginalQuantity_Stk] 
WHEN [PC].[ProductCategoryCode] <> 5990 THEN [PL].[OriginalQuantity_Stk] 
END) AS [Weight]
   , @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () AS [Shrink]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode]) 
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE Sum([PL].[OriginalQuantity_Stk]) / @Credits
END AS [Yield]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] 
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25 
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0 
ELSE [ICP].[UnitCost]
END AS [UC]
   , CASE 
 WHEN PC.ProductCategoryCode = 5870
 THEN ICP.UnitCost * (Sum(PL.OriginalQuantity_Stk) / @Credits) 
 WHEN PC.ProductCategoryCode = 5730
 THEN (ICP.UnitCost - 0.25) * (Sum(PL.OriginalQuantity_Stk) / @Credits)
 WHEN PC.ProductCategoryCode = 5990
 THEN (ICP.UnitCost * 0) * (Sum(PL.OriginalQuantity_Stk) / @Credits) 
 ELSE ICP.UnitCost * (Sum(PL.OriginalQuantity_Stk) / @Credits) 
 END AS [Value]
   , SUM(ICP.UnitCost * (Sum(PL.OriginalQuantity_Stk) / @Credits)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
) AS [Total Value]
   , (CASE WHEN PC.ProductCategoryCode = 5780 OR PC.ProductCategoryCode < 5700 THEN Sum(Sum(PL.OriginalQuantity_Stk)) OVER(PARTITION BY PC.ProductCategoryCode)
ELSE 0
 END) AS [Test]
 FROM (((( IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey)
    INNER JOIN  AR_Customers [ARC] 
     ON [DCT].CustomerKey = [ARC].CustomerKey)
    INNER JOIN  IC_ProductLots [PL] 
     ON [DCT].LotKey = [PL].LotKey)
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
 WHERE 
    ([PL].ProductionDate >= { ts '2015-07-10 00:00:00' }   AND ([PL].ProductionDate <= { ts '2015-07-10 00:00:00' } OR [PL].ProductionDate Is Null)) 
AND ((1=1)  AND [ARC].CustomerKey IN (39) ) 
 GROUP BY 
     CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings' 
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1] 
END
   , [PC].ProductCategoryCode
   , [ARC].CustomerKey
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] 
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25 
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0 
ELSE [ICP].[UnitCost]
END
   , [ICP].UnitCost
   , [PC].ProcessCode
 ORDER BY 
     @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () 
   , SUM(SUM(PL.OriginalQuantity_Stk)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
) 
   , SUM(ICP.UnitCost * (Sum(PL.OriginalQuantity_Stk) / @Credits)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
)

SQL Performance with Distinct and Count

I have a stored procedure which contain so many SET statements. That is taking so long to execute. What I can do for increase the performance. One statement I have included here.

SET @VisitedOutlets=(select count (distinct CustomerId) from dbo.VisitDetail  
                        where RouteId = @intRouteID   
                        and CONVERT(VARCHAR(10),VisitDate,111) between CONVERT(VARCHAR(10),@FromDate,111)  
                        and CONVERT(VARCHAR(10),@ToDate,111)); 

Trouble connecting to remote SQL Server 2005 with PHP using mssql

I've been struggling with this for a ridiculous amount of time and I'm not sure what to do. I'm using PHP 5.4 on a GoDaddy Linux shared hosting account with the mssql module enabled. The database I'm trying to reach is hosted elsewhere. I'm trying to connect via mssql_connect. I've tried

$con = mssql_connect('<ip address>', 'user', 'pass');

as well as

$con = mssql_connect('<ip address>\<instance name>', 'user', 'pass');

And the above two with port numbers. But I still get the "Unable to connect to server" error. I've made sure SQL Server is accepting outside connections and SQL Agent is running. I can't use PDO with sqlserv because of GoDaddy, mssql is the only module I'm able to use.

Does anyone have any ideas as to what could be wrong?

Update multiple tables from fixed width file

I need to do a bulk import into SQL Server 2005 using a fixed width file that has data to be written to 3 different tables. I am not sure the best way to go about this. I assume the best way would be to import all of the data into a single table and update each of the 3 from the new staging table. I am not sure though how to import the data into the table from the fixed width file.

Thanks, Scott

SQL not calculating TOTAL of selected columns

I need to calculate the total value of a specific range of product codes. The product codes I need the calculation done on are 5780 and everything under 5700.

To get the total value I need the total cost of those products as well as the yield.

Value = Pirce * Yield %

To calculate the yield I use the equation:

 CASE 
    WHEN [PC].[ProductCategoryCode] = 5870
            THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
    WHEN [PC].[ProductCategoryCode] = 5730
            THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
    WHEN [PC].[ProductCategoryCode] = 5990
            THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
    ELSE Sum([PL].[OriginalQuantity_Stk]) / @Credits
END

The price is already in the system so I don't need to calculate that.

I have tried several equations to calculate the total value but none of them return the desired amount.

I feel like it should be similar to this equation but this just displays the value for each row as opposed to one total value:

SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (
        PARTITION BY (
            CASE 
                WHEN PC.ProductCategoryCode = 5780
                    OR [PC].[ProductCategoryCode] < 5700
                    THEN PC.ProductCategoryCode
                END
            )
        ) * ICP.UnitCost

This equation displays one total sum but the total is incorrect:

CASE 
    WHEN [PC].[ProductCategoryCode] = 5780 OR [PC].[ProductCategoryCode] < 5700
        THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits * [ICP].[UnitCost]) OVER ()
END

I think that maybe I need the SUM of the UnitCost as well but when I try to do that inside the equation I get errors.

Sample Output:

    --------------------------------
    Product Category Code | Value  |
    ----------------------x--------x
    5870                  | 100    |
    5730                  | 400    |
    5990                  | 200    |
    5780                  | 200    |
    1111                  | 50     |  
    2222                  | 175    |  
    3333                  | 500    |  
    4444                  | 125    |  

Total Value: 1050 It has to ignore product code 5870, 5990, and 5730 and not include those in the total value.

I am using Microsoft SQL Sever 2005. Let me know if I need to explain further.

Full Code:

SET NOCOUNT ON;

DECLARE @Credits DECIMAL(19, 8);
DECLARE @Price DECIMAL(19, 8);

SET @Credits = 41000;
SET @Price = 1.57;

SELECT CASE 
        WHEN [PC].[ProductCategoryCode] = 5870
            THEN 'Trimmings'
        WHEN [PC].[ProductCategoryCode] = 5730
            THEN 'Rib Tips'
        WHEN [PC].[ProductCategoryCode] = 5990
            THEN 'Skins'
        ELSE [PC].[Description1]
        END AS [Description]
    ,SUM(SUM(PL.OriginalQuantity_Stk)) OVER (
        PARTITION BY (
            CASE 
                WHEN PC.ProductCategoryCode IN (
                        5870
                        ,5730
                        ,5990
                        )
                    THEN PC.ProductCategoryCode
                END
            )
        ) AS [Total Weight]
    ,Sum(CASE 
            WHEN [PC].[ProductCategoryCode] <> 5870
                THEN [PL].[OriginalQuantity_Stk]
            WHEN [PC].[ProductCategoryCode] <> 5730
                THEN [PL].[OriginalQuantity_Stk]
            WHEN [PC].[ProductCategoryCode] <> 5990
                THEN [PL].[OriginalQuantity_Stk]
            END) AS [Weight]
    ,@Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () AS [Shrink]
    ,CASE 
        WHEN [PC].[ProductCategoryCode] = 5870
            THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
        WHEN [PC].[ProductCategoryCode] = 5730
            THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
        WHEN [PC].[ProductCategoryCode] = 5990
            THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
        ELSE Sum([PL].[OriginalQuantity_Stk]) / @Credits
        END AS [Yield]
    ,CASE 
        WHEN [PC].[ProductCategoryCode] = 5870
            THEN [ICP].[UnitCost]
        WHEN [PC].[ProductCategoryCode] = 5730
            THEN [ICP].[UnitCost] - 0.25
        WHEN [PC].[ProductCategoryCode] = 5990
            THEN [ICP].[UnitCost] * 0
        ELSE [ICP].[UnitCost]
        END AS [UC]
    ,CASE 
        WHEN [PC].[ProductCategoryCode] = 5870
            THEN [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
        WHEN [PC].[ProductCategoryCode] = 5730
            THEN ([ICP].[UnitCost] - 0.25) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
        WHEN [PC].[ProductCategoryCode] = 5990
            THEN ([ICP].[UnitCost] * 0) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
        ELSE [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
        END AS [Value]
    ,SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (
        PARTITION BY (
            CASE 
                WHEN PC.ProductCategoryCode = 5780
                    OR [PC].[ProductCategoryCode] < 5700
                    THEN PC.ProductCategoryCode
                END
            )
        ) * ICP.UnitCost AS [TotalValue]
FROM (
    (
        (
            (
                IC_Products [PC] INNER JOIN DC_Transactions [DCT] ON [PC].ProductKey = [DCT].ProductKey
                ) INNER JOIN AR_Customers [ARC] ON [DCT].CustomerKey = [ARC].CustomerKey
            ) INNER JOIN IC_ProductLots [PL] ON [DCT].LotKey = [PL].LotKey
        ) LEFT JOIN IC_ProductCosts [ICP] ON ICP.ProductKey = PC.ProductKey
        AND ICP.ProductCostCode = 5
    )
WHERE (
        [PL].ProductionDate >= { ts '2015-07-10 00:00:00' }
        AND (
            [PL].ProductionDate <= { ts '2015-07-10 00:00:00' }
            OR [PL].ProductionDate IS NULL
            )
        )
    AND (
        (1 = 1)
        AND [ARC].CustomerKey IN (39)
        )
GROUP BY CASE 
        WHEN [PC].[ProductCategoryCode] = 5870
            THEN 'Trimmings'
        WHEN [PC].[ProductCategoryCode] = 5730
            THEN 'Rib Tips'
        WHEN [PC].[ProductCategoryCode] = 5990
            THEN 'Skins'
        ELSE [PC].[Description1]
        END
    ,[ARC].CustomerKey
    ,[PC].ProductCategoryCode
    ,CASE 
        WHEN [PC].[ProductCategoryCode] = 5870
            THEN [ICP].[UnitCost]
        WHEN [PC].[ProductCategoryCode] = 5730
            THEN [ICP].[UnitCost] - 0.25
        WHEN [PC].[ProductCategoryCode] = 5990
            THEN [ICP].[UnitCost] * 0
        ELSE [ICP].[UnitCost]
        END
    ,[ICP].UnitCost
    ,[PC].ProcessCode
ORDER BY @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER ()
    ,SUM(SUM(PL.OriginalQuantity_Stk)) OVER (
        PARTITION BY (
            CASE 
                WHEN PC.ProductCategoryCode IN (
                        5870
                        ,5730
                        ,5990
                        )
                    THEN PC.ProductCategoryCode
                END
            )
        )

Join 3 tables in Sql Server

I have 3 tables clalled tbl_monday, tbl_tuesday, tbl_Wednesday and consist of data as follows.

tbl_Monday

id    empid   Plan
---------------------
1     6       Mon_27
2     6       Mon_27
3     6       Mon_27

tbl_Tuesday

id    empid   Plan
--------------------
1     6       Tue_28
2     6       Tue_28
3     6       Tue_28     

tbl_Wenesday

id  empid     Plan
------------------
1     6       Wed_29
2     6       Wed_29
3     6       Wed_29

Is there any eazy way to get result like this ?

empid    Plan
----------------------
6       Mon_27
6       Tue_28
6       Wed_29

Trace exact column during error while debugging stored procedure

I work on support for a large and complex project based on SQL server 2005. It consists of more than 800 stored procedures and 1000 tables. Most SPs contain 2000+ lines and some have 10000+ lines of code. Each table contain more than 200 columns. Most of the SPs contain while loops, cursors, while loop in another cursor loop which is enormously complex. We use many temp tables, table variables with more than 200 columns I face difficulty when there is any error occurs. The most frequent errors are like below:

1. String or binary data would be truncated.
2. Arithmetic overflow error converting numeric to data type varchar .. and so on

It is extremely difficult to find out which column is occuring error. As the above error messages just show the error but not the actual column. Since there are more than 200 coloumns it has become a daunting task to find out the culprit. I have to comment/hardcode few columns and need to check if error is occuring or not.

Please find an example below for better understanding of my issue:

DECLARE @tablevar TABLE( Column1 int, column2 char(2),column char(6),.....
......,**Column137 char(7)**,..............column199 char(7), column(200) char(6))

......some code...

--while loop

while i < count  

Begin

--some code

Insert into @tablevar(Column1, column2,column3,.....
.....,**column137**...............column199, column200))

select t1.OthertableColumn1,t1.OthertableColumn2,t2.OthertableColumn3,t3.Othertablecolumn4,...
....,**t2.OthertableColumn137**,....

so on..........

t2.Column200)

from OtherTable t1 with (nolock)

LEFT JOIN OtherTable2 t2 with (nolock) on t1.col1 = t2.Column1

LEFT JOIN OtherTable3 t3 with (nolock) on t3.col1 = t2.Column1

LEFT JOIN OtherTable4 t4 with (nolock) on t4.col1 = t3.Column1

LEFT JOIN OtherTable5 t5 with (nolock) on t5.col1 = t1.Column1

LEFT JOIN OtherTable6 t6 with (nolock) on t6.col2 = t4.Column1

....
End

The actual error is occurring with the column Column137. The size of the column column137 of the table variable @tablevar is 7 characters but it is inserting more characters, for e.g. 'sample data'. So throwing the error - String or binary data would be truncated.

How do I identify this Column137 out of that large number of 200 columns? Is there any way of finding the specific column name where the error is occuring? Is there any shortcut/simple way to find out exact column? Your help will be appreciated.

insert column into table and increment row by one

Given the following tables. I am trying to increment by one the current value of the eventid column highest value. So max(eventid) + 1???. I cannot seem to get the right SQL syntax to accomplish this.

What I have right now that works but does not give me what I need is the following. How would I get the eventid to show, in this case, 96740?

INSERT INTO stock_history
            (lastmodby,
             event,
             previous_stock,
             new_stock,
             lastmodified,
             productid)
SELECT '160'                     AS lastmodby,
       'SALE'                    AS event,
       stockstatus               AS previous_stock,
       stockstatus + 1           AS new_stock,
       Getdate()                 AS lastmodified,
       products_joined.productid AS productid
FROM   products_joined
WHERE  productcode = 'abc' 

stock_history table

+--------+-----------+----------------+-------+-----------+-------+---------+-----------------------+-----------+
|   id   | productid | previous_stock | count | new_stock | event | eventid |     lastmodified      | lastmodby |
+--------+-----------+----------------+-------+-----------+-------+---------+-----------------------+-----------+
| 105619 |      9282 |              9 |     1 |        10 | SALE  |         | 7/24/2015 5:29:00 PM  |       160 |
| 105578 |      9282 |              8 |     1 |         9 | ORDER |   96739 | 7/23/2015 7:30:00 PM  |     37655 |
|  89241 |      9282 |              7 |     1 |         8 | ORDER |   96738 | 6/1/2014 6:06:00 PM   |     30761 |
|  86773 |      9282 |              6 |     1 |         7 | ORDER |   96737 | 4/12/2014 4:36:00 PM  |     29745 |
|  70419 |      9282 |              5 |     1 |         6 | ORDER |   96736 | 5/21/2013 1:17:00 PM  |      1754 |
|  69088 |      9200 |             19 |     1 |        20 | EDIT  |   96735 | 4/28/2013 10:26:00 AM |      1754 |
|  69050 |      9200 |             18 |     1 |        19 | ORDER |   96734 | 4/27/2013 2:17:00 PM  |     23001 |
|  68127 |      9200 |             17 |     1 |        18 | ORDER |   96733 | 4/13/2013 12:34:00 PM |     22674 |
|  67064 |      9200 |             16 |     1 |        17 | ORDER |   96732 | 3/30/2013 9:23:00 AM  |     22327 |
+--------+-----------+----------------+-------+-----------+-------+---------+-----------------------+-----------+

products_joined table

+-------------+-----------+-------------+
| productcode | productid | stockstatus |
+-------------+-----------+-------------+
| abc         |      9282 |           9 |
| xyz         |      9200 |          19 |
+-------------+-----------+-------------+

How to get the RAISERROR message from Stored Procedure in sql server in zend framework 2

I am executing sql server stored procedures, and in output i am getting two things result and message when i execute it from Sql Server management studio. But i don't know how do i get that message in zend framework 2 when i am executing the stored procedure?

  class myController{
     public function indexAction() {   
        $getResource = $this->fetchTransStatus('123');
        $obj = sqlsrv_fetch_array($getResource, SQLSRV_FETCH_ASSOC);
        // $obj will give the result coming from database,
        // but i am not getting the message coming from stored procedure
        // which i it is sending through RAISERROR 
     }
     public function fetchTransStatus($data = null)
     {
         $procedure = 'procedure_name';
         $params = " @param1 = $data, @param2 = 1";
         $stmt = $this->tableGateway->getAdapter()->createStatement();
         $stmt->prepare("EXEC $procedure $params");
         $result = $stmt->execute();
         return $result->getResource();
      }
  }

How will i get the RAISERROR message coming from stored procedure in zend framework 2?

SQL 2005 Deattach database and attach a new one with same name

I need some help to script and schedule some tasks to do in sql 2005. I have One database to be backed up full and I need back the space used by this database. Manually I do the deattach of the database (I don´t need to keep the connections to the database because its a backup database) Then I compress this both files (mdf and ldf) and finally it´s sended to the respective folder. I have one database template with the same data (purged) of this database with the minimum space. (2048kb) I attach this file and the database it`s online again. I want to do this process (all of them) by a scheduled task in a script to do all. If I need to write sqlcmd or windows schedule process I have no problem. I dont want to use the full backup and clear the old backups of the dbms because I dont have the way in SQL 2005 to compress the old data and get a new database clear.

Thank you in advance.

SQL - Creating a Grouped 'range' set

I have a table of support tickets - with time opened and time closed. I would like to create a table of ranges, as such:

 ticket count   |    time to close 
----------------------------------
        30      |        up to 2 hours
        25      |         2 - 4 hours
        10      |         4 - 6 hours

what i have so far gives me the range (using a CASE with DATEDIFF), but i cant figure out how to group the eventual range. When trying to GROUP on the new openTimeRange computed column, the error of course is that its an unknown column.

SELECT COUNT([tblTickets].*),  DATEDIFF(hh,[dateOpened],[closeDate]) AS OpenTime

, case when  DATEDIFF(hh,[dateOpened],[closeDate]) between 0 and 2 then '0-2'
     when  DATEDIFF(hh,[dateOpened],[closeDate]) between 3 and 4 then '3-4'
     when  DATEDIFF(hh,[dateOpened],[closeDate]) between 5 and 6 then '4-6'
end as openTimeRange

FROM  [tblTickets]
WHERE closeDate is not null 
GROUP BY  [dateOpened],[closeDate] 

Using MSSQL 2005 SP4

Thanks!

Show comma instead of point as decimal separator

I just want to get the right number format here in germany, so i need to show commas as decimal separator instead of points. But this...

DECLARE @euros money
SET @euros = 1025040.2365
SELECT CONVERT(varchar(30), @euros, 1)

Displays 1,025,040.24 instead of 1.025.040,24 (or 1025040,24). In C# it would be simple to provide the appropriate CultureInfo but how to do it in T-SQL?

Do i really need to use REPLACE? But even if, how to replace 1,025,040.24 correctly?

Alias Logic in Sql Server 2005

I have always find problem while using alias. Actually I want to use table alias that means a complex type of alias. I am not sure if my requirement is right or I am doing in the right way it is suppose to but yet if you can clear my doubts on how we can select something using alias on a query would be much appreciated. Please ignore my english language.

This is the Query

select * from TBL where lng_TaskId not in (select lng_TaskId from Mask_Tasks MT inner join Mask_TaskCategories MC on
MT.lng_TaskCatID = MC.lng_taskCatid and MT.lng_MajEqId = MC.lng_MajEqId)TBL

So, I have a sub query which will run first to give me some ID's. Now I want to use it an alias and then want to select from this alias in my outer or main query. Can you please explain how to use alias in this way or any other better way to use an alias in SQL server. I will really be thankful to you.

Joins with aggregates doubling, sometimes tripling quantity amounts

I'm trying to join 4 tables to get several columns of results, two of which are sums/aggregates of their respective columns. My query is returning multiples of what the true sums should be. Here is what I have:

select pl.[Vendor Item No_], bc.[Item No_], min(ile.[Description]) as 'Item Description',
        sum(ile.[Quantity]) as 'Quantity On Hand', 
        bc.[Bin Code] as 'Item Location'
 from [live$Bin Content]bc left outer join [live$purchase line]pl
  on bc.[Item No_] = pl.[No_]left outer join [live$item ledger entry] ile
  on bc.[Item No_] = ile.[Item No_]
where bc.[Bin Code] like 'ANNEX BACK'
and   bc.[Item No_] like 'sk%'


group by pl.[Vendor Item No_], bc.[Item No_], pl.[Description], bc.[Bin Code]

Calculating TOTAL of certain items using CASE and OVER

I need to calculate the total value of specific product codes. The product codes are 5780 and everything below 5700.

To calculate the value I use the code:

CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk])  / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode] )
WHEN [PC].[ProductCategoryCode] = 5730 THEN ([ICP].[UnitCost] - 0.25) * SUM(Sum([PL].[OriginalQuantity_Stk])  / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode] )
WHEN [PC].[ProductCategoryCode] = 5990 THEN ([ICP].[UnitCost] * 0 ) * SUM(Sum([PL].[OriginalQuantity_Stk])  / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode] )
ELSE [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk])  / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode] )
END

The ELSEpart is what relates to the product codes I need for this section of the report.

I tried using two different formulas and they both return different amounts but not the correct amount.

One is:

CASE WHEN [PC].[ProductCategoryCode] = 5780 OR [PC].[ProductCategoryCode] < 5700 THEN SUM(Sum([PL].[OriginalQuantity_Stk])  / @Credits * [ICP].[UnitCost] )
OVER ()
END

And the other is:

SUM(Sum([PL].[OriginalQuantity_Stk])  / @Credits * [ICP].[UnitCost]) OVER
       (PARTITION BY (CASE WHEN [PC].[ProductCategoryCode] IN (5870, 5730, 5990)
                           THEN [PC].[ProductCategoryCode] 
                           END) 
       )  

They group the product codes correctly by displaying the same number for each, but the calculated number is incorrect.

Sample output:

    Product Category Code | Value  |
    5870                  | 100    |
    5730                  | 400    |
    5990                  | 200    |
    5780                  | 200    |
    1111                  | 50     |  
    2222                  | 175    |  
    3333                  | 500    |  
    4444                  | 125    |  

    Total Value: 1050 
It has to ignore product code 5870, 5990, and 5730 and not include 
those in the total value.

I am using Microsoft SQL Sever 2005. Let me know if I need to explain further.

Whole Code:

SET NOCOUNT ON; 
DECLARE @Credits Decimal(19,8);
DECLARE @Price Decimal(19,8);

SET @Credits = 41000;
SET @Price = 1.57;

SELECT 
     CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings' 
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1] 
END AS [Description]
   , SUM(SUM(PL.OriginalQuantity_Stk)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
) AS [Total Weight]
   , Sum(CASE WHEN [PC].[ProductCategoryCode] <> 5870 THEN [PL].[OriginalQuantity_Stk] 
WHEN [PC].[ProductCategoryCode] <> 5730 THEN [PL].[OriginalQuantity_Stk] 
WHEN [PC].[ProductCategoryCode] <> 5990 THEN [PL].[OriginalQuantity_Stk] 
END) AS [Weight]
   , @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () AS [Shrink]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode]) 
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE Sum([PL].[OriginalQuantity_Stk]) / @Credits
END AS [Yield]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] 
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25 
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0 
ELSE [ICP].[UnitCost]
END AS [UC]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5730 THEN ([ICP].[UnitCost] - 0.25) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN ([ICP].[UnitCost] * 0) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
END AS [Value]
   , 
 **SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits * [ICP].[UnitCost]) OVER
 (PARTITION BY (CASE WHEN [PC].[ProductCategoryCode] IN (5870, 5730, 5990)
 THEN [PC].[ProductCategoryCode] 
                         END) 
) AS [TotalValue]**
 FROM (((( IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey)
    INNER JOIN  AR_Customers [ARC] 
     ON [DCT].CustomerKey = [ARC].CustomerKey)
    INNER JOIN  IC_ProductLots [PL] 
     ON [DCT].LotKey = [PL].LotKey)
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
 WHERE 
    ([PL].ProductionDate >= { ts '2015-07-10 00:00:00' }   AND ([PL].ProductionDate <= { ts '2015-07-10 00:00:00' } OR [PL].ProductionDate Is Null)) 
AND ((1=1)  AND [ARC].CustomerKey IN (39) ) 
 GROUP BY 
     CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings' 
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1] 
END
   , [ARC].CustomerKey
   , [PC].ProductCategoryCode
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] 
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25 
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0 
ELSE [ICP].[UnitCost]
END
   , [ICP].UnitCost
   , [PC].ProcessCode
 ORDER BY 
     @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () 
   , SUM(SUM(PL.OriginalQuantity_Stk)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
)

*Part of code I need assistance with.

SQL query to get the data not including latest versions

I'm working in SQL Server 2012. I have a table which contains below 2 columns

table, th, td {
    border: 1px solid black;
     border-collapse: collapse;
}
th, td {
    padding: 15px;
}

<table style="width:100%">
   <tr>
    <th>ID</th>
     <th>Version</th> 
   </tr>
  <tr>
    <td>1</td>
     <td>0</td> 
   </tr>
   <tr>
    <td>1</td>
     <td>1</td> 
   </tr>
   <tr>
    <td>1</td>
     <td>2</td> 
   </tr>
   <tr>
    <td>1</td>
     <td>3</td> 
   </tr> 
  <tr>
    <td>2</td>
     <td>0</td> 
   </tr>
   <tr>
    <td>2</td>
     <td>1</td> 
   </tr>
   <tr>
    <td>2</td>
     <td>2</td> 
   </tr>
   <tr>
    <td>3</td>
     <td>0</td> 
   </tr>
   <tr>
    <td>3</td>
     <td>1</td> 
   </tr>
</table>

I want to get only the id which are having more than 2 versions and not include the latest 2 versions.

table, th, td {
    border: 1px solid black;
     border-collapse: collapse;
}
th, td {
    padding: 15px;
}

<table style="width:100%">
   <tr>
    <th>Id</th>
     <th>Version</th> 
   </tr>
  <tr>
    <td>1</td>
     <td>0</td> 
   </tr>
  <tr><td>1</td>
  <td>1</td></tr>
  <tr><td>2</td>
  <td>0</td></tr>
</table> 

How to write the query?

SQL Server column alias has no effect

I'm trying to rename a column name in order to aid mapping data from this database into another for a data push.

What I need to do is change the name of StdType to IncomeType, although no matter what I try it simply does not change the column name in the result.

This is my SQL query:

SELECT
    'AA' + CAST(ClientID AS VARCHAR) AS AAID, 
    Description, 
    Convert(Money,Amount)/100 AS Amount, 
    Note, 
    StdType FROM [Finstat_Income] AS IncomeType
INNER JOIN #Assessments A
    ON 'AA' + CAST(ClientID AS VARCHAR) = A.AAID

#Assessments is a temporary table which does exist and works for my other queries.

Finstat_Income is a table and not a view. I've also tried the query with and without square brackets but there is no difference.

The resulting output is headed as:

AAID | Description | Amount | Note | StdType
...

Where my desired output is:

AAID | Description | Amount | Note | IncomeType

Create sequence partitioned by multiple columns and contiguous date

I'm trying to figure out how to create a sequence partitioned by multiple columns, where the sequence must reset once another (date based) column is not contiguous.

Problem: Hospital ADT (Admission/Discharge/Transfer) events occur at a particular point in time, but we want to turn these events into activities which have a duration (timespan), i.e. we have the start date, but don't have the end date, which is based on the next appropriate ADT event. We have done this in code, but also want to do it in SQL to improve performance. e.g. find patients who have spent more than 48 hours in ICU.

There are six different levels of site locations we want to record the duration of: facility, point of care, building, floor, room and bed.

Example:

Stream  Event  Started           Facility    PointOfCare  ...
1       1      2015-01-01 09:05  Hospital-A  ICU           
1       2      2015-01-02 13:10  Hospital-A  WARD-1
2       3      2015-02-10 12:00  Hospital-A  ICU           
2       4      2015-02-11 12:00  Hospital-A  ICU
2       5      2015-02-12 04:30  Hospital-A  WARD-2

So for each event we want to know how long they were in each particular site location. The end dates of the last activity in each stream are either null (still an inpatient) or the date patient was discharged.

Here's my current solution:

-- Create a sequence for each site location
INSERT INTO ADT_Activity_Sequence
SELECT 
  [Stream], 
  [Event],
  [Started],
  [Facility], 
  ROW_NUMBER() OVER (PARTITION BY [Stream], 
    ISNULL([Facility], [Event]) 
    ORDER BY [Started]) AS [FacilitySequence], 
  [PointOfCare], 
  ROW_NUMBER() OVER (PARTITION BY [Stream], 
    ISNULL([Facility], [Event]), 
    ISNULL([PointOfCare], [Event]) 
    ORDER BY [Started]) AS [PointOfCareSequence]
  -- and so on for all site locations
FROM ADT_Event
INNER JOIN ADT_Stream ON ADT_Event.Stream = Stream.Id

Example:

Stream  Event  Started           Facility    FacilitySequence  PointOfCare  PointOfCareSequence ...
1       1      2015-01-01 09:05  Hospital-A  1                 ICU          1 
1       2      2015-01-02 13:10  Hospital-A  2                 WARD-1       1
2       3      2015-02-10 12:00  Hospital-A  1                 ICU          1
2       4      2015-02-11 12:00  Hospital-A  2                 ICU          2
2       5      2015-02-12 04:30  Hospital-A  3                 WARD-2       1

Then create duration from the sequences:

INSERT INTO ADT_Activity_Duration
SELECT 
    [Stream], 
    [Event],
    [Started],
    [Facility], 
    [Sequence].[FacilitySequence],
    (
        -- Find most recent activity which is the first in current sequence
        SELECT TOP 1 [FacilitySequence].[Started] 
        FROM [ADT_Activity_Sequence] [FacilitySequence]
        WHERE [FacilitySequence].[Stream] = [Event].[Stream] AND [FacilitySequence].[FacilitySequence] = 1 AND [FacilitySequence].[Started] <= [Event].[Started]
        ORDER BY [FacilitySequence].[Started] DESC
    ) AS [FacilityStarted],
    (
        -- Find first activity in next sequence as this activities end date
        -- Last activity returns null, so activity uses stream end date if set
        ISNULL((                
            SELECT TOP 1 [FacilitySequence].[Started]
            FROM [ADT_Activity_Sequence] [FacilitySequence]
            WHERE [FacilitySequence].[Stream] = [Event].[Stream] AND [FacilitySequence].[FacilitySequence] = 1 AND [FacilitySequence].[Started] > [Event].[Started]
            ORDER BY [FacilitySequence].[Started]), [Stream].[Ended])
    ) AS [FacilityEnded],
    [PointOfCare], 
    [Sequence].[PointOfCareSequence],
    (
        SELECT TOP 1 [PointOfCareSequence].[Started] 
        FROM [ADT_Activity_Sequence] [PointOfCareSequence]
        WHERE [PointOfCareSequence].[Stream] = [Event].[Stream] AND [PointOfCareSequence].[PointOfCareSequence] = 1 AND [PointOfCareSequence].[Started] <= [Event].[Started]
        ORDER BY [PointOfCareSequence].[Started] DESC
    ) AS [PointOfCareStarted],
    (
        ISNULL((
            SELECT TOP 1 [PointOfCareSequence].[Started]
            FROM [ADT_Activity_Sequence] [PointOfCareSequence]
            WHERE [PointOfCareSequence].[Stream] = [Event].[Stream] AND [PointOfCareSequence].[PointOfCareSequence] = 1 AND [PointOfCareSequence].[Started] > [Event].[Started]
            ORDER BY [PointOfCareSequence].[Started]), [Stream].[Ended])
    ) AS [PointOfCareEnded]
    -- and so on for all site locations
FROM ADT_Event AS [Event]
INNER JOIN [ADT_Stream] AS [Stream] ON [Event].[Stream] = [Stream].[Id]
INNER JOIN [ADT_Activity_Sequence] [Sequence] ON [Event].[Id] = [Sequence].[Event]

Example:

Stream  Event  Started           Facility    FacilitySequence  FacilityStarted  FacilityEnded     PointOfCare  PointOfCareSequence  PointOfCareStarted  PointOfCareEnded  ...
1       1      2015-01-01 09:05  Hospital-A  1                 2015-01-01 09:05 2015-01-03 12:00  ICU          1                    2015-01-01 09:05    2015-01-02 13:10  
1       2      2015-01-02 13:10  Hospital-A  2                 2015-01-01 09:05 2015-01-03 12:00  WARD-1       1                    2015-01-02 13:10    2015-01-03 12:00
2       3      2015-02-10 12:00  Hospital-A  1                 2015-02-10 12:00 <NULL>            ICU          1                    2015-02-10 12:00    2015-02-12 04:30
2       4      2015-02-11 12:00  Hospital-A  2                 2015-02-10 12:00 <NULL>            ICU          2                    2015-02-10 12:00    2015-02-12 04:30
2       5      2015-02-12 04:30  Hospital-A  3                 2015-02-10 12:00 <NULL>            WARD-2       1                    2015-02-12 04:30    <NULL>

My problem lies when the contiguous date sequence is broken, which happens when a patient is transferred from any site location, and then transferred back again, all within the same stream:

Stream  Event  Started           Facility    PointOfCare  ...
3       1      2015-03-01 09:05  Hospital-A  ICU           
3       2      2015-03-02 13:10  Hospital-A  WARD-1
3       3      2015-03-02 10:00  Hospital-A  ICU           

Example:

Stream  Event  Started           Facility    FacilitySequence  PointOfCare  PointOfCareSequence ...
3       1      2015-03-01 09:05  Hospital-A  1                 ICU          1 
3       2      2015-03-02 13:10  Hospital-A  2                 WARD-1       1
3       3      2015-03-02 10:00  Hospital-A  3                 ICU          2

Note event #3 has a point of care sequence of 2, which is incorrect, it needs to be reset back to 1 due to event #2 being in a different location.

I've been going around in circles for a while now :) so any help appreciated, thanks!

SQL Server : Tracking a slow running query on completion

Am trying to work on script for slow-running queries. I understand that we can get the list of slow running queries using sys.dm_exec_requests. Say, I have a query Q1 which is running very slow, say, 1 hour. Till Q1 is running i.e. executing, I can track it using sys.dm_exec_requests DMV. If I understood it right, once Q1 is completed, its statistics will be no more available in this DMV. Instead, we need to look at sys.dm_exec_query_stats. But how do I get the statistics of that specific query Q1 which had been slow-running ? Meaning how do I relate the data I earlier got in sys.dm_exec_requests and sys.dm_exec_query_stats?

How to execute a stored procedure in php using sqlsrv and "?" style parameters

I've looked over several other questions that seem (from the titles) the same as this. However, my case is a bit different.

The following works (i.e. I get "success" and my database performs what I expect when running the procedure with the given variables):

$sql = "MyDB.dbo.myProcedure {$var1}, {$var2}, {$var3}";
$result = sqlsrv_query($myConn, $sql);
if (!$result) {
    echo 'Your code is fail.';
}
else {
    echo 'Success!';
}

I want to avoid (or lessen the possibility of) SQL injection by creating the SQL string using parameters. For example:

$sql = "select * from aTable where col1 = ? AND col2 = ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2));
//please note. This code WILL work!

But when I do that with a stored procedure it fails. It fails with no errors reported via sqlsrv_errors(), no action taken in database, and $result === false.

To be clear, the following fails:

$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2, $var3));

Likewise a prepare/execute statement created the same way will also fail:

$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$stmt = sqlsrv_prepare($myConn, $sql, array(&$var1, &$var2, &$var3));
foreach($someArray as $key => $var3) {
    if(sqlsrv_execute($stmt) === false) {
        echo 'mucho fail.';
    }
}
//this code also fails.

For completeness, I have confirmed that the stored procedure in question works directly within SQL Management Studio AND if called the way I mentioned above. Likewise, I have confirmed that I can use parameterized queries for any raw query (like an insert, select, update vs a stored procedure).

So, my question is how can I call a stored procedure using the parameterized query vs embedding the variables in the query string?

More importantly, I am actually wanting to use a prepare/execute, so hopefully the answer will allow this to work as well.

How to make a model for joined tables?

I have this query that gets data from SQL Server 2005.

SELECT DocumentNbr AS [Whole Nbr], DocumentType AS [Type], 
        DrawingSize AS [Size], DocumentRevision AS [Revision],
        OtherSheetNbr AS [Other Nbr], SheetNbr AS [Sheet Nbr], 
        NbrOfSheets AS [Of Sheets], FrameNbr AS [Frame Nbr], 
        NbrOfFrames AS [Of Frames], DocumentTitle AS [Doc Title], 
        VolumeLabel AS [Volume], CASE WHEN VolumeNote IS NULL THEN '' ELSE ' * ' END AS [Note], 
        CASE WHEN Blueprint = 1 THEN 'Yes' ELSE 'No' END AS Prnt, CASE WHEN ObsoleteCards = 1 THEN 'Yes' ELSE 'No' END AS Obs, 
        CASE WHEN AcquisitionDate IS NULL THEN tblImage.CreationDate ELSE AcquisitionDate END AS [Acquire Date],
        Mnemonic AS [Source], FilePath, FileName, FileExtension, 
        CASE WHEN IsBaseDoc = 1 THEN 'Yes' ELSE 'No' END AS [Base Doc], 
        AccDocNbr AS [Acc Doc Nbr], CommonSubDirectory, 
        ImageID, tblImage.VolumeID, tblImage.VolumeTypeID, tblVolume.VolumeSourceID, LatestImage 
    FROM ((tblVolume INNER JOIN tblVolumeSource ON tblVolume.VolumeSourceID = tblVolumeSource.VolumeSourceID) 
        INNER JOIN tblVolumeType ON tblVolume.VolumeTypeID = tblVolumeType.VolumeTypeID) 
        INNER JOIN tblImage ON tblImage.VolumeID = tblVolume.VolumeID 
    WHERE DocumentNbr = 'ss9220'

Which will return more than one row. I can't find documentation on the all method (or the best way to join multiple tables). Can I use the all method to return more than one row passing it the DocumentNbr?

Do I make a model for each table and use the belongs_to or has_one in a separate model?

Can I have a model that does not map to a table?

What is the best way to handle the aliases? The above query was taken from an Access form. I am converting the Access form to a rails app. This is my first rails app and I'm having a difficult time learning the 'rails' way. I have done 3 tutorials but they are all basic and do not reflect the real world issues I must deal with.

Database schema:

CREATE TABLE [dbo].[tblImage](
    [ImageID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Image_ImageID]  DEFAULT (newid()),
    [VolumeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblImage_VolumeID]  DEFAULT (newid()),
    [VolumeTypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblImage_VolumeTypeID]  DEFAULT (newid()),
    [DocumentNbr] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CAGE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DocumentType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DrawingSize] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DocumentRevision] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [RevisionDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    
    [DocumentTitle] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OtherSheetNbr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SheetNbr] [numeric](10, 0) NULL,
    [NbrOfSheets] [numeric](10, 0) NULL,
    [SheetRevision] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FrameNbr] [numeric](5, 0) NULL,
    [NbrOfFrames] [numeric](5, 0) NULL,
    [FileType] [int] NULL,
    [FileTypeFormat] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SourceFlavor] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DestinationFlavor] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FileTypeContent] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FileTypeVersion] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DFISSourceCAGE] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FileName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FileExtension] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FilePath] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MediaVolumeID] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MajorGroup] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MinorGroup] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SecurityLevel] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Rights] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ForeignSecure] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Image_Foreign/Secure]  DEFAULT (0),
    [Nuclear] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Image_Nuclear]  DEFAULT (0),
    [SubSafe] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Image_SubSafe]  DEFAULT (0),
    [AirType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [APL] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CADInfo] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ControlCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HSC] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NSN] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [UIC] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [System] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Nomenclature] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ShipClass] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ShipTypeHullNbr] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MasterLocation] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OfflineLocation] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ParentCAGE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ParentDocNbr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PartNbr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SubSheet] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SucceedingDrwg] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Distribution] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AccDocKind] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AccDocNbr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AccDocCAGE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AccDocRev] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AccDocSheet] [numeric](18, 0) NULL,
    [AccDocFrame] [numeric](18, 0) NULL,
    [WeaponSystem] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DFISVersion] [numeric](18, 0) NULL,
    [AltDFISVersion] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [IsBaseDoc] [bit] NULL CONSTRAINT [DF_Image_IsBaseDoc]  DEFAULT (1),
    [HasAccDoc] [bit] NULL CONSTRAINT [DF_Image_HasAccDoc]  DEFAULT (0),
    [CDFileName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CDFilePath] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CreationDate] [datetime] NULL,
    [LatestImage] [bit] NOT NULL CONSTRAINT [DF_tblImage_LatestImage]  DEFAULT (0),
    [ReadAccessCounter] [int] NOT NULL CONSTRAINT     DF_tblImage_ReadAccessCounter]  DEFAULT (0),
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED  (     [ImageID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

CREATE TABLE [dbo].[tblVolume](
    [VolumeID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Volume_VolumeID]  DEFAULT (newid()),
    [VolumeLabel] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [VolumeTypeID] [uniqueidentifier] NOT NULL,
    [VolumeSourceID] [uniqueidentifier] NOT NULL,
    [SolicitationID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ItemID] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ItemRevision] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [QuoteID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SalesOrderID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CreationDate] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AcquisitionDate] [datetime] NULL,
    [LoadedDate] [datetime] NULL,
    [LoadedBy] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ImageCount] [int] NOT NULL CONSTRAINT [DF_Volume_ReadAccessCounter]  DEFAULT (0),
    [LastReadDate] [datetime] NULL,
    [OnlineFlag] [bit] NOT NULL CONSTRAINT [DF_Volume_OnlineFlag]  DEFAULT (1),
    [Path] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [VolumeNote] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ObsoleteCards] [bit] NOT NULL CONSTRAINT [DF_tblVolume_ObsoluteCards]  DEFAULT (0),
    [Blueprint] [bit] NOT NULL CONSTRAINT [DF_tblVolume_Blueprint]  DEFAULT (0),
     CONSTRAINT [PK_Volume] PRIMARY KEY CLUSTERED 
([VolumeID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[tblVolumeSource](
    [VolumeSourceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_VolumeSource_VolumeSourceID]  DEFAULT (newid()),
    [Mnemonic] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Location] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [IndexFileName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [VolumeIDFileName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FormatName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CD] [bit] NOT NULL CONSTRAINT [DF_tblVolumeSource_CD]  DEFAULT (0),
    [AC] [bit] NOT NULL CONSTRAINT [DF_tblVolumeSource_AC]  DEFAULT (0),
    [Internet] [bit] NOT NULL CONSTRAINT [DF_tblVolumeSource_Internet]  DEFAULT (0),
    [RFQ] [bit] NOT NULL CONSTRAINT [DF_tblVolumeSource_RFQ]  DEFAULT (0),
 CONSTRAINT [PK_VolumeSource] PRIMARY KEY CLUSTERED (
    [VolumeSourceID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

CREATE TABLE [dbo].[tblVolumeType](
    [VolumeTypeID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_VolumeType_VolumeTypeID]  DEFAULT (newid()),
    [TypeName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TypeDesc] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CommonSubDirectory] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CDLoader] [bit] NOT NULL CONSTRAINT [DF_tblVolumeType_CDLoader]  DEFAULT (0),
    [ACLoader] [bit] NOT NULL CONSTRAINT [DF_tblVolumeType_ACLoader]  DEFAULT (0),
 CONSTRAINT [PK_VolumeType] PRIMARY KEY CLUSTERED ( [VolumeTypeID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

SQL Calculating Values Using CASE

I am using CASE to calculate the total weight of an item based on the product category.

To do that I am using the code:

CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode]) 
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE SUM(SUM([PL].[OriginalQuantity_Stk])) OVER()
END
 AS [Total Weight]

5870, 5730, 5990 all display the correct total weight. The problem comes in with the ELSE part. The rest of the values all have different product category codes but they need to be grouped together and I need the total weight of those. Currently, the ELSE part is taking the total of all the items, including product 5870, 5730, 5990.

For example:

Product Category Code | Weight |
5870                  | 100    |
5730                  | 400    |
5990                  | 200    |
1111                  | 50     |  
2222                  | 175    |  
3333                  | 500    |  
4444                  | 125    |  

5870 = 100
5730 = 400
5990 = 200
ELSE = 850

Whole Code:

SET NOCOUNT ON; 
DECLARE @Credits Decimal(19,8);
DECLARE @Price Decimal(19,8);

SET @Credits = 41000;
SET @Price = 1.57;

SELECT 
     CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings' 
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1] 
END AS [Description]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode]) 
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE SUM(SUM([PL].[OriginalQuantity_Stk])) OVER()
END
 AS [Total Weight]
   , Sum(CASE WHEN [PC].[ProductCategoryCode] <> 5870 THEN [PL].[OriginalQuantity_Stk] 
WHEN [PC].[ProductCategoryCode] <> 5730 THEN [PL].[OriginalQuantity_Stk] 
WHEN [PC].[ProductCategoryCode] <> 5990 THEN [PL].[OriginalQuantity_Stk] 
END) AS [Weight]
   , @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () AS [Shrink]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode]) 
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE Sum([PL].[OriginalQuantity_Stk]) / @Credits
END AS [Yield]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] 
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25 
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0 
ELSE [ICP].[UnitCost]
END AS [UC]
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5730 THEN ([ICP].[UnitCost] - 0.25) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN ([ICP].[UnitCost] * 0) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
END AS [Value]
   , CASE WHEN [PC].[ProductCategoryCode] = 5780 OR [PC].[ProductCategoryCode] < 5700 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits * [ICP].[UnitCost])
OVER (PARTITION BY [PC].[ProductCategoryCode])
END AS [TotalValue]
 FROM (((( IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey)
    INNER JOIN  AR_Customers [ARC] 
     ON [DCT].CustomerKey = [ARC].CustomerKey)
    INNER JOIN  IC_ProductLots [PL] 
     ON [DCT].LotKey = [PL].LotKey)
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
 WHERE 
    ([PL].ProductionDate >= { ts '2015-07-10 00:00:00' }   AND ([PL].ProductionDate <= { ts '2015-07-10 00:00:00' } OR [PL].ProductionDate Is Null)) 
AND ((1=1)  AND [ARC].CustomerKey IN (39) ) 
 GROUP BY 
     CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings' 
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1] 
END
   , [ARC].CustomerKey
   , [PC].ProductCategoryCode
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] 
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25 
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0 
ELSE [ICP].[UnitCost]
END
   , [ICP].UnitCost
   , [PC].ProcessCode
 ORDER BY 
     @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () 
   , CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode]) 
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE SUM(SUM([PL].[OriginalQuantity_Stk])) OVER()
END

I am using Microsoft SQL Sever 2005.

Connecting to SQL Server from Linux Box using Integrated Authentication

I am trying to connect to SQL Server from Linux box through Mule. Is it possible to use Integrated authentication ? If so should i use jtds or Microsoft SQL Driver ? : My Authentication scheme (select auth_scheme from sys.dm_exec_connections where session_id=@@spid) in SQL Server says NTLM. So will it accept java Kerebros intgerated auth from Linux ? Please advice. Thanks!

body_format not working when specified in cursor

I am using the following code to grab values from an "email_address" field, and sending an email message to those recipients found in the table. I previously had emails sending in html format with the sp_send_mail, but now in this new script, the email sends to the right people, but it is just showing up as plain text.

    DECLARE
  @email VARCHAR(64),
  @format VARCHAR(20),
  @emailSubject varchar(128),
  @emailBody varchar(128)
  DECLARE c1 CURSOR FOR 
    SELECT 
      email_address
    FROM customeremailtest
    WHERE id > 0

  OPEN c1
  FETCH NEXT FROM c1 INTO @email
  WHILE @@FETCH_STATUS <> -1
    BEGIN
    SELECT 
      @format = 'HTML',
      @emailSubject = 'Please send Feedback for Order#',
      @emailBody = '<img src="http://ift.tt/1GDw3Y1" />'

      --you cannot concatenate strings  as the parameters for a query, they must be eitehr a static string or an already built variable.
      EXEC msdb.dbo.sp_send_dbmail 
      @profile_Name ='P21 Alert',
       @recipients= @email ,
       @subject = @emailSubject,
       @body = @emailBody,
       @body_format = @format


    FETCH NEXT FROM c1 INTO @email
    END
  CLOSE c1
  DEALLOCATE c1

What am I doing wrong?

Working out a percentage group

I'm essentially trying to work out which percentage value (e.g. 1 to 100) a range of numbers fall into.

For example, if I have a simple table of 500 records:

declare @temp table (id int, percentage int)

insert @temp (id)
select Number
from master..spt_values
where type = 'p' 
and number between 1 and 500

select * from @temp

Then I'd like to work out that the first 1% of the records (i.e. those with id 1 to 5) will be be percentage number 1, the second 1% of records (i.e. those with id 6 to 10) will be number 2 etc.

I'm seen that some of the more recent versions on sql server have some percentage based functions like PERCENTILE and PERCENT_RANK which I think might have been a possibility, however I'm using SQL Server 2005 so not sure if my options are limited?

Is there an easy way to do this that I'm missing?

Thanks!

Converting Legacy MSSQL 2005 Store Procedure to Transaction based for parallelism

My task is to convert a legacy Stored Procedure written in SQL Server 2005 so that it can be used in parallel (multiple executions from different users). As of now the SP is designed and written to run one instance at a time. If other people have to call it then they have to wait for the first execution to be completed.

The SP is not using any sort of transactions or isolation levels or locks on resources.

The code is about 8000 lines. But it is not a single SP. It is a collection of many SPs, functions, Table Valued Functions over tables and views. The execution starts from a single parent SP and then based on the huge list parameters provided, around 50 different SPs get called during the execution. The call hierarchy can go to 3 to 4 levels deep in some cases.

The SP works over a bunch of tables (empty in the beginning) and fills it with data by calculations done over existing data in other tables and at the end empties them after transferring the output to other database. Most of the execution is done over and over again on these tables.

There is no error handling of any sort other than the basic check over the return value in the front end C# application.

I have spent a few days studying and understanding the SP and its flow. The purpose of the SP is to calculate a financial benchmark by analyzing already stored data wrt the provided parameters and eventually transfer the results to another database and truncate the temporary (but real) tables. These temporary tables are real tables with 'tmp_' prefix attached to them. The relations between these tables is pretty complex so it is out of question to translate everything to actual temporary tables or table variables.

I would like to express my doubts and would appreciate some expert opinion regarding my choices and any room(s) for improvement.

I have experience in developing database centered applications. Also I have designed database systems from scratch. I have used transactions but never locking. Based on my experience and knowledge obtained from The Interwebs (SO etc.) I think I must follow these guidelines to avoid any (existing/future) deadlocks:

• Ensure the database design is properly normalized.
• Have the application access database objects in the same order every time.
• Keep transactions as short as possible.
• During transactions, don't allow any user input.
• Avoid cursors.
• Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
• Consider using the NOLOCK hint to prevent locking.
• Use as low a level of isolation as possible for user connections.

The SP is running over a very complex database and changing its architecture other than adding a column is not an available option as the database is also being used by other parts of the application.

If I add too many transactions, I think that will impact the overall system performance. So I should manage a good balance between transactions and non-transactional code.

Cursors is a real issue as most of the SP code is running on cursors. I read that a few might be translated to CROSS/OUTER APPLY. But that would also be a big task as it is common in the code to find many unions on different and same tables inside the cursors.

To avoid deadlocks (which is what is happening right now, if I force multiple execution of the SP in its current form) I am planning to do the following at least:

• Put transactions inside internal SPs. Eventually have to place some nested transactions as well.
• I am going to use sp_getapplock() in very critical places with proper timeouts.
• Most of the critical tables are using Clustered PK indexes and thats all. I might have to create a few non-clustered index on columns used in the where clauses under joins for performance concerns.
• I may have to put retry logic in transactions to avoid dead stops in case of instances becoming deadlock victims. Appropriate isolation will have to provided as well.

TLDR: Have to convert legacy serial SP to parallel. Insights from experts is what I seek.

Pagination of records with MSSQL 2005

I am trying to paginate the records fetched into pages of 10 (using PHP AJAX on MSSQL Server 2005). (I am passing SQL as PHP strings to MSSQL server) I wish to display button for each page (for example if there are 45 records then I wish to display 5 buttons with each button fetching 10 records). I have came across this code-

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum <= 10
ORDER BY RowNum

But this is highly ineffective because firstly whole database is fetched then from it 10 rows is fetched every time. Could anyone suggest some technique (that works with MSSQL server 2005) wihich fetches only 10 rows at a time and not the whole 100 records at a time.

Also, since I have to display the page numbers (pagination tabs), I wish to have a count of total number of records beforehand.

Thanks!

How do I order by a column in a subquery with a distinct clause?

I have an extremely large query that I need to include a comma delimited list in. I'm accomplishing this with subqueries like so:

        STUFF(( SELECT  distinct ',' + t1.Name
                FROM    t2
                        inner join t1   ON t1.ID        = t2.ID
                WHERE   t2.otherField = 12345
                ORDER BY t2.ID
                FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') as talentName

In this particular case I need to add a distinct clause (as shown). However, when I do so I get the following error:

    Msg 145, Level 15, State 1, Line 2
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I understand why this error occurs, but I'm not sure what to do about it. t2.ID cannot be included in the select statement as it would then get returned as part of the resultset. I cannot remove the order by clause because the comma delimited list must be in an order that matches another list I'm generating of the IDs.

How can I insure that this comma delimited list is both distinct and in the proper order?

End Goal

To help clarify what I'm trying to accomplish, this query will pull comma delimited lists of both the t1.name and t1.ID. I'm currently doing this in two separate STUFF statements, but if there is an alternate method I'd be open to it. This query will return several thousand rows, so I'm attempting to find a set-based solution to avoid thousands of ad-hoc queries running each time our web page is loaded.

Backup Database, Copy Files to another Server, restore Database by script

I'm working on a solution for the following issue:

About 5 - 10 times a day, I make a backup of a MS-SQL Database "dbname1" (SQL 2005 - 2014) on SQLServer1 (different databases on different versions). Then copy the file to SQLServer2 and restore the files onto "dbname2". There are some more steps in between (reading users/roles on pre-restore "dbname2", write user/roles after restore "dbname2", creating backups pre- and after-restore and so on...).

I already have some scripts, which make it easier to do it, but what I'm working on is one single script to do all of it at once...

So far, I tried several things but always ran in some problems I can't solve:
- Powershell. The problem here is, that I am not able to run the .ps1 file with the credentials that I need to read the permission from "dbname2". If I use a connection string with "user id='sa'; Password='mypwd';" it gets ignored and uses only the limited permissions that my current windows logged-on user has. Therefore, I only see my own permission on the database..
- T-SQL which would write and execute a copy statement with the xp_cmdshell command.. But the security settings for our SQL Server do not allow the execution of that...
- SQL-Agent-Job. With Step 1, I wanted to create the backup and save the filename in a variable (stored procedure already in place). Then Step 2, copy the file to the remote location. Problem here is that i cant pass the filename from step 1 to step 2...

Yeah.. That's about it, I guess...

I'm looking for some input (or solution ;-) ) how I can create a backup, copy the created file to a different server and restore it there. Preferable, it should also run some commands, which are created from a stored procedure (something like this 'CREATE USER [xxxcccvfr-username] FOR LOGIN [xxxcccvfr-username] WITH DEFAULT_SCHEMA=[dbo]', BUT that's just a bonus ;-) )

Any help is highly appreciated!

Thanks
D