Merge replication Error: The process could not bulk copy into table

Hi I am using SQL SERVER 2005 Service pack 4 on both publisher and distributor. While trying to setup merge replication, i am getting below error continuously. Below are replication details.

1. I am using push subscription and path is network path.
2. Distributer and publisher present on the same server.
3. I have restored recent backup on subscriber and 1 week back backup on publisher.
4. I am setting up replication for only few tables, procedures and user defined functions.
5. I have verified and both the publisher and subscriber are having same schema.
6. As the replication is failing initially saying unable to drop userdefined functions : To resolve it I have set publisher property for user defined functions as Keep existing object unchanged.
7. Every time the error is coming after running synchronization for around 50 to 55 minutes.
8. My snapshot agent is working fine without any issue. Problem is only with merge agent.
9. I have changed the verbosehistory value to 3 in merge agent profile but it is not giving any additional information

Error messages: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

Get help: http://help/MSSQL_REPL-2147201001

The process could not bulk copy into table '"dbo"."refund_import_log"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

Get help: http://help/MSSQL_REPL20037

The system cannot find the file specified. (Source: MSSQLServer, Error number: 0)

Get help: http://help/0

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)

Get help: http://help/20253

bcp "greyhound"."dbo"."refund_import_log" in "\usaz-ism-db-02\ghstgrpltest\unc\USAZ-ISM-DB-02_GREYHOUND_GREYHOUND-STAGE\20150529112681\refund_import_log_7.bcp" -e "errorfile" -t"\n\n" -r"\n<,@g>\n" -m10000 -SUSGA-QTS-GT-01 -T -w (Source: MSSQLServer, Error number: 20253)

Here i am getting problem with different table every time.

Is there any bug related to it ? If so where i can get the fix ? If it is not a bug then please let me know how to resolve this problem.

Innerjoin coma seperated values in sql server 2005

I have a table coloum with comma seperated values

Stateids CountryId 1,2,3,4 1 1,2 2

All these stateids have a statename in the corresponding statetable. Statetable also have a coloum named country id(or the purpose of join) I need all the statenames as below

Stateids CountryId Statename 1,2,3,4 1 andra,kerala,tamilnadu,karnataka 1,2 2 state1,state2

Thanks

SQL Server 2005 - RIGHT() not working when adding to CHARINDEX()

I'm trying to use the RIGHT function to get the substring of a value if it consists of a '/', but it doesn't work when I add a number to the CHARINDEX value; only without.

Here is a sample of the code:

SELECT CASE
            WHEN 
                CHARINDEX('/',REPLACE(ISNULL(d.target_grade,'NA'), 'N/A', 'NA')) = 0
            THEN 
                REPLACE(ISNULL(d.target_grade,'NA'),'N/A','NA')
            ELSE 
                RIGHT(d.target_grade, CHARINDEX('/',REPLACE(ISNULL(d.target_grade,'NA'), 'N/A', 'NA'))+1)
        END as target_grade
FROM tbl --etc.

This returns for example

target_grade
-------------
C/D

It should return though this

target_grade
-------------
D

If I remove the +1, however, the RIGHT function works exactly as it should

target_grade
-------------
/D

What am I doing wrong here? Is my logic flawed?

sql query to get all child nodes of a parent in sqlserver 2005

In sqlserver 2005 how can i find all directly related and indirectly related child of a parent.

For eg 27 is a parent.31 is a direct child of 27.But 32 is the child of 31.Since 32 is the child of 31 ,and 31 is the child of 27, both 31 and 32 is the child of 27.Also 27 itself is the child of 27.

The result must be comma seperated,with coloum names parent and Children. The result must comes as follows.

27(parent) 27,31,32... (childs)

Thanks

display php mssql query array in html table

I'm a total amateur with php. I've tried dozens of different things, mostly found while browsing around here. I've been at it for hours, and I'm starting to lose my mind. Lacking real knowledge I'm just guessing at this point.

I'm positive this code is completely butchered and will be highly offensive to many of you.

The goal is to display the mssql query results into a nice HTML table. So far, nothing is displaying except the table headers. The query should return dozens of rows.

Here's the current code, any suggestions?

<?php
// Open db connection
$dbc = mssql_connect('host', 'sa', 'password');
if (!$dbc || !mssql_select_db('dbname', $dbc)) {die('Unable to connect or select database!');}

// Select queries
$query = mssql_query("SELECT [name],[customer],[start_time],[end_time],[status] FROM dbo.reservation ORDER BY last_update DESC");

// display the results!
if (!mssql_num_rows($query)) {
    echo 'No records found';
} else {
    ?>
    <table>
        <thead>
            <tr>
                <th>Name</th>
                <th>Customer</th>
                <th>Start Time</th>
                <th>End Time</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody>
        <?php 
            while ($row = mssql_fetch_row($query)) {
                echo'<tr>'; 
                echo'<td>'. $row['name']."</td>";
                echo'<td>'. $row['customer'].'</td>';
                echo'<td>'. $row['start_time'].'</td>';
                echo'<td>'. $row['end_time'].'</td>';
                echo'<td>'. $row['status'].'</td>';
                echo'<tr>';
            }
        ?>
        </tbody>
    </table>
    <?php 
}
?>

Note: This is for an internal site and I need to use mssql_connect.

An INSERT INTO in MSSQL Server 2005 that groups by a column leads to unexpected duplicate key violation

I am executing a stored procedure on Microsoft SQL Server 2005. The code in question has worked flawlessly for about 12 months, but is now failing on "duplicate key violation error"

How is it possible to get a duplicate key violation error with the following?

This is the error:

Violation of Primary Key constraint 'Pk_temp_item_sales_details' Cannot insert duplicate key in object 'dbo.temp_item_sales_details'. [SQLSTATE 23000][Error2627]

This is the SQL that is executed:

TRUNCATE TABLE temp_item_sales_details;

INSERT INTO temp_item_sales_details
            (item_id,
             last_order_date)
SELECT detail.item_no,
       Max(header.sales_invoice_date)
FROM   invoice_header header WITH(NOLOCK)
       JOIN invoice_detail detail WITH(NOLOCK)
         ON header.invoice_number = detail.invoice_no
WHERE  Isnumeric(detail.item_no) = 1
GROUP  BY detail.item_no 

item_no in the invoice_detail table is a varchar whereas the item_id in the temp_item_sales_details table is a numeric, but I am only selecting numeric values with ISNUMERIC(detail.item_no) = 1

Script to concatenate columns and remove leading/trailing delimiter

I have a table like this and I want to return concatenated strings where the column values are in ('01', '02', '03', '04', '99'). Plus the values will be delimited by a ';'. So row 1 will be 01;04, row 3 will be 01;02;03;04 and row 5 will simply be 01. All leading/trailing ; should be removed. What script would do this successfully?

R_NOT_CUR   R_NOT_CUR_2 R_NOT_CUR_3 R_NOT_CUR_4
01          NULL        04          NULL
98          56          45          22
01          02          03          04
NULL        NULL        NULL        NULL
01          NULL        NULL        NULL

Getfirstweek monday in sql server

Hi Friends I Have small doubt in sql server please tell me how to solve below issue

Table : testdate

date          |weekno
1/1/2015      | 1
1/2/2015      | 1
1/3/2015      | 1
1/1/2014      | 1
1/1/2014      | 1
1/2/2014      | 1
1/3/2014      | 1
1/4/2014      | 1
1/4/2015      | 2
1/5/2015      | 2
1/6/2015      | 2
1/7/2015      | 2
1/8/2015      | 2
1/9/2015      | 2
1/10/2015     | 2
1/11/2015     | 3
1/12/2015     | 3
1/13/2015     | 3
1/14/2015     | 3
1/15/2015     | 3
1/16/2015     | 3
1/17/2015     | 3
1/18/2015     | 4
1/19/2015     | 4
1/20/2015     | 4
1/21/2015     | 4
1/22/2015     | 4
1/23/2015     | 4
1/24/2015     | 4
1/25/2015     | 5
1/26/2015     | 5
1/27/2015     | 5
1/28/2015     | 5
1/29/2015     | 5
1/30/2015     | 5
1/31/2015     | 5
2/1/2015      | 6

based on above data I tried like below query

;WITH CTE AS (
      SELECt Date, Weekno ,ROW_NUMBER() OVER(PARTITION BY WEEKno, YEAR(date) ORDER BY date ASC) AS RN
      FROM dateinfo)
    select a.Date,a.Weekno,

    CAST(a.WEEKno AS VARCHAR)    + '  ' +
    convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,a.Date),0),110) Firstdayofweeknodate

    FROM dateinfo AS A
    JOIN CTE AS B
    ON A.WEEKno=B.Weekno
    AND YEAR(A.Date)=YEAR(B.Date)
    WHERE B.RN = 1

its given output like below

Date           |Weekno  |Firstdayofweeknodate
    2014-01-01     |1   |1  12-30-2013
    2014-01-02     |1   |1  12-30-2013
    2014-01-03     |1   |1  12-30-2013
    2014-01-04  |1  |1  12-30-2013
    2015-01-01  |1  |1  12-29-2014
    2015-01-02  |1  |1  12-29-2014
    2015-01-03  |1  |1  12-29-2014
    2015-01-04  |2  |2  01-05-2015
    2015-01-05  |2  |2  01-05-2015
    2015-01-06  |2  |2  01-05-2015
    2015-01-07  |2  |2  01-05-2015
    2015-01-08  |2  |2  01-05-2015
    2015-01-09  |2  |2  01-05-2015
    2015-01-10  |2  |2  01-05-2015
    2015-01-11  |3  |3  01-12-2015
    2015-01-12  |3  |3  01-12-2015
    2015-01-13  |3  |3  01-12-2015
    2015-01-14  |3  |3  01-12-2015
    2015-01-15  |3  |3  01-12-2015
    2015-01-16  |3  |3  01-12-2015
    2015-01-17  |3  |3  01-12-2015
    2015-01-18  |4  |4  01-19-2015
    2015-01-19  |4  |4  01-19-2015
    2015-01-20  |4  |4  01-19-2015
    2015-01-21  |4  |4  01-19-2015
    2015-01-22  |4  |4  01-19-2015
    2015-01-23  |4  |4  01-19-2015
    2015-01-24  |4  |4  01-19-2015
    2015-01-25  |5  |5  01-26-2015
    2015-01-26  |5  |5  01-26-2015
    2015-01-27  |5  |5  01-26-2015
    2015-01-28  |5  |5  01-26-2015
    2015-01-29  |5  |5  01-26-2015
    2015-01-30  |5  |5  01-26-2015
    2015-01-31  |5  |5  01-26-2015
    2015-02-01  |6  |6  02-02-2015

What I expect That result not come properly.I want output like below

Date           |Weekno  |Firstdayofweeknodate
2014-01-01     |1   |1  01-01-2014
2014-01-02     |1   |1  01-01-2014
2014-01-03     |1   |1  01-01-2014
2014-01-04  |1  |1  01-01-2014
2015-01-01  |1  |1  01-01-2015
2015-01-02  |1  |1  01-01-2015
2015-01-03  |1  |1  01-01-2015
2015-01-04  |2  |2  01-05-2015
2015-01-05  |2  |2  01-05-2015
2015-01-06  |2  |2  01-05-2015
2015-01-07  |2  |2  01-05-2015
2015-01-08  |2  |2  01-05-2015
2015-01-09  |2  |2  01-05-2015
2015-01-10  |2  |2  01-05-2015
2015-01-11  |3  |3  01-12-2015
2015-01-12  |3  |3  01-12-2015
2015-01-13  |3  |3  01-12-2015
2015-01-14  |3  |3  01-12-2015
2015-01-15  |3  |3  01-12-2015
2015-01-16  |3  |3  01-12-2015
2015-01-17  |3  |3  01-12-2015
2015-01-18  |4  |4  01-19-2015
2015-01-19  |4  |4  01-19-2015
2015-01-20  |4  |4  01-19-2015
2015-01-21  |4  |4  01-19-2015
2015-01-22  |4  |4  01-19-2015
2015-01-23  |4  |4  01-19-2015
2015-01-24  |4  |4  01-19-2015
2015-01-25  |5  |5  01-26-2015
2015-01-26  |5  |5  01-26-2015
2015-01-27  |5  |5  01-26-2015
2015-01-28  |5  |5  01-26-2015
2015-01-29  |5  |5  01-26-2015
2015-01-30  |5  |5  01-26-2015
2015-01-31  |5  |5  01-26-2015
2015-02-01  |6  |6  02-02-2015

this data for one month but in orgianly table data have more years data similary to above data.

please tell me how to write query to achive all years related data task in sql server.

Complex sorting based on next and previous records in SQL

This is a follow-up question on Sorting based on next and previous records in SQL

But now it gets a little more complex, for example:

1. If the last letter of number 1 is equal to one of the letters of number 2, I want to change the ordering, so that the letter matches with the following record.
2. I also want to switch ordering if the first letter of number 1 matches with the last letter of number 2.
3. If no matches are found the normal ordering by letter should be done.
4. The id's are potentially not succeeding and the records are not persé in the correct order.

Create script and sql fiddle

http://ift.tt/1euYD8f

create table Parent (
id [bigint] IDENTITY(1,1), 
number bigint NOT NULL,
PRIMARY KEY (id)
)
GO

create table Child (
id [bigint] IDENTITY(1,1), 
parentId BIGINT, 
letter VARCHAR(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE (parentId, Letter),
FOREIGN KEY (parentId) REFERENCES Parent(id)
)
GO

INSERT Parent (number) VALUES (1)
INSERT Parent (number) VALUES (2)
INSERT Parent (number) VALUES (3)

INSERT Child (parentId, letter) VALUES (1, 'C')
INSERT Child (parentId, letter) VALUES (1, 'A')
INSERT Child (parentId, letter) VALUES (3, 'C')
INSERT Child (parentId, letter) VALUES (3, 'B')
INSERT Child (parentId, letter) VALUES (5, 'D')
INSERT Child (parentId, letter) VALUES (5, 'B')

Current query

Currently I am sorting with this query:

;WITH CTE AS 
(
SELECT id,ParentID,letter,

ROW_NUMBER()OVER(ORDER BY ID) seq_id,
ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID) first_element,
ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID DESC) Last_element
FROM Child
), CTE2 AS 
(
SELECT c1.id,c1.parentid,c1.letter,c2.parentid as c2parentid
FROM CTE c1
INNER JOIN CTE c2
ON c1.last_element = 1
AND c2.first_element = 1
AND c1.seq_id + 1 = c2.seq_id
), CTE3 AS 
(
SELECT C.parentid,C.id
FROM CTE2
INNER JOIN child C ON CTE2.c2parentid = C.parentid
AND C.letter = CTE2.letter
)
SELECT P.number, C.letter
FROM Child C
JOIN Parent P ON C.parentId = P.id
LEFT JOIN CTE3 ON CTE3.id = C.id
ORDER BY P.number, ISNULL(CTE3.id,0) DESC, C.letter

Current result set

number               letter
-------------------- ------
1                    A
1                    C
2                    B
2                    C
3                    B
3                    D

Expected result set

To clearify what I actually want to do, here is the expected result set (with C and B of number 2 switched).

number               letter
-------------------- ------
1                    A
1                    C
2                    C 
2                    B 
3                    B
3                    D

Other requirements and question

• It has to work in SQL SERVER 2005.
• There is a scenario where 3 letters per number are used, I am happy if it just uses the best match.

Can anyone point me in the right direction on how to deal with this scenario?

Sorting based on next and previous records in SQL

I am trying to order a specific query by taking the next and previous records into account, but I can't seem to get it done. I would like to order by a number and a letter, but if, for example, the last letter of number 1 is equal to one of the letters of number 2, I want to change the ordering, so that the letter matches with the following record.

Create script and sql fiddle

http://ift.tt/1EwUd5s

create table Parent (
id [bigint] IDENTITY(1,1), 
number bigint NOT NULL,
PRIMARY KEY (id)
)
GO

create table Child (
id [bigint] IDENTITY(1,1), 
parentId BIGINT, 
letter VARCHAR(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE (parentId, Letter),
FOREIGN KEY (parentId) REFERENCES Parent(id)
)
GO

INSERT Parent (number) VALUES (1)
INSERT Parent (number) VALUES (2)
INSERT Parent (number) VALUES (3)

INSERT Child (parentId, letter) VALUES (1, 'A')
INSERT Child (parentId, letter) VALUES (1, 'C')
INSERT Child (parentId, letter) VALUES (2, 'B')
INSERT Child (parentId, letter) VALUES (2, 'C')
INSERT Child (parentId, letter) VALUES (3, 'B')
INSERT Child (parentId, letter) VALUES (3, 'D')

Current query

Currently I am sorting with this query:

SELECT P.number, C.letter 
FROM Child C
JOIN Parent P ON C.parentId = P.id
ORDER BY P.number, C.letter

Current result set

number               letter
-------------------- ------
1                    A
1                    C
2                    B
2                    C
3                    B
3                    D

Expected result set

To clearify what I actually want to do, here is the expected result set (with C and B of number 2 switched).

number               letter
-------------------- ------
1                    A
1                    C
2                    C --switched
2                    B --switched
3                    B
3                    D

Other requirements and question

• It has to work in SQL SERVER 2005.
• There is a scenario where 3 letters per number are used, I am happy if it just uses the best match.
• I am actually also interested in solutions for later versions of SQL Server (for learning), but those do not answer my question.

Can anyone point me in the right direction on how to do this?

How is it possible to have changes in a database available at a later stage inside a local transaction?

I have a vb.net application that updates a record in table A and then inserts a record in the same table, inside a local transaction (in SQL Server 2005.)

_DbConnection.Open()
Try
    _Transaction = _DbConnection.BeginTransaction()

    If _DbConnection.State = ConnectionState.Open Then

        bProcessSuccessfully = Update()

        _Transaction.Commit()
    End If
Catch ex As Exception
    Try
        _Transaction.Rollback()
    Catch ex2 As Exception
        Logger.Error(ex2.Message)
    End Try
Finally
    _DbConnection.Close()
End Try

The Update method updates a field (lets call it ID) which has a unique constraint and sets it to a new unique value. Then it proceeds to insert a new record in the table using the old value of field ID.

For example if field ID has the value 0542-70-00002, the existing record is updated with ID=0542-70-00002/S and a new record is inserted with ID=0542-70-00002.

The problem is that when trying to insert the new record I get an exception Violation of UNIQUE KEY constraint 'UF_ID'. Cannot insert duplicate key in object 'A'.

The code above does not fail all the time. In fact until today was not failing at all. I made sure (using git's log) that no changes were made to this code that could cause this change in its behavior.

So the question is, what needs to be done to make the updated value of field ID visible when the insertion is performed in order to succeed? Both the update and the insertion need to be performed inside a transaction, since I need an all-or-nothing operation.

I have tried specifying a different isolation level in BeginTransaction (ReadCommited and ReadUncommited), but the problem persists.

How to get latest record weekly within an SQL statement

I'm trying to export the latest records from SQL Server 2005 database once weekly. This is my table:

agent_name  date          ID
ALEX        2015-05-25    13
ALEX        2015-05-22    13
ALICE       2015-05-24    10
ALICE       2015-05-26    10

How to create output table should like this:

agent_name  date          ID
ALEX        2015-05-25    13
ALICE       2015-05-26    10

My sql script:

SELECT a.agent_name,  
       a.date, 
       a.ID 
FROM Payment a 
INNER JOIN agentmaster b ON a.ID = b.ID2 
WHERE b.agent ='Y'
AND a.date >= DATEADD(day, -7, GETDATE())

How to generate tables in MS SQL?

I need to generate tables like 'test_table_1', 'test_table_2'... What I am doing wrong?

declare @i int = 1;
declare @i_str varchar(1024);
declare @table VARCHAR(1024);


while (@i < 500)
BEGIN
SET @i = @i + 1;
SET @table = 'TEST_TABLE_' + convert(varchar, @i);

IF NOT EXISTS(SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = @table) 

CREATE TABLE @table (
        ID INT,
        userid INT,
        name TEXT,
        last_name TEXT,
        country TEXT,
        city TEXT
)

END

Error: Incorrect syntax near @table. Expecting '.', ID or QUOTED_ID

How do I generate an SQL Query to form XML output

I am trying to generate a XML output as below:

<Employees>
    <EmployeeID>0025907E9BB4</EmployeeID>
    <EmpDateJoin>2015-05-18 01:58:44</EmpDateJoin>
    <EmpRegId>89-16036-1267</EmpRegId>
    <ProjectDetails> 
            Project Name: SVS-DC1 | Project Last Deployed: 2012-03-20 01:48:43 | ErrorDesc: Not Applicable
    </ProjectDetails>
    <ProjectDetails>
        Project Name: ADP-SERVER | Project Last Deployed: 2015-05-18 01:57:43 | ErrorDesc: backup failed due to low memory
    </ProjectDetails>
 </Employees>

But my SQL output is JOINS of Multiple tables which return output as

EmployeeID      EmpDateJoin             EmpRegId        ProjectName         LastDeployedDate    ErrorDesc
0025907E9BB4    2015-05-18 01:58:44     89-16036-1267   SVS-DC1             2012-03-20 01:48:43 Not Applicable
0025907E9BB4    2015-05-21 01:48:44     89-16036-1267   ADP-SERVER          2015-05-18 01:57:43 backup failed due to low memory

Just want to know how can I separate distinct values in parent tags and resource values in Project Details tags

I have tried achieving this but was able to generated only below output:

 <Employees>
     <ProjectDetails>
        EmployeeID: 0025907E9BB4 | EmpDateJoin: 2015-05-18 01:58:44 | EmpRegId: 89-16036-1267 | Project Name: SVS-DC1 | Project Last Deployed: 2012-03-20 01:48:43 | ErrorDesc: Not Applicable
    </ProjectDetails>
     <ProjectDetails>
        EmployeeID: 0025907E9BB4 | EmpDateJoin: 2015-05-21 01:48:44 | EmpRegId: 89-16036-1267 | Project Name: ADP-SERVER | Project Last Deployed: 2015-05-18 01:57:43 | ErrorDesc: backup failed due to low memory
    </ProjectDetails>
 </Employees>

Does JOOQ Support SQL Server 2005 or SQL Server 2008 R2 in Code Generator?

I've been trying to auto-generate artefacts for my SQL Server Database. Its originally from SQL Server 2005, and then I didnt find in the documentation of JOOQ, so i imported it on a 2008 R2. But then its still says the same thing, around the lines "Make sure you database (and database version!) is compatible with JOOQ (somthing..). Case sensitivity: "\

Can some JOOQ expert point me to the right direction on where I can see supported JOOQ Database, or just answer it directly.

Note: I'm on a computer shop and Im not using my local working environment so i was not able to phrase the correct error messages in the Generator, as well as Screen Caps for that matter.

Different order by clause for different columns

I hope my title made sense, here's a sample of my table.

My table has 1 column for a unique product barcode, and 4 date columns which is for storing what date and time a product passed an inspection area(4 of them), i removed the date in my example so just assume they're all the same dates.

Product | Time1 | Time2 | Time3 | Time4
---------------------------------------
   A    | 10:00 | 10:15 | 10:30 | 10:45
   B    | 10:05 | 10:25 | 10:35 | 10:50
   C    | 10:10 | 10:20 | 10:40 | 10:55

Output:

 Inspect1 | Inspect2 | Inspect3 | Inspect4
 -----------------------------------------
    A            A         A          A
    B            C         B          B
    C            B         C          C

In my output, I want to select the product multiple times with different order by(order by Time1, order by Time2, etc.)

I used case select to select the product multiple times, how do I put an order by so that I get something like my example above.

SELECT     
CASE WHEN time1 BETWEEN '10:00' AND '11:00' THEN product END AS inspect1, 
CASE WHEN time2 BETWEEN '10:00' AND '11:00' THEN product END AS inspect2, 
CASE WHEN time3 BETWEEN '10:00' AND '11:00' THEN product END AS inspect3, 
CASE WHEN time4 BETWEEN '10:00' AND '11:00' THEN product END AS inspect4
FROM         Table

Scheduled SQL Server Data Export to CSV using SQLCMD

This is my command line:

sqlcmd -S DEVSERVER\SQLEXPRESS -i  c:\SQL_Query.sql -o c:\CSV_Output.csv -s”,”

And my sql script:

SELECT * FROM dbo.pay

The file with this error message:

Msg 208, Level 16, State 1, Server DEVSERVER\SQLEXPRESS, Line 1
Invalid object name 'dbo.pay'.

How to sort the varchar datatype column in SQL server

I have a column in SQL Server that contains data,

1.1.1.QuestionText
1.1.1.Question
1.1.1(a).Questions
1.1.2.Questionswithtext
1.1.2(b).Text
10.1.1.Answers
2.1.1.Questions
2.2.2.QuestionText

etc...

How do I display this in Ascending order?

Associate couples between 2 tables with SQL Server 2005

The question is easy, answer is not (for me).

I have 2 identical tables composed of 2 columns : ID and value. I have to find all couples sharing the same value. So when a record is used in a couple, it can't be reused in another couple.

For example, with this two tables :

CREATE TABLE [Tab1]([ID1] [int], [Val] [int]) 
CREATE TABLE [Tab2]([ID2] [int], [Val] [int])
INSERT [Tab1] ([ID1], [Val]) VALUES (1, 10)
INSERT [Tab1] ([ID1], [Val]) VALUES (2, 20)
INSERT [Tab1] ([ID1], [Val]) VALUES (3, 20)
INSERT [Tab1] ([ID1], [Val]) VALUES (4, 50)
INSERT [Tab1] ([ID1], [Val]) VALUES (5, 100)
INSERT [Tab2] ([ID2], [Val]) VALUES (1, 20)
INSERT [Tab2] ([ID2], [Val]) VALUES (2, 10)
INSERT [Tab2] ([ID2], [Val]) VALUES (3, 50)
INSERT [Tab2] ([ID2], [Val]) VALUES (4, 30)
INSERT [Tab2] ([ID2], [Val]) VALUES (5, 20)
GO

a good answer would be (there are several solutions, but one is enough) :

ID1   ID2
---  ----
2    1
1    2
4    3
3    5

I'm looking for a query to find this result. I use SQL Server 2005 but I can use SQL Server 2008 if it's needed.

How to get manager,employee pair output?

I just require Required output as i posted below.This question asked in an interview.

Table Structure:

create table #test
(
id int,
emp char,
roles char
)

insert into #test values(1,'A','M')
insert into #test values(2,'B','E')
insert into #test values(3,'C','E')
insert into #test values(4,'D','M')
insert into #test values(5,'E','E')
insert into #test values(6,'F','E')
insert into #test values(7,'G','M')
insert into #test values(8,'H','E')
insert into #test values(9,'I','E')
insert into #test values(10,'J','E')

Required output:

emp emp
A   B
A   C
D   E
D   F
G   H
G   I
G   J

How to retrieving data from database using hibernate?

When I'm using this I'm getting java.lang.ClassCastException:
java.lang.String cannot be cast to com.transgenomic.domain.Note error showing

public List<Note> getNoteDescription(Long userId) {
    Session session=sessionFactory.openSession();
    Transaction transaction=session.beginTransaction();
    SQLQuery query=session.createSQLQuery("SELECT n.description"
         + " FROM notes n, users u, tasks t "
         + " where u.userId=t.assignTo AND t.taskId=n.taskId");

    List<Note> notes=query.list();
    System.out.println("***********"+notes);
    transaction.commit();
    session.close();
    return notes;
}

Stored Procedure in SQL 2008 Not Working in 2005

Hi I'm Trying to Create Stored Procedured in SQL2005 below is my syntax

CREATE PROCEDURE [dbo].[POBalance]  @PONumber nvarchar(50)
AS BEGIN

declare @status bit = (Select status from tblPOHeader where PONo = @PONumber  )
if @status = 'False'

                Select 
                    A.Description,
                    C.qty as POqty,
                    B.QtyDelivered as PDQty, 
                    case when A.partialflag ='false' 
                    then '0'
                    else
                    A.qty  end as Balance,
                    A.Unit,
                    A.Unitprice,
                    A.Partialflag 
            from tblPOdetails as A

            Inner Join  ( SELECT  id, SUM(Qty) AS QtyDelivered
                                                    FROM             dbo.tblPDdetails
                                                    WHERE     (PONo = @PONumber)
                                                    GROUP BY id)as B On A.id = B.id   
            Inner Join tblpodetailshistory as C on A.id =C.id

            where A.PONo = @PONumber
ELSE

            Select 
                    A.Description,
                    C.qty as POqty,
                    B.QtyDelivered as PDQty, 
                    '0' as Balance,
                    A.Unit,
                    A.Unitprice,
                    A.Partialflag 
            from tblPOdetails as A

            Inner Join  ( SELECT  id, SUM(Qty) AS QtyDelivered
                                                    FROM          dbo.tblPDdetails
                                                    WHERE     (PONo =  @PONumber)
                                                    GROUP BY id)as B On A.id  = B.id   
            Inner Join tblpodetailshistory as C on A.id =C.id

            where A.PONo = @PONumber
END

When i execute i execute i got this error.

Msg 102, Level 15, State 1, Procedure POBalance, Line 13
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure POBalance, Line 14
Must declare the scalar variable "@status".
Msg 156, Level 15, State 1, Procedure POBalance, Line 36
Incorrect syntax near the keyword 'ELSE'.

But that syntax is already working in SQL2008 and no problem. im wondering if there is different syntax between 2005 and 2008..

Could anyone have idea about this.

Thank you!

Temp table in different database

Can a temp table be created in another database with sql server 2005? I know that when temp tables are created, they go to tempdb and will be automatically be dropped. My question is can I create it in a different database, if so, how?

What can justify 15 seconds of delay to retrive rows from system tables?

If I run select * from sysobjects, the first time it takes 15 seconds, after that it just takes 0 seconds. The same occurs with sp_helptext someAppSP or other stuff. Server is almost all time at 0% CPU usage.
It is a classic asp App on top of a VM with Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
I am after recent (newly) App's delays. I don't care the mentioned querys by itself.

Get firstweekdaydate in sql server

Hi Friends I Have small doubt in sql server please tell me how to solve below issue

Table :  testdate


    date          |weekno
    1/1/2015      | 1
    1/2/2015      | 1
    1/3/2015      | 1
    1/1/2014      | 1
    1/1/2014      | 1
    1/2/2014      | 1
    1/3/2014      | 1
    1/4/2014      | 1
    1/4/2015      | 2
    1/5/2015      | 2
    1/6/2015      | 2
    1/7/2015      | 2
    1/8/2015      | 2
    1/9/2015      | 2
    1/10/2015     | 2
    1/11/2015     | 3
    1/12/2015     | 3
    1/13/2015     | 3
    1/14/2015     | 3
    1/15/2015     | 3
    1/16/2015     | 3
    1/17/2015     | 3
    1/18/2015     | 4
    1/19/2015     | 4
    1/20/2015     | 4
    1/21/2015     | 4
    1/22/2015     | 4
    1/23/2015     | 4
    1/24/2015     | 4
    1/25/2015     | 5
    1/26/2015     | 5
    1/27/2015     | 5
    1/28/2015     | 5
    1/29/2015     | 5
    1/30/2015     | 5
    1/31/2015     | 5
    2/1/2015      | 6

based on above data I need to display one more column firstdayofweekwithdate and data look like below.this data for one month but in orgianly table data have 30 years data similary to above data.

        date         |weekno  |   Firstdayofweeknodate
        1/1/2015     |  1     | "1   1/1/2015"
        1/2/2015     |  1     | "1   1/1/2015"
        1/3/2015     |  1     | "1   1/1/2015"
        1/1/2014     |  1     | "1  1/1/2014" 
        1/1/2014     |  1     | "1  1/1/2014" 
        1/2/2014     |  1     | "1  1/1/2014" 
        1/3/2014     |  1     | "1  1/1/2014" 
        1/4/2014     |  1     | "1  1/1/2014" 
        1/4/2015     |  2     | " 2  1/4/2015"
        1/5/2015     |  2     | " 2  1/4/2015"
        1/6/2015     |  2     | " 2  1/4/2015"
        1/7/2015     |  2     | " 2  1/4/2015"
        1/8/2015     |  2     | " 2  1/4/2015"
        1/9/2015     |  2     | " 2  1/4/2015"
        1/10/2015    |  2     | " 2  1/4/2015"
        1/11/2015    |  3     | "3   1/11/2015"
        1/12/2015    |  3     | "3   1/11/2015"
        1/13/2015    |  3     | "3   1/11/2015"
        1/14/2015    |  3     | "3   1/11/2015"
        1/15/2015    |  3     | "3   1/11/2015"
        1/16/2015    |  3     | "3   1/11/2015"
        1/17/2015    |  3     | "3   1/11/2015"
        1/18/2015    |  4     | "4  1/18/2015"
        1/19/2015    |  4     | "4  1/18/2015"
        1/20/2015    |  4     | "4  1/18/2015"
        1/21/2015    |  4     | "4  1/18/2015"
        1/22/2015    |  4     | "4  1/18/2015"
        1/23/2015    |  4     | "4  1/18/2015"
        1/24/2015    |  4     | "4  1/18/2015"
        1/25/2015    |  5     | "5  1/25/2015"
        1/26/2015    |  5     | "5  1/25/2015"
        1/27/2015    |  5     | "5  1/25/2015"
        1/28/2015    |  5     | "5  1/25/2015"
        1/29/2015    |  5     | "5  1/25/2015"
        1/30/2015    |  5     | "5  1/25/2015"
        1/31/2015    |  5     | "5  1/25/2015"
        2/1/2015     |  6     | "6  2/1/2015" 

please tell me how to write query to achive all years related data task in sql server.

XML string to xml unable to switch encoding

I have an XML string that is malformed.

DECLARE @xmlt TABLE(xstr nvarchar(max), xml xml)

INSERT INTO @xmlt(xstr) VALUES (
'  <?xml version="1.0" encoding="windows-1257" ?>
- <objects><object id="778913">a</object>
- <object id="785491">b</object>
- <object ...goes on...
- </objects>
'

To be able to use that XML I convert it to XML

UPDATE @xmlt SET xml = CAST(REPLACE(LTRIM(xstr), ' - <', '<') AS xml);

But I get an error XML parsing: line 1, character 46, unable to switch the encoding.

Is there any other way (without replacing the string encoding="windows-1257" with "") to convert that XML string to xml in SQL Server?

Todays latest and yesterdays latest record

I have a table with code and Date

Code         Date  
----------------------------
A1           21 May 2015 15:47
A2           21 May 2015 10:30
A3           20 May 2015 10:30
A4           21 May 2015 10:30
A1           19 May 2015 15:20
A2           21 May 2015 12:30
A3           19 May 2015 05:30
A4           18 May 2015 15:38
A1           19 May 2015 05:30
A2           20 May 2015 05:30
A3           21 May 2015 05:30
A4           21 May 2015 05:30
A3           21 May 2015 06:30
A1           21 May 2015 05:30

I need to get the Todays latest record, and yesterdays latest record of A1,A2,A3,A4 showing like below

Flag         Code         Date
-----------------------------------------
Today         A1       21 May 2015 15:47
Today         A2       21 May 2015 10:30
Today         A3       21 May 2015 06:30
Today         A4       21 May 2015 10:30


Yesterday     A1        -- 
Yesterday     A2       20 May 2015 05:30
Yesterday     A3       20 May 2015 10:30
Yesterday     A4        --

Help me how to write query to get data

Set-based approach to applying payments to bills

I'm trying to show payments applied to bills and I'd like to know if there is a way to do this without using cursors and imperative logic. I have a Bills table and Payments table. Payments are not always in the amounts of the bills, sometimes over, sometimes under. I'm trying to create some sort of join that shows how much of each payment was applied to each bill.

Assumptions:

• Bills belong to a single account
• Payments are applied to each bill in order of its ID

Given a Bills table:

ID  Amount
1   500
2   500
3   500

Scenario 1:

Payments table

ID  Amount
1   750
2   750

With the above Bills table, and the scenario 1 payments table, I would like to see this output:

Bill ID | Payment ID | Amount Applied
1   1   500
2   2   250
3   2   500
3   2   250

Scenario 2:

Payments table:
1   300
2   300
3   300
4   300
5   300

Given above Bills table and scenario 2 Payments table, I would like to see output:

Bill ID | Payment ID | Amount Applied
1   1   300
1   2   200
2   2   100
2   3   300
2   4   100
3   4   200
3   5   300

I can do this with cursors, but I'd like to find out if anyone knows how to do this with set-based SQL.

Thanks!

Correct syntax for foreign key constraint for multiple foreign keys

As per this example what is the correct syntax for a foreign key constraint for multiple foreign keys that all reference the same primary key from the referenced table?

ALTER TABLE team
ADD CONSTRAINT fk_team_players
FOREIGN KEY (player_1, player_2, player_3, player_4, player_5, player_6, player_7, player_8) 
REFERENCES player (player_id)

What SELECT statement will get the non-zero values from table rows?

If I have multiple rows in a table of 27 columns which are all integers like this

id_1    id_2    id_3    id_4    id_5    id_6    id_7    id_8    id_9    id_10   id_11   id_12   id_13   id_14   id_15   id_16   id_17   id_18   id_19   id_20   id_21   id_22   id_23   id_24   id_25   id_26   id_27
0       2       0       4       5       0       0       8       0       10      0       0       0       14      0       0       17      0       0       0       21      0       0       0       0       0       0

and I wanted to run a SELECT statement to get a maximum of 8 columns which are > zero (there will never be more than 8) what would be the best, or at least functional, way of doing it? In the event of there not being 8 values > 0 then NULLS are allowed. So the resulting table from the above would be.

col1 col2 col3 col4 col5 col6 col7 col8
2    4    5    8    10   14   17   21

Function to split a string at the first space after a certain character limit with sequence into multiple rows

I've been trying to make a T-SQL function that would behave just like in the title. I found some kind of function that resembles what I need, but I want to make it so, instead of searching in declarations, it searches in a column in certain table.

Here's a code from Microsoft forums that resembles what I need. It was made by Sarat Babu:

declare @str varchar(max)

set @str = 'Guidelines and recommendations for lubricating the required components of your Adventure Works Cycles bicycle. Component lubrication is vital to ensuring a smooth and safe ride and should be part of your standard maintenance routine. Details instructions are provided for each bicycle component requiring regular lubrication including the frequency at which oil or grease should be applied'

declare @data table (col varchar(100))
while (LEN(@str)>0)
begin

if (LEN(@str)<=50)
begin 
        insert into @data select @str
        set @str=''
end 
else 
begin 

insert into @data
select STUFF(@str,charindex(' ',SUBSTRING(@str,50,LEN(@str)))+50,LEN(@str),'')

set @str= STUFF(@str,1,charindex(' ',SUBSTRING(@str,50,LEN(@str)))+49,'')
end
end 
select * from @data

As I said, I want to make it so instead of declaring the text, it searches for it in a specific column of a table. Let's say ProductName column inside Products table.

Incoming linked servers information in SQL Server

Is there anyway that we can list out the linked servers coming to a SQL server? I am trying to catch all the connections coming to a SQL Server. Please help!! Thanks

How do I match a substring of variable length?

I am importing data into my SQL database from an Excel spreadsheet.

The imp table is the imported data, the app table is the existing database table.

app.ReceiptId is formatted as "A" followed by some numbers. Formerly it was 4 digits, but now it may be 4 or 5 digits.

Examples:

A1234
A9876
A10001

imp.ref is a free-text reference field from Excel. It consists of some arbitrary length description, then the ReceiptId, followed by an irrelevant reference number in the format " - BZ-0987654321" (which is sometimes cropped short, or even missing entirely).

Examples:

SHORT DESC A1234 - BZ-0987654321
LONGER DESCRIPTION A9876 - BZ-123
REALLY LONG DESCRIPTION A2345 - B
REALLY REALLY LONG DESCRIPTION A23456

The code below works for a 4-digit ReceiptId, but will not correctly capture a 5-digit one.

UPDATE app
SET
[...]
FROM imp
INNER JOIN app
ON app.ReceiptId = right(right(rtrim(replace(replace(imp.ref,'-',''),'B','')),5) 
                   + rtrim(left(imp.ref,charindex(' - BZ-',imp.ref))),5)

How can I change the code so it captures either 4 (A1234) or 5 (A12345) digits?

Insert query not working with select query

I need to copy a row from a database table to another database table. I used this query,

insert into db_target.dbo.table_name(column_names)
  values(select * from db_source.dbo.table_name);

But it is not being copied!

How to arrange the data in sql table as per below requirement

ID | EmpDailyFee | EmpMonthlyFee | CompDailyFee | CompMnthlyFee

1        NULL         12            NULL               NULL

1        50           NULL          NULL               NULL

1        60           NULL          NULL               NULL

2        50           NULL          NULL               NULL

3        NULL         30            NULL               NULL

Expected :-

ID | EmpDailyFee | EmpMonthlyFee | CompDailyFee | CompMnthlyFee

1        50            12            NULL               NULL

1        60            12            NULL               NULL

2        50           NULL           NULL               NULL

3        NULL          30            NULL               NULL

Join 3 tables and select 1 coloum using linq

I have three tables Student, TimeSheet and TimeRecord.

*Talbe columns:**

• Student : StudentId, FirstName, LastName

• TimeSheet: TimeSheetId,StudentId, IsActive

• TimeRecord: TimeRecordId,TimeSheetId, AddId

Table relationship:

• Student 1:N TimeSheet (FK StudentId)
• TimeSheet 1:N TimeRecord (FK TimeSheetId)

Student sample data:

StudentId, FirstName, LastName

• 10 , Macro , John
• 11 , Hiro , Edge
• 12 , Sarah , Lemon

TimeSheet sample data:

TimeSheetId, StudentId, IsActive

• 187 , 10 , True
• 196 , 11 , True
• 195 , 12 , True
• 199, 10 , False
• 200 , 12 , False

TimeRecord sample data:

TimeRecordId, TimeSheetId, Addid

• 1 , 187 , 1

• 2 , 196 , 2

• 3 , 187 , 3

• 4 , 187 , 4

• 5 , 196 , 5

• 6 , 196 , 6

• 7 , 195 , 7

• 8 , 199, 8

How to write linq Query to get count of the aadid for the student with id=10.

How to identify the binary compression type from the content?

I am trying to restore files which are stored in MS SQL database (used by third party application which has stopped their support) as an image data type(byte arrays). So what I do is to write those rows of byte arrays to file to convert for know file extensions. However some of them are not known which I believe they are compressed. Since I get "CC_Compress" string and random characters in the file after conversion. I was wondering if it is possible to find out which compression method were used and how can I decompress it before I convert them.

Thanks in advance,

SSRS Report Configuration Error in IIS 6, SQL 2005

Server Error in '/Reports' Application. Could not load file or assembly 'ReportingServicesNativeClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format. Description: An unhandled exception occurred during the execution

When I try to access the SSRS I get this error. All my application are in 32 bit mode and I am using IIS 6 and SQL 2005. I have enabled IIS to run in 32 bit in 64 bit OS. My SSRS services are also in 32 bit mode. Help Needed.

Query with subquery and group by slower than expected

The whole query below runs incredibly slowly.

The subquery query [alias Stage_1] takes only 1.37 minutes returning 9514 records, however the whole query takes over 20 minutes, returning 2606 records.

I could use a #temp table to hold the subquery to improve the performance however I would prefer not to.

An overview of the query is that table WeeklySpace inner joins to Spaceblock_Name_to_PG table on SpaceblockName_SID, this cuts down the results in WeeklySpace and includes PG_Code with the results in WeeklySpace. WeeklySpace is then Full Outer Joined to Sales_PG_Wk across 3 fields. The where clause focuses the results, and may be changed. The results from the subquery are then sum'd. You cannot do the final sum'ing in the subquery due to the group by and sum over used.

I believe the issue is due to the subquery re calculation repeatedly during the group by in the final sum'ing. The field SpaceblockName_SID also appears to be involved in causing the issue as without it the run time with a group by in the subquery isn't affected.

I have read though loads of suggestion, trying them all to resolve the issue.

These include;

• Adding TOP 2147483647 with Order by to force intermediate materialization, both in the subquery and using a CTE.
• Adding a join after stage_1.
• Cast'ing SpaceblockName_SID from an int to a varchar and back again

The execution plan (cut in two parts, shown below the code) for both the subquery and the whole query appear similar. The cost is around the Full Outer Join (Hash Match), which I expected.

The query is running on T-SQL 2005.

Any help greatly appreciated!

select 
    Cost_centre
    , Fin_week
    , SpaceblockName_SID
    , sum(Propor_rep_SRV) as Total_SpaceblockName_SID_SRV
from
(
        select  
            coalesce(space_side.fin_week , sales_side.fin_week) as Fin_week 
           ,coalesce(space_side.cost_centre , sales_side.cost_Centre) as Cost_centre 
           ,space_side.SpaceblockName_SID
           ,case
                when  space_side.SpaceblockName_SID is null
                    then sales_side.SalesExVAT
                else  sum(space_side.TLM)
                                /nullif(sum (sum(space_side.TLM) ) over (partition by coalesce(space_side.fin_week , sales_side.fin_week)
                                                                                            , coalesce(space_side.cost_centre , sales_side.cost_Centre)  
                                                                                            , coalesce( Spaceblock_Name_to_PG.PG_Code, sales_side.PG_Code)) ,0)*sales_side.SalesExVAT 
            end as Propor_rep_SRV
        from
            WeeklySpace as space_side
        INNER JOIN
            Spaceblock_Name_to_PG 
                ON space_side.SpaceblockName_SID = Spaceblock_Name_to_PG.SpaceblockName_SID 
                    and Spaceblock_Name_to_PG.PG_Code < 10000
        full outer join
            sales_pg_wk as sales_side
                on  space_side.fin_week = sales_side.fin_week 
                    and space_side.Cost_Centre = sales_side.Cost_Centre 
                    and Spaceblock_Name_to_PG.PG_code = sales_side.pg_code
        where 
            coalesce(space_side.fin_week, sales_side.fin_week) between 201538 and 201550
                and
            coalesce(space_side.cost_centre, sales_side.cost_Centre) in (3, 2800)
        group by  
            coalesce(space_side.fin_week, sales_side.fin_week)  
           ,coalesce(space_side.cost_centre, sales_side.cost_Centre)  
           ,coalesce( Spaceblock_Name_to_PG.PG_Code, sales_side.PG_Code)  
           ,sales_side.SalesExVAT
           ,space_side.SpaceblockName_SID
) as stage_1
group by 
    Cost_centre
    , Fin_week
    , SpaceblockName_SID

Get number of tables in each database in SQL Server

SELECT d.NAME
,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4
GROUP BY d.NAME
ORDER BY d.NAME

I have the above T-SQL script which lists all databases on an SQL Server instance along with their corresponding size in MBs & GBs.

What i'm struggling with is also to include a column for the number of tables in each database.

Does any one also know how i can improve the above script to also show the total numbers of tables in each listed database. Optionally, it would be nice to get also the number of rows in each table but this is not a big issue.

I'm targeting sql server 2005 and obove.

T-SQL query to get number of tables, size in MBs & GBs for each database in an SQL Server

Does any one of a T-SQL query that i can execute against an SQL Server and get a list of all user database with their corresponding total number of tables, database size in MBs and database size in GBs for each database in an SQL Server instance.

Windows Embedded Standart 7 and Sql Server

My question is that; Can i install sql server 2005, 2008, 2012 or 2014 to a computer of which operating system is Windows Embedded Standart 7.

There are 4GB ram, 64GB SSD and 1.8 ghz cpu in the computer.

How to reduce code redundancy in this query?

I've got these two calculated fields in my query:

CASE
    WHEN TRN.TOTAL_VALUE_T = 0 THEN '00000000000'
    ELSE
        CASE
            WHEN CC.PORT_CURRENCY1 = 'CAD' THEN RIGHT('00000000000' + REPLACE(LTRIM(STR(TRN.TOTAL_VALUE_T + TRN.TAXES, 11, 2)), '.', ''), 11) 
            WHEN CC.PORT_CURRENCY1 = 'USD' THEN RIGHT('00000000000' + REPLACE(LTRIM(STR((TRN.TOTAL_VALUE_T + TRN.TAXES) * DBO.fnGetExchangeRate(TRN.TRADE_DATE, 'USD', 'CAD'), 11, 2)), '.', ''), 11)
    END
END AS TX_PRIX_CAD,

CASE 
    WHEN T.TRANS_TYPE in ('ADD','DELIV') THEN
        CASE
            WHEN (SELECT currencyTitle FROM DBO.GetPrice(TRN.SEC_NO, TRN.TRADE_DATE)) = '' THEN TX_PRIX_CAD 
    END
END AS TX_MNT_BRUT

You see, the second field (TX_MNT_BRUT) must equal the first one (TX_PRIX_CAD) under certain conditions.

Problem when doing this is SQL is telling me that TX_PRIX_CAD is not a valid column name, I guess because the column name does not exist physically in the table.

How could I achieve this without repeating the code in TX_PRIX_CAD in TX_MNT_BRUT ?

Thanks.

MSSQL - Select distinct products that are found in two columns

Suppose I have the following combinations in my dataset:

**ProductA**            **ProductB**
  Apple                   Banana
  Apple                   Orange
  Apple                   Pear
  Banana                  Orange
  Banana                  Pear
  Orange                  Pear

How would I return a complete list of unique products in a single column? Desired output below:

**Products**
Apple
Banana
Orange
Pear

If I do select distinct, I obviously won't get the pear because it's not included in column ProductA.

Any help would be appreciated. Thanks!

Remove charecters from concatenation

I have a table in with the following layout:

    CREATE TABLE dbo.tbl (
    Ten_Ref VARCHAR(20) NOT NULL,
    Benefit VARCHAR(20) NOT NULL
);

INSERT INTO dbo.tbl (Ten_Ref, Benefit)
VALUES ('1', 'HB'),
       ('1', 'WTC'),
       ('1', 'CB'),
       ('2', 'CB'),
       ('2', 'HB')
       ('3', 'WTC';

I then run this code to perform a transform and concatenation (I need all the benefit information in one field'

with [pivot] as

(
SELECT Ten_Ref
,[HB] = (Select Benefit FROM tbl WHERE t.Ten_Ref = Ten_Ref and Benefit = 'HB')
,[CB] = (Select Benefit FROM tbl WHERE t.Ten_Ref = Ten_Ref and Benefit = 'CB')
,[WTC] = (Select Benefit FROM tbl WHERE t.Ten_Ref = Ten_Ref and Benefit = 'WTC')
/*Plus 7 more of these*/

FROM tbl as t

GROUP BY Ten_Ref
)

select  p.ten_Ref
        /*A concatenation to put them all in one field, only problem is you end up with loads of spare commas*/
        ,[String] = isnull (p.HB,0) + ',' + isnull (p.cb,'') + ',' + isnull (p.wtc,'')

from [pivot] as p

My problem is not ever ten_ref has all of the Benefits attached. Using this code, where there is a gap or NULL then I end up with loads of double commas e.g 'HB,,WTC'

How can I get it so it is only one comma, regardless of the amount of benefits each tenancy has?

Thanks - (first post)

Delete Data From Table of Specific Month

In My Table, I Have a Column Which Stores the Date. I am Passing Month Number as an argument to a stored Procedure.

I would like to delete all entries of that month from table.

Is it Possible....??

ASCII error when fetching results from database with pyodbc and sql server 2005

I have this error when fetching results from the database.

UnicodeEncodeError: 'ascii' codec can't encode character u'\xf1' in position 3: ordinal not in range(128)

Upon closer inspection, I found out that several of the records have ñ and Ñ in them. And I cannot just replace them or ignore them because my db are records for personal information and cannot be changed. I have read many links regarding this and most of it says that I have to replace them or remove them. I have also tried using what's in here but it's not applicable when database is involve. Also, I tried this one, and this one but nothing seems to answer my problem. I am formatting the fetched results like this:

cursor.execute("select * from dummy")
str1=''
for row in cursor:
    str1 = str1 + ''.join(map(str,row)) + "\n" 
print str1

and it outputs the error above. However, when I try this one:

for row in cursor:
    str1 = str(row)
    print str1.decode('latin-1')

It display NO errors but the results were:

(u'Ara\xf1as    ', )
(u'Para\xf1aque ', )
(u'Ma\xf1a\xf1a    ', )
(u'Di\xd1a      ', )

instead of

Arañas
Parañaque
Mañaña
DiÑa

How can I solve this ascii error?

I am using SQL Server 2005 facing multiple rows displaying error

Here's my Table1 structure

MRNO   IPNO  PLNO
1      2     1324
2      3     1325
3      4     1326

Table2 structure

MRNO   IPNO  PLNO   PLNDT      PLNTM
1      2     1324   20140430   13:24
1      2     1324   20140430   15:12
1      2     1324   20150501   12:01
1      2     1324   20150501   16:01
1      2     1324   20150501   17:21
1      2     1324   20150502   10:11
1      2     1324   20150502   13:01
1      2     1324   20150502   15:13

Here's my required output I would like to show the data as follows

MRNO    IPNO   30TH_PLNTM_DATA      01ST_PLNTM_DATA  02ND_PLNTM_DATA
1       2       13:24               12:01            10:11
1       2       15:12               16:01            13:01
1       2                           17:21            15:13

SQL code:

SELECT  
   MRNO, IPNO, 
   30TH_PLNTM_DATA.PLNTM,
   01ST_PLNTM_DATA.PLNTM,
   02ND_PLNTM_DATA.PLNTM
FROM   
   TABLE1 T1
LEFT JOIN 
   TABLE2 30TH_PLNTM_DATA ON 30TH_PLNTM_DATA.PLNO = T1.PLNO 
                          AND 30TH_PLNTM_DATA.PLNDT = '20150430'  
LEFT JOIN 
   TABLE2 01ST_PLNTM_DATA ON 01ST_PLNTM_DATA.PLNO = T1.PLNO 
                          AND 01ST_PLNTM_DATA.PLNDT = '20150501'
LEFT JOIN 
   TABLE2 02ND_PLNTM_DATA ON 02ND_PLNTM_DATA.PLNO = T1.PLNO 
                          AND 02ND_PLNTM_DATA.PLNDT = '20150502'

But that query is not getting the above format data..

Please anyone have any idea?

Thanks,

~San

Regular expressions in TSQL

In cell e_vis_name i have organization structure where divisions divided with \ symbol, e.g.

• Moscow\Direction
• Yaroslavl\Sales
• Omsk\Commercial center\Sales

I need to cut everything after first \ symbol to get the following result: - Moscow - Yaroslavl - Omsk

How can I do it?

SQL Job with TRY reports success on failure when inserting from remote SP

I've found a way to fail a SQL Scheduled Job (with severity 16) that does not report failure (and so does not send email notifications). I've fixed my immediate issue, but I want to know why there is a failure case that does not report as failure, and if there are any other surprising ways to miss notification.

I've set up two linked servers and am attempting to run an hourly scheduled SQL Job on one that queries the other. I found this morning that the code in the SP had not been running, but the history on the Job was reporting success. The Job's only step is EXEC _testSP. If I ran EXEC _testSP in a query window from SSMS, I received this error message:

Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

The SP's contents are wrapped in TRY ... CATCH. If I remove the TRY ... CATCH, executing the SP gives up this error message:

Msg 213, Level 16, State 7, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

This made sense. The remote table was referenced with a SELECT * FROM and some columns had been added to it. I've removed the asterix and the job runs fine now, but I want to make sure that all future exceptions get logged either by the job failure notification, or the CATCH block in _testSP. I don't understand why this one didn't get logged, and I hope that someone can explain it to me.

The job runs and fails and notifies just as I would expect when the TRY ... CATCH wrapping is removed, but we have some important things in the TRY ... CATCH that need to be kept.

This is not a duplicate of this related question. The Microsoft BOL for TRY...CATCH says that some exceptions cannot be caught by TRY...CATCH. It may be related, but what I've found is an exception that is not caught by the Scheduled Job agent.

Reproduceable example: (also try removing the TRY...CATCH wrapper and see the change)

USE [RemoteServer].[Database]
CREATE TABLE [Tally](
    [ID] [int] IDENTITY(0,1) NOT NULL,
    [ID2] [int] NOT NULL
) ON [PRIMARY]
GO

USE [LocalServer]
-- Setup procedure
    CREATE PROCEDURE _testSP
    AS
    BEGIN
        SET NOCOUNT ON;

        BEGIN TRY

        -- Create destination temp table
            CREATE TABLE #tempb (a int)

        -- Insert into temp table from remote Tally table
            DECLARE @query nvarchar(4000)
            SELECT @query = '
                SELECT TOP 5 *
                FROM [Database].[dbo].Tally
            '
            INSERT INTO #tempb
            EXEC [RemoteServer].[master].[dbo].sp_executesql @query

        END TRY BEGIN CATCH
            -- Rethrow the exception
            DECLARE @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
            SELECT
                @ErrorMessage = 'Handled Exception: ' + ERROR_MESSAGE() + ' line ' + CAST(ERROR_LINE() as nvarchar(5)),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
        END CATCH

    END
    GO

-- Setup job
    DECLARE @database varchar(100)
    SELECT @database = DB_Name()
    EXEC msdb.dbo.sp_add_job
        @job_name = '_testSPJob'
    EXEC msdb.dbo.sp_add_jobstep
        @job_name = '_testSPJob',
        @step_name = '_testSPJob',
        @subsystem = N'TSQL',
        @command = 'EXEC _testSP',
        @database_name = @database
    EXEC msdb.dbo.sp_add_jobserver
        @job_name =  '_testSPJob',
        @server_name = @@SERVERNAME
    GO

-- Manual execution fails
    EXEC _testSP
    GO

-- Run job
    EXEC msdb.dbo.sp_start_job
        @job_name = '_testSPJob'
    WAITFOR DELAY '00:00:02'
    GO

-- Select job history
    SELECT * FROM msdb.dbo.sysjobhistory
    WHERE step_name = '_testSPJob'
    ORDER BY run_date, run_time
    GO

I really need to convince the bosses to get off SQL 2000. Here are my software versions. Perhaps this is fixed in later versions of SQL?

SSMS Version: 2012 (11.0.5058.0)
Local DB: SQL 2005 (9.0.5069)
Remote DB: SQL 2000 (8.0.760)

How to create an android app which gets and post data to SQL server 2005 hosted on IIS using WCF

I am a newbie in this vast world of android. I am really struggling very hard in understanding how to create an android application which can communicate with SQL Server 2005 through WCF Service hosted on IIS. I know it sounds duplicate, but please believe me , i tried all of them :(

WHAT I WANT TO CREATE I am looking to create an app which will authenticate a user and will display its user details. Thus on login activity i am looking to send the username and password to the webservice which will then retrieve the data from SQL Server. If credentials are valid then app will call another activity which will display the

After many days of research i finally found some code which creates a WCF service and sends back a JSON String.

Can someone please help me or share some links , resources, ebooks basically anything which suggests how to consume on android application a WCF service returning JSON hosted on IIS. If possible any help around how to create a login page that authenticates a user credential will be marvelous.

Link URL :- http://localhost/JsonWcfService/GetEmployees.svc/json/employees String returned :- {"GetAllEmployeesMethodResult":[{"firstname":"James ","lastname":"Bond ","salary":51622.22},{"firstname":"Nishant ","lastname":"Godbole ","salary":55211652.55},{"firstname":"Tom ","lastname":"Cruse ","salary":2932.55}]}

The code which i found on internet. It has some flaws which took me a while to figure out :) http://ift.tt/1EZ73Ns

How to migrate SQL Server 2005 to ANOTHER server with SQL Server 2012?

Old server has Windows Server 2003 and SQL Server 2005. New server has Windows Server 2012R2 and SQL Server 2012. We need to move database to the new server because windows server 2003 is end of life. What is the best practice to migrate it?

how to use openxml within a user defined function in sql server

I am running into problems. I have an XML structure that I parse using OPENXML within a stored procedure to retrieve parameters used to perform a query. This procedure was a base proc that a different stored procedure (procedure 2) is calling. Procedure 2 uses an insert-exec construct to get the data from the base procedure. This works great as long as we only call Procedure 2 or the base procedure.

My first problem is that I have a different procedure (procedure 3) that now needs to get the result from procedure 2 (I need the business rules that this procedure enforces), but cannot due to the message "An INSERT EXEC statement cannot be nested".

I then tried to take the base procedure and make it a table valued function, but when I execute it, I receive the message "Only functions and some extended stored procedures can be executed from within a function."

How do I get around one or both of these issues?

Thanks,

Changing boolean values from VB.NET

I have a function on VB.NET that deletes the selected record, I would like to change it so instead it updates a boolean field from false to true

This is the actual code block:

<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, True)> _
Public Function BLL_tblClient_DeleteByPK_ClientID(ByVal ClientID As Integer) As Boolean

Dim rowsAffected As Integer = Adapter.Delete(ClientID)

Return rowsAffected = 1

End Function

Field on the table is Inactive so a simple sql way of doing this would be UPDATE tblClients SET Inactive = 1 WHERE ClientID = ClientID but how can I accomplish that from VB.NET?

Create datetime2 as custom datatype in SQL Server 2005

Many have come across the fact that SQL Server 2005 doesn't support datetime2. I was wondering if I can add it as a custom datatype instead.

I created a custom type with the name datetime2 so that's done.

Now I need to set the min date value, but is that even possible, since the custom type is based on the datetime type?

MSSQL Loop table, Populate new ID

I need to populate a column nvarchar(12) with a unique sequence starting at "PM1000000000" and increment by 1 for each row. There is no identity column to loop around which makes this problem quite challenging as most of the example I found use an identity column to loop around

I found an example, example 3 on MSDN at http://ift.tt/1KQqxpD but it doesn't show incrementing a row

Can anyone help me populate this field so that I can make this a primary key? Note: this table has 60 million rows but I'm open to any ideas at this point to just get this working

How to change output of stored procedure on the fly in this query?

I am trying to achieve something which I think is simply, but yet the syntax escapes me

Here's the query:

SET @T02_IDENTIFIANT_DSC = 'Hello ' + @Compagnie + ' '
INSERT INTO @TableDonnees
EXEC THE_DATABASE..spHeader @ValDate, @ValDate, '65', @T02_IDENTIFIANT_DSC, @RecCount

spHeader returns a record with two rows. How could I catch the value of one of the rows, modify it, then proceed to the INSERT ?

Thanks

SQL Server: Full Outer Join, On vs. Where

I've searched for this and can't seem to find an answer. I apologize in advance as I'm sure this answer is out there, but I can't seem to find it.

I'm working with a SQL Server 2005 DB, and I'm aware that the query below doesn't represent a normalized DB, since the numPlacements field is in both the detail and rollup table. I didn't create the DB.

The below SQL gives the expected result when the where clause is used. The expected result is all rows where a matching value is missing from either table, or the two values don't match.

However, if I comment the where clause and uncomment the final AND in the ON clause, it returns over 200k rows instead of the expected 120 results.

SELECT CASE WHEN A.ID is NULL THEN B.ID ELSE A.ID END,
       A.numPlacements AS 'AnumPlacements', 
       B.numPlacements  AS 'bnumPlacements',
       B.numPlacements - A.numPlacements as 'Variance'
FROM   (SELECT ID,
               Sum(numPlacements) AS 'numPlacements' 
        FROM   PlacementDetailLevel
        GROUP  BY ID) A 
       FULL OUTER JOIN (SELECT ID,
                               Sum(numPlacements) AS 'numPlacements' 
                        FROM   PlacementRollupLevel
                        GROUP  BY ID) B 
                    ON A.ID = B.ID 
                       --AND B.numPlacements <> A.numPlacements 
 WHERE  A.numPlacements <> B.numPlacements or A.numPlacements is null or B.numPlacements is null

Any ideas as to why?

How to copy files from sql server 2005 vm to sql server 2012 vm

I want to copy few files from E drive from my VM which has sql server 2005 and paste it to another VM which has sql server 2012. But i am not able to do it,as paste option is not available in my sql server 2012 vm. What is wrong about this copy and paste. could anyone please help me out in this

Sql DATEDIFF keeps ignoring seconds part of date

I am getting 0 on executing the following statement:

select DATEDIFF(mi,'1970-01-01 00:00:00','1970-01-01 00:00:01') * CONVERT(BIGINT,60)*1000  as BidTicks    

Whereas I get 6000 on executing this:

select DATEDIFF(mi,'1970-01-01 00:00:00','1970-01-01 00:01:01') * CONVERT(BIGINT,60)*1000  as BidTicks    

What are my options?

Reduce SQL query execution time

I have a situation where our web application generates a Crystal Report based on the query result which takes almost 1 hr to execute.

I know this is crazy but, can anyone suggest any optimization or changes in the following giant query.

Thanks.

Note: I have shorten some of the similar queries.

CREATE procedure [dbo].[sp_ExportRevenueMenifest_27FEB] 
   (@vessel varchar(10), @voyage varchar(10),
    @call varchar(10), @GLC varchar(10),
    @Port varchar(10), @str varchar(200)) 
AS 
   create table #OCF (PortCode varchar(10), 
                      PortName varchar(40),
                      BLNumber varchar(20), RS decimal(18,4),
                      RS1 decimal(18,4), Consignee varchar(500),
                      NoPackages varchar(50), Vessel varchar(10),
                      Vayage varchar(10), Call char(2),
                      Vessel_Name varchar(50),
                      Sailed_Date varchar(20), ExRate decimal(8,4)
                     )
insert into #OCF 
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'OCF')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode


create table #DDC (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #DDC 
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'DDC')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode

create table #FUM (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #FUM
SELECT     a.PortofDischarge, ....., a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ....INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'FUM')
GROUP BY Port_Master.Port_Name,.... B.ConfigCode


create table #PSS (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #PSS
SELECT     a.PortofDischarge, .... a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'PSS')
GROUP BY Port_Master.Port_Name, ....., B.ConfigCode


create table #BAF (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #BAF
SELECT     a.PortofDischarge, .... a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ...... ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'BAF')
GROUP BY Port_Master.Port_Name, ..... B.ConfigCode


create table #HD (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #HD
SELECT     a.PortofDischarge, ..... a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN

                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      ...... ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'HD')
GROUP BY Port_Master.Port_Name, ..... B.ConfigCode

create table #WRS (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #WRS
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'WRS')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode


create table #DIV (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #DIV
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'DIV')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode

create table #PAN (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #PAN
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'PAN')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode
create table #CAF (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #CAF
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'CAF')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode

create table #CDR (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #CDR
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      ..... ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'CDR')
GROUP BY Port_Master.Port_Name, .... B.ConfigCode

create table #THC (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18),RS1 decimal(18),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #THC
SELECT     a.PortofDischarge, ......, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ......
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'THC')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode

create table #INL (PortCode varchar(10),....,ExRate decimal(8,4))
insert into #INL
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtRupee) AS PrepaidAmtRupee,SUM(B.CollectAmtRupee) AS collectAmtRupee, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      ......
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'INL')
GROUP BY Port_Master.Port_Name, ......, B.ConfigCode

create table #ARB (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #ARB
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtRupee) AS PrepaidAmtRupee,SUM(B.CollectAmtRupee) AS collectAmtRupee, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'ARB')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate, B.ConfigCode


create table #DOC (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #DOC
SELECT     a.PortofDischarge, .... a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      .... ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (B.ConfigCode = 'DOC')
GROUP BY Port_Master.Port_Name, ...., B.ConfigCode


create table #THCUSD (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #THCUSD 
SELECT     a.PortofDischarge, .... a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      .... ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (b.ConfigCode='THC')
GROUP BY Port_Master.Port_Name,...., a.ExchangeRate


create table #INLUSD (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #INLUSD 
SELECT     a.PortofDischarge, .... a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      .....ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (b.ConfigCode='INL')
GROUP BY Port_Master.Port_Name,.... a.ExchangeRate

create table #ARBUSD (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #ARBUSD 
SELECT     a.PortofDischarge, ....., a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      .....
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND (b.ConfigCode='ARB')
GROUP BY Port_Master.Port_Name,.... a.ExchangeRate


create table #OTH (PortCode varchar(10), PortName varchar(40),BLNumber varchar(20), RS decimal(18,4),RS1 decimal(18,4),Consignee varchar(500),NoPackages varchar(50),Vessel varchar(10),Vayage varchar(10),Call char(2),Vessel_Name varchar(50),Sailed_Date varchar(20),ExRate decimal(8,4))
insert into #OTH
SELECT     a.PortofDischarge, Port_Master.Port_Name, a.BLNumber, SUM(B.PrepaidAmtUSD) AS PrepaidAmtUSD,SUM(B.COLLECTAmtUSD) AS COLLECTAmtUSD, a.Consignee, a.NoPackages, a.Vessel, 
                      a.Voyage, a.Call, Vessel_Master.Vessel_Name, ExportVoyage_Master.Sailed_Date, a.ExchangeRate
FROM         BLMaster a INNER JOIN
                      BL_ChargesDetails B ON a.BLID = B.BLID INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code INNER JOIN
                      Port_Master ON a.PortofDischarge = Port_Master.Port_Code
WHERE     (a.ImportExport = 2) AND b.ConfigCode NOT IN('OCF','FUM','BAF','YAS','CAF','HD','DIV','PSS','CAF','PSS','PAN','WRS','CDR','THC','INL','DDC','ARB','DOC','STX')
GROUP BY Port_Master.Port_Name, a.BLNumber, a.Consignee, a.NoPackages, a.Vessel, a.Voyage, a.Call, Vessel_Master.Vessel_Name, 
                      ExportVoyage_Master.Sailed_Date, a.PortofDischarge, a.ExchangeRate



DECLARE @Query varchar(8000)
SET @Query = ''




SELECT @Query ='SELECT     Port_Master.Port_Name,Vessel_Master.Vessel_Name, Vessel_Master.Vessel_Code, ExportVoyage_Master.Voyage_Code, ExportVoyage_Master.Sailed_Date, 
                      Vessel_Master.Nationality, ExportVoyage_Master.Master, a.Portofloading,a.PortofDischarge,a.FinalDestination, ExportVoyage_Master.Call,ExportVoyage_Master.Berth_Date,a.BLNumber,a.Shipper, a.Consignee,a.port,
                      a.MarksNo, a.GoodsDescription, a.NoPackages, BL_ClubbedContainer.ContainerNo,   
                      BL_ClubbedContainer.TypeSize, A.ExchangeRate, a.BrokerCode,
                     isnull(OC.RS,0)OCF,isnull(FU.RS,0)FUM,isnull(BA.RS,0)BAF,isnull(CA.RS,0)CAF,isnull(HD.RS,0)HD,isnull(DI.RS,0)DIV,isnull(PS.RS,0)PSS,isnull(PA.RS,0)PAN,isnull(WR.RS,0)WRS,isnull(CD.RS,0)CDR,isnull(TH.RS,0)THC,isnull(DD.RS,0)DDC,isnull(INL.RS,0)INL,isnull(AR.RS,0)ARB,isnull(DO.RS,0)DOC,isnull(OT.RS,0)OTH,isnull(THUS.RS,0)THCUSD,isnull(INUS.RS,0)INLUSD,isnull(ARUS.RS,0)ARBUSD,isnull(OC.RS1,0)OCF1,isnull(FU.RS1,0)FUM1,isnull(BA.RS1,0)BAF1,isnull(CA.RS1,0)CAF1,isnull(HD.RS1,0)HD1,isnull(DI.RS1,0)DIV1,isnull(PS.RS1,0) PSS1,isnull(PA.RS1,0) PAN1,isnull(WR.RS1,0) WRS1,isnull(CD.RS1,0)CDR1,isnull(TH.RS1,0)THC1,isnull(DD.RS1,0)DDC1,isnull(INL.RS1,0)INL1,isnull(AR.RS1,0)ARB1,isnull(DO.RS1,0) DOC1,isnull(OT.RS1,0)OTH1,isnull(THUS.RS1,0)THCUSD1,isnull(INUS.RS1,0)INLUSD1,isnull(ARUS.RS1,0)ARBUSD1
FROM         BLMaster a LEFT OUTER JOIN

                     BL_ClubbedContainer ON a.BLNumber = BL_ClubbedContainer.BLNumber INNER JOIN
                      ExportVoyage_Master ON a.Vessel = ExportVoyage_Master.Vessel_Code AND a.Voyage = ExportVoyage_Master.Voyage_Code AND 
                      a.Port = ExportVoyage_Master.Port AND a.Call = ExportVoyage_Master.Call INNER JOIN
                      Vessel_Master ON ExportVoyage_Master.Vessel_Code = Vessel_Master.Vessel_Code inner join port_master on a.FinalDestination=Port_Master.Port_Code
left outer join #OCF OC on OC.blnumber=a.blnumber 
left outer join #DDC DD on DD.blnumber=a.blnumber 
left outer join #FUM FU on FU.blnumber=a.blnumber 
left outer join #BAF BA on BA.blnumber=a.blnumber 
left outer join #HD HD on HD.blnumber=a.blnumber 
left outer join #DIV DI on DI.blnumber=a.blnumber 
left outer join #WRS WR on WR.blnumber=a.blnumber 
left outer join #CAF CA on CA.blnumber=a.blnumber 
left outer join #PSS PS on PS.blnumber=a.blnumber 
left outer join #PAN PA on PA.blnumber=a.blnumber 
left outer join #CDR CD on CD.blnumber=a.blnumber 
left outer join #THC TH on TH.blnumber=a.blnumber 
left outer join #INL INL on INL.blnumber=a.blnumber 
left outer join #ARB AR on AR.blnumber=a.blnumber 
left outer join #DOC DO on DO.blnumber=a.blnumber 
left outer join #OTH OT on OT.blnumber=a.blnumber 
left outer join #THCUSD THUS on THUS.blnumber=a.blnumber 
left outer join #INLUSD INUS on INUS.blnumber=a.blnumber 
left outer join #ARBUSD ARUS on ARUS.blnumber=a.blnumber 
WHERE     (a.ImportExport = 2) '+ @str + ' order by a.Portofdischarge,a.blnumber asc'
EXEC (@Query)

steps to upgrade database from sql server 2005 to sql server 2012

Could anyone please help me out in knowing the step by step process to be done to upgrade the database from sql server 2005 to sql server 2008. I already surfed about the process but i am not able to understand the complete idea and i dont know on how to start this process.Could some one please help me on this.

Select nth to nth row while table still have values unselected with python and pyodbc

I have a table with 10,000 rows and I want to select the first 1000 rows and then select again and this time, the next set of rows, which is 1001-2001.

I am using the BETWEEN clause in order to select the range of values. I can also increment the values. Here is my code:

count = cursor.execute("select count(*) from casa4").fetchone()[0]    
ctr = 1
ctr1 = 1000
str1 = ''
while ctr1 <= count:
    sql = "SELECT AccountNo FROM ( \
        SELECT AccountNo, ROW_NUMBER() OVER (ORDER BY Accountno) rownum \
        FROM  casa4 ) seq \
        WHERE seq.rownum BETWEEN " + str(ctr) + " AND " + str(ctr1) + ""
    ctr = ctr1 + 1
    ctr1 = ctr1 + 1000
    cursor.execute(sql)
    sleep(2) #interval in printing of the rows.

for row in cursor:
    str1 = str1 + '|'.join(map(str,row)) + '\n'
print "Records:" + str1 #var in storing the fetched rows from database.
print sql #prints the sql statement(str) and I can see that the var, ctr and ctr1 have incremented correctly. The way I want it.

What I want to achieve is using a messaging queue, RabbitMQ, I will send this rows to another database and I want to speed up the process. Selecting all and sending it to the queue returns an error.

The output of the code is that it returns 1-1000 rows correctly on the 1st but, on the 2nd loop, instead of 1001-2001 rows, it returns 1-2001 rows, 1-3001 and so on.. It always starts on 1.

Sum Colum case () after pivot

Sorry if I don´t explain very well, and the title isn´t very clear. I´m using SQL Server 2005. I have a query with Pivot that is working fine, but know I must add a new query that get resuls from quarterly.

This is my query to get result from Month

    WITH PivotData AS
(
SELECT  idWatimetro, mes,ano, valor
FROM   E_Registros_Watimetros where ano = 2012
)
SELECT *
FROM PivotData 
PIVOT(SUM(valor) FOR idWatimetro IN ([1],[2],[3],[4],[5] AS P order by mes;

So, know only I want to get four registres

1 With Month 1+2+3
2 Wint Month 4+5+6
3 With Month 7+8+9
4 Wint Month 10+11+12

I have been trying with UNION ALL but don´t work as I expected, Any help will be gratefull, and sorry for my bad english and explanation.

This is my query without result

SELECT  * 
FROM    (
            SELECT  idWatimetro, ano,mes, valor
            FROM E_Registros_Watimetros   
            WHERE (ano = 2012 and mes = 1 ) or (ano = 2012 and mes = 2)or (ano = 2012 and mes = 3)
            UNION ALL
            SELECT  idWatimetro, ano,mes, valor
            FROM E_Registros_Watimetros    
            WHERE (ano = 2012 and mes = 4 ) or (ano = 2012 and mes = 5)or (ano = 2012 and mes = 6)
        ) AS SourceTable
PIVOT(SUM(valor) FOR idWatimetro IN ([1],[2],[3],[4],[5]))AS P

Thanks advanced.

Excute a file in Multple server and place the output file in single location

I need to install the one file(.exe) over multiple servers and it would extract shows the server info and generate one output file after executing finished. So I request you how to place a output file in single server folder and execute the file across different domains. Each domain consists of multiple servers and have to handover the output files(executing the file across multiple servers) to our customers. It is difficult to execute the file in multiple servers and gathering the output file in multiple servers and placed into single server and handover to customers is a bit time consuming process. Team, Please request you to share few ideas or remediation plan on this hectic issue.

Regards, Chaitanya

sql Add up fieilds into one column and column

Im really struggling with this one. Looked at a few examples (more than a few to be honest) and I cannot wrap my wee brain around it. I want to sum two fields, show that total and then do a percent calculation. Is this possible?

asgn_id    pyt_itemcode asgn_type   Miles   Total Mile's    % of Total
   BLARE   COMILE          DRV      31083     31083            1.00 
   BOCA    COMILE          DRV      6930                       0.13 
   BOCA    COTEAM          DRV      44669    51599             0.87 
   BOWD    COTEAM          DRV      49941    49941             1.00 
   BOWN    COMILE          DRV      138                        0.00 
   BOWN    COTEAM          DRV      43021    43159             1.00 

Convert date to readable format

I have a column that stores dates and other that stores times, but they are not in a readable format i.e.

Date      Time
42017     0,453545345351222
42015     0,022323223232333

I suppose that the values in "Date" corresponds to the number of days since 1900-01-01 and the value in "Time" is the fraction of a day i.e. 0,45 is more or less 10h40m00s.

In sql server 2005 how can I convert these values to human readable format?