Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1500 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.
I am proficient in Corporate Training. I have designed and implemented complex database architecture, and have also implemented strategies for database high availability and scalability. Furthermore, my core expertise lies in query tuning and performance optimization.
If you want to seek my expertise then drop me a line and tell me about your requirements by using the form below or send me email pinal “at” sqlauthority.com. I value development community and will be happy to help you at any stage of project development, from design to deployment.
Copyright violation and Reproduction of blog:
SQLAuthority.com is trademark of Pinal Dave. Exact work “SQLAuthority” or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave. If any article published on this blog violates copyright please contact me, I will remove it right away. Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, which ever is maximum in event of linking to blog (Link is must).
Search SQLAuthority.com
If you have any questions for faster response, Search SQLAuthority.com. It is possible that your question is already answered in one of the hundreds articles.
Community Rules
- Do not post obscene, indecent, hateful, offensive, defamatory, abusive, harassing or profane material. They will removed.
- Do not post junk mail, pyramid schemes, chain letters or advertisements.
- Do not engage in personal attacks. We have zero tolerance for such incidents.
- Vulgar and insulting nicknames will be removed. Nicknames attempting to impersonate other users will be removed.
- Do not post anyone’s telephone number, street address, email address. or any other personal information.
- Do not post copyrighted material.
- Lively debate and opposing opinions are welcome, but please behave courteously.
- All comments are moderated because of heavy spam activities.
pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com
sir i want to know that is there any solution to get records from two different databases on server with different user name and passwords in one query…
please help me out….I have a very bulky database and now i want to split it in two databases….
Dear Surendra
IF the Databases are on same server, then you need to Add rights of the same user for both DB.
If the Databases are on Different Servers, You need to create a Link Server. There also, you need to create the same user with same password.
Please add me as frnds
Scenario:We have a bulky Database on the server, so we decided to divide the database acc. to branches on their local servers.And Our Main Database will be online.which will be synchronized with local DBs..on weekly or monthly basis..So for this problem m looking for Data Synchronization Solution which can be used in our asp.net MVC Application with SQL Server Express Edition.
I would like to know the method of data synchronization in SQL Server2005 (Express Edition).I hav tried data synchronization using stored proc…bt it has certain limitations…so m luking for sum flawless solution.How should I proceed for Data synchronization in SQL Express(2005) edition.Will Microsoft Data synchronization service or SQL Azure do ny help to me.
hi,
I have a three tables where the information is
countrycode,countryname
1,India
Another table information is
StateCode,StateName,CountryCode
1,Tamilnadu,1
2,Delhi,1
then another table information is
CityCode,CityName,StateCode,CountryCode
1,Chennai,1,1
2,Madurai,1,1
3,New Delhi,2,1
I want the query result as
CountryName,no of states,no of cities
India,2,3
Please let me know as early as possible the tell your suggestion or query for this.
Thanks
Samy
Hi,
I have try to get result from this query,
If any other better query is there, give me ur suggestions,
Select A.CountryName,
Count(Distinct B.StateName) AS [No. Of States],
Count(C.CityName) AS [No. Of Cities]
From CountryMaster AS A
INNER JOIN StateMaster AS B
INNER JOIN CityMaster AS C
ON C.StateCode=B.StateCode
ON A.CountryCode=B.CountryCode
Group By A.CountryName
Thanks
Samy
Hi Samy,
you can use below query also.
select
CountryMaster.countryname,
(select count(StateName) from StateMaster where StateMaster.countrycode = CountryMaster.countrycode)as ‘no of states’,
(select count(CityName) from CityMaster where CityMaster.countrycode = CountryMaster.countrycode)as ‘no of cities’
from CountryMaster
From execution plan from MSSQL
(91% of 100%)
1.Select A.CountryName,
Count(Distinct B.StateName) AS [No. Of States],
Count(C.CityName) AS [No. Of Cities]
From CountryMaster AS A
INNER JOIN StateMaster AS B
INNER JOIN CityMaster AS C
ON C.StateCode=B.StateCode
ON A.CountryCode=B.CountryCode
Group By A.CountryName
(9% of 100%)
2.select
CountryMaster.countryname,
(select count(StateName) from StateMaster where StateMaster.countrycode = CountryMaster.countrycode)as ‘no of states’,
(select count(CityName) from CityMaster where CityMaster.countrycode = CountryMaster.countrycode)as ‘no of cities’
from CountryMaster
Thanks & Regards,
Amit Makwana
select TCO.CountryNAme,(SELECT COUNT(TS.StateID) from tblState TS WHERE TS.CountryID = TCO.CountryID) AS State ,(SELECT COUNT(TC.CityID) from tblCity TC INNER JOIN tblState TS1 ON TS1.StateID = TC.StateID ) AS CityCount FROM tblCountry TCO
Dave i have a bit of a problem, hopefully you, or someone on your blog can help me.
here is my delema:
I have windows server 2008 r2 installed on VMWare fusion 3. And on windows server i have SQL server 2008 installed on it, but when i try to install a failover cluster on it I had no success. So i checked my server manager and noticed that i only have 41 features instead of 42. I downloaded a hardware assisted virtualization patches. Then, i tried the cluster installation again, yet another failure. I updated the R2, I tried to install the “Hyper-V” role, I received a message that reads; the processor on my machine is not compatible. what can i do to be able the install a failover cluster on my sql server 2008……
I have searched the blogs and Internet but could not find the answer for below question. Can you please let me know whether Microsoft support this or not. If yes, can you please send me an article for achieving this? I hope I am not wasting your time.
Question: – How to implement SQL SERVER 2008 R2 Anonymous access?
References: – http://connect.microsoft.com/SQLServer/feedback/details/565010/anonymous-access-with-ssrs-2008-r2-not-working
Hi All,
Usually how much data does DMV’s Hold and how often the data from DMV’s gets cleared. I was under the impression when ever the SQL Server starts or machine (server) starts the DMV’s holding the information will get cleared, is this right if I’m not. Can any clear me with this issue.
Thanks,
Ravi.
Hi Pinal,
Can I schedule a job in SQL to run a Stored procedure which calls a batch file, when there is no users logged into the server PC?
I know that we cannot schedule to run a batch file through Scheduled tasks when there is no users logged on to the Server PC. Is there any work around. Please help.
Thank you in advance.
Raj.
Hi Pinal,
My application consist of region,compaign(IBM,Microsoft,Dell),Line Of business(lob),weekly schedules of employees,Employee Data.User(Company Authority
uploads weekly schedules of the employee by selecting region,Compaign and LOB in dropdownlist.
Should i need to use master table for this,if yes can i use same master for the all the compaigns.
One authority may upload dell copmaign data with 500 employees schedule,another authority may upload microsft compaign schedule.
Give me table struture for this.
EmpNo,EmpName,Monday(date and tiimings in and out)…sunday,Region,Compaign and LoB
Hi,
I am posting my questions where i wiil get my reply in mail or in this blog itslf.Last week i posted my question.
should i need to create master table for scheduling agents for different compaigns(EarthLink,IBM etct) from different regions with more than 500 empoyees in each compaign.
If i need to create master table then can i use same master table for all the compaigns.
Hi,
I’m pretty much new in the xml to sql implementation, I need to import an xml file into sql database. May you please advise as to which is the best way to implement this.
My xml data adheres to the xsd file created from the database table.
e.g
PF
414513
Clermont Tea Room Cash Sale
CLERMONT TOWNSHIP|DURBAN|||000
test@test.com
None
11ad4b3d-d41d-4075-af8a-6de043e4572e
11.990
1.140
COD
Add
How to use multiple update with WITH SQL statement. I’m getting error message invalid object. ?
WITH a_BO(TEST_CUST_NUM, TEST_CUST_TERM_DATE, TEST_B0_NUM) as
(
SELECT a.CUST_NUM, a.CUST_TERM_DATE, b.B0_NUM
FROM PS_TEST a
INNER JOIN PS_VALUE v ON v.UDF_VALUE = a.CUST_NUM
INNER JOIN PS_PERSON p on p.PERSONNUM = v.PERSONNUM
INNER JOIN PS_SI b ON b.B0_NUM = p.B0_NUM
WHERE CUST_TERM_DATE <= CONVERT(VARCHAR(10), GETDATE(), 101)
AND CUST_TERM_DATE ’00/00/00′ a
)
UPDATE o
SET BO_CM_END_DT = x.TEST_CUST_TERM_DATE
FROM PS_CX o
JOIN a_BO AS x
on o.B0_NUM = x.TEST_B0_NUM
UPDATE r
SET ROLE_END_DT = x.TEST_CUST_TERM_DATE
FROM PS_X_ROLE r
JOIN a_BO as x
on r.B0_NUM = x.TEST_B0_NUM;
(4 row(s) affected)
Msg 208, Level 16, State 1, Line 20
Invalid object name ‘a_BO’.
hi sir
i have a table named CompanyMaster have data like
ComId——————-ComName
1————————–Infosys
2————————–wipro
3————————–C
4————————–Dfgf
5————————–Eghtghg
6————————–tyhg
7————————–hgfg
8————————–fytyr
9————————–gfdgtyuy
if i deleted 2 records from middle then it shows like
ComId——————-ComName
1————————–Infosys
2————————–wipro
3————————–C
7————————–hgfg
8————————–fytyr
9————————–gfdgtyuy
here You see that the ComId is not serialize, means i want like this in each delete query
ComId——————-ComName
1————————–Infosys
2————————–wipro
3————————–C
4————————–hgfg
5————————–fytyr
6————————–gfdgtyuy
Plz suggest me, how can i achieve it
thanks
So you want Comid as just a serial number. You can use a row_number() function to do this. For more informations refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
Pinal,
I am working on project where I need help from you. I have created a online banking software which logs transactions. It works fine as online version. One of my customer wants offline version of that. So when there is no internet the will use software from local intranet website where code and database is hosted on their local network machine. They should able to perform all operations in offline database. Once internet is available they should able to synchronize their offline database with online database. While some user working on offline system other members in different region can use online system and add their transaction. So when there is synchronization only those data which are changed in offline database should affect in online database instead of over writing whole.
Quick reply will be highly appreciated.
table name is::CandidateTemp
id FieldName Value
1 email aaa
1 skill c
1 name AAAAA
2 email aaa
2 skill c,java
2 name AAAAA
5 email aaa
5 skill c
5 name GGGG
9 email iii
9 skill c,vb,java
9 name AAAA
and i want this format
id email skill name
1 aaa c AAAAA
2 aaa c,java AAAAA
5 aaa c GGGG
9 iii c,vb,java AAAA
If you want to show them in a front end application, you can do this concatenation there. Otherwise refer this
http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/
Sir,
I required script/procedure to get the details of procedures with parameter in a database in sql 2000
EXEC sp_helptext ‘your_procedure_name’
Hi,
I’m trying to update two columns types from part of a large procedure as follows:
alter table dbo.fakturapos
alter column rabattproz decimal(15,2) null
alter table dbo.fakturapos
alter column nachlassproz decimal(15,2) null
I get the following meldung:
Meldung 5074, Ebene 16, Status 1, Zeile 1
Das Objekt-Objekt ‘DF__fakturapo__rabat__024846FC’ ist vom Spalte-Objekt ‘rabattproz’ abhängig.
Meldung 4922, Ebene 16, Status 9, Zeile 1
Fehler bei ALTER TABLE ALTER COLUMN rabattproz, da mindestens ein Objekt auf diese Spalte zugreift.
The alter statements do work on their own but not in the procedure.
Could you please explain why this is.
Thank’s and kind regards
Wayne
what is decode, encode, translate functions in sql?
i was asked in an interview. Please do email ! thanks
These functions are not supported in SQL Server. You need to search for them in ORACLE sites
Hi,
I need help to solve the following problem:
I have a sql server with multiple databases, all databases have the same tables and every table has the same structure.
Example
Database – 10CT01
Table – CCON
Field – CCONTA
Database – 10CT02
Table – CCON
Field – CCONTA
I need to make an update to the field CCONTA in all tables CCON in all databases begin by 10CT.
Until now I had 10 databases and made the correction one at a time, but now I have 300 databases to correct.
This is the statement that I use now:
USE [10CT01]
GO
UPDATE CCON
SET CCONTA = ‘S’
WHERE CCOD IN (’621′, ’622′)
I am a self didactic in SQL, everything I know I learned to read books and searching in the web.
Can you please help me find the best way I can solve this problem?
Thanks
Sir,
Please tell me the correct syntax for create a PROCEDURE because when i read the book, they all make this part more complex to understand .
Regards,
Create procedure procedure_name
as
Begin
set of codes
End
just to add few points
Create Proc
( )
as
begin
begin try
end try
begin catch
end catch
end
Hi,
Am getting this error when am connecting to my trying to connect my two servers internally which are having Microsoft SQL Server Standard Edition (64-bit). Am doing this for mirroring.
I have two more server with same settings and same SQL version where am able to connect the server vice versa using the computer name as the SERVER NAME. Also here the mirroring is working perfect.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (.Net SqlClient Data Provider)
please help me.
Thanks in advance. as i know You have always have a solution to any SQL query…:-)
me doing a project in .net.i hv created database in which i want to insert some values of a table into anther table.. hw to do
Can you give the structure of both the tables, Neethu?
INSERT INTO target_table(column_list)SELECT column_list FROM source_table
WHERE <somecondtion>
i hv gvn d same query…bt its nt wrkg..
Can you post the code you used?
insert into myaccount(‘”username=
+textbox1.txt+”‘,address=
‘”+txtbx2.txt+”‘,mobile=’”+txtbx3.txt+”,
district=’”+txtbx4.txt+”,state=
‘”+txtbx5.txt+” country=’”+txtbox6.txt+”‘)
select usrename,address,mobile,
distrit,state from register where
state = ‘india’
The code is meaningless
Are you going to take data from text boxes and insert to table or table data from register table and insert into myaccount?
Have you tried this?
insert into myaccount(usrename,address,mobile,
distrit,state)
select usrename,address,mobile,
distrit,state from register where
state = ‘india’
Hi Pinal
If you can write down something about Replication in depth, that would be really great.
Thanks for your help in advance.
Refer this site http://www.replicationanswers.com/ for more informations about replications
Hello,
I am very new to sql server management studio 2008/visual studio 2008.
I develop at work but use perl, jsp jsp etc and have made connection strings but setting the whole framework and getting the connection has been a little rocky.
I have the set up below
Microsoft SQL Server Enterprise Edition
L-06914 server name and computer name
Windows Authentication
getting error 26 cannot locate instance name and can’t seem to solve this. Any help would be great. Thanks,
Hi,
I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
select col from
(
select max(col1) as col1, ‘col1′ as col from table
union all
select max(col1) as col1, ‘col1′ as col from table
union all
.
.
.
select max(col20) as col20, ‘col20′ as col from table
) as t
where col is not null
Hi Pinal,
I have table with data.
EmpNo Date TimeIn TimeOut TotalHours Matchind Day
1 8/1/2010 10:00 6:00 9 hours 10:00
2 8/2/2010 11:00 7:00 9 hours 11:00
3 8/3/2010 Off Off Off Off
Similarly it will have 4 weeks data and for all the remaing employees.
My question-
Find the percentage of employees who are getting off in same day across 4 weeks,3 weeks and 2 weeks.
There are 100 employees
10 employees are getting off in sunday for 4 weeks then percentage is 10.
SImilraly another 20 employees are getting off on saturday Now total percentage is 20+10=30.Off can be any day but we have to check whether that employee is getting off in same day for last 4,3,2 week.Similarly how many employees are getting off like that in percentage..
Please send me sample query on how to acheive it.It is urgent.
Thanq Raj,Madhivanan
i hv gvn d same query before bt its nt wrkg… bt der is no error.
insert into myaccount values(username=’”+textbox1.text+”‘, address = ‘”+textbox2.text+”‘,mobile=’”+textbox3.text+’”,email=’”+textbox4.text+”‘,………….)select username, address, mobile,…..) from register where country=’india’
this is my query.
i did’nt fill the values fully……
insert into myaccount values(username=’”+textbox1.text+”‘, address = ‘”+textbox2.text+”‘,mobile=’”+textbox3.text+’”,email=’”+textbox4.text+”‘,………….)select
username, address, mobile,…..) from register where country=’india’
this the query
The post is unclear. Can you give more details on what you want? Have you seen my previous reply to your question?
Hi , Temme which front end u r using …
Hi Madhivanan,
Can I schedule a job in SQL to run a Stored procedure which calls a batch file, when there is no users logged into the server PC?
I know that we cannot schedule to run a batch file through Scheduled tasks when there is no users logged on to the Server PC. Is there any work around? Please help.
Thank you in advance.
Raj.
As long as the server is on, you can do it
Hi Pinal,
I was going through your blog and I am highly impressed about the amount of effort you have put in and the dedication you have towards your blow. You can add up me in your fan list now.
I am planning to go for SQL developer and SQL administrator certification. I have been learning all these through Video tutorials till now, but to be honest I am still very poor on majorly all the main skill set. So my request is to guide me and help me with your experience as in where to start and what to follow.
I am in desperate need of help.
Thanking you in advance.
Regards,
Premansh
Hi ,
I have experience on sql server 2000 and 2005 of 2 to 3 years
But I have practicle experience.
I mostly going to your site for many sql problem
Sir Is it possible to have a training under you I am only becom graduate. I am ready to pay
Currently I am making project for steel Utensils Manufacturing company which is producing from iron ore to steel untesils
Please Guide me to make my future in It field
I have complete many small projects.
best regards
ajay garg
Hi all,
I have a question and want a support
We can create a Non-Clustered Index which contain a field which is key in Clustered Index
Eg: I create a table has 3 column
Create table A
(
ReportDate
MerchantNumber
MerchantName
SaleCount
)
Then I create a Clustered Index P on ReportDate column
So, Can I create a Non-Clustered Index A on ReportDate,MerchantNUmber?
Thanks,
Hi,
I have to design a database for storing 3 different types of transactions.
I have thought of two different approaches
Style 1 : Make 3 master table for 3 different transaction type
store data in 3 different transaction table related by their respective ids
Ex. master 1 : (id, commodity)
(1, pen) (2, pencil) (3,rubber)
master 2 : (id, commodity)
(1, monitor) (2, keybord) (3,ram)
master 3 : (id,commodity)
(1,nail) (2,ply) (3,adhesive)
transaction 1(id, customerid, masterid, value)
(1, 23, 2,1000) (2,23,3,500),(3,24,1,1000)
transaction 2(id, customerid, masterid, value)
(1, 23, 2,2000) (2,24,3,500),(3,24,1,1000)
transaction 3(id, customerid, masterid, value)
(1, 23, 1,2000) (2,24,3,500),(3,24,1,1000)
no. of records expected in each master : 20
no. of records expected in each transaction : 18 lakhs
*******************
Style 2 : Make 1 master table for store different transaction type with their ids for commodity type also and store all transactions in one table
master 1 (id, commoidtytype,commodity)
(1, S,pen) (2,S,pencil) (3,S,rubber)
(4,C, monitor) (5,C, keybord) (6,C,ram)
(7,H,nail) (8,H,ply) (9,H,adhesive)
transaction 1(id, customerid, masterid, value)
(1, 23, 2,2000) (2,24,3,500),(3,24,1,1000)
no. of records expectein in master : 60
no. of records expected in transation : 60 lakhs
which is better approach in terms of :
1) speed of operation at the time of page load….
any help is highly appreciated
Thanks in advance
Hello everybody,
I have a question and want you guys to help me find the solution.
I want to find the last two records of a table. I know that using an ID column we can do it. But is it possible to display the last two records without using the ID column.
For example, I have the following table TEST and it has the columns NAME and ADDRESS.
The columns contains the following records :
name address
xyz abc
bbb bcd
yyy cde
eee efg
fff fgh
yzz ghi
hhh hij
aaa ijk
Is it possible to write a query which will display the last two records of the above table, i.e., I want to display :
hhh hij
aaa ijk
I tried by using the following query :
select top 2 * from em order by ename desc
but it displays the following results :
yzz ghi
yyy cde
Please help me with this.
Regards,
Rituraj
A change in last few lines :
I tried by using the following query :
select top 2 * from TEST order by NAME desc
but it displays the following results :
yzz ghi
yyy cde
Please help me with this.
Regards,
Rituraj
Hi Rituraj,
This is impossible if you use NAME or ADDRESS to get the last 2 records.
I suggest you should use a temp table. The table consists of a field ID is IDENTITY. Then you insert data into the table and use this query
SELECT TOP 2 * FROm #tmp ORDER BY ID Desc
Another way, you can use CURSOR to seek to the last 2 records. However, it’s not good if your table contains large data.
Hope that can help y.
Regards,
Thank you Dung for your response. It help me a lot.
With regards,
Rituraj
Without any unique it is not possible. Do you have any unique key? If you use version 2005, you can use row_number() function
select top 2 * from
(
select *,row_number() over (order by (select 0)) as sno from table
) as t
order by sno desc
Thank you Madhivanan for your response.
With regards,
Rituraj
hi i am unable to retreive records form a table based on the date specified by the user… there are 3 dropdown menus from which i get the day.month and year from the user .. if i write the select query then it gives conversion failed while converting datetime from character string exception…
i tried mm/dd/yyyy , dd/mm/yyyy , yyyy/mm/dd and date .parse function but still couldn’t retreive the records … but still i get the exception..
hope u can help me ..
regards..
Make sure you read this blog about about DATETIME datatype and handling date formats
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
I have an application that requires a lot of calculation, there are a lot of clients that uses that application having separated DB’s. Now I am going to develop another web-application that will get the records of all those databases and on the basis of filter will display the results. I want to ask you a question that
1. Should I change the structure of the application in which all of my clients share a same database and from where I could get all the records I needed and perform calculation on them?
2. Would I keep the all databases separate (as it is condition) and develop a service that will fetch the records from the entire database into one from where I would perform my calculation and display the data.
Do also tell me if there is some other solution to this application architecuture.
hey Pinal Dave,
I have some questions related to sql azure i wish if you can help me with these.
the questions are:
• • Is remote access to an SQL Azure™ database supported? If yes, is there an impact on performance?
Can two separate SQL Azure™ databases access each others’ data using the “USE” statement?
select namee,date,sum(totle) from table where namee=’ali’ group by namee,date
this not working mean not give sum as it is?
What did you mean by not working?
i mean this is not give sum as it should be
Try this
select namee,dateadd(day,datediff(day,0,date),0) as date ,sum(totle) from table where namee=’ali’ group by namee,dateadd(day,datediff(day,0,date),0)
Hi Pinal,
I’m working as Microsoft SQL server DBAdministrator for 3 years in SQL 2005 as well as in SQL 2008. Hence i would like to do certification in one of these (i prefer in 2008 ..since 2005 is little older). Can you please let me know what all i need to do and any material for preparation?
I heard that 70-431 is enough for DBA .. is this true ? or let me know what all i need to do to complete this.
Your quick response is appreciated.
Thanks,
I wanted to thank you for the useful articles in your site,
Please help me solve deadlock in SQL server, I already posted it in different blogs but no answer yet
the scenario:
We have a database server running SQL server 2000 Win Server 2003 which manages couple of huge databases and hundreds of simultaneously connected clients to them. Number of transactions done by multiple large size applications on these databases are enormous and we did not have much problems for a long period with partially same data and transaction volume.
Recently, server has encountered lots of blockings in its processes and finally deadlocks which made the server halt… the number of deadlocks is increasing day after day and server halts are becoming a serious headache: before we had once a month, now we have twice a day.
We have set multiple trace flags on server to generate and capture logs, used profiler and installed advanced SQL analysis monitoring tools. Finally, we found out that deadlocks are mostly caused by similar simultaneous queries blocking each other’s in table access, but the queries and the tables are different for each deadlock and each query may come from a different application or client. Following is a typical blocking session that caused deadlock and server halt:
- Blocking Process A ran at 9:36:11, Waiting time(ms): 469313, SQL Query: UPDATE TableX
- Blocked Processes B ran at 1: 9:36:12, Waiting time (ms): 436094, SQL Query: INSERT INTO TableX
The SELECT statements are locked and unfortunately we cannot change them by adding ‘nolock’ access modifier since they are all called from third party applications. The waiting times for most of blocked processes are above 400,000 mille seconds (more than 6 minutes!). Our servers are high performance, wealthy in CPU and Memory resources and the SQL has a huge memory allocated to itself that never reached to its limit! All we can do is to set changes directly on database, SQL Server settings, network and hardware.
So far, we have come up with the following solutions (which are not the ultimate cure) and we still did not apply them since the database is huge in size and design detail and we better get sure before any action:
1- reduce the LOCK_TIMEOUT in db that applications do not wait a long time
2- Index all fields in blocking tables to shorten query times
If you can suggest an avenue of search or an alternative to prevent or minimize this error it would be appreciated.
Thanks, and thanks again for useful articles…
A.Oveissian
why it happened please tell me
not give sum as sum is?
Hello Sir
I have an urgent requriement to update my database to Sql Server 2005 and Sql Server 2008. So I am looking to have detailed notes for updating sql server 2000 to sql server 2005 and sql server 2005 to sql server 2008. Therefore I request you to mail me the steps to proceed.
Thanks in Advance.
V.Santosh Kumar.
Search for Upgrade adviser in Google/Bing
Parrallel demand for exclusive access to tables is causing this but we can not do anything for it. I’m looking for the reason why it started happening since 2 weeks ago only.
Please let me know if I must check something else as well…
Hi,
i am facing a problem as following
a table TB1 contain column
AccountCode
AS1
AS10
AS12
AS13
AS14
AS15
AS16
AS17
AS18
AS19
AS2
AS20
i want sort in following format
AccountCode
AS1
AS2
AS10
AS11
AS12
AS13
AS14
AS15
AS16
AS17
AS18
AS19
AS20
can any one help me, given query in my mail ID
[email address removed]
Refer this post to know more methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx
Hello Sir
I have an urgent requriement to update my database to Sql Server 2005 and Sql Server 2008. So I am looking to have detailed steps for updating sql server 2000 to sql server 2005 and sql server 2005 to sql server 2008. Therefore I request you to mail me the steps to proceed.
Thanks in Advance.
V.Santosh Kumar.
Dear Sir,
I have some problem while I am at last phase of my development. I have one module where I am backup and restore the database to network location. Here problem is that it is work successfully when the all PC’s are in the domain, but it is not work while I am doing same task at the PC which is not in the domain. Please give me some guidance to take backup and restore at without domain location….
Thanks in advance,
Ankit Shah
If the system is not accessible to your Server, it is not possible to copy backup to it
Sir, Please help me with this, i have a table with the follwoing fields:
Date
Duration (in Months, eg 6, 12 or 18)
StartDate
EndDate
from StartDate, I want to find out last date of the month as EndDate, which is determined by Duration.
Regards.
select dateadd(month,duration,EndDate) from your_table
Hi Pinal,
Can you suggest me some script which can attach multiple databases (no of databases is morethan 27 ) in sql 2005
Thanks,
Krishna
You need to write a customized code that invloves a loop
Hi Pinal! can u help me? i have 500 users with login and pathword in excel format. how can i automatically enter these logins to database?
Hi Pinal,
Here I am again.
I just want to know stored procedures / functions or views to list users in a group, also how to add / drop user from a group.
Thanks in adv. for your time and knowledge.
Shoaib
Please anybody give answer to my above query?
Hi Pinal
I use query like this but i use 4 tables
tblA have relation with tblB
tblC have dont have relation with tblD
i want to with INSERT INTO-SELECT statement
copy data from tblC, tblD To tblA,tblB
but i have error that say constarint error for parent and child rows…
i want to know which row id has error or which data cant to transfer data from first 2 table to another 2 table
INSERT INTO tbl1(a,b,c)
SELECT a,b,c FROM tbl2
and i have
Hi Pinal,
I have a problem regarding with SQL Server 2000 Standard sp4 on Windows server 2003. The application running above this is Eric (ERP System). There are a couple of instances wherein we got this error: db process is dead
also when we try to use the query analyzer there are instances that we got disconnected. the connection will automatically be recovered after a few minutes.
Can you help me solve this issue? we also had this problem with a SQL Server 2000 enterprise edition on windows server 2003….. Please help.. :D
Hi Pinal,
I am using MSSQL Server 2005, master db is corrupted, I am trying to rebuild the master db by using following command on command prompt :-
D:\start /wait d:\sqlserver2005\setup.exe /qn instancename=mssql$Taint reinstall=sql_engine rebuilddatabase=1 sapwd=asd123
when I press enter cursor start blinking and after few minutes it comes on next line without giving any notification.
Then I try to start MSSQL server using following command on command prompt:-
D:\net start mssql$Taint -m
But it does not start, it throws an error :
The sql server service could not be started.
A service specific error occurred : 17113.
I tried to search the error’s cause in events viewers of windows and searched in error log file of SQL but could not found appropriate solutions.
Please suggest if I am doing something wrong.
Thanks in advance. :)
Hello sir,
I am transfering my data from one db to another. While mapping process my query is slow.
DECLARE @UpdateCursor as CURSOR
declare @product_id as int
DECLARE @product_name as NVARCHAR(MAX)
SET @UpdateCursor = CURSOR FOR
select product_id,product_name
from kemchhorajkot.dbo.product_master
OPEN @UpdateCursor
FETCH NEXT
FROM @UpdateCursor INTO @product_id, @product_name
WHILE @@FETCH_STATUS = 0
BEGIN
–select @product_id,@product_name
update CoreMaster
set TempRCoreId=@product_id
where CorePName=@product_name
–UPDATE CompanyProduct
–SET CProductID = (SELECT top 1 CProductID FROM CoreMaster WHERE CorePName=@CorePName ORDER BY CProductID)
–WHERE CProductID IN (SELECT CProductID FROM
–CoreMaster WHERE CorePName=@CorePName)
FETCH NEXT
FROM @UpdateCursor INTO @product_id,@product_name
END
CLOSE @UpdateCursor
DEALLOCATE @UpdateCursor
Plese help me in this query, its too much slow and time consuming.
Thanks
You can apply this logic
upadte t
set col=s.col
from source_tabe as s inner join target_table as t
on s.keycol=t.keycol
I am a very basic SQL user. I use SQL as a back-end database with a front end user interface using MS Access. I have screens where the user wishes to type in text with paragraphs – using carriage returns.
The data fields I have for them to do this are set-up as varchar(max). If they type the text in Word and then paste it into the Access interface, the formatting of the paragraphs is preserved.
However if they just type in text directly in the Access interface and try to use the carriage return to start a new paragraph, Access thinks the user is trying to advance to a new record.
Is this a problem you can address and if so, what would be the cost? It would also be helpful to know what you think would be required to fix this problem.
Sir,
We have a unique proble with sqlserver 2005 over a cluster (Active/Active) on MS 2008 64bit server.
It always happen without any exception that first time connection to SQL Server Manager fails and give following error: (2nd try is always successful)
TITLE: Connect to Server
——————————
Cannot connect to MSHSQLCLSTA\PR100.
——————————
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
Kindly advise the resolution.
SIH
I have an application that requires a lot of calculation, there are a lot of clients that uses that application having separated DB’s. Now I am going to develop another web-application that will get the records of all those databases and on the basis of filter will display the results. I want to ask you a question that
1. Should I change the structure of the application in which all of my clients share a same database and from where I could get all the records I needed and perform calculation on them?
2. Would I keep the all databases separate (as it is condition) and develop a service that will fetch the records from the entire database into one from where I would perform my calculation and display the data.
Do also tell me if there is some other solution to this application architecuture.
I have transactional replication from one server to another and need to shut the servers down. How can I ensure that this goes smoothly?
Do I have to stop the agents on the Publisher and Distributer first? Which order should I shut the servers down? Do you have a checklist of tasks?
am trying to create a SQL temp table and populate it with data from a excel.csv file. The file is being uploaded from a ColdFuison page. I have set my SQL variable. I used your code with a couple of changes. Below is my code.
CREATE TABLE #pmwtTable
(circuit VARCHAR(50), ban VARCHAR(50), clli VARCHAR(50), uvClass int)
BULK
INSERT #pmwtTable
FROM @file
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
the SQL errors:
Incorrect syntax near ‘@file’.
Thank you for your help.
You need to use dynamic sql for this. Assign the statement to a variable and execute it
Sir
I have problem in connectivity to SQL Server. Please find the current structure.
Domain Account: Windows 2003 server: IP 192.168.0.6
SQL 2005 Server : IP 192.168.0.8
Authentication is Windows authentication.
When I set DNS 192.168.0.6 as primary and 192.168.0.8 as secondary, working fine.
I have to set another IP for Internet Service Provider as primary DNS. When I change the order of windows domain account server to secondary, it triggers connection time out.
I want to know whether IP address of SQL server installed machine must be primary DNS or not?
Thanks & regards
Abdurahman
Hi pinal
Want to find highest from emp(eid,department,salary)
Refer this post to know more methods available
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
Hi Pinal
want to find highest salary from emp(eid,department,salary).
select max(salary) from emp
I’m using asp.net default membership, but now I realized that using uniqueidentifier as user id is useless and I want to convert uniqueidentifier column to int. But the column got many relations with other tables. I cant manually change the DB structure and update already exists row one by one. However, I thought to do that using queries, so any idea ?
Want to find highest from emp(eid,department,salary)
without using any sql function
Why do you not want to use a sql function?
select top 1 salary from emp order by salary desc
Hi Pinal
I’m developing a ticketing system for 50 bus company. There are possibility of 1million ticket transaction in a month. My question is which are a preferred way to design database, either separate database for each company or manage all company in one database? FYI the company will use the system as a service not own the system
Thanks
Hi,
I am getting an error
Login failed for user(null)
Reason:Not associated with a trusted SQL Server Connection.
I am using SQL Server 2000
I have 2 servers
ServerA(DatabaseA)
ServerB(DatabaseB)
I try to do the following
ServerA——>DatabaseA—–>Storedproc inserting a record in Database B of ServerB
Please help.
Hello Sir,
i got this error when i tried to restore the database.
help me to fix the problem.
TITLE: Microsoft SQL Server Management Studio Express
——————————
Restore failed for Server ‘CLIENT193\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file “C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\MedicalStore.mdf” failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
——————————
BUTTONS:
OK
——————————
Hi Pinal,
I have to remove the duplicate records
Say I have a table emp(name, sal)
Please suggest me a query for SQL server.
CREATE TABLE [dbo].[SGA_INVOICED_SALES_Cumulative_YEAR](
[0SHIP_TO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[0BILL_DATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[0SALES_ORG] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sales] [numeric](18, 2) NULL,
[COST] [numeric](18, 2) NULL,
[0DOC_TYPE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON cview_PartitionScheme(SELECT left([0bill_date],4));
hi Pinal,
is it possible to use left while creating a partioned table as above example
Hi Pinal,
We’re having connectivity issues with Active/Active SQL Server 2008 Enterprise Edition Cluster running on Windows Server 2008 R2 Enterprise Edition. Where we’re seeing disconnects prior to the connection timeout as well as issues similar to the issues with TCP Chimney Offload as described in http://blogs.msdn.com/b/psssql/archive/2010/02/21/tcp-offloading-again.aspx. Although their recommendations have not resolved our issues. Have you had any experience with this?
Additionally, when we failed the instance having issues to the other node on the cluster the connectivity issues disappeared.
Thanks in advance,
Jeff
Hi,
I want to do insertion from another table but with some kind of datatype conversion like in my original table I took varchar and my old table has nvarchar or in my old table I took date and my old table has varchar.So I want to fire a query with datatype conversion?
I appreciate if any onc help me out.Thanks
Hi:
I just want to know if there a form of knowing if a column in a sql server table is encrypted or not?
Thanks,
Jose
Hi Pinal,
My Question is I want to remove the old empty partitions and want to add new partitions with new range.
My Main concern is how to findout empty partitions and merge them.
Suggest me on this
Here is my question. I’m writing a stored procedure in which I’m using a select statement which accepts two parameters: one which identifies the column of interest, strColumnName ,and the other which specifies the particular value, @NutriVal, of the rows I want returned to my data grid. The select statement I was attempting to use is as follows:
set @SQL = ‘SELECT Food_Name, MType, ‘ + @strColumnName + ‘ FROM tblNutritionalData WHERE ‘ @strColumnName = @NutriVal
Unfortunatly this statement isn’t working. What do I need to do to return the rows to my datagrid that contains the particular value, @NutriVal?
Thanks in advanced,
WBM, Jr.
You need to make use of dynamic sql. Refer this post to know a method of what you want to do
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
Hi,
I saw your article regarding INSERT INTO statement in SQL. I have a similar problem with my project.
I am trying to insert ID from table A to table B when record is inserted in table A.
I am using the following stored procedure:
PROCEDURE [dbo].[InfoInsert]
– Add the parameters for the stored procedure here
@UserId uniqueidentifier,
@Title char(32),
@FirstName char(32),
@LastName char(32),
@CompanyName char(32),
@Email char(32),
@DealerID int
AS
BEGIN
INSERT INTO [tblDealers] ([UserId], [Title], [FirstName], [LastName], [CompanyName], [Email])
VALUES (@UserId, @Title, @FirstName, @LastName, @CompanyName, @Email)
INSERT INTO [Categories] ([DealerID]) SELECT tblDealers.DealerID FROM tblDealers WHERE tblDealers.DealerID = @DealerID
END
It is not inserting value in using WHERE tblDealers.DealerID = @DealerID statement. But if I change @DealerID to let’s say ’547′ (which is a DealerID in tblDealers table) it executes above INSERT INTO statements separately and inserts two rows in Categories table. Whar I want to do is to create one row in Categories table with DealerID-CatCode in the same row.
It is obviouse it can not retrive DealerID based on @DealerID but I don’t know how to resolve it.
Thank you.
i want to know that
can i Create Live Test server which get Live Data from Live Database server
i have one Live database server but when i want to use live database for testing i have to take backup it and restore on test server i do this process everyday to getting Live updated data
is there any way that live Database automatically Transfer to my Test server and whatever data changed in Test server could not be replicate on the Live Database server
Have you tried replication?
Do you mean Database Mirroring?
Hi Pinal,
I am using following pivot query getting result as 4 rows but i want it as 1 row.One more thing is if i pass dates as parameters to stored procedure i am getting error Incorrect syntax near ‘@date1′.This is my query and stored procdure
Incorrect syntax near ‘@date1′
Result should be like this
HeadCount 8/1/2010 8/8/2010 8/15/2010 8/22/2010
Forecasted 192 198 178 189
But iam getting values daigonally as 4 rows
My query
create procedure Sample(@date1 datetime,@date2 datetime,@date3 datetime,@date4 datetime,@CampaignID int)
as
SELECT ‘Forecasted’ AS HeadCount,
[@date1] as date1
FROM
(SELECT *
FROM SAR_HeadCount) AS SourceTable
PIVOT
(
SUM(HeadCount)
FOR StartDate IN [@date1]
) AS PivotTable;
create procedure Sample(@date1 datetime,@date2 datetime,@date3 datetime,@date4 datetime,@CampaignID int)
as
SELECT ‘Forecasted’ AS HeadCount,
[@date1] as date1
FROM
(SELECT *
FROM SAR_HeadCount) AS SourceTable
PIVOT
(
SUM(HeadCount)
FOR StartDate IN [@date1]
) AS PivotTable;
You need to use Dynamic Pivot as described in this blog post
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
dave.
issue with sql 2005
Hi Dave,
i need your or any one who can help me with sql 2005 T-log
we have 3 internment in 3 different areas.
master
replication
mirror
our T-log backup start growing from 400 mk to 2,2 GB and it cause a lot of issue with Replication and mirror that can not catch up with production master DB its always behind for 3 to 4 hours.
i need help to understand what is casing t-log to jump from 400 mg to 2 GB
and our job for T-log backup run every 15 min and its 2 GB
all help will be appreciated.
Hi,
Unable to pass parameters to stored procedure with pivot.This is my stored procedure
alter procedure Sample(@date1 datetime,@date2 datetime,@date datetime,@date4 datetime)
as
SELECT ‘Forecasted’ AS HeadCount,[@date1],
[@date2],[@date3] ,[@date4]
FROM
(SELECT
StartDate,HeadCount FROM SAR_HeadCount)
AS SourceTable
PIVOT
(
SUM(HeadCount)
FOR StartDate IN ([@date1],[@date2],[@date3],[@date4])
) AS PivotTable;
StartDate is DateTime datatype only but still getting erro
Error is
Msg 8114, Level 16, State 1, Procedure Sample, Line 3
Error converting data type nvarchar to datetime.
Msg 473, Level 16, State 1, Procedure Sample, Line 3
The incorrect value “@date1″ is supplied in the PIVOT operator.
I would like to have a high level discussion with you about SQL DBA work, SQL Programming, SSIS/SSRS vs. BI tools like Micro Strategy for designing/maintaining information portals.
I am the company owner for both Digitalrep (Retail Consulting) and Pixelstreams (IT SaaS services) and we’re a Microsoft SPLA shop with 20 employees.
I’m specifically looking for advice that will help me determine strategy and best practices for future growth. This may lead into a consulting agreement, but I’m not sure how you engage with web based leads.
fyi.. I’m not a SQL expert, so our conversation my just be limited to describing our current environment, data warehousing, reporting tools, etc.
Thank you in advance for your time and consideration.
hi
I want to create a link server. In one machine I have sql server 2008 and in another machine I have sql server 2005. Now If I create a link server on 2008 to 2005 then It will me a network or instance related error.If I switch from 2005 t0 2008 then it gives me a version error. Even both the server are not connected each other like If I want to connect from sql server 2008 to 2005 then and then it gives me an error.I appreciate if anyone help me create link server with different version.Thanks.
Hi,
I have 4 weeks data.
Can u tell me how to find reports for across last 4 weeks,3 weeks and 2 weeks and 1st week,2nd week,3rd week and 4th week.
Across 4 weeks should include data among the 4 weeks.
4th week means only 4th week.
Just give me the structure on how to do that.
Dear Sir,
Due to some error in installation my SQL Express 2005 did not install completely. I tried uninstalling the components by using “Add or Remove Programmes”. All the components were removed successfully but SQL Server VSS Writer is not getting removed. The error message says sqlwriter.msi file is not available in the installation package.
Since I downloaded it from the net I do not have an installation CD. Please help me in removing the VSS writer so that I can re-install SQL Express 2005 afresh.
Regards,
Shaibal.
Hi,
I am getting error because of this line in stored procedure.if i give integer value its working but when i enclose in quotation i am geting error.25:0 is string datatype
Count(Case When Totalhours<='25:0' Then EmpNo end)
ALTER PROCEDURE [dbo].[SAR_Sp_GetScheduledHours](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
Count(Case When Totalhours<='25:0' Then EmpNo end) as Less
FROM TestTable1 group by WeekStartDate)
AS SourceTable
PIVOT
(
max(Less)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable'
EXEC(@query)
END
Hi,
In Pivot can i have multiple selection.from table i am getting data based on the criteria for each criteria i want to select data for each week.Like this
PIVOT
(
max(Less than 2 hours),max(greater than 2 hours)
for WeekStartDate in ([],[])
)
SELECT WeekStartDate,
Count(Case When Totalhours=’35:0′ Then EmpNo end) as greater than 2 hours
FROM TestTable group by WeekStartDate)
AS SourceTable
PIVOT
(
max(Less than 2 hours),max(greater than 2 hours)
FOR WeekStartDate IN (‘ + ‘[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ + ‘)
) AS PivotTable
Good Morning Pinal -
I need your help regarding one of my requirement in SQL SELECt query..
I want to pull Birthdate between given day and month range. I don’t want to consider year at all whatever the year is it doesn;t matter.
Let’s say i want to pull birthdate between 11/10 to 20/12 (dd/mm) format. (i.e., 11/10/1990, 18/12/2001, 19/11/2010 and so on)
I have tried by using Month and Day function with BETWEEN operator but it’s not giving me correct result.
Let’s say, if someonce birthdate is 09/11/2008 or 05/12/1990 or 10/12/2010 (dd/mm) then this not pulls into result..
It’s urgent..
One way is
where day(birthdate)=9 and mon(birthdate)=11
Hi
Pinal
I got help many time from your site. its very useful site and i realy like your your help for sql server queries.
i have database server1 having sql server 2005 dev edition.
I have another server2 having sql server 2005.
Transaction take place in sql server1 , i want all transaction will also be place in sql server2.
can you please help me how can i do this.
i mean by replication or by clustering or any other way.
if any of them then how?
I’ll be very much thanks to you if you help me regarding this matter.
Abdul Jabbar Patel
Read about Replication in SQL Server help file
Hi All,
I am getting error in Stored procedure when passing TotalEmployee
Please check words in Bold.Also i am getting result as percentage value without percentage symbol.Where to percentage symbol.(for example i am getting 75 i should get 75 %)
Error is -Must declare the scalar variable “@TotalEmployee”.
alter PROCEDURE GetSchedule @date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignID INT AS
DECLARE @query VARCHAR(MAX),@TotalEmployee int
BEGIN
set @TotalEmployee=4
SET @query = ‘SELECT ‘ + CHAR(39) + ‘b/w 24 and 40′ + CHAR(39) + ‘ AS TotalHours, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ +
‘FROM
(SELECT WeekStartDate,
convert(varchar,dbo.FDiv(Count(Case When Totalhours>=24.00 and TotalHours=40.00 Then EmpNo end),@TotalEmployee)*100) as empno
FROM TestTable4 group by WeekStartDate) AS SourceTable
PIVOT
(
max(empno)
FOR WeekStartDate IN (‘ + ‘[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ + ‘)
) AS PivotTable’;
EXEC(@query);
END
CONVERT(VARCHAR,@date2,101)
should be
CONVERT(VARCHAR,’+@date2+’,101)
Hi All
I would like to write a trigger on insert or update
so it will write the recored from the table in the MS SQL database to the table in Oracle
can some one help me in that
the trigger must be written inthe MS SQl database and it should write the record to the Oracle table
Regards
You should not be doing this in a trigger.
Hello, im a Trainee Developer need ur help
I have a table Student_INFO where S_ID,S_UNI,S_ROLL are Columns S_ID is a PK
S_UNI have string values ‘UIT’ now in S_ROLL i want ‘UIT-0001′ automatically generated. I tried the formula ['UIT' +S_ID] but it doesn’t generate give INT value error.
Pleas help me out.
thanks :)
Use identity column with int datatype and do the formation in the front end application
Hi,
I want to know about finding Discrepancy between tables records.
I have 2 tables.1st table is static table and the other table I am forming from 15 different tables using joins.
Now i want to compare results between these 2 and want to diaply records if there is any discrepancy in any of the field. Both are having same no of fileds.(say at least 150 fields)
Thanks for your help.
Thanks,
Hitendra
Try this
select * from table1
except
(
your big query
)
Hi,
In my table I have a varchar type column, the values like..
sale_date
04/23/2010
02/03/2010
12/24/2010
I need to retrieve the records in both ascending and descending order. Problem is now it is a varchar type if I use order by o/p like
02/03/2010
04/23/2010
12/24/2010
I tried with convert(DATETIME,sale_date) but no use.
You need to use proper DATETIME datatype to store dates. Refer this article for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/29/understanding-datetime-column-part-iv.aspx
try this code
set dateformat mdy
select columns from table
order by convert(DATETIME,sale_date)
u use convert(datetime,saledate,parameter) like this
order by convert(datetime,’saledate’,103)
Hi
I have a problem when i tyr tp backup the transaction log file i find this error Backup log cannot be performed because there is no current database backup
I think you need to take full backup of the entire database before doing this
Hi Madhivanan,
I have one doubt for ‘SP_HELP’
I have given “50″ the column length, when create a table,
Create Table Temp121010
(ID INT,
Description NVARCHAR(50))
After created table then i’ve use ‘SP_HELP’ query, its displayed that column length is “100″
{SP_HELP Temp121010}
Can you tell me that reason, why appears?
Thanks
Samy
It shows the length in bytes. As nvarchar takes two bytes to store a single character 50*2 becomes 100
Hi Pinal,
iam trying to find the missing sequences numbers
the values are 1, 5000000,99865754 like that
Please help to find the missing sequences numbers.
Regards
kalyan
Search for “Find gaps” in this site
Hi Sir,
i have two table t1 and t2
in t2 i created dual primary key on two columns
i want to insert t1 table data to the t2 table.
bt before inserting t1 table data to the t2 table
i want to create report of data which will not going to insert in t2 table
Means because of dual primary key of t2 table ,only unique records will insert ,nt duplicate.
nw in this scenario before inserting i want to create report of data which is not going to insert?
I used after insert,for insert but its giving data which is not going to insert in 2nd table after inserting records.
Please help me.
Thanks in advance
select t1.* from table1 as t1 where exists (select * from table2 where keycol=t1.keycol)
Hi,
I have 4 weeks data of employees.
Can u tell me on how to find no of employees who are getting weekoff in sameDay.For example If the emp 101 is getting week off in same day say saturday or any day consistently for all 4 weeks then we can count is as 1.
In the below table we have to get data based on MatchingOff.It will have TimeIn value if the TimeIn if Off then MatchingOff Column value will be Off.
Tablestructure
EmpNo Date TimeIn TimeOut MatchingOff
Post some sample data with expected result
Sridhar Kani.N
Hi Sir,
I need query for
multiple table insert in single query using sql
Plese help Me
Regards
Sridhar Kani
It is not possible until you use Merge statement
Hi Sir,
Sir i want to know that can i use if statement on place of case statement in select statement. If yes then how can i do it.
No you can’t use if inside a select statement
SELECT iMap_TeamScan_Temp.ID, iMap_TeamScan_Temp.PlantID, iMap_TeamScan_Temp.TeamID, iMap_TeamScan_Temp.SRPID, iMap_TeamScan_Temp.ScanDateTime,
iMap_TeamScan_Temp.Trn_User, iMap_TeamScan_Temp.Trn_Date, iMap_TeamScan_Temp.Status,
iMap_TeamScan.SRPID AS SRPID2
FROM iMap_TeamScan_Temp LEFT OUTER JOIN
iMap_TeamScan ON iMap_TeamScan_Temp.SRPID = iMap_TeamScan.SRPID
WHERE (iMap_TeamScan.SRPID IS NULL)
I use join to insert avoid “Duplicate insert”. Also I can use a loop to insert row by row while checking if any duplicates are inserting.
Which is the best inorder to avoid low performance.
Sameera,
You must be inserting into table2 from Table1 which not in Table2.
Use : NOT EXISTS clause or EXCEPT set based operation
hello sir
please tell me
how to use in sql server compact edition 4 in use javascript .
with activex object .
Hi ALl,
I have 4 weeks employee data with table columns EmpNo,MatchingOff,atnDate.
MatchingOff column will have TimeIn value if the TimeIn is 0 then MatchingOff column value will be Off.Based on this i need to check If the employee is getting weekOff on sameday for all weeks if yes then increment count.Similarly how many employees are getting weekoff on sameday then increment the count.
You need to post some sample data with expected result
Hi,
I am currently setting up a SQL Server in my laptop using SQL Server 2008. However i dont know how to create a SQL server. I can create only SQL database.
I have worked in my client test environment and they have a Server and inside that they installed SQL server. Here in my case, is my laptop is acting as server?
Can you please clear this basic fundamental doubt?
Regards,
Divakar Ragupathy
If your laptop has SQL Server 2008, you have already server you dont need to create any server
Hello Sir,
i have table called Employee and i am trying to load data from one SP to this table, earlier it did not gave me error but its giving me error now like “violation of constraint of PK_Employee” it means duplicate data can not be added so i checked the PK_Employee which is clustered key on index for “Calldate “column and “Employee ID” column , Employee ID column is having unique values while Calldate Column have duplicate values but earlier it did not gave me error when i ran that SP to insert Records
so i dont understand why its giving me error now as each record is different with unique Employee ID , n i tried to disable the clustered index key but then i can not access that table with even simple select query, so what should i do to Add the data in this table ?
Please help me soon,,
i would appreciate that
As error says, you need to check the existance of the value before adding to the table
HI
In my procedure i am connecting with a linked server. how can i check the connection status of linked server
Hi Pinal
I have a table with Bit column. I want to write a sp which has a bit parameter. Now I want to create a query to do something like this
select all records with 0 when the parameter is 0
select all records with 1 when the parameter is 1
select all records when the parameter is null
I know i can do this by creating a temporary table variable and then using if clause, insert values in temporary table based on parameter value and then use this table with IN clause
but I am looking for a solution which does not involve creating any temporary table.
where bitcol=@bit or @bit is null
Thanks Madhivanan
HI
I CREATE XML FILES USING ADO AND XML DOM
FROM ASP CLASSIC.
COULD YOU TELL ME THE BEST WAY TO ADD THE ENCODING TAG AT THE BEGINNING OF MY DOC. I OPEN THE XML WITH THE FILESYSTEMOBJECT WRITE THE TAG AND APPEND THE REST OF THE XML. IS THAT GOOD ENOUGH?
MY XML IS PRODUCED WITH ADO AND ‘FOR XML AUTO’ TO A STREAM AND THEN USING THE DOC I CREATE THE DOC
PLEASE HELP
THANK YOU
hi sir, i like to be DBA. any suggestion on what sql site/article a beginner like me should read?
thanks in advance!
Hi
I’ve got a little question,
When inserting a value too large for a column SQL server is truncating the string instead of throwing an exception.
e.g.
Table t(a varchar(10))
insert into t(a) values (‘more than ten characters’)
select a from t
results in:
‘more than ‘
why?
This is the expected behaviour. If you dont want to happen this, you need to check the length of the string before inserting to the table. Refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx
How can I get a member and Post my queries?
please tell me.
I have a tabel Visitor with column names as in the following store procedure- but when I create this its gave an error that- the name “Vis_Name” is not permitted in this context……..Column names are not permitted.
CREATE PROCEDURE sp_AddVisitor(@Vis_Name varchar(50),@Vis_Gender varchar(6),@Vis_Address varchar(100),@City_Id int,@State_Id int,@Vis_Pin int,@Vis_Contact_No)
AS
begin
insert into VISITOR values(Vis_Name,Vis_Gender,Vis_Address,City_Id,State_Id,Vis_Pin,Vis_Contact_No)
Return
Hi Pinal,
I was wondering if you had an example for calling a Calling A Web Service From SQL Server 2008 using SQL CLR?
Your presence is requested by ee lizards.. see db question
Thanks!
How can I prevent administrator from altering the physical record in a table?
Hi Pinal,
I have a ExcelSheet contain macros to format the data i.e adding borders to data. need to develop a report by copying the sheet to retain the macros and populate the excelsheet with the user filter conditions. is it possible to create a excel,placing data from sqlserver. i am in dilema whether to use .Net programming to create excel or directly generating the excel using sqlserver.
Hello Nitesh,
Make the following corrections in your stored procedure
1. Specify the datatype for the last variable in your stored procedure (@Vis_Contact_No)
2. You have created a stored procedure with the first three variables with varchar datatype and the next four variables with integer datatype and trying to insert the values without specifying the datatypes.
3. end your stored procedure
So, the corrected stored procedure is,
CREATE PROCEDURE sp_AddVisitor(@Vis_Name varchar(50),@Vis_Gender varchar(6),@Vis_Address varchar(100),@City_Id int,@State_Id int,@Vis_Pin int,@Vis_Contact_No int)
AS
begin
insert into VISITOR values (‘Vis_Name’,'Vis_Gender’,'Vis_Address’,City_Id,State_Id,Vis_Pin,Vis_Contact_No)
—(‘Vis_Name’,'Vis_Gender’,'Vis_Address’,1, 2, 123, 1234)
end
return
This should work fine.
Hi
Would you be able to write a custom query/job for us?
Thanks,
Doug
How to perform row by row operations without cursors in sql server 2005?
Give Examples?
or
What is the Alternative of Cursor in sql server 2005?
Give Examples?
Alternates are using a set based approach like directly doing in a single query or using a stored procedure
Sir
I have a SQL database. There are 278 foreign key relationships in my db. I have an issue that when i delete record from my parent table whose relationship exists in child tables. It delete the record from parent table.
I write this query: delete from parent table name where id = 2, but with id 2 record is exists in child table.
In SSMS in Object Explorer in design view foreign key relation is exist but i am still able to delete the record from parent table.
Also when i create foreign key relation by droping existing relation and create relation again then it works properally.
Please help me on this issue or i have to create all relations again.
hi, we are working on aspx version using sql server. but our application was build on asp. we are working on latest aspx. the problem is that when we are work on aspx it creating a big log file in sql server due this reason sql server become hang. we think apsx pages creating problem. can you describe how we can resolve this issue.
we are using sql server 2008. asp pages creating small log file but when we work aspx pages it create big log file.
for example asp pages create 1kb log file. but aspx pages creating 109211KB files.
kindly help us. its urgent if you provide some guide or tips
thanks
Sultan
HI Pinal,
First I want to praise your site …it helps so greatly.
I am totally new in SQL. Specially in SQL 2005. I am facing a big problem in one of my Visual basic program where there is frequent update and access of fields from several SQL 2005 tables. The program was running fine when the backend database was SQL2000. Recently we have shifted to SQL2005. Since then I am getting an error of DEAD LOCK error where it says Microsoft forcefully ended a process due to dealock of reqsouurces and I have to rerun the transaction. NOw I do understand the meaning of that. I have checked all my connection and records sets. I am getting that so frequently that I need to do something. Can You please please please help me . I can provide you with the code if you want to see. I am anxiously waiting for your reply. Thanks.
Runa.(Winnipeg, Manitoba, Canada)
I want to connect two remote SQL-Servers using Dynamic IP address over internet to replicate data.
My Subscriber on dynamic IP.
Receiver on Static IP.
It is possible?
Hi,
There are two tables.
Table Patient Table Followup
Field1 PID Field1 FID
Field2 Pname Field2 PID
Field3 Fdate
Field4 Remark
Field5 Next_F_Date
I want the all the process names and remarks which are there in the followup table with max(next_F_Date).
I need the solution using the Group By and Having Clause without using the complex queries.
Regards,
Pritam.
Dear Sir,
FYI, I have few SQL servers with SQL 2008 database, I would like to know, how to bulk deploy some sql source codes (SP,alter/create table) into all this sql server database on the same time (in parallel) ? And how to maintain/manage all sql server like sql server database performance, And how to bulk install SQL server patches?
Thank you very much !
HI Pinal,
I have a query and requirement also. query is if i will detach sql2000 database (.mdf and .ldf file) and then atach it to sql2005 or 2008 server, then i am not able to call stored procedures of it, altough i quite able to see the text of it via sp_helptext command. and my application is also not able to use any stored procedure from this database. Please suggest any server side settings or query for this issue.
kind regards,
Abhishek
Did you get any error when calling the procedure?
Hi Pinal,
Your blog is too helpful,
I need one more favour as i have to fetch records where if the current date is first of any month then data should be of entire last month else it should give current month data with 7 days gap.
for e.g if today date is 01 Nov my query should return data for oct month .
else if todays date is 03 Nov it should return data with date into consideration as 01 and 02,
and if date is 08Nov or above it should consider dates with 7 days gap like 1,2,3,4,5,6,7
Post some sample data from the table and your expected result
Sir! My problem is whenever i try to create new login or change the password of sa in SQL Server 2008 what happens is when i set it up by providing the username and password & also uncheck that enforce password policy and press ok. Now when i reopen the properties of that login the password has always been changed to something unknown, also if soon after creating new login if i try to login using that username and password it doesen’t. Can you please help me
Thankyou very Much.
Hi
How do you implement relational integrity without using foreign key?
What is the alternative to achieve the same as foreign key without using one?
You can use a trigger or application. But why do you want to do this?
hi madhivanan
thans for reply but what do you mean by or application?
i can understand that I can achieve this with trigger but did not get the part ‘or application’?
If you use any front end application, you can do the same logic there
What is a staging database or staging table? what is the use? explain with an example. how do you use it?
Staging table can be used for temporarily. For example you want to import some data from text file to the production table. but you want to do some processing before insertion. In this case you can import data to different table;process it;insert to production table
Dear Sir,
Can anyone help me.. I have successfully created 1000 linked servers to connect to 1000 branches ( original dbf files) , the application is liv and therefore migration is ruled out. Now can i create a single view out of these 1000 views..
Hi,
I am working with to databases. From one of de database I use a table and from de other one a view. There a no primary key in this tables that refer to one a another. So if a make a new view it doesn’t work good.
In the table I have colum useractivity with string text rows like this “Modified Card : Card Number 17961 : Modified : Inactive Date/Time” , now I want to extract only de number from this string text and convert this number from string type to number type, so that I can put it in a alias colum, How can I do that in Sql 2005?????
Because than I can set the number in the alias colum as primary key and the cardnumber in the view as primary key to get the info I want. So please can you help me.
Refer this post and see if it helps. It is used to extract the numbers from the text
http://beyondrelational.com/blogs/madhivanan/archive/2010/04/22/extracting-numbers-part-2.aspx
this is an interview question and even I did not understand why do he wants to do that? Is it possible that it might be beneficial in mobile applications ?
Pinal,
I have a value = ’00L103L1′ and I want to remove the ‘L1′ portion at the end. What is wrong with my query that the result ends up being ’0003′ rather than my expectation of ’00L103′?
update main1
set main1.volser = replace(main1.volser, substring(main1.volser,7,2),”)
where clientid = 1 and len(main1.volser) = 8 and substring(main1.volser,7,2) in (‘L1′)
For your convience,
create table main1
(clientid numeric(18,0) not null, volser varchar(15) not null)
insert into main1 (clientid, volser) values (1,’000003L1′)
insert into main1 (clientid, volser) values (1,’00L103L1′)
insert into main1 (clientid, volser) values (1,’00L103′)
insert into main1 (clientid, volser) values (2,’000003L1′)
insert into main1 (clientid, volser) values (2,’00L103L1′)
insert into main1 (clientid, volser) values (2,’00L103′)
select * from main1
update main1
set main1.volser = replace(main1.volser, substring(main1.volser,7,2),”)
where clientid = 1 and len(main1.volser) = 8 and substring(main1.volser,7,2) in (‘L1′)
select * from main1
drop table main1
Regards,
Dave
Do you want to remove it from the last part only or any part?
Try this
select volser,replace(volser,’L1′,”) from main1
As stated in the original e-mail, “…I want to remove the ‘L1′ portion at the end” or more specifically, where L1 appears in the volser column in positions 7+8 only.
Try this
select volser,left(volser,6) from main1 where substring(volser,7,2)=’L1′
Thanks, but I am doing an update…can you explain how to do the update as intended with an actual UPDATE statement?
update main1
set volser=left(volser,6) from main1 where substring(volser,7,2)=’L1′
Thanks Madhivanan, using ‘LEFT’ instead of ‘REPLACE’, works!
update main1
set main1.volser = left(main1.volser, 6)
where clientid = 1 and len(main1.volser) = 8 and substring(main1.volser,7,2) in (‘L1′)
I need to loop thru a large number of rows 50k+
compare a pay field in one table to a fund amtt in another table
if table 2 amount is > = table 1 amount then
create a new row in another table with the results and update amount in table 2
I am new to loops – cursors – fetch while statements….any help will be greatly appreciated….
Lisa,
Make us better understand your requirement with Table structure and lets find if logic can be derived from sql statments unless there is a need of cursors
Hi,
How can we view what are all objects using SQL Server memory and how much (mb) in a AWE Enabled 32 bit SQL Server 2005.
Regards,
S.Balavenkatesh
Hi Dave,
Is there any scripts or query how can we view what are all the objects using SQL Server Memory and how much in a AWE Enabled Sql Server 2005.
Regards,
S.Balavenkatesh
hi mr.pinal dave.
I enable cdc on mydb and 2tables and aduite my change.but see when I change data type in table’s design ,cdc table which store modified data,destroy and I can not see cdc.capture instance_ct and whole data destroy.what do this event happen?
hi mr.pinal.
I enable cdc for my db and 2 tables.but when modify data type on design’s table,I see that cdc.captureinstance_ct not exist and whole changed data destroy.why do this event happen?
SSRS Report timepout.
Error : “System.outofMemoryException”
I have SSRS 2005 report.
The report displays 10,000 of records successfully, but when the report is supposed to display lakhs of records, the report gets time-out.
I have also tried the below changes but still the timeout occurs.
1) In Report manager have selected the “Do not timeout report execution” option.
2) In Machine.config changed the
to
3) In rsreportserver.config changed “MemoryLimit” and “MaximumMemoryLimit” to 120 and 160 respectively.
SSRS Report timepout.
Error : “System.outofMemoryException”
FYI : Dotnet Framework 2.0, Sql Server 2005, SSRS 2005.
I have SSRS 2005 report.
The report displays 10,000 of records successfully, but when the report is supposed to display lakhs of records, the report gets time-out.
I have also tried the below changes but still the timeout occurs.
1) In Report manager have selected the “Do not timeout report execution” option.
2) In Machine.config changed the
to
3) In rsreportserver.config changed “MemoryLimit” and “MaximumMemoryLimit” to 120 and 160 respectively.
Please let me know, what are the possible solutions that I can do to avoid this error and display whatever data needs to be displayed.
Hi,
This is my table.If i dont have data for particular date i am getting null but it should return 0.
CREATE TABLE SAR_HeadCount(
HeadCount int,
CampaignID int,
atnDate datetime,
)
Insert into SAR_HeadCount(HeadCount,CampaignID,atnDate)
select ’40′,’1′,’8/1/2010′ union all
select ”,’1′,’8/8/2010′ union all
select ’43′,’1′,’8/15/2010′ union all
select ’43′,’1′,’8/22/2010′
Please run this stored procedure it will return null for one column but it should return 0
exec SAR_Sp_GetForecastedHC ’8/8/2010′,’9/15/2010′,’10/22/2010′,’10/29/2010′,1
ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = ‘SELECT ‘+ CHAR(39) + ‘Agents Scheduled as per Schedule’ + CHAR(39) + ‘ AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ +
‘FROM
(SELECT HeadCount as Head,
StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
AS SourceTable
PIVOT
(
max(Head)
FOR StartDate IN (‘ + ‘[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ + ‘)
) AS PivotTable
union
SELECT ‘+ CHAR(39) + ‘Forecasted HC as per Hiring Plan’ + CHAR(39) + ‘ AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ +
‘FROM
(SELECT HeadCount as Head,
StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
AS SourceTable
PIVOT
(
max(Head)
FOR StartDate IN (‘ + ‘[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ + ‘)
) AS PivotTable’
EXEC(@query)
END
Hi,
Please tell me how to handle value for the below stored procedure.
when i run the below stored procedure for date 8/22/2010 i am getting corresponding column value as null but is i should handle that null value with 0.
CREATE TABLE SAR_HeadCount(
HeadCount int,
CampaignID int,
WeekStartDate datetime,
)
Insert into SAR_HeadCount(HeadCount,CampaignID,WeekStartDate)
select ’46′,’1′,’8/1/2010′ union all
select ’46′,’1′,’8/8/2010′ union all
select ’46′,’1′,’8/15/2010′
Please execute this stored procedure
exec SAR_Sp_GetForecastedHC ’8/1/2010′,’8/8/2010′,’8/15/2010′,’8/22/2010′,1
ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = ‘SELECT ‘+ CHAR(39) + ‘Agents Scheduled as per Schedule’ + CHAR(39) + ‘ AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ +
‘FROM
(SELECT HeadCount as Head,
StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
AS SourceTable
PIVOT
(
max(Head)
FOR StartDate IN (‘ + ‘[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ + ‘)
) AS PivotTable
union
SELECT ‘+ CHAR(39) + ‘Forecasted HC as per Hiring Plan’ + CHAR(39) + ‘ AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']‘ +
‘FROM
(SELECT HeadCount as Head,
StartDate FROM SAR_HeadCount where CampaignID=’ + CAST(@CampaignID AS VARCHAR) + ‘)
AS SourceTable
PIVOT
(
max(Head)
FOR StartDate IN (‘ + ‘[' + isnull(CONVERT(VARCHAR,@date1,101),0) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + isnull(CONVERT(VARCHAR,@date4,101),0) + ']‘ + ‘)
) AS PivotTable’
EXEC(@query)
END
Hi Dave:
I just bought SQL Server 2008 Developer version. I Installed it on the old PC running Windows Vista and it was OK even though I could not see the DB diagram. But I try to install it on my newer PC running Windows 7 (using same defaults as other computer) I could not log-on.
The system gave me the following error message”
“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)”
Would you please give me step-by-step instructions on how to correct it.
Many thanks
Hung
Hello Sir,
I need your help for querying the following problem.
suppose i have a table with the following columns ord_num(PK) and ord_date.I need to pass 2 parameters for the start date and end date from SSRS report.
lets take @StartDate=’2009-12-01′
@EndDate=’2010-02-10
Now i need to take count of the ord_num week wise.
The output should be
Week Count
—————– ———————-
Tue,Dec 1,2009 to Sun,Dec 12,2009 10
Mon,Dec 13,2009 to Sun,Dec 19,2009 20
………………….
Mon,Feb 08,2010 to Wed,Feb 10,2010 30
I some how renamed a table [table1].
Meaning the brackets are part of the name. ‘[ ]‘
using – MS SQL
I would like to just remove the table altogether.
I get errors because the brackets are part of the table name.
Any suggestions?
Post the code you used
Hi,
I have planned to write the MCTS 70-432 on SQL Server exam by next week. Please Provide me with any DISCOUNT VOUCHERS if anyone of you have it. This will be very helpful.
Regards,
VK
Hello Sir,
I want to do..
If one database get any type of changes (insert, update etc) then another database will get those changes automatically at time.
Those two database has same server.
How i do in script (ASP)
Please reply as soon as possible
Trigger is one of the options
SQL 2000 data to 2008 after the transfer date field has changed
sql2000 date field= 01.12.2010
sql2008 date field = 2010-12-01
SQL 2008 also show how the date field in the form of 01.12.2010
please help me
What is the datatype of the date column?
field datetime
Then how did you knwo the format was changed?
datetime format and type of order has changed. Do you wonder if the standard was in sql 2008
Make sure you read these
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-ii.aspx
Hello Sir,
Is there any limitation for the existence of SQL server 2000 and SQL server 2008 R2 in same server? If they can exist in same server, then is there any specific installation steps to be followed?
Thanks
I read the article. but the date fields in sql how can I do as 01.01.2010.
Hi Pinal,
I am following your tips almost every day in my practice and I want to say thanks to you that you really helped and helping so many pupils through your blog.
Pinal I need your help regarding Linked server.
We have two different locations for our two servers and I want to transfer my data from one server to another server every day. For that I used linked server.
Both are SQL Server 2005 and operating server 2008.
If I transfer data say about 10000 records it works fine.
But when I am trying to send more then 10000 rows then I am getting some error after 30 to 40 min.
Below is the error massage.
- Copying to [XXXXXXXXXXXx].[dbo].[XXXXXXXXXXXXXXXXx] (Error)
Messages
• Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Communication link failure”.
An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “TCP Provider: An existing connection was forcibly closed by the remote host.
“.
(SQL Server Import and Export Wizard)
• Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “Destination Input” (107)” failed because error code 0xC020907B occurred, and the error row disposition on “input “Destination Input” (107)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
• Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Destination – XXXXXXXXXXXXX” (94) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
• Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “WorkThread0″ has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
• Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
• Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Source – XXXXXXXXXXXX” (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
• Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “SourceThread0″ has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
Please help me to find some solution.
Thanks
Prakash Paul
hi..
i am trying to follow an example.. please send by the file Shape2SQ.. i have failed to down load…
i am using sql server 2008
I am novice to Sql Server 2005. I have a question to you:
Why we have 249 non clustered index in sql server 2005? why not 240 or 300? and the same question for sql server 2008, why 999 ? Why not 800 or 1000?
I have asked this question in several sites but all I see is “Ask Microsoft Developers”. I believe that there must be some reason for it.
Hope to see your attention on it.
Regards,
Claus
Hi,
I am building a BI application where I want to copy input data from files located external from the SQL Server database environment.
Until now the copy from external folders to the database has been made by a windows script (an admin autority) in a BAT-file controlled by the Scheduler.
Now I want to solve it by using a File System Task.
However, what’s left is the login process.
Can this windows login/connect be made by programming?
If so how to do that?
And what kind of syntax should be used to refer to external sites/folders: //server/xxx or \\server\xxx or…
Thanks in advance
tomas
Hi,
I forgot to inform of the development env:
SQL Server 2005, Visual Studio 2005, Mgmt Studio 2005
tomas
I am trying to get the information of Logins history like LoginsName, Createdby, Created_Date, PermissionGiventothesatabase, Givenbywhom…etc. This is he imp report which I have to give every month. Please help me on the same.
Hi
I have a set of tables with a common field and I need to retrieve common records, as long as that record appears in more than one table. Also I need to know in which tables that common record is appearing.
Regards
Orateng
See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
Hi Pinal,
Thanks for all the help you are providing through your blog.
I’m new to SQL and need a help.
I’ve two tables- Table A and Table A_Archived.
Table A_Archived is an exact replica of table A.
I want to move all the records from Table A to Table A_Archived after a period of 120 days. That means if today I enter some data in to the Table A after 120 days it should move to Table A_Archived and Table A data should be deleted simultaneously.
I”m thinking of a trigger/Stored procedure for this.
for 120 days condition can I use following :
DECLARE @120DaysPrior datetime
SET @120DaysPrior = DateAdd(d, -120, GetDate())
— Calculate 120 days prior to today’s date
Can you please help me writing the query.
Thanks,
Arnab.
Hi All,
Can we right query form the given table X for the data like
GroupHead Team
A A1
A A2
A A3
A1 B1
A1 B2
A1 B3
B1 C1
C1 D1
1. now I want to select group Head A and want result
“A1,A2,A3,B1,B2,B3,C1,D1″
2. now I want to select group Head B1 and want result
“C1,D1″
Please help if any one having idea regard this group select query.
Thanks,
Prakash
Prakash,
Find below code snippet a workaround. Note that you got change GroupHead value in where clause to display comma seperated values.
create table #table (
GroupHead varchar(4),
Team varchar(10))
go
insert into #table
select ‘A’, ‘A1′
union all
select ‘A’, ‘A2′
union all
select ‘A’, ‘A3′
union all
select ‘A1′, ‘B1′
union all
select ‘A1′, ‘B2′
union all
select ‘A1′, ‘B3′
union all
select ‘B1′, ‘C1′
union all
select ‘B1′, ‘D1′
go
declare @sql varchar(1000)
select @sql = coalesce(@sql+’, ‘, ”) + Team
from #table
where GroupHead = ‘A1′
select @sql
Hi Pinal
I have a scenario in my project where the users are allowed to post Ads.The Ads have Start date and End Date which means the Ad will be visible to the end users only when the current date falls between start date and end date.My application is a world wide application and the data type for the start date and end date is smalldatetime.Suppose if a user posts an Ad in India and another user in USA is trying to view the Ad how should i manage datetime between the 2 countries.My question is how should manage the datetimes between different countries especially when dealing with the End Date.I should not show the ads if the current date exceeds the End Date.Since we have different time zones for different countries,should i take the localtime of the user into consideration who is browsing our application to show the ads irrespective of the datetime where the ad has been posted ?
Yes you need to take local time and query based on it
Hi Pinal
I am in search of a stored procedure which can create html page from the table stored in sql server database.
i am fetching the result in a sql query and i want that result to be displayed in html page.
pls help me asap to find the solution.
Thanks
Vikram Mahapatra
If you use any front end application, you can do this easily there
Hi Pinal Dave,
I have a SQL problem I just can’t seem to resolve. I have a table:
CREATE TABLE [dbo].[SensorData](
[SensorID] [int] NOT NULL,
[Temp] [float] NOT NULL,
[UnitNo] [int] NOT NULL,
[DateStamp] [datetime] NOT NULL
) ON [PRIMARY]
My goal is to find the last entry (Datestamp) for each SensorID (especially when they are not equal to-88.88 or 85, so that if the latest date entry has one of those values then it will go to the next latest row/date, etc Note: those values are erroreous so we don’t want any rows returned with those “Temps”) I’ve tried:
Select SensorData.SensorID, max(SensorData.DateStamp) as Datestamp FROM SensorData WHERE SensorData.UnitNo=2233 AND (sensordata.temp-88.88 AND sensordata.temp85)
Group By SensorData.SensorID
and it works almost perfect except I need to get the “Temp” column values returned for each row it returns and I just can’t seem to get that last piece of the puzzle.
I hope I have supplied enough information. All help is appreciated.
John
UPDATE: just before posting this I decided to try one last thing and it seems to work :) Is the following SQL the “Best” way to accomplish my goal? I believe it would be so much more simpler if I could just get the “Temp” col to return with the original SQL.
Select Unitno, SensorData.SensorID, SensorData.Temp, SensorData.DateStamp From SensorData, (Select SensorData.SensorID, max(SensorData.DateStamp) as Datestamp FROM SensorData WHERE SensorData.UnitNo=2200 AND (sensordata.temp-88.88 AND sensordata.temp85)
Group By SensorID)b Where sensordata.SensorID=b.SensorID and sensordata.Datestamp=b.datestamp
Hello,
i have a problem with connecting sql server 2005 from sql server 2008 management studio. i did all you said about connecting to sql server on this link: http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/
but it does not worked for me. there is no firewall restriction and all the configurations which have to made on sql server has been made. i could not figure it out.
can you help?
thanks
How to read SQL Server query execution plans ?
and How to optimize the Query ?
hi
please help me
i install sql sever2005(developer) on my laptop with windows vista os.
also visual stadio 2008
but sql can not connect to server
error:18456
i cant create new user account for connect to data base
please learn me more than text in your topic about this error
thanks
Hi,
Can anyone help me in getting list of all SQL servers and instance/databases created under it in a particular domain.
It will be really helpful.
Thanks in advance.
Regards
Laldhar
Hello Sir,
1.i have a database of size upto 50GB.Can i split that into two databases.
2.Can i retrieve a specific nth row from a table
without using ‘where’condition in query.
regards
Hi, sir, i would like to know if there is a way to set the opening new window in the right side, like when you open a new tab in the IE or Firefox
Regards
Kenji
Hi,
Donno where to submit my query. Please help me on the following.
Have two columns acctid and pages in accounts table
acctid is uniqueid – 1,2,3,4,5,6,7,…
and pages are pagenos like 2,24,3,2,8,2,45…..
want to loop for each acctid it has to show in another columns like
If there are 2 pages for acctid – 1
then 1_1
1_2
If there are 24 pages for acctid – 2
then 2_1
2_2
2_3
2_4
2_5
2_6 and so on till 2_24
Can you please help me in doing this. struggling with this from 4 days.
Please help me asap. I need to complete this tommorow.
Thanks in Advance.
HI
Below is my sqlstament:
Select * from Emp where city=’Ahmedabad’ or Age>30.
I got the correct result as query very simple. I want to show in result that for which criteria match in result.
E.g If there are 10 records found. 5 for matching city=’Ahmedabad’, 3 for matching Age>35 and 2 for matching both.
Hi,
I am working on the migration project of sql 6.5 to sql 2008.In the
SQL 6.5 version
*=,=,=* are used for representing Joins
SQL 2008
LEFT OUTER JOIN,RIGHT OUTER JOIN,INNER JOIN
are used for representing joins.Even *=,=* notations are allowed.
Can anyone explain the difference between using the two kind of notation in SQL 2008?
If any difference is there,is there any performance related issues between two?
Hi,
I am going to estimate the disk space for the new database. I have overall 1903 columns in a DB & monthly insertion of 1 million records.
So how can I estimate the disk for the DB?
Please Reply.
hi sir,
here i have a doubt in replication
can i make my subscription database as a publisher database to another subscription ?
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID