Error Converting NVARCHAR to DATETIME

I create a temp table

CREATE TABLE #Test(theDate DATETIME)

I use this query to insert data into the temp table

INSERT INTO #Test VALUES(CONVERT(NVARCHAR, '2016-09-30' 21))

It works on one database (SQL Server 2005), but gives error on another (SQL Server 2008). I can't remember the exact error, but it has something to do with 'Error converting NVARCHAR TO DATETIME'

Why is working on one database, but not another? Is there a special property to enforce error on mismatched type? I can't find information anywhere.

Distribution Transaction Completed - Error - SQL Server 2005

Trying to execute a stored procedure to update a certain data warehouse, however when I execute the stored procedure I get an error message stating:

error Msg 8525, Level 16, State 1, Line 1 Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

After some Googling, it suggested to configure DTC settings, but I have already checked my MSDTC and it is already checked for allowing inbound and outbound for Transaction Manager Communication.

Any ideas to fix it?

Select last value from within multiple groupings (SQL Server)

I'm unsure how to both group and select the last value from within that group and could use some expertise.

This simple table (snippet of table below) holds the values of various timestamps for when a series of URLs I'm checking don't respond correctly. The yellow highlighted lines represent the data that I'm attempting to capture.

Essentially I'm trying to capture the last downtime timestamp for each grouping. failure_id will auto-increment with each entry.

I've got a partial success with the following:

WITH FAILURES AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY url_id ORDER BY failure_id DESC) AS lastFail
    FROM URL_FAILURES
)

and then building my actual SELECT statement with that data and the condition

WHERE
  lastFail = 1

...but it disregards the groupings that I need for last_check_success.

---

Microsoft SQL Server 2008 R2 (SP3)
Database Compatibility: SQL Server 2005

Not able to Append @FIPSName value on the selected Query using Procedure

ALTER PROCEDURE [dbo].[usp_RmsExecuteValidationRule]        

-- Parameters           
 @nRuleId INT     

AS BEGIN        
-- Local variables        
DECLARE @sqlstat AS NVARCHAR(MAX)        
DECLARE @params  AS NVARCHAR(MAX)        
DECLARE @RULE_QUERY NVARCHAR(MAX)     
DECLARE @FIPS  varchar(5) 


SET @sqlstat = N'SELECT @RULE_QUERY=RULE_QUERY from GdmValidationRuleMaster where RULE_ID = @nRuleId'        
--SET @sqlstat =  N'SELECT ' + @RULE_QUERY + '=RULE_QUERY from GdmValidationRuleMaster where RULE_ID = ' + @nRuleId 

SET @params = N'@nRuleId INT,   @RULE_QUERY NVARCHAR(MAX) OUTPUT'        
EXEC sp_executesql         
    @sqlstat,        
    @params,        
    @nRuleId = @nRuleId,     
    @RULE_QUERY= @RULE_QUERY OUTPUT 

-- Output   

SELECT @RULE_QUERY            

END

When we Exec usp_RmsExecuteValidationRule 1 It Returns @RULE_QUERY i.e:

SELECT GDMID AS GDM_ID from @FIPSName+CADMIN1 WHERE INTPRIORITY IS NULL

Here I have intentionally added @FIPSName+ As prefix because

I have another table LCountry table which FIPS coulumn

When we It Returns

FIPS
----
GM
FR
UK
AN
BE
BU
BL
BR
BH
CA
AU
VT
CI
CH
CO
CS
EZ
AS
DA
EC
SP
AR
BD
BB
BF
AV
AA
AC
DO
CU
CJ
DR
GP
GJ
HA
JM
ST
RQ
SC
MB
MH
XN
XM
XJ
VC
VI
TK
TD
TB
RN
XL
XK
VQ
GR
GT
GQ
HK
HO
HU
ID
EI
IS
GZ
XC
WE
IN
IT
SM
JA
LS
LU
MX
NU
NL
NO
NZ
PM
PE
RP
PL
PO
RO
SW
SI
LO
ES
TU
TW
SZ
VE
SN
SB
PF
PG
PC
NE
NF
MQ
US
SX
TE
TT
WQ
BQ
DQ
BS
CK
CQ
CR
AY
AT
BV
LQ
KQ
JQ
JU
KT
IO
IP

Using Loop I am able fetch data using ObjectID of Lcountry.

DECLARE @LoopCounter INT , @MaxBcountryObjId INT, 
        @FIPSName NVARCHAR(100)
SELECT @LoopCounter = min(OBJECTID) , @MaxBcountryObjId = max(OBJECTID) 
FROM dbo.LCOUNTRY

WHILE(@LoopCounter IS NOT NULL
      AND @LoopCounter <= @MaxBcountryObjId)
BEGIN
   SELECT @FIPSName = FIPS
   FROM dbo.LCOUNTRY WHERE OBJECTID = @LoopCounter

   PRINT @FIPSName  
   SET @LoopCounter  = @LoopCounter  + 1 

This query Return @FIPSName.

QUESTION

I want to This return Value @FIPName Should Appends on Rule_Query return Value. and the same value can be executed too.

For Example :

SELECT GDMID AS GDM_ID from @FIPSName+CADMIN1 WHERE INTPRIORITY IS NULL
Changes Into

SELECT GDMID AS GDM_ID from GM+CADMIN1 WHERE INTPRIORITY IS NULL
Changes Into 

GDMID
------         (The above query must be executed and Return GDMID.)
1198

Why date is not taking 13/09/2016

I am checking condition for 12/09/2016 to 13/09/2016 but it is not showing me data for 13/09/2016 and giving error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Here is my query

SELECT DISTINCT  
   b.mkey ,  a.N_UserMkey, cuser_id,isnull(a.N_UserMkey,cuser_id) aa,
   ISNULL(b.first_name + ' ', '')  
   + ISNULL(b.last_name, '') NAME, convert(varchar,a.U_datetime,103) Action_Date
  FROM      inward_doc_tracking_trl a  
   INNER JOIN user_mst b ON isnull(a.N_UserMkey,cuser_id) = b.mkey  
  WHERE 
  convert(datetime,a.U_datetime,103) 
    BETWEEN convert(varchar,'12/09/2016',103)
  AND convert(varchar,'13/09/2016',103)
   and b.mkey=2357

Check if two different values appear successively in a table in SQL?

There's a table that looks like this (there are more columns, but not relevant to the query):

DocumentId | DocumentStateId | TransitionMoment
111222       -2                2016-04-21
111222       -1                2016-04-22
111222       -7                2016-04-23
111222       -5                2016-04-24
111222       -6                2016-04-25
111222       -1                2016-04-26
333141       -2                2016-05-01
333141       -7                2016-05-09
333141       -6                2016-05-10
333141       -3                2016-05-15
777525       -1                2016-02-10
777525       -6                2016-02-10
777525       -7                2016-02-10
777525       -5                2016-02-10
777525       -2                2016-02-10

What options do I have to check whether a document has went from state "-7" to state "-6" consecutively (without transitioning through other states in between)? In the example Document no. 33141.

Thanks in advance!

How to SUM Only One Time Per UniqueId in SQL?

I have two tables that look roughly like this:

Table A

DocumentId (*is unique) DocumentDate
1                       2016-01-01
2                       2016-01-01
3                       2016-02-01
4                       2016-03-01

and Table B

ContractId SnapshotTimeId NetFinanced
1          20160231       300
1          20160331       300
1          20160431       300
2          20160231       450
2          20160331       450
2          20160431       450
3          20160331       500
3          20160431       500
4          20160431       150

I would like the final table to look something like this:

DocumentDate NetFinanced
2016-01-01   750
2016-02-01   500
2016-03-01   150

I have tried the following and it doesn't work:

SELECT A.DocumentDate, SUM(B.NetFinanced)
 FROM A
 JOIN B on B.ContractId=A.DocumentId
GROUP BY A.DocumentDate

Any ideas? Thanks in advance

Count how many rows are for a value in SQL?

I have a table that looks like this:

[ContractId]     [ContractDate] [SnapshotTimeId]    [DaysPastDue] [Exposure]

Int(not unique)  Datetime       Int(format20160431) Int           Int

The table is sorted by ContractId, ContractDate.

Now, I would like to add a 6th column, let's call it Unique, which has value 1 for the first ContractId value then adds 1 until it bumps across the next ContractId. Basically, I want to know how many rows I have for each ContractId and put the values, incrementally, in a column.

Edit: I want the output to look like this

>DocumentId ContractDate    SnapshottimeId  DPD Exposure Unique
>1          31-Aug-15       31-Aug-15       0   500      1
>1          31-Aug-15       30-Sep-15       5   450      2
>1          31-Aug-15       31-Oct-15       35  450      3
>1          31-Aug-15       30-Nov-15       7   350      4
>1          31-Aug-15       31-Dec-15       37  350      5
>1          31-Aug-15       31-Jan-16       67  340      6
>2          31-Aug-15       30-Jun-14       3   800      1
>2          31-Aug-15       31-Jul-14       15  760      2
>2          31-Aug-15       31-Aug-14       45  750      3
>2          31-Aug-15       30-Sep-14       75  750      4
>2          31-Aug-15       31-Oct-14       0   630      5
>2          31-Aug-15       30-Nov-14       15  590      6
>2          31-Aug-15       31-Dec-14       45  580      7

How to rollup columns

We are running SQL Server 2005. I am having problems rolling up columns.

create table group_roll 
(
    id_name int,
    full_name varchar(50),
    qty int
)
go

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 10)

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 40)

insert into group_roll (id_name, full_name, qty) 
values (1, 'jane smith', 50)

insert into group_roll (id_name, full_name, qty) 
values (1, 'dean smith', 10)

insert into group_roll (id_name, full_name, qty) 
values (2, 'frank white', 5)

insert into group_roll (id_name, full_name, qty) 
values (2, 'Ann white', 12)

insert into group_roll (id_name, full_name, qty) 
values (1, 'john smith', 8)

insert into group_roll (id_name, full_name, qty) 
values (2, 'frank white', 10)

insert into group_roll (id_name, full_name, qty) 
values (3, 'perry mason', 10)
go

select * 
from group_roll
order by id_name

Output:

   id_name       full_name          qty
   --------------------------------------
    1            john smith          10
    1            john smith          40
    1            jane smith          50
    1            dean smith          10
    1            john smith           8
    2            frank white         10
    2            frank white          5
    2            Ann white           12
    3            perry mason         10

I want the result to be rolled up into something like this

    id_name  name                                   qty
    ----------------------------------------------------
     1       john smith, jane smith, dean smith     118
     2       frank white, ann white                  27
     3       perry mason                             10

How do you code to rollup names and qty as shown?

Thank you,

Seyed

SQL Server: How to write query to bring data in particular format?

I have below sample data: Here Events are repeated twice.

Sample data

Now I want to write query to display data in the below format. Output data

I am trying to use RANK function. I am running out of ideas.... Anyone, Please help me with query. Thanks

The parameter "@parameter" is not the same type as the type it was created with. Drop and recreate the proc using a two-part name for the type

I have a error in SQL server 2005. Help me! Error:"The parameter "@parameter" is not the same type as the type it was created with. Drop and recreate the proc using a two-part name for the type." Thank you very much!

Add CDept_Id In first query for result

In my first query, I want to get CDept_Id. But CDept_Id column does not exist in inward_doc_tracking_hdr table.

It comes from inward_doc_tracking_trl table. like below

select CDept_id from inward_doc_tracking_trl where ref_mkey in
(Select mkey from inward_doc_tracking_hdr where doc_no = 'IW/HU/16/42')

So, From this. I get CDept_Id. Now I want to add this in my below query.

Select mkey,Delivered_By,Department_Id, 
 (Select mkey from erp190516.dbo.emp_mst where mkey  in 
 (select employee_mkey from erp190516.dbo.user_mst where mkey =   To_User))User_Id, 
Doc_Type,Email_Id,Ref_No,convert(varchar(25),Ref_date,103) Ref_date,
Inward_Amt, Remarks, party_name, disp_through
from erp190516.dbo.inward_doc_tracking_hdr, CDept_id  -- add CDept_id here
 where doc_no = 'IW/HU/16/42'  
 and Status_Flag = '13'

How to add this

UPDATE

inward_doc_tracking_hdr mkey is equal to inward_doc_tracking_trl ref_mkey

SQL Replaced Stored Procedure

Is there a way to recover replaced Stored Procedure after restoration. I forgot to create a backup before replacing the database.

SQL Server JOIN returns Cartesian result

I am trying to combine two views and it returns a Cartesian result.

View 1 192 (this view has inner join with the order id for the expired keys)

View 2 70 (this view has inner join with order id and sale date to get the renewed keys)

When I join these views (any of the joins), it results in 13340 rows returned.

Thanks

ETL from SQL Server 2005 to SQL Server Management Studio 2016

I am trying to copy a database from sql server 2005 server to another server in sql server managment studio 2016. (server1.database1) to (server2.database1) I tried detach and attached method and it did not work. I also tried to copy the data base and it did not work either! I need all of tables and views and pr! finally I could copy all of tables and pr's but problem is I can not have the views as a view and they just saved as a table. to solve this issue I thought if I copy the query from server1.views and execute that query in server2.database1.views I will have the view since I have all objects copied in server2.database1. but I get some errors: one was "format" function that they used in sql server 2005 which should be "convert" now. I changed all of that "format" functions and executed the query and now I left with this errors: Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37 [Batch Start Line 35] Object is invalid. Extended properties are not permitted on 'dbo.v_RASAM', or the object does not exist.

So view that I want to copy calls : dbo.v_RASAM And I am sure (because I checked the procedure name sp_addextendedproperty) sp_addextendedproperty exist! So question that I have is possible that procedure follows some statements that needs to be change since "view" had functions that are not available in sql server management studio 2016?(this maybe sounds stupid but I think of any possible reason that may cause this issue) how can I have those views copy to server2.database1 any other way that I have not tried?

any help or thought will be much appreciate! Thank you so much for your time!

Increase Varchar in a SQL2005 field and connected erp

I don't know if you can help me in this problem. I've an ERP connected to SQL 2005 DB. I need to increase the characters permitted in the description of my articles... If I increare the varchar(80) that I've in the description column it's enough? Increasing it I can damage my DB or my software?

Find a constraint column name using INFORMATION_SCHEMA.TABLE_CONSTRAINTS in SQL

I have a primary key constraint set up in a table - "tCustomerSessions" with the name "PK_tCustomerSessions".

Below is my query -

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'tCustomerSessions'
    AND Constraint_Type = 'PRIMARY KEY'

Here how do i get the name of the column on which the primary key constraint has been set up ?

check column ISNULL and replace another column

I have column as UserMail like below

U.Email AS UserEmail

I want to replace with RAU.Email if U.Email is isNULL

Error ASP.NET Core EF6 and SQL Server 2005: The version of SQL Server in use does not support datatype 'datetime2'

I receive the following exception when attempting to update a database record even though I was passing in the correct datetime values to the controller.

System.ArgumentException: The version of SQL Server in use does not support datatype 'datetime2'

Other stackoverflow topics suggest either the datetime has not been set or to edit the edmx file, which in this case, doesn't exist. I have tested the application with a localdb context in Visual Studio 2015 but I only receive an error when trying to connect to the SQL Server 2005 DbContext.

Is this exception due to an incompatibility with SQL Server 2005 or is there a workaround which I'm missing?

stored procedure within stored procedure

create  procedure  spGoti

@WeekNumber nvarchar(255)
as
begin


execute spPointsUpdate @WeekNumber

execute spGivebadges @WeekNumber

execute spLevelField @WeekNumber
execute spAddNewWeekDataToTotalOfEmployeeTable @WeekNumber

execute spTop15Overall
execute spWeeklytop15 @WeekNumber

end

in the above code i have written stored procedures within stored procedures. I have a peculiar problem here.the second stored procedure here "spGivebadges @WeekNumber" uses the output of previous stored procedure "spPointsUpdate @WeekNumber" here.

The problem is if i execute the parent stored procedure i.e "spGoti" the second stored procedure "spGivebadges @WeekNumber" doesnot get executed .But when i RUN THE PARENT STORED PROCEDURE i.e "spGoti" THE SECOND TIME THE SECOND STORED PROC GETS EXECUTED WITHOUT ANY PROBLEM.

All the other stored procedures within the parent gets executed the first time itself,though some are using output of another.

wHY IS THIS HAPPENING ? ANYONE FACED SOMETHING SIMILAR BEFORE OR ANY SOLUTION YOU CAN THINK OF ? THANKS

Create View "The name is not a valid identifier" [on hold]

I have a dynamic crosstab which I need to build every month. I thought I could use a view to do this, and have cobbled the following together

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(YrMth) 
                from tbl_CRO_AverageAge where YrMth>=DATEADD(M,-11, DATEADD(DD,day(getdate())+1,GETDATE())) and 
YrMth<=DATEADD(DD,day(getdate())+1,GETDATE()) ORDER BY YrMth
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
    --print @cols

set @query = 'create view [vw_CRO_AverageAge] AS SELECT ' + @cols + ' from 
         (
            select YrMth, sum(AverageAge) over(partition by YrMth) Age FROM
              tbl_CRO_AverageAge
              where YrMth>=DATEADD(M,-11, DATEADD(DD,day(getdate())+1,GETDATE())) and 
YrMth<=DATEADD(DD,day(getdate())+1,GETDATE())
        ) x
        pivot 
        (
            sum(Age)
            for YrMth in (' + @cols + ')
        ) p '

print @query
execute @query

The output of the print can be run fine, but the execute returns the error. I've clearly made some simple error but I can't see it, as I'm not familiar at all with this sort of dynamic code. Is what I'm attempting possible in this manner, or have I gone about it all wrong?

Reward points and create new fields in original table

I have a table here :

I want to reward a gold and a silver(i.e a value of 1 wherever applicable,else 0 )to top 2 persons by looking at pointsRewarded field.

I already have the first table created.In the first table, the two new fields should be created i.e the gold and silver fields.

i want the output to be something like this:

Please help me with the query or give me some suggestions on how to proceed.

I have some query suggested to be:

select t.*,
   (case when rnk = 1 then 1 else 0 end) as gold,
   (case when rnk = 2 then 1 else 0 end) as silver
from (select t.*,
     dense_rank() over (partition by week order by pointsrewarded) as rnk
from t
 ) t;

But I want the new fields i.e rank,gold and silver to be added in the first table.Don't want it as a view.I want the new fields to be hardcoded in the original table.

Please help me with the query or give me some suggestions on how to proceed.

Thanks a lot.

Return duplicates by comparing each records of the same table in the sql server

I have table like below.I wanted to get the duplicate records.Here the condition

I need find duplicate on subscriber whoes status = 1 i.e. active and for current year it has the multiple records by compairing start_date and end_date. I have around more than 5000 records in the DB.Showing here few sample example.

id      pkg_id  start_date  end_date    status  subscriber_id
2857206 9128    8/31/2014   8/31/2015   2       3031103
2857207 9128    12/22/2015  12/22/2016  1       3031103
3066285 10308   8/5/2016    8/4/2018    1       3031103
2857206 9128    8/31/2013   8/31/2015   2       3031104
2857207 9128    10/20/2015  11/22/2016  1       3031104
3066285 10308   7/5/2016    7/4/2018    1       3031104
3066285 10308   8/5/2016    8/4/2018    2       3031105

I tried below's query but not worked for all records:

select * from dbo.consumer_subsc 
where subscriber_id in (3031103) and status=1 and year(getdate()) >= year(start_date) and
year(getdate()) <= year(end_date) and subscriber_id in (select T.subscriber_id from (select subscriber_id,count(subscriber_id) as cnt from dbo.consumer_subsc where
status=1 group by subscriber_id having count(subscriber_id) > 1)T
) order by subscriber_id desc

The problem is I'm not able to find a way, where each row can be compared with each other with above date condition.I should get the result like below as duplicate:

id      pkg_id  start_date  end_date    status  subscriber_id
2857207 9128    12/22/2015  12/22/2016  1       3031103
3066285 10308   8/5/2016    8/4/2018    1       3031103
2857207 9128    10/20/2015  11/22/2016  1       3031104
3066285 10308   7/5/2016    7/4/2018    1       3031104

How to connect remote sql server in my ssms with windows authentication?

I'm new in sql server,i install the sql server in my local network pc and in my pc want to connect to that sql server with windows authentication,for that purpose i click browse for more option and click network server and wait to show that sql server,show that and try to connect but show me this error message:

what happen?i try to find that problem solution in google but i can't understand any solution,thanks.

Retrieving the timezone of a recorded datetime in SQL Server (2005)

Let testdate be a table with a datetime column d. I executed this sql request a few times and changed the OS timezone between each execution.

INSERT INTO [testdate] ([d])
     VALUES (CAST(GETDATE() AS DATETIME))

I got this result :

     ________________________
    | d (datetime)           |
.---|------------------------|
| 1 | 2016-09-08 15:15:28.847|
| 2 | 2016-09-08 18:15:45.407|
| 3 | 2016-09-08 11:17:23.317|
°----------------------------°

The first was played at GMT+6, second at GMT+9, last at GMT+1 (summer time). I believed I'll get all these rows at current timezone.

Are these datetime values stored as a "snapshot" ? Or is there a way to get the timezone for each value ?

DBLink performance issue after upgrading to 11.2.0.4 writing to SQL Server

We have been writing information to a MS Sql Server database (v2005) for a few years now without much difficulty. After upgrading our Oracle db to 11.2.0.4 (from 11.1.0.7) this past weekend we are finding the process extremely slow. For example, to write 100 records used to take 5 seconds and now takes 50. With almost 30,000 records, it's going to be a problem if we can't figure out how to do it better. The destination table is truncated before beginning. Here is the code we used for the 100 record test. Any tips you have to speed this up are appreciated.

BEGIN
  FOR rec IN
  (SELECT * FROM my_orders_v WHERE  item_number like  '52548%'
  )
  LOOP
    INSERT
INTO bill_test_so@sqldatabase
  (
    ORDER_NUMBER ,
    item_number ,
    ITEM_DESCRIPTION ,
    QUANTITY ,
    uom_code,
    ORDER_TYPE_TEXT ,
    NEW_DUE_DATE ,
    ACTION ,
    PLANNER_CODE ,
    ORGANIZATION_ID ,
    INVENTORY_ITEM_ID ,
    LAST_UPDATE_DATE ,
    LAST_UPDATED_BY ,
    CREATED_BY ,
    CREATION_DATE ,
    TRANSACTION_ID
  )
  VALUES
  (
    rec.ORDER_NUMBER ,
    rec.item_number ,
    rec.DESCRIPTION ,
    rec.QUANTITY ,
    rec.uom_code,
    rec.ORDER_TYPE_TEXT ,
    rec.NEW_DUE_DATE ,
    rec.ACTION ,
    rec.PLANNER_CODE ,
    rec.ORGANIZATION_ID ,
    rec.INVENTORY_ITEM_ID ,
    rec.LAST_UPDATE_DATE ,
    rec.LAST_UPDATED_BY ,
    rec.CREATED_BY ,
    rec.CREATION_DATE ,
    rec.TRANSACTION_ID
  );
  END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
  retcode := 2;
  errbuf  := SQLERRM;
  ROLLBACK;
END ;

Thanks for your help! Bill

How to copy stored procedure from one database to another database on same server dynamically

I tried like this:

SELECT @def = [definition] 
FROM [@from_db].sys.sql_modules 
WHERE object_id = OBJECT_ID('MyProcedure') 

EXEC(@def); 

or any other way same like this.

sqlcmd utility not showing greek letters

First of all I am using SQL Server 9.00.4035.00 which is SQL Server 2005 SP3.

I am using the below command to fetch the results from a table which includes columns with either english or greek characters.

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

This command returns rows with english characters ok, but when it comes to greek characters I only get ??????

For example instead of

1110|20160907|ΓΙΩΡΓΟΣ

I am getting

1110|20160907|????????

I already tried the -f and -u options but none of them seems to work. Here is how I used them:

650001 is for UTF-8 according with this link

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -f 65001 -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

1253 is for greek accoring to this book

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -f 1253 -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

-u option

sqlcmd -W -S SERVER_NAME -d DATABASE_NAME -U USER_NAME -P PASSWORD -u -s "|" -Q "set nocount on; set ansi_warnings off;SELECT * from DATABASE.TABLE;" 

Add values of a column and display added result in new table

The table with the data that I have

In the above table I have the columns : weekNumber , weeklyHours , points_Rewarded.

There are four employees : a,b,c,d

I have the values for week1,week2,week3, and so on ( I can have data for many more weeks also such as week4,week5, etc)

I want to write a query such that after passing the query I get the total of the weeklyHours and points_Rewarded for each employee in a new table. The kind of table that the query should give me is here the desired table that I want after passing the query

Please help me with the query.

Thanks in advance.

find consecutive info and reward points accordingly

In the table,I have the columns

: WeekNumber,WeeklyHoursLogged,points_Rewarded

I want to write a query to find out if any employee (see EmployeeName column) has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both i.e consecutively for both week32 and week33).

If the condition is true i.e the employee has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both) then subtract 25 points from the Points_Rewarded column.

Please help me with the query. Refer the below link for table schema.

Thanks in advance.

Table for the above question

sql query to find two consecutive value and reward points accordingly

Table for below question

In the table,I have the columns : WeekNumber,WeeklyHoursLogged,points_Rewarded .I want to write a query to find out if any employee ( see EmployeeName column) has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both i.e consecutively for both week32 and week33).If the condition is true i.e the employee has less than 40 (<40) in WeeklyHoursLogged column for week32 and week33 (both) then subtract 25 points from the Points_Rewarded column. Please help me with the query.table in above link.Thanks in advance.

SQL Server Database Transaction Log File size increased dramatically

After a backup, I noticed size of SQL Server Database Transaction Log File increased dramatically. We were having 10 GB free space, now I could see only 9 MB.

Where should I check to find the root cause of this issue ?

MS SQL Union all & Group by clause not working

I have a table as per below detail:-

CREATE TABLE ITEM_DIM
(DOCKET_NO VARCHAR(9),DOC_DT DATETIME,GL_CODE VARCHAR(10),ITEM_CD VARCHAR(15),
ITEM_DIMENSION VARCHAR(100),LOT_NOS VARCHAR(15),
QTY1 DECIMAL(14,6),QTY2 DECIMAL(14,6),QTY3 DECIMAL(14,6),QTY4 DECIMAL(14,6),QTY5 DECIMAL(14,6),
QTY6 DECIMAL(14,6),QTY7 DECIMAL(14,6),QTY8 DECIMAL(14,6),QTY9 DECIMAL(14,6),QTY10 DECIMAL(14,6)
,QTY11 DECIMAL(14,6),QTY12 DECIMAL(14,6),QTY14 DECIMAL(14,6),QTY15 DECIMAL(14,6),QTY16 DECIMAL(14,6))

inserted statement to insert data into table :-

INSERT INTO ITEM_DIM (DOCKET_NO,DOC_DT,GL_CODE ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8) values
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA', 'AA8/ZZ16', 40.1,40,39.91,39.92,39.93,39.94,39.95,39.96),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA 1', 'AA8/ZZ16', 37.8,37.71,37.72,37.73,37.74,37.75,37.76,37.77),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS ', 'AA8/ZZ16', 0.88,0.72,0.73,0.74,0.75,0.76,0.77,0.77),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS 1', 'AA8/ZZ16', 0.71,0.72,0.73,0.74,0.75,0.76,0.77,0.78)

Select statement to display data :-
SELECT DOCKET_NO,DOC_DT,GL_CODE PARTY_CD,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8 FROM ITEM_DIM

i m trying to get result as per attached image "Desire_Result":-

so i write below query to get desire result:-

SELECT DOC_DT,PARTY_CD,ITEM_CD,LOT_NOS,INNER_DIA,THK FROM
(
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY1 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY2 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY3 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY4 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY5 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY6 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY7 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY8 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY1 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY2 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY3 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY4 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY5 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY6 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY7 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY8 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')) Z 

but my query displaying result as per attached image "My_Qry_Result" instead of desire result.

i also tried joins but not getting desire data So pls help me to get desire result thanxxxx

sql server 2005 srvice pack 3 and 4

I am working in a company with 3000 windows server 2003 with sql server 2005 installed; we have some thing like atm devices that read record (information about buy) from sql server and people purchase mony from that device; idea was that we must install sql server service packs to better performance to read records from sql server; but after install packages all off servers become slower than past and now we can not unistall packages; what we should do? Please help us; thanks:-(

Coldfusion cfthread MSSQL returning multiple records

we are sending almost million email. our process runs in cfthread. MSSQL gives recordset, when we loop through emails, we are updating records as sent.

when we run email sending process every one minute it works fine, when we run every 30 seconds. it start feeding records multiple times.

although we flag records when we pull as pulled but still facing issues. It look like when 1st request is in process, 2nd request comes before updating as pulled flag it serves again same record.

i don't want to lock the table because while, im pulling records, im sending emails and updating records as sent in same table.

please advice me.

Script to run SQL upgrade to 2012 unattended

I need a script to run silent and unattended to upgrade from 2005 to 2012. Backup the SQL databases, remove SQL 2005, Upgrade to 2012 and attach preexisting databases to the newly upgraded 2012. I've noticed that when upgrading to 2012, there are two instances of SqL ( both 2005 and 2012) sharing the same databases, hence creating a lag time in production. This is needed for an upgrade of around 1000 sites. Thank you

Dealing with out of range value on varchar date conversion

I'm attempting to convert dates input in our system as text in the format YYYYMMDD into dates. Unfortunately our system allows the use of the 31st of any month to signify that it's the last day of the month that's important, for some functions like interest accrual etc.

I have a date showing as 20160931 which obviously fails to convert via CONVERT(Datetime, CONVERT(Char(8), [FIELD])) and throws the out-of-range value error.

How can I overcome this, so that I can convert it to the correct value, in this case 30/09/2016.