i have a table with columns
SponsorID AccountID CountryID 123 456 236 123 789 236 123 654 40 123 897 236 123 978 40 123 564 40 ' ' ' ' ' ' ' ' ' i want to write a sql query to find the sponsorID's who have count of accountID
s having countryID = 40 more than 10 [ count(CountryID = 40) > 10 ]
I have table name as table1 has 6 columns. and i am performing 3 type of select operations against this table.
select * from table1 where col1='something' and col2='something'
select * from table1 where col3='something' and col4='something'
select * from table1 where col1='something' and col4='something'
I across many forums that multi column index is better in performance wise. so in order to create multi column index ? in which type i create index for the above scenario ?
create index1 for col1 and col2 create index2 for col3 and col4 create index3 for col1 and col4 like this ? or any idea for optimized index creation to increase performance ?
I stuck with bellow code I don't know where is the problem SELECT s.code,sum(qty) as stock FROM Salelg S INNER JOIN brand D ON S.CODE=D.CODE WHERE D.BRAND='adidas' and s.outlet=’delhi' GROUP BY s.CODE HAVING SUM(s.QTY)>0 . when I execute this query it shows 71 individual records with each one stock total 71 stock. now I changed the code like SELECT sum(s.qty) as stock FROM Salelg S INNER JOIN brand D ON S.CODE=D.CODE WHERE D.BRAND='adidas' and s.outlet=’delhi' but it shows only 69 stock please tell me where is the mistake
I am getting "Changed database context to.." message when USE statement is used.Application is built using Delphi XE7. I need to modify the databases in between without trying to get this message.Other database which i need is tempDB.Is there any SQL statement to pass from code to stop this message from coming?
we are using sql server 2005 and in some situation we found inverse executing order i-e second insert statement executes before first?
I have database with a size of around 100GB in SQL Server 2008. I want to run this database in SQL Server 2005. Can anyone suggest the best way to move that database from SQL Server 2008 to 2005?
I have generated a script using the generate script wizard. The script file is around 25 GB large. So I ran the script from the command prompt using this command:
Sqlcmd -U sa -P **** -S 192.168.10.001\SQL2005
-i D:\dbRestore\Script.sql -a 32767
-o D:\dbRestore\ScriptError.txt
And I got the following errors:
Unclosed quotation mark after the character string
Cannot insert explicit value for identity column in table 'xxx' when IDENTITY_INSERT is set to OFF
Invalid object name 'data_set'
Invalid column name 'benefit_contract_id'
Cannot assign a default value to a local variable.
Must declare the scalar variable
Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml or large user defined type columns.
User or role 'tuser01' does not exist in this database.
Windows NT user or group 'xxx\SQL_QA' not found. Check the name again
Can anyone help me to solve these issues?
While i try to split up the tempdb into .ndf . am getting below error.Please help
Directory lookup for the file "D:\tempdb\tempdev2.mdf" failed with the operating system error 2(The system cannot find the file specified.)
USE Master GO ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\tempdb\tempdev2.ndf' , SIZE = 512MB , FILEGROWTH = 256MB) GO
Explained my query below with the help of a dummy table
Create table #Temp
(
ColA varchar(50)
,ColB Float
,ColC Float
)
Inserting dummy values
Insert into #Temp values('A1',1,1)
Insert into #Temp values('A2',4,NULL)
Insert into #Temp values('A3',5,NULL)
Insert into #Temp values('A4',2,NULL)
The Question here is that i want to fill values for 'A2', 'A3' and 'A4' using the following scenario.
For 'A2': ((ColB of 'A2'+1)*(ColC of 'A1'+1)) -1
i.e ((1+4)*(1+1))-1 = 9
For 'A3':((ColB of 'A3'+1)*(ColC of 'A2'+1)) -1
i.e ((1+5)*(1+9)) -1 =59
and so on for all the rows values are to be updated in the table.
We used ZKAccess in our company. When we installing ZKAccess, programming asking to use default database(mysql) or sql server 2005, we want to use sql server2005, but we can not to connect to the server. ZkAccess and Sql server2005 in one server. Please, help us
I have been receiving the following alerts from this error message as shown below:
Operating system error: 317(The system cannot find message text for message number 0x345
in the message file for 1033.).
Check that sqlevn70.rll is installed in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\Resources\1033, where 1033 is
the language ID of US English, or the appropriate folder for the locale
in use. Also check memory usage.
The root directory of this MSSQL instance is at E:\drive, which I think might be the why it is causing that error.
I have googled and seen what other people have suggested, and mostly is suggesting of uninstalling and re-installing the MSSQL. But this is a production instance, and I am trying to avoid this approach as this might cause a bigger impact to the business.
Hence, I have tried the following steps:
However, it doesn't seems to help and I still received the same error message.
Could anyone advice me on how to resolve this error message?
Thank you!
I am trying to restore a SQL 2014 database in SQL 2005. I am doing by generating the complete script (with schema and data) from SQL 2014.
I get an error saying
'INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.'
and
'Cannot insert explicit value for identity column in table 'T_GASAdminNote' when IDENTITY_INSERT is set to OFF.'
I have searched for solution but have not been able to rectify the problem. I have tried the solution given in this link, but it didnt help me.
Can anyone suggest me some solutions for restoring the database.
I have a table in which I have a column name as total_days, this column contains the number of days the employee has taken leave
What I want is, I want to take the above column in a parameter say @NoOfDaysForDelayApproval.
What I tried is
declare @NoOfDaysForDelayApproval int
BEGIN
select @NoOfDaysForDelayApproval = sum(Total_Days)
from XXACL_EMP_DELAY_APPROVAL_V
where cardno=@EmpCardNo
and DMonth= @Month
and DYear = @Year
END
but I am unable to get the exact value for an employee. Is there any other simple way to get that value in a parameter ?
I am using SQL server 2005
I want to know the value of @NoOfDaysForDelayApproval in a print syntax, but it is not displaying it.
declare @NoOfDaysForDelayApproval int
BEGIN select @NoOfDaysForDelayApproval = sum(Total_Days)
from XXACL_EMP_DELAY_APPROVAL_V
where cardno=@EmpCardNo
and DMonth= @Month
and DYear = @Year
print 'delayed :- ' + convert(varchar(50), @NoOfDaysForDelayApproval)
END
even delayed is not getting print.
I am using SQL-server-2005
If you combine columns of different yet convertible data types, the result of the UNION will produce a column data type equal to the maximum precision of the two columns.
create table tab1 (col1 decimal (5,2)) create table tab2 (col2 int)
insert into tab1 values (11.5) insert into tab2 values(11)
select col1 from tab1 union select col2 from tab2
RESULT:
11.00 11.50
BUT i should get result as
RESULT:
11 11.50
Is there a way to achieve this?
In reality the queries are not this simple.
I Have a query and I'm using a PIVOT which return a table in this way
category date value
cat1 2015-08-01 10
cat1(Previous) 2015-07-01 20
cat2 2015-09-01 15
cat2(Previous) 2015-08-01 25
cat3 2015-10-01 NULL
cat3(Previous) 2015-09-01 58
and my code looks like:
select * from (
select t1.name as name, t1.date as date, t1.value as value from (
select ct.theName as [name],
CONVERT(nvarchar(50), DATENAME(m, buca.date)
+', '
+ DATENAME(yyyy,buca.date))as date
, sum(buca.value) as value from blackU bu
join blackUCat buc on bu.id = buc.blackucat_id
join cat_size ct on ct.id = buc.size_id
join blackU_actual2 buca on buc.id = buca.blackU_Id
where buca.date between cast (@start as VARCHAR(50)) and cast (@actual as VARCHAR(50))
and bu.name = cast (@blackU as VARCHAR(50))
group by buca.date, ct.category_name
)t1
union all
select t2.name as name, t2.date as date, t2.value as value from (
select ct.theName as [name],
CONVERT(nvarchar(50), DATENAME(m, buca.date)
+', '
+ DATENAME(yyyy,buca.date))as date
, sum(buca.value) as value from blackU bu
join blackUCat buc on bu.id = buc.blackucat_id
join cat_size ct on ct.id = buc.size_id
join blackU_actual1 buca on buc.id = buca.blackU_Id
where buca.date between cast (@next as VARCHAR(50)) and cast (@end as VARCHAR(50))
and bu.name = cast (@blackU as VARCHAR(50))
group by buca.date, ct.category_name
)t2
union all
select t3.name as name, t3.date as date, t3.value as value from (
select ct.theName as [name],
CONVERT(nvarchar(50), DATENAME(m, buca.date)
+', '
+ DATENAME(yyyy,buca.date))as date
, sum(buca.value) as value from blackU bu
join blackUCat buc on bu.id = buc.blackucat_id
join cat_size ct on ct.id = buc.size_id
join blackU_actual buca on buc.id = buca.blackU_Id
where buca.date between cast (@start as VARCHAR(50)) and cast (@end as VARCHAR(50))
and bu.name = cast (@blackU as VARCHAR(50))
group by buca.date, ct.category_name
)t3
) as table
All I want is to have a row with name cat1(difference) and to be the difference between first and second row, third and fourth something like this:
category date value
cat1 2015-08-01 10
cat1(Previous) 2015-07-01 20
cat1(difference) 2015-09-01 -10
cat2 2015-09-01 15
cat2(Previous) 2015-08-01 25
cat2(difference) 2015-08-01 -10
cat3 2015-10-01 NULL
cat3(Previous) 2015-09-01 58
cat3(difference) 2015-09-01 -58
My googling powers were not strong enough for this one. This is all theoretical question.
Let's say I have a huge database with hundreds of tables and each table has a user column which references user table.
Now if I would change the user column to have a foreign key constraint, would the increase in database size be noticeable?
I want the exact date as 22/06/2015` from the query
whose Joining date should be exact
22/06/2015
which is exact 6 months back from todays date
I tried like below
Select date_of_joining,* from emp_mst Where Dt_Of_Join >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, getdate())), 0)
but it didn't worked.
what is the exact query for that ?
I am using SQL- server- 2005
while running the below query, I get error as
Incorrect syntax near 'month'.
update p_leave_allocation_14122015
set 'month' + @actualMonth = @actualleavedays
where year = @actualYear and emp_card_no = @emp_card_no
the table p_leave_allocation_14122015 has columns like month1, month2, month3,.....
in my update query what I want is month will be hardcoded and the no of month will be dynamic based on some conditions which I have written.
But when I tried with the above query I got the mentioned error.
I am using SQL server 2005
Kindly suggest
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
To install our application we use a setup created ad hoc, which (together with many other operations) runs some scripts, including a script that contains the MERGE clause. The minimum requirement is to use MS SQL Server 2008 R2.
A customer get an error when trying to execute the script described above, which says:
Error: Incorrect syntax near the keyword 'MERGE'.
I'm trying to figure out why, since he is using a SQL 2008 R2 database. I've discovered that in the same machine there's a SQL 2005 installation. Could this cause a conflict? Can this has something to do with compatibility level? If I try to run the same script via the SQL 2008 R2 SSMS it works.
I have a query which gives me some data. I have 12 months say. but it will update the data for the column say month12 (because current month is december)
there are 12 columns based on each month. here is my query,
In my select query I need to find which month's columns needs to be updated.
Below is my query
select --a.emp_mkey,
@Total_day = Sum(total_day),
@Days_worked = Sum(days_worked)
from emp_mon_day a
where a.emp_mkey = @emp_card_no
and a.month = @actualMonth and Year = @actualYear
group by emp_mkey
if(@Days_worked > 0)
BEGIN
set @actualleavedays =((1.75) / @Total_day) * (@Days_worked)
END
print @actualleavedays
I am using SQL - server 2005
UPDATE
so what month will come here -- month1, month2, ...... ???
-- update p_leave_allocation_14122015 a
-- set a.month1 = 8.18
-- where year = a.actualYear and a.emp_card_no = @emp_card_no
Do let me know if you want to view whole procedure.
DECLARE @Count INT = 4 --Actually this is calculated based on some logic but here i have hard coded it to keep it simple.
SET @Count = @Count + 10 --To accommodate Precision + Scale correctly
DECLARE @Temp DECIMAL( @Count , 10 ) -- I want something like this
I want to create a decimal variable in SQL Server whose Precession is taken from some variable dynamically, I have tried the above snippet but it fails with error 'Incorrect syntax near '@Count'.'
Is there any possible solution to do some thing like that ?
I have my first PHP project and I can connect to a SQL server database on my local SQL Server express but when I attempt to connect to a remote connection it fails. This is the connection that works locally;
$serverName = "IT90334\SQLEXPRESS, 1433";
But I have tried the following connections;
$serverName = "servername\MSSQLInstancename, 40009";
$serverName = ""server.I.P.Address\MSSQLInstancename, 40009";
$serverName = "//server.I.P.Address\MSSQLInstancename, 40009";
$serverName = "//server.I.P.Address\MSSQLInstancename";
$serverName = "server.I.P.Address"
$serverName = "server.I.P.Address:40009"
They output an error to the browser that initially states one of the following;
Provider: No such host is known Error Locating Server/Instance Specified and Timing out Named Pipes Provider: Could not open a connection to SQL Server
Then the next part of the error states;
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections
I can telnet to the server using this command
C:\telnet servername 40009
But it still won't connect. What am I missing here? Below is a section of the PHP and obviosuly I am looking for the "Connection established" to be shown in the browser
$serverName = "servername\MSSQLInstancename, 40009";
$connectionInfo = array('Database' => 'AuditDEV');
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn) {
echo "Connection Established.<br />";
} else {
echo "Something went wrong while connecting to MSSQL.<br />";
die(print_r(sqlsrv_errors(), true));
}
I have my first PHP project and I can connect to a SQL server database on my local SQL Server express but when I attempt to connect to a remote connection it fails. This is the connection that works locally;
$serverName = "IT90334\SQLEXPRESS, 1433";
But I have tried the following connections;
$serverName = "servername\MSSQLInstancename, 40009";
$serverName = ""server.I.P.Address\MSSQLInstancename, 40009";
$serverName = "//server.I.P.Address\MSSQLInstancename, 40009";
$serverName = "//server.I.P.Address\MSSQLInstancename";
$serverName = "server.I.P.Address"
$serverName = "server.I.P.Address:40009"
They output an error to the browser that initially states one of the following;
Provider: No such host is known Error Locating Server/Instance Specified and Timing out Named Pipes Provider: Could not open a connection to SQL Server
Then the next part of the error states;
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections
I can telnet to the server using this command
C:\telnet servername 40009
But it still won't connect. What am I missing here? Below is a section of the PHP and obviosuly I am looking for the "Connection established" to be shown in the browser
$serverName = "servername\MSSQLInstancename, 40009";
$connectionInfo = array('Database' => 'AuditDEV');
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn) {
echo "Connection Established.<br />";
} else {
echo "Something went wrong while connecting to MSSQL.<br />";
die(print_r(sqlsrv_errors(), true));
}
I need to create an output where I got one row per year-month.
Assume the dataset is:
id | dateStart | dateEnd
1 | 2015-01-01 00:00:00.000 | 2015-03-31 00:00:00.000
2 | 2014-07-01 00:00:00.000 | 2014-08-31 00:00:00.000
...
I need the following output:
id | year-month
1 | 2015-01
1 | 2015-02
1 | 2015-03
2 | 2014-07
2 | 2014-08
The output can be any datatype since I can just change that later. That is for 2015-01 the following is ok, "2015-01-01 00:00:00.000", "2015-01-01", "201501", "2015 | jan" ect.
Note I'm using SQL Server 2005.
This question already has an answer here:
SQL: MS SQL Server 2005.
There is tab1 which has integer fields id,f1,f2 .
There is a record (1,1,1) in the tab1.
Can I catch the difference in after update trigger if I run:
1: update tab1 set f1=2, f2=1 where id=1 or 2: update tab1 set f1=2 where id=1 ?
inserted and deleted pseudo-tables in the after update trigger will be the same after the above commands. If it possible, How could I get the list of fields from update command after set key word?
Thank you in advance.
I have a basic query below.
Select [ID] FROM MyTable Where [TextBlobField] like '%test%'
This query takes a long time when I first run it each day. After that it only takes about 5 seconds. Next morning back to long query time. Is this because SQL is caching a index temporary?
How can I make this fast all the time? NOTE: I do not have permission to create my own index on the table. I can create a Stored Procedure... but don't know if that help? Should I create a view?
I have a Stored Procedure which gives me Last date of month
I tried like below
select @actuallastdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@actualMonth),0))
but it is giving me wrong year as, the result is
Dec 31 1899 11:59PM
The SP is as below
ALTER PROCEDURE Monthly_leave_Allocation_2016
@Year int,
@Month int
AS
BEGIN
declare @actualMonth int
declare @actualYear int
declare @actuallastdate datetime
BEGIN
IF (@Month = 1)
BEGIN
set @actualYear = @Year - 1
set @actualMonth = 11
END
ELSE
BEGIN
set @actualMonth = @Month - 1
set @actualYear = @Year
END
select @actuallastdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@actualMonth),0))
END
print @actuallastdate END GO
I guess it may be related to timezone issue as my System date takes the date in dd/mm/yyyy format.
So what's going wrong here. any idea
I am using SQL-server-2005
I am stucked here for one small reason.
I want to display the user some error message based on some condition. and it is below
IF user selects LeaveType as OL then its fine, but if User selects LeaveType as PL it should print Error.
Because, while selecting PL first the @OL_FirstHalf remains 0 and there will no rows gets affected.
The rows will get affected if @OL_FirstHalf = 1
So what I tried is below:-
IF (@OLType = 'OL')
BEGIN
INSERT INTO p_leave_allocation_14122015(Mkey,Entry_Sr_no,Emp_doj, Comp_mkey,FDepartment_Id,FModule_Id,Month,Year, Emp_mkey, Emp_card_no,OL_FirstHalf)SELECT ROW_NUMBER() OVER(ORDER BY lev.MKey) + 1643,ROW_NUMBER() OVER(ORDER BY Entry_Sr_no) + 898, emp.Date_Of_Joining, lev.Comp_mkey,FDepartment_Id, FModule_Id,1,@Year,Emp_mkey,lev.Emp_card_no,1) FROM p_leave_allocation lev LEFT JOIN Emp_mst emp
ON Emp.Emp_card_no = lev.Emp_card_no and lev.Year = (@Year -1)
WHERE emp.Status in ('A', 'S') and
YEAR(emp.Date_Of_Joining) <= @Year
ELSE IF (@OLType = 'PL')
IF(@OL_FirstHalf = 0)
BEGIN
Print 'Error'
END
But it is giving me error as
Must declare the scalar variable "@OL_FirstHalf".
I am using sql-server-2005. Sorry I couldn't format the code.
Please suggest what to do ?
I'm having a hard time solving this, i hope someone can help me out with some tips or advice.
I have a Table in SQL with 3 columns:
Pencil Sales Notebook Sales Pen Sales
1 2 3
9 5 6
7 8 9
I made a query using "Union all" with the sum of each column.
My query looks like this:
select sum(pencilsales) from table1 union all
select sum(notebooksales) from table1 union all
select sum(pensales) from table1
and it gives me the following:
(No Column Name)
17
15
18
But i wanna know if there's a way of sorting this new query by using "desc" or something like that and to add a new column saying which one is each row, like this:
Sales Name
18 Pen Sales
17 Pencil Sales
15 Notebook Sales
Hope you can help me out with ideas and thank you in advance :)
Does MS SQL 2005/2008 has something simular to MySQL Archive table? What we need: no updating, no deleting, no locking, no transaction for specific table(s), only Insert and Select.
I am dealing with a performance issue on a production server that hosts many databases. Two of the databases are dominant on this instance consuming about 90% of the total memory in use. These databases are both read and write intensive which is not ideal but it is what I have inherited.
We are running SQL Server 2005 SP1.
As part of my troubleshooting I have noted that PLE is chronically low anywhere between 0 and 40 seconds pretty much continuously apart from a small 2 hour let up during the night.
One of the troubleshooting steps we took was to increase the maximum memory by 4GB (This instance is part of an active/active FCI so there is more physical memory available to the node). I expected to see SQL Server gobble up the extra memory but instead it grabbed an extra 500MB or so and wouldn't go any higher but the PLE remained low and disk activity remained high along with lazy writes.
We then tried forcing SQL server to take up the extra memory by setting min memory 2GB higher than total memory used but it remained resolute and didn't budge.
I am using sys.dm_os_buffer_descriptors to identify how much data is in the buffer pool.
Has anyone come across this behaviour before and have any suggestions for troubleshooting steps to identify the route cause of the memory behaviour I am seeing?
I am running Windows 7 (32 bit) and have installed Apache 2.2 and PHP 5.3.1 thread safe. I installed these seperately as I previously tried a WAMP bundle and did not like it as I found it problematic. The apache and PHP components run fine. I have the apache port set to 7777 and when I go to the URL http://localhost:7777/phpinfo.php the PHP Info appears. Also I can create a basic PHP Hello World and it can be viewed in the browser. I need to connect to a SQL Server 2005 instance to learn PHP on a project. I have downloaded the SQLSRV30.EXE and extracted the files and placed the DLL's php_pdo_sqlsrv_53_ts.dll and php_sqlsrv_53_ts.dll in the folder C:\PHP\ext then placed the following entries in the php.ini file
extension=php_pdo_sqlsrv_53_ts.dll extension=php_sqlsrv_53_ts.dll
Restarted Apache and in the phpinfo list it does not state any SQL Server attributes. And when I try to run the example below the browser states - Fatal error: Call to undefined function sqlsrv_connect(). I have found several examples on stackoverflow but they concern using wamp. What am I doing wrong here please?
Update: I actually installed NetBeans for PHP and created a new project with the same code below in the index.php page and when I run it the error is the same
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Check SQL Server Connection</title>
</head>
<body>
<?php
$serverName = "IT90334\SQLEXPRESS";
$connectionInfo = array('Database' => 'AdventureWorks');
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn) {
echo "Connection Established.<br />";
} else {
echo "Something went wrong while connecting to MSSQL.<br />";
die(print_r(sqlsrv_errors(), true));
}
?>
</body>
</html>
- Querry.
select Sum(Case when fld_event_subject='Cold Call' THEN 1 ELSE 0 END) AS ColdCall, Sum(Case when fld_event_subject='Referral Call' THEN 1 ELSE 0 END) As ReferalCall, Sum(Case when fld_event_subject='Email Sent & Fax Sent' THEN 1 ELSE 0 END) As [Email Sent & Fax Sent] from tbl_event WHERE (fld_event_created_on >= '12/15/2015 12:00:00 AM' AND fld_event_created_on <= '12/16/2015 3:53:04 AM')
Question:- How to Display Timings Column in {i Will Take From Hour ,to Hour} ? How to Add start Hour(in From Hour Field) As 5:30PM? How To Get End Hour(in to Hour Field) As 6:30PM?
How To increase Hours in Table Automatically
like 5:30PM to 6:30PM 6:30PM to 7:30PM :: :: 4:30AM to 5:30 AM
Querry.
select
Sum(Case when fld_event_subject='Cold Call' THEN 1 ELSE 0 END) AS ColdCall,
Sum(Case when fld_event_subject='Referral Call' THEN 1 ELSE 0 END) As ReferalCall,
Sum(Case when fld_event_subject='Email Sent & Fax Sent' THEN 1 ELSE 0 END) As [Email Sent & Fax Sent]
from tbl_event WHERE (fld_event_created_on >= '12/15/2015 12:00:00 AM' AND fld_event_created_on <= '12/16/2015 3:53:04 AM')
Question:
For example:
5:30PM to 6:30PM
6:30PM to 7:30PM
::
::
4:30AM to 5:30 AM
In my UI, I have two filters:
Date - Today , Yesterday, WTD , MTD
Shift - 1st, 2nd, 3rd
Shift timings are following:
1st: 06:00 - 14:00
2nd: 14:00 - 22:00
3rd: 22:00 - 06:00
I am counting production qualtities for each material via following query:
SELECT
C.MATERIAL
,G.MACHINE
,F.PART
,SUM(A.QT_CONFIRMED) QT_CONFIRMED
FROM
CONFIRMATION A
INNER JOIN LOT_RECORD B ON A.LOT_RECORD_ID=B.LOT_RECORD_ID
INNER JOIN LOT C ON B.LOT_RECORD_ID=C.LOT_RECORD_ID
INNER JOIN MATERIAL D ON C.MATERIAL = D.MATERIAL
INNER JOIN PART F ON C.PART=F.PART
INNER JOIN MACHINE G ON F.MACHINE=G.MACHINE
WHERE
A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]'
GROUP BY
C.MATERIAL
,G.MACHINE
,F.PART
ORDER BY
G.MACHINE ASC
For Today / Yesterday, I can get the date and shift and can pass relevant parameter to the query and getting proper output.
Like if I select Yesterday and Choose shift 2nd then Param.1 = 2015-12-14T14:00:00 Param.2 = 2015-12-14T22:00:00
But problem I'm facing is for WTD and MTD. If I choose MTD and 1st shift then
My Start date will be 1st Dec. End Date will be 15th Dec.
Now I can't pass
Param.1 = 2015-12-01T06:00:00
Param.2 = 2015-12-15T14:00:00
Because it will calculate quantity for all the shifts. I need to pass parameter in such a way that it calulates quantity only between 06:00 - 14:00 for 1st Dec to 15th Dec.
How can I achieve that? I'm really confused and stuck.
Thanks
The SQL Server 2005 table I'm working from is really strange. Here's an example:
key | a | b | c | d | e | f
z 0 1 2 3 4 5 6
y 1 8 9 10 11 12 13
x 14 15 16 17 18 19 20
w 21 22 23 24 25 26 27
What I need to accomplish is to, in a single statement, select the column name from a different table, then get the value here. So it would be something like "Select (select colName from table2 where id=VAR1) From table1 where key = VAR2" So table2 will return either a/b/c/d/e/f, and then the main statement will get the value of the corresponding value based on the key. Table2 will always return a-f, and I will know the VAR1 and VAR2 ahead of time.
I can't figure out why sql 2005 doesn't like it. It is giving me and error that says
Incorrect syntax near the keyword 'where'
on the RN=1 line.
left join (select tSpent, Customerid
from (select SUM(spent) as tSpent, Customerid,
RN = ROW_NUMBER() OVER (PARTITION BY Customerid ORDER BY Customerid DESC)
from Customer_Sales
WHERE Customerid is not null)
where RN = 1) Sales ON Customer_Sales.Customerid = Data.Customerid
After applying innerjoin and I got table as
Size Planet
Big Jupiter
Big Saturn
Big Earth
Small Pluto
Small Venus
now i want this to group such that column planet will have comma separated values in sql.
i.e
Size Planet
Big Jupiter,Saturn,Earth
Small Pluto,Venus
Thanks.
1) How did it get such a convoluted name
2) Is it advisable to change it
3) Why is command prompt prompting me if I want to change it
4) If I choose not to change it, will I continue to get such prompts in the future
5) If I choose to change it, will it affect the operation of stored procedures therein.
The name is of the form tcp:12.34.56.12.3456\nam
An organization wants to shift back from 2008 to 2005: I can't influence this.
What would be the most significant problems based on your experience that i could face?
1) I have noticed that the data type "Date" is going to affect the shift back.
I am inserting value into the table like below
INSERT INTO p_leave_allocation_11122015(Mkey, Entry_Sr_no, Comp_mkey,FDepartment_Id,FModule_Id,Year, Emp_mkey, Emp_card_no, U_Datetime, Delete_Flag,Month12,PL_Days,)
SELECT ROW_NUMBER() OVER(ORDER BY p_leave_allocation.MKey) + 1643,
ROW_NUMBER() OVER(ORDER BY Entry_Sr_no) + 898, Comp_mkey,FDepartment_Id,FModule_Id,
'2016',Emp_mkey,Emp_card_no,U_Datetime,Delete_Flag,Month12,0
FROM p_leave_allocation lev
where Emp_mst Emp left join p_leave_allocation and Emp.status in ('A','S')
and Year(Emp.Date_Of_Joining) = '2015'
but getting error as
An expression of non-boolean type specified in a context where a condition is expected, near 'Emp'.
what is wrong here ?
I have a query like below,
Select max(Mkey) from p_leave_allocation
which gives me max(Mkey) as 1643.
Now I want to insert into another table say p_leave_allocation_11122015 whose mkey should start from 1644
what will be the query then ?
UPDATE
I will be inserting data like this
INSERT INTO p_leave_allocation_11122015(Mkey, Entry_Sr_no, Comp_mkey,FDepartment_Id,FModule_Id,Year, Emp_mkey, Emp_card_no, U_Datetime, Delete_Flag,Month12)SELECT Mkey,Entry_Sr_no, Comp_mkey,FDepartment_Id,FModule_Id,Year,Emp_mkey,Emp_card_no,U_Datetime,Delete_Flag,Month12FROM p_leave_allocation
I am using sql-server-2005
I've got a pretty basic SQL query that's become a bottleneck in my processing. It's selecting a large varchar(999) column that's slowing it down. Removing that column from the select speeds it up considerably so I know it's the column that's causing problem.
I was experimenting with breaking it up into smaller 300 record batches to see if that helped and I saw something weird happening. Some of the batches were taking almost 30 seconds, and some were taking 0.012 seconds. I don't know what's causing this discrepancy.
I have a reproducible scenario where the first query is taking many times faster than the 2nd:
select r.ID, r.FileID, r.Data
from Calls c
join RawData r on r.ID = c.ID
join DataFiles f on f.ID = r.FileID
where r.ID between 1118482415 and 1118509835
0.3 seconds
select r.ID, r.FileID, r.Data
from Calls c
join RawData r on r.ID = c.ID
join DataFiles f on f.ID = r.FileID
where r.ID between 1115330220 and 1118482415
8 seconds
I see no visible differences in the returned data. They both return 300 records and all of the record's "Data" column values are about 170 characters long. I'm running this directly from the SqlStudio client. Also there's no other traffic in this database.
Does anybody know what could be causing this problem or have any suggestions to try? I can't decrease the size of the column because there are some bigger records in there, just not in this example. I do have indexes on all the columns used in the joins (Calls.ID, RawData.ID, RawData.FileID, DataFiles.ID).
I have a stored procedure that is querying 4 tables with Huge amount of data. Executing the Stored proc with input filter parameters takes about 4 - 5 seconds to run in SQL Management studio and pull all the data required.
This stored procedure is used in a SSRS report which is deployed in a Reporting server. When run in the SSRS server the report takes about 2-3 mins to render and stops responding most of the time. Same filters as the ones used in SQL Management studio are applied on the report.
What are all the possible reasons this delay is occurring in SSRS but not in SQL Server?
NOTE: Due to client policies i am not allowed to share any code or additional details. Apologies for that
The goal is to print an ad hoc report without the aid of any supporting software. The only software we have is the application, which uses MS SQL 2005 and Crystal Reports technology. However, neither of these supporting applications are installed on the computer and the computer does not have memory resources available for them. Even Microsoft Office is not installed. A final point is that I have limited opportunities for failure before I lose authorization to try this sort of thing.
I found some documentation which sounds like it may work:
However, I'm concerned it will only let me print one query at a time, whereas I would like for a decently formatted report, with aggregate data displayed below certain columns.
Anyone with experience in this situation is welcome to reply.
I'm trying to figure out when a table has been ammended. Using the 2 different sets of code below, i get 2 different answers. Please see below.
Select * from sys.tables where name =
'AN_StockChecks_000_Specs_010_StockChecks_010_PlantStockCheckSettings'
modify_date
2015-12-07 15:40:58.557
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'IESA_DWHS')
AND OBJECT_ID=OBJECT_ID('AN_StockChecks_000_Specs_010_StockChecks_010_PlantStockCheckSettings')
last_user_update
2015-12-10 09:25:43.290
SQL 2005
I can access the linked server with
SELECT [id] FROM [AFSDB1].[EWMData].[dbo].[eventid]
But when I try to access another database with
SELECT [id] FROM [AFSDB1].[ASPSecurity2].[dbo].[test]
I get - Invalid object name 'ASPSecurity2.dbo.test'.
The databases/schemas and tables exist, the above queries were even generated by SQL Management Studio while browsing the linked server so there are no typos and I've tried with many tables, all [EWMData] tables are accessible and all [ASPSecurity2] tables aren't.
There seems to be something special about [EWMData] that allows the remote server to access it. I suspect it's a permissions issue but I can't find any differences between the databases. I can browse these databases through the linked server entry in SMS and both databases in the remote server have login 'sa' mapped to user 'dbo'.
Both databases are using the same collation - Latin1_General_CI_AS
I Have 2 Table in SQL SERVER 2005:
first table :
Tanggal
2015/01/01
2015/01/02
2015/01/03
2015/01/04
2015/01/05
2015/01/06
Second Table :
Tanggal Jam_Masuk Jam_Pulang. Id_user
2015/01/01 08:00:00 17:00:00 00600320
2015/01/03 08:00:00 17:00:00 00600320
2015/01/05 08:00:00 17:00:00 00600320
2015/01/06 08:00:00 17:00:00 00600320
I want select like this :
Tanggal Jam_Masuk Jam_Pulang Total Status Id_user
2015/01/01 08:00:00 17:00:00 09:00:00 OK 00600320
2015/01/02 --:--:-- --:--:-- --:--:-- ALPHA/IZIN 00600320
2015/01/03 08:00:00 17:00:00 09:00:00 OK 00600320
2015/01/04 --:--:-- --:-- :-- --:--:-- ALPHA/IZIN 00600320
2015/01/05 08:00:00 17:00:00 09:00:00 OK 00600320
2015/01/06 08:00:00 17:00:00 09:00:00 OK 00600320
Note : Field Total (hh:mm:ss)= Jam_Pulang - Jam_Masuk
Thanks Before
I am adding a date as "09/10/2014" into the textbox and clicking the submit button but getting error as:-
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value error.
Below is my query generated while debugging:-
select * from WMS_BIN_STATUS_TRACK where 1!=1 or Current_Item_Exp_Dt = convert(datetime, '09/10/2014', 103)
and below is the full code:-
protected void btnTrack_OnClick(Object sender, EventArgs e)
{
string whereClause = "1!=1";
if (ddlBin.SelectedValue != "0")
{
whereClause = whereClause + "or location_name='" + ddlBin.SelectedValue + "'";
}
if (ddlItem.SelectedValue != "0")
{
whereClause = whereClause + "or Current_Item_code='" + ddlItem.SelectedValue + "'";
}
if (txtBatch.Text != "")
{
whereClause = whereClause
+ " or Current_Item_Batch " + (ddlmathsign.SelectedValue == "Equal" ? (" = '" + txtBatch.Text + "'") : (" like '%" + txtBatch.Text + "%'"));
}
if (txtExpCal.Value != "")
{
whereClause = whereClause + "or Current_Item_Exp_Dt " + (ddlAssignvalue.SelectedValue == "Greater than" ? ">" : (ddlAssignvalue.SelectedValue == "Less than" ? "<" :
(ddlAssignvalue.SelectedValue == "Equal to" ? "=" : (ddlAssignvalue.SelectedValue == "Greater than equal to" ? ">=" : "<=")))) + "convert(datetime, '" + txtExpCal.Value + "', 103)";
}
if (ddlBin.SelectedValue == "0" && ddlItem.SelectedValue == "0" && txtBatch.Text == "" && txtExpCal.Value == "")
{
BindGrid();
}
else
{
string query = "select * from WMS_BIN_STATUS_TRACK where " + whereClause;
SqlDataAdapter da = new SqlDataAdapter(query, strConnString);
DataTable dt = new DataTable();
da.Fill(dt);
GrdBinStockTracker.DataSource = dt;
GrdBinStockTracker.DataBind();
}
}
NOTE As I am working on local condition only to test, SQL Injection is not a matter of concern here.
Also, Is this a issue related to different datetime. ?
I'm building hourly report(last 8 hour production count) from SQL Table CONFIRMATION via SQL Query. Query absolutely runs fine and gives proper results as follow:
SELECT
(DATENAME(hour, C.DT_CONFIRMED) + ' - ' + DATENAME(hour, DATEADD(hour, 1, C.DT_CONFIRMED))) as PERIOD,
SUM(C.QT_CONFIRMED) as QT_CONFIRMED
FROM
CONFIRMATION C
WHERE C.DT_CONFIRMED >= DATEADD(hh, -8 , '2015-12-03T11:00:00')
GROUP BY (DATENAME(hour, C.DT_CONFIRMED) + ' - ' +
DATENAME(hour, DATEADD(hour, 1, C.DT_CONFIRMED)))
ORDER BY PERIOD
I get following result:
Period QT_CONFIRMED
8 - 9 4
10 - 11 8
But instead of that, i want result in following forma:
Period QT_CONFIRMED
2 - 3 0
3 - 4 0
4 - 5 0
5 - 6 0
7 - 8 0
8 - 9 4
9- 10 0
10 - 11 8
Basically for all those hours where QT_CONFIRMED is zero, I want to show that in the report.
How can I achieve that?
CONFIRMATION Table looks like following:
DT_CONFIRMED QT_CONFIRMED ID_CONFIRMATION
2015-12-03T10:40:43 5 1
2015-12-03T10:48:33 3 2
2015-12-03T11:03:03 12 3
Thanks
I have a dynamic sql.
declare @CustomerId int
set @CustomerId = 1
declare @SDate datetime
set @SDate = '2015/12/07'
declare @ItemId int
set @ItemId = 2
declare @QtyS nvarchar(max)
declare @QtyOut decimal(18,3)
set @QtyS = 'SELECT isnull(sum(d.Qty),0)
FROM InvoiceDetail AS d INNER JOIN
InvoiceHeader AS h ON d.InvoiceNo = h.InvoiceNo
where
h.CustomerId = '''+@CustomerId+''' and
d.itemmasterid = '''+@ItemId+''' and
h.Deleted = 0 and
h.invoicedate = '''+@SDate+''''
exec sp_executesql @QtyS, N'@Qty decimal(18,3) out', @QtyOut out
select @QtyOut
when executing this attached error coming. Error as a text also attached with this. Any idea?
Error:
Msg 245, Level 16, State 1, Procedure rpt_SpecialLaunch, Line 76
Conversion failed when converting the varchar value 'SELECT isnull(sum(d.Qty),0)
FROM InvoiceDetail AS d INNER JOIN
InvoiceHeader AS h ON d.InvoiceNo = h.InvoiceNo
where
h.CustomerId = '' to data type int.
I have a query below
SELECT DISTINCT a.mkey, 0 child_menu_mkey, UPPER(a.menu_name) parent_menu, '' child_menu
FROM wms_menu_hdr a,
wms_menu_hdr b
WHERE a.mkey = b.parent_mkey
UNION
SELECT DISTINCT b.mkey, b.mkey child_menu_mkey, '' parent_menu, b.menu_name child_menu
FROM wms_menu_hdr a,
wms_menu_hdr b
WHERE a.mkey = b.parent_mkey
ORDER BY 1,2
which displays me results as
mkey child_menu_mkey parent_menu child_menu
1 0 ADMIN
2 0 PUT AWAY PROCESS
3 0 PICK UP PROCESS
4 0 STORAGE PROCESS
5 0 DAMAGED STOCK
5 5 Damaged Stock
6 0 MASTERS
6 6 Masters
8 8 Put Away Details
9 9 Put Away Allocation Bin
10 10 Put Away Confirmation
11 11 Put Away Report
12 12 Pick Up Details
13 13 Pick Up Find Storage Bin
14 14 Pick Up Confirmation
How to delete duplicate mkey now ?
I am using SQL-server-2005
UPDATE
I want the result to be displayed as like below,
mkey child_menu_mkey parent_menu child_menu
1 0 ADMIN
1 6 Masters
2 0 PUT AWAY PROCESS
2 8 Put Away Details
2 9 Put Away Allocation Bin
2 10 Put Away Confirmation
2 11 Put Away Report
I have a query which fills my gridview, but what happening here is my
mkeyis getting repeated.
I dont know why
Below is my query,
SELECT a.mkey, 0 child_menu_mkey, UPPER (a.menu_name) parent_menu, '' child_menu "+
"FROM wms_menu_hdr a, wms_menu_hdr b " +
"WHERE a.mkey = b.parent_mkey "+
"UNION "+
"SELECT DISTINCT b.mkey, b.mkey child_menu_mkey, '' parent_menu, b.menu_name child_menu "+
"FROM wms_menu_hdr a, wms_menu_hdr b "+
"WHERE a.mkey = b.parent_mkey "+
"ORDER BY 1, 2
and here is what it looks like:-
Also see the table structure of my table wms_menu_hdr
Mkey numeric
Sr_No numeric
MENU_NAME nvarchar
PARENT_MKEY numeric
URL nvarchar
ORDER_BY numeric
DELETE_FLAG char
CREATION_DATE datetime
CREATED_BY numeric
LAST_UPDATED_BY numeric
LAST_UPDATE_LOGIN numeric
LAST_UPDATE_DATE datetime
I am using SQL server 2005. I dont know why what is wrong here
How can I link two server using query in SQL? I want to remotely query the table of this Server (BL4/SQLEXPRESS) to my Server (BLSVR11). Can you show me how to do it in SQL? This is how I did it but it giving me an error..
EXEC master.dbo.sp_addlinkedserver
@server = 'BL4\SQLEXPRESS',
@srvproduct = '',
@datasrc = '192.168.1.1',
@provider = 'SQLNCLI10',
@provstr = ''
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'BL4\SQLEXPRESS',
@useself = 'false',
@rmtuser = 'ID',
@rmtpassword = 'passwd'
SELECT * FROM [BL4].[dbo].[T_Order]
I am trying to check if a persons name appears multiple times in a table aswell as pull additional information (position title, department name, employee number) to limit creating two stored procedures 1. to check if name appears multiple times and 2. to get the additional information after check has been completed.
my current query right now is as follows, which returns multiple rows as the ID I am using to join the tables appears multiple times in one table (an employee can belong to multiple departments, the EmpID occurs here multiple times thyus returning the multiple values).
SELECT c.FirstName+ ' ' + c.LastName as emp_full_name, e.EmployeeNumber,
e.EmpID, dh.PositionTitle, d.Name as deptName, e.isActive
FROM Person.Contact c
INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeDepartmentHistory dh ON e.EmpID = dh.EmpID
INNER JOIN HumanResources.Department d ON dh.DepartmentID = d.DepartmentID
WHERE c.FirstName+ ' ' + c.LastName LIKE @empName
My general table structure is as follows:
ContactID
FirstName
LastName
EmpID
EmployeeNumber
isActive
DepartmentHistoryId
EmpID
DepartmentID
PositionTitle
DepartmentID
Name
I set the CommandTimeout property of my SQLCommand to 3 but it's still taking at least 10 seconds for it to throw an exception.
cmd = New SqlCommand("query", con)
cmd.CommandTimeout = 3
cmd.ExecuteNonQuery()
how to Date Wise Filter in sql server?
Now i am ItemID Wise data can be Properly show. but my need IssueDate Wise data show.
ItemID Wise Query is - WITH Last3Month AS (SELECT DISTINCT TOP 3 MONTH(IssueDate) AS Mth FROM Issue WHERE ItemId = 452 ORDER BY Mth DESC) SELECT CONVERT(CHAR, DATENAME(MONTH, IssueDate)) AS MonthName, ItemId,COUNT(CONVERT(varchar, IssueDate, 6)) AS WorkingDays FROM dbo.Issue AS Issue INNER JOIN Last3Month ON MONTH(Issue.IssueDate) = Last3Month.Mth GROUP BY CONVERT(CHAR, DATENAME(MONTH, IssueDate)), ItemId, Last3Month.Mth HAVING (ItemId = 452) ORDER BY Last3Month.Mth
ItemID Wise Result is
IssueDate Wise Query is - WITH Last3Month AS (SELECT DISTINCT TOP (3) MONTH(IssueDate) AS Mth FROM dbo.Issue AS Issue_1 WHERE(IssueDate BETWEEN '01-01-14' AND '01-01-17') ORDER BY Mth DESC) SELECT CONVERT(CHAR, DATENAME(MONTH, Issue.IssueDate)) AS MonthName, Issue.ItemId, COUNT(CONVERT(varchar, Issue.IssueDate, 6)) AS WorkingDays, SUM(Issue.IssueQuantity) AS IssueQty FROM dbo.Issue AS Issue INNER JOIN Last3Month AS Last3Month_1 ON MONTH(Issue.IssueDate) = Last3Month_1.Mth WHERE (Issue.IssueDate BETWEEN '01-01-14' AND '01-01-17') GROUP BY CONVERT(CHAR, DATENAME(MONTH, Issue.IssueDate)), Issue.ItemId, Last3Month_1.Mth HAVING (Issue.ItemId = 452) ORDER BY Last3Month_1.Mth
IssueDate Wise Result is
plz help me.
This question already has an answer here:
every body tale me "Difference between stored procedure and function?"
i have two table created scripts that
create table #t1 ( gr char(10), muh int, mes int, less_id int )
insert #t1 values('a1',4,2,2) insert #t1 values('a1',2,2,1) insert #t1 values('a1',2,1,6) insert #t1 values('a1',3,1,5) insert #t1 values('a1',3,2,3) insert #t1 values('a1',2,2,4)
create table #t2 (gr char(10),d_id int,start_time time,end_time time,less_id int,last_week_less int)
insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'12:30','14:05') insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'14:15','15:50') insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'16:00','17:35') insert into #t2 (gr,d_id,start_time,end_time) values('a1',1,'17:45','19:20')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'12:30','14:05') insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'14:15','15:50') insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'16:00','17:35') insert into #t2 (gr,d_id,start_time,end_time) values('a1',2,'17:45','19:20')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'12:30','14:05') insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'14:15','15:50') insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'16:00','17:35') insert into #t2 (gr,d_id,start_time,end_time) values('a1',3,'17:45','19:20')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'12:30','14:05') insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'14:15','15:50') insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'16:00','17:35') insert into #t2 (gr,d_id,start_time,end_time) values('a1',4,'17:45','19:20')
insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'12:30','14:05') insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'14:15','15:50') insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'16:00','17:35') insert into #t2 (gr,d_id,start_time,end_time) values('a1',5,'17:45','19:20')
select *from #t1
gr muh mes less_id a1 4 2 2 a1 2 2 1 a1
2 1 6 a1 3 1 5 a1 3 2 3 a1 2 2 4select* from #t2
gr d_id start_time end_time
less_id last_week_lessa1 1 12:30:00.0000000 14:05:00.0000000 NULL NULL a1
1 14:15:00.0000000 15:50:00.0000000 NULL NULL a1
1 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
1 17:45:00.0000000 19:20:00.0000000 NULL NULL a1
2 12:30:00.0000000 14:05:00.0000000 NULL NULL a1
2 14:15:00.0000000 15:50:00.0000000 NULL NULL a1
2 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
2 17:45:00.0000000 19:20:00.0000000 NULL NULL a1
3 12:30:00.0000000 14:05:00.0000000 NULL NULL a1
3 14:15:00.0000000 15:50:00.0000000 NULL NULL a1
3 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
3 17:45:00.0000000 19:20:00.0000000 NULL NULL a1
4 12:30:00.0000000 14:05:00.0000000 NULL NULL a1
4 14:15:00.0000000 15:50:00.0000000 NULL NULL a1
4 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
4 17:45:00.0000000 19:20:00.0000000 NULL NULL a1
5 12:30:00.0000000 14:05:00.0000000 NULL NULL a1
5 14:15:00.0000000 15:50:00.0000000 NULL NULL a1
5 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
5 17:45:00.0000000 19:20:00.0000000 NULL NULL
and i need update table #t2 columns less_id and last_week_less
for table#t1 where #t2 gr= #t1 gr
but hier need interesting rules
1) when muh colum from #t1 = 4
then we need update 2 column in #table
and need different in the #t1 table d_id column
for example after output result
need give
gr d_id start_time end_time less_id last_week_less a1 1 12:30:00.0000000 14:05:00.0000000 2
NULLa1 2 12:30:00.0000000 14:05:00.0000000 2 NULL
hier our count update=2 (4/2)
and d_id need diffrent
and if will muh column =3 (3/2)
then we need update 1 column and 1 in the last_week_less update
after ouput need give
gr d_id start_time end_time less_id last_week_less a1 1 14:15:00.0000000 15:50:00.0000000 5
5
because 3/2 we give 1 and 1
and after this update we need update for column mes column too
when the column is null
and simple fragment for result we need give that results
for example
and in 1 section d_id dont more 3 less_id for example this is update for muh column #t1 table after output
gr d_id start_time end_time less_id last_week_less
a1 1 12:30:00.0000000 14:05:00.0000000 2 NULL a1
1 14:15:00.0000000 15:50:00.0000000 1 null a1
1 16:00:00.0000000 17:35:00.0000000 null NULL a1
1 17:45:00.0000000 19:20:00.0000000 null null a1
2 12:30:00.0000000 14:05:00.0000000 2 NULL a1
2 14:15:00.0000000 15:50:00.0000000 null null a1
2 16:00:00.0000000 17:35:00.0000000 null NULL a1
2 17:45:00.0000000 19:20:00.0000000 NULL NULL a1
3 12:30:00.0000000 14:05:00.0000000 5 5 a1
3 14:15:00.0000000 15:50:00.0000000 6 NULL a1
3 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
3 17:45:00.0000000 19:20:00.0000000 NULL NULL a1
4 12:30:00.0000000 14:05:00.0000000 3 3 a1
4 14:15:00.0000000 15:50:00.0000000 NULL NULL a1
4 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
4 17:45:00.0000000 19:20:00.0000000 NULL NULL a1
5 12:30:00.0000000 14:05:00.0000000 4 NULL a1
5 14:15:00.0000000 15:50:00.0000000 NULL NULL a1
5 16:00:00.0000000 17:35:00.0000000 NULL NULL a1
5 17:45:00.0000000 19:20:00.0000000 NULL NULL
thanks for helping
I am inserting checkbox (4-5 checkboxes) values into the database table which is inside Gridview.
So what happening here is,
There 4 columns and 20 rows. and I am checking each checkbox from each column and 19 rows are unchecked. So i am getting error message as
Column name 'ADD_FLAG' appears more than once in the result column list. Msg 264, Level 16, State 1, Line 1
Column name 'MODIFY_FLAG' appears more than once in the result column list. Msg 264, Level 16, State 1, Line 1
Column name 'VIEW_FLAG' appears more than once in the result column list. Msg 264, Level 16, State 1, Line 1
Column name 'DEL_FLAG' appears more than once in the result column list.
I dont know why this is happening, may be due to placing of code for that columns. Here is my code:-
public bool Save()
{
try
{
for (int i = 0; i < GrdRights.RowsInViewState.Count; i++)
{
string strSQLMKEY = "SELECT WMS_User_Rights.MKEY FROM WMS_User_Rights Inner JOIN WMS_Menu_Rights on " +
"WMS_User_Rights.User_Id = WMS_Menu_Rights.Mkey " +
"where WMS_User_Rights.User_Id='" + Hid_Selected_user.Value + "' " +
"AND WMS_User_Rights.DELETE_FLAG = 'N'";
if (GrdRights.Rows[i].Cells[GrdRights.Columns.GetColumnIndexByDataField("child_menu_mkey")].Text.Trim().ToString() == "0")
{
strSQLMKEY += " and MENU_MKEY='" + GrdRights.Rows[i].Cells[GrdRights.Columns.GetColumnIndexByDataField("MKEY")].Text.Trim() + "'";
}
else
{
strSQLMKEY += " and MENU_MKEY='" + GrdRights.Rows[i].Cells[GrdRights.Columns.GetColumnIndexByDataField("child_menu_mkey")].Text.Trim() + "'";
}
con.Open();
SqlCommand cmdMKEY = new SqlCommand(strSQLMKEY, con);
if (cmdMKEY.ExecuteScalar() != null)
{
strMode = "M";
iMKey = Convert.ToInt32(cmdMKEY.ExecuteScalar());
}
else
{
strMode = "A";
iMKey = 0;
}
con.Close();
StringBuilder StrPubBldg = new StringBuilder();
XmlWriter xw = XmlWriter.Create(StrPubBldg);
xw.WriteStartElement("DocumentElement");
{
xw.WriteStartElement("WMS_Menu_Rights");
if (GrdRights.Rows[i].Cells[GrdRights.Columns.GetColumnIndexByDataField("child_menu_mkey")].Text.Trim().ToString() == "0")
{
xw.WriteElementString("MENU_MKEY", GrdRights.Rows[i].Cells[GrdRights.Columns.GetColumnIndexByDataField("MKEY")].Text.Trim());
}
else
{
xw.WriteElementString("MENU_MKEY", GrdRights.Rows[i].Cells[GrdRights.Columns.GetColumnIndexByDataField("child_menu_mkey")].Text.Trim());
}
xw.WriteElementString("USER_ID", Hid_Selected_user.Value);
xw.WriteElementString("DELETE_FLAG", "N");
xw.WriteElementString("CREATION_DATE", System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss"));
for (int j = 0; j < GrdRights.RowsInViewState.Count; j++)
{
bool str_checkadd = ((CheckBox)((GridDataControlFieldCell)GrdRights.RowsInViewState[j].Cells[4]).FindControl("ChkIDAdd")).Checked;
bool str_checkEdit = ((CheckBox)((GridDataControlFieldCell)GrdRights.RowsInViewState[j].Cells[5]).FindControl("ChkIDEdit")).Checked;
bool str_checkView = ((CheckBox)((GridDataControlFieldCell)GrdRights.RowsInViewState[j].Cells[6]).FindControl("ChkIDView")).Checked;
bool str_checkdel = ((CheckBox)((GridDataControlFieldCell)GrdRights.RowsInViewState[j].Cells[7]).FindControl("ChkIDDelete")).Checked;
xw.WriteElementString("ADD_FLAG", str_checkadd == true ? "Y" : "N"); //
xw.WriteElementString("MODIFY_FLAG", str_checkEdit == true ? "Y" : "N");
xw.WriteElementString("VIEW_FLAG", str_checkView == true ? "Y" : "N");
xw.WriteElementString("DEL_FLAG", str_checkdel == true ? "Y" : "N");
}
}
xw.WriteEndElement();
xw.Close();
MainEnqMkey = InsertUpdateDelete.InsertUpdateDeleteCls.InsertUpdateDelete_sql(strMode, Convert.ToInt16(iMKey), "WMS_Menu_Rights", "MKEY", "MUR", StrPubBldg.ToString());
}
if (MainEnqMkey.Equals(0))
{
ClientScript.RegisterStartupScript(this.GetType(), "SuccessScript", "alert('Some Error Occured While Saving Data !!')", true);
}
else
{
con.Open();
SqlCommand ObjPriCmd = new SqlCommand("delete from WMS_User_rights where MKEY=" + HidTempMkey.Value, con);
ObjPriCmd.ExecuteNonQuery();
con.Close();
if (!Directory.Exists(Server.MapPath(StrFolder)))
{
Directory.CreateDirectory(Server.MapPath(StrFolder));
}
if (File.Exists(Server.MapPath(StrFolder + StrFileName)) == false)
{
using (System.IO.StreamWriter sw = File.CreateText(Server.MapPath(StrFolder + StrFileName)))
{
sw.WriteLine("\n");
//sw.Write("CEF No. : " + TxtCefNo.Value.ToString().Trim() + " Followup Sr No :" + TxtSrNo.Value.ToString().Trim());
//sw.WriteLine("\n"); sw.WriteLine("\n");
//sw.Write("Mode : " + strMode + " & Xml : " + StrPubBldg.ToString());
sw.WriteLine("\n"); sw.NewLine = "\n------------------------------------------------------------------";
sw.WriteLine("\n");
sw.Close();
sw.Dispose();
}
}
else
{
using (System.IO.StreamWriter sw = File.AppendText(Server.MapPath(StrFolder + StrFileName)))
{
sw.WriteLine("\n");
//sw.Write("CEF No. : " + TxtCefNo.Value.ToString().Trim() + " Followup Sr No :" + TxtSrNo.Value.ToString().Trim());
//sw.WriteLine("\n");
//sw.Write("Mode : " + strMode + " & Xml : " + StrPubBldg.ToString());
sw.WriteLine("\n"); sw.NewLine = "------------------------------------------------------------------";
sw.WriteLine("\n");
sw.Close();
sw.Dispose();
}
}
}
return true;
}
catch (Exception ex)
{
if (!Directory.Exists(Server.MapPath(StrFolder)))
{
Directory.CreateDirectory(Server.MapPath(StrFolder));
}
if (File.Exists(Server.MapPath(StrFolder + StrFileName)) == false)
{
using (System.IO.StreamWriter sw = File.CreateText(Server.MapPath(StrFolder + StrFileName)))
{
sw.WriteLine("\n");
//sw.Write("CEF No. : " + TxtCefNo.Value.ToString().Trim() + " Followup Sr No :" + TxtSrNo.Value.ToString().Trim());
//sw.WriteLine("\n"); sw.WriteLine("\n");
//sw.Write("Mode : " + strMode + " & Xml : " + StrPubBldg.ToString());
sw.WriteLine("\n"); sw.NewLine = "\n------------------------------------------------------------------";
sw.WriteLine("\n");
sw.Close();
sw.Dispose();
}
}
else
{
using (System.IO.StreamWriter sw = File.AppendText(Server.MapPath(StrFolder + StrFileName)))
{
sw.WriteLine("\n");
//sw.Write("CEF No. : " + TxtCefNo.Value.ToString().Trim() + " Followup Sr No :" + TxtSrNo.Value.ToString().Trim());
//sw.WriteLine("\n");
//sw.Write("Mode : " + strMode + " & Xml : " + StrPubBldg.ToString());
sw.WriteLine("\n"); sw.NewLine = "------------------------------------------------------------------";
sw.WriteLine("\n");
sw.Close();
sw.Dispose();
}
}
return false;
}
finally
{
}
}
protected void CmdSave_Click(object sender, EventArgs e)
{
if (Save() == true)
{
fillGrid();
if (strMode == "M")
{
ClientScript.RegisterStartupScript(this.GetType(), "CloseScript", "alert('Record Modified Successfully');window.location.href='Frm_User_Rights.aspx?TranType=MUR&Mode=A&Key=0&PView=N&userid=" + Request.QueryString["userid"].ToString() + "';", true);
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "CloseScript", "alert('Record Saved Successfully');window.location.href='Frm_User_Rights.aspx?TranType=MUR&Mode=A&Key=0&PView=N&userid=" + Request.QueryString["userid"].ToString() + "';", true);
}
}
}
Also for database i am using SQL -server- 2005
Is there a tool or someone does have a query that can for provided table make a query that will have (left) joins on all referenced tables and subsequently all other tables that are referenced by those etc... to the n-th lvl.
Thanks
There are multiple DB relationship diagram found during product support. However, I searched on internet, none of the database diagrams are seems irrelevant and Im also seeing this diagram for 1st time. Could any one has come across such DB designed diagram. Please clarify me.
My requirement is to display the table name once followed by all column names of that table. I am not able to do that. And I wanted it to be done with query itself not with the stored procedure. Always, it repeats the table names with the column names. I want table name(Entity) to be displayed once and all the column names(attributes) to be displayed under that. Then next table name(Entity) followed by its column names(attributes) and so on.
Thank you in advance.
can anyone help to find the issue in the query. I am using sql server 2005.
This query is working fine:
SELECT DecryptByPassPhrase('SHA1', ColName) FROM Table
but when i use:
SELECT isnull(convert(varchar(200) DecryptByPassPhrase('SHA1', ColName)), '')
FROM Table
it gives error:
Incorrect syntax near 'DecryptByPassPhrase'. Severity 15
i also tried where clause WHERE colName IS NOT NULL expecting it is because of null values but same error.
I have two tables tblA and tblB. And a constraint called tblA_tblB_FK is created between these tables. I wanted to update both columns in tables chained with tblA_tblB_FK constraint. While reading different posts I thought the best way is to disable the constraint for a moment and enable again after the update. For that reason I executed these queries:
alter table tblA NOCHECK CONSTRAINT tblA_tblB_FK
After this step I did the update and till now everything was OK, but then I tried to enable again the constraint, so I executed this query:
ALTER TABLE tblA CHECK CONSTRAINT tblA_tblB_FK
and it says command successfully completed. But when I try to make update again it doesn't stop me from doing that, meaning there is a problem with the enabling process. I tried to execute another query:
ALTER TABLE tblA WITH CHECK CHECK CONSTRAINT tblA_tblB_FK
and it doesn't allow me complaining there is tblA_tblB_Fk constraint active. I don't understand why it allows me to make an update, while it doesn't allow me to execute this command?
I am using SQL Server 2005. Thanks in advance for any suggestions!
While debugging through the code, I am getting error as
Invalid column name Menu_mkey
Here is the code for that
string strsqlflag = "select count(*) from WMS_User_Rights where User_Id='" + Hid_Selected_user.Value + "' and delete_flag='N' ";
if (Dt.Rows[e.Row.RowIndex]["child_menu_mkey"].ToString() == "0")
{
strsqlflag += " and MENU_MKEY = '" + Dt.Rows[e.Row.RowIndex]["MKEY"] + "'";
}
else
{
strsqlflag += " and MENU_MKEY = '" + Dt.Rows[e.Row.RowIndex]["child_menu_mkey"] + "'";
}
which generated the below query
select count(*) from WMS_User_Rights where User_Id='1' and delete_flag='N' and MENU_MKEY = '1'
Now, here I want Menu_mkey too, but the issue is that it is coming from another table which is from table WMS_menu_rights
How to achieve this ?
on a side note:- I am using SQL-server 2005
SQL STATEMENT HOW TO SEPARATE RESULT WITH ' ; '.
There are two tables - Entity table and Account table. Entity table having EntityID and Account table having AccountID. There is a 3rd table EntityAccountAssociation which contains both EntityID and AccountID. - This I know.
My scenario is; suppose in a product support, I do not know about this 3rd table (and no one is there to tell me) , then, is there any query to find out this 3rd table to get the relationship in a huge database with 100s of table?
For Example: In that query, I will pass these two column name as parameter and it will show me on which particular table these two columns exists.
Please help and let me know. Thanks.
Because of a bug in my reporting tool (Rave reports) which causes errors with text columns that contain more than X characters i need to break some rows in a result set into extra rows. Say I have a table called transactions like:
CREATE TABLE Trans
(id int, type int, description varchar(55), memo text)
;
INSERT INTO Trans
(id, type, description, memo)
VALUES
(1, 1, 'blah', 'hi there'),
(2, 100, 'foobar', 'yawn'),
(3, 700, 'emailmessage', 'This some long text that needs to broken into chunks. This some long text that needs to broken into chunks. This some long text that needs to broken into chunks. '),
(4, 1, 'blah blah blah', 'some other text')
;
Look at the third row, it has approx 160 chars, and lets say I want to break row 3 into 4 extra rows each with a chunk of no more than 50 chars each of the long memo. The final result of the query would be the same structure as the Trans table but now with 7 rows. The ID column should be renumbered to reflect the extra rows. I need it for SQL Server 2005.
Of course the extra rows would each have a different chunk of the long memo in the correct order but their other column values would be copies of the values in the original row
I know I could use a cursor to do it but I'm looking for better way.
The result set for this example and a chunk size of 50 chars should look like this:
id type description memo
----- ----- --------------- -------------------------------------------------
1 1 blah hi there
2 100 foobar foobar
3 700 emailmessage This some long text that needs to broken into chu
4 700 emailmessage nks. This some long text that needs to broken int
5 700 emailmessage o chunks. This some long text that needs to broke
6 700 emailmessage n into chunks.
7 1 blah blah blah some other text
Make the chunk length a variable that I can alter. Thanks in advance.
I’m trying to copy a SQL Server table, schema and data, from Server A to Server B. The SQL Server table is just a reference table which hasn't been populated for some reason on Server B. Can anyone advise how the entire table could be copied across please? On SQL Server 2000/2005.
So far we've tried a long-winded approach by copying the .mdf and .ldf files from Server A to Server B with a plan to then copy the table across into the Server B database but we are having some difficulty re-attaching the database to Server B.
Please can anyone help?
Kind Regards James
I would like to repeat for each page the header of the matrix but can not find solution ...
all recommend using RepeatOnNewPage but in ssrs 2005 is not supported see
I want to get last 3 months name from current month. For example current month is December. So, I want get like this October, November and December.
this is my query
SELECT CONVERT(CHAR, DATENAME(MONTH, IssueDate)) AS MonthName, ItemId
FROM dbo.Issue AS Issue
GROUP BY CONVERT(CHAR, DATENAME(MONTH, IssueDate)), ItemId
HAVING (ItemId = 427)
But, my need is - required this
N.B. When December month close and January month open then October auto excluded as like (November, December and January)
I have 3 Tables 1) user 2) phone 3) address
1) user
id name
1 abc
1 abc
2 tyu
2 tyu
2) phone
id number
1 0987654
1 0890764
2 3445667
2 5643456
3) address
id addr type
1 usa 1
1 uae 1
2 Uk 2
So now i have written below query:
select * from (
select u.id, u.name , p.number, cs.COL + CAST(row_number()over(PARTITION BY u.ID ORDER BY cs.COL) AS VARCHAR) RN ,
cs1.COL + CAST(row_number()over(PARTITION BY a.IDnum ORDER BY cs1.COL) AS VARCHAR) RN1
,a.addr1
from user u left join phone p on p.id = u.id
left join address as a on a.id = p.id CROSS APPLY (VALUES ('phone',number)) CS(Col,val)
CROSS APPLY (VALUES ('add',a.addr)) CS1(Col,val)
where u.id=1 and a.type = '1'
)P
PIVOT (MAX(number) FOR RN IN ([phone1],[phone2])) as pivot1
PIVOT (MAX(addr1) FOR RN1 IN ([add1],[add2])) as pivot2
so the above query give me output like:
id name phone1 phone2 add1 add2
1 abc NULL 0987654 NULL usa
1 abc 0890764 NULL uae NULL
But i want the ouput like below:
id name phone1 phone2 add1 add2
1 abc 0890764 0987654 uae usa
So how can i achieve this with the pivot ?