SQL Server services not starting and getting 1053 error- MSSQL 2005 with 2003 OS; The regedit value that I have checked is 20000 Added local admin to administrator group and tried no luck. What can I do?
mardi 31 mai 2016
Stored procedure giving error while adding two more columns
I have an SP, which was working fine until I added two more columns. Now after adding two more columns it started giving error as
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
But I have already inserted that columns.
two newly added columns are
Dept_received varchar(110), doc_type varchar(110)
Below is my SP.
ALTER PROCEDURE [dbo].[Inward_Rec_Dept_doc]
@From_date Datetime,
@To_date Datetime
AS
BEGIN
CREATE TABLE #temp(
Dept_received varchar(110), doc_type varchar(110), date datetime, Total int,doc_From_To varchar(50),Inward int, First_Level_Transfer int,
Data_Entry_Transfer int,
Second_Level_Transfer int, Outward_Transfer int,
Closed int, Communication_Transfer int, Returned int
)
INSERT INTO #temp
(Dept_received, doc_type, date, Total,doc_From_To, Inward, First_Level_Transfer,
Data_Entry_Transfer,
Second_Level_Transfer, Outward_Transfer,
Closed, Communication_Transfer, Returned)
SELECT
doc_date, COUNT(*),
(select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select min(mkey) FROM inward_doc_tracking_hdr jj
where jj.doc_date =convert(datetime,aa.doc_date,103) ) )
+ ' - '+
(select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select max(mkey) FROM inward_doc_tracking_hdr jj
where jj.doc_date =convert(datetime,aa.doc_date,103) ) )
,SUM(
CASE
WHEN status_flag in ('6','23') THEN 1 ELSE 0
END)
,SUM(
CASE
WHEN status_flag in ('4','26','24') THEN 1 ELSE 0
END)
,SUM(
CASE
WHEN status_flag in ('15','20') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('17','21') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('18','27') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='5' THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='16' THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='14' THEN 1 ELSE 0
END)
FROM inward_doc_tracking_hdr aa
WHERE doc_date between @From_date and @To_date
--AND status_flag <> '6'
GROUP BY doc_date, Dept_received, doc_type
END
Select * from #temp
I am using sql-server-2005
kindly help me what is the error
lundi 30 mai 2016
Incorrect syntax near the keyword 'join'
My below query is causing an error:
Incorrect syntax near the keyword 'join'.
Select top 1
a.mkey, emp_received, a.mkey,
c.type_desc DOC_TYPE, a.doc_no INWARD_NO,
c.type_desc dept_received, c.type_desc EMP_RECEIVED,
convert(varchar, a.doc_date, 103) date,
a.to_user, a.No_of_pages, Ref_No, c.type_desc,
e.emp_name NAME,
ISNULL(e.Email_Id_Official, 'abc@test.com') EMAILID
from
inward_doc_tracking_hdr a
left join
type_mst_a c on a.doc_type = c.master_mkey
and
left join
type_mst_a c on a.dept_received = c.master_mkey
left join
emp_mst e on a.emp_received = e.mkey
where
a.emp_received is not null
and a.mkey = (select mkey
from inward_doc_tracking_hdr aa
where doc_no='IW/16/97')
I don't know why it is causing that error
samedi 28 mai 2016
why does replacing integer values with arabic numbers give me different results Gridview
I had a table field named 'topicfull' code. Its type was varchar(50). I had some values as follows
Now I want to change the value of topicFullCode into arabic numbers. So I have changed the type of the table name using the following query.
ALTER TABLE TopicInfo
ALTER COLUMN topicFullCode NVARCHAR(50) null
Then create a function as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ReplaceIntegersWithArabicNumbers]
(
@str NVARCHAR(1000)
)
RETURNS NVARCHAR(2000)
AS BEGIN
DECLARE @newStr NVARCHAR(1000)
set @newStr= ''
declare @val NVARCHAR(1)
set @val = ''
declare @i INT
set @i = 1
WHILE @i<=LEN(@str)
BEGIN
SET @val = SUBSTRING(@str, @i, 1)
IF ((@val) >= '0' and (@val) <= '9')
BEGIN
SET @val =
CASE @val
WHEN 1 THEN N'۱'
WHEN 2 THEN N'۲'
WHEN 3 THEN N'۳'
WHEN 4 THEN N'٤'
WHEN 5 THEN N'۵'
WHEN 6 THEN N'٦'
WHEN 7 THEN N'۷'
WHEN 8 THEN N'۸'
WHEN 9 THEN N'۹'
WHEN 0 THEN N'۰'
END -- CASE
END -- IF
SET @newStr = (@newStr + @val)
SET @i=@i+1
END -- WHILE
RETURN @newStr
END
And execute the following query to replace the values.
UPDATE TopicInfo
SET topicFullCode = dbo.ReplaceIntegersWithArabicNumbers(topicFullCode)
It shows the following results from the SQL Server 2005 query window.
But when I show this data in Gridview the decimal point seems like comma (,) as shown in the following figure.
I need to know why this is happening and a solution of it.
vendredi 27 mai 2016
I need a TSQL to achieve a requirement near to pivot
I have table data like
Now, I want a result like this,
Keep in mind, I want only 3 columns, IsHeader, Col1, Col2 and a query must be dynamic.
Please help.
jeudi 26 mai 2016
Get Max and Min of document no in one column in Stored procedure
I have a Stored procedure which fetches data according to the date paramters. here is a preview
Now I want to add one more column which gets the MAX and MIN of doc_no from the inward_doc_tracking_hdr table.
Below is the SP
ALTER PROCEDURE [dbo].[GET_RECORDS_FORDATE]
@From_date Datetime,
@To_date Datetime
AS
BEGIN
CREATE TABLE #temp(
date datetime, Total int, Inward int, First_Level_Transfer int,
Data_Entry_Transfer int,
Second_Level_Transfer int, Outward_Transfer int,
Closed int, Communication_Transfer int, Returned int
)
INSERT INTO #temp
(date, Total, Inward, First_Level_Transfer,
Data_Entry_Transfer,
Second_Level_Transfer, Outward_Transfer,
Closed, Communication_Transfer, Returned)
SELECT
doc_date, COUNT(*),
SUM(
CASE
WHEN status_flag in ('6','23') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('4','26','24') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('15','20') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('17','21') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag in ('18','27') THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='5' THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='16' THEN 1 ELSE 0
END),
SUM(
CASE
WHEN status_flag='14' THEN 1 ELSE 0
END)
FROM inward_doc_tracking_hdr
WHERE doc_date between @From_date and @To_date AND status_flag <> '6'
GROUP BY doc_date
END
Select * from #temp
How to get that MAX and MIN document with respective to the TOTAL column.
Stored procedure not working in sql server 2005
I want to run this SP but it is not working and giving error as
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
ALTER PROCEDURE GET_RECORDS_FORDATE
@From_date Datetime,
@To_date Datetime
AS
BEGIN
DECLARE @Total INT
DECLARE @Inward INT
DECLARE @First_Level_Transfer INT
DECLARE @Data_Entry_Transfer INT
DECLARE @2nd_Level_Transfer INT
DECLARE @Outward_Transfer INT
DECLARE @Closed INT
DECLARE @Communication_Transfer INT
SELECT *
INTO #temp
FROM (
select User_Id, Mkey, 0 Total, 0 Inward, 0 First_Level_Transfer, 0 Data_Entry_Transfer,
0 Second_Level_Transfer, 0 Outward_Transfer,
0 Closed, 0 Communication_Transfer
from inward_doc_tracking_hdr
) AS x
DECLARE Cur_1 CURSOR
FOR SELECT User_Id FROM #temp
OPEN Cur_2
DECLARE @User_Id INT
FETCH NEXT FROM Cur_2
INTO @User_Id
WHILE (@@FETCH_STATUS = 0)
BEGIN
/***** Total *******/
select
@Total = count(*), 'Total' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
group by doc_date
/***** Inward *******/
select
@Inward = count(*), 'Inward' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
and status_flag in ('6')
group by doc_date
/***** 1st Level Transfer *******/
select @First_Level_Transfer = count(*), '1st Level Transfer' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
and status_flag in ('4','26','24')
group by doc_date
/***** Data Entry Transfer *******/
select @Data_Entry_Transfer = count(*), 'Data Entry Transfer' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
and status_flag in ('15','20')
group by doc_date
/***** 2nd Level Transfer *******/
select @2nd_Level_Transfer = count(*), '2nd Level Transfer' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
and status_flag in ('17','21')
group by doc_date
/***** Outward Transfer *******/
select @Outward_Transfer = count(*), 'Outward Transfer' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
and status_flag='18'
group by doc_date
/***** Close *******/
select @Closed = count(*), 'Close' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
and status_flag='5'
group by doc_date
/***** Communication Transfer *******/
select @Communication_Transfer = count(*), 'Communication Transfer' stage, doc_date Till_date from
inward_doc_tracking_hdr
where
doc_date between convert(datetime, @From_date,103)
and convert(datetime, @To_date, 103)
and status_flag='16'
group by doc_date
Update #temp
SET
Total = @Total,
Inward = @Inward,
First_Level_Transfer = @First_Level_Transfer,
Data_Entry_Transfer = @Data_Entry_Transfer,
Second_Level_Transfer = @2nd_Level_Transfer,
Outward_Transfer = @Outward_Transfer,
Closed = @Closed,
Communication_Transfer = @Communication_Transfer
FETCH NEXT FROM Cur_2 INTO @User_Id
END
CLOSE Cur_2
DEALLOCATE Cur_2
SELECT * FROM #temp
END
I dont know what's the error.
I am using sql-server-2005
SQL INTERSECT with most matched results
I have a question related to SQL script:
If I do this: DECLARE @ProfileID int SET @ProfileID = 1
SELECT [SetID],[ProfileID] FROM [GeneralizedTable].[dbo].[DBProfileConditionTable]
where ([DBTypeID] = 4 AND ([ProfileID] = 1 OR [ProfileID] = 3 OR [ProfileID] = 13) AND [ConditionID] = 1 AND [CondMin] between 0 and 0 AND [CondMax] between 1000 and 1000)
group by [SetID], [ProfileID]
ORDER BY ABS([ProfileID] - @ProfileID)
this will sort the results that most matched with Profile = 1 on top.
But if I add some "INTERSECT" clauses to construct multiple search, will pop up a error "Incorrect syntax near the keyword 'INTERSECT'"
the final clause is :
DECLARE @ProfileID int SET @ProfileID = 1
SELECT [SetID],[ProfileID] FROM [GeneralizedTable].[dbo].[DBProfileConditionTable]
where ([DBTypeID] = 4 AND ([ProfileID] = 1 OR [ProfileID] = 3 OR [ProfileID] = 13) AND [ConditionID] = 1 AND [CondMin] between 0 and 0 AND [CondMax] between 1000 and 1000)
group by [SetID], [ProfileID]
ORDER BY ABS([ProfileID] - @ProfileID)
INTERSECT
SELECT [SetID], [ProfileID] FROM [GeneralizedTable].[dbo].[DBProfileConditionTable]
where ([DBTypeID] = 4 AND ([ProfileID] = 1 OR [ProfileID] = 3 OR [ProfileID] = 13) AND [ConditionID] = 3 AND [CondMin] between 0 and 0 AND [CondMax] between 200 and 200)
group by [SetID], [ProfileID]
ORDER BY ABS([ProfileID] - @ProfileID)
INTERSECT SELECT [SetID], [ProfileID] FROM [GeneralizedTable].[dbo].[DBProfileParameterSetTable]
where ([DBTypeID] = 4 AND ([ProfileID] = 1 OR [ProfileID] = 3 OR [ProfileID] = 13) AND [BondClassificationID] = 0 AND [BondObjectID] = 0)
group by [SetID], [ProfileID]
ORDER BY ABS([ProfileID] - @ProfileID)
The running SQL Server is SQL Server 2005. Could you tell me which part is wrong to cause this error and how to solve
this to make it work? Thanks.
Query is giving error
Below is my query which is giving error as
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
select @Total = count(*), 'Total' stage,doc_date Till_date
from inward_doc_tracking_hdr
where doc_date between convert(datetime,@From_date,103) and convert(datetime,@To_date,103)
group by doc_date
mercredi 25 mai 2016
Create procedure with UNION ALL query and pass date as parameters
I have a query which gives me the result as
the query is
select count(*) tot_count,'Total' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
union all
select count(*) tot_count,'Inward' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103) and status_flag in ('6')
union all
select count(*) tot_count,'1st Level Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag in ('4','26','24')
union all
select count(*) tot_count,'Data Entry Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag in ('15','20')
union all
select count(*) tot_count,'2nd Level Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag in ('17','21')
union all
select count(*) tot_count,'Outward Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag='18'
union all
select count(*) tot_count,'Close' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag='5'
/*union all
select count(*) tot_count,'Return to Creator' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag='6'*/
union all
select count(*) tot_count,'Communication Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag='16'
So, what I want is, I want to use this query in the Stored procedure with two parameters such as
from_date and To_date which will be dynamic in the above query.
Also, I want to display result as below.
SR_NO Total Inward 1stLevelTransfer
1 8 0 2
I am using sql-server-2005
kindly suggest how to do
Cannot Find either column or aggregate, or name is ambiguous
I'm trying to import a XML table to a SQL table. I created a basic table in the SQL Server database but I'm getting an error:
Cannot find either column "var1" or the user-defined function or aggregate "var1.value", or the name is ambiguous.
Code:
declare @xmldata as xml
set @xmldata = (SELECT CONVERT(XML, BulkColumn)AS Bulkcolumn
FROM OPENROWSET (BULK '<filename>.xml', SINGLE_BLOB) as X)
INSERT INTO table1(var1, var2, var3)
SELECT
var1 = var1.value('var1', 'int'),
var2 = var2.value('var2', 'int'),
var3 = var3.value('var3', 'int')
I must've gone wrong somewhere, specifically when I attempt to import it into the SQL Server table.
NHibernate stored procedure timeout(deadlock?) SQL Server 2005
I have a stored procedure that I have to call which has a couple of Update statements in it.
I was running it using _session.CreateSQLQuery("exec procName @param1=:param1, @param2=:param2...")
I upgraded nhibernate(after 5 years) and now this times out, although the stored procedure executes immediately in SSMS. At first I suspected it to be parameter sniffing, so I went to the SQL Profiler and grabbed all the connection information and set that on the SSMS connection and running the exact query nhibernate sent to SQL Server(execute_sql query), it executed successfully immediately. I also tried clearing out the Proc Cache for the DB in case of corrupted query plans.
I then executed the SP using ADO.NET with no interaction with NHibernate at all and it executes successfully, which lead me to believe it is some sort of blocking/deadlocking issue, but the profiler doesn't show any deadlocking/blocking happening. I'm running a transaction per request.
Running NHibernate v4.0.0.4000, SQL Server 2005, ASP.NET MVC 5
Anyone have any ideas where I should go from here before I light my hair on fire?
Query is not getting executed in sql
I have a query which I want to run and check what result it is giving.
select
trl.status_flag, ty.type_desc
from
Inward_Status_Trl trl, type_mst_a ty
where
trl.status_flag = ty.add_iinfo1
and ty.type_code = 'S2'
and trl.delete_flag = 'N'
and Return_flag = '4'
order by
sr_no
but what happening here is, it is not getting executed. Just shows the message
Executing query......
I am using SQL Server 2005
Upgrading SQL server cluser 2008 to SQL server cluster 2012
Please i want some one to help me with step by step method of upgrading SQL server 2008 with cluster node on two different machines to SQL sever 2012 without loosing the existing settings/configurations
mardi 24 mai 2016
How do i remove secounds from date time in SQL query
There are several ex for same type but i think this is bit unique.
My Query is:
select Convert(nvarchar(15),date,103)+ ' ' + LTRIM(RIGHT(CONVERT(CHAR(20),date, 22), 11))DC from Table1
Result is:
I am getting right in the above result. only thing i need to require is the format should 09/11/2015 2:29 PM.
there are Many format to achieve this but the user unlike to change any other format. Thank you in advance
Count Function and IF Condition in (SQL)
select Count(Student_ID) as 'StudentCount' from CourseSemOne where Student_ID =1; i want to add only two values for this ID(Student_ID=1)..if Admin adding more than two values for this ID..then i want to show me a message "Sorry! you cannot add more than two values".
lundi 23 mai 2016
how to use the data in parameter and make a format like this @Hours : @Mins
how to use the data in parameter and make a format like this @Hours : @Mins and display and output like this 24:33. because I use A different parameter for the hours and mins. now I want to call it in the UI so I want it to output like this 24:33
Inserting over 100,00 rows into ms-sql database
I have an sql script that has over 100,000 individual insert statements. The sql is correct as I it will run if I choose small portions of the statements but it will not run if I run the entire script. It throws an error but does not give a reason like it typically does. I have opened the script in management studio to see if their may be something wrong with one of the statements but it all looks correct. I have run scripts in the past and it will usually insert up to a point and then give me an error. It will not even insert the first row, but if I select just that row and execute it inserts no issue.
Could their be a limit to the number of inserts allowed? Here is a small sample of the insert statements. They are all basically the same just a lot more.
Insert into test (Article_ID, Product_ID, Name, Description, Type, Category, Color, Price, Commission, Currency, URL_Product, URL_Picture__big_ ) values ( 101661638, 1005344977, 'In Russ We Trust', 'Exclusive to Morel Tees - Show your support for one of the NBA''s "premier" point guards - Oklahoma City Thunder''s Russell Westbrook. Design depicts the Oklahoma City skyline, the #0 and the faith that Oklahoma City Thunder fan''s have for their star point guard. Limited Time Only Online Only', 'Men''s Premium T-Shirt', 'T-Shirts', 'royal blue', 25, 9.6, 'USD', 'http://ift.tt/1NHxgYY', 'http://ift.tt/20rKQRB' );
Insert into test (Article_ID, Product_ID, Name, Description, Type, Category, Color, Price, Commission, Currency, URL_Product, URL_Picture__big_ ) values ( 16746212, 1001526574, 'Team Trash Talk - Crossover', 'Exclusive to Morel Tees - Team Trash Talk ankle breaking crossover. A must have for those that lack trash talk game.', 'Men''s T-Shirt', 'T-Shirts', 'white', 20, 2, 'USD', 'http://ift.tt/1NHxdwj', 'http://ift.tt/20rL4YK' );
Insert into test (Article_ID, Product_ID, Name, Description, Type, Category, Color, Price, Commission, Currency, URL_Product, URL_Picture__big_ ) values ( 16746194, 1001526704, 'Team Trash Talk - Crossover', 'Exclusive to Morel Tees - Team Trash Talk ankle breaking crossover. A must have for those that lack trash talk game.', 'Men''s T-Shirt', 'T-Shirts', 'black', 20, 2, 'USD', 'http://ift.tt/1NHxhfA', 'http://ift.tt/20rLtdM' );
Insert into test (Article_ID, Product_ID, Name, Description, Type, Category, Color, Price, Commission, Currency, URL_Product, URL_Picture__big_ ) values ( 16211582, 1001098367, 'Team Trash Talk - Jumpshot', 'Exclusive to Morel Tees - Team Trash Talk deadly jump shot This is a must have for all those that spend their time in pick up games. Let everyone else know that the court is now yours.', 'Men''s T-Shirt', 'T-Shirts', 'white', 20, 2, 'USD', 'http://ift.tt/1NHxfEr', 'http://ift.tt/20rLCOn' );
Actual error is - Query completed with errors
how to copy the databases from sql server 2005 in windows server 2003 to sql server 2014 in windows 2012 r2
I was used sql server 2005 in old windows server 2003,Now I purchased the windows server 2012 R2.In that windows server R2 uses sql server 2014,so I want to copy the all databases to sql server 2014 from sql server 2003.Please suggest me.
Thank you in Advanced
Unable to create EXTERNAL TABLE at Azure SQL SERVER
CREATE EXTERNAL DATA SOURCE EX_SOURCE
WITH (
TYPE = RDBMS,
LOCATION = 'SERVER.database.windows.net',
DATABASE_NAME = 'DB_NAME',
CREDENTIAL = "CREDENTIAL"
);
Getting error, Incorrect syntax near EXTERNAL.
Does anyone know the solution?
Sql-Server, Is null case-sensitive?
a co-worker of mine stated that she have a query that "work" only if she use NULL, and not with null.
She wasn't able to provide me with an example.
As far as I know there shouldn't be any difference, i even run a simple test:
select case when NULL is NULL then 1 else 0 end;
select case when NULL is null then 1 else 0 end;
select case when null is NULL then 1 else 0 end;
select case when null is null then 1 else 0 end;
and, as expected, they all returned 1.
So here the question, there is any know istance where using null is different than using NULL?
samedi 21 mai 2016
Python Pycharm and SQL Server connection
I would like to use data from SQL server in Pycharm using python. I have my database connection set up in Pycharm, but not sure how to access this data within my python code. I would like to query the data within the python code (similar to what I would do in R using the RODBC package).
Any suggestions on what to do or where to look would be much appreciated.
vendredi 20 mai 2016
How to redirect a request for DB1 to DB2
Suppose I have two databases named DB1 and DB2. In DB1, there is a table named Student, In DB2, there is a stored procedure named SP1. In SP1, I am selecting data of Student Table using below query :
Select *from DB1.dbo.Student.
I have more than 300 stored procedures having above said cross database communication. Now, I want to change my database from DB1 to DB3 that is identical to DB1 from data and schema perspective. For this, I also have to modify all 300 stored procedures that are having fully-qualified database name. Now, the query will likely to be as follows :
Select *from DB3.dbo.Student
I don't want to change all stored procedure to point DB3 now, also don't want to change my queries written in stored procedure into dynamic SQL (I know this can be done by creating dynamic SQL).
Is it possible if We run DB1.dbo.Student, It will redirect to DB3.dbo.Student. Any intermediate layer or any SQL setting.
It'll be very big help for me. Thanks In Advance !!
jeudi 19 mai 2016
Get the lastest record from the query
I have a query which gives me 5 records in the sql, but I want to show one record which is latest one.
Below is my query
Select a.mkey, c.type_desc DOC_TYPE, a.doc_no INWARD_NO,
convert(varchar, a.doc_date,103)date,
a.to_user, a.No_of_pages, Ref_No, d.type_desc DEPT_RECEIVED, b.first_name + ' ' +
b.last_name EMP_RECEIVED, b.first_name + ' ' + b.last_name NAME,
b.email
from inward_doc_tracking_hdr a , user_mst b ,type_mst_a c,
type_mst_a d
where a.to_user = b.mkey and a.doc_type = c.master_mkey
and a.dept_received = d.Master_mkey and a.to_user = '1260'
Below is the result
I am using sql-server-2005
I tried with TOP1 but it is not giving me the latest record
sql server 2005 add row with totals
I have a SQL SERVER 2005 query , I need to add a row at the end , with the total amounts of some of the columns . I have tried using ' rollup ' , but that requires me to group the query . I have also tried to cram everything into a subquery , but I could not . Could someone give me some idea? the columns have to totalize , are all the 'IMP'. Thanks!!
SELECT C.INS,C.TIM,C.OIM,C.SIM,C.NIM,C.FEC AS FECHA_FACT,C.CCC,C.SUC,C.NRO,C.IMP AS TOTAL_FACT,
ISNULL(C1.IMP,0) AS PAGO_INST,ISNULL(C2.IMP,0) AS NOTA_CREDITO,ISNULL(C3.IMP,0) AS RET_GANANCIAS,
ISNULL(C4.IMP,0) AS RET_ING_BRU,ISNULL(C6.IMP,0) AS RET_IVA, ISNULL(C5.IMP,0) AS DEBITO_FECLIBA,
ISNULL(C7.IMP,0) AS RET_SUSS
,(C.IMP + ISNULL(C1.IMP,0) + ISNULL(C2.IMP,0) + ISNULL(C3.IMP,0) + ISNULL(C4.IMP,0) + ISNULL(C5.IMP,0)+ ISNULL(C6.IMP,0) + ISNULL(C7.IMP,0)) AS SALDO
FROM CLICTA C
LEFT JOIN CLICTA C1 ON C.INS = C1.INS AND C1.CCC = 'PI' AND C.NIM = C1.NIM
LEFT JOIN CLICTA C2 ON C.INS = C2.INS AND C2.CCC = 'cp' AND C.NIM = C2.NIM
LEFT JOIN CLICTA C3 ON C.INS = C3.INS AND C3.CCC = 'R1' AND C.NIM = C3.NIM
LEFT JOIN CLICTA C4 ON C.INS = C4.INS AND C4.CCC = 'R2' AND C.NIM = C4.NIM
LEFT JOIN CLICTA C5 ON C.INS = C5.INS AND C5.CCC = 'R5' AND C.NIM = C5.NIM
LEFT JOIN CLICTA C6 ON C.INS = C6.INS AND C6.CCC = 'R6' AND C.NIM = C6.NIM
LEFT JOIN CLICTA C7 ON C.INS = C7.INS AND C7.CCC = 'S1' AND C.NIM = C7.NIM
WHERE C.INS BETWEEN '011I' AND '011I' --INSTITUCION
AND C.TIM BETWEEN 'fp' AND 'fp' --TIPO COMPROBANTE
AND C.OIM BETWEEN '' AND '' --ORIGEN
AND C.SIM BETWEEN '100' AND '100' --SUCURSAL
AND C.NIM BETWEEN '4370' AND '4395' --DESDE HASTA COMPROBANTE
AND C.CCC = 'fp' --TIPO COMPROBANTE
AND C.IMP <> 0
ORDER BY C.NIM
sql server - Arrange objects in view in diragram pane
in database diagrams you can easily right click and select arrange tables,Is there any way to use that in view diagram pane? thank you in advance
Adding datetime as a parameter is giving Error converting data type varchar to datetime (Error) in stored procedure
I have a stored procedure which accepts one parameter as @ReportDate.
but when I execute it with parameter it gives me error as
Error converting data type varchar to datetime.
Here is the SP.
ALTER PROCEDURE [dbo].[GET_EMP_REPORT]
@ReportDate Datetime
AS
BEGIN
DECLARE @Count INT
DECLARE @Count_closed INT
DECLARE @Count_pending INT
DECLARE @Count_wip INT
DECLARE @Count_transferred INT
DECLARE @Count_prevpending INT
SELECT *
INTO #temp
FROM (
select distinct a.CUser_id,a.CUser_id User_Id, b.first_name + ' ' + b.last_name NAME,
0 RECEIVED, 0 CLOSED,
0 PENDING, 0 WIP, 0 TRANSFERRED, 0 PREV_PENDING
from inward_doc_tracking_trl a, user_mst b
where a.CUser_id = b.mkey
) AS x
DECLARE Cur_1 CURSOR
FOR SELECT CUser_id, User_Id FROM #temp
OPEN Cur_1
DECLARE @CUser_id INT
DECLARE @User_Id INT
FETCH NEXT FROM Cur_1
INTO @CUser_id, @User_Id
WHILE (@@FETCH_STATUS = 0)
BEGIN
/***** received *******/
SELECT @Count = COUNT(*) FROM inward_doc_tracking_trl
WHERE CUser_id = @CUser_id
AND NStatus_flag = 4
AND CStatus_flag = 1
AND U_datetime BETWEEN @ReportDate AND GETDATE()
/***** closed *******/
SELECT @Count_closed = COUNT(*) FROM inward_doc_tracking_trl
WHERE CUser_id = @CUser_id
AND NStatus_flag = 5
AND U_datetime BETWEEN @ReportDate AND GETDATE()
/***** pending *******/
SELECT @Count_pending = COUNT(*) FROM inward_doc_tracking_trl trl
INNER JOIN inward_doc_tracking_hdr hdr ON hdr.mkey = trl.ref_mkey
WHERE trl.N_UserMkey = @CUser_id
AND trl.NStatus_flag = 4
AND trl.CStatus_flag = 1
AND hdr.Status_flag = 4
AND trl.U_datetime BETWEEN @ReportDate AND GETDATE()
/***** wip *******/
SELECT @Count_wip = COUNT(*) FROM inward_doc_tracking_trl trl
INNER JOIN inward_doc_tracking_hdr hdr ON hdr.mkey = trl.ref_mkey
INNER JOIN (select max(mkey) mkey,ref_mkey from inward_doc_tracking_trl where NStatus_flag = 2 group by ref_mkey ) trl2
ON trl2.mkey = trl.mkey and trl2.ref_mkey = trl.ref_mkey
WHERE trl.N_UserMkey = @CUser_id
AND trl.NStatus_flag = 2
AND hdr.Status_flag = 2
AND trl.U_datetime BETWEEN @ReportDate AND GETDATE()
/***** transferred *******/
SELECT @Count_transferred = COUNT(*) FROM inward_doc_tracking_trl
WHERE CUser_id = @CUser_id
AND NStatus_flag = 4
AND CSTATUS_flag <> 1
AND U_datetime BETWEEN @ReportDate AND GETDATE()
/******** Previous pending **********/
SELECT @Count_prevpending = COUNT(*) FROM inward_doc_tracking_trl trl
INNER JOIN inward_doc_tracking_hdr hdr ON hdr.mkey = trl.ref_mkey
INNER JOIN (select max(mkey) mkey,ref_mkey from inward_doc_tracking_trl where NStatus_flag = 2 group by ref_mkey ) trl2
ON trl2.mkey = trl.mkey and trl2.ref_mkey = trl.ref_mkey
WHERE trl.N_UserMkey = @CUser_id
AND trl.NStatus_flag = 2
AND hdr.Status_flag = 2
AND trl.U_datetime < @ReportDate
UPDATE #temp
SET RECEIVED = @Count,
CLOSED = @Count_closed,
PENDING = @Count_pending,
WIP = @Count_wip,
TRANSFERRED = @Count_transferred,
PREV_PENDING = @Count_prevpending
WHERE CUser_id = @CUser_id
AND User_Id = @User_Id
FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
END
CLOSE Cur_1
DEALLOCATE Cur_1
SELECT * FROM #temp
END
I am executing like this EXEC GET_EMP_REPORT '16/05/2016'
The current date format entered is DD/MM/YYYY which gives me the error. Executing it as MM/DD/YYYY works but I would prefer executing it as DD/MM/YYYY.
but getting error
I am using SQL-server-2005
sql server xml.value skeleton dynamic cases
I'm using SQL Server 2005.I want to enquiry xml records to row by rows. So that I was used the below codes. There is three records. When I excuted like that, I can see the one records only.
DECLARE @Xml XML
DECLARE @Emp TABLE(ps NVARCHAR(MAX) NULL,inv NVARCHAR(MAX) NULL)
INSERT INTO @Emp(ps,inv)
SELECT @Xml.value('(/NewDataSet/POSDetails/PSTATION)[1]', 'NVARCHAR(MAX)'),
@Xml.value('(/NewDataSet/POSDetails/INVNUMBER)[1]', 'NVARCHAR(MAX)')
SELECT * FROM @Emp
So that, I use next two SQL Statements the above SQL. I want to use dynamic for index.Can I use? Please advise.
@Xml.value('(/NewDataSet/POSDetails/PSTATION)[2]/[3]', 'NVARCHAR(MAX)')
INSERT INTO @Emp(ps,inv)
SELECT @Xml.value('(/NewDataSet/POSDetails/PSTATION)[2]', 'NVARCHAR(MAX)'), @Xml.value('(/NewDataSet/POSDetails/INVNUMBER)[2]', 'NVARCHAR(MAX)')
INSERT INTO @Emp(ps,inv) SELECT @Xml.value('(/NewDataSet/POSDetails/PSTATION)[3]', 'NVARCHAR(MAX)'), @Xml.value('(/NewDataSet/POSDetails/INVNUMBER)[3]', 'NVARCHAR(MAX)')
Thank in advance.
mercredi 18 mai 2016
Select rows horizontally in SQL Server 2005
I have Employee SQL Table (SQL Sever 2005):
EmployeeID Field1 Field2
121212 MISC1 ABC
121212 MISC2 XYZ
121213 MISC1 AAA
121213 MISC2 BBB
I would like to display the result like:
EmployeeID MISC1 MISC2
121212 ABC XYZ
121213 AAA BBB
Please advise and thank you in advance for your help.
Mssql 2005 How to implement limit x,y without using ORDER BY?
I have a table where is no any numeric field which can be used in order by. So can someone help to adapt existing queries to my situation? I need to get only 4 fields from the table (let's call it field1, field2, field3, field4). I'm trying to do that within two last hours, but with no success ;(
trying to avoid In operator sql2005
I am trying to avoid In operator in the below query, I didn't see any room for adding exist operator. Is there any way to improvise the query
UPDATE ITEM
SET QTYREQ=ISNULL(( SELECT SUM( D.QTYORDERED-D.QTYPICKED)
FROM ORDRDET D, ORDRHDR H
WHERE D.ASCITEMID=ITEMQTY.ASCITEMID
AND H.ORDERNUMBER=D.ORDERNUMBER
AND D.ORDERFILLED IN ('O', 'T')
AND H.CONSOLIDATED_ORDERNUMBER IS NULL
AND D.QTYPICKED<D.QTYORDERED
AND H.PICKSTATUS IN ('N','I','W', 'H', '')),0)
+( SELECT ISNULL( SUM( WD1.QTY-WD1.QTY_PICKED),0)
FROM WO_DET WD1, WO_HDR WH1
WHERE WD1.WORKORDER_ID=WH1.WORKORDER_ID AND
ITEM.ASCITEMID=WD1.COMP_ASCITEMID
AND WD1.QTY_PICKED<WD1.QTY
AND WH1.STATUS IN ('S','N'))
SQL Table to MDB (SQL 2005)
I have a SQL table that I want to export to a MDB file. I've read around here that says to use the import/export wizard.
There's one problem though: The server I'm working with has no access to Microsoft Access so I can't simply create a new MDB file.
Is there an alternative to the Import export wizard or perhaps a way to create an MDB file without having Microsoft Access?
Create cursor for temporary table in sql server 2005
I have some data inserted into the temporary table and I want to show COUNT in a LOOP by using CURSOR
Here is the temp table:-
SELECT *
INTO #temp
FROM (
select distinct a.CUser_id, b.User_Id, b.first_name + ' ' + b.last_name NAME
from inward_doc_tracking_trl a, user_mst b
where a.CUser_id = b.mkey
and CStatus_flag = 1
and NStatus_flag = 4
) AS x
Select * from #temp order by NAME
I am new to cursor, kindly help
I am using sql server 2005
mardi 17 mai 2016
Need to insert the join query data into temp table
I want to insert the data coming from the below query into the JOIN.
select distinct a.CUser_id,b.User_Id, b.first_name + ' ' + b.last_name NAME
from inward_doc_tracking_trl a, user_mst b
where a.CUser_id = b.mkey
How to achieve it ?
I am using SQL server 2005
Check count of user in a table for particular date
I have table for which I want to check the count for particular user for dates between 01/04/2016 - 17/05/2016
I have added the query like below
select count(CUser_Id) from inward_doc_tracking_trl
where CSTATUS_flag = 4
and NStatus_Flag = 1
and CUser_Id = 1260
now how to add the date part and check ??
I am using SQL server 2005
How to get data from multiple devices like smoke meter,plc controller,etc to my application which contains lots of forms
I am currently using a software where i have to measure speed,fuel efficiency,torque,etc. This values will be obtained from controllers and other measuring devices.Actually my software is successfully communicating with all this devices. I have multiple forms in my software/application and multiple devices for getting data.Each device has a list of parameters to be measured.Is it possible to get all this lists in a single class and use them for different functions like calculations on 1st form ,display data on 2nd form and real graph on another form? And my second problem is if i have changed data in 1st form it should be reflect on all other forms at same instance?
SQL query not giving proper result
I have a query with multiple joins for which DOC_TYPE column is coming NULL even if it has some values in it. The query is below
SELECT a.mkey,
c.type_desc DOC_TYPE,
a.doc_no INWARD_NO,
CONVERT(VARCHAR, a.doc_date, 103)date,
a.to_user,
a.No_of_pages,
Ref_No,
c.type_desc DEPT_RECEIVED,
c.type_desc EMP_RECEIVED,
b.first_name + ' ' + b.last_name NAME,
b.email
FROM inward_doc_tracking_hdr a
LEFT JOIN user_mst b
ON a.to_user = b.mkey
LEFT JOIN type_mst_a c
ON a.doc_type = c.master_mkey
AND a.dept_received = c.Master_mkey
AND a.emp_received = c.Master_mkey
WHERE a.to_user = '1279'
The doc_type value is 428 and whose desciption comes from
select type_desc from type_mst_a where master_mkey = 428
as Drawing but when I run the join query I get it as NULL. why ??
I am using sql server 2005
lundi 16 mai 2016
SQL count not showing result properly
I have a simple select query
select count(status_flag) STATUS_COUNT from
inward_doc_tracking_hdr where to_user = 1279 and status_flag = 4
which display' results as
I have joined with user_mst table like below:-
SELECT COUNT (a.status_flag) counts, a.mkey, a.to_user, b.email, b.first_name + ' ' +
b.last_name name FROM inward_doc_tracking_hdr a LEFT JOIN user_mst b
ON a.to_user = b.mkey WHERE a.to_user = '1279' AND a.status_flag = '4'
Group by a.mkey, a.to_user, b.email, b.first_name,b.last_name
which displays result as
So my issue is
why the second query is showing two rows for the same
to_userwhose count is 2.
I am using sql-server-2005
BETWEEN operator not comparing dates correctly in SQL Server 2005
I've got a simple query:
SELECT *
FROM TABLE
WHERE EventDateTime BETWEEN '2016-05-12 00:00:00' and '2016-05-12 23:59:59'
For some reason, the query output includes rows with a datetime of 2016-05-13 00:00:00.
Using CAST to convert the string to datetime removes the values for 2015-05-13:
SELECT *
FROM TABLE
WHERE EventDateTime BETWEEN CAST('2016-05-12 00:00:00' AS DateTime) and CAST('2016-05-12 23:59:59' AS DateTime)
What would cause this?
sql begin end week of year
I do cant get the begin and end week between two dates in Sql Server? for example:
begin end
2016-04-25 2016-05-01
2016-05-02 2016-05-08
2016-05-09 2016-05-15
2016-05-16 2016-05-22
2016-05-23 2016-05-29
2016-05-30 2016-06-05
...
Regards!
SQL server 2005 SP4 error
I am trying to install sp4 on our server, but when I am Installing it is showing below error.
The product instance sqlname does not have prerequisite update 5000 installed. Update 5292 is dependent on prerequisite update 5000. Exit setup and refer to the Knowledge Base article to find the prerequisite patch. Install the prerequisite and rerun the installation.
current version:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Nov 24 2008 16:17:31
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
SQL query giving error for join
I want to run a SQL query which is below
SELECT
COUNT (a.status_flag) counts,
a.mkey, a.to_user
FROM
inward_doc_tracking_hdr a
LEFT JOIN
user_mst b ON a.to_user = b.mkey
WHERE
a.to_user = 1260 AND a.status_flag = 4.
but it is not running, throwing this error:
Column 'inward_doc_tracking_hdr.Mkey' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What's wrong here?
I am using SQL Server 2005.
dimanche 15 mai 2016
Get records today which satisfy two date ranges and within a specific timing in SQL server
I have following [RestaurantOffer] table. Offers, which is valid only within two date ranges (i.e FromDate and ToDate) and at a particular timing(FromTime,ToTime).
So I want to write a query which would give me all Offers on current date(Today) and the time will be more than current Time(Today's Current Time). Because I don't want to get the expired Offers.
Database :
FromDate(date),
ToDate(date),
FromTime(time),
ToTime(time)
vendredi 13 mai 2016
How to show result based on matching keywords count in descending order using asp classic
I want to show result based on highest matching keywords in descending order.
Below code shows result but not sorted in descending order.
please help me to resolve this issue as i don't want to use full text index
my code is as folows:
<%
Dim SearchWord, arrKeyWords, Cnt, tsearch, i
SearchWord = trim(request("searcha"))
SearchWord = replace(SearchWord, " and ", " ")
SearchWord = replace(SearchWord, " in ", " ")
SearchWord = replace(SearchWord, " or ", " ")
arrKeyWords = Split(SearchWord ," ")
%>
<%
If IsArray(arrKeyWords) Then
For i = 0 To (UBound(arrKeyWords)-1)
Dim objRSsg, objCmdsg, strsg
Set objCmdsg = Server.CreateObject("ADODB.Command")
Set objRSsg = Server.CreateObject("ADODB.Recordset")
dim countItem, numPerPage, page, totalpages, totalRecs
countItem = 0
numPerPage = 50
objRSsg.cursorlocation = 3
if request("page") = "" then
page=1
else
page=CLng(request("page"))
end if
strsg = "select rProd_name, r_id from reseller_prod where rProd_name LIKE '%' + ? + '%' union select Prod_name, '' as r_id from product where Prod_name LIKE '%' + ? + '%'"
With objCmdsg
.ActiveConnection = MM_connDUdirectory_STRING
.CommandText = strsg
.CommandType = adCmdText
.Parameters.Append(.CreateParameter("@sa1", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa2", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa3", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa4", adVarChar, adParamInput, 1000))
.Parameters("@sa1").Value = arrKeyWords(i)
.Parameters("@sa2").Value = arrKeyWords(i)
.Parameters("@sa3").Value = arrKeyWords(i)
.Parameters("@sa4").Value = arrKeyWords(i)
end with
objRSsg.Open objCmdsg, , 1, 2
next
end if
....
....
%>
display multiple values from two tables in one row in sql server
I have the following two tables
TableA Table B
id bid bname btitle
1 1 john titlejohn
2 1 william titlewilliam
3 1 george titlegeorge
2 bill titlebill
3 kyle titlekyle
3 seb titleseb
I need a query in sql server which displays the following output:
id name title
1 john,william,george titlejohn,titlewilliam,titlegeorgw
2 bill titlebill
3 kyle,seb titlekyle,titleseb
Please help.
SQL Updating column after adding it giving "Invalid column name" error
I have the following SQL in SQL Server 2005 but I get an error stating "Invalid column name 'ExpIsLocalTime' (ln 7) when I run it:
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ExpIsLocalTime' AND Object_ID = Object_ID(N'[dbo].[tbl_SessionsAvailable]'))
BEGIN
ALTER TABLE dbo.tbl_SessionsAvailable ADD
ExpIsLocalTime bit NOT NULL CONSTRAINT DF_tbl_SessionsAvailable_ExpIsLocalTime DEFAULT (0)
UPDATE tbl_SessionsAvailable
SET ExpIsLocalTime = 1
END
GO
This will be in a script file that may be run more than once so I'm trying to make sure the UPDATE only runs once. Is there something about BEGIN/END that delays the execution of the DDL statement?
Search for a special character in sql server 2005
I have some special character in SQL Server 2005 on one of the field which has Japanese data. Due to which Cube processing fails with Attribute Key not found error.
The value interpreted by Analysis Services server is '野田 ', but the value in db is 野田.
[Note the space after value '野田 ']
Am trying to search for all the rows that contain this special character at end of the data in last_name column.
For this, I tried below query, but it doesnt give me the incorrect results.
select * from [#temp1]
where convert(nvarchar,last_name) like convert(nvarchar,'% ')
I want the rows that contains special character (' ') at end of values in last_name field.
What am I missing?
Update database with another database
I have two databases(Say 'A' and 'B'). Many changes are used to done in 'A' database, but after certain time period, I need to make 'B' database too same as 'A'. Currently I am deleting and creating 'B' database, which seems the easiest to me. But, I'm curious is there any method so that I can just update 'B' from 'A'. Also, I think in case of large size of database, if I use delete and create method, it costs too much time to me. If there's any advice or solution, I feel highly helpful. Thanks in advance.
jeudi 12 mai 2016
how to show result by highest matching keywords result first using asp classic
I want to show result sorting by highest matching keywords result should come first and then second highest matching keywords result should come next like this...
below code show result but not in sorting as what i mentioned above.
please help me to resolve this issue as i don't want to use full text index
my code is as below given:
<%
Dim SearchWord, arrKeyWords, Cnt, tsearch, i
SearchWord = trim(request("searcha"))
SearchWord = replace(SearchWord, " and ", " ")
SearchWord = replace(SearchWord, " in ", " ")
SearchWord = replace(SearchWord, " or ", " ")
arrKeyWords = Split(SearchWord ," ")
%>
<%
If IsArray(arrKeyWords) Then
For i = 0 To (UBound(arrKeyWords)-1)
Dim objRSsg, objCmdsg, strsg
Set objCmdsg = Server.CreateObject("ADODB.Command")
Set objRSsg = Server.CreateObject("ADODB.Recordset")
dim countItem, numPerPage, page, totalpages, totalRecs
countItem = 0
numPerPage = 50
objRSsg.cursorlocation = 3
if request("page") = "" then
page=1
else
page=CLng(request("page"))
end if
strsg = "select rProd_name, r_id from reseller_prod where rProd_name LIKE '%' + ? + '%' union select Prod_name, '' as r_id from product where Prod_name LIKE '%' + ? + '%'"
With objCmdsg
.ActiveConnection = MM_connDUdirectory_STRING
.CommandText = strsg
.CommandType = adCmdText
.Parameters.Append(.CreateParameter("@sa1", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa2", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa3", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa4", adVarChar, adParamInput, 1000))
.Parameters("@sa1").Value = arrKeyWords(i)
.Parameters("@sa2").Value = arrKeyWords(i)
.Parameters("@sa3").Value = arrKeyWords(i)
.Parameters("@sa4").Value = arrKeyWords(i)
end with
objRSsg.Open objCmdsg, , 1, 2
next
end if
....
....
%>
Update and insert to one table from another
I have two tables:
table1: (ID, Code, Name)
table2: (ID, Code, Name) with same columns
I want to to insert data from table1 to table2 or update columns if that exists in table2 (table1.ID = table2.ID)
What is the simple way to do this?
WHITOUT MERGE
showing 0 result while searching multi keyword string in table column using asp classic
i am searching multi keyword string from two table's column using UNION. it show result if table's column contain all string keywords otherwise it show 0 result.
How to check search string with "or" operator in statement so it will show result for matched keyword from search string and rest will be ignored as it doesn't match.
please help me to resolve this issue.
one more doubt i have that is: will it show result sorting by most matching keywords? mean show the closest matches first?
my code is as below given:
<%
Dim SearchWord, arrKeyWords, Cnt, tsearch, i
SearchWord = trim(request("searcha"))
SearchWord = replace(SearchWord, " and ", " ")
SearchWord = replace(SearchWord, " in ", " ")
SearchWord = replace(SearchWord, " or ", " ")
arrKeyWords = Split(SearchWord ," ")
%>
<%
If IsArray(arrKeyWords) Then
For i = 0 To (UBound(arrKeyWords)-1)
Dim objRSsg, objCmdsg, strsg
Set objCmdsg = Server.CreateObject("ADODB.Command")
Set objRSsg = Server.CreateObject("ADODB.Recordset")
dim countItem, numPerPage, page, totalpages, totalRecs
countItem = 0
numPerPage = 50
objRSsg.cursorlocation = 3
if request("page") = "" then
page=1
else
page=CLng(request("page"))
end if
strsg = "select rProd_name, r_id, (case when rProd_name like '%' + ? + '%' then 1 else 0 end) as [priority] from reseller_prod where rProd_name LIKE '%' + ? + '%' union select Prod_name, '' as r_id, (case when Prod_name like '%' + ? + '%' then 2 else 0 end) as [priority] from product where Prod_name LIKE '%' + ? + '%' order by [priority] desc"
With objCmdsg
.ActiveConnection = MM_connDUdirectory_STRING
.CommandText = strsg
.CommandType = adCmdText
.Parameters.Append(.CreateParameter("@sa1", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa2", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa3", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa4", adVarChar, adParamInput, 1000))
.Parameters("@sa1").Value = arrKeyWords(i)
.Parameters("@sa2").Value = arrKeyWords(i)
.Parameters("@sa3").Value = arrKeyWords(i)
.Parameters("@sa4").Value = arrKeyWords(i)
end with
objRSsg.Open objCmdsg, , 1, 2
next
end if
....
....
%>
Use IF / ELSE IF on STORED PROCEDURE in Sql Server 2005
I have problem with this STORED PROCEDURE in Sql Server.
Any value entered as a parameter extracts data with parameter 'S'.
Can you please help me figure out the problem?
Thanks in advance.
My code below.
ALTER PROCEDURE [dbo].[xxxx](@StartDate VARCHAR(10),@EndDate VARCHAR(10),@Parameter CHAR(1))
AS
IF @Parameter='S'
BEGIN
SELECT * FROM doTable_S
END
ELSE IF @Parameter='A'
BEGIN
SELECT * FROM doTable_A
END
mercredi 11 mai 2016
Invalid object name 'sys.dm_exec_procedure_stats' error
I am trying to obtain the maximum time consumed by a stored procedure in my DB. I obtained a sample query from here to obtain the same using sys.dm_exec_procedure_stats. The same is posted below. Whenever I try to execute this query I get the error as
Msg 208, Level 16, State 1, Line 1 Invalid object name 'sys.dm_exec_procedure_stats'.
Can you please let me know where I might probably be going wrong?
Below is the query used. No changes made.
SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;
EDIT: Sorry for the blunder. Server is 2005.
mardi 10 mai 2016
calculate the time difference using mssql server 2012
i have to attribute from-time and to-time that is data type time
create table timescale ( from_time time,to_time time)
first values is: 12:00 AM
second values is: 05:00 PM
i want to calculate time that how many hours i spend.
lundi 9 mai 2016
Is it possible for java to update MSSQL95 datetime column?
I try to update my MSSQL'95 datetime column with java by trying many sample provided in internet, none of them worked!. Can some one else tell me that's possible or not possible?
dimanche 8 mai 2016
How can I pass sql query to User Defined Function
I have a function as follows
CREATE FUNCTION [dbo].[udf_replaceIntegersWithArabicNumbers]
(@str NVARCHAR(1000))
RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @i INT
SET @i = 1
WHILE @i<=LEN(@str)
BEGIN
DECLARE @val NVARCHAR(1)
SET @val = SUBSTRING(@str, @i, 1)
IF(@val) >= '0' and (@val) <= '9'
BEGIN
DECLARE @newchar NVARCHAR(1)
SET @newchar = CASE(@val)
WHEN 1 THEN N'۱'
WHEN 2 THEN N'۲'
WHEN 3 THEN N'۳'
WHEN 4 THEN N'٤'
WHEN 5 THEN N'۵'
WHEN 6 THEN N'٦'
WHEN 7 THEN N'۷'
WHEN 8 THEN N'۸'
WHEN 9 THEN N'۹'
WHEN 0 THEN N'۰'
END
SET @str = REPLACE(@str, @val, @newchar)
END
SET @i=@i+1
END
RETURN @str
END
I want to update the value of a table column to arabic numerals.i.e. I have a column and I want to update the column value as follows:
column value
------------
1
2
33
column value New
----------------
۱
۲
۳۳
That's why I have written the following query. But it throws an error. Can I do what I want in this way. Is there any other way to do that.
update topicinfo
set topicfullcodearabic = (select dbo.udf_replaceIntegersWithArabicNumbers (select topiccode from topicinfo t1 where topicinfo.topicid=t1.topicid
))
where topicid in (select topicid
from topicinfo t2
where topicinfo.topicid = t2.topicid)
This query causes these errors:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
vendredi 6 mai 2016
SQL Server : whole number without rounding Issue
I am running below query I am expecting value of 300 (I am getting in Excel). I want to get value 300 without rounding.
declare @a decimal(20, 10)
set @a = (1.00000 / 33.00000)
select @a
select @a * 9900 (0.0303030303 * 9900 = 300 in Excel).
In SQL Server, the result is 299.9999. I want to get 300 without rounding. I am using SQL Server 2005.
count maximum occurrence of a value per customer
I have a dataset for a helpdesk system I am building a report for and I need to be able to count the maximum contact attempt for each customer. A customer can have multiple tickets open at any given time.
This is the code I am presently using
select *
from reporting
order by Job_id, seq_id, REason
SEQ_ID job_id EVENT_Name reason account_number 1 70449 Created 10341307 2 70449 Available 10341307 3 70449 Allocated 10341307 4 70449 Rescheduled Failed Contact - Attempt 1 10341307
Above is example output.
The Failed Contact Attempt can occur up to three times with the Attempt number increasing each time. So if a customer has a Failed Contact - Attempt 3 I only want to count that attempt, nothing that proceeds it.
How canI do this?
mercredi 4 mai 2016
Restore database backup avoiding replication
I'm trying to restore a database backup. The original database has replication settings.
When I do the restore using the following:
exec('RESTORE DATABASE [mydatabase] FROM DISK = ''' + @filename + ''', NOUNLOAD, REPLACE, STATS = 10')
It throws an error Database was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
RESTORE could not start database
RESTORE DATABASE is terminating abnormally.
What's worst. My actual replication on my other 3 servers went offline after this...
Subtacting two coloums within the sql query
I have been trying to subtract two coloums in sql server to form a third one.Below is my query
select AD.Id, Sum(APS.Amount) AS TotalDue,
isnull((select sum(Amount) from Activation where InvoiceId in (select InvoiceId from Invoices where AgreementId = AD.Id)),0)
As AllocatedToDate
from AdvantageDetails AD
inner join AllPaymentsSubstantial APS
on APS.AgreementId=AD.Id
where AD.OrganizationId=30
group by AD.Id
What I tried is below but it is not working. :
select AD.Id, Sum(APS.Amount) AS TotalDue,
isnull((select sum(Amount) from Activation where InvoiceId in (select InvoiceId from Invoices where AgreementId = AD.Id)),0)
As AllocatedToDate , (TotalDue-AllocatedToDate) as NewColumn
from AdvantageDetails AD
inner join AllPaymentsSubstantial APS
on APS.AgreementId=AD.Id
where AD.OrganizationId=30
group by AD.Id
At last I tried it using a CTE which worked fine. But I want to do it without creating CTE. Can there be any other way for performing the same functionality. I do not want to use CTE because it is forcasted that there can be other columns which will be calculated in future.
with CTE as(select AD.Id, Sum(APS.Amount) AS TotalDue,
isnull((select sum(Amount) from Activation where InvoiceId in (select InvoiceId from Invoices where AgreementId = AD.Id)),0)
As AllocatedToDate , (TotalDue-AllocatedToDate) as NewColumn
from AdvantageDetails AD
inner join AllPaymentsSubstantial APS
on APS.AgreementId=AD.Id
where AD.OrganizationId=30
group by AD.Id) select * , (CTE.TotalDue-CTE.AllocatedToDate)As Newcolumn from CTE
lundi 2 mai 2016
Quering Sql server to obtain sum total using CTE and joins
I have a below query that I am trying since yesterday with some 33 records of Employee with employeeId on various conditions:
With CTE
(
select EmployeeId, and other colums with joins and conditions.
)
Now I want to join this query to obtain sum of invoices of each employee from below tables. table1 and table2
table1 has employeeid so as my CTE has employeeid I can join it with table1
With CTE
(
select EmployeeId, and other colums with joins and conditions.
) select *, table1.invoiceId from CTE left join table1 on table1.employeeid=CTE.employeeId
left join table2 on table2.invoiceid = table1.invoiceid groupby
but my table1 only have invoices and for each such invoice there are amount spend in other table i.e table2. table2 has a coloumn "amount" that i need to sum up depending upon invoiceid. For more clarity I am writing the table structure or output as below. I am trying like above but they are not showing correct results
Assume CTE has
Emplyeeid empName Empaddress empcode
1 john America 121
2 sandy America 122
Now table1 has
InvoiceId EmployeeId RecordId PAyeeid
1 1 223 202
2 1 222 212
3 1 121 378
4 2 229 987
5 2 345 333
table2 has the coulmm amount that we need for each invoice of epmloyee
now table2
InvLine Invoiceid Amount
1 1 30
2 1 30
3 1 20
4 2 10
5 2 10
6 2 10
The output should be as per employe john has two invoices in table1 ie with Id 1 and 2 and for 1 and 2 invoiceds there are amounts that need to be add up
Emplyeeid empName Empaddress empcode Amount
1 john America 121 80
dimanche 1 mai 2016
in() vs manual equals in sql server 2005
Just want clarifications on who is faster, the
col1 in ('val1', 'val2', 'val3')
or the
col1 = 'val1' or col1 = 'val2' or col1 = 'val3'
right now I'm using the first one because it is more readable.
How can I update a database MSSQL 2005 Express record using the ID field
How can I update a database MSSQL 2005 Express record using the ID field and using VBScript?
I have a script in VBScript:
myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = '" & & Klient_niceform' 'WHERE Klienci_NC.ID =' "& ID_zmienna &" '"
But to update the record in the Klienci_NC.Klient in MSSQL database Klienci_NC I have to type in the box ID_zmienna given number representing the record ID column.
What to do variable it is automatically downloaded to the box?
All my VBScipt script looks like this:
Dim conn, SQL, rs
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=DJ-PC;Initial Catalog=Baza_NC;user id ='user_baza_nc';password='password'"
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
rem myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = 'Hello'"
rem myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = '" & Klient_niceform & "'"
myCommand.CommandText = "UPDATE Klienci_NC SET Klienci_NC.Klient = '" & Klient_niceform & "' WHERE Klienci_NC.ID = '" & ID_zmienna & "'"
myCommand.Execute
myConn.Close
What to do to update the selected record in the database, and not all?
Inscription à :
Commentaires (Atom)