SQL View generate current YTD total for GL periods

My SQL skills are fairly basic and I'm trying to create a SQL View that will give me the current YTD figure for each of my GL Accounts - showing all 12 periods and then listing them as rows. I have a GL Summary table that gives me the opening DR and CR, then a DR and CR for each of the 12 periods. I wrote the code below but it only gives me point in time, it needs to be a running total (ie take the opening CD/DR then add the P1 CR/DR, then take that total and add the P@ CR/DR, etc). I can't work out how to do this without using a temp table. The image below shows there was a 500.00 DR transaction in P9 which made the balance 500.00. Period 10, 11 and 12 should also show as 500.00 not 1,000.00. Can anyone help please?

   GLAccount    Description FiscalYear  Period  Total
10105-30-30-10-05-00    Petty Cash  2015    P1  1,000.00
10105-30-30-10-08-00    Petty Cash  2015    P2  1,000.00
10105-30-30-10-12-00    Petty Cash  2015    P3  1,000.00
10105-30-30-10-50-00    Petty Cash  2015    P4  1,000.00
10105-30-30-10-92-00    Petty Cash  2015    P5  1,000.00
10105-30-30-10-95-00    Petty Cash  2015    P6  1,000.00
10105-30-30-10-97-00    Petty Cash  2015    P7  1,000.00
10105-30-30-20-05-00    Petty Cash  2015    P8  1,000.00
10105-30-30-20-08-00    Petty Cash  2015    P9  500.00
10105-30-30-20-50-00    Petty Cash  2015    P10 1,000.00
10105-30-30-20-51-00    Petty Cash  2015    P11 1,000.00
10105-30-30-20-97-00    Petty Cash  2015    P12 1,000.00

---

    SELECT  gl.MaskedAlias AS GLAcct,
    gl.Description AS GLDesc,
        gl.FiscalYear,
    unpvt.Period,
    unpvt.Total
FROM(SELECT DISTINCT gltsum.GLAcct,gltsum.FiscalYear,gla.MaskedAlias, gla.Description
FROM GLTranSumm AS gltsum
JOIN GLAcct AS gla ON (( gltsum.GLAcct ) = ( gla.AcctIndex ))
WHERE gltsum.FiscalYear >= 2015 ) gl 
LEFT OUTER JOIN
( SELECT glts.GLAcct,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer01CR,0)+ ISNULL(glts.TranPer01DR,0)) P1,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer02CR,0)+ ISNULL(glts.TranPer02DR,0)) P2,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer03CR,0)+ ISNULL(glts.TranPer03DR,0)) P3,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer04CR,0)+ ISNULL(glts.TranPer04DR,0)) P4,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer05CR,0)+ ISNULL(glts.TranPer05DR,0)) P5,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer06CR,0)+ ISNULL(glts.TranPer06DR,0)) P6,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer07CR,0)+ ISNULL(glts.TranPer07DR,0)) P7,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer08CR,0)+ ISNULL(glts.TranPer08DR,0)) P8,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer09CR,0)+ ISNULL(glts.TranPer09DR,0)) P9,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer10CR,0)+ ISNULL(glts.TranPer10DR,0)) P10,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer11CR,0)+ ISNULL(glts.TranPer11DR,0)) P11,
    SUM((glts.OpenTranCR)+ ISNULL(glts.OpenTranDR,0) + ISNULL(glts.TranPer12CR,0)+ ISNULL(glts.TranPer12DR,0)) P12
    FROM GLTranSumm AS glts
    WHERE glts.GLType = 'Accrual' AND glts.FiscalYear >= 2015
    GROUP BY glts.GLAcct) p
UNPIVOT
(
Total FOR Period IN (P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12)
) AS unpvt
ON unpvt.GLAcct = gl.GLAcct

How do you do a BULK Export to a CSV using a SQL 2005 Query?

I have simple Bulk Inserts down. I am trying to take the same type of query and write to a file instead of reading from a file. The opposite of BULK Insert from a File.

If you need a scenario for the example below, I am creating a table from a text file, running a DISTINCT query to remove duplicate rows/lines and then I want to output the deduplicated result to another text file.

My simple BULK Insert query is:

CREATE TABLE dbo.TEMP 
(NUMBER40 VARCHAR(16), 
BIN VARCHAR(6))

BULK INSERT dbo.TEMP 
FROM 'c:\install\InputWithDupes.txt' 
WITH 
(FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n');

I am trying to do this:

SELECT DISTINCT NUMBER40
FROM dbo.TEMP
INTO OUTFILE 'C:\install\DeDupedOuput.txt'
WITH
(FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n');

I would also like to be able to add some logic like WHERE NUMBER40 LIKE '123%' if possible.

Exclude from result if Sum = 0

How to exclude records from the result if (+)LotQty has the same with (-)LotQty but different Transtype.

Example : from this

Docentry  Transtype  LotQty
4594      67          250.000000
4643      60         -250.000000
9253      67          100.000000
16822     60         -200.000000

to this

Docentry  Transtype  LotQty
9253      67         100.000000
16822     60        -200.000000**

connect to an sql server 2005 from a windows mobile

i am new to this site, hope that you won't laugh at my problem, i have a new type of project for me to build, i need to make an app for a motorola symbol mc9090 barcodescanner running windows mobile 6. i need to connect from this app to a sql server 2005 database, can i connect with an ado connection like the one below? the port is open. i use windows classic emulator for debugging but it crashes on myconn.open().

thank you very much for your answers.

SqlConnection myConn = new SqlConnection(@" Data Source=***.***.0.***,1443;Initial Catalog=test_database;User ID=****ica;Password=********;");
        try
        {
            myConn.Open();

            SqlCommand comm = new SqlCommand("select * from test_table where id = 1", myConn);

            SqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                MessageBox.Show(reader[0].ToString() + "\t" + reader[1].ToString());
            }

        }catch(Exception ex){MessageBox.Show(ex.Message);}
        finally{ myConn.Close();}
    }

Simple SQL Insert - Many Comma Delimited Values from Textbox to ONE SQL Column

This should be simple. I have a textbox (textarea) with comma separated values. Like this:

425020,547538,548029,548853,552373

I have done this two ways. One with a table that has two columns, |Number6|Number16| ... and one that just has one column |Number6| in order to try and remove any confusion. For what is below they are being run against the one column table.

Here are four of the ways I tried:

INSERT INTO MYDB.dbo.MYTABLE (Number6) VALUES (425020,547538,548029,548853,552373);
INSERT INTO MYDB.dbo.MYTABLE VALUES (425020,547538,548029,548853,552373);
INSERT INTO MYDB.dbo.MYTABLE (Number6) VALUES (425020),(547538),(548029),(548853),(552373);
INSERT INTO MYDB.dbo.MYTABLE VALUES (425020),(547538),(548029),(548853),(552373);

Since I am submitting this via an ASP page I am trying to avoid writing an insert line for every value. I have had over 20,000 values before.

Obviously the above code failed because in the first and third insert each comma indicates a column to SQL. In the second and fourth insert it is incorrect syntax near ","

I have built much more complicated queries and yet for some reason I can't figure out this simple insert.

SQL Server Query- How to Prune a table

I have a Table 'L2'

Itemset      Itemset2      
   1             3
   2             3
   2             5
   3             5

I created a [combination in pair of three] for these values in columns in table 'c3'

Itemset       Itemset2       itemset3
   1              3             5
   2              3             5
   1              2             3
   1              2             5

Like in Apriori I want to prune the table C3.

i.e. Getting this table as output 'C3Prune'

 Itemset       Itemset2       itemset3
   2              3             5

I want to create a SQL Server query for the same, I tried loops but it's not correct.

Please don't down vote the question if the question is not clear. I shall explain as many times needed in the comments. Thank you in advance.

How to generate script INSERT from result?

I have two databases in different SQL Server which are not connected each other. I want to insert selected lignes from my first DB (2008) to second DB (2005). I want to generate INSERT script, in order to execute it in DB 2005.

I found how to export all database, or all lignes in one table, but nothing about how to export only selected lignes ?

Any idea ?

Why is the performance of these scripts so different?

We are trying to migrate ~650 million records to a new database on the same server and are seeing extremely discrepant performance between the two alternatives being tested right now. The first script is below, and uses a reference table with beginning and end time intervals and a cursor to build out insert statements. The alternative is using the same reference table to actually create ~26000 unique insert statements and executing them.

Option 1 (ran for 16 hours and only moved ~2% of total records):

--Declare scoped variables
DECLARE @migStart datetime, @migEnd datetime;

--Set up cursor for reference intervals
DECLARE migCur CURSOR FOR
SELECT dttm_interval_start, dttm_interval_end
FROM DBA.dbo.intervalReference
WHERE complete = 0
ORDER BY dttm_interval_start;

OPEN migCur;

--Initialize the cursor
FETCH NEXT FROM migCur
INTO @migStart, @migEnd;

--Iterate through the reference intervals
WHILE (@@FETCH_STATUS = 0)
BEGIN

    --Move the records as >= start and < end
    INSERT INTO NEW.dbo.new_log(field1, field2, field3, ......, field19)
    SELECT field1, field2, field3, ......, field19
    FROM OLD.dbo.old_log
    WHERE log_dt >= @migStart and log_dt < @migEnd;

    --Mark the interval as done, capture record count
    UPDATE DBA.dbo.intervalReference
    SET complete = 1, records = @@ROWCOUNT
    WHERE dttm_interval_start = @migStart and dttm_interval_end = @migEnd;

    --Fetch the next cursor item
    FETCH NEXT FROM migCur
    INTO @migStart, @migEnd;

END;

CLOSE migCur;
DEALLOCATE migCur;
GO

Option 2 (our calculations project this one to finish in ~65 hours total. There are ~26000 of these, each one covers a different time interval.)

INSERT INTO new.dbo.new_log(field1, field2, field3, ......, field19)
    SELECT field1, field2, field3, ......, field19
    FROM OLD.dbo.old_log
    WHERE log_dt >= '2013-02-17 12:00:00.000' and log_dt < '2013-02-17 14:00:00.000';

I understand that cursors are not performant, but are they really so bad that I can't even use them to iterate through a reference table to control query bounds?

I have looked at the execution plan for the script vs the individual insert statements and the number of records expected at the insert step is ~190 million, way more than the 140k record batch size we calculated the time intervals around. Could it be that the statistics are misleading the engine about the best way to optimize?

Do queries in loops get optimized?

Why is Option 2 so much more performant than Option 1?

MS SQL INSERT INTO trouble

I need to insert new record in table. The first column name ob_no is int, not null. So I need generate number which is maximum ob_no at the moment +1. How can I do it? Something Like (max(ob_no) + 1) but it doesn't work in SQL 2005. Thanks for any ideas.

INSERT INTO et_thanks_2014 (ob_no, c_name)
VALUES (???, 'Some Text')

Need help timeout expired when restoring database using SQL Server 2005 Management Studio

Here's my script:

RESTORE DATABASE [database 2.2] <br>
FROM  DISK = N'F:\database Backup\database Backup 20.Mar.2015\database2.2.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

result:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Msg -2, Level 11, State 0, Line 0

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Please help I need to restore my database. I am using SQL Server 2005 Management Studio.

Thanks

String or binary data would be truncated. The statement has been terminated.?

If Val(dr("USE_Amount").ToString) >= Val(txtprice.Text) Then dr.Close() msg = "Ref No : " + ref_no + ". Your mobile no : " + txt_mob.Text + " Talk time : " + txttalk.Text + " Validity : " + txtval.Text cmd = New SqlCommand("update TBL_Banking set USE_Amount=USE_Amount-" + txtprice.Text + " where USE_creditcard='" + txt_credit.Text + "' and USE_pin='" + txt_pwd.Text + "' and USE_uname='" + HiddenField1.Value + "' and USE_Bank='" + ddl_bank.SelectedItem.Text + "'", con) cmd.ExecuteNonQuery() cmd = New SqlCommand("insert into TBL_Recharge values('" + Now.ToString + "','" + txt_mob.Text + "','" + dvendor.SelectedItem.Text + "','" + txt_credit.Text + "','" + ddl_bank.SelectedItem.Text + "'," + txtprice.Text + ",'" + HiddenField1.Value + "','" + msg + "')", con) cmd.ExecuteNonQuery() lbl_msg.Text = "Recharge sucessfully"

            MsgBox("Recharge Message Successfully send to mobile no : " + txt_mob.Text)
            txt_mob.Text = ""
            txt_credit.Text = ""
            txt_pwd.Text = ""
            txt_pwd.Text = ""
        Else
            dr.Close()
            lbl_msg.Text = "Unable to Recharge..Invalid Balance"
        End If

sql server function on a group of records

I have a group by query. Is there a way to access all records within a group for each every group in pure T-SQL 2005?

What I want to achieve if that, say I have a table Table(A, B) and a group by A query. Then I need to get a list of concatenations, each of which is a concatenation of the values of B within a group. Any thought?

Need to fetch Metadata from sql query using java

I have a sql query fro which I need the column name and data type and it's table name and schema name:

Thsi is the method I am using for using and testing it for SQLSERVER:

public static void getMetadataForConn(Connection conn) throws SQLException
    {
        ResultSet rs = null;
         try
         {

             Statement stmt = conn.createStatement();
             rs = stmt.executeQuery("SELECT AB_DEMO_SRC.dbo.employee.dept_id dept_id, AB_DEMO_SRC.dbo.employee.email_add email_add, AB_DEMO_SRC.dbo.employee.emp_address emp_address, AB_DEMO_SRC.dbo.employee.emp_id emp_id, AB_DEMO_SRC.dbo.employee.emp_name emp_name FROM AB_DEMO_SRC.dbo.employee ");
             ResultSetMetaData md = rs.getMetaData();
             int columnCount = md.getColumnCount();
             for (int i = 1; i <= columnCount; i++)
                 System.out.print(md.getColumnName(i) + "(" + md.getColumnType(i) + ") "+md.getSchemaName(i)+"."+md.getTableName(i));
             System.out.println();
             while (rs.next())
             {
                 for (int i = 1; i <= columnCount; i++)
                 {
                     Object o = rs.getObject(i);
                     System.out.print(null == o ? "" : o.toString() + " ");
                 }
                 System.out.println();
             }
            }
            finally
            {
                if (null != rs)
                {
                    rs.close();
                }
            }
    }

I get all the other metadata details like data type, precision and scale..Strangely, I am getting tablename and schema name as "" that is blank..IS there any other way to fetch the metadata of the columns present in a Query?

Transferring 3 SQL databases from both SQL Express 2005 and Standard to a new physical server

I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.

I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.

Here is the breakdown of the servers:

1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit 2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard 3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)

**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?

Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server?

I have researched this online and I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit. I cannot find anything online that I feel confident in trying. Please help me, as I have this migration deadline of this weekend. Any help is appreciated. Thanks

SQL data export issue : The code page on input column "column3" (50) is 65001 and is required to be 1252

I have an issue while exporting data to UTF-8 format from SQL server 2005.

Below is my query and error. Kindly help me with this.

Query : select distinct a.column1,a.column2,a.column3,a.column4,a.column3 from table1 a, table2 b where a.column1 = b.column1;

Error: Error 0xc00470d4: Data Flow Task: The code page on input column "column3" (50) is 65001 and is required to be 1252. (SQL Server Import and Export Wizard) · Error 0xc00470d4: Data Flow Task: The code page on input column "column4" (53) is 65001 and is required to be 1252. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Destination - test1_txt" (37)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) · Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard)

Column3 and column4 are of type char. Remaining are of type varchar.

Kindly help me with this. I also tried converting them but getting the same error. "convert(varchar(50),a.column3) as column3. error : · Error 0xc00470d4: Data Flow Task: The code page on input column "column3" (64) is 65001 and is required to be 1252".

Thanks in advance.

SQL - Select max week from a group

I need to be able to get a result set which shows the last teacher for a course, for which I have the following SQL query:

SELECT      
    a.acad_period, MAX(a.start_week) as start_week, 
    a.staff_code, b.aos_code, b.aos_period
FROM 
    qlsdat.dbo.sttstaff a
INNER JOIN 
    qlsdat..sttrgaos b ON a.acad_period = b.acad_period 
                       AND a.register_id = b.register_id
                       AND a.register_group = b.register_group
WHERE 
    a.acad_period = '14/15'
GROUP BY 
    a.acad_period, a.staff_code, b.aos_code, b.aos_period

However, the issue is that it returns to me the maximum start week for a teacher on that course, whereas I want the maximum start week for a course, and the teacher that happens to be teaching for that start week.

Here is a sample result set returned from the above query:

14/15   37  HKARUNATHIL A2ES        001A 
14/15   37  CSHUKLA     A2ES        001B 
14/15   37  PSEDOV      A2ES        002A 
14/15   37  BBANFIELD   A2ES        002B 
14/15   14  VKRISHNASWA A2EX        BL1 X
14/15   14  VKRISHNASWA A2EX        BL2 X
14/15   6   BODAMEKENTO ACA2        BL1 A
14/15   41  SKLER       ACA2        BL1 A
14/15   44  BODAMEKENTO ACAS        BL1 F
14/15   37  MMILLER     ARA2        BL1 C
14/15   45  MMILLER     ARAS        BL1 E
14/15   44  SHOULTON    ARAS        BL1 E

Here is an example of the problem within the result set:

14/15   10  HMALIK      MMGX        GB2F3
14/15   44  JMULLANEY   MMGX        GB2F3

In the above example I only want:

14/15   44  JMULLANEY   MMGX        GB2F3

The query produced is going to be used as a subquery in another query.

Visual Studio 2013 with SQL Server

Bit of a novice but wish to create websites from Visual Studio 2013 with SQL Server backend. I have a copy of SQL Server 2005 but am experiencing connection issues then trawling forums for solutions is as usual, confusing. Is there a 'best' version of SQL Server, or Express for achieving my goal and if so has anybody the download link? Thanks in advance.

SQL UPDATE and FROM clause. How to know which table is being updated?

I am studying the various effects of the SQL UPDATE command.

In reading the MSDN official definition, they mention an example of an UPDATE of the same table in both the UPDATE and FROM clause

ie:

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
    ON sp.SalesPersonID = so.SalesPersonID
    AND so.OrderDate = (SELECT MAX(OrderDate)
                        FROM Sales.SalesOrderHeader 
                        WHERE SalesPersonID = 
                              sp.SalesPersonID);
GO

It's obvious the example is updating the SalesYTD from the Sales.SalesPerson table because the same table is linked in the FROM clause but with an ALIAS, sp.

That's easy, but what if it did not have an alias?

ie:

UPDATE Sales.SalesPerson
    SET SalesYTD = SalesYTD + SubTotal
    FROM Sales.SalesPerson
...

How would the UPDATE know which instance of the table (row) it's updating? My question is specifically to know which "instance" of the table it's going to update, the one mentioned in the UPDATE statement or the one mentioned in the FROM clause?

Any clarification on the above example from MSDN would be appreciated.

Thanks

UPDATE Could the original example have been written thus ...?

UPDATE sp
SET sp.SalesYTD = sp.SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
...

timeout expired I'm using sql server 2005

need help.

i got this error whenever i restore my database (.bak) from file.

http://ift.tt/1Co2Zq9

every time Progress Executing at (60%) error show up.

any help is appreciated.

thanks

restrict who can connect to database engine via ssms

I have a very simple DB set up in SQL Server 2005. I only use my databases via SSMS running queries on them there. I do not use these DB's with any interfaces or applications of any sort. What I need to do is lockout all access to those databases with the exception of user accounts I allow. To keep it as simple as possible, is there a place I can just create users and grant them access thereby denying access to anyone else who tries to connect to the database engine?

Combine and modify XML in TSQL

Using SQL Server 2005, is it possible to combine XML and add an attribute at same time?

Unfortunately, due to project restrictions, I need a SQL Server 2005 solution.

Consider the following, where I need to combine XML from multiple rows within a new <root> element...

; WITH [TestTable] AS (
    SELECT 1 AS [PkId], CAST('<data><id>11</id></data>' AS XML) AS [Data]
    UNION ALL
    SELECT 2, CAST('<data><id>22</id></data>' AS XML)
    UNION ALL
    SELECT 3, CAST('<data><id>33</id></data>' AS XML)
)
SELECT (
    SELECT XMLDATA as [*]
    FROM (
        SELECT [Data] AS [*]
        FROM [TestTable]
        FOR XML PATH(''), TYPE
    ) AS DATA(XMLDATA)
    FOR XML PATH('root')
)

This produces the desired output of...

<root>
  <data><id>11</id></data>
  <data><id>22</id></data>
  <data><id>33</id></data>
</root>

But what I need to do, if possible, is add an attribute to the existing data element in each of the rows with the PkId value. The desired output would then look like this...

<root>
  <data pkid="1"><id>11</id></data>
  <data pkid="2"><id>22</id></data>
  <data pkid="3"><id>33</id></data>
</root>

My gut feeling is that this is going to be impossible without the use of a cursor, but if anybody knows a way of doing it I'd love to hear it.

This page has no error but runtime it display invalid credit card . why this type error appears?

Design:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://ift.tt/kkyg93">

<html xmlns="http://ift.tt/lH0Osb" >
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body background="pic/bg1.jpg">
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="txt_mob" runat="server" Style="z-index: 100; left: 174px; position: absolute;
            top: 149px" MaxLength="10"></asp:TextBox>
        <asp:TextBox ID="txt_credit" runat="server" Style="z-index: 101; left: 165px; position: absolute;
            top: 320px" MaxLength="20"></asp:TextBox>
        <asp:TextBox ID="txt_pwd" runat="server" Style="z-index: 102; left: 170px; position: absolute;
            top: 390px" MaxLength="16"></asp:TextBox>

        <asp:Label ID="Label1" runat="server" Style="z-index: 103; left: 174px; position: absolute;
            top: 125px" Text="Mobile Number"></asp:Label>
        <asp:Label ID="lbl_msg" runat="server" Style="z-index: 104; left: 191px; position: absolute;
            top: 429px" Font-Bold="True"></asp:Label>
        <asp:Label ID="Label12" runat="server" Style="z-index: 105; left: 160px; position: absolute;
            top: 210px; height: 24px;" Text="Bank"></asp:Label>
        <asp:Label ID="Label13" runat="server" Style="z-index: 105; left: 160px; position: absolute;
            top: 210px; height: 24px;" Text="Bank"></asp:Label>
        <asp:Label ID="Label2" runat="server" Style="z-index: 105; left: 165px; position: absolute;
            top: 185px; height: 24px; right: 1034px;" Text="Via"></asp:Label>
        <asp:Label ID="Label3" runat="server" Style="z-index: 106; left: 170px; position: absolute;
            top: 270px; height: 44px;" Text="Card No"></asp:Label>

        <asp:DropDownList ID="ddl_bank" runat="server" Style="z-index: 108; left: 165px;
            position: absolute; top: 245px" Width="149px">
        </asp:DropDownList>
        <asp:Button ID="Button1" runat="server" Height="50px" Style="z-index: 109; left: 505px;
            position: absolute; top: 450px" Text="Recharge" Width="189px" />

        <asp:Image ID="Image1" runat="server" ImageUrl="~/pic/r2.jpg" Style="z-index: 110;
            left: 8px; position: absolute; top: 113px" Height="67px" Width="146px" />
        <asp:Label ID="Label7" runat="server" Font-Bold="True" Style="z-index: 111; left: 370px;
            position: absolute; top: 215px" Text="Rechare Card"></asp:Label>
        <asp:TextBox ID="txtprice" runat="server" Style="z-index: 112; left: 580px; position: absolute;
            top: 243px" Width="109px"></asp:TextBox>
        <asp:TextBox ID="txttalk" runat="server" Style="z-index: 113; left: 580px; position: absolute;
            top: 289px" Width="109px"></asp:TextBox>
        <asp:TextBox ID="txtval" runat="server" Style="z-index: 114; left: 580px; position: absolute;
            top: 330px" Width="109px"></asp:TextBox>
        <asp:Label ID="Label8" runat="server" Font-Bold="True" Style="z-index: 115; left: 416px;
            position: absolute; top: 151px" Text="Vendor"></asp:Label>
        <asp:Label ID="Label9" runat="server" Font-Bold="True" Style="z-index: 116; left: 497px;
            position: absolute; top: 330px" Text="Validity"></asp:Label>
        <asp:Label ID="Label10" runat="server" Font-Bold="True" Style="z-index: 117; left: 496px;
            position: absolute; top: 287px" Text="Talktime"></asp:Label>
        <asp:ListBox ID="lst_rc" runat="server" AutoPostBack="True" Height="114px" Style="z-index: 118;
            left: 370px; position: absolute; top: 260px" Width="104px"></asp:ListBox>
        <asp:Label ID="Label11" runat="server" Font-Bold="True" Height="18px" Style="z-index: 119;
            left: 498px; position: absolute; top: 252px" Text="Price"></asp:Label>
        <asp:DropDownList ID="dvendor" runat="server" AutoPostBack="True" Style="z-index: 121;
            left: 473px; position: absolute; top: 146px; height: 22px;" Width="97px" >
        </asp:DropDownList>
        <asp:Button ID="Button2" runat="server" Height="28px" Style="z-index: 123; left: 338px;
            position: absolute; top: 148px" Text="Fetch" Width="48px" />

    </div>
        <asp:Label ID="Label4" runat="server" Style="z-index: 107; left: 170px; position: absolute;
            top: 350px; height: 24px; width: 61px;" Text="Password"></asp:Label>
        <p>
             </p>
    <p>
         </p>
    <p>
         </p>
    <p>
          </p>
    <p>

        <asp:DropDownList ID="ddl_bank0" runat="server" Style="z-index: 108; left: 250px;
            position: absolute; top: 185px" Width="149px">
        </asp:DropDownList>
         </p>
    </form>
</body>
</html>
VB Coding:
Imports System.Web.UI
Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration

Partial Class recharge
    Inherits System.Web.UI.Page

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mobile.mdf;Integrated Security=True;User Instance=True"
        con.Open()

        If IsPostBack = False Then
            ddl_bank.Items.Add("SBI")
            ddl_bank.Items.Add("ICICI")
            ddl_bank.Items.Add("AXIS")

            'fetch vendor name from vendor table

            cmd = New SqlCommand("select distinct(ADM_Servicename) from TBL_Service", con)
            dr = cmd.ExecuteReader
            dvendor.Items.Add("none")
            dvendor.Items.Clear()
            Do While dr.Read
                dvendor.Items.Add(dr(0).ToString)
            Loop
            dr.Close()
        End If

        If IsPostBack = False Then
            ddl_bank0.Items.Add("Netbank")
            ddl_bank0.Items.Add("Card")

        End If

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        If txt_mob.Text = "" Or txtprice.Text = "" Or txt_credit.Text = "" Or txt_pwd.Text = "" Then
            lbl_msg.Text = "Empty Field"
            Exit Sub
        End If

        Dim msg As String = ""

        Dim r As New Random

        Dim ref_no As String = ""
        Dim USE_uname As String = "USE_user"

        ref_no = (r.Next Mod 10000).ToString

        'Try

        cmd = New SqlCommand("select * from TBL_Banking where USE_creditcard='" + txt_credit.Text + "' and USE_pin='" + txt_pwd.Text + "' and USE_uname='" + Session("USE_user") + "' and USE_Bank='" + ddl_bank.SelectedItem.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            If Val(dr("USE_Amount").ToString) >= Val(txtprice.Text) Then
                dr.Close()
                msg = "Ref No : " + ref_no + ". Your mobile no : " + txt_mob.Text + " Talk time : " + txttalk.Text + " Validity : " + txtval.Text
                cmd = New SqlCommand("update TBL_Banking set USE_Amount=USE_Amount-" + txtprice.Text + " where USE_creditcard='" + txt_credit.Text + "' and USE_pin='" + txt_pwd.Text + "' and USE_uname='" + Session("USE_user") + "' and USE_Bank='" + ddl_bank.SelectedItem.Text + "'", con)
                cmd.ExecuteNonQuery()
                cmd = New SqlCommand("insert into TBL_Recharge values('" + Now.ToString + "','" + txt_mob.Text + "','" + dvendor.SelectedItem.Text + "','" + txt_credit.Text + "','" + ddl_bank.SelectedItem.Text + "'," + txtprice.Text + ",'" + Session("USE_user") + "','" + msg + "')", con)
                cmd.ExecuteNonQuery()
                lbl_msg.Text = "Recharge sucessfully"

                txt_mob.Text = ""
                txt_credit.Text = ""
                txt_pwd.Text = ""
                txt_pwd.Text = ""
            Else
                dr.Close()
                lbl_msg.Text = "Unable to Recharge..Invalid Balance"
            End If
        Else
            dr.Close()

            lbl_msg.Text = "invalid"
        End If
        'Catch ex As Exception
        '    Label8.Text = ex.Message
        'End Try

    End Sub

    Protected Sub dvendor_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dvendor.SelectedIndexChanged


    End Sub
    Private Sub vi()
        cmd = New SqlCommand("select distinct(ADM_rechargecard) from TBL_Offers where ADM_vendername='" + dvendor.SelectedItem.Text + "'", con)
        dr = cmd.ExecuteReader
        lst_rc.Items.Clear()
        Do While dr.Read
            lst_rc.Items.Add(dr(0).ToString)
        Loop
        dr.Close()
    End Sub

    Protected Sub lst_rc_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lst_rc.SelectedIndexChanged
        cmd = New SqlCommand("select * from TBL_Offers where ADM_vendername='" + dvendor.SelectedItem.Text + "' and ADM_rechargecard='" + lst_rc.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            txtprice.Text = dr(4).ToString
            txttalk.Text = dr(5).ToString
            txtval.Text = dr(6).ToString
        End If
        dr.Close()

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            If txt_mob.Text.Length <> 10 Then
                lbl_msg.Text = "Invalid Mobile number"
            Else
                If Mid(txt_mob.Text, 1, 3) = "999" Or Mid(txt_mob.Text, 1, 3) = "944" Then
                    dvendor.SelectedItem.Text = "BSNL"
                ElseIf Mid(txt_mob.Text, 1, 3) = "989" Then
                    dvendor.SelectedItem.Text = "AIRCEL"

                ElseIf Mid(txt_mob.Text, 1, 3) = "898" Then
                    dvendor.SelectedItem.Text = "Vodafone"
                Else
                    dvendor.SelectedItem.Text = "Airtel"
                End If
            End If
            Call vi()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub




End Class

Database Connection: TBL_Banking: USE_Name,USE_Bank,USE_acno,USE_address,USE_uname,USE_pin,USE_Amount,USE_pin TBL_Offers: ADM_refno,ADM_vendername,ADM_Servicelocn,ADM_rechargecard,ADM_Price,ADM_talktime,ADM_Validity,ADM_Category,ADM_Desc TBL_Recharge: USE_Tdate,USE_mobnum,USE_Vender,USE_creditcard,USE_Bank,USE_Amount,USE_Rcuser, USE_Mdesc

Combine multiple XML using TSQL, appending data at the same time

In SQL Server 2005, is it possible (without masses of complex code) to combine XML held in a column of multiple rows, and also added extra data at the same time?

Unfortuantely, due to project restrictions, I need SQL Server 2005 compliant answers

For instance, say I have the following schema / data...

PKID  Saved             Data
1     2015-01-01 12:34  '<data><id>11</id><id>22</id></data>'
2     2015-02-03 14:56  '<data><id>22</id><id>33</id></data>'

I'd like to produce the following XML, so each of the Data columns are combined, and the pkid attribute is added to the <data> node...

<root>
  <data pkid='1'><id>11</id><id>22</id></data>
  <data pkid='2'><id>22</id><id>33</id></data>
<root>

At first I simply needed to combine the XML into a single set of <id> nodes, so I created the following...

SELECT D.value('.','int')
FROM [MyTable]
    CROSS APPLY [Data].nodes('/data/id') AS D(D)
FOR XML PATH('id'), ROOT('data')

This produced the required XML of <data><id>1</id><id>2</id><id>1</id><id>3</id></data>.

But then I realised that I needed to keep the individual <data> nodes separate and with specific unique ids... so the above does not meet my needs.

Unfortunately I'm unable to come up with anything even close to a working answer... so am unable to show you "what I have done so far" (beyond the above), as I simply don't have anything yet.

Is Doctrine2 compatible with SQL Server 2005

I have a lot of troubles with Doctrine2 and SQLServer2005,

The first issue was the DATETIME2 datatype, but it can be easily fixed, then, i had an unsolvable issue with UNIQUE index Here

Now, I try to use @GeneratedValue(strategy="IDENTITY") on my ID column, and the generated SQL syntax is incorrect.

So my question is simple, Is it a good deal to (try to) use Doctrine2 with SQL Server 2005?

How to send data from a Sql Server to another Sql Server (Not Linked) Using C# ASP.NET HttpClient

I need to send data from one Database (Server A) to another Database (Server B), on both server I have an ASP.NET application. I've searched about using a XML file with HttpClient Post. I don't want to use Web Service because i can't modify the Server B's application (I'll use an aspx file that already receive a xml from a Android App).

Any advice? If you can share some links about how can i do it i'll appreciate it :)

Thank you for your time, peace.

SQL null image to VARBINARY(MAX) Results in Empty

Here is the scenario. We have a column of type IMAGE in a table that holds compressed data. This column can be null. In a different version, we moved this data to a separate table, still an image column.

We have a stored procedure that is checking where the data is, in the original table or the new table. To do that, it selects the value into a VARBINARY(MAX) variable, checks if it is null, if so grabs it from the new table.

Something strange is going on with the checks. For a few rows in the database, the column displays as NULL, but when cast to VARBINARY(MAX) it ends up being an empty value of 0x. This is only for 4 rows out of 426, all others evaluate to NULL fine. If you check for null in the table itself, like WHERE <column> IS NULL it evalulates to true, but if you do WHERE CAST(<column> as VARBINARY(MAX)) IS NULL it evaluates to false.

I can change around my stored procedure so that it handles correctly, but I'm a little baffled as to what is going on here. Any ideas?

This is on SQL Server 2005.

partition over weeks sql 2005

I have in table two columns with dates. One date represent expected date and other one date when job is finished. I want to make query to get how many jobs were finished including specific week in current year and how many jobs should be finished to that week.

So if I transform my dates in weeks in those two colums as:

wkx:7,7,7,8,8,9,10,10,10,10,11,11
wkf:7,8,10,10,12,13,14,15,16,17,18,19 

response should be smth like:

wk:7,8,9,10,11,12,13,14,15,16,17,18,19
numx:3,5,6,10,12,12,12,12,12,12,12,12,12
numf:1,2,2,4,4,5,6,7,8,9,10,11,12

I am using MS SQL 2005, and have no idea even how to get distinct weeks in one column as is in example. regards

Doctrine2, bad syntax for UNIQ Index, SQL Server 2005

I'm trying to setup Doctrine2 inside my project, it works fine, but I have a issue while generating schema with columns option: 'unique=true'

my Entity :

<?php
namespace Entities\Test;
/**
* @Entity(repositoryClass="")
* @Table(name="test")
*/
Class Test {
    /**
     * @Id
     * @Column(type="integer",unique=true, nullable=false)
     */
     private $id;

    /**
     * @Column(type="string", length=32, unique=true, nullable=false)
     */
     private $test;
}

Then, I just unse the console command :

orm:schema-tool:update --dump-sql

It return some nice SQL :

CREATE TABLE test (id INT NOT NULL, test NVARCHAR(32) NOT NULL, PRIMARY KEY (id));
CREATE UNIQUE INDEX UNIQ_D87F7E0CBF396750 ON test (id) WHERE id IS NOT NULL;
CREATE UNIQUE INDEX UNIQ_D87F7E0CD87F7E0C ON test (test) WHERE test IS NOT NULL;

But when I execute it (--force), I get an error :

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_D87F7E0CBF3
96750 ON test (id) WHERE id IS NOT NULL':

SQLSTATE [42000, 156]: [Microsoft][SQL Server Native Client 11.0][SQL Serve
r]Syntaxe incorrecte vers le mot cl▒ 'WHERE'.

It seems that this syntax is for SQLServer > 2008, but I'm working with SQLServer2005, so I added the good Platform in my options array :

    $conn = array(
        'driver' => 'sqlsrv',
        'user' => 'gaia_dev',
        'password' => 'RYUuflm3Ny',
        'host' => '10.2.0.19',
        'dbname' => 'Contentieux',
        'platform' => new Doctrine\DBAL\Platforms\SQLServer2005Platform(),
    );

    $this->em = EntityManager::create($conn, $config);

But it do not change anything to the SQL code, I still have this 'incorrect syntax near WHERE'

I work with the last stable version of doctrine, downloaded with composer and I tried it in a new clean project, it still the same, I searched a lot for a similar issue, but didn't find anything, I think I missed something.

Any help would be welcome !

Thank you for reading me.

View executed sql from dynamic sql

I've inherited a complicated application that uses a lot of dynamic SQL. Many of the stored procedures construct and execute significantly different SQL statements depending on input parameters. Is there a way that I can see the executed SQL, without using SQL profiler - ideally from within SQL Server Manager Studio?

How to make a database auto commit all transactions

I am using SQL Server 2005 with several databases.

Recently I backed up our main production database and re-attached it as a development or test database for development purposes.

For some reason this 2nd copy of the database won't commit my SELECT statements. Every statement I run a query on the database it "locks it" and won't release back to other users, unless I force a commit statement at the end of my script.

Is there a way to SET IMPLICIT_TRANSACTIONS to OFF so that I don't have to COMMIT all the time and lock the database from other users? And again, I don't want to use that statement in the query. I want to set it in the properties of the database in some way.

I read this, but it's not much help

http://ift.tt/1O4K7Bf

Thanks

How do I parse this piece of XML?

I have the following piece of XML in my SQL:

DECLARE @xml XML
SET @xml = '<?xml version="1.0" encoding="UTF-8"?>
<BillingAddresses>
  <BillingAddress Winner="W1">
    <Losers>
      <Loser Id="L1" />
      <Loser Id="L2" />
      <Loser Id="L3" />
    </Losers>
  </BillingAddress>
  <BillingAddress Winner="W10">
    <Losers>
      <Loser Id="L65" />
    </Losers>
  </BillingAddress>
</BillingAddresses>'

I'd like to fetch data in the following manner.

Winner | LoserID 
W1       L1
W1       L2
W1       L3
W10      L65

I can get to the Winner attribute of BillingAddress:

SELECT Col.value('(@Winner)[1]', 'varchar(30)')
FROM @xml.nodes('/BillingAddresses/BillingAddress') Rev(Col) 

but I am at a loss how to get to the Id attribute of the Loser nodes

Updating a binary field taking a file from filesystem without using dynamic SQL

The Scenario

I have a table (let's call it myTable) in Sql Server 2005 with some columns, including a varbinary(max) field:

• REPORT_ID int (primary key)


• REPORT_FILE varbinary(max)


• ...

I often need to update the files contained inside the varbinary(max) column via TSQL scripts reading a file from the filesystem. These scripts are executed in Sql Server Management Studio.

I wrote the following script to update the binary value using OPENROWSET:

begin try
    begin tran
    update myOtherTable set field1 = 'value1'
    --other instructions
    --...

    UPDATE myTable 
    SET [REPORT_FILE] = (SELECT * FROM OPENROWSET(BULK 'c:\filename.ext', SINGLE_BLOB) AS T)
    WHERE REPORT_ID = ...
end try

begin catch
    if @@TRANCOUNT > 0 begin
        rollback tran
        --other instructions for error handling
        --...
    end
end catch

if @@TRANCOUNT > 0  begin
    commit tran
end 

The Problem

I want to handle the scenario where the file is missing or the filename is wrong; the desired behavior is:

• rollback if an error occurs


• commit if everything worked

If the input file is missing the above script generates an error, but the CATCH block fails to capture the exception, so the transaction and the update to myOtherTable remain pending because the COMMIT instruction isn't executed. As a result I have to rollback the transaction manually.

My solution

The only way I have found to capture the error in the CATCH block is using dynamic TSQL, so my actual script is:

declare @dSQL nvarchar(max)

begin try
    begin tran
        update myOtherTable set field1 = 'value1'
        --other instructions 
        --...
        set @dSQL = '
            UPDATE myTable 
            SET [REPORT_FILE] = (SELECT * FROM OPENROWSET(BULK ''c:\filename.ext'', SINGLE_BLOB) AS T)
            WHERE REPORT_ID = ...
             '
    exec sp_executesql @dSQL
end try

begin catch
    if @@TRANCOUNT > 0 begin
        rollback tran
        --other instructions for error handling
        --...
    end
end catch

if @@TRANCOUNT > 0  begin
    commit tran
end

The Question

Is it possible to achieve the desired behavior without using dynamic TSQL?

Using several subqueries in sql statement

I am trying to use several subqueries but I am getting an error. It works if I remove the second subquery.

Error: Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

USE SUPERSTARIS
SELECT lines.Item,
       Inventory.ItemAlternative,
       Inventory.OnHandPhys,
       Inventory.Allocated,
       Inventory.CostPrice,
       replace(vInventoryOptional2Values.Value, '/', '') AS Brand,
       Inventory.Optional3 AS StockStatus,

  (SELECT SUM(l.Invoiced) * -1
   FROM lines l
   WHERE l.Date >= '2014-01-01'
     AND l.Date <= '2014-01-31'
     AND l.Item = lines.Item) AS QtySoldInPeriod,

  (SELECT SUM(lines.AmountBase-lines.CostAmount)*-1
   FROM lines l
   WHERE l.Date >= '2014-01-01'
     AND l.Date <= '2014-01-31'
     AND l.Item = lines.Item) AS QtySoldInPeriod2
FROM lines
INNER JOIN inventory ON lines.item = Inventory.item
INNER JOIN vInventoryOptional2Values ON Inventory.Optional2 = vInventoryOptional2Values.RecordID
WHERE lines.Invoiced < 0
  AND Inventory.Status = 0
  AND Inventory.Optional3 <> 3
GROUP BY Lines.Item,
         Inventory.Optional3,
         Inventory.ItemAlternative,
         Inventory.OnHandPhys,
         Inventory.CostPrice,
         vInventoryOptional2Values.Value,
         Inventory.Allocated

Showing data as a column name

I have three tables. I called them as Table A,Table B,Table C. And I have desired view which I want to get.

Table A
Aid         RegNum  BID         Value
2CE7D0A7    2000000 D5981DFC    OFFCRO
9D3C13AA    2000000 C58566C5    YCH - from 
9DDB90C4    2000000 812E9E75    Y

Table B is connected to Table A by Table B's foreign key in Table A

Table B

BID             Label                               ColumnName              Order
D5981DFC        Offered/Change Role                 StatusChangeCode        0
C58566C5        Offered/Role Change Comments        StatusChangeComments    1
812E9E75        Assessed                            StatusChangeAssessed    2

Table C has foreign key in Table A as well. Reg Num. Reg num is primary key in Table C

Table C 

Name        Surname     RegNum
Etibar      Hasanov     2000000

As you see there are column's names which are datas in Table B

DesiredView
Name        Surname     RegNum      StatusChangeCode    StatusChangeComments    StatusChangeAssessed
Etibar      Hasanov     2000000     OFFCRO              YCH - from              Y

How to truncate and add new rows to the table with a select query never getting empty results

I have a requirement where a table holds the state of certain things. This table is truncated and new status data in inserted in it every second. The problem is that if a select query is executed between a delete and the following insert, the user will get empty table in return. SQL Transactions would not help here i think but not sure. Also, if the select query is executed between the delete and insert query, it shouldn't return error because its blocked by a database lock. it should just wait till the delete + insert operation is finished.

What would be the best way to implement such a system? How should i form the "delete + insert" query and the "select" query?

Thank you in advance.

Pull ONLY Most Recent CallSwitch Info

2 Tables one with the generic info, the other showing our phone switch information. I want to pull the most recent information based off our call switch. This is sample data for my table structure, how can I ONLY pull the most recent info, even though the callswitch table could possibly contain multiple records for the same day?

Table Structure: Create Table #PhoneSwitch ( PSID int, UserID int, #Dialed varchar(100), LengthofCall int, CallDateTime datetime, CallOutcome varchar(100) ) Create Table #ResourceTable ( RID int, UserID int, AddressMain varchar(100), PhoneNameFirst varchar(100), PhoneNameLast varchar(100), PhoneNameCity varchar(100), PhoneNameState varchar(100), PhoneNameZip varchar(100) )

Insert Into #PhoneSwitch Values ('1', '311', '5555555555', '0',     '03/16/2015', 'No Connect'), ('2', '311', '5555555555', '0', '03/16/2015', 'No Connect'),
('3', '311', '5555555555', '0', '03/16/2015', 'No Connect'),('4', '511',   '5555555555', '0', '03/15/2015', 'No Connect'), ('5', '511', '5555555555', '0', '03/15/2015', 'No Connect')

Insert Into #ResourceTable Values ('1','311','123 Nowhere Street', 'Z',   'F', 'Montreal', 'CA', '123'),
('2','311','123 Nowhere Street', 'Z', 'F', 'Montreal','CA',  '123'),     ('3','311','123 Nowhere Street', 'Z', 'F', 'Montreal', 'CA', '123'),
('4','511','623 Nowhere Street', 'A', 'X', 'Montreal', 'CA', '192'),    ('5','511','623 Nowhere Street', 'A', 'X', 'Montreal', 'CA', '192')

And this is the query I tried, but it is not returning ONLY the most recent information from the switch

Select case when [UserID] LIKE '311' Then 'Sam Smith' when [UserID] Like    '511' Then 'Ricky Zefry' end As [User Name], MAX(PS.CallDateTime) As [Call   Date], RT.AddressMain, RT.PhoneNameFirst, RT.PhoneNameLast, RT.PhoneNameCity,   RT.PhoneNameState, RT.PhoneNameZip
FROM #ResourceTable RS
Inner Join #PhoneSwitch ps
On RS.UserID = PS.UserID

Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified

I am not able to connect to oracle from SSIS project. I have installed SQL Server 2005 on Windows server 2008 64 bit. However, I can do it from C:\PROGRA~2\Microsoft SQL Server\90\DTS\Binn\dtswizard.exe I am getting below error on connection:

Test connection failed because of an error in initializing provider. ORA-06413: Connection not open. Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified

getting the range of a number column(min/max) if there are missing numbers in between

How would I query the range of a number column if the number ends somewhere then picks up again at a higher number?

If I had a column like:

Number
-------
 1
 2
 3
 4
 5
 11
 12
 13

How can I return a result like

Min | Max
----------
 1  |  5
 11 |  13

Multiple joins on same table?

I have two table:

tbl_EmploymentSegmentEM:

╔══════╦═════════════╦════════════╦═══════════════╦═══════════════════════════════╦════════════╦═════════════╦══════════════════════════╦════════════════╗
║ SrNo ║ CIBILTuefID ║ Prospectno ║ ApplicantType ║         ApplicantName         ║ SegmentTag ║ AccountType ║ DateReportedandCertified ║ OccupationCode ║
╠══════╬═════════════╬════════════╬═══════════════╬═══════════════════════════════╬════════════╬═════════════╬══════════════════════════╬════════════════╣
║    1 ║           1 ║     718580 ║ APPLICANT     ║ RAJKUMAR GIRISHCHANDRA PANDEY ║ E01        ║          10 ║                 31122014 ║             02 ║
║    2 ║           4 ║     718638 ║ APPLICANT     ║ Anil Kumar Aggarwal           ║ E01        ║          10 ║                 31122014 ║             01 ║
╚══════╩═════════════╩════════════╩═══════════════╩═══════════════════════════════╩════════════╩═════════════╩══════════════════════════╩════════════════╝

tbl_CIBILFieldDescription:

╔════════╦══════════╦══════════════════════════════╦═══════╦═════════════════════════════╗
║ Header ║ FieldTag ║          FieldName           ║ Value ║      ValueDescription       ║
╠════════╬══════════╬══════════════════════════════╬═══════╬═════════════════════════════╣
║ PT     ║       03 ║ TelephoneType                ║ 03    ║ Office Phone                ║
║ EM     ║       03 ║ OccupationCode               ║ 01    ║ Salaried                    ║
║ EM     ║       03 ║ OccupationCode               ║ 02    ║ Self Employed Professional. ║
║ EM     ║       03 ║ OccupationCode               ║ 03    ║ Self Employed               ║
║ EM     ║       03 ║ OccupationCode               ║ 04    ║ Others                      ║
║ EM     ║       05 ║ NetGrossIncomeIndicator      ║ G     ║ Gross Income                ║
║ EM     ║       05 ║ NetGrossIncomeIndicator      ║ N     ║ Net Income                  ║
║ EM     ║       06 ║ MonthlyAnnualIncomeIndicator ║ M     ║ Net Monthly                 ║
║ EM     ║       06 ║ MonthlyAnnualIncomeIndicator ║ A     ║ Net Annual                  ║
║ SC     ║       01 ║ ScoreCardName                ║ 01    ║ CIBILTUSCR                  ║
╚════════╩══════════╩══════════════════════════════╩═══════╩═════════════════════════════╝

I am trying to get Account Type and Occupation code description from tbl_CIBILFieldDescription for the corresponding values.

I tried this :

SELECT DISTINCT CIBILTuefID, 
                Prospectno, 
                ApplicantType, 
                ApplicantName, 
                SegmentTag, 
                AccountType, 
                DateReportedandCertified, 
                OccupationCode, 
                mst.ValueDescription  AS OccupationCodeDesc, 
                Income, 
                NetGrossIncomeIndicator, 
                mst.ValueDescription AS NetGrossIncomeIndicatorDesc, 
                MonthlyAnnualIncomeIndicator, 
                DateofEntryforErrorCode, 
                ErrorCode, 
                DateofEntryforCIBILRemarksCode, 
                CIBILRemarksCode, 
                DateofEntryforErrorDisputeRemarksCode, 
                ErrorDisputeRemarksCode1, 
                ErrorDisputeRemarksCode2, 
                MkrId, 
                MkdDt 
FROM   tbl_EmploymentSegmentEM EM 
       INNER JOIN tbl_CIBILFieldDescription mst 
               ON 1 = 1 

WHERE  mst.Header = 'EM' 
       AND mst.FieldName = 'OccupationCode' 
       AND mst.Value = EM.OccupationCode 

And it seems to work ok for OccupationCode but what if I want both OccupationCode and AccountType from the same query? What is the best way to do this?

Calculate extra column in group by

In sure this should be simple, but I'm having a brain fart over it.

In SQLServer 2005, how do you add an "extra count" to a GROUP BY query?

Consider the following...

;WITH DATA AS (
    SELECT 1 AS ID, 1 AS TYPEID, 0 AS ACTIONNEEDED 
    UNION
    SELECT 2 AS ID, 1 AS TYPEID, 0 AS ACTIONNEEDED 
    UNION
    SELECT 3 AS ID, 2 AS TYPEID, 0 AS ACTIONNEEDED 
    UNION
    SELECT 4 AS ID, 2 AS TYPEID, 1 AS ACTIONNEEDED 
    UNION
    SELECT 5 AS ID, 2 AS TYPEID, 0 AS ACTIONNEEDED 
)
SELECT TYPEID, COUNT(*) AS TOTAL
FROM DATA
GROUP BY TYPEID

The result is...

TYPEID  TOTAL
1       2
2       3

But what I need is an extra column that totals the number of rows where ACTIONNEEDED=1...

TYPEID  TOTAL ACTIONNEEDED
1       2     0
2       3     1

Note: unfortunately due to project constraints, I'm restricted to SQLServer 2005 compatible answers

Distinct Clause not working in SQL 2005

I have so many records having duplicate taskid assigned to multiple person, but i want to show distinct records means only one taskid in output in SQL

below is my query not working give me solution

SELECT DISTINCT taskid, taskname, person, userid, dept, date, status, monitor, comments, monitor_comments, respondtime, assignedby, reassigncomment, priority,date_complete, followup_date, re_status
FROM         task
WHERE     (status IS NULL)

SSIS Package not running from code

I am attempting to run an SSIS package from a website using the following code:

Process process = new Process();
        try
        {
            process.StartInfo.UseShellExecute = false;

            process.StartInfo.RedirectStandardOutput = true;

            process.StartInfo.FileName = "dtexec";

            process.StartInfo.Arguments = "/F " + ConfigurationManager.AppSettings["SSISLocation"] + packageName + " /ConF \"" + configLocation + "\"";

            System.IO.File.AppendAllText(ConfigurationManager.AppSettings["SSISLocation"] + "Log.txt", "/F " + ConfigurationManager.AppSettings["SSISLocation"] + packageName + " /ConF \"" + configLocation + "\"\r\n");

            process.Start();
            System.IO.File.AppendAllText(ConfigurationManager.AppSettings["SSISLocation"] + "Log.txt", process.StandardOutput.ReadToEnd() + "\r\n");                                
            process.WaitForExit();

            return true;
        }

This fails and my log returns: Could not create DTS.Application because of error 0x800401F3

I have googled this but there is not much help so I ran the package via command line which works. So I figure it must be permissions so I run psexec to get me a command window under the network service user that the website is running under. it still works but still doesn't work through my website.

I'm not running 64 bit and the machine itself is 32 bit so it wouldn't work on command line even if I were.

The folders being accessed have full permissions to "Everybody"

I am running sql server 2005.

I am running windows server 2003

I have set delay validation all on components that I can without causing issues.

I have run process monitor for both methods and the call looks pretty much the same but here they are anyway:

Via Command line:

Parent PID: 4292, Command line: dtexec /F c:\SSIS\packagename.dtsx /ConF "c:\SSIS\1cee35d1-b181-4ee7-a1de-eb9fc9c5ebe7.dtsConfig", Current directory: C:\WINDOWS\system32, Environment: =C:=C:\WINDOWS\system32 =ExitCode=00000000 ALLUSERSPROFILE=C:\Documents and Settings\All Users ClusterLog=C:\WINDOWS\Cluster\cluster.log CommonProgramFiles=C:\Program Files\Common Files COMPUTERNAME=servername ComSpec=C:\WINDOWS\system32\cmd.exe FP_NO_HOST_CHECK=NO lib=C:\Program Files\SQLXML 4.0\bin\ NUMBER_OF_PROCESSORS=2 OS=Windows_NT Path=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Dell\SysMgt\oma\bin;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\WINDOWS\system32\WindowsPowerShell\v1.0;C:\Program Files\Microsoft\Web Platform Installer\ PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.VBS;.PSC1 PROCESSOR_ARCHITECTURE=x86 PROCESSOR_IDENTIFIER=x86 Family 6 Model 37 Stepping 1, GenuineIntel PROCESSOR_LEVEL=6 PROCESSOR_REVISION=2501 ProgramFiles=C:\Program Files PROMPT=$P$G PSModulePath=C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\ SystemDrive=C: SystemRoot=C:\WINDOWS TEMP=C:\DOCUME~1\NETWOR~1\LOCALS~1\Temp TMP=C:\DOCUME~1\NETWOR~1\LOCALS~1\Temp USERDOMAIN=NT AUTHORITY USERNAME=NETWORK SERVICE USERPROFILE=C:\Documents and Settings\NetworkService windir=C:\WINDOWS

Via Website:

Parent PID: 3692, Command line: "dtexec" /F c:\SSIS\RNReport.dtsx /ConF "c:\SSIS\1cee35d1-b181-4ee7-a1de-eb9fc9c5ebe7.dtsConfig", Current directory: c:\windows\system32\inetsrv, Environment: ALLUSERSPROFILE=C:\Documents and Settings\All Users APP_POOL_ID=AppPoolName ClusterLog=C:\WINDOWS\Cluster\cluster.log CommonProgramFiles=C:\Program Files\Common Files COMPUTERNAME=servername ComSpec=C:\WINDOWS\system32\cmd.exe FP_NO_HOST_CHECK=NO lib=C:\Program Files\SQLXML 4.0\bin\ NUMBER_OF_PROCESSORS=2 OS=Windows_NT Path=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Dell\SysMgt\oma\bin;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\WINDOWS\system32\WindowsPowerShell\v1.0;C:\Program Files\Microsoft\Web Platform Installer\ PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.VBS;.PSC1 PROCESSOR_ARCHITECTURE=x86 PROCESSOR_IDENTIFIER=x86 Family 6 Model 37 Stepping 1, GenuineIntel PROCESSOR_LEVEL=6 PROCESSOR_REVISION=2501 ProgramFiles=C:\Program Files PSModulePath=C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\ SystemDrive=C: SystemRoot=C:\WINDOWS TEMP=C:\WINDOWS\TEMP TMP=C:\WINDOWS\TEMP USERPROFILE=C:\Documents and Settings\Default User windir=C:\WINDOWS

Any help at all will be most appreciated.

Cheers

Microsoft SQL Server Report builder

I'm building a report using a stored procedure (Haven't done this before), this is the error i get when i run query from the report builder. But it works perfectly from sql server

"An error occurred while executing the query. There is already an object named 'TempResult' in the database.

---

ADDITIONAL INFORMATION:

There is already an object named 'TempResult' in the database. (Microsoft SQL Server, Error: 2714)"

Is it possible to allocate memory to a query in MS SQL Server?

I have query inside a stored procedure and i am trying to see is there a way to specify memory that required for this query to use.

i know on server level we can change Minimum memory to use Query but i that is for all queries on the server but i am looking for an option at specific query level.

store sql result in a variable in python

I need help in storing the query result in a variable after executing the select statement. this is my code below.. there are no error but i need help in storing it in a variable in PYTHON. i am using pymssql and python 2.7.9. thanks in advance

conn = pymssql.connect(host='localhost', user='xx', password='xx', database='dbpython')
    c = conn.cursor()
    row = c.execute("select fid, fname, lname, contact from tblcontact where fname='maiji'")
    row = c.fetchall()
    while row:
        print row
        row = c.fetchall()

sql query to get the date difference in working hours and considering weekends the instances could happen on weekends and after working hours as well

using sql server 2005 table looks like below

Fax no  Date Action       Date Received      Action Taken By
44358820    3/1/15 8:18 AM      3/1/15 7:12 AM  hmagzoub

want to have something like this

Fax no  Date Action Date Received   Action Taken By ResponseTime in Hours   STATE
44358820    3/1/15 8:18 AM  3/1/15 7:12 AM  hmagzoub    1   OK
44386911    3/1/15 8:18 AM  3/1/15 7:20 AM  hmagzoub    1   OK
44059999    3/1/15 8:26 AM  3/1/15 7:24 AM  naqawi  9   LATE

I simply want to get the date diff only for weekdays here its sunday to thursday and friday saturday is off , and in work hours that is from 7am to 3pm , the dates could fall on the weekday and weekends and after working hours as well , further i want to add another column that is already there in the table called username that will identify which user processed the respective id .

My table looks like this

id, creation_date, completed_date, userid

if i could add the responsetime here then i would like to add add another function that checks if the resposetime is less than 8hrs then OK if over 8hrs then Late ... PLz help me , i tried for hours playing with the code above but could not complete the whole thing

I want to select random questions from a Table from each catogories

SQL Server 2005 query needed, please help.

I have a table which have questions and all questions are categorized TopicWise wise.

And another table which Contains topic and its weightage of number in the Subject

Now, I need to select 50 random questions from Table QUESTIONS which are based on the weightage of the Topic like out of 50 random selected questions there must be

5 questions from Subtopic_Id=1 and    
15 questions from Subtopic_Id=2 and
10 questions from Subtopic_Id=3 and
10 questions from Subtopic_Id=4 and
10 questions from Subtopic_Id=5

See Second image (Table Topics)

and the number of topics and their weightage may vary in different subjects, so union statement will be a bad idea for a dynamic query.

I even have not figure out a single bit of line of code for this selection.

How to add leading zeros to my data?

I have a varchar column that has variations of following data: WKKT-FM, 2/21 WKKT-FM, 1/24-2/14 WKKT-FM, 3/14-3/21, 4/11-4/18 IKKT-FM, 12/29-1/12, 1/26-2/09, 6/01-6/15

And so on. I need to place leading zeros in front of days and months. As you can see some of them already have leading zeros.

I tried looking for #/ or -#, but I have to account for some values that already have the leading zero.

A function would be preferable as I have to get this field via the SELECT statement.

What is an effective way to accomplish this?

how to connect remote MSSQL server via PHP

steps for connect remote mssql server 2005 using PHP but getting follwing error undefined function mssql_connect()

Inserting an image to sql server from another computer

How do I insert an image to an sql server from a client computer?

INSERT INTO imagetable (imagecolumn)
SELECT     BulkColumn
FROM         OPENROWSET(BULK 'C:\Image.png', Single_Blob) AS img

I'm getting this error and after searching i found out it's because the sql server is looking for the image path in the server machine. My imagecolumn is a varbinary(MAX) format.

Cannot bulk load because the file "C:\Image.png" could not be opened. Operating system error code 3(The system cannot find the path specified.)

Moving from SQL 2005 to 2012

Here is my Stored procedure written in SQLServer 2005. Now we are moving to SQL2012 and getting bunch of errors where I used *= . I know I have to use LEFT OUTER JOIN syntax, but somehow I am stumped. Can someone please help me ? Also getting error on where I used ABS in where clause.

These 2 lines in question in WHERE CLAUSE BELOW
currhold.current_hold__001 *= #tmp_transac.current_hold__001 and
((abs(t_quantity_c)> 0.01 and #tmp_transac.current_hold__001 is null) or

Full SP is here
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_getEPMData_GFS]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#factortemp','u') IS NOT NULL

BEGIN

DROP TABLE #factortemp

END


IF OBJECT_ID('tempdb..#transactemp','u') IS NOT NULL

BEGIN

DROP TABLE #transactemp

END

select *  into #transactemp from transac where tran_type in ('BUY','SHORT')


select curr_factor,cusip into #factortemp 
from mbs_fact A
where A.factor_date = 
(select max(B.factor_date) from mbs_fact B 
where B.cusip = A.cusip --and B.factor_date <= '10/30/2008'
group by B.cusip)

IF OBJECT_ID('tempdb..#pricetemp','u') IS NOT NULL

BEGIN

DROP TABLE #pricetemp

END

Create TABLE #pricetemp
(price numeric(19,8),cusip varchar(20),price_source varchar(20),Max_Price_Date smalldatetime)


IF OBJECT_ID('tempdb..#pricetemp1','u') IS NOT NULL

BEGIN

DROP TABLE #pricetemp1

END

IF OBJECT_ID('tempdb..#pricetemp2','u') IS NOT NULL

BEGIN

DROP TABLE #pricetemp2

END

select cusip, max(price_date) as MX_pxdate 
INTO #pricetemp1
from pricing 
group by cusip 
order by 1

SELECT A.cusip,B.Mx_pxdate, count(*) as dupcount 
INTO #pricetemp2
FROM pricing A, #pricetemp1 B 
WHERE A.cusip = B.cusip
AND A.price_date = B.MX_pxdate 
group by A.cusip,B.Mx_pxdate


insert into #pricetemp
select A.price,A.cusip,A.price_source,B.MX_pxdate
from pricing A, #pricetemp2 B
where A.price_date = B.MX_pxdate
AND A.cusip = B.cusip
AND B.dupcount = 1

insert into #pricetemp
select A.price,A.cusip,A.price_source,B.MX_pxdate 
from pricing A, #pricetemp2 B
where A.price_date = B.MX_pxdate
AND A.cusip = B.cusip
AND A.price_source='MANUAL'
AND B.dupcount = 2


IF OBJECT_ID('tempdb..#tmp_transac','u') IS NOT NULL
BEGIN
DROP TABLE #tmp_transac
END

select distinct current_hold__001, sum(abs(transac.total_amount)) as total_amount,broker
into #tmp_transac
from transac
where setl_y_n = 'N'
and replace(tran_type, ' ', '') in 
(select distinct replace(tran_type, ' ', '') as tran_type from ut_transac_types where amount_sign = 'POSITIVE') 
group by current_hold__001,broker



declare @System_Date smalldatetime
Select @System_Date = Convert(varchar(12),dbo.uf_getGLDate(),112)


--ISB
IF OBJECT_ID('tempdb..#tmpISB','u') IS NOT NULL

BEGIN

DROP TABLE #tmpISB

END

select distinct
     iss.user_desc1 as Issuer_ISB, 
     Guarantor.user_desc1 as Guarantor_ISB, 
     Security.Cusip  
     into #tmpISB
     from Security 
     left outer join Issuer Iss ON (Security.issuer = Iss.issuer ) 
    left outer join Issuer Guarantor ON (Security.Guarantor = Guarantor.issuer ) 


--

-----Existing EPM on Top
SELECT case 
when #factortemp.curr_factor = 0.00 then 0.00 
when #factortemp.curr_factor is null then 0.00 
else #factortemp.curr_factor end as curr_factor,
case 
when #pricetemp.price = 0.00 then 100.000000000 
when #pricetemp.price is null then 100.000000000 
else #pricetemp.price end as price,
replace(security.description1,',',' ') as description1,security.isin,portfoli.MGR1_TITLE as branch,
currhold.portfolio,replace(currhold.sec_type,' ','') as sec_type,
Convert(float,currhold.CURRENT_HOLD__001)*10000 as Trade_num,
currhold.cusip,
securtyp.prn_curr,
Chartacc.securities,
securitiesamt=case When SECURTYP.SEC_TYPE='RESIDCERT' Then Convert(decimal(18,2),CURRHOLD.GORIG_COST_C)
                        else Convert(decimal(18,2),CURRHOLD.T_QUANTITY_C)
End,
chartacc.INT_DUE_ACCRUED,
INT_DUE_ACCRUEDAmt = (CURRHOLD.T_ACCR_INTRST_C + CURRHOLD.T_INT_DIV_DUE_C),
chartacc.Discount,
DiscountAmt =  case When SECURTYP.SEC_TYPE='RESIDCERT' Then 0
                        When (CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C) > 0 THEN 0 
                        ELSE Convert(decimal(18,2),CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C)
End,
chartacc.Premium,
PREMIUMAmt=case    When SECURTYP.SEC_TYPE = 'RESIDCERT' Then 0
                   When (CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C) < 0 THEN 0 
                   ELSE Convert(decimal(18,2),CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C)
End,
chartacc.UNRLZD_GL_ASSET,
UNRLZD_GL_ASSETAmt=isnull(Convert(decimal(18,2),(CURRHOLD.GMARKET_VALUE_C - CURRHOLD.GBOOK_VALUE_C)),0),
chartacc.UNRLZD_GL_INC,
UNRLZD_GL_INCAmt = isnull(Convert(decimal(18,2),(CURRHOLD.GBOOK_VALUE_C - CURRHOLD.GMARKET_VALUE_C)),0),
chartacc.INTEREST_INCOME,
Interest_incomeamt= Convert(decimal(18,2),CURRHOLD.GAMZ_YTD_P+CURRHOLD.T_ID_EARN_YTD_P),
chartacc.RECVABLE_BROKER,
RECVABLE_BROKERAmt=
case when CURRHOLD.STATUS in ('SELL','SELL TBA') and (CURRHOLD.GSETTLE_DATE) >= dbo.uf_getGLDate()
then isnull((CURRHOLD.GORIG_COST_C)-(CURRHOLD.T_DUE_ACCR_CY_C),0)

else isnull(#tmp_transac.total_amount, 0) -- - isnull(seccash.prin_amount, 0)
end,
chartacc.PAYABLE_BROKER,
PAYABLE_BROKERAmt=
case when CURRHOLD.STATUS in ('BUY','BUY TBA') and (CURRHOLD.GSETTLE_DATE) >= dbo.uf_getGLDate() 
then isnull(-(CURRHOLD.GORIG_COST_C)-(CURRHOLD.T_DUE_ACCR_CY_C),0)

else 0
end,
CurrStatus=Case when currhold.status in ('BUY','SELL') AND currhold.closed_date > dbo.uf_getGLDate() and currhold.T_QUANTITY_C  <> 0 THEN 1
              when currhold.status in ('BUY','SELL') AND currhold.gsettle_date > dbo.uf_getGLDate() and currhold.T_QUANTITY_C  <> 0 THEN 3
else 2
end,
myPrincBalAL= Case when currhold.T_QUANTITY_C > 0 Then 'A' else 'L' end,
myAccruedAmtAL= Case when currhold.t_accr_intrst_c + currhold.t_int_div_due_c > 0 Then 'A' else 'L' end,
myUnearnedpremAL= Case when (currhold.T_QUANTITY_C - currhold.GBOOK_VALUE_C) < 0 Then 'A' Else 'L' end,
myUnearnedDiscAL = Case when (currhold.T_QUANTITY_C - currhold.GBOOK_VALUE_C) < 0 Then '' Else 'L' end,
myUnrealProfitAL= Case when currhold.Gunrl_gl_ytd_c > 0 Then 'A' Else 'L' end,
myUnrealLossAL = Case when currhold.Gunrl_gl_ytd_c > 0 Then '' Else 'L' end,
myCouponRate = case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM' 
                   then Convert(decimal(20,7),currhold.COUPON_RATE/100) 
                   else Convert(decimal(20,7),security.COUPON_RATE/100) end,
myFixRate = Case when [security].Float_Rate = 'Y' 
then Convert(decimal(20,7),[security].index_rate_add/100) 
else case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM' 
          then Convert(decimal(20,7),currhold.COUPON_RATE/100) 
          else Convert(decimal(20,7),security.COUPON_RATE/100) end
end,
myFixVarInd=Case when Security.float_rate='Y' then 'V' else 'F' end,
myIntIncomeAL = case when (currhold.t_accr_intrst_c + currhold.Gchange_amz_c) > 0 Then 'I' else '' end,
Convert(varchar(12),currhold.gTrade_Date,112) as RB_VALDATE,
Convert(varchar(12),currhold.maturity_date,112) as Maturity_date,
Convert(varchar(12),currhold.gTrade_Date,112) as RB_TRADEDATE,
Replace([security].sec_type,' ','') as Product,[security].guarantor as CIF_No,
myTradeType = Case when currhold.Portfolio = 'ABST' THEN 'TRADING' else 'SALE' end,
Open_Flag='Y',
Convert(varchar(12),currhold.GSETTLE_DATE,112) as GSETTLE_DATE,
currhold.t_quantity_c as Quantity,
INT_RATE= case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM' 
then currhold.Coupon_Rate - (security.Index_Rate_Add/100) 
else security.Coupon_Rate - (security.Index_Rate_Add/100) end,
security.Index_Rate_Add,
Convert(varchar(12),security.Next_PMT_Date,112) as Next_PMT_Date,
currhold.status,
currhold.account,
SECURITY.FLOAT_FREQ,
'SOURCE' as Source_Code,
Convert(varchar(12),@System_Date,112) as  System_Date,
#transactemp.broker,
security.issuer,
Convert(decimal(20,8),currhold.gorig_price) as Orig_Price,
isnull(portfoli.MGR2_TITLE,'') as OBU,
isnull(#tmpISB.Issuer_ISB,'') as Issuer_ISB,
Security.Author,
isnull(Convert(varchar(12),#pricetemp.Max_Price_Date,112),Convert(varchar(12),@System_Date,112)) as Max_Price_Date,
Security.Symb_pool,
isnull(Convert(varchar(12),Currhold.Issue_Date,112),'') as Issue_Date,
isnull(Convert(varchar(12),Security.Last_pmt_date,112),Convert(varchar(12),@System_Date,112)) as Last_pmt_date,
Security.Accrual,
isnull(Convert(varchar(12),Security.Last_float_Date,112),'') as Last_float_date,
isnull(Convert(varchar(12),Security.Next_Float_Date,112),'') as Next_float_date,
isnull([security].guarantor,'') as guarantor,
isnull(Convert(varchar(12),Currhold.Maturity_date,112),'') as LAST_PRIN_PMT,
Security.Country,
isnull(currhold.ANT_HEDGE,'') as ANT_HEDGE,
isnull(Broker.User_desc1,'') as Broker_ISB,
isnull(#tmpISB.Guarantor_ISB,'') as Guarantor_ISB
from securtyp,chartacc,security,portfoli,#factortemp,#pricetemp,currhold,#tmp_transac,issuer,#transactemp,broker,#tmpISB
where  
#tmpISB.cusip=security.cusip and
#transactemp.broker=broker.broker and
security.cusip=#transactemp.cusip and
#transactemp.current_hold__001 = currhold.current_hold__001 and
#transactemp.cusip = currhold.cusip and
currhold.current_hold__001 *= #tmp_transac.current_hold__001 and
((abs(t_quantity_c)> 0.01 and #tmp_transac.current_hold__001 is null) or
#tmp_transac.current_hold__001 is not null) and
portfoli.account=currhold.Account and 
portfoli.portfolio=currhold.Portfolio and
securtyp.sec_type=currhold.sec_type and
security.cusip=currhold.cusip and
currhold.account=chartacc.account and
currhold.portfolio = chartacc.portfolio and
currhold.sec_group = chartacc.sec_group and
currhold.sec_type=chartacc.sec_type and
currhold.custodian = chartacc.custodian and
currhold.cusip*=#factortemp.cusip and
currhold.cusip*=#pricetemp.cusip  and
security.issuer = issuer.issuer and
CURRHOLD.PORTFOLIO not in (select portfolio from ut_portfolio_exception where proc_name='SKY') 
and abs(CURRHOLD.T_QUANTITY_C) > 0.01
and currhold.account not in ('SEC PUR','SEC SOLD','RABO SEC','GOSMORE')

How to check for SQL Server Database version in WIX database installer

I have a database installer WIX tool and i want to check if sql server 2008 r2 is installed in the system and if it is not installed i want to show a message to install. How do i do it using WIX tool.....

Get Start of Year based on Last Month

I run a stored procedure on the 1st of the month that takes all data from the previous month and puts it into another table. I do it as follows:

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

insert into ThisTable
select Things
from AnotherTable
where AppsEntryDate >= DATEADD(month, -1, @startOfCurrentMonth)
and AppsEntryDate < @startOfCurrentMonth

I now need to build another query that does this on a yearly basis. So, right now it's March. I need to enter all the data from Jan 1 2015 through Feb 28 2015 using this same structure. The trick is, on Jan 1 2016, I need it to capture all data from 2015. How would I code this?

Why TSQL convert a function's result in one way and a character string to other way?

I try this command in SQL Server 2005 to obtain a MD5 from '123':

select SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  '123' )), 3, 32) 

and I get this result:

202cb962ac59075b964b07152d234b70

I want to convert to binary format,

select 
   convert(varbinary(16), SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  '123')), 3, 32)) 

And I get this result:

0x32003000320063006200390036003200

Why does this code:

select convert(varbinary(16), '202cb962ac59075b964b07152d234b70')

result in a different value?

0x32303263623936326163353930373562

sql support for my application in c#

i've created a small application which includes data.sqlclient ,so my question is that when i m running my application in another machine which doesnt have an sql installed should i be using any thing like sql runtime or something lyk tat .. please help me

THANKS IN ADVANCE.... :)

Show Query Results in Month/Year Order

I have a query that sales and it shows the month and year (field names are salemonth & saleyear) of the sale. Example return-set would be

January 2014
February 2014
March 2014
December 2014
January 2015

Now obviously I can't set it that way in my straight query as if I try to order by salemonth ASC it woudl show December, February, january, january, March. or even if I order by year ASC it still would not show in the actual calendar month order. How can I sort this result set to show in the order of an actual calendar?

One caveat their may be 0 sales for the month (november for example) I would still want this month/year shown in the query but have a 0 shown. Is this achievable?

Groovy Sql in Grails project to access SqlServer classpath issue

Environment: Windows 7 Enterprise, SP1, 64-bit, IntelliJ Ultimate 14.0.3, build #IU-139.1117, Java jdk 1.8.0_3

I'm trying to migrate data from Sql Server. It is not necessary to define a datasource. This works with Groovy standalone, but not in Grails: def sql = Sql.newInstance( 'jdbc:jtds:sqlserver://OSSA:1433/LTT;domain=camp', 'dxg151430', ***', 'net.sourceforge.jtds.jdbc.Driver' ) With Groovy, I put the driver jar, jtds-1.3.1.jar on the classpath. In Grails, I added this dependency to the BuildConfig.groovy: runtime "net.sourceforge.jtds:jtds:1.3.1" I Grails I get "No suitable driver found for jdbc:jtds:sqlserver://OSSA:1433/LTT;domain=camp". I had the same error message with Groovy standalone, before I added the .jar to the classpath. I've also tried to add the .jar to the Grails lib folder. And yes, with both options, dependency declaration or lib folder, I did "Grails compile --refresh-dependencies". Tried it from CMD and within the IDE.

SQL server corrupt log file attempted restore, want to continue with next file

Our live instance of SQL server 2005 crashed last night but managed to write out a corrupted transaction log file.

Partial results from RESTORE HEADERONLY commands below:

193000.trn OK - First LSN  140773000030325500001 / Last LSN 140773000304635700001

195224.trn Corrupted *Incomplete*

203000.trn OK - First LSN 140773000304635700001 / Last LSN 140773001216537800001

So it looks to me as though I should be able to ignore the 195224.trn file to continue the log restore. Unfortunately our automated log restore job tried to restore the 195224.trn file and of course got an error.

So now when i try to run

RESTORE LOG [dbname] FROM DISK =N'H:\Logs\203000.trn' WITH NORECOVERY

I get the below error

A previous restore operation was interrupted and did not complete processing on file 'log'. Either restore the backup set that was interrupted or restart the restore sequence. 

I have tried running both this file and the previous OK file WITH RESTART but that has not worked.

Is there anything else I can do without having to resort to something slower like restore using diff?

TSQL Pivot with combination result

I have data in SQL server 2005 table similar to below format.

OrderNo ProductId Sale
----------------------
1          A    £10
2          B    £20
3          C    £30
4          A    £10
4          B    £20
5          A    £10
5          B    £20
6          C    £30
6          B    £20
7          C    £30

I need to write TSQL query that would give me result table in this format.

NoOfOrders     'A' SaleValue 'B' SaleValue  'C' SaleValue
------------------------------------------------
Prod A (Only)   1   £10     
Prod B (Only)   1               £20 
Prod C (Only)   2                             £60
Prod A & B      2   £20         £40 
Prod A & C      0   £0          £0             £0
Prod B & C      1               £20            £30

Any idea greatly appreciated.

Need help to create file with name and current time stamp

I need to create .xlsx file exporting data from sql database and file name would be 'FileName' and current yyyymmdd.

I'm using following code.

declare @filepath nvarchar(4000); set @filepath = '\file1\Shared\Buying Report\NewFile' + CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR)+ RIGHT('00'+CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR),2)+ RIGHT('00'+CAST(DAY(CURRENT_TIMESTAMP) AS VARCHAR),2)+'.xlsx'

EXEC p_CreateExcel @db_name='st01', @table_name = 'Austomate_report', @file_name = @filepath

I enabled xP_cmdshell on my server also give right permission to destination folder and File got created at provided path with YYYYMMDD but not getting data copy on the file. Let me show you what's it shows me.

1,output NULL Starting copy... NULL 1 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.) NULL

2,output

NULL Starting copy... 1000 rows successfully bulk-copied to host-file. Total received: 1000 1000 rows successfully bulk-copied to host-file. Total received: 2000 1000 rows successfully bulk-copied to host-file. Total received: 3000 NULL 3741 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 94 Average : (39797.87 rows per sec.) NULL

3,

output The system cannot find the path specified. NULL

Search and Replace a a partial string / substring in mssql tables

I was tasked with moving an installation of Orchard CMS to a different server and domain. All the content (page content, menu structure, links, etc.) is stored in an MSSQL database. The good part: When moving the physical files of the Orchard installation to the new server, the database will stay the same, no need to migrate it. The bad thing: There are lots and lots of absolute URLs scattered all over the pages and menus.

I have isolated / pinned down the tables and fields in which the URLs occur, but I lack the (MS)SQL experience/knowledge to do a "search - replace". So I come here for help (I have tried exporting the tables to .sql files, doing a search-replace in a text editor, and then re-importing the .sql files to the database, but ran into several syntax errors... so i need to do this the "SQL way").

To give an example:

The table Common_BodyPartRecord has the field Text of type ntext that contains HTML content. I need to find every occurance of the partial string /oldserver.com/foo/ and replace it with /newserver.org/bar/. There can be multiple occurances of the pattern within the same table entry.

(In total I have 5 patterns that will need replacing, all partial string / substrings of urls, domains/paths, etc.)

I usually do frontend stuff and came to this assignment by chance. I have used MySQL back in the day I was playing around with PHP related stuff, but never got past eh basics of SQL - it would be helpful if you could keep your explainations more or less newbie-friendly.

The SQL server version is SQL Server 9.0.4053, I have access to the database via the Microsoft SQL Server Management Studio 12

Any help is highly appreciated!