Strange characters in MSSQL table

This is my first time working in a Microsoft environment, all of my experience being in Unix, so forgive me if my question is a common one (I looked but couldn't find anything quite like this).

I have a Microsoft SQL Server 2005 database, and many of the tables have text like the following:

¼ƒ¹ƒƒ…““ŽP ƒƒ…““ŽP ƒ

The above text seems to translate to NSX (I copied the text into a different column in the database, and checked in the application that uses this database, and it is "NXS"). Obviously, the application seems to know how to translate this text. I'm wondering, is this an encoding issue? Is it fixable such that I can view the text? Or, possibly, is this some method of encrypting the text?

I'm just hoping someone has encountered this before, knows what's up and how to fix it.

SQL Server Query returning all rows in a table instead of MAX()

Im trying to return the highest/last hour meter reading for each piece of equipment in an inventory, however, the query below lists all of the instances even using MAX.

select      sil.[Posting Date], 
            mre.[Service Item No_], 
            sil.[Job Code], 
            max(mre.[Reading]) as 'Hour Reading'     

from        [$meter reading entry]mre left outer join
            [$service invoice line]sil on mre.[Service Item No_] = sil.[Service Item No_]

where       sil.[Job Code] = 200


group by    mre.[Service Item No_], sil.[Job Code], mre.[Reading], sil.[Posting Date]

insert based on value in first row

I have a fixed file that I am importing into a single column with data similar to what you see below:

ABC$        WC        11683                                    
11608000163118430001002010056788000000007680031722800315723      
11683000486080280000002010043213000000007120012669100126691      
ABC$        WC                         000000020000000148000     
ABC$        WC        11683                                    
1168101057561604000050200001234000000027020023194001231940      
54322010240519720000502000011682000000035640006721001067210      
1167701030336257000050200008765000000023610029066101151149      
11680010471244820000502000011680000000027515026398201263982

I want to split and insert this data into another table but I want to do so as long as the '11683' is equal to a column value in a different table + 1. I will then increment that value (not seen here).

I tried the following:

declare @blob as varchar(5) 
declare @Num as varchar(5)

set @blob = substring(sdg_winn_blob.blob, 23,5)
set @Num = (Cnum.num + 1)

IF @blob = @Num
INSERT INTO SDG_CWF
    (
        GAME,SERIAL,WINNER,TYPE
    )
SELECT convert(numeric, substring(blob,28, 5)),convert(numeric, substring(blob, 8, 9)),
        (Case when (substring(blob, 6,2)='10') then '3' 
              when (substring(blob, 6,2)='11') then '4' 
              else substring(blob, 7, 1)
         End),
        (Case when (substring(blob, 52,2)='10') then '3' 
              when (substring(blob, 52,2)='11') then '4' 
              else substring(blob, 53, 1)
         End)

FROM sdg_winn_blob
WHERE blob not like 'ABC$%'
else
print 'The Job Failed'

The insert works fine until I try to check to see if the number at position (23, 5) is the same as the number in the Cnum table. I get the error:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "sdg_winn_blob.blob" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Cnum.num" could not be bound.

How to Combine the data which is other rows in Same SQL Table [duplicate]

This question already has an answer here:

• Simulating group_concat MySQL function in SQL Server? 8 answers

SQL - joining records from same table

I have data in a table and I need to simply the big table into smaller one version of table by reducing duplicated rows the concat data into rows.

Here is my base table:

BW  SPEC    BAND    PORT    MODE    VC  TC  DOMAIN  BLOCK
---------------------------------------------------------
-   SNDR    1      see_b    umts    VC1 TC1  SVB      rx1
-   SNDR    2      see_b    umts    VC1 TC1  SVB      rx1
-   SNDR    3      see_b    umts    VC1 TC1  SVB      rx1
-   SNDR_2  4      see_b    umts    VC1 TC1  SVB      rx1
-   SNDR    5      see_b    umts    VC1 TC1  SVB      rx1
-   SNDR    6      see_b    umts    VC1 TC1  SVB      rx1
-   SNDR    8      see_b    umts    VC1 TC1  SVB      rx1
12  SNDR_2  9      see_b    umts    VC1 TC1  SVB      rx1
12  SNDR_2  29     see_b    umts    VC1 TC1  SVB      rx1

I want to combine data which present in BAND:

Based on BW Spec PORT MODE VC TC DOMAIN BLOCK for example

Thanks in advance

enter image description here

how to get update records which is newly inserted only in sql?

i want to move data from old sql db to new sql db which are having same tables. but new table is having 100(assume) records and old db is having 1000 records. i inserted all records in new db but i got diffetent id's because this id is having identity in new db.

now my concern is how can i update new identity value for the foreigh key columns. the foreign key column is having duplicate in new db. i have to update foreign key column only for newly inserted records.

User Defined function SQL 2008

I'm trying to create a calculated column that returns an INT value, I ve created a function and need to pass the ndx number to the function and having issues with returning multiple values within the sub query. how do I pass the ndx number to the function, I'm assuming that the calculated column looks at values from the same row!?

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

CREATE FUNCTION dbo.Nat_Weight(@me38_cycle_data_ndx INT)
RETURNS INT
AS 
BEGIN
DECLARE @nat_weight INT =0;
DECLARE @mattype1 INT;
DECLARE @mattype2 INT;
DECLARE @mattype3 INT;
--DECLARE @me38_cycle_data_ndx INT;
-- get material type, need only hoppers 1-3, hopper 4,5,6 material type will never = 2
SET @mattype1 = (SELECT typehopper_01 FROM mm_Cycle_Data);
SET @mattype2 = (SELECT typehopper_02 FROM mm_Cycle_Data );
SET @mattype3 = (SELECT typehopper_03 FROM mm_Cycle_Data );
-- if material type=2 then add to @nat_weight ,  
IF @mattype1 = 2
    set @nat_weight = (SELECT cyclehopper_01 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=@me38_cycle_data_ndx );
IF @mattype2 = 2
    set @nat_weight =@nat_weight+ (SELECT cyclehopper_02 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=@me38_cycle_data_ndx );
IF @mattype3 = 2
    set @nat_weight =@nat_weight+ (SELECT cyclehopper_03 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=@me38_cycle_data_ndx )
RETURN @nat_weight
END 

LIKE clause in PIVOT?

I want to have a LIKE kind of clause in a PIVOT. Right now, I have this:

PIVOT (
    MAX(TAX_AMOUNT) FOR TAX_CODE IN ([TVH], [TVH-ON], [TVH-NB], [TPS], [TVQ])
) PVT

But that is problematic, if new TVH TAX_CODE's are being added (TVH-SK, TVH-QC, etc.), this pivot will stop working.

I tried [%TVH%] but that's a syntax error.

Any ideas ?

Passing a variable from Access2010 to stored procedure

I have created an SSIS package that runs from the following stored procedure:

DECLARE @SQLQuery ASVARCHAR(2000)
DECLARE @ServerName VARCHAR(200)
SET @ServerName ='myServerName'
SET @SQLQuery ='DTExec /F ^"\\...\...\My_Package.dtsx^" '
EXEC master..xp_cmdshell @SQLQuery

I then have an Access2010 application with a few buttons that run 3 procedures all set up the same way running the package using the following code:

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString As String

sConnString = "Provider=sqloledb; Server=MyServerName; Database=MyDB; Trusted_Connection=True; Integrated Security=SSPI;"

conn.Open sConnString

With cmd
  .ActiveConnection = conn
  .CommandType = adCmdText
  .CommandText = "EXEC my_sproc;"

Now I need to add text boxes to two of the buttons that trigger the procedures where a user will enter a file name. The button needs to pass that file name to the stored procedure and the stored procedure needs to then use it in the package.

Any suggestions are greatly appreciated. This is my first time doing something like this and I am a little lost.

Thanks, Scott

MVC 5 and Sql server date filter issue

I have datetime values in my database table like "05/05/2015 23:00:00", I am putting date filer in my query and try to fetch all the data of "05/05/2015"date as below

select * from table where date<="05/05/2015". It's not giving me record which have value "05/05/2015 23:00:00" in database.

Please suggest the way..

whitespace is not allowed at this location

I have an SSIS package that is importing a flat file into my database. I get the error message in the title and it points to the location of an ampersand. I can run this package from SSIS and it runs without issue. But when I try to run it is a SQL Server agent job I get the error. I also just finished setting up 2 other jobs that work similarly and have similar data (company names with ampersands) and they work fine.

Group by calculated Case column from another table

I have the below code which works correctly however the data isn't in the format that I want to display. The code only works when I group by Plant however this will obviously create multiple PlantGrps. Is it possible to group by the case when statement?

select 
    Case 
    When Plant IN(
                    Select Client From 
                                            CO_001_Plants_090_Final
                                                                    where CustGrp = 'HovisMills'
                    ) Then 'HovisMills' 
        When Plant IN(
                    Select Client From 
                                            CO_001_Plants_090_Final
                                                                    where CustGrp = 'HovisBakeries'
                    ) Then 'HovisBakeries'                  

                            When Plant IN(
                    Select Client From 
                                            CO_001_Plants_090_Final
                                                                    where CustGrp = 'HovisGroup'
                    ) Then 'HovisGroup'     

                    end as PlantGrp,
    --Type
    sum(Line_Val),Count(Material)

    FROM            dbo.vw_DA_003_ChargingRecord as P

    where   

            Plant IN(select Client 
    FROM            dbo.CO_001_Plants_090_Final
    where Upper(CustGrp) = 'HOVIS')
    and Charging_Period = '201509'
    and Type <> 'Carriage'
    group by 


    Plant, Type

How do I use this condition inside CASE WHEN?

I want if Status column = 1

Check If there are rows in another table return 'Check' and If no rows return 'In DB'

SELECT ID, UserName,
CASE [Status]
    WHEN 1 THEN
        if ((Select Count(*) From Logs_TB Where Logs_TB.UserName = Users_TB.UserName) > 0)
            'Check'
        Else
            'In DB'
    WHEN 2 THEN 'Revision'
    WHEN 3 THEN 'Sent'
END AS StatusName
FROM Users_TB CROSS JOIN Logs_TB

How to get node content based on sibling content from xml column in database?

<book>
 <author>a1</author>
 <title>t1</title>
</book>
<book>
 <author>a1</author>
 <title>t2</title>
</book>
<book>
 <author>a2</author>
 <title>t3</title>
</book>
<book>
 <author>a3</author>
 <title>t4</title>
</book>

I want to get all titles and only titles of books by an author. The book info is stored in a db column as xml data. Each is in a different row. I tried various value calls and it didn't work at all. The closest I got was:

select parameters.query('//book[author="a1"]/title/text()') from tablename

But this returns 4 rows with

"t1"
"t2"
""
""

Please help

SQl division over a declared value

I'm using Sql 2005, I want to check the "TypeHopper_0x" value if= 2, add "CycleHopper_0x" to a @nat_weight, This part I was able to accomplish . Now @nat_weight =1871

Now I want to divide the value of"cycleHopper_0x) by the @nat_weigh and UPDATE the value to "HPR_x_PRCT", this is where i'm having problems. I'm targeting index 8000 with this code, but eventually I'll need to run for the entire table.

for hopper 5 the operation update the E 177/1871 = 0.0149

DECLARE @nat_weight INT;
DECLARE @mattype1 INT;
DECLARE @mattype2 INT;
DECLARE @mattype3 INT;
-- get material type, need only hoppers 1-3, hopper 4,5,6 material type will never = 2
SET @mattype1 = (SELECT typehopper_01 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);
SET @mattype2 = (SELECT typehopper_02 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);
SET @mattype3 = (SELECT typehopper_03 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);
-- if material type=2 then add to @nat_weight ,  
IF @mattype1 = 2
    set @nat_weight = (SELECT cyclehopper_01 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);

IF @mattype2 = 2
    set @nat_weight =@nat_weight+ (SELECT cyclehopper_01 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000);

IF @mattype3 = 2
    set @nat_weight =@nat_weight+ (SELECT cyclehopper_03 FROM mm_Cycle_Data WHERE me38_cycle_data_ndx=8000)


UPDATE mm_Cycle_Data 
Set  HPR_1_PRCT =CycleHopper_01/@nat_weight, --my error might be here
     HPR_2_PRCT =CycleHopper_02/@nat_weight,
     HPR_3_PRCT =CycleHopper_03/@nat_weight,
     HPR_4_PRCT =CycleHopper_04/@nat_weight,
     HPR_5_PRCT =CycleHopper_05/@nat_weight
WHERE me38_cycle_data_ndx=8000

me38_cycle_data_ndx Cycle       SettingHopper_01       SettingHopper_02       SettingHopper_03       SettingHopper_04       SettingHopper_05       SettingHopper_06       CycleHopper_01 CycleHopper_02 CycleHopper_03 CycleHopper_04 CycleHopper_05 CycleHopper_06 TotalHopper_01 TotalHopper_02 TotalHopper_03 TotalHopper_04 TotalHopper_05 TotalHopper_06 TypeHopper_01 TypeHopper_02 TypeHopper_03 TypeHopper_04 TypeHopper_05 TypeHopper_06 Get_Address t_stamp                 HPR_6_PRCT             HPR_3_PRCT             HPR_6_ErrPrct          HPR_3_ErrPrct          HPR_4_ErrPrct          HPR_5_ErrPrct          HPR_2_ErrPrct          HPR_2_PRCT             HPR_5_PRCT             HPR_4_PRCT             HPR_1_ErrPrct          HPR_1_PRCT             CurrentWorkingShift
------------------- ----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- ------------- ------------- ------------- ------------- ------------- ------------- ----------- ----------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------------------
8000                61820       100                    0                      0                      0                      1.39999997615814       0                      11871          0              0              0              177            0              274443072      2059773259     0              0              5744198        23988001       2             1             0             0             3             3             50          2015-10-06 15:16:37.267 0                      0                      0                      0                      0                      -28.5714282989502      0                      0                      0                      0                      0                      1                      2
7999                939         100                    10                     1.5                    0                      0                      0                      21375          2181           334            0              0              0              7115425        388436         107363         0              0              0              2             1             3             0             3             0             38          2015-10-06 15:16:16.043 0                      1.53800475597382       0                      2.53365039825439       0                      0                      -4.2374324798584       9.84164714813232       0                      0                      0                      100                    2
7998                413         0                      100                    0                      0                      0                      0                      0              7962           0              0              0              0              0              3407112        0              0              0              0              1             2             3             0             0             0             39          2015-10-06 15:13:35.420 0                      0                      0                      0                      0                      0                      0                      100                    0                      0                      0                      0                      2
7997                61818       100                    0                      0                      0                      1.39999997615814       0                      11836          0              0              0              175            0              274431201      2059773259     0              0              5744021        23988001       2             1             0             0             3             3             50          2015-10-06 15:13:03.387 0                      1.51998627185822       0                      0                      0                      -28.5714282989502      0                      0                      1                      0                      0                      100                    2

Dynamic SQL UNPIVOT and Select INTO temporary table

I know that there are a few questions out there on Dynamic SQL and Insert into temporary tables, but I cannot find an exact match for my particular problem.

I have data in 52 columns in ##tmp and I need to total these and store in ##tmp2.

Note: The syntax works fine if I remove the first line

 select * into ##tmp2 from

It's the "Select Into" that I have a problem with! My current Dynamic query provides the following syntax, but I cannot get this to work. Currently the open bracket after the initial "from" has no matching closing bracket.

I've tried various positions for the final ")" but get a mixture of

Incorrect syntax near ')' -- if placed at the end of the statement
Invalid column name 'wk' -- if added as  "from ##tmp) onto the second from"
Incorrect syntax near the keyword 'group'. -- if added after ")) as U"

Here's the current syntax

select * into ##tmp2 from(
select x,y,sum(wk) as mysum  from ##tmp
  unpivot (wk for nwk in ([1],[2],[3],[4],[7],[8],[9],[10],[11],[12],[13],   [14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],
[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52]))
 as u  group by x,y

Any ideas?

Regex to replace stored procedure params with declarations

Quite often I have to rewrite stored procedure because I don't have execution rights to a "normal" code - meaning rewriting stored procedure params to declarations + SETs.

For example:

@pParam  VARCHAR(20),
@pPlant  VARCHAR(4) = NULL,
@pDateTimeFrom  VARCHAR(30) = NULL,
@pDateTimeTo  VARCHAR(30) = NULL,
@pLanguage  VARCHAR(2) = 'EN',
@pClass  NVARCHAR(MAX) = NULL,
@pFilterCompleteDate  BIT = NULL,
@user VARCHAR(50) = NULL,
@updateKey VARCHAR(50) = NULL,
@seqnoKey INT = NULL,
@comment VARCHAR(1000) = NULL,
@testID INT = NULL,
@info NVARCHAR(MAX) = NULL

And with regex find and replace I want it in this format:

DECLARE @pParam VARCHAR(20) 
SET @pParam = 
DECLARE @pPlant VARCHAR(4) 
SET @pPlant = NULL
DECLARE @pDateTimeFrom VARCHAR(30) 
SET @pDateTimeFrom = NULL
DECLARE @pDateTimeTo VARCHAR(30) 
SET @pDateTimeTo = NULL
DECLARE @pLanguage VARCHAR(2) 
SET @pLanguage = 'EN'
DECLARE @pClass NVARCHAR(MAX) 
SET @pClass = NULL
DECLARE @pFilterCompleteDate BIT 
SET @pFilterCompleteDate = NULL
DECLARE @user VARCHAR(50) 
SET @user = NULL
DECLARE @updateKey VARCHAR(50) 
SET @updateKey = NULL
DECLARE @seqnoKey INT 
SET @seqnoKey = NULL
DECLARE @comment VARCHAR(1000) 
SET @comment = NULL
DECLARE @testID INT 
SET @testID = NULL
DECLARE @info NVARCHAR(MAX) 
SET @info = NULL

I came up with this regex

Find:

(@.+?)\b\s+\b(.*?)(\s=\s(.*),?|,)

Replace:

DECLARE \1 \2 \r\nSET \1 = \4

But the 4th capture group includes the , at the end of the string. When I included it in the regex, it didn't get the first line without value and last line without comma.

Could you help me find a regex which would correctly find all declarations (no value, value, no comma). Thanks!

Identifying duplicates within a table: looking for query advice

So I am trying to identify duplicated contact records within an account, and looking for the best way to do this. There is a an account table, and a contact table. Below is the query I've come up with to give me what I need, but I feel like there is probably a better/more efficient way to do this, so looking for any feedback/advice. Thanks in advance!

SELECT * FROM sysdba.CONTACT a WITH(NOLOCK)
WHERE EXISTS
(
SELECT ACCOUNTID, FIRSTNAME, LASTNAME, EMAIL FROM sysdba.CONTACT b WITH(NOLOCK)
GROUP BY ACCOUNTID, FIRSTNAME, LASTNAME, EMAIL
HAVING COUNT(*) > 1
AND a.ACCOUNTID = b.ACCOUNTID AND a.FIRSTNAME = b.FIRSTNAME AND a.LASTNAME = b.LASTNAME AND a.EMAIL = b.EMAIL
)
ORDER BY ACCOUNTID, FIRSTNAME, LASTNAME, EMAIL

Here is another way I can do this, but having to use DISTINCT seems ugly..

SELECT DISTINCT a.CONTACTID, a.FIRSTNAME, a.LASTNAME, a.EMAIL FROM sysdba.CONTACT a WITH(NOLOCK)
JOIN sysdba.CONTACT b WITH(NOLOCK)
ON a.ACCOUNTID = b.ACCOUNTID AND a.FIRSTNAME = b.FIRSTNAME AND a.LASTNAME = b.LASTNAME AND a.EMAIL = b.EMAIL AND a.CONTACTID != b.CONTACTID
ORDER BY a.CONTACTID, a.FIRSTNAME, a.LASTNAME, a.EMAIL

Alias table that's actually a pivoted one?

I have this query:

SELECT
    A.USERID 
    A.NAME
    PVT.PHONE 'PROBABLY A CASE STATEMENT ON NULL WILL GO HERE...
    PVT.ADDRESS 'ON HERE AS WELL...
FROM
    USERS A
    'I NEED TO CREATE A PIVOT TABLE HERE WITH THE ALIAS OF 'PVT' ON TABLE 'B'

B Contents:

 UserID  PHONE          ADDRESS      TYPE
 1       444-555-2222   XXXXXXX      PHONE
 1       XXXXXXX        66 Nowhere   NOTADDRESS 

I want, on the same row, the user's phone by getting B.PHONE if TYPE = 'PHONE'.

I also want, on the same row, the user's address by getting B.ADDRESS content if TYPE = 'ADDRESS'.

As you see in the table dump above, I don't have a record matching the user ID AND TYPE = 'ADDRESS'

So I would need to show a blank or 'No address' in the main SELECT which will show the phone, but on the same row, blank or 'No address'.

I don't want to create an INNER JOIN because if there are no matching UserID's in B, the query will not return the info that I have in table A for that user.

Also, a LEFT JOIN will create two rows, which I don't want.

I think I pivoted table as alias would do it, but I don't know how to create such an alias.

Any ideas ?

How do I perform daily, monthly SQL Server 2005 maintenance?

Team,

I have SQL Server 2005 and I am new to DBA role and wondering if there is any T-SQL Script/Stored Procedure out there that they will help me accomplish the following on regular basis - Daily, Weekly, Monthly Yearly.

1. Hardware Utilization and Performance (Memory, Available Disk Storage, and then CPU Utilization)
2. Database and Server Configuration (Database Settings, Database Status, Database Back Up Status - Completion, Max Server Memory Settings)
3. Database Maintenance (SQL Server Logs, File Growth, Mirroring Monitor , Back up completion, Index Fragmentation)

Thank You.

SQL 2005 - Updating table in a one to many relationship

I have a staging table that was created from a flat file and modified before copying to a final destination. Some of the records will be inserted and the rest updated if needed. The only issue I have is it is a one to many relationship. The table is a list of retailers and some of them are entered with the same store name and SS# more than once but with a different contact type. Such as:

Store_ID   SS#         First_Name     Last_Name        Type  Description
________________________________________________________________________
1234       123-12-1234 JP             Crawford         A     Owner
1234       123-12-1234 JP             Crawford         D     Other Contact 1
1234       987-76-9876 Aaron          Nola             E     Other Contact 2
1236       321-12-3210 Mikael         Franco           A     Manager
1236       321-12-3210 Mikael         Franco           J     Other Contact 7

I need to be able to select one of the records when there is a duplicate store_id/SS#. There is no date available so I do not know which record was added last. In cases where one of the records is "Owner" and the other is "Other Contact" I can assume the correct one is "Owner". Same for if one of them is "Manager" for instance. But there are some examples where one record may be "Other Contact 5" and the next is "Other Contact 6".

Any suggestions are greatly appreciated.

Cannot resolve the collation conflict between "SQL_AltDiction_CP850_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

I have a piece of code in my stored procedure as below -

update tblexpwitretrocmdocs set sCheckedOut = A.sEditor, idone = 0 
from #tblDocs A
where A.iDocumentTypeId in (16,17,13,11)
and A.sid not in (select SID from tblexpwitdocumentgeneral)

I am getting the error "Cannot resolve the collation conflict between "SQL_AltDiction_CP850_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation." for the first line of code.

The column - sCheckedOut in table - tblexpwitretrocmdocs has collation SQL_AltDiction_CP850_CI_AS. So to make the column - sEditor compatible to it, I defined the temp table as below -

CREATE TABLE #tblDocs(
iId INT IDENTITY (1,1),
SID NVARCHAR(50),
iDocumentTypeId INT,
sType NVARCHAR(200),
sEditor NVARCHAR(50) COLLATE SQL_AltDiction_CP850_CI_AS 
)

Still I am getting the same error. Please help me resolve this.

Whats wrong with my connection string?

Hello please help me out with this connection string

try
        {
            conn = new SqlConnection("Server=RANJITHM-MOBL\MSSQLSERVER1;DataBase=master;Trusted_Connection=True;");


            conn.Open();
            SqlCommand cmd = new SqlCommand("dbo.new", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            rdr = cmd.ExecuteReader();
            Console.WriteLine(" connection success");

        }


// I hope i have mentioned correct connection string but not able to execute my   stored procedure

// please see  my images which attached  also  

How to deny Execute permission for bunch of stored procedure except some of them?

I have a login sa and database name called dummy and 40 stored procedures called sp1,sp2,sp3,sp4 etc.,. what is my requirement is i have to access only 2 stored procedure sp1 and sp2 in my login only.other procedure should not to access in my login.Please give any ideas.

SQL Server sum of two varchar columns from their alias

I have a table as shown below

declare @Location Table([state] nvarchar(30) null,city nvarchar(30) null) Insert Into @Location Values('California','San Francisco') Insert Into @Location Values('California',null) Insert Into @Location Values('California','Orange County') Insert Into @Location Values('California',null)

And a select statement as shown below

select [state],city,[state] as CurrentState, case when city is null then 'Sacramento' else city end as CurrentCity from @Location

The output is as follows

I want a fifth column titled Address which gives me the sum of the columns CurrentState and CurrentCity Like thus:

Address California, San Francisco California, Sacramento California, Orange County California, Sacramento

Is this possible?

I've already tried CurrentState+', '+CurrentCity. It doesn't work

SA user password for software which automatically installs sql 2005

I am testing a software which requires sql 2005 for storing the data. The sql 2005 database is automatically installed when I installed the software. During the software installation I didn't encounter any screen to configure the sql 2005 database so I am assuming the configuration is done by the software installer.

I can reset the password for the sa but theoretically it would stop the application from working. Can anyone push me in a direction by which I can access the database with a client.

How to import MS SQL dumps from Ruizata Project's SQL Server Dumper?

I used http://ift.tt/1OCf0z6 to make an sql dump of a database. How do I import the data into an empty database on a new server, without having to run the hundreds of .sql files manually?

SQL Query - Change my long date format to DD-MM-YYYY

I have been researching a lot of similar questions on how to convert date format to DD-MM-YYYY but none work for the date format I'm using.

This is the date format I'm working with: Wednesday, October 14, 2015 5:57 PM

And this IS a "date" field not a text field. I have a feeling that the inclusion of the day of the week is precluding my other attempts from working.

I'm new to SQL so forgive me if I'm overlooking something obvious.

Thank you!

Sql 2005 sub query

I have A wo_records table, want to be able to extract the current work order for each machine with the start time. I wrote this query to rank each records and gives the latest entry a rank of 1, but I'm unable to nest it where I can use A where clause (where rank =1)

SELECT  * ,
        RANK() over ( PARTITION BY get_address order by t_start desc)  AS    Last_value
        FROM  wo_records 

ndx|Wo        | t_start                 |t_end               | get_address| Rank
--------------------------------------------------------------------------------
45  12521231    2019-01-07 15:41:24.000 NULL                    44           1
46  12521231    2018-01-08 15:42:24.000 2018-01-08 15:47:24.000 44           2
39  12521231    2016-01-21 15:43:24.000 2016-01-21 15:49:24.000 44           3

What is the correct way to nest this statement to retrieve only the rows with rank= 1?

Thanks,

Group by Keywords

I have a table with records as follows

CategoryId and KeywordId are coming from two different tables "Categories" and "Keywords" both with columns Id and Name. I want to show group wise count of keywords and their names how can I do it? Please help

expected result

Group by Keywords

I have a table with records as follows

CategoryId and KeywordId are coming from two different tables "Categories" and "Keywords" both with columns Id and Name. I want to show group wise count of keywords and their names how can I do it? Please help

Result of a sql query to a new table

In the below sql query, the Subquery executes successfully,when trying to store the results of that subquery to a new table t_test.I am getting error as

Incorrect syntax near ')'

select * into t_test from
(select prm.RNumber,
case when pom.Category = 'Medicine'
    then pom.field
    end  as [Dept],
pop.branch
from tbl_pr_m prm join tbl_po_m pom on prm.rnumber = pom.rnumber
join tbl_po_p pop on prm.rnumber = prm.rnumber);

May i know, how to store the results to a new table t_test.

SQL 2005 query uses ? which doesn't work in SQL 2012

I'm working on an application which queries live data on an SQL server. The user enters a name within '% %' marks to search. Ie. if the user was to search for the owner of a property such as Noble, they would enter %noble%. We recently upgraded both the application and the SQL server that stores the data from SQL 2005 to SQL 2012.

The existing query and the new query are identical:

SELECT aurtvalm.pcl_num 

FROM aurtvalm 
INNER JOIN rtpostal ON aurtvalm.ass_num = rtpostal.ass_num

WHERE rtpostal.fmt_nm2 Like ?

In the old version, the above query produces 16 results. The exact same query in 2012 version produces an error:

Incorrect Syntax near '?'

Has the use of the ? symbol changed since SQL 2005?

Group results with max elements per group

I need to divide records into groups that have max n elements per group id, I need extra column to be some kind of counter that will increment by one when it reaches certain number of rows. I'm not speaking English native so I've build example code to show what data I have and what results I need.

Here is sqlfiddle: http://ift.tt/1NcJjJN

and here is sqlfiddle code:

CREATE TABLE tab1 
    (
     id int
    );

INSERT INTO tab1 (id) VALUES (1)
INSERT INTO tab1 (id) VALUES (2)
INSERT INTO tab1 (id) VALUES (3)
INSERT INTO tab1 (id) VALUES (4)
INSERT INTO tab1 (id) VALUES (5)
INSERT INTO tab1 (id) VALUES (6)
INSERT INTO tab1 (id) VALUES (7)
INSERT INTO tab1 (id) VALUES (8)
INSERT INTO tab1 (id) VALUES (9)
INSERT INTO tab1 (id) VALUES (10)
INSERT INTO tab1 (id) VALUES (11)
INSERT INTO tab1 (id) VALUES (12)
INSERT INTO tab1 (id) VALUES (13)
INSERT INTO tab1 (id) VALUES (14)
INSERT INTO tab1 (id) VALUES (15)
INSERT INTO tab1 (id) VALUES (16)
INSERT INTO tab1 (id) VALUES (17)
INSERT INTO tab1 (id) VALUES (18)
INSERT INTO tab1 (id) VALUES (18)
INSERT INTO tab1 (id) VALUES (20)

SELECT id,
NTILE(4) OVER(ORDER BY id DESC) AS X
from tab1

My table with data looks like this:

 id
 --
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

and I need result that look looks like this:

 id | group
 --
 1 | 1
 2 | 1
 3 | 1
 4 | 1
 5 | 2
 6 | 2
 7 | 2
 8 | 2
 9 | 3
10 | 3

In above example I want to group all records into groups containing max 4 elements, so records 1-4 are in group 1, records 5-8 in group 2 and record 9-10 are in third.

Probably there is build in function that can do this, but because of my English skills I wasn't able to find it.

I've tried counting all elements in table and dividing it by number or elements and then using it in NTILE, but this way I get equal group and that's not result I need.

Add a column containing the id of the previous row in each group

I want to add a column that contains the id of the previous row in each group.

Sample:

Product       ID
Orange        1
Orange        2
Orange        3
Orange        4
Apple         5
Apple         6
Apple         7
Grapes        8
Grapes        9

Desired output:

Product       ID
Orange        1
Orange        1
Orange        2
Orange        3
Apple         5
Apple         5
Apple         6
Grapes        8
Grapes        8

Thank you!

Get the top ID in each group in SQL Server

i want to add a column that contains the first in each group.

Sample:

Product       ID
Orange        1
Orange        2
Orange        3
Orange        4
Apple         5
Apple         6
Apple         7
Grapes        8
Grapes        9

Desired output:

Product       ID
Orange        1
Orange        1
Orange        1
Orange        1
Apple         5
Apple         5
Apple         5
Grapes        8
Grapes        8

How to make a SSRS work for multiple users

I can't find anything on this so I don't think I am asking the question right but here is my situation. I have a stored procedure which the end user passes a list of filter criteria, since I don't know what the filter criteria will be I used dynamic SQL. Further, to allow for more then one user to run the stored procedure concurrently I used all dynamically named temp tables so there would be no collisions. That all works.

Now my problem is how to output the report. Right now I have a SSRS report pointing to a single database that the stored procedure dumps its output to. When the stored procedure finishes the report is displayed. This works for one user but if two users run the stored procedure at the same time I have no way of knowing which output data will show on the report. Complicating issues is the dynamic user filter criteria can greatly effect the time the stored procedure takes to complete. I can see the report loading data just as the other stored procedure session is truncating or loading data to the output table.

I can queue up requests and run them one at a time but ideally I want them to be able to run concurrently as several users have to run this report many times at the beginning of each month. Is there a way to ensure that the data displayed on the report to the end user matches the data outputted from the stored procedure session the user ran.

Thanks.

Setup of DG4SQL Gateway from Oracle 11.2 to SQL 2005

First, I am new to using Oracle and need to make a connection from Oracle 11.2 to SQL Server 2005. I have read many articles, but I still can't setup a heterogeneous connection. I am asking for any help, please?? Thanks in Advance

The error displayed is ORA-12154

I am using one pc running Windows 7, which has windows virtual xp.
Oracle is installed on on the XP virtual machine and SQL 2005 is installed on the windows 7 machine (64 bit).

Listed are my entries from tnsnames.ora, listener.ora and initdg4sql.ora:

tnsnames.ora

dg4msql=
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = VirtualXP-28481)(PORT = 1521))
  (CONNECT_DATA = (SID = dg4msql)
)
(HS = OK)
)

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = dg4msql)
     (ORACLE_HOME = C:\app\XPMUser\product\11.2.0\dbhome_1)
     (PROGRAM = dg4msql)
   )
  )
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = VirtualXP-28481)(PORT = 1521))
   )
 )

ADR_BASE_LISTENER = C:\app\XPMUser

initdg4sql.ora

HS_FDS_CONNECT_INFO=[inspiron]//Academic
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

SQL Server : group by with corresponding row values

I need to write a T-SQL group by query for a table with multiple dates and seq columns:

DROP TABLE #temp
CREATE TABLE #temp(
       id char(1),
       dt DateTime,
       seq int)

Insert into #temp values('A','2015-03-31 10:00:00',1)
Insert into #temp values('A','2015-08-31 10:00:00',2)
Insert into #temp values('A','2015-03-31 10:00:00',5)
Insert into #temp values('B','2015-09-01 10:00:00',1)
Insert into #temp values('B','2015-09-01 10:00:00',2)

I want the results to contains only the items A,B with their latest date and the corresponding seq number, like:

id  MaxDate CorrespondentSeq
A   2015-08-31 10:00:00.000 2
B   2015-09-01 10:00:00.000 2

I am trying with (the obviously wrong!):

select id, max(dt) as MaxDate, max(seq) as CorrespondentSeq
from #temp
group by id

which returns:

id  MaxDate CorrespondentSeq
A   2015-08-31 10:00:00.000 5  <-- 5 is wrong
B   2015-09-01 10:00:00.000 2

How can I achieve that?

EDIT

The dt datetime column has duplicated values (exactly same date!)

I am using SQL Server 2005

Creating SQL Where clause dynamically that is not open to SQL Injection

I wrote this bit of VBA code that creates a SQL query dynamically based on the number of fields the user has selected and values read from an XL spreadsheet. It basically just adds "FIELD_VARIABLE=VALUE_VARIABLE OR" to the where clause and then removes the final OR after the loop ends.

It works for N number of fields added like I was hoping but my concern is security because I think I could just put like ';DROP TABLE Projects or some other malicious code into the spreadsheet from where the program is reading FIELD_VARIABLES. To a lesser extent since the query is different every time the execution path must be different and that probably slows down execution time.

I'm thinking of looking into parameterized queries or T-SQL to improve this. Was hoping one of you smart fellas could point me in the right direction before I waste too much time on this. Here is the relevant VBA code:

 '---loop through array of search fields and search values using the same index
 '---since the arrays sizes will always be the same and create where filters dynamically
          i = 1
          For i = LBound(sLookupFields) To UBound(sLookupFields)
                Set rngLookup = wsLookupSrc.cells(counter, lLookupCols(i))
            '---clear where from last iteration through loop
                SQLWhereDynamic = ""

                SQLWhereDynamic = SQLWhereDynamic & " p." & sLookupFields(i) & " = '" + CStr(rngLookup.Value) & "' OR"
          Next i
        '---remove extra ' OR'
          SQLWhereDynamic = Left(SQLWhereDynamic, (Len(SQLWhereDynamic) - 3))

          SQLValue = wsLookupSrc.cells(counter, lLookupCols(1)).Value

          SQLWhereDefault = "WHERE p.ClientId = " + CStr(iClientId) + ""
          SQLQuery = SQLSelect + SQLWhereDefault + " AND (" + SQLWhereDynamic + ");"

I need help in converting this sql query to linq

I need to convert the following sql query to linq.

I am having trouble in using left outer join in LINQ. Any help will be appreciated

select 
LTRIM(RTRIM(SKU.SELLER_SKU)) AS "SKU Number",
LTRIM(RTRIM(SKU.DESCRIPTION)) as "Description",
LTRIM(RTRIM(SOURCING.SOURCING_CATEGORY)) as "Product Category",
LTRIM(RTRIM(SKU.UOM_CODE)) as "Seller UOM",
LTRIM(RTRIM(SKU.LEAD_TIME)) as "Lead Time",
CASE
 WHEN CONRT.CONVERSION_RATE is null  THEN 
'1'
else
LTRIM(RTRIM(conrt.CONVERSION_RATE)) end as "Converstion Rate",
LTRIM(RTRIM(SKU.QTY_MEASURE)) as "Qty UOM",
LTRIM(RTRIM(SKU.START_DATE)) as "Seller Start Date",
LTRIM(RTRIM(SKU.END_DATE)) as "Seller End Date",
CASE
 WHEN SKU.END_DATE >= getdate() or SKU.END_DATE is null THEN 
 'Published'
 else
    'Unpublished' end as "Web Status",
     LTRIM(RTRIM(SUPP.SUPPLIER_NAME)) as "Supplier",
     LTRIM(RTRIM(SUPPSKU.SUPPLIER_SKU)) as "Supplier SKU",
     LTRIM(RTRIM(SUPP.BLANKET_PO)) as "Blanket Number",
     LTRIM(RTRIM(SUPPSKU.UOM_CODE)) as "Supplier UOM",
     LTRIM(RTRIM(SUPPCOST.UNIT_COST)) as "Supplier Unit Cost",
     LTRIM(RTRIM(SUPPSKU.START_DATE)) as "Supplier Start Date",
     LTRIM(RTRIM(SUPPSKU.END_DATE)) as "Supplier End Date",
     LTRIM(RTRIM(SKU.MANUFACTURER_NAME)) as "Manufacturer Name",
     LTRIM(RTRIM(SKU.MANUFACTURER_SKU)) as "Manufacturer Part Number"


from [dbo].[SELLER_ITEMS] as SKU
LEFT OUTER JOIN  [dbo].[SUPPLIER_ITEMS] as SUPPSKU
ON SKU.ITEM_ID = SUPPSKU.SELLER_ITEM_ID
LEFT OUTER JOIN SOURCING_CATEGORY AS SOURCING
ON SKU.SOURCING_CATEGORY_ID = SOURCING.SOURCING_CATEGORY_ID 
LEFT OUTER JOIN [dbo].[SUPPLIERS] as     SUPP
ON SUPPSKU.SUPPLIER_ID = SUPP.SUPPLIER_ID
LEFT OUTER JOIN [dbo].[CONVERSION_RATES] AS CONRT
ON SUPPSKU.SUPPLIER_ITEM_ID = CONRT.SUPPLIER_ITEM_ID
LEFT OUTER JOIN DBO.SUPPLIER_ITEM_COSTS SUPPCOST
ON SUPPSKU.SUPPLIER_ITEM_ID = SUPPCOST.SUPPLIER_ITEM_ID

Need conversion of above query to linq...

Query optimization with variables

First, I apologize if this question is a common one... I can't seem to find the correct terms to search...

I have a somewhat large table that has an incremental ID and an inserted datetime value. There are a couple indexes on it, including a primary key on the ID and an non-clustered index on the inserted time and ID. So, when I write a query like the following, it's very quick:

select min(ID), max(ID)
from tbl
where inserted between '2015-10-07' and '2015-10-08'

However, if I variablize the where clause conditions (as below), it's quite a bit slower:

declare @sTime datetime, @eTime datetime
select @sTime = '2015-10-07', @eTime = '2015-10-08'

select min(ID), max(ID)
from tbl
where inserted between @sTime and @eTime

When I look at the two query plans I see the obvious problem. The first query utilizes a single seek on the non-clustered index of the inserted time and ID. However, the second query instead performs 2 scans on the primary key (which is just the ID).

So, my question is two-fold: 1) Why is sql server performing these optimizations, and 2) how can I fix it?

How to migrate from SQL Server 2005 to SQL Server 2012

I have this scenario:

Old Server -> SQL Server 2005 with the current Database

New Server -> SQL Server 2012 no database created.

I want to migrate the old database to the new one.

I've tried to create a backup from the Management Studio 2005 and restore in the 2012. Failed.

I've connected from the 2012 server using Management Studio 2012 to the old server and create the backup. Then restored in the new one. Failed.

Then, I tried to use the Copy database Wizard. But a error message shows up: The destination server can not be an instance of SQL Server 2005 Express or higher .

So, how it is supposed to do the backup?

I'm getting the scripts in sql files as last resort...

PS: I've check this thread, I need to migrate to 2008 and then 2012? How to migrate a database from SQL Server 2005 to 2008 as is?

Counting and grouping challenge in a pivot table with T-SQL

I have a pivot table that converts a vertical database design to a horizontal one:

The source table:

Id  ParentId    Property    Value
---------------------------------
1   1           Date        01-09-2015
2   1           CountValue  2
3   1           TypeA       Value1
4   1           TypeB       Value2
5   1           TypeC       Value2
6   2           Date        15-10-2015
7   2           CountValue  3
8   2           TypeA       Value3
9   2           TypeB       Value22
10  2           TypeC       Value99

After pivoting this looks like:

ParentId    Date        CountValue  TypeA   TypeB   TypeC
----------------------------------------------------------
1           01-09-2015  2           Value1  Value2  Value2
2           15-10-2015  3           Value3  Value22 Value99

Then, there's a look-up table for valid values in columns TypeA, TypeB and TypeC:

Id  Name    Value
-----------------
1   TypeA   Value1
2   TypeA   Value2
3   TypeA   Value3
4   TypeB   Value20
5   TypeB   Value21
6   TypeB   Value22
7   TypeC   Value1
8   TypeC   Value2

So, given the above structure I'm looking for a way to query the pivot table in a way that I'll get a count of all invalid values in TypeA, TypeB and TypeC where Date is a valid date and CountValue is not empty and greater than 0.

How can I achieve a result that is expected and outputted like below:

Count   Column
--------------
0       TypeA
1       TypeB
1       TypeC

I've accomplished the result by creating three several queries and glue the results using UNION, but I think it should also be possible using the pivot table, but I'm unsure how. Can the desired result be realized using the pivot table?

Note: the database used is a SQL Server 2005 database.

Create a Linked Server for a MySQL Database in SQL Server 2005

I'm currently trying something I've never done before - I'm trying to access MySQL data in an SQL Server environment. Where I am, we have two servers (the MySQL and MSSQL servers), and each stores different types of data. Recently I've found a need to write queries that include data for both servers. One of the ways I read to do this is adding a "Linked Server" in SQL Server. But try as I might I cannot seem to get a Linked Server added.

I am working on a Windows 7 system, our SQL Server instance is 2005, and the MySQL version is 5.5.31.

What I've done so far is this:

 - Downloaded and installed the MySQL ODBC drivers (http://ift.tt/1hr1Vdl)
 - Created a System DSN using that driver for the MySQL database, called "Production". (I also tested the connection from the Data Sources application and it connects just fine).
 - Checked for the Provider in SQL Server (MSDASQL) to configure options such as "allow nested queries" and "allow 'LIKE' operator"
 - right click on Linked Servers in Server Objects in SQL Server > "New Linked Server"

From here, I fill out the linked server info like this:

---

 Checked "Other data source" (not SQL Server)


 Chose Provider "Microsoft OLE DB Provider for ODBC Drivers"


 Product Name: "Production"


 Data Source: (the name of the System DSN) Production

---

Then in the security tab, I chose the "Connections will be made using this security context" and provided my username and login for the MySQL server.

Then this is where I run into an error every time. I always get the following error message:

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

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

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "FLUX".
OLE DB provider "MSDASQL" for linked server "FLUX" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

For help, click: http://ift.tt/1LitqSF

If you click that link for help, by the way, nothing shows up. I saw other people suggested trying an older MySQL ODBC driver, adding a System Data Source for the 32bit driver as well as the 64bit driver, and restarting my machine (not the DB server) -- none of these fixed my error.

What I do not understand is why my System DSN seems to work and connect fine, but SQL Server cannot see the Data Source. Does anyone see what I'm missing, or what I might be configuring incorrectly?

Thanks for your time.

EDIT Clarified that I restarted my local machine, but not the DB server since trying to add the Linked Server.

Batch process to delete table from SQL Server backup files

I have a set of full backups from a same SQL Server database saved in different files. For example:

• 01.my-database.bak
• 02.my-database.bak
• 03.my-database.bak

I want to delete a specific table from those files to save disk space. There is a batch process to do that?

MsSql Merge not working

I'm trying to preform a merge but for some reason i get a error.

What I'm doing is: I am insert all the records where the productname is not empty and it is not marked as double in a temp table.

After I loop through temp table to insert/update every record in the real table [Products]

MERGE INTO [Products] AS T
USING SingleProducts AS S
ON (T.SoftwareID = S.[@ProductCode] AND T.PriceType = S.[@PriceType])
WHEN NOT MATCHED BY TARGET
    THEN INSERT (SoftwareID, IsActive, [Image], ProductNameNL, ProductNameFR, DescriptionNL, DescriptionFR, UnitPrice, PriceType, CreationDate, LastUpdatedDate)
        VALUES (S.[@ProductCode], 0, 'no-image.png', S.[@ProductNameNL], S.[@ProductNameFR], S.[@DescriptionNL], S.[@DescriptionFR], 
                    S.[@UnitPrice], S.[@PriceType], S.[@CreationDate], GETDATE())
WHEN MATCHED
    THEN UPDATE SET
        T.[SoftwareID] = S.[@ProductCode],
        T.[ProductNameNL] = S.[@ProductNameNL],
        T.[ProductNameFR] = S.[@ProductNameFR],
        T.[DescriptionNL] = S.[@DescriptionNL],
        T.[DescriptionFR] = S.[@DescriptionFR],
        T.[UnitPrice] = S.[@UnitPrice],
        T.[PriceType] = S.[@PriceType]
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, inserted.*, deleted.*;

Calculating the AVG value per GROUP in the GROUP BY Clause

I'm working on a query in SQL Server 2005 that looks at a table of recorded phone calls, groups them by the hour of the day, and computes the average wait time for each hour in the day.

I have a query that I think works, but I'm having trouble convincing myself it's right.

SELECT
    DATEPART(HOUR, CallTime) AS Hour,
    (AVG(calls.WaitDuration) / 60) AS WaitingTimesInMinutes
FROM (
    SELECT
        CallTime,
        WaitDuration
    FROM Calls
    WHERE DATEADD(day, DATEDIFF(Day, 0, CallTime), 0) = DATEADD(day, DATEDIFF(Day, 0, GETDATE()), 0)
        AND DATEPART(HOUR, CallTime) BETWEEN 6 AND 18
) AS calls
GROUP BY DATEPART(HOUR, CallTime)
ORDER BY DATEPART(HOUR, CallTime);

To clarify what I think is happening, this query looks at all calls made on the same day as today, and where the hour of the call is between 6 and 18 -- the times are recorded and SELECTed in 24-hour time, so this between hours is to get calls between 6am and 6pm.

Then, the outer query computes the average of the WaitDuration column (and converts seconds to minutes) and then groups each average by the hour.

What I'm uncertain of is this: Are the reported BY HOUR averages only for the calls made in that hour's timeframe? Or does it compute each reported average using all the calls made on the day and between the hours? I know the AVG function has a optional OVER/PARTITION clause, and it's been a while since I used the AVG group function. What I would like is that each result grouped by an hour shows ONLY the average wait time for that specific hour of the day.

Thanks for your time in this.

Bring through a newly created calculated column in another query

I have 2 separate queries below which run correctly.Now I've created a calculated column to provide a count of working days by YMs and would like to bring this through to query1(the join would be query1.Period = query2.Yms) please see the query and outputs below.

SELECT        Client, ClientGroup, Type, Value, Period, PeriodName, PeriodNumber, ClientName
FROM            metrics.dbo.vw_KPI_001_Invoice 



select YMs,sum(case when IsWorkDay = 'X' then 1 else 0 end) from IESAONLINE.Dbo.DS_Dates
where Year > '2013'
group by YMs 

Query 1
Client  ClientGroup Type    Value   Period  PeriodName        PeriodNumber   ClientName
0LG0    KarroFoods  Stock   5691.68 201506  Week 06 2015    35  Karro Foods Scunthorpe

Query 2
YMs (No column name)
201401  23

can not select Id of a persian value from the microsoft sql server in android

i have MSSQL DB,wich is SQL_Latin1_General_CP1_CI_AS collation and filled out with some persian values, the type of the field stored in DB is String(nvarchar(50), null) I want to get the ID of this String,that is Integer. so i open my Connection and write this in my code :

                ConnectionHelper connectionHelper = new ConnectionHelper();
                statement2 = connectionHelper.getConnection().createStatement();
                setType();
                ResultSet resultSet = statement2.executeQuery("select Id from tblProductCategory where Name='" + getType() + "'");

But i couldnt. getType() return a string in persion from my GridView.

After hours, i insert Latin String in my tblProductCategory and select its ID, and it worked correctly. My problem is with Persian Language. i searched and find that mssql server doesnt have utf-8, but nvarchar support this. any Idea?

MS SQL try catch ignored when executed by php mssql_query

I simplified my problem with a simple stored procedure in MS SQL 2005 with a try catch block.

It goes like that:

CREATE PROCEDURE testError

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    BEGIN TRY
    DECLARE @X INT
    ---- Divide by zero to generate Error
        SET @X = 1/0
        select 'Command after error in TRY block' AS retour
    END TRY
    BEGIN CATCH
        select 'Error Detected' AS retour
        return
    END CATCH
    select 'Command after TRY/CATCH blocks' AS retour
END

When I execute it in management studio, I get the following result: Error Detected, which is what I want.

But when I execute it in PHP with mssql_query, I get the following result: Command after error in TRY block

Why my catch block is not triggered when an SQL error is encountered?

I want SQL to stop further execution and go to the catch block, as it does in management studio.

Thanks a lot for your help!

Convert time from British to American format

I have the below code which is failing at the 'CAST('10-10-2014' AS DATETIME)', please can someone assist?

SELECT Sum(poval)
FROM   iesa_dwhs.dbo.vw_an_purch_bkb_010_sources vw_AN_Purch_BKB_010_Sources
WHERE  Upper(plant) = Upper(('0LH0'))
       AND dt BETWEEN Cast('10-10-2014' AS DATETIME) AND Getdate() - 7
       AND Upper(matcat) = 'CODED'

Advanced sql with windowing claus

SELECT a.*,
       SUM(s.amount) over(ORDER BY s.month rows unbounded preceding) AS a ,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows unbounded preceding) AS b,
       SUM(s.amount) over(PARTITION BY s.month ) AS c_1,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN unbounded preceding AND unbounded following) AS c,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN 1 preceding AND unbounded following) AS d,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN 1 preceding AND 1 following) AS e,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows BETWEEN unbounded preceding AND 1 following) AS f,
       SUM(s.amount) over(PARTITION BY s.month ORDER BY s.month rows CURRENT ROW) AS g
  FROM all_sales s,
       (SELECT *
      FROM all_sales) a
 WHERE s.rowid = a.rowid;

/ --above query give the result shown below what is difference between c_1 and c column.

    YEAR  MONTH PRD_TYPE_ID EMP_ID  AMOUNT  A   B   C_1 C   D   E   F   G
1   2006    1   1             21    1.00    1   1   10  10  10  3   3   1
2   2006    1   1             21    2.00    3   3   10  10  10  6   6   2
3   2005    1   2             21    3.00    6   6   10  10  9   9   10  3
4   2005    1   2             22    4.00    10  10  10  10  7   7   10  4
5   2006    2   1             21    5.00    15  5   11  11  11  11  11  5
6   2005    2   1             21    6.00    21  11  11  11  11  11  11  6
7   2005    3   1             21            21      7   7   7   7   7   
8   2006    3   2             21    7.00    28  7   7   7   7   7   7   7
9   2005    4   1             21    8.00    36  8   17  17  17  17  17  8
10  2006    4   2             21    9.00    45  17  17  17  17  17  17  9
11  2006    5   2             21            45      10  10  10  10  10  
12  2005    5   1             21    10.00   55  10  10  10  10  10  10  10
13  2006    6   1             21    11.00   66  11  23  23  23  23  23  11
14  2005    6   1             21    12.00   78  23  23  23  23  23  23  12
15  2005    7   2             21    13.00   91  13  27  27  27  27  27  13
16  2006    7   1             21    14.00   105 27  27  27  27  27  27  14
17  2005    8   2             21    15.00   120 15  31  31  31  31  31  15
18  2006    8   1             21    16.00   136 31  31  31  31  31  31  16
19  2005    9   2             21    17.00   153 17  35  35  35  35  35  17
20  2006    9   1             21    18.00   171 35  35  35  35  35  35  18
21  2005    10  2             21    19.00   190 19  39  39  39  39  39  19
22  2006    10  1             21    20.00   210 39  39  39  39  39  39  20
23  2006    11  1             21    21.00   231 21  43  43  43  43  43  21
24  2005    11  1             21    22.00   253 43  43  43  43  43  43  22
25  2006    12  2             21    23.00   276 23  47  47  47  47  47  23
26  2005    12  1             21    24.00   300 47  47  47  47  47  47  24

TSQL Null data to '' replace

I have the following query:

SELECT pics.e_firedate FROM et_pics

Result:

NULL
2014-12-01 00:00:00.000
2015-04-03 00:00:00.000
NULL
NULL

I want to replace NULL values to ''.

CASE 
        WHEN pics.e_firedate IS NULL THEN ''
        ELSE pics.e_firedate
END

makes NULL transforming to 1900-01-01 00:00:00.000, which I've tried to cast and replace with no success also.

How can I achive my goal?