Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1200 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.
pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com
Hi there,
I am a fresher and looking for a job.
I want to ask that i am trying to store a database on a local drive but unable to do it.
Could u please tell me how to store a database on to a local drive(can we do it through export if yes then how?) and retrieve (may be it’s import option) it back from a local drive.
Plz help……….. can we get the assistance within 24 hrs. if stuck somewhere
shalini Gupta
[phone and email address removed]
While creating a database,it uses default location of datafiles and log files.You can change them then and there.If you want to avoid it,in the server settings put the path where you want to keep your database files.From then onwards,new database will take this path automatically.
I could not understand the later half of your question.You can keep a backup of your database on your local disk and you can restore it whenever you need.
Please explain your question clearly if I missed out something.
Thanks
Chandan
Copy the mdf file from instance folder and take where ever u go .. use Sql Server to attach the file to new database .it will take care every thing ……..
We are running Microsoft Dynamics4.0 and I perform daily normal backup through sql server management studio the database name is AXDynamics.
Now I want to restore it on another Virtual machine.
Please help me to solve this issue.
huy
i am ghada from yemen
and new i make accounting programs
i neet to knrw about
barcode
what is the type data can i give to barcode filed
dear ghada
you can give your data type for barcode as string type
because the barcode is having alphanumeric data
you can have to check a barcode from any product
I am working as SQL DBA in NOC based compnay, i am facing some regular problem of CPU and memory high utilization, normally we suggest for the services to be restart but after restart services we found it increase utilization after some days or we suggest for configure max memory limit for the particular instance, i know this is not proper way, please suggest us to better option with step so that we can follow to troubleshoot issue. this is very important to me solve this issue with proper manner.
Waiting for your precious reply.
Thanks you.
Hello Gulzar,
Even CPU and IO utilization increases with workload on an OLTP database but you can improve the utilization indirectly by query optimization. Using profiler or SSMS summary reports get some queries that are mostly using the CPU, memory or IO. Optimizing these queries could help in reducing the high utilization.
Regards,
Pinal Dave
Hi pinal,
Thanks for ur response.
i jus wanna knw how to get the usernames who have the rights to access the particular db. is there any way?….
Hi
Sir,
This is Subhash,
;With CTE as ( Query..) when ‘with clause’ run on SQL server2005 then ‘)’ error arise. its not being support or ..
Tell me sol?
Hi Pinal Dave,
i am working as a progrmmer ,i got scenior, in my database column i am having incident description field , i want to get value strating with number and ending with alpha.i have search in goolge ,but i could not find any example.
ex:
input in database column :
column name is:
Incident description
SYD – SYD 28E Handset socket broken.
my out should be
outPut : 28E
could you please help me
Hi Pinal Dave thanks for saving me lot of times in job with ur work …
if u dont mind can u me solution for 1 thing which i have prob
like for a string
ex this is a nice blog site for sql
i want like @temp tables with 1 column
with rows like
this is
is a
a nice
nice blog
blog site
site for
for sql
sql
this is for 2 to words ……..is it possible
Why do you want to do this?
Try this code too
Declare @s Varchar(100),@delimiter varchar(5)
Set @s = 'this is a nice blog site for sql'
set @delimiter = ' '
Declare @Xml Xml
Select @Xml = Cast('' + Replace(@s, @Delimiter,'') + '' As Xml );
declare @t table (sno int identity(1,1),data varchar(100))
insert into @t(data)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @XML.nodes('/d') T (split)
select t1.data+' '+t2.data as data from @t as t1
inner join @t as t2
on t1.sno+1=t2.sno
@Kumar,
Check if this helps…
declare @Sqlcmd1 varchar(100)
set @Sqlcmd1 = LTRIM(RTRIM(‘this is a nice blog site for sql ‘))
declare @Temp_Table table (Column1 varchar(100))
While len(@Sqlcmd1)>0
begin
if ( SELECT CHARINDEX(‘ ‘, @Sqlcmd1, CHARINDEX(‘ ‘, @Sqlcmd1)+1) ) > 0 — checking for second space.
begin
insert into @Temp_Table (Column1) values (substring (@Sqlcmd1,1 , CHARINDEX(‘ ‘, @Sqlcmd1, CHARINDEX(‘ ‘, @Sqlcmd1)+1)))
select @Sqlcmd1 = (substring ( @Sqlcmd1
, (CHARINDEX(‘ ‘, @Sqlcmd1)+1)
, (len(@Sqlcmd1)- CHARINDEX(‘ ‘, @Sqlcmd1)+1)
)
)
End
Else
begin
insert into @Temp_Table (Column1) values (@Sqlcmd1)
Set @Sqlcmd1 =”
End
End
select * from @Temp_Table
~ IM.
@Imran Mohammed
Thank u man for great help ……….really appreciated
Hello Pinal,
I am a regular follower of ‘SQLAuthority’
Recently I had to face a problem related to maintaining ‘Transactions’ while doing sql manipulation.
Scenario:
There are two different tables (say db1..table1 & db2..table2) in two different databases (db1 & db2) of same server (say server1).
I need to insert records in these two tables in a single transaction.
There are around 40+ input parameters,hence I decided to opt for dynamic sql (sending comma separated param as string). I did it in following way,
sample :
CREATE PROCEDURE [dbo].[StaffMasterInsertForApprovers]
@Table1Param AS VARCHAR(1000),
@Table2Param AS VARCHAR(1000)
)
AS
BEGIN TRY
BEGIN TRANSACTION T1
– Turn off count return.
SET NOCOUNT ON
DECLARE @Table1SQL AS NVARCHAR(500)
DECLARE @Table2SQL AS NVARCHAR(500)
SET @Table1SQL = ‘
INSERT INTO db1..Table1
(
[Col1]
,[Col2]
,[Col3]
.
.
,[Col40]
)
VALUES
(
‘
SET @Table2SQL = ‘
INSERT INTO db2..Table2
(
[Col1]
,[Col2]
,[Col3]
.
.
,[Col40]
)
VALUES
(
‘
–INSERT IN db1..Table1
IF @Table1Param ”
BEGIN
DECLARE @Table1Insert AS NVARCHAR(MAX)
SET @Table1Insert = @Table1SQL + @Table1Param + ‘)’
EXECUTE sp_executesql @Table1Insert
END
–INSERT IN db2..Table2
IF @Table2Param ”
BEGIN
DECLARE @Table2Insert AS NVARCHAR(MAX)
SET @Table2Insert = @Table2SQL + @Table2Param + ‘)’
EXECUTE sp_executesql @Table2Insert
END
SET NOCOUNT OFF
COMMIT TRANSACTION T1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
RETURN ERROR_MESSAGE()
END CATCH
*****************************************
But above transaction didnt rolled back when I tried to raise error at some level in above sp.
Please help…
- > Kshitij
Hello Kshitij,
Use SET XACT_ABORT ON before the start of transaction in stored procedure. This will rollback all work in case of error.
Regards,
Pinal Dave
Thanks for reply.
Hi Dave,
Just curious if there is a possible way to trace if a column result of an sql DML statement is taken from an ‘Included column’ of an index or if it is taken from the clustered index ? I have quite a complicated sql statement and I’m wondering if the optimizer is actually using the included column of my indexes.
Thanks for this website, its been helping me a lot !
Regards,
Marellano
Hello Merellano,
Using execution plan we can check which index is used and if your result having data of included column is returned from an index (that have included column) then its apparent that included column is used.
Regards,
Pinal Dave
Hi Pinal Dave,
i have one doubt,
i have database which is stored in different locations.if i delete one file in one location what is the database status? how to change the databse status?
Regards,
Nagaraju
Hello Nagaraju,
A database file can not be dropped unless this is offline. So to drop a file first alter the database to offline that file. The database will be available with data of all other online files.
Regards,
Pinal Dave
Respected sir,
I personally attended the community day event today at Ahmedabad and i am very much pleased with it.and really admire your work and sincere efforts..
Greetings Dave,
I have a question regarding encrypted data. I have a base database that contains a table with encrypted data in it. I use this database to create databases in our other environments. That table of encrypted data needs to be carried over to the new databases. I am using SMO in C# to copy the databases, create the symmetric key, and execute the Transfer. This appears to work, the database is there, the key is there with the same Password as the source database, but the encrypted data is null. Is there a way to move the encrypted data over using C# and SMO? Or do I need to read the data from the source, decrypt it, and then insert it into the new database encrypted with the key on the new database? It would be nice if I could make an exact copy the database including keys and data. Do you have a suggestion on this?
Thanks,
Donnie
Hi,
We are planning to convert Informix Database to SQL Server 2005.
What are the steps to follow to convert from Informix to SQL server 2005?
Hi Sir,
I have one problem, actually our software running in Clients (more than 10) side. when I changed Database change how to update Table & others changes (Each client different Database version like 1 Client update January 1, 2nd Client January 10) now I DB version February 1st. so how I know DB Changes need to update?
Thanks
Hello,
I try to use teh SQLBAP and I don’t see my sql server instance, the message is unable to connect.
Please help me for this.
hi,
can you tell me how to calculate SQL query recordset if it is more than one lakh datas.
thank u
Amit
Hello Amit,
what issue you are facing while calculating large recordset? In SQL server there is no constraint of row number in any clause or functionality.
Regards,
Pinal Dave
Hi Folks,
I have seen this code in an SQL Server 2008 tutorial:
DECLARE @StrSalary Varchar(10),
@StrHours Varchar(6),
@WeeklySalary Decimal(6,2)
SET @StrSalary = N’22.18′;
SET @StrHours = N’38.50′;
SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) *
CAST(@StrHours As Decimal(6,2));
SELECT @WeeklySalary;
GO
In code lines 4 and 5 above, is the capital letter ‘N.’
What does the N mean or signify here?
Thanks in advance for your help.
-Tom Jarosinski-
This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.
Unicode is designed so that extended character sets can still “fit” into database columns. What this means is that Unicode character data types are limited to half the space, because each byte actually takes two bytes (Unicode is sometimes referred to as “double-wide”).
While using Unicode is a design choice you can make in building your own applications, some facilities in SQL server require it. One example is sp_executeSQL. If you try the following:
EXEC sp_ExecuteSQL ‘SELECT 1′
You will get this error:
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.
You can get around this in two ways:
– (a) using the N prefix
EXEC sp_ExecuteSQL N’SELECT 1′
– (b) using a variable
DECLARE @sql NVARCHAR(100)
SET @sql = N’SELECT 1′
EXEC sp_ExecuteSQL @sql
It may be helpful…..
Hi Tom,
The ‘N’ stands for unicode(National language character set).
In simple terms it means that you are going to pass a value which is of the form NCHAR,NVARCHAR or NTEXT (instead of CHAR,VARCHAR or TEXT.
http://support.microsoft.com/kb/239530
Another beautiful article that I found on google is:
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
Cheers!!!
Chandan
Thank u for your reply,Actually i have a method to manipulate various datas of binary trees but if my datas crosses 1 lakh then it takes lots of time,can say 6-7 hours,and its very difficult to manipulate my tree.
Can you suggest some ideas to reduce my calculating time.
Thank you
Amit
Pinal Sir,
I visit this site very often. Thank you for this very great effort. I have 2 suggestions.
1, Can you please include a question answer section. As you can see, many questions are spread over a lot of the articles, if we have a common page to post our question for a topic that is not currently being discussed. ex. In an article about generating scripts of stored procedures, a question about which field to use for storing a bar code information, I feel is out of place. When I have a question outside that of the topic discussed, i use the contact me to raise my question. I admit, members of this blog have always been kind to help me solve my issues effectively, a big thank you to all for that. I am only suggesting a common area to post a sql related question.
2, My second suggestion is that this is a blog used by many sql professionals, we should have a job posting board, where a member who knows that there is a sql related job in his or her company can post that vacancy, and a member who is looking out for a job change can pick on that lead.
Hi ,
which is faster a stored procedure or a function ?
Can you explain in detail??
Hello Chethan,
The complied plan of both function and stored procedure are stored in memory. so theoratically there should be no major performance difference.
Regards,
Pinal Dave
Hi everybody,
I have excel 2003 files with some visual basic code on XP SP3. The files retrieve information from SQl 2005. SQL2005 is installed on win2003 server. The connection is done with TCP/IP. The files run fine. But last month I got a new dell server with win2008. I installed sql2005 on the new server and works fine. I have some web applications running and connecting to sql 2005. Everything is OK. But the excel files with the visual basic code can’t connect to the win2008 server. the connect fails. I checked the IP, IP on the new server is enable.
Any suggestion.
Thanks
hello sir,
i want to insert “amit-005″ in table student only in single query,But “amit” should store into Name field and “005″ should store in RollNo field . How i can do this ??? Hurry !!!reply
Hi Amit,
You can use CHARINDEX and SUBSTRING to identify name and rollno.
You can do it as:
DECLARE @x VARCHAR(50)
SELECT @x=’amit-005′
SELECT SUBSTRING(@x,0,CHARINDEX(‘-’,@x)) AS Name,
SUBSTRING(@x,CHARINDEX(‘-’,@x)+1,LEN(@x)) AS RollNo
Thanks,
Tejas
SQLYoga.com
Hello Amit,
Please see my article:
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/#comment-60430
Regards,
Pinal Dave
Hi Pinal,
I want to load SQL Server 2005 on my laptop. Can ypu please tell me which of the link on MSDN site do I download and to load the Sample database what do I do?
Appretiate your response
Thanks
Hello Hiba,
see the below link.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en
Also you can refer this article from sqlauthority…
http://blog.sqlauthority.com/2007/05/23/sql-server-2005-northwind-database-or-adventureworks-database-samples-databases/
Regards,
Mohan
Hi ,
I am using SQL Server 2005.
Can any one explain “sp_dropwebtask” ?
I used this system defined procedure but it is not working .
exec sp_dropwebtask @procname=N’test_tmp’, @outputfile=N’\\devdb\RAMCOERPDOCUMENTS\chd1.xls’
showing error message :-
Msg 16802, Level 11, State 1, Procedure sp_dropwebtask, Line 25
sp_dropwebtask cannot find the specified task.
hi pinal
i have one problem ,please solve it.
problem statement :
i have one text file that i want to import it into sql sever 2005.
in text file data is like this
[00:00:00 16/11/2011] wf t0=43483 tf=147677 id=a40215
[00:00:00 16/11/2011] nu t0=42659 tf=147599 id=a40215n
[00:00:00 16/11/2011] wf t0=54788 tf=111787 id=a41059
[00:00:00 16/11/2011] nu t0=52919 tf=111719 id=a41059n
[00:00:00 16/11/2011] ic dd=1730 ds=1 ld=MALIG NEO SKIN LIP
[00:00:00 20/09/2012] ic dd=2720 ds=4 ld=PURE HYPERCHOLESTEROLEM
[00:00:00 20/09/2012] ic dd=2859 ds=7 ld=ANEMIA NOS
[00:00:00 20/09/2012] ic dd=4019 ds=3 ld=HYPERTENSION NOS
[00:00:00 20/09/2012] ic dd=4111 ds=2 ld=INTERMED CORONARY SYND
[00:00:00 20/09/2012] ic dd=41401 ds=1 ld=CORON ATHEROSCLER NATIVE
[00:00:00 20/09/2012] ic dd=49390 ds=5 ld=ASTHMA, UNSPECIFIED
[00:00:00 20/09/2012] ic dd=53081 ds=6 ld=ESOPHAGEAL REFLUX
[00:00:00 20/09/2012] ic dd=V707 ds=8 ld=EXAM PARTIC CLINICAL TRI
[10:00:00 20/09/2012] po tf=[12:00:00 21/09/2012] pt=Unit Dose rt=PO fr=QD fs=10 dn=1 m1=Aspirin (Buffered) v1=325 u1=mg
[10:00:00 20/09/2012] po tf=[12:00:00 21/09/2012] pt=Unit Dose rt=PO fr=QD fs=10 dn=1 m1=Atorvastatin v1=10 u1=mg
means in a colom many datatype value come together e.g datetime,varchar,int .
how we seperate them in different colom according to their datatype.
please help me it’s requried for my project.
Hi Pinal,
We are using SQL Server 2005. We have scheduled Push-Replication between Server-1 (Publisher & Distributor) and Server-2(Subscriber). We are currently designing on Disaster recovery plan.
Consider the scenario that Server-1 goes unavailable.
What are the steps to restore the Server-1 with publishing jobs and User Databases and re establish the replication with Server-2?
What are the back-ups of databases we need to take (like master, msdb of publisher etch..) for re-establishing the replication?
During the downtime of Server-1, there might be some data additions to the user database in Server-2. How to retain them while re-storing the Replication?
Thanks,
Sasikumar
Hi Pinal,
How do i migrate DTS packages from sql server 6.5 to sql server 2005 .
Thanks you i need your help please .
kind regards
Hello Elimane,
Insert the data of msdb.dbo.sysdtspackages table from source server to destination server.
Regards,
Pinal Dave
hi pinal,
do you please suggest any mysql support site
i have to need support
Hi Pinal,
Request you please look into the below.
I am working on the logic for the following scenario.
Table : t1 with cols (EmpId int , PresentDate DateTime , Shift Varchar(20))
No of rows are variable in the above table.Eg of data is
EmpId Shift id Attendance date
21213 1 12/02/09
21225 2 12/02/09
21213 2 13/02/2009
21225 1 13/02/2009
What i am trying to achieve is
EmpID 12/02/09 13/02/2009
21213 1 2
21225 2 1
Appreciate your help.
Regards
Pawan.
Hi Pawan,
You can use Cursor/While loop for it :-
we have a table which contains like
Invoice No Item Item Desc Item club Location
7/08-09 SER Service Charges for Repair CHD
8/08-09 SER1 Service Charges for Repair CHD
8/09-10 SRC Service Charges TMT CHD
9/08-09 SERVCH Service Charges for Repair CHD
10/08-09 RMAM ANNUAL MAINTENANCE CHARGES CHD
10/09-10 SERV1 Service Charges for Repair CHD
11/08-09 SER2 Service Charges for Repair CHD
11/09-10 SER3 Service Charges for Repair CHD
12/08-09 SER4 Service Charges for Repair CHD
Now I used Cursor for clubbing of item description for each invoice number i.e.
declare @locn_no varchar(20)
declare @locn_no_tmp varchar(20)
declare @invoice_no as varchar(20)
declare @invoice_no_tmp as varchar(20)
declare @item_no as varchar(20)
declare @description as varchar(250)
declare @description_tmp as varchar(2000)
select @description_tmp=”
declare getrun cursor for
select location,invoice_no, item_no, description
from invoice_item_tbl
order by location,invoice_no, item_no
open getrun
fetch next from getrun into @locn_no,@invoice_no,@item_no,@description
while @@fetch_status=0
begin
select @invoice_no_tmp=@invoice_no
select @locn_no_tmp=@locn_no
update invoice_item_tbl
set item_desc=@description_tmp+@description+’ , ‘
from invoice_item_tbl
where invoice_no=@invoice_no
and location = @locn_no
select @description_tmp=item_desc
from invoice_item_tbl
where invoice_no=@invoice_no
and location= @locn_no
fetch next from getrun into @locn_no, @invoice_no,@item_no,@description
if @locn_no_tmp = @locn_no
begin
if @invoice_no @invoice_no_tmp
begin
set @description_tmp=’ ‘
end
end
if @locn_no_tmp @locn_no
begin
set @description_tmp=’ ‘
end
end
close getrun
deallocate getrun
It may be done thorugh While loop
@Pawan.
Try using PIVOT OPeration.
Try below script, may be this could give you a start…
Create table t1 (EmpId int , PresentDate DateTime , Shift Varchar(20))
insert into t1 (EmpId, Shift, PresentDate) values ( 21213, 1, ’02/12/09′)
insert into t1 (EmpId, Shift, PresentDate) values ( 21225, 2, ’02/12/09′)
insert into t1 (EmpId, Shift, PresentDate) values ( 21213, 2, ’02/13/2009′)
insert into t1 (EmpId, Shift, PresentDate) values ( 21225, 1, ’02/13/2009′)
go
select * from t1
go
SELECT EmpID, [2009-02-12], [2009-02-13]
FROM (
SELECT EmpID, PresentDate, Shift
FROM t1) up
PIVOT ( max(shift) FOR PresentDate in ([2009-02-12], [2009-02-13]) )AS pvt
ORDER BY EmpID
If your concern is rows are variables, meaning there could be multiple dates and if there are multiple dates then in your final output you will have more columns, then I would suggest you to use dynamic SQL.
Let us know if you need help with this.
~ IM.
what is o/p of following
SELECT EmpID, [2009-02-12], [2009-02-13]
FROM (
SELECT EmpID, PresentDate, Shift
FROM t1)
This is not the correct syntax
You have missed derived table name and referred non-exist columns
Hi Pinal,
Everybody getting answers and suggestions from you and your team for their queries.But i haven’t received any thing.
Just My question is:
In our organization, they are planning to migrate from Informix Database to SQL Server 2005 database.
I need to prepare documentation.I am new to prepare documentation.Could you help me where and how do i start?
Hi,
this is Lokesh, I have joined Sql server DBA Course, i am about to complete the course in a week..
So, I want to know is there openings for Freshers? Please let me know, if any openings is there..
Because I am not having experience.. I am planning to do Certification in DBA, I hope it will be an Extra Advantage.
Please guide me…>
Regards:
Lokesh.R
Pinal, even better than querying sys.master_files for the size of the data/log file? How about getting even more info via the stored proc I created and wrote about here at mssqltips.com: http://www.mssqltips.com/tip.asp?tip=1629
- Tim
Hi,
I created linked server connection to Access 2003 (.MDB) file from SQL server 2005 server.
Can I create new columns in access database dynamically through this linked server connection and dump data?
I tried to execute alter table command using OPENQUERY, did not owrk….
Please help me in solving this issue…
Thanks for your help….
Surya Prasad.
Hello Surya Prasad,
You can add column on linked server using below syntax:
EXECUTE ( ‘alter table statement ‘ ) AT linkedserverName;
Regards,
Pinal Dave
dear sir,
i have reading all your solution that help me a lot.
but here i can’t find that how to merge same database with same table and attributes that exist on different system. i want to merge one system database in other
how can i achieve it.
if you have some spare time then kindly please make a query for this above mentioned problem
i hope this is helpful to others.
thanks with respect
Hello Dave,
How can we migrate Sybase database to SQL Server or in Oracle 10g, kindly tell me the procedure.
Hello Shiv,
You will have to perform merge using import/export wizard or t-sql query for every table.
Regards,
Pinal Dave
dear sir,
Please, received my thank first that you gave me your valuable time and suggestion.
what i do when using mysql.
actually i have to work with SqlSever and MySql both.
my offline database is distributed on 3 different sites and here the problem i have facing to merge all sites data in to single one database.
i have searched more but would not get any fruitful result
for this i have created a new database and restore all 3 sites database but the newer database data is over write the old one.
kindly, Please tell me any procedure regarding mysql
Hello Sir,
I am working on SQL Server 2000 and Development of DTS Packages. Now i want to learn sql 2005 and Datawarehousing. Could you please suggest me some books (pdf format) so that i can learn myself.
Thanks
Sandeep
pinal,
in CSV File i have 3 columns and in Destination Table i have 4 Columns and the Extra Column is not Null(Should have some value inserted)
this 4th column value is populated from the other Column of the Table.
destination columns=csv file + other column from other table.
i’m need to use Bulk Insert or any other option ,in this situation how can i proceed.
Your Help is Appreciated
Thanks
Sudarshan
@Sudarshan
Perform bulk copy in some other table , or may be temporary table and then insert into your destination table.
~ IM.
Hi Imran Mohammed ,
I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,
In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication
please tell me
Any Script please Provide me
Thanks&Regrads
Harishkumar.M
Hi,
Im always checks ur site for my sql querys.
I want to implement keyword search in sqlserver2005.
after googling i found FULL-Text search with indexing.
I have a problem o how can identify which colum i want to search depends on keyword.
i want to get accurate result depends on key word.
my table fields are
[CollegeName] (varchar)
[GroupName] (varchar)
[CollegeAdd1] (varchar)
[CourseDetails] (xml)
maximum details are stored in xml field.
xml is like this
Engineering
Engineering,Engg,
Btech
FullTime
Evening
Telugu,English
AICTU
30000
CSE
Yes
C.S.E, Computers
English
60
sc
Bc
60
sc
Bc
Govt Employee
Software Engineer
Hardware Engineer
Aronatical Engineer
CAT
sc
dear anand,
what you aspect,
i mean to say you want the solution in which version
2000, 2005 or 2008
clarify it
Hello Anand,
A full-text index can be created on multiple columns.
For xml datatype column create XML indexes.
Regards,
Pinal Dave
Greetings Pinal Dave,
Our one of the project using Hyperion Essbase 6.5.3 as backend, now we are planned to migrate into SQL Server 2005. Essbase is Multidimensional Database, in our application we can able do the following: Insert, Update, Delete and Analytical Reports, Ad-hoc Report. How can we do the same in Sql Server 2005?. Please advice me and provide sample code/link.. Hope you reply as earlier…
Hi Pinal or anyone
In our SQL Server 2005 (on Windows 2003 64bit SP 2 with 10 GB RAM), we are getting these error messages very frequently:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 18832, committed (KB): 5390488, memory utilization: 0%.
I searched on Google, found some articles even from Microsoft website, but couldn’t solved our problem. Actually some of our clients have complained about very slow response of the server, even when I tried to login to the server (both thru Management Studio on my machine and Thru Remote Login direct to the server), I couldn’t login. However, after a short while I was able to login. I couldn’t find any unusual activity or error message in any log except the above error messages.
http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
http://support.microsoft.com/kb/918483
I had my Wintel guy applied a solution from this article:
http://msmvps.com/blogs/omar/archive/2007/09/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx
But I am still getting the error messages, even more frequently, (it used to be 20 to 30 minutes now after every 5-10 minutes) even though nobody is complaining, but still it is not good sign – I think.
May be useful for the investigation, in our server the Virtual memory is:
Total Paging File size for all Drives:
Minimum Allowed: 16 MB
Recommended: 15262 MB
Currently allocated: 4092 MB
Please let me your observation and suggestions – what could be the reason and solution. Thanks.
Shoaib
From Canada
I have a question related to defragmenting indexes. I can identify the indexes I want to defragment and rebuild them one by one through a temp table and a WHILE statement. I have a SQL Server Agent job run the process weekly. So here’s the question. Why did 3 of the 119 indexes degragmented actually have a higher defrag percent after the process than before? Most percentages looked a lot better after the process, some were marginal, but higher? I don’t understand that and hope you can shed some light on the subject.
Hello,
I need a help. As iam not from software background. but i need to execute a query in sql server management studio express periodically.
SELECT Table1. * INTO Table2
FROM Table1
WHERE (“Date_time” BETWEEN ’1/11/2010 11:52:18 AM’ and ’1/11/2010 12:26:35 PM’)
the above query should be executed automatically everyday morning 9.00 am. Is this possible in this tool. ????
i have tried to do this with internet assistance.
the following steps were what i have tried:
1. i have created a batch file with the following script
@echo off
osql -U sa -S .\gft -P Fiduciary -i Script.txt -o output.txt
here, the script.txt file contains the query which creates table 2 from table 1, and i have created an output .txt file also.
2.if i double click on to the batch file it was not executing anything. command prompt was just opened with a blank screen and it is not closing down.
3. after i opened the command prompt and typed the below script there.
@echo off
osql -U sa -S .\gft -P Fiduciary -i Script.txt -o output.txt
and i just gave enter. after processing sometime it is giving me the below error.
[SQL Native Client]SQL Network Interfaces: Error Locating
Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.
4. so to change the default settings and to allow remote connection i have done the settings with the following site link assistance :
http://support.microsoft.com/kb/914277
After that also i have been getting the same error.
5. So now i have opened the command prompt and just typed the blow script
osql -U sa -S
and it was asking for password and i just press enter, after processing sometime it is giving me the below error:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>cd c:\
C:\>osql -U sa
Password:
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL
Server [2].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.
6. so to sort out the SQL Server [2] error i again use the below site link assistance:
http://www.cryer.co.uk/brian/sqlserver/hresult_0x2_npp_error_establishing_connection.htm
in that they mentioned about SQLEXPRESS which suppose to be in SQL Server Configuration Manager (Local) → SQL Server 2005 Network Configuration. but in my system i could not found that.
After all above i came here. i need it severly.please anybody explain me in detail, ASAP.
Thanks in advance.
Regards,
Ramesh.S
Hello Ramesh,
In the osql command provide the password of “sa” login using -P switch.
If working on SQL Server 2005 or later version then use the sqlcmd utility.
Regards,
Pinal Dave
I want to create report in SQL 2008 using SSRS and SSAS. Will anybody help me on this?
Hi Pinal ,
I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,
In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication
please tell me
Any Script please Provide me
Thanks&Regrads
Harishkumar.M
Hi Friends,
Note: Let me know if this is the right place to post this question.
I have an issue with SQL Server Reporting Service 2005 explained below.
I have three Servers
ServerA :- SQL Server 2005, 32 bit machine, Reporting Service installed.
ServerB :- SQL Server 2005, 32 bit machine , Reporting Service installed.
ServerC :- SQL Server 2008, 64 bit machine, Reporting Service NOT INSTALLED.
Please see below and let me know your suggestions as I am getting different errors in different scenario.
From event logs and error logs I found below errors.
1. Accessed the web site on ServerA (staging server) and used connection string to connect to ServerC. ServerA has reporting service and accessing data from ServerC (SQL Server 2008 on 64 bit machine,Windows 2003).
In this case below are the errors I got for reporting charts in eventlog and reporting service log file.
–Login failed for user ‘ServerC\xyz’. Reason: Token-based server access validation failed with an infrastructure error.
Check for previous errors. (CLIENT: 10.48.145.14)
Here the irony is that I was able to login as “ServerC\xyz” from my local machine and could access C drive of the ServerC.
Here, first I am not able to understand how it picked up above user. As I was running site from my local system, I did not specify above user anywhere and thought that the site is running under “Network Service” user for domain access by default and that user will be used to login to SQL Server 2008 machine, but I was wrong. We have ‘xyz’ user on all the servers (staging, production, not on client where I am accessing site) with same password.
Also it says failing to connect with above user, then with which user it is accessing data, as I am able to see data in data grid from the ServerC database but only charts are failing?
And the strange thing is, on ServerC, I see Kerberos or negotiate as the authentication package in Security event log for all the connection with other machines but for the connection from ServerA, I see “NTLM”. Please also let me know if you can, how to verify with which user connection are made from reporting service or w3wp process to SQL Server 2008.
2. I accessed the site on ServerB (its production server and this also has reporting service, 32 bit machines) and gave connection string of ServerC, I got below errors from event log and reporting service logs.
–Failure Audit:- Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.48.1.203]
–Error:- SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.48.1.203]
What do you suggest for these?
Appreciate your time and help.
Thanks
Hi,
What is most suitable data type in sql server 2005 to store time duration? As there is no such data type “time”, so most people use datetime; other ways can be to store in float/decimal types like 1.5 can be considered as 1hr & 30mins, depending on your interpretation.
What is most recommended?
-Moin
FLOAT is the worst kind of way to store durations or almost anything (well, maybe REAL would be worse). You end up with weird rounding errors eventually. If you need to use decimals, use fixed point types like NUMERIC.
Personally I would use INT or BIGINT to store the durations in minutes, seconds, milliseconds – depending on the accuracy required. It’s easy to convert integer value to for example .NET’s TimeSpan type and display it to user by calling ToString method.
@Moin
Instead of storing the duration, is it possible to store Start and End? IOW, record the actual data elements instead of a derivation.
@moin
For SQL Server 2005 you have to use datetime which is the best datatype for storing datetime values, sql server 2008 has data type TIME, if you want to store only time and this is only on your interpretation then you can modify the value of your date time and store it in some varchar column.
What kind of time you exactly want in your column?
Hi Pinal,
How can you set difference total record count of the table?
I have 3 tables Table A, Table B, Table C,
that gets wipe out every friday at 9:00 am and new records insert by schaduled job in each table.
If I want check difference between total record count of new and old records in all 3 tables, How can you suggest me to do that?
@Jay
How do you identify what is an old record and what is new?
because old record does not exist, all 3 table gets wipe out every friday and new data inserted.
One approach is record the count in different table before truncating/deleting the table. Also record the current datetime and table name in that table so that it would be easy to find it out based on the lastest date
Hi,
I’m getting error msg in SQL management studio when creating sql login. Here is the error msg could not continue with NOLOCk due to data movement. (microsoft sql server, error:601) can someone please advise on the error that keep on prompt. Thanks
Hi Pinal
I was reading your “Important Guidelines for SQL Server”. I am posting this question at a cost of appearing daft.
You mention that table names should end with “s”. Isn’t that against the entity modelling 101 course which I took a while ago?
We were told that entity names, (from which we derive our table names), should never be pluralized, because we are always referring to a single instance of that particualr entity at any given time. Which in turn makes it easier for us to determine whether relationships are many-to-one or otherwise. Or is it just a case of semantics, did I miss the train completely?
Thanks for the wonderful work. Your blog is an inspiration.
Regards,
Thato
I wouldn’t pluralize table names either. I use ORMs and especially Entity Framework heavily. If I got, let’s say, table named Cars that converts to class named Cars in the code it would look really silly to say:
Cars myCar = new Cars();
This instantiates a single Cars-object into memory but the type says Cars which means many objects. This could easily confuse coders who are not so proficient with the C# code. Much more clear would be:
Car myCar = new Car();
Of course I could change the name of the class to Car and ORM mapping takes care that I’m dealing with table Cars but that feels silly also. Now I would have to remember that class Car is written to table Cars.
I have googled extensively on this issue and cannot find much information related to this problem.
I am using sql server database encryption with a symmetric key protected by password.
in my c# code, when i want to read some data, i
1. submit an “open symmetric key MyKey decryption by password = ‘mypassword’” SqlCommand to open the symmetric key.
2. call my stored proc using a new SqlCommand to return me back encrypted data.
3. submit another new SqlCommand “close symmetric key MyKey” to close the symmetric key.
if i issue the 3 commands in that order, the data is returned but *not* decrypted.
when i wrap all 3 commands in a *transaction*, the data is decrypted and returned.
since some stored procs are taking a long time to return it is causing deadlocks. anybody who’s anybody knows that you dont read data within a transaction but it seems unless i open the key, read my data and close the key in a transaction the key does not remain open.
has anyone else seen this behaviour?
pinal – can you shed any light on this?
many thanks in advance for anyone’s help
–alan
Hi Pinal,
Excellent post, I configured my Database Mail using your blog, but how do I ask the stored procedure sp_send_db_mail to send all the file names in the back job when the backup is successful
or fails. I have created a job and sends test mail until the following: but when I add @file_attachements = ‘C:\Program Files\Microsoft SQL Server\….., it states access
denied.
Use msdb
GO
EXEC sp_send_dbmail @profile_name =’DB Mail Profile’,
@recipients =’csaha@imsa.edu’,
@copy_recipients =’csaha@imsa.edu’,
@blind_copy_recipients=’csaha@imsa.edu’,
@body =’This is a test message’,
@subject=’Database Backup’,
@body_format=’TEXT’,
@importance=’Normal’,
@sensitivity=’Normal’
Regards
Chitra
SELECT
@SourceNote = SUM(TotalPatientCount) / DATEDIFF(MONTH, @MinDate, @MaxDate)
FROM
Summary.Remits_DrugView S
JOIN @Top5 TP ON TP.Name = S.PayerName
JOIN @Drug D ON D.DrugCsv = s.DrugMstName
OR ( ISNULL(@DrugCsv, ”) = Replace(S.DrugMstName, S.DrugMstName, ”) )
JOIN @Age A ON A.AgeCsv = s.Age
OR ( ISNULL(@AgeCsv, ”) = Replace(S.Age, S.Age, ”) )
JOIN t_ReportRegions_D R ON R.zipcode = S.ZipPlus3
AND R.RegionGroup = @RegionGroup
JOIN @state ST ON ST.StateCsv = R.State
OR ( ISNULL(@StateCsv, ”) = Replace(R.State, R.State, ”) )
JOIN @Region RC ON RC.RegionCsv = R.RegionName
OR ( ISNULL(@RegionCsv, ”) = Replace(R.RegionName, R.RegionName, ”) )
Hi
i need to join with that @Top5 for one report and dont need to join for another.
Is there any condition i can use .so that i can get two reports from only one report.
plz have a look when u have time
Thank You
–
REGARDS
NAVEEN GADDAMREDDY
I have a filestream on a SQL 2008 dbase and the volume is becoming full about 1.5TB. What is the best method create a second filestream will all new data be written to filestream. If load balancing is used will it write only to the volume that has enough storage? Thanks in advance
Hi Pinal,
I think I need your help in sorting out an issue I have at the min.
The existing code is in PHP using RESTful web services with CURL extension.
The Apache web server requests hsbc webserver and retrieves the response as XML data.
We are migrate to that code into sql server.
I’ve implemented SOAP web services using web services tasks in some projects. But I can’t do that here. As there are no methods exposed to us or no WSDL exist in REST.
Can you shed some light into this and help me how I ho about it.
I am currently analysing the existing PHP code and come to a stage I have interpreated correctly.
And looking forward to see your reply
Thanks in advance
Seshi
Hi,
I liked your articles. A small suggestion for you to the list of articles (only to this page).
1) Add a column to group by category – SQL Server, SQL Authority (as we know that you work most of the time two categories at top level). You may have multiple sub-categories
2) Add table filter using JQuery
http://plugins.jquery.com/project/uiTableFilter
3) Add table sorter using Jquery
http://tablesorter.com/docs/
Hope you consider it.
This is only useful for guys coming back repeatedly.
Regards,
Sreedhar
lbObjectTypes
Hi,
I am Raju working as a Web Developer.
I am into a problem, please let me out.
I have a table containing colums (TopicId, SubTopicId, QuestionNo, DifficultyLevel, RevisionLevel)
Revision Level column is for maintaining different versions of a question.
Difficulty Level will tell the toughness of the question.
I have a query which will get me all the questions with latest Revision Level
(SELECT MAX(revisionlevel) as revisionlevel, QuestionNo, topicid, subtopicid FROM table1 where subtopicid = 12 GROUP BY QuestionNo,topicid,subtopicid order by QuestionNo )
No I want the questions to be fetched based on Difficulty Level also.(1 to 4 As Easy, 5-7 As Medium and 8-10 as Difficult)
For this am using the below query.
(SELECT MAX(revisionlevel) as revisionlevel, QuestionNo, topicid, subtopicid FROM table1 where subtopicid = 12 and difficultylevel between 1 and 4 GROUP BY QuestionNo,topicid,subtopicid order by QuestionNo )
This query is executing but the result is not that I need.
Here the condition “difficultylevel between 1 and 4 ” will consider all the questions titled with difficulty level 1 to 4 irrespective of latest revision level.
Please help me in writing this query which should consider latest revision level ( latest version of question) which has 1 to 4 difficulty level.
A question will have
TopicId SubtopId QiestionNo DiffLevel RevisionLevel
1 1 1 2 1
1 1 1 4 2
1 1 1 6 3
1 1 1 8 4
IN the above table I should not get first 3 revisionlevels even the condition is “difficultyLevel between 1 and 4″
This is any how am retrieving with condition “difficulty level between 8 and 10″ but not for “difficulty level between 1 and 4.
In the above table
TopicId SubtopId QiestionNo DiffLevel RevisionLevel
1 1 1 7 5
Then question 1 should be retrieved only for condition “difficulty level between 5 and 7″ and not for any other condition.
Please help me in resolving this.
Thanks in Advance.
With regards.
Raju-
why the following error comes
“Microsoft.ReportingServices.ReportProcessing.Unhandled ReportRenderingException
in SSRS 2005?
We tried all possible ways, the report has image data in bulk,IS it because of that?
Please give the solution if found?
Thanks
Mahesh
Hi,
I have Question Sql data Encryption? I want to encrypt the data in Sql 2005 & 2008 .
What are the data type can we use ?
If it is Only Varbinary data type .. Please let me know the reason and what is the length of varbinary should we use.
Hi Pinal ,
I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,
In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication
please tell me
Any Script please Provide me
Thanks&Regrads
Harishkumar.M
Hello Harish,
Yes, COPY_ONLY option is to take FULL or TRANSACTION LOG backup without affecting the LSN sequence.
Let us know what is your doubt.
In SQL Server 2000 there was no way to implement the functionality of COPY_ONLY.
Regards,
Pinal Dave
HI pinal ,
Thank you For giving reply.
No.of interviewers Asking How to Copy_only in Sqlserver2005,
In Sqlserver 2000 Production server that using in log shipping or mirroring ?.
With out LSN BreakSqlServer2000 Production to My test Enviroment how to take any backup(Environmental refresh) Any other .net(C#) or Any oTher third party Tools is there please tell me
Please Don’t Angry…. pinal, how much busy this current possition you are giving Replay. thank you
Thank you once Again Giving Replay.
Thanks&Regrads
Harishkumar.M
Hi Imran,
I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,
In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication
please tell me
Any Script please Provide me
Thanks&Regrads
Harishkumar.M
we have couple couple of user they are client, they have SQL 2005,
when they try to create view from management studio, they get message “invalid default schema” but when they write query it works fine
what could be the issue
You should include schema to tables when creating views
Hi Pinal
I wanna use complex stored procedures but not able to use it plz guide me about some best book for stored procedure
Urs Ravi
Hi Pinal Deve i am a fresher i need Group by and order by clauses in sqlserver in detail. Could you explain me please
SQL Server help file has informations with examples
Dear Sir,
How do i concatenate NVARCHAR string variables in a sql server 2005 stored procedure.
Thankx in Advnace
Venkat
Hello Venkat,
String values can be concatenated using the + operator. For example:
DECLARE @SqlString nvarchar(10)
DECLARE @SqlString1 nvarchar(10)
SET @SqlString = ‘ABC’
SET @SqlString1 = ‘xyz’
select @SqlString + @SqlString1
It returns “ABCxyz” as output.
Regards,
Pinal Dave
Hi Pinal Dave,
can you let me know the answer for the below solution please..
I have Question Sql data Encryption? I want to encrypt the data in Sql 2005 & 2008 .
What are the data type can we use ?
If it is Only Varbinary data type .. Please let me know the reason and what is the length of varbinary should we use.
Hello Arun,
In SQL Server 2008 there is two type of encryption TDE and cell-level. At first you have to decide which one is fulfilling your requirements.
For TDE no change is required in database design, schema and application. But its just encrypt data while writing on disk and decrypt data while reading.
In cell-level encryption you can encrypt data to save from unauthorized access. But this needs change in database schema, and code to read and write in tables. The encypted data can be stored in varbinary data type column and the maximum length of encrypted value could be 8000 bytes.
Regards,
Pinal Dave
Hello Pinal and friends.
In the Q and A of the interview section, can you please include
1, Compare the advantages and disadvantages of a, Log Shipping; b, Data Mirroring; c, Clustering
2, Compare the advantages and disadvantages of a table variable and the temporary table.
3, Partition Views and Partition Tables.
Can you please explain each of these topics separately. (I have read your informative articles on partition tables)
I suggest that you split (tag) the interview questions as general, developer, production and BI.
Regards
Hi Pinal,
What is the difference between Set and Select ?
Can you explain with example?
Thanks in Advance
Chethan.K.V
Chetan,
“SET” is set based operation.
“SELECT” is a SQL clause which uses to return either one or more than one attribute from the entity.
For e.g.
1. DECLARE @Val INT
SET @Val = 1
2. SELECT TOP 1 name FROM sys.objects
In above example, SQL Server generates the plan only for second query.
SET can be used to assign a single variable only
SELECT can be used to assign multiple variables
Also SET is ANSI standard
Hi Pinal
I am trying to insert from one database table to another database table.When i a m creating procedure it shows one error.
SET IDENTITY_INSERT ‘sysname.dbname.owner.tablename’ ON
The object name ‘sysname.dbname.owner.tablename’ contains more than the maximum number of prefixes. The maximum is 2.
Please help Me
@ravikumar
SET IDENTITY_INSERT cannot be set for a remote DB.
How to return a result set from a cursor
Good Day Pinalkumar Dave,
I was wondering if you could speak too or refer me too any articles that might shed light on the above subject:
Using Top N with a Where Clause SQLServer 2008
I have found that if I run a query like
SELECT TOP 15000 field1
FROM tbl1
Inner join tbl2 on tbl2.id = tbl1.id
WHERE field2 = x and field3 = y and field4 = z
Let’s say I get 14500 records, yet when I run the same query but get rid of the TOP 15000 I get 200000.
Why wouldn’t I get 15000 records?
Any ideas?
Thank you
RICHARD HUNT
Software Architect
360Facility
Hello Richard,
Does the number of records retuned 14500 or change every time with TOP clause?
It would really need more investigation :)
Regards,
Pinal Dave
Sir,
If you are asking does the number fluctuate, I don’t believe so. I would need to confirm but I believe that it does not. I am thinking that it is getting top 15000 records from the join first and then looking at the where? I don’t know…I’m stumped.
In the end it doesn’t return what I am looking for.
Any information you can provide would be greatly appreciated.
Thank you
RICHARD HUNT
Software Architect
360Facility
If the total number of rows returned without top is less than 15000, you get that much number of rows you use top 15000
But it would never return more than the number of rows specified at the TOP clause
Also top without Order by clause doesn’t make any sense
Hi Pinal,
I want to be a SQL professional.
Can you please help me learning sql server and what are the process to learn sql server and ho di acheave it.
I am redy to work hard but I wan the guaidence.
Please help.
Thanks,
Ranjan
Hi Pinal,
I am big fan of you and I do regularly visit your site for many nice tips.
I am having problem with one of table in our database which have 200milion plus records and schema look as below.
CREATE TABLE Prices](
AssetId [int] NOT NULL,
Date [smalldatetime] NOT NULL,
Supplied [smalldatetime] NOT NULL,
Price [float] NOT NULL,
Currency [int] NULL,
LastChangeDate [smalldatetime] NULL,
CONSTRAINT [PK_DS_T02_Prices] PRIMARY KEY CLUSTERED
(
[AssetId] ASC,
[Date] ASC
) ON [PRIMARY]
I need to change Date column type from smalldatetime to DateTime.
I wrote script to drop primary constraint then change column data type then put back primary key constraint then finally rebuild the index.
My script only able drop script then it fails to do the rest of the tasks.
I need your help.
Thanks
Nachi
Please let us know the script that you are running and error that is occuring.
Regards,
Pinal Dave
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID(‘tempdb..#tmpErrors’)) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N’Dropping constraints from [dbo].[Prices]‘
GO
ALTER TABLE [dbo].[DS_T02_Prices] DROP CONSTRAINT [PK_Prices]
GO
IF @@ERROR0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N’Altering [dbo].[Prices]‘
GO
ALTER TABLE [dbo].[Prices] ALTER COLUMN [Date] [datetime] NOT NULL
ALTER TABLE [dbo].[Prices] ALTER COLUMN [LastChangeDate] [datetime] NULL
GO
IF @@ERROR0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N’Creating primary key [PK_Prices] on [dbo].[PK_Prices]‘
GO
ALTER TABLE [dbo].[Prices] ADD CONSTRAINT [PK_Prices] PRIMARY KEY CLUSTERED ([AssetId], [Date])
GO
IF @@ERROR0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT ‘The database update succeeded’
COMMIT TRANSACTION
END
ELSE PRINT ‘The database update failed’
GO
DROP TABLE #tmpErrors
GO
Hi Pinal,
I have slightly changed the table and column names while posting to you.
Hi Pinal,
Thanks a lot for your forum and articles. You know Whenevr i face any problem related to sql Server .I always gets the Exact solution on your site only.
Thanks a lot.
Keep it up.
can we create clustered index of non primary key?
we can’t create clustered index on non primary key just
because of the simple reason that in clstered
index,physical sorting is done while in non primary key
there might be hundreds of duplicate entries. so, if we
create clusterd index on non primary key it will get
confused and then error.
Nikhil,
Clustered index can be created on a non-primary key column as well as on a column that has duplicate values.
Regards,
Pinal Dave
Thanks Sir,
I implemented what you wrote comment.
Yes, of course you can. Just don’t create PRIMARY KEY as CLUSTERED.
Yes.
Also note that when a primary key is created by default clustered index will be created on it provided there is no other clustered index for other column
Hello Pinal,
I need your help. This is the best place where i can come when i am stuck up :). Here is my problem.
I have table in which i have added a new column in the schema.
Now there is an Bulk insert statement which inserts into this table from another table. The bulk insert statement looks like this
INSERT into destinationTable
SELECT *,getdate() as created_ts FROM sourceTable
Now i have added a column in the destination table, the same column is also added in the sourceTable.
But when the select is done from the sourceTable the last column which comes is a date (from getdate()) but in my destination table the last column is my newly created column (which char(1)).
Due to some reasons it is not possible for me to change the insert statement (to remove the *).
So i am thinking is there some mechanism to give the column order explicitly in the ALTER statement (while adding the column).
Awaiting for your reply (much needed !)
Regards,
Taher
Hello Taher,
Yes, you can define the column order. Please visit my blog:
http://mssqlonline.blogspot.com/2008/04/sql-server-change-order-of-column-in.html
Regards,
Pinal Dave
Hi Pinal,
Very useful blog. keep it up. I have a question. I have a scheduled job to delete .bak files older than 3 days but the files are not deleted. I manually have to go and delete these files. I am using 2005 sql server and the Recovery model is simple. Any help is appericiated.
Regards,
Abdul M.
Post the code that is used to delete the files
Hi to All,
How can drop all user defined stored procedures and functions from database in single query
Can u tell me that sql query.
Thanks
- Samy
One simple method is to run the following code
select 'drop '+case when type='p' then 'procedure' else 'function' end +' '+ name from sys.all_objects
where type in ('p','fn','tvn')
and is_ms_shipped=0
Copy the output
Run them again
Hi,
Thanks for ur reply….
Hello Pinal,
I am inserting lots of rows (near 12 crore) in database using BULKINSERT syntax.
But my problem is some rows are different format like…
I have three columns in table: Firstname, LastName and Phone.
My data file contains two types of row in files.
1) sumit,sanghani,”123456789,123456789”
2) sumit, sanghani,123456789
When I insert rows using “,” separated value first row return 4 column and second row return 3 columns.
Please reply asap.
Regards,
Sumit sanghani
If the data contains a comma, it would be split to different columns. You may need to use different seperator like : for phone number
Sir,
I’m wondering if there is a way to use Visual Studio 2008 and VB.NET to build a front end while using a simple Access 2007 database on a common server for the back end. Can ADO.NET pass queries to the Access database which in turn uses it’s own copy of the Jet Engine to do the work? I know this sounds nutty but I have all the desktop resources I need but I’m having a hard time acquiring SQL Server resources at my company.
Respectfully,
William Rich
I would like to be able to pass the name of the database (through a function, for example). I want to be able to do something like
declare @run nvarchar(25) = ‘[Feb10]‘
select * from @run.dbo.Companies
This doesn’t work. I’ve been getting around this by doing:
declare @sql1 nvarchar(50)
declare @run nvarchar(25) = ‘[Feb10]‘
set @sql1 = ‘select * from ‘+@run+’.dbo.Companies’
execute(@sql1)
This works for now, but it seems like there has to be a more efficient way. As the queries I’m using are becoming increasingly complex, I worry that this method is making them go too slowly. Is there a better way to do this?
Remember when concatenating SQL statements like that you risk to open up your database to SQL injection attacks. If you need to build up dynamic queries use predefined views and sp_executesql procedure with which you can parametrize your queries.
Also make sure that if client needs only select data from the database give the client read rights only and only to the procedure that’s building up the dynamic SQL. Revoke any other (unnecessary) rights.
And what comes to performance, I’ve read that the optimizer can build up execution plan when sp_executesql is used so it can use that on subsequent calls.
This is a derived table approach that can avoid SQL Injection
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx
I have a setup a test server using Production server.
I have over 100 jobs and SSIS packages in Production server.
I also want logins and all security into test server.
I have get to everything into test server without shutting down production server. Like I want to setup test server with 1 day old data from prod server.
Can someone help me with this and give detailed steps to do.
Thanks in Advance.
Hi Pinal,
I am a fresh graduate and I am new to SQL Server administrating stuffs. I want to know how to show users of a particular roll using a stored procedure..
Thanks in advance….
Hi Piumi,
Add a where clause to filter for a Role in following query:
select u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ‘public’
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM sys.database_principals u
LEFT JOIN (sys.database_role_members m
JOIN sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE ‘R’
Regards,
Pinal Dave
Hi pinal,
I have two Questions
1.
On what scenario we create clustered index and non clustered index on a table? Can you explain with example?
2.
What is extended Stored Procedures?
Thanks in advance
Chethan.K.V
@Chethan.K.V
1. If you do not know the answer, use SQL Server’s defaults. It will make the PK CLUSTERED (if it is there before any other CLUSTERED INDEX), and all other INDEXes will be non-CLUSTERED.
2. http://msdn.microsoft.com/en-us/library/ms187644.aspx
Extended Stored Procedures
ms187644.note(en-us,SQL.100).gifImportant:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead.
Extended stored procedures let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.
ms187644.note(en-us,SQL.100).gifNote:
CLR Integration provides a more robust and secure alternative to writing extended stored procedures.
Greetings!
In spite of any and all negative arguments about a vertically aligned database, my customer has mandated its use. We’ve strongly discourged its use with no success in our attempt to dissuade them.
With that I have a question with regard to returning horizontal rows for a collection of records per “System.” A system – for our purposes – would represent one horizontal record with all its attributes stored vertically (a collection of individual records which make up the one System’s record).
What I need to do is create a view that returns (in horizontal form) all the System records as a table from which I can perform a Select statement against it.
A view doesn’t support the SQL Cursor function, a SQL View doesn’t permit the use of stored procedures as its data source.
SoI’ve been reading about Table-Valued functions. The return statement in a TVF typically has the fields hard-coded and I’ve gotten that to work in my Vertical database model. However, I need to have the TVF dynamically build the fields in the table it returns. This is required so that when a field is added or deleted from the Systems schema design, the target field will either be included (if added) or will not cause the function to error (if deleted).
Any assistance in getting this to work would be extremely helpful.
Bob
It seems you may need to use dynamic PIVOT
Refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Hello Dave,
I am new to sql and I am trying to figure out the easiest and shortest way to pull out the highest sale price from the sale table as well as few other information from product and customer table. I came up with two ways, please let me know which way is better. If you have a different suggestion, I would really appreciate it.
Here is my code. Both statements return the same result:
select top 1 c.Customerid,c.firstname,c.lastname,p.productname,p.category,s.saleprice
from customers c,products p,sales s,
(select top 1 max(saleprice) as saleprice,customerid from sales
group by customerid order by saleprice desc) x
where c.customerid=s.customerid
and s.productid=p.productid
and s.customerid=x.customerid
order by s.saleprice desc
–********************************************
select * into ##temp from sales
where saleprice in (select max(saleprice) as b from sales)
select c.Customerid,c.firstname,c.lastname,p.productname,p.category,(select saleprice from ##temp) as price
from customers c,products p
where customerid in (select customerid from ##temp)
and p.productid in (select productid from ##temp)
Warm regards,
Liviu
Here is another way:
select top 1 c.Customerid,c.firstname,c.lastname,p.productname,p.category,s.saleprice
from customers c,products p,sales s
where exists (select top 1 max(saleprice) as saleprice,customerid from sales
group by customerid order by saleprice desc)
and c.customerid=s.customerid
and s.productid=p.productid
order by s.saleprice desc
Pinal Dave:
Thanks for rescuing me from the ever annoying appearance of “sqldumper library failed initialization” error message. The Microsoft.com site had no clue how to fix it. You’re on top of your game.Bill Gates should send you a commission.
Best,
Bill
hey Dev,
i have a Table1 ,Col1 has a Values ‘A”B’,'C’,'D’ etc
and other Table2 has a columns ‘A”B’,'C’,'D’ etc(from Table1)
i need to get the data of the Columns of ‘A”B’,'C’,'D’ etc of Table 2.
Table1 has
Col1(Values)
———————
A
B
C
D
Table2
A| B| C| D (Four Columns)
——————————
x| Y|Z|V|
i need to get the data in col A(x),Col B(y),Col C(z),Col D(v)………
Please guide me,
Thanks in Advance…….
In tables1, are values fixed or dynamic?
Hi Pinal,
Myself Anup and I am a regular reader of your awesome BLOG.
I have a situation and I would request your advice in this regard.
Suppose I have a mirroring session which is working on High Availability mode and Data Transfer between Principal and Mirrored server is Synchronous.
I understand that data will flow via Principal and will be committed first in Mirrored server and then it will be committed in Principal and acknowledgement will be sent to the application.
What if: The mirrored server is down? Because there is a commit dependency on the mirrored server.
Incase the environment is a high transactional system, then log file will be full because commit dependency is there.
Please advise how the system will work for this situation.
Thanks in advance.
Thank You,
Anup
Hi Pinal,
How are you?
if Database and Shareopoint server in Same box, i am able to see Data in BI webparts,But if data Base is diffrent server/Different machine
1. If Sharepoint URL Opened in Different Machine,then Data is not displaying BI webParts.
Showing below error
This error BI web parts
Can not create instancee
This below error from Reportserver
[An error has occurred during report processing.
Cannot create a connection to data source 'PBXTracking'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors ]
Regards
Sisira
Appshark pvt ltd,Hyd,India
if i open the SP Url in differnt mechine,below error diaplaying in WebParts
Unable to connect to SSOPBXTrackingInstance.
Hello Pinal,
Few days back I attended a interview. In that i was asked a question, as below
There is a table in which there is no Primay key constraint and the records in the table are duplicated (as shown below)
ID Fname Lname
—– ———— —————-
1 XYZ PQR
1 XYZ PQR
1 XYZ PQR
2 ABC DEF
2 ABC DEF
2 ABC DEF
I need to write a single delete query which will delete all the duplicate records and keep only a unique copy of each record. I said that it can be done by doing a bulk insert into a temporary table, by selecting from source table doing a group by on all columns and then truncating the original table and then again doing a bulk insert from temp table.
But the interviewer said that it can be done by a single delete statement. I tried to search on the internet but i am not able to find any answer. Can you please suggest….
PS : I cleared the interview :) :)
Hi Pinal,
I tried to get answer but i can’t find answer, can you please give that answer.
Thanks
Samy.
One option is to use row_number() function if you use version 2005 or above
Refer point 6
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
Hello Pinal,
Got the answer to my previous question (on this blog only).. Thanks a lot. This blog rocks !!!! :)
Regards
Taher
Is it possible to create clustered index on view.
and how ?
When i run this query, its appear the following error message, can u tell me any person, why appears this error message?
GO
Create Table TestCompression (Col1 INT, Col2 CHAR(50))
GO
Insert Into TestCompression Values (10, ‘Compression testing’)
GO 5000
– Original
EXEC sp_spaceused TestCompression
GO
– DATA_COMPRESSION = ROW
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
– DATA_COMPRESSION = PAGE
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression
GO
– DATA_COMPRESSION = NONE
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression
Go
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘WITH’.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘WITH’.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘WITH’.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Thanks,
- Samy
Which version of SQL Server are you using?
This wont work in versions prior to 2008
hi madhivanan,
Thanks for your reply.
Hello Pinal,
I have been reading your articles for sometime now but I have not come across an article that will solve a pending issue.
I need a way (using SQL) to compare an inventory request for say a quantity of 2 to the current balance of quantity 5 and then update (temp table) to set balance equal to 3 before I compare the next quantity request of 1.
CREATE TABLE invTest( reqID int, prodID varchar(20), reqQty int, invQty int)
INSERT invTest(1234,’015551212′,2,5)
INSERT invTest(1235,’015551212′,2,5)
INSERT invTest(1236,’015551212′,1,5)
INSERT invTest(1237,’015551212′,4,5)
The goal would be to use LIFO (last in first out), so the inventory would be exhausted after filling reqID(1237,1236)
I hope this makes sense, but I cannot seem to find a resolution.
Anything that you can do would be greatly appreciated.
Thank you,
Randy Sigmond
just want to say that you are great. every thing you understood us by photo. which is very helpful for us. Thanks.
Hi Peenal,
This Article really helpful.
emp_no emp_name supervisor_no
————————————————-
1 vinay 3
2 kamesh 1
3 muhu NULL
NULL NULL NULL
This is my table datas.
I want the result to be in this format;
for vinay the suprevisor is muhu which is from the same table…
emp_name supervisor_name
——————————————
vinay muhu
kamesh vinay
muhu NULL
Select a.EMP_Name,b.Supervisor_Name From
(Select Emp_no as SUP_No,Emp_Name as Supervisor_Name From EmpTable Where Emp_No in (Select Distinct SUP_NO From EmpTable)) as b,
EmpTable as a
where a.SUP_NO=b.SUP_NO and a.Emp_Name not in (‘null’)
EMP_Name Supervisor_Name
————————- ————————-
Vinay Muthu
Kamesh Vinay
Muthu NULL
(3 row(s) affected)
Wouldn’t simple LEFT OUTER JOIN do?
SELECT e.emp_name emp_name, s.emp_name supervisor_name
FROM employee e
LEFT OUTER JOIN employee s ON s.emp_no=e.supervisor_no
hi Marko Parkkola
Thanks
one more method :
select e1.emp_name as emp_name,(select e2.emp_name from employee e2
where e2.empid = e1.mgrid) as mgr_name
from employee e1
If the subquery returns more than a value, it will throw error
Dear Pinal,
Problem1) Sql Server 2005 64 bit enterprise edition is consuming 7 Gb memory out of 8 GB.Database size is also 1GB and transaction occurs in kbs.I saw performance monitoras well as hit ratio is greater than 98 %.What would be the problem.Cpu utilization is 1%.
Problem2)I scheduled transaction backup every half hour.After executing transaction log backup i execute DBCC SQLPERF(Logspace).But it shows log percenatge always same as before backup.What would be the cause.
Problem 3) After transaction log backup we see log percentage growth to zero but when we check log size then it is not changed.So i want to know actually what transaction log backup works.
Problem 4)How to trace Database size weekly
Problem 5)I am unable to create jobs in SQl Server using maintenance jobs.
hi sir regarding the bulk copy from csv to sql…is there any way which can validate if there’s an existing data in sql from csv???need you help..thanks
Import data to staging table
Compare it with origianal table
There is no direct way of doing this with bulk insert
Dear Pinal,
What is the best way of deigning Audit trails in DB? Further to add it, how can we optimize the queries on top of this, to get the reports, query on single column for updates, etc.
Thanks.
Hi Pinal,
I want a real time example for ACID Properties, Can you please help me.
Thanks
how
we restore backup database from sqlserver2000 to sql2005
restore database db_name from disk='file_path'
Hi Pinal,
How Can I add a non DBO user to read sysobjects in Sql Server . Is this possible or does the user have to be DBO to access sysobjects table. Appreciate your help on this .
Hello Nima,
Check if sys.all_objects is accessible. The permission is not required for sysobjects catalog view, but on objects that can be viewed in its result. The user must have some permission on object to get viewed in sysobjects output.
Regards,
Pinal Dave
Hi Pinal,
Is there any alternate for wildcard character in full text search.. my script is taking more time to use this full text search..
Thanks in advance !
In Full text search with CONTAINS or CONTAINSTABLE fucntions there is no need of wildcard (*) until unless you are not looking for specified prefix of suffix.
Please let us know for what type of search, what is your query.
Regards,
Pinal Dave
new to this so bear with me. how can i verify that the data in my csv file (created from an excelspreadsheet) is clean and contains no corrupt data.
It depends on What your definition of clean is
You need to give us more informations to help you
Hi Pinal
I am a silent follower of your website. I really like your post and they are much helpfull too. I work as a BI Consultant in US. Here I have a Task on which I need your help. Its really very Urgent!!
I am supposed to retrieve files from a SFTP Server (*.dat files) to our local server using SSIS 2005 package. When I tried googling regarding this, many of them suggested using couple of Third party tools, as I cannot perform this Task using FTP. I am not allowed to use those 3rd Party Tools here.
Can you please help me with this. The best possible script for my package to recieve files from a sftp server to my local server with out using Third party tools.
Any one know what is the max number of columns allowed in SQL Server 2005 replication. I have two tables of more then 350 columns.
You reply is much appreciated in advance.
Thanks,
Kamlesh C. Patel
Read about Maximum Capacity Specifications in SQL Server help file
advantages of ssis over Sql procedure when migrating data from central server to local system??
Hi Goud,
SSIS provides in-memory intermediate storage, better error handling with error-row redirection and event procedures, comprehansive logging, dynamic deployment with configuration files and easy user interface.
But if your data migration is a simple select-insert then use t-sql queries.
Regards,
Pinal Dave
Hello Dave sir,
i have installed visual studio 2008 alog with SQL that was already in studio 2008. but when i tryed to create DataBase it showing me following error…
****
An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact under the default setting SQL Server does not allow remote connection.(Provider: Named pipes Provider, error:40-Could not open a connection to SQL Server )
****
and also tell me how to find server name….is it “SQLEXPRESS” or else….while creating DataBase he is asking me for server name, so what should i write over there?????…pls tell me it’s veerrrrryyyyyy urgent?
thank uuuu….
byeee tc…
Hello Dave sir,
pls tell me which services of SQL should i keep active…while working with studio 2008…..
Hello Amit,
studio 2008 could be : SSMS, BIDS, or visual studio. The service that you are using should be running. Even you can work in disconnected mode in these studio.
Regards,
Pinal Dave
Thanx sir …
could you please help me out in learning SSIS topic
mostly like variables….
Amit kalke
Hello Dave sir,
i have installed visual studio 2008 along with SQL that was already in studio 2008. but when i tryed to create DataBase it showing me following error…
****
An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact under the default setting SQL Server does not allow remote connection.(Provider: Named pipes Provider, error:40-Could not open a connection to SQL Server )
****
and also tell me how to find server name….is it “SQLEXPRESS” or else….while creating DataBase he is asking me for server name, so what should i write over there?????…pls tell me ?
byeee tc…
@Amit
When you install SQL Server 2005 on a machine you usually have two options.
1. Install SQL Server with Default Instance Name ( Default instance name is nothing but name of the computer).
2. Install SQL Server with Named Instance (You need to provide name to instance and complete SQL Server instance name will be Computer Name\Instance Name)
When Installing Any Edition of SQL Server 2005 (Like Enterprise, Developer, Standard, Workgroup but not Express) with Default Instance Name, you will see that SQL Server Service name will be MSSQLSERVER. When you want to connect to this SQL Server Instance, you need to type in name of the Server(Windows Machine).
When Installing Express Edition of SQL Server 2005, SQL Server Service name will be shown as SQLEXPRESS (like in your case), if you want to connect to this SQL Server, you need to type in the name of the computer as Server Name.
If you are trying to connect to this server from other machine and you get this error: SQL Server does not allow remote connection, then follow 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/
Let us know if you this do not solves your issue.
~ IM.
Thankxxx Mr Imran For reply and Problem is resolved …..
dear sir
i have company table and create a stored procedure for it
if new company inserted the procedure out a parameter ‘Company saved’ and if comany exists it out a param ‘Comapny already exists’ but the problem is that when we run this it can’nt out this parameter
the SP is here:
ALTER PROCEDURE [dbo].[InsCompanyMaster]
(
@CompanyName varchar(500),
@result varchar(50) output
)
AS
if not exists(select * from CompanyMaster where CompanyName=@CompanyName)
begin
Insert CompanyMaster (CompanyName) values (@CompanyName)
if(@@error0)
begin
select @result=’Error in query’
end
else
begin
select @result=’Company saved’
end
end
else
begin
select @result=’Company already exists’
end
Can you use this procedure….
CREATE Procedure dbo.InsCompanyMaster
(
@CompanyName varchar(50)
)
AS
Declare @Result varchar(50)
IF NOT EXISTS (Select * From CompanyMaster Where CompanyName=@CompanyName)
BEGIN
Insert CompanyMaster Values (@CompanyName)
SET @Result=’Company Saved’
SELECT @Result Output
END
ELSE
BEGIN
SET @Result=’Company Exists’
SELECT @Result Output
END
Hi,
I need a sql query to get the following
Findout that are all the Stored procedures called against a database, by whom. When an application call of to execute the SQL action requested.
Procudure Name
Machine name (Executed from)
login name
Executed time
Eg.
Hi Sathya,
Such details can be recorded using SQL Profiler or profiler trace.
Regards,
Pinal Dave
Hello Pinal,
I am facing a problem regarding Multilanguage in SQL Server 2008. When i store a multilanguage string in database using the nvarchar datatype, its stored as “아라파트”. When i try to search this particular column value from my application (using nvarchar N’searchval’), its not searching. I want to know why in database its stored like that way. Is it a functionality of SQL Server 2008. As far as i know in 2005 it was storing multilanguage in some other format, some symbols kind of thing.
Regards
Taher
In above comment.. this “아라파트” is stored as 50500;46972;54028;53944; (removing the &# from each word)
Hi
Data Synchronization In SQL Server database table using sql script or TSQL script or code.
I want to Synchronize my In SQL Server database table at server end using client database table where data are entered, both database have same table name.
if this can be accomplish by using TSQL ,Stored Procedure then it is good otherwise the script in ASP.NET(C#.NET) is better.
You can make use of replication
or add another server as linked server and do query
insert into linked_server,db.owner.table(columns)
select columns from table as t
where not exists
(select * from linked_server,db.owner.table where keycol=t.keycol)
Hi Pinal and Everyone
Above is the URL what I use to send SMS by providing the actual mobile number and message. Till now I am using the HTTP URL PUSH method from a JSP file which does not open the browser, just process the above URL on the server and the mobile number mentioned receives the SMS.
Now I want to remove JSPs from the picture and do the same task whenever a new record is inserted into a table. Suppose the record has the mobile number and the message to be sent, how can I generate the above http request in the trigger on insert on that table?
Any help will be appreciated, I am struck into it for the last 3 days and tired of googling.
Thanks
Deepak
Hi
In my previous post i wrote the URL. may be removed by the moderator. I am again giving the URL (not actual, just for an idea).
http://www.somewebsite.com/sendSMS.asp?mobilenumber=9999999999&message=TestingTestingTestingTestingTesting
Thanks & Regards
In the AFTER INSERT trigger,write
insert into target_table(columns)
select ‘http://www.somewebsite.com/sendSMS.asp?mobilenumber=’+cast(mobileno as varchar(20))+’&message=’+message from inserted
Hi,
I want to get the first and last row from .txt file using SSIS wizard.
Could you please help me out on this?
One option is to bulkinsert data to a table that has identity column and select data based on min and max value of identity column
Pinal Dave,
You seem to have an extremely strong far reaching knowledge of sql server. I was wondering if you have ever run into the the issue I am trying to solve.
I have a page that dynamically builds forms input fields from a table allowing users to enter input into the form. Each input is a field in a _info table.
I would like report showing all of the entries of a user in one line. Each field entry is a row. So I need to take the vertical data and make it horizonal. There are many different forms with different configurations so must be dynamic. I’ve come across a lot of articles for cross-tab pivots but they only work for aggragates/sums.
Below is an example table layout:
FORM
——————————————
FORM_ID FORM_NAME
500 “Custom Form 1″
FORM_FIELD
——————————————
FORM_FIELD_ID, FORM_ID, LABEL
1 500 “Field 1″
2 500 “Field 2″
REGISTRATION
——————————————
REG_ID FORM_ID USER_ID
23 500 45
24 500 98
REGISTRATION_INFO
——————————————
REG_ID FORM_FIELD_ID FIELD_VALUE
23 1 “My Name”
24 2 “My City”
Required Report Output:
REG_ID |Field 1 |Field 2 |FORM_ID |USER_ID
———————————————————————————————
23 |My Name |My City |500 |45
24 |Another Name |Another City |500 |98
Hi Troy,
You can do it by creating two derived tables for each Fordm_field as below:
SELECT * FROM REGISTRATION,
(SELECT * FROM REGISTRATION_INFO WHERE FORM_FIELD_ID = 1) AS MyName,
(SELECT * FROM REGISTRATION_INFO WHERE FORM_FIELD_ID = 2) AS MyCity
WHERE REGISTRATION.REG_ID = MyName.REG_ID
AND REGISTRATION.REG_ID = MyCity.REG_ID
This is not a tested query so you need to edit and verify it it as per your requirement.
Regards,
Pinal Dave
Hi Pinal,
I am using SQLCMD with -itest.sql and -otest.log and noticed that log file is not showing information accurately.
Example, in test.sql I have
select ‘Update customer_name at: ‘, getdate()
go
update 1
go
update 2
go
update 3
go
test.log will have information in just two of them. It just seems that it skips writing to log file ramdomly.
Can you please advice what should I do to fix this?
Best regards,
Nina
hi,
i wanted to know the difference between view and cursor ? I am reading a large amount of data and was wondering if we can subset what would be advantageous. A cursor with selected rows or a view on that table.
thx.
Hi Pal,
View is a Virtual table and Cusror works as a datareader. Anyhow there is no similarity in View and Cursor.
But in respect to get large amount of data in subset View is a better approach.
because in cursor we have to again read the whole subset data and it’s a time consuming process.
Get last Child record from root parent Category id
i have table “Category”….the fields of table are as per Below
1) CategoryId
2) CategoryName
3) ParentId…(its foreign key to the CategoryId of this table)
4) CategoryLevel
now i want to get last child category id from the root category id….
Records are as per below….
CategorId CategoryName ParentId CategoryLevel
1 Root Cat 1 0 1
2 Root Cat 2 0 1
3 Child Cat 1 1 2
4 Child Cat 2 1 2
5 Child Cat 3 2 2
6 Child Cat 4 2 2
7 Child Cat 5 3 3
8 Child Cat 6 3 3
9 Child Cat 7 3 3
10 Child Cat 8 4 3
11 Child Cat 9 5 3
now if i pass CategoryId=’1′ then i expect results as per below
CategorId CategoryName ParentId CategoryLevel
3 Child Cat 1 1 2
4 Child Cat 2 1 2
7 Child Cat 5 3 3
8 Child Cat 6 3 3
9 Child Cat 7 3 3
10 Child Cat 8 4 3
Hi Pinal,
Kindly suggest how to drop Excel sheet or delete records of Excel sheet using SQL Server.
Hi pinal, i would like to findout tht is it possible for me to perfom a full text indexed search on an sql server running CTP version. i have configured everything ok but when i run the sql statement no rows are returned. can you please hel me on this.
SQL Management studio ( SSMS.exe ) is throwing memory exceptions while selecting from a record size of 2 Crores. i have noticed that the process running in taskmanager is SSMS.exe*32 and its close to 1.5 GB Memory. The server has a 64 bit processor as well as SQL 2008 64 bit with 8 GB RAM. I have set 4 GB max for SQL server process. Is the issue due to 32 bit studio that is used for this 64 bit database. I tried installing SQL Mngmt studio 2008 Express 64 bit but i am unable to select features.
How to implement SCD type 2 without usind SCD component which was present in SSIS?
Hi pinal…
How to implement SCD type 2 without usind SCD component which was present in SSIS?
Hi Pinal,
First of all I wan tto thanks you for all of your blogs, helped me a lot to learn and grow.
then I am facing a problem..
I am connected to a DB exists on a SQL SERVER 2005
I am deleting data from a table which consists approx.. 68 lac records. I need to empty it and Insert data again it this table.
But when I execute “Delete from ” Or “Truncate Table ” It takes s long time to delete the data, today when I run the delete command it took 4 Hrs to delete the data. There is no index on table, please help me and suggest what may be the cause of it, and how I can resolve it.
I am really very frustrated with this situation. please Help me..
Thanks in advance..
Gyan
Hello Gyan,
If there is no foreign key constraint on this table than use truncate instead of delete to delete all records.
Verify if there is any trigger for delete statement.
Is this replicated or published by any way?
Regards,
Pinal Dave
Hi,
Please tel me how to use a view inside the stored procedure in ms sql server?
Thanks,
Anu
Create procedure proc_test
as
select columns from your_view
Hi,
I need your kind assist , my scenario is I’ve 5 branches including one main branch all running sql server 2003 and on daily basis pull the data from other server to the main head office . What is the best and secure way of pulling data from there currently there is no vpn is setup yet . Hope to get some idea from you or any other member .
Regards
Tehseen Sagar
Well, you do need data encryption between your offices anyway so I wouldn’t do anything until I have VPN in place.
At my workplace we have exactly the same situation. Five factories which all have their own Sql Servers (lot’s of them) and head office which pulls everything from factory servers to our big servers. I’m not expert on this area but I’ve understood we use DTS to the actual transfer, timed jobs to execute the transfer once a night and VPN to keep the data safe during the transfer. We also have some heavy network infrastructure (2×1 gb fiber in every location IIRC) to make sure that everything goes fast.
In SQL Server Mode one can access the database by supplying valid login name and password. We can restrict the database visibility using SQL Server Authentication. Im doing this in customer place at the time of installation in SYSTEM-A [Computer Name], so that he can not see the database tables or stored procedure scripts.
Now the scenario is like this:
Assume a techie at customer place copied database files from SYSTEM-A, and try to restore the database in SYSTEM-B using Windows authentication mode. There he can see the table structures and sp scripts. How to avoid this, is the question.
OR. How to lock database tables and sp’s in this scenario?
Hello Lokesh,
SQL server 2008 introduced new feature called Transparent Data Encryption (TDE) to encrypt data in database files and backups.
In previous versions you will have to keep the backup files secure enough to come in suspecious hands.
Regards,
Pinal Dave
Hello Dave,
Is this good practice to use NOLOCK with in the BEGIN TRANSACTION AND COMMIT TRANSACTION in a stored procedure..
BEGIN TRANSACTION
SELECT SomeCol FROM MyTable WITH (NOLOCK)
–Some operatrions on tables…
COMMIT TRANSACTION
Thanks in Advance,
Shalem
Hello Shalem,
The functionality of NOLOCK does not affected by BEGIN or COMMIT TRAN statemets. So if you want to reduce locking and blocking at the cost of dirty read then use NOLOCK with each SELECT statement.
Regards,
Pinal Dave
Hi Pinal
Do you provide professional Service.. We need to expert opinion on How we can increase our database efficiency .. If yes please let me know.. I will be providing further details.
Regards
Suraj jain
Hello Dev sir
I have two table with there fields are below . .
“Employee”
EMPNO ENAME JOB MGR HIREDATE SAL COMM Deptno
” Department ”
DEPTNO DNAME LOC
My Query is ===>>>
” Find out the employee name and department name of employee in each department “
Hi Amit,
I see no complexity in getting employee and department name just by joining these two tables on DeptNo columns.
To knowabout joins please read my article:
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
Regards,
Pinal Dave
Hello all,
help please
I recently enabled my production server for mirroring, which works fine but my transaction log just keeps growing and is up to 98G and my datbase is only 3G..i find when i remove mirroring i can shrink the log file…is there any way i can shrink my log file without removing mirroring.
thanx sir…it works…
please help
I came across a peculiar problem with two tables. Can you
please let me know the solution. Please fnd below the scenario
A productcentre can have any type of applications
a ProductCentre table
prdcntrId (primary key), prdcntrname
a ApplicationType table
apptypeid (primary key)
prdcntreid(foreign key to ProductCentre )
apptypname
ProductCentre table
prdcntrId prdcntrname
001 Delhi
002 Mumbai
003 Hyd
ApplicationType table
apptypeid prdcntreid apptypname
11 001 Busines
12 003 Engg
13 001 Soft
14 002 Science
The end result should be like this
——————————————————————
prdcntrname Busines Engg Soft Science
Delhi 1 0 1 0
Mumbai 0 1 0 1
Hyd 0 1 0 0
Result table shows delhi has 1 busines, 1 softwre application
mumbai has 1 engg aplication etc.
pleae let me knw if we can achevie this result
Dear all
i am fresher i stuck in following problem pls help me…
i have “Employee” table with fields.
EMPNO ENAME JOB MGR HIREDATE SAL COMM Deptno
the datatype of HireDate is date time ….my query is that i want to display all the employee whose joining is in 1999 …so how i can do this ????
Hi Amit,
You can simple write a query with where clause.
You can identify year as:
SELECT YEAR(HireDate)
FROM Employee
To find employee hired in 1999, you can write it as:
SELECT *
FROM Employee
WHERE YEAR(HireDate) = 1999
Thanks,
Tejas
Heeeyyyy Thanx Rajesh…..it works…
If the column has index and if you want to make use of it, use
select columns from Employee
where
HireDate>=DATEADD(year,1999-1900,0) and
HireDate<DATEADD(year,1999-1899,0)
Greetings Pinal,
I had created this little tool a while back and would like you to see how it is. Its in the very early stage.
Your input will help me in adding some bells and whistles to it.
https://sites.google.com/site/sqlscriptter/
Regards.
TD
HI
We are working with our client to migrate their Enterprise data from SQL 2005 32 bit to SQL 2008 64 bit. Keeping this in mind, we wanted to talk to people who have done SQL Server migrations so that we can make sure that we cover all the different aspects related to the migration. It could be related to different architectural approaches to ensure high availability OR issues that we might encounter during migration etc..
can you share some docs if you have (steps to follow)
Regards
Suresh
Hello sir..
please go through link..
i have question.
please give me some hint.. by Sql Server..
as of now
i have solved it by creating temporary table in SP. and have played with query to generate such output…
but i m not satisfied with this solution..
please give me some hint… or proper solution..
Thnks
Dear Pinal,
I have a simple query.
I have different tables with Auto incremented INT as primary key.
PK auto incremented INTs are FKs in some other tables.
I use joins in sql statements to retrieve rows from multiple tables.
All I want to know is that
- If I use SQL server and define a MAPPING (at database level using the diagram tool), does the query execute faster?
Does a linking tables affect the speed at which joins are calculated when a query is executed?
I get the same result, with or without the tables being linked at database level. Just wondering about efficiency.
Dear dave,
I m working as sql programmer at Lucidinfotech Bhubaneswar, India. I want to prepare for SQL DBA. Will u please help me knowing the process of Microsoft Certification. And also which book I shall go through for the same.
Thanks & regards
Sirajur Rahmaan
Dear Pinal,
I have a Situation that Create a interface to Upload table from excel to SQL Database.
For that my approach is like create a table, copy the data from excel to new table Using bulk copy. Process the table and Update the Processed data to Required table. After updating delete the created table.
This works fine if the one user is Uploading. Wat if multiple user try to upload. Whether SQL will Automatically manage the table Creation and update process ?
regard
Francis
Dear Pinal,
I have a problem on connecting to the SQL server 2005 via the port 1433. Firewall are turned off and the server is running on Windows 2003 environment. Every time I am trying to connect it says cannot connect to the SQL Server.
What are the possible reasons for that???
Thanks & regards
Piumi
Hello Piumi,
Please check my following article:
http://blog.sqlauthority.com/2008/08/24/sql-server-fix-error-40-could-not-open-a-connection-to-sql-server-fix-connection-problems-of-sql-server/
If this doesn’t help then send the error details.
Regards,
Pinal Dave
Thanks Pinal..
I got the reason. Thanks Again
Regards,
Piumi
I am not sure, whether I can ask it here or not,
I am newbie, I would like to select the list of users in the table, DrillTypes, and count. I would like these count fields as dynamic columns. ex
Name applDrillCnt BreathDrilCnt KnotsDrlcnt SmalGeaCnt
1. Giri 12 15 5 20
how can i do that with best performance
Hello Giri,
Please check my following article:
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
Regards,
Pinal Dave
Hi Thanks for previous one, I have one more query.
how can i display dynamic columns, in my customer table, i have different products, and the purchased dates. i want to display the customername, productname, count(product), 1stPurchase, 2ndPurchase, 3rdpurchase, ….so on
groupby the purchasedate but dont want to display the dates, instead i want like 1st, 2nd, 3rd,… these should be dynamic columns.
pls help me how can i do that
Hello Gerry,
You can specify the column name as in following example:
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY pvt.VendorID;
Regards,
Pinal Dave
Hello sir,
My question is my two application is running one is Window Application and second one is Web Application.
In both Application Some Functionality is same for example i am add customer detail in window application also same same detail in Web application.i want whatever data i can insert or update its effect on both database or During some period i can run Query on sql server 2005 then its data transfer to Web Application Data.
Is it Possible ?Because Lots of problem is Created in Data when i m Copy Data to Window Application And Paste In Web Application Data. or Enter in Both Database.
Please Give Me Solution ,
Hello Pinal,
Do you conduct any training on SQL Server in Ahmedabad. I am looking for a crash course in SQL Server 2008 for 1-2 weeks.
Thanks & Regards
Shalin
Hi Pinal,
I found below indexes defined one db table.
CK_GroupByColumns nonclustered located on PRIMARY ItemID, UnitOfIssue, RegHospDivID, CabinetID
PK_TransactionDetail clustered, unique, primary key located on PRIMARY TransactionDetailID
YMC_Ex nonclustered located on PRIMARY YearMonthCode, ExclusionType
YMC_TD nonclustered located on PRIMARY YearMonthCode, RegHospDivID, CabinetID, UnitOfIssue
YMC_UIUS nonclustered located on PRIMARY YearMonthCode, ExclusionType, ItemID
YMN_EXT_Item nonclustered located on PRIMARY YearMonthCode, ExclusionType, ItemID
I am querying the table in one query with RegHospDivId, YearMonthCode in where condition and in other query with YearMonthCode, RegHospDivID in where condition and join on CabinetID.
1. I am not sure, how will above repeat of columns in multiple non-clustered index help?
2. Which non-clustered index will be used in which case then?
Appreciate your time.
Thanks
please help me out i am using this query, but the result set 0 on every count. when i execute the subquery i can get the results. is there any other way to do this?
DECLARE @RecruitId Int
SELECT Distinct(RecruitId), (
SELECT COUNT(DrillType)
FROM dbo.Drill
WHERE DrillType LIKE ‘Appliance%’
AND RecruitId = @RecruitId)AS Appliance, (
SELECT COUNT(DrillType)
FROM dbo.Drill
WHERE DrillType LIKE ‘Breathing%’
AND RecruitId = @RecruitId)AS Breathing, (
SELECT COUNT(DrillType)
FROM dbo.Drill
WHERE DrillType LIKE ‘Driving%’
AND RecruitId = @RecruitId)AS Driving, (
SELECT COUNT(DrillType)
FROM dbo.Drill
WHERE DrillType LIKE ‘RoadCrash%’
AND RecruitId = @RecruitId)AS RoadCrash, (
SELECT COUNT(DrillType)
FROM dbo.Drill
WHERE DrillType LIKE ‘Knots%’
AND RecruitId = @RecruitId)AS Knots
From dbo.Drill
Group by RecruitId
Use PIVOT
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Thanks Madhivanan,
I followed the instructions, but what happend it gave me the error.
Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ‘PIVOT’
just find out, recently installed sql2008and sp1, but when select the version using select @@version the result is sql2000?? i am stuck
is the above error just bcoz of this version? but PIVOT keyword is greyed out as of other key words in sql.. help me out plss
Run
SELECT @@VERSION
and see what version you are using
Thanks Madhivanan,
as I told you it says version: SQL 2000
how should i fix this? i already installed SQL2008 and SP1, and opened management studio from 2008 and wrote this query.
what should i do to make it work SQL 2008?
Thanks once again
It means you have both the versions
Follow this
Start–>Programs–>Microsoft SQL Server 2008–>SQL Server Management Studio
Connect to your server and try the code
Hi Madhivanan,
I am really sorry, i was confused! in my machine i have installed SQL2005, and 2008 but the server is running on 2000, so thats why PIVOT is not working….
is there any otherway instead of using PIVOT can i get this done in SQL 2000??
Thank you very much
Ok. For version 2000, Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
or
Read about Cross-tab reports in SQL Server help file
Thanks alot!!
Hi,
according one of your blogs, I restored a database through queries but after execution, it says successfully completed which the restoring a database in the process when I physically view in the object explorer of the Microsoft SQL server 2008. Neither I cant open that database. Please suggest me this issue.
Soon after restoring the database, run
EXEC sp_helpdb ‘your_restored_db’
and see if you get results
Hi Sir,
While executing a stored procedure ,It has a statement like
DBCC
SHRINKDATABASE (tempdb) WITH NO_INFOMSGS
I Get error while executing the stored procedure .Error like
User ‘guest’ does not have permission to run DBCC shrinkdatabase for database ‘tempdb’.
Could you please suggest me the possible reason for this error and what could be the possible solution.
Thanks,
Sradhanjali
Hello Sradhanjali,
This is permission denied issue. You can execute this stored procedure by the login that is either db_owner of database or a member of sysadmin server role.
Regards,
Pinal Dave
Hii
plzzz i need ur help
i have problem with instal MS SQL 2005 on windows 7,
when i try to login give me error 18456
login by sa is dosnt work
plz help
thanks for ur time
please help me with solution
Please fnd below the scenario
a ProductCentre table
prdcntrId (primary key), prdcntrname
a ApplicationType table
apptypeid (primary key)
prdcntreid(foreign key to ProductCentre )
apptypname
ProductCentre table
prdcntrId prdcntrname
001 Delhi
002 Mumbai
003 Hyd
ApplicationType table
apptypeid prdcntreid apptypname
11 001 Busines
12 003 Engg
13 001 Soft
14 002 Science
The end result should be like this
——————————————————————
prdcntrname Busines Engg Soft Science
Delhi 1 0 1 0
Mumbai 0 1 0 1
Hyd 0 1 0 0
A productcentre can have any type of applications
Application types can be business, enrng, science.
productcentres are delhi, mumbai,hyderabad.
Result table shows delhi has 1 busines, 1 softwre application. mumbai has 1 engg aplication etc.
pleae let me knw if we can achevie this result
by using queries.
regards,
karthik
@Karthik
Something like this?
WITH
ProductCentre(prdcntrId, prdcntrname)
AS
(
SELECT 1, ‘Delhi’ UNION ALL
SELECT 2, ‘Mumbai’ UNION ALL
SELECT 3, ‘Hyd’
),
ApplicationType(apptypeid, prdcntreid, apptypname)
AS
(
SELECT 11, 1, ‘Busines’ UNION ALL
SELECT 12, 3, ‘Engg’ UNION ALL
SELECT 13, 1, ‘Soft’ UNION ALL
SELECT 14, 2, ‘Science’
)
SELECT
ProductCentre.prdcntrname,
MAX(CASE ApplicationType.apptypname WHEN ‘Busines’ THEN 1 ELSE 0 END) Busines,
MAX(CASE ApplicationType.apptypname WHEN ‘Engg’ THEN 1 ELSE 0 END) Engg,
MAX(CASE ApplicationType.apptypname WHEN ‘Soft’ THEN 1 ELSE 0 END) Soft,
MAX(CASE ApplicationType.apptypname WHEN ‘Science’ THEN 1 ELSE 0 END) Science
FROM
ProductCentre,
ApplicationType
WHERE
ApplicationType.prdcntreid = ProductCentre.prdcntrId
GROUP BY
ProductCentre.prdcntrname;
hi Brian
Thanks for the reply. But the given reply didnt turn up the correct result. My Application can be in any product centre. To be more precise, a productcentre can have business/engrng/science applications.
i have two parent tables productcentre , application types. a child table which has foreign keys of these two parent tables. This child table has records.
PrdctCentre
prdctrid(pk) prdcntrename
001 delhi
002 hyd
003 del
AppType
AppId(pk) prdctrid(fk) AppName
1 001 busi
2 001 eng
3 002 soft
Maintable
Mainid prdctrid(fk) AppId(fk) etc ….columns
This maintable has records in this pattern
Maintable
Mainid prdctrid(fk) AppId(fk)
0001 001 1
0002 002 2
0003 001 1
0004 001 2
0005 002 1
So we have
in 001 productcentre 3 applications( 2 bus 1 eng)
in 002 prdtcentre 2 applications ( 1 eng 1 bus)
so i want a query which shows gives result like the one in below
prdtcntrename bus eng sci
del 2 1 0
hyd 1 1 0
this is the desired result.
please help me
@karthik
Hope this will help you this is same query as Brian provide you with same concept just a little midification.
Select b.prdcntrname,t.Busines,t.Engg,t.Science from
(
select prdctrid,SUM(CASE WHEN APPID=1 then 1 else 0 end) as Busines,SUM(CASE WHEN APPID=2 then 1 else 0 end) as Engg
,SUM(CASE WHEN APPID=3 then 1 else 0 end) as Science from maintable group by prdctrid
) T join ProductCentre B on T.prdctrid=b.prdcntrId
@Ashish
Thanks for reply.
The query provided by you doesnot work. Business/eng/science/soft is not a column in applicationtype table.
can anyone please provide the solution for my query
Hello,
How to Get Data From Password Protected Excel File ?
Hi Dave,
I want to know while working on websites, which is the best way of handling locking in sql for dml statements when we have multiple users working on the tables.
Regards
Hello Adarsh,
For DML statements, locking can not be avoided. But faster and smaller transactions can help in blocking other transactions.
Also ROWLOCK lock hint is recommonded on tables in DML statements to reduce blocking.
Regards,
Pinal Dave
Hello all,
I have one query i.e. I want to find 2nd Highest Salary person with all his details. I have answer but it gives salary only
My answer is:
SELECT MIN(Sal) AS Highest_Sal
FROM (SELECT TOP (2) Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No
FROM Employee
ORDER BY Sal DESC) AS derivedtbl_1
pls help me to solve the query
Hello Amit,
There are many ways to get such result. Among those one is by using ROW_NUMBER() function as below:
WITH CTE AS (SELECT TOP 2 Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No, ROW_NUMBER() OVER (ORDER BY Sal) AS RecordPos
FROM Employee)
SELECT Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No FROM CTE WHERE RecordPos = 2
Regards,
Pinal Dave
@Pinal
You forgot to remove “TOP 2″. And the ORDER BY needs to be DESC.
Oops. :)
@Amit Kumar
WITH CTE AS (SELECT ROW_NUMBER() OVER(ORDER BY ORDER BY Sal DESC) RN, Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No FROM Employee)
SELECT Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No FROM CTE WHERE RN = 2;
Hello Pinal,
I have a query on CTE. I want to use conditional statement in CTE like if statement & depending upon rowcount i need to insert a row with some data init & i need to use recursion for it so using CTE. Can I use if statement in CTE if yes can u give me small example of it? Thanks.
Hello Krishnapratap,
Are you facing any issue in using CASE statement in CTE query?
I think CASE can be used in CTE same as in other t-sql queries.
Regards,
Pinal Dave
Hello Pinal,
I will paste my CTE to you with result set & tell you what i want:
Here is my CTE:
WITH sh (Id, Name, LR, ParentId, Level)
AS
(
SELECT Id, Name, [LeftRight], [Parent Id], 0 AS Level
FROM SelfHelpMummyCodes09thFeb2010
WHERE Id = ‘SH2313802873′
UNION ALL
SELECT s.Id, s.Name, s.LeftRight, s.[Parent Id], Level + 1
FROM SelfHelpMummyCodes09thFeb2010 s
INNER JOIN sh
ON sh.Id = s.[Parent Id]
)
SELECT * FROM sh ORDER BY Level
Result Set:
SH2313802873 Sarika Amol Apte Left SH7734102870 0
SH8599602874 Sarika Amol Apte Left SH2313802873 1
SH3172902875 Sarika Amol Apte Right SH2313802873 1
SH5310803026 Jayesh Suresh Vaidya Left SH3172902875 2
SH3633903027 Sarika Amol Apte Right SH3172902875 2
SH4547303526 Sarika Amol Apte Left SH8599602874 2
SH8343003055 Priya Kunal Ghosh Right SH8599602874 2
SH9978903056 Shilpa Abhay Vaidya Left SH8343003055 3
SH9846903672 Anuya Ramesh More Left SH4547303526 3
SH9478703528 Poonam Shashikant Date Right SH4547303526 3
I want to find ParentId Count lesser than 2 & if it so then i want to insert or show my own row with specfic data. I am using CTE because it will save my time & do recursion for me for all child records. Thanks.
Hello Pinal,
Got it. Now, am testing. Do you take training in Mumbai also? Thanks.
sir i have a database table like given below
here BatchID(Primary, AutoIncrement) bind in document index table which have DocumentIndexID(Primary, AutoIncrement) which again bind in AssignedDescriptionID(Primary, AutoIncrement)
if i merge it then it prevent
but the task is it. how can i do it.
Batch Table
————–
BatchID BatchName
———- ————–
116 300-B-HQ-2008
117 200-B-HQ-2009
DocumentIndex Table
—————————-
DocumentIndexID BatchID DestinationImageName
———————– ———- ——————————
17 116 vijay kumar.tif
18 116 sunil kumar.tif
AssignedDescription Table
—————————————
AssignedDescriptionID DocumentIndexID DescriptionValue
—————————- ———————– ———————
113 17 VIJAY KUMAR
114 17 Sunil kumar
@shiv shanker
What is the result?
actually i want to merge table so that the primary key and autoincrement done there job as usuasl
and data show like
Batch Table
————–
BatchID BatchName
116 300-B-HQ-2008
117 200-B-HQ-2009
from source table that have batchid 1
insert like below
118 300-b-hq-2009
.
.
DocumentIndex Table
—————————-
DocumentIndexID BatchID DestinationImageName
17 116 vijay kumar.tif
18 116 sunil kumar.tif
from source table that have documentindexid 1
insert like below
19 118 shanker.tif
and so on………for next table
Hi,
Is there a simple way to create a UDF that works out the number of woking days between two dates, exclusign weekends and public holidays?
The idea being that if in a table you hve two date fields, use a UDF to create a computed field to return the number of woking days between the two.
Regards
Hello Pinal,
Hope all is well with you. I am one of your millions of followers and would like to ask your most help on how can I make my SQL 2005 DB Prod replication work successfully with my SQL 2005 Backup Server? I am also Planning to Have our BI implemented in our Backup server (separate DB).
Thank you so much in Advance,
Guada
Hi,
I need to backup MSSQL database to txt files, and restore the database from those files – using C#.
Please let me know if you have any idea.
MArcind
Can I convert MSSQL backup file (BCA) to txt and open via text editor to see data?
No. It is not possible
Hi Dave,
I do have a question regarding defragmentation of sql-server-databasese.
Already having a defragmented database, is it a good idea to:
1. backup the defragmented db on another drive
2. delete the backuped db on the fragmented drive and free up as much as additional space as possible
3. defragment the drive with an appropriate windows defrag-tool
4. restore the database on the defragmented drive from the backup with an initial size big enough to avoid further growing
Especially I want to know if the restore of the database also re-creates the internal defragmentation as it was originally in the backuped database?
If yes, there seems to be no gain.
Any help highly appreciated.
Regards,
Torsten
Can we install sql server 2005 in centos (linux). If not what is proper ways of using sql 2005 as we are planning to shift our entire OS to linux.
hi Pinal,
I want delete the admission records from database, but these students register numbers are in excel sheet in external path,
so If possible to use from external excel file in WHERE Clause in SQL server. please suggest me…
Thanks
Samy
Hello Samy,
Use OPENRAWSET or OPENDATASOURCE functions to use data directly from excel file.
Regards,
Pinal Dave
@samy may be like this….
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=D:\test\xltest.xls’, [Sheet1$])
Hope this will help you.
Hi,,
i have query
select * from emp where empid in (3,1,5,2,11)
i want result should be in this order 3,1,5,2,11.
is there any way to display in sql reports 2000.
Quick’n'dirty solution:
select * from emp where empid = 3
union all
select * from emp where empid = 1
union all
select * from emp where empid = 5
union all
select * from emp where empid = 2
union all
select * from emp where empid = 11
@Marko
That is not guaranteed to work. An ORDER BY is still required for that.
@siva
select * from emp where empid in (3,1,5,2,11)
ORDER BY
CASE empid
WHEN 3 THEN 1
WHEN 1 THEN 2
WHEN 5 THEN 3
WHEN 2 THEN 4
WHEN 11 THEN 5
END;
or have seperate table with specific orders and join it
select emp.* from emp
inner join
(
select 3 as empid, 1 as sorder union all
select 1 as empid, 2 as sorder union all
select 5 as empid, 3 as sorder union all
select 2 as empid, 4 as sorder union all
select 11 as empid, 5 as sorder
) as orders
on emp.empid=orders.empid
where emp.empid in (3,1,5,2,11)
ORDER BY orders.sorder
HI i didn’t found any other ugly way to achieve this.. so this will work.
select *,
case when empid=3 then a
when empid=1 then b
when empid=5 then c
when empid=2 then d
when empid=11 then e
END as sortorder
from emp where empid in (3,1,5,2,11)
order by sortorder
Did you mean literal value ‘a’, ‘b’ etc?
Becuase your code wont work as they will be treated as columns
How to take SQL Server database back without having the permissions?
i have the right of read,write,delete.
but i am trying to take the back it is showing the path of the server drive.
i have hared of various tools, but never used can u guide me through.
Pinal:
I want to prevent a row from being updated or deleted after a value has been entered into a specific column.
IF NOT [Frozen] IS NULL
– Don’t update or delete
Will you please point me to a web site that will help me freeze a row based on a column value?
thank you!
i am not sure if i understand you or not but may be you are looking for this.
update table set column=value where frozen is null
Most likely you need to use a trigger that rollbacks the transaction if the column is not null
thanks for the replies
hi,
i need to write a query for selecting the record in a specific order,
The order is to select top first from each category and then top second from each category and so on…
i include a sample data to understand my need easier.
The actual data is
nId GroupId
1 1
2 1
3 2
4 2
5 1
6 5
7 3
8 4
9 7
10 3
11 2
12 5
13 3
14 1
15 8
16 1
17 4
18 2
19 7
i need the output in the following order
nId GroupId
1 1
3 2
7 3
8 4
6 5
9 7
15 8
2 1
4 2
10 3
17 4
12 5
19 7
5 1
11 2
13 3
14 1
18 2
16 1
Suggest me some ideas to bring the desired output…
an english statement to explain my requirement is also welcome to improve my english skill.
thanks
Annamalai
@anamalai may be this will be helpful to you.
select nId,GroupId from(
select *,row_number()over(partition by GroupId order by nId) as rn from #table
)t
order by t.rn,t.GroupId,t.nId
this is what i need,
thanks…
gald to know that this helps.
Your welcome.
Hi All,
I hv got error while taking backup of Databe. Its an TFS Server Database,
Please help me.
——————————————————————————————————————————————————
TITLE: Microsoft SQL Server Management Studio
——————————
Backup failed for Server ‘TFS’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup of full-text catalog ‘TeamFoundationServer10FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
——————————
BUTTONS:
OK
——————————
Hello,
The error stats that there is a full-text catalog in offline status. So first drop or convert the full text catalog to online and then take the backup.
Regards,
Pinal Dave
Sir,
I have an Stored Proc which returns 4 record sets. This SP is getting called from lot of web pages. In a new webpage, i need a modified 3rd recordset returned by the above SP do some more filtering. Can I do the following:
Call the 1st SP in another SP and do some more joins on the 3rd recordset and return to the new webpage.
I cannot modify this logic in 1st SP, as it includes signature changes, which will affect lot of page in my application.
At the same time i don’t want to copy the code from 1st SP to 2nd SP, which would result in maintenance issues in future.
Can you please suggest me a solution for this.
Regards,
Shanmukh.
Here’s quick example how you can do this. You need to use table variable or temp table (I would suggest the former) to get results from SP1.
create procedure SP1 as
select 1, ‘foo’
union
select 2, ‘bar’
go
create procedure SP2 as
begin
declare @t table(id int, sometext varchar(max))
insert into @t
exec SP1
select id, sometext, len(sometext) sometext_len from @t
end
go
exec sp2
Hello Sir,
I have to write a stored Procedure, I have table in which I have a column Amount for the user. So I want that I transfer the amount from one account to another account. so that increase in other account and decerase in other account in single table.
Plz give solution.
Thank you
with Regards
Vikas Semra
Below is my question.
Lets say, I have a list of id’s ’1,2,3,4,5,6′ . There is a table which has these id’s and there is a related names.
So Table ‘CityName’ is like this
int varchar
1 Mumbai
2 Delhi
3 New York
4 Paris
5 London
6 Tokyo
I would want a function which will return me names of what ever list of ID’s I pass?
Somehow I do know SQl well, but seems like I am having a problem doing this.
so lets call the function listOfCities(listofIDs) returns list of cityNames
Thanks,
Tanvir
@Tanvit
Are you sure you want a FUNCTION for this? It is very easy to query, and can be added to an existing query with a simple join.
Please give an example of how you want to use it.
Where do you want to show data?
If you use front end application, return names and do concatenation there
Otherwise
declare @list varchar(100), @names varchar(1000)
set @list='1,2,3,4,5,6'
select @names=coalesce(@names+',','')+ names from table_name
where ','+@list+',' like '%,'+cast(id as varchar(10))+',%'
select @names
Sir,
I am completed my engineering and currently doing SQL server2008 BI. Now i need to know the major difference between Sql2000, SQL2005 and SQL2008. Could send me the developer details and all the developing queries and its syntax. It would help me develope my knowledge and skill on an interview point of view and other places. So please give a reply on to my request.
Thank you
Nagarajan
[phone number removed]
Chennai
@Nagarajan
I don’t think so that anybody there will provide you all developer details or developing details, as their is so much changed from SQL Server 200 to know SQL Server 2008 R2.
So my suggestion to you is go to books online and look their what is changed. Read this blog frequently as you will find many new things here so keep track of this blog as well as read books online and jump to what new section.
Hello Sir,
I have a user Table which contains the 129462 records,
Fields are (UserID,UserPhone,UserCountrycode,UserCountry)
In This Table Which users not Selected the Country then I stored the -1.both for UserCountrycode and UserCountry (217 records)
Now I wants to update the records which contains the -1,
I have a field UserPhone where from i will get first three characters for country code and then select the country from my this table which have the other users of this country (First 3 Charaacters are same) and then update it as desired.
Please guide me,
Thanks,
Amir Abdullah
hey pinal…i would like to register for your course “query optimization and performance tuning”…can you pls tell me how do i can register it…
Hello
I want to create some kind of Job that helps me take care of capacity planning so I use following command to get all the information I need but I am not able to insert output in table, can you please help me.
insert into DatabaseInformation
EXEC @command1=”use [?] exec sp_spaceused”
when I try to use above query I get following error message
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable “@command1″.
please help me
thanks
Feroz
What do you want to do?
Do you want to move the resultsets to a table?
I want a query or function to get the hours of the day.
Like
1:00:00 AM
2:00:00 AM
:
:
:
12:00:00 AM
Also is it possible that if the system datetime is set to 24 hr format then i get time values as
1:00:00
:
:
:
24:00:00 hr
Please advise.
TIA
Hi Xaria,
To generate a list of hours, you can write this query with CTE as:
;WITH cte AS(
SELECT CAST(’1900-01-01 01:00′ AS DATETIME) AS H
UNION ALL
SELECT DATEADD(hh,1, c.h) AS H
FROM cte c
WHERE DATEADD(hh,1, c.h) <= '1900-01-01 23:59:59.997'
)
SELECT CONVERT(VARCHAR(100),h,108)
FROM cte
Let me know if you have any question.
Thanks,
Tejas
SQLYoga.com
or
select convert(varchar(12),dateadd(hour,number,0),108) as dates from master..spt_values
where type='p' and number between 0 and 23
Hi,
How to make a table Read Only in SQL Server?
Regards,
Jeyavel N
One option is to create view with computed column and create permissions for that view to the users
Hi Pinal,
I am new to Sql Server 2008, I am taking the training in sql Server 2008. Would you please suggest for certification which books for test preparation would be helpful. Which practice test(which author) will be helpful
Any feedback would be highly appreciated.
I am looking forward to hearing from you as per your earlier convenience.
Thanks,
Narendra Garg
Hi,
Is there any system table from where we can can time taken for restoration of databases?
From msdb..restorehistory we can get only Start time but I am interested in End time also so that It can help me in analyzing total time taken for restorations.
Thanks!
Vaibhav
Hi,
Is there any system table from where we can get time taken for restoration of databases?
From msdb..restorehistory we can get only Start time but I am interested in End time also so that It can help me in analyzing total time taken for restorations.
Thanks!
Vaibhav
I dont think you can find it in a system tables
One option is to look up the Error Log files
The backup informations will be there where you can find start and ending dates with time
Hi Pinal,
We have database which is in NON -Unicode. we want to convert it into Non-Unicode so that we can support our application for multiple languages.
What should be the best approch to convert it into Unicode.
Is there any tool which can convert tables schema (Varchar to NVarchar)and stored procs into UNICODE.
Thanks
Kumar
Any comment on this please?
Hi sir,
I read in an article and in that i got an info like it is be possible to drop the parent table even when a child table exists using CASCADE CONSTRAINTS…
is it possible to do so.. if possible pls reply me with an example..
This is the info that i read from that article..
What is the Use of CASCADE CONSTRAINTS…?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists..
I am reluctant with this info..pls reply me with the same..
Hello Nandgopal,
CASCADE CONSTRAINTS is not supported in SQL Server. IN SQL Server, CASCADE DELETE and CASCADE UPDATE clause are used to maintain referential integrity.
Regards,
Pinal Dave
Thanks Pinal for your valuable Info…
With Regards,
Nandagopal T
Hello all,
i have query i.e. i have 100 records in table i want to display records from 5 to 15 then how i can do this..???
@ankur do you have any criteria or basis on which you can identify records no. In between you can try like this
select * from (
select *,ROW_NUMBER() OVER(order by (select 1)) as RN from UrTable
)T
where RN>=5 and RN<=15
if you have any criteria then just put it in place of select 1.
Hope this will help.
Heyy It’s warking!!! Thanxxx
Hi,
I am creating a report from a single table which have columns DealerName, CarModels, PurchaseDate
i want create a report which should group both dealername and Models and date should be dynamic columns to display as Purchase 1, purchase 2, Purchase,,,, like least date as purchase 1, to highest date (don’t want to display the date)
Dealer Model Purchase 1 Purchase 2 purchase 3
Kerry Honda 5 2 10
oldmac mazda 5 3 6
can you please help me, or guide me how can i do this
Hi,
I am creating a state report that needs to be in a specific layout. but I am having problems in the query. BTW I am using Mssql 2005
this is my source table:
studentid, school, cycle, InstrSetting, ExcessHrs.
A Student may contain different InstrSettings for a cycle. Saying that, the result I need is as follows.
studentid, school, cycle, InstrSetting, ExcessHrs, Instrsetting2, ExcessHrs2, Instrsetting3, ExcessHrs3
I tried to use Pivot but it won’t work (at least that is my conclusion).
I have some data to support what I am trying to explain.
Source:
stid schid cycle instrset exchrs
1234 122 01 00 0.000
1234 122 02 00 0.000
1234 122 03 00 9.000
1234 122 04 00 0.500
1234 122 04 41 1.000
1234 122 05 41 0.000
Desired Format
stid schid cycle instrset exchrs instrset2 exchrs2
1234 122 01 00 0.000
1234 122 02 00 0.000
1234 122 03 00 9.000
1234 122 04 00 0.500 41 1.000
1234 122 05 41 0.000
Thanks,
Carlos
Hello Sir,
I am Prakash Neupane from Nepal and I am studing BSc Computer Science & IT. I attained you whole presentation in Techmela
Sir, Can I get all material that you show us in presentation so that it will be easy for me to further more understand and practice
Hi
Dear Pinal Dave
Sorry I Can’t Write English Well.
I Have Select query that return integer values.
I want to have a column that show horizontal sum of a row and a Row that show vertical sum of a column.
Example:http://nima-dir.persiangig.com/image/Tbl.JPG
Now I’m using cursor but I prefer using better way.
Please help me
Thanx
@nima
You can directly use it as
Select *,col1+col2+col3 as col4 from urtable
Hope this will help.
Hi Pinal,
i dont know where did it goes wrong, here is my query, when i excute this it gave me the error
USE [NTFRS_SkillsConsolidation_DEVT]
GO
SELECT RecruitId,
[Appliance] AS ApplianceDrills,
[BreathingApparatus] AS BreathingDrills,
[DrivingAndOperation] AS DrivingDrills,
[Knots] AS KnotsDrills,
[SmallGear] AS SmallGearDrills,
[HazardousMaterials] AS HazmatDrills,
[CallsAttended] AS CallsAttended,
[RoadCrashRescue] AS RoadCrashRescueDrills
FROM (SELECT RecruitId, DrillType FROM dbo.vwDrillsSubmitted)T
PIVOT(COUNT(DrillType) FOR DrillType IN([Appliance],[BreathingApparatus],[DrivingAndOperation],
[Knots],[SmallGear],[HazardousMaterials],[CallsAttended],[RoadCrashRescue]))P
GO
the error message is:
Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ‘PIVOT’.
can you please advise what should i do to fix it.
Note that this will work from version 2005 onwards
For versions prior to 2005, use
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
Hi,
I have taken the Scheduled Backup through Jobs.
My Question is Where the respective backups are usually
store..
I mean the path where backups are store..
I have given the path while doing scheduling….
But the Backups were not stored…. i could not found even
single file…..
But It shows the Job executed Suceesfully……..
@bharath
You can find all the information about your backup’s in MSDB for a reference look into this script and modify it according to your usage
http://beyondrelational.com/blogs/ashish/archive/2009/07/22/backup-status-script.aspx
Hope this will help.
When you give the parth, by default they are stored in Server’s directory and not in your local system
Hello Sir,
I have small isssue about Installing SQL SERVER 2008.
I Followed your step how to installed SQL SERVER 2008. Its really good it works.
But my problem is that while installing second time after first one remove from system as well as registry its give me INSTANCE Problem i.e. it displays INSTANCE NAME “MSSQLSERVER” already used. Since Installation failed.
I need your guidance to how solve this problem
Thank You
Hi,
Have u Deleted “Microsoft SQL Server” folder from Program Files, if not please follow the following steps,
1) Open command Prompt
2) Give SQL Server installation path on Command Prompt as per follows,
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release
4) After that run following command,
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe /action=uninstall
Thanks,
Dnyanoba
Hi,
I have a database backup of 500GB which contains 250 days of backup sets. It does not contains expiration date. I want to remove backup sets older than 180 days. How can i do it?
@chinthaka
Hope this will help you
Declare @expiration_log datetime
SET @expiration_log = DATEADD(DD,-180,getdate())
EXECUTE master.dbo.xp_delete_file 0,N’URPATH’,N’bak’,@expiration_log
You can test in carefully before implement as it will delete files from your folder so test it carefully before implement.
If using Maintanance plan then create a new backup everytime and delete the older backups on the basis of creation date & time or create a script to delete old backups.
Regards,
Pinal Dave
Hi Pinal Dave,
I am now facing a problem that is I am using Windows XP as a server to connect Windows Server 2003 as a database by using ODBC. But it pops me error:
Connection failed:
SQLState: ’01000′
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()).
and
Connection failed:
SQLState: ’08001′
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] SQL Server does not exist or access denied.
I have had follow your comment by viewing your blog but it still cannot solve my problem. Can you please guide me?
Thanks in advance!
Hi Everybody,
This is Rama Sai. I am joing as a fresher in a small
Organisation.
They are giving a chance to prepare my self
on Sql server 2005.
so please give any suggestions how
can i start sql server 2005 and if it is possiable send any
links related to sql server 2005.
First Learn SQL
These links will help you in writing the queries
Refer these
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
Hi Pinal,
Recently i have occurred one problem with sql query:
e.g : suppose i have created one product.aspx page and i will get product id in string.
e.g : for (i=0; i<proId; i++)
{
string proId += i + ",";
}
now i have one string like this : "1,2,3,4,6" ….
i want to pass this parameters in sql stored procedure and use in key word of T-Sql
e.g select * from product where pro_id in (@proIdList)
It is possible ??
Thanks in Advance.
Either use a split function and do join
select t1.* from product as t1 inner join split_values(@ProidList) as t2 on t1. po_id =t2.po_id
or
select * from product
where ','+@ProidList+',' like '%,'+cast(po_id as varchar(10))+',%'
I’m a big fan of yours and have referenced your site for 2 years. You have many great solutions for novice, intermediate and even advanced peers. Thank you!!
If I want to add Replication Services to an already running MSSQL 10 (2008) enterprise installation, do I have to stop all services to get replication installed or can I just add the service through “add remove programs”?
Solved my own problem by testing the effect. Apparently the different services are actually services and may be added or removed from the server without affecting the status of the core DBMS services.
Thanks for the opportunity to share.
Hi Pinal,
Can we identify the index is working properly which we are creating on table.
If Yes then how to know my index is used to search the data?
As per my knowledge it is the internal process to maintain the index by sql server. am i write?
Thanks
Set the execution plan and see it
You can use the sys.dm_db_index_usage_stats catalog view to know the usage of an index.
Regards,
Pinal Dave
Dear All,
I have Emp Table with following fields
Emp(Emp_No,Emp_Name,Job,Mgr,Hire_Date,Sal,Comm,Dept_No)
Query::==>>
Display Manager No and salary of the lowest payed employee for that manager.Exclude any group where minimum salary is 6000 or less. sort o/p in descending order????
My database size is 3.5GB. When I try to take transaction log backup, it takes too much time. Why is that?
*Full Backup & Differential Backup does not take much time.
BACKUP LOG [MCIS LIVE] TO DISK = N’F:\DB_TRANLOG.trn’
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10, RETAINDAYS = 14
GO
Hello Chinthaka,
What is the size of log file? If its too large than first shrink it. Try to take backup on different drive.
Regards,
Pinal Dave
Dear all,
i have the two table like
table1
Batchid batchname
——— ————–
1 b1
2 b2
3 b3
table2
indexid Batchid document
——— ——— ————-
1 null b1
2 null b1
3 null b2
4 null b3
5 null b3
6 null b2
**************************
Result i want:—-
indexid Batchid document
——— ——— ————-
1 1 b1
2 1 b1
3 2 b2
4 3 b3
5 3 b3
6 2 b2
here document get the value from batchname(table1)
so it have one or more batchname as under document(table2)
i want to update batchid according to first table.
how can i do this?
@shiv
You can do it like this
update BATCHESSUMMARY
set Batchid=b.batchid
from BATCHESSUMMARY A join BATCHES B on A.DOCUMENT=b.batchname
Hope this will help
if the table2 have the value like ‘doc/b1′
batch table
Batchid——–BatchName
——————————-
1—————–b1
2—————–b2
3—————–b3
4—————–b4
5—————–b5
documentIndex table
indexid—Batchid—-document
————————————–
1———–null———–b1
2———–null———–b1
3———–null———–doc/b2
4———–null———–b3
5———–null———–b3
6———–null———–doc/b2
7———–null———–doc/b4
8———–null———–doc/b4
9———–null———–b5
10———-null———–b5
11———-null———–b5
how can i update documentIndex table for getting the result like below:
indexid—Batchid—-document
————————————–
1———– 1———–b1
2———– 1———–b1
3———– 2———–b2
4———– 3———–b3
5———– 3———–b3
6———– 2———–b2
Hello Shiv,
The desired output can be generated by following simple inner join:
SELECT indexid, Batchid, document
FROM Table1 INNER JOIN table2
ON table1.batchname = table2.document
Regards,
Pinal Dave
SIR
ACTUALLY I WANT TO
UPDATE THE BATCHID WITH NULL IN TABLE2 WHICH COLLECT THIS ID FROM TABLE1
I have a failry large table which contains one image data type column. How can I find out if this table needs to have its indexes rebuilt or re orgarnized.
Can I perform the reindexing or reorganizing any time of the day ie., the database is a live one and there are lot of DMLs that get executed on this table constantly.
Thanks.
hi sir.. im a regular reader of your blog,,
i have a small doubt regarding DBCC commands,,
can we use UNDOCUMENTED DBCC COMMANDS LIKE DBCC PAGE, DBCC CLEANTABLE etc.., in REAL TIME..
plz do reply
Thank you sir.,,
Hello Sir,
I am a regular reader of your Blog. Its a really very nice place for developers. Its very very helpful for me.
I want to execute a insert query automatic whenever i on my computer. I have a User table. and i want to insert user name and system time when the user start the computer.
Plz reply,
Thanks Sir
Hey Saurabh,
You can do by creating Transact-SQL Script Files and with little use of sqlcmd create a command file (.cmd) and schedule it as as Windows Job.
Ref: http://msdn.microsoft.com/en-us/library/ms170572.aspx
sir,
could you please tell me the exact differences between a stored procedure and functions??
regards,
shilpi
Hi
I have all my packages stored on desktop folder ,now i need to move all of them to sql server instance….
Ex:c:\ram\desktop\config
In config file i have all my packages stored
Hi friends,
I have one more question…I need to migrate my tables ,stored procedures..from sql 2005 to sql 2008…
Any immediate response is truly appreciated
One option is to make use of upgrade advicer
Dear Pinal Sir,
Kindly explain difference between various versions of SQL SERVER ie Enterprise editions, developer edition etc…
Hello Pinal ,
I wanted to know is there any way to find out last modified date for a table in SQL Server 2000.
Thanks
Naina
Hello Pinal,
When i checked few of ur comments …found that few techniques are avilable for higher version of Sql Server 2005 or 2008 ( sys.tables ) to check last modified date for a table.
Please let me know is ther any way we can check last modified date for a table in SQL Server 2000.
Thanks
Naina
Hello Naina,
There is no in built feature provided by SQL Server to know the last modified data-time. You would have to design a method as per your requirement.
Regards,
Pinal Dave
hello sir am new to this blog,i have read some of your explanation for the queries send.am impressed with your explanation.
am facing a problem in executing following procedure
it runs successfully “Command(s) completed successfully.”
but when i pass values and execute then the problem raises
ALTER PROCEDURE [dbo].[proc_name](@From INT,@To INT)
as
BEGIN
DROP TABLE ##newTbl
DROP TABLE ##tbl
EXEC(‘SELECT * INTO ##newTbl FROM (SELECT * from table_name WITH (NOLOCK) where ID=15) temp’)
SELECT * INTO ##tbl FROM ##newTbl
ALTER TABLE ##tbl ADD sno INT Identity(1,1)
SELECT * INTO #tbl FROM ##tbl WITH (NOLOCK)
SELECT * FROM #tbl WITH (NOLOCK) WHERE sno BETWEEN @From AND @To
–select * from #tbl2
return
END
when i execute this procedure is giving return value as null and query completed with errors
What was the error you got?
sir,
this is the first time i had a visit for your blog.it will be very helpful for students.
sir i have a query..as u have provided server 2008 interview qs and answers
sir can you please post server 2005 introductio,questions and answers in your blog
i am waiting to see about server 2005 in your blog
thank you sir
Hi Dear,
I am Najam Khan and working as a software Engg., I faced problem in PIVOT.I would like to generate Column at run when query is execute So kindly help me on this issue.
Declare @Issuer int
Declare @From_Dt datetime
Declare @To_Dt datetime
set @Issuer=141
set @From_Dt=’2009-01-01′
set @To_Dt=’2009-12-31′
SELECT * FROM
(SELECT lrc.Policy_No,(select comp_name from company_master where comp_id =(lrc.issuer_id))AS Comp,
(select Data from master_data where master_id=20 and data_value =(ar.prem_Freq)) As Prem_freq, DATENAME(month, lrc.renewal_dt) AS Renewal, lrc.prem_comm_rcvd
FROM life_renewal_comm lrc Inner Join Ar_head ar
ON Ar.policy_no=lrc.policy_no
WHERE lrc.renewal_dt between @From_Dt and @To_Dt
and ar.issuer_id=@Issuer
and lrc.Policy_No IN (select distinct(Policy_No) from life_renewal_comm )) src
PIVOT (SUM(prem_comm_rcvd) FOR Renewal
IN (select +’['+DATENAME(month, @From_Dt)+']‘+’,'+’['+ DATENAME(month, dateadd(m,1,@From_Dt))+']‘
+’,'+’['+ DATENAME(month, dateadd(m,2,@From_Dt))+']‘+’,'+’['+ DATENAME(month, dateadd(m,3,@From_Dt))+']‘
+’,'+’['+ DATENAME(month, dateadd(m,4,@From_Dt))+']‘+’,'+’['+ DATENAME(month, dateadd(m,5,@From_Dt))+']‘
+’,'+’['+ DATENAME(month, dateadd(m,6,@From_Dt))+']‘+’,'+’['+ DATENAME(month, dateadd(m,7,@From_Dt))+']‘
+’,'+’['+ DATENAME(month, dateadd(m,8,@From_Dt))+']‘+’,'+’['+ DATENAME(month, dateadd(m,9,@From_Dt))+']‘
+’,'+’['+ DATENAME(month, dateadd(m,10,@From_Dt))+']‘+’,'+’['+ DATENAME(month, dateadd(m,11,@From_Dt))+']‘)) AS pvt order by Policy_No
SSRS Question:
============
I’ve a table in my CRM database of Image URLs. The images are stored in Sharepoint Document Library.
I’ve a report which will load the dataset with all image URL’s and display the Images in Report. But the images are not getting displayed in SSRS reprot. If I add a image url from any of the website those images are perfectly getting displayed. But not the Images from Sharepoint Server.
Both CRM / Database / Reprot Server / Sharepoint Server are in the same domain.
My first thought is as Sahrepoint works on AD Users, can i do something so that the images will be displayed in REport.
Awaiting your feedback ASAP. Thanks, Raj
Hi Pinal,
I wanted to know the sequencal steps for creating a new db user, with my own schema and grant specific rights to different users to this user.
Thanks and regards,
Dave.
HI Pinal,
Please let me know is there any tool avilable to check the SQL Server 2000 to Sql Server 2008 Migration, so that we can anlyze the procedure or any discrepancies with the Database we are trying to migrate and list all potential issues.
Thanks
Naina
Hello Naina,
Use SQL Server 2008 Upgrade Advisor to identify any possible issue that can be downloaded from here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en
Regards,
Pinal Dave
Hi Pinal sir,
I have a question…I need to migrate my tables ,stored procedures..from sql 2005 to sql 2008…
If possible could provide us some doc so that it will be useful…
Any immediate response is truly appreciated
Thanks,
Raki
Take a backup from 2005 and restore it in 2008
Hello Pinal,
Thanks a lot for ur valuable comments.
Thanks
Naina
Hi
I am using MS SQL Server 2008 on MS Windows Server 2003.
While attempting to mirror two sql servers without a witness server, the following error kept coming through right at the end:
The server network address “TCP://MOSSSQL-B.mycompany.com:5022″ can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error:1418)
I checked that both endpoints were created and on/started. I can ping both servers from the other server. I can telnet both servers on 1433 and 5022 both sides work.
I am following these steps …..
1. Created a new database named it as testmirror.
3. Right clicked on the database Tasks-backup-BackupType–Full–Ok created testmirror.bak
4. Right clicked on the database Tasks-backup-BackupType–Transactional Log–Ok created testmirror-Transaction Log backup (testmirror.trn)
5. Copied this testmirror.bak from my system to the Mirror Server.
6. Right click on Mirror Server Database–Restore Database –Manually wrote down the ToDatabase
7. Selected from device and selected the added the copied backup file
8. Performed a restore (with no recovery) — selected the relevant backup–checked the checkbox on restore
(Did it individually for backup and transactional log)
9. After going through security wizard When I start mirroring on principal server for testmirror DB. I am getting this error
The server network address “TCP://MOSSSQL-B.mycompany.com:5022″ can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error:1418)
I have tested the network connectivity with ping and telnet. I was able to telnet on both ports 1433 and 5022 on both machines. I double checked the connectivity with netstat on other server.
I don’t know what I am doing wrong?
I tried following things
1. I used local system Administrator accounts when i was connecting to the mirror server in security wizard. but no joy….
2. I created mirroring login on both SQL Servers and gave sysadmin role to “mirroring” login …. bot no joy
As mentioned here
http://blog.sqlauthority.com/2007/04/22/sql-server-fix-error-1418-microsoft-sql-server-the-server-network-address-can-not-be-reached-or-does-not-exist-check-the-network-address-name-and-reissue-the-command/
I followed
Step 1) system Firewall should not block SQL Server port.
Step 2) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.
Just to make sure follow one more step which may or may not be necessary.
Step 3) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Client Configuration
Enable TCP/IP protocol.
but again no joy …. still the same error – 1418
Any help will really be appreciated ….
Many thanks…
we are coding getdate() functions for all the date columns in our application. presently our servers are in Los Angeles (US)
we will go worldwide release . i have got some doubt .IF SOME one FROM different country post comment ON their profiles. we will order BY GETDATE coloumn .IF i am IN different timezones
how i have TO TAKE care this one .. Actually i dont know how it will display ..IF posible can u suggest what i need TO do FOR this
Hi,
I have add Constraints (Check Constraints) to a table.
There is no Try… Catch block or RAISEERROR message.
If user enter invalid data, the row of data would not be inserted into the database and EXECUTENONQUERY returns -1.
How can I have exception or error raised on this instead of returning -1 to me?
Thank you & Good day.
Only way is to use try catch block at the .NET application
Hi Pinal
I have a question ..
Why We can not create or alter procedure,trigger inside
if block …
IF (1=1)
BEGIN
PRINT ‘Hiii’
CREATE PROCEDURE Rk
As
BEGIN
Print ‘Procedure Created.’
END
END
ELSE
BEGIN
PRINT ‘Byyy’
END
Thanks
Rahul Bhargava
It is becuase CREATE/ALTER procedure should be the first statement in the block
You can rewrite it as
IF (11)
BEGIN
PRINT ‘Byyy’
END
GO
CREATE PROCEDURE Rk
As
BEGIN
Print ‘Procedure Created.’
END
GO
PRINT ‘Hiii’
Thanks Madhivanan to reply me soon,
But isn’t this looks embarrassing .
As you said that we can not create or alter procedure ,trigger inside if or else block….
Isnt there any way to bypass this..
See my reply
I have given the alternate solution
Hello ,
As you described to copy the whole database and its objects using management studio [Create Script to Copy Database ], can it possible to create a query file using a store procedure ?
as i am using one application and if i fire that store procedure with a file name and it will create a sql file at that path.
Yes Madhivanan i had seen your reply
but what will happen when i need code like..
If any procedure already in my DB then alter it otherwise create it…
You need something like this as dynamic sql
if exists(……)
EXEC(‘ ALTER PROCEDURE …’)
else
EXEC(‘ CREATE PROCEDURE …’)
Hi Dave,
I am Wilson Gunanithi. Few years back, I used to ask many questions to you and got the answers also.
But, somebody used my name for the irritating questions. So, please ignore if the questions are irrelevant or very silly.
Note:
You are doing very very great job. :)
Kind Regards,
Wilson Gunanithi.J
Hi Wilson,
Thanks for coming back, do not worry at all. If you spot someone using your name, do let me know and I will take necessary actions.
Kind Regards,
Pinal
Hello,
Just read ur blog post .
Its really awesme. Ur doing a great job :)
Tahnsk
Naina
Hi,
DECLARE @var2 INT = 0
declare @var3 varchar(100)
set @var3=’table_’ + @var2
create table @var3
(
id int
)
i want table name should be ‘table_’+ @var2.
out put like this table_0,table_1.
But why do you want to create table dynamically?
You need to use dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
@naresha you have to use dynamic SQl for this purpose.. you can do it like this..
DECLARE @var1 int=1
declare @var3 varchar(100)
DECLARE @QRY varchar(max)
set @var3=’tbl_’ + cast(@var1 as varchar(5))
SET @qry=’create table ‘ + @var3
SEt @QRY=@QRY+’(id int)’
print @qry
exec (@qry)
Hi
We have SQL 2005 Free Addition and wish to archive excess data, are we able to do this or is there a program that we could use for this purpose?
Hi Pinal,
Can u explain in detail how we can troubleshoot issue of CPU and Memory high utilization in step by step manner with the help of Perfomance, Query and profiler. and how we can improve the utilization indirectly by query optimization as said earlier post,
Please explain step which we have to follow troubleshoot issue.
Thanks in advaced.
Hi sir,
Could you please give me some idea ..on how to generate script for all jobs under sql server agent,,
Previoulsy i posted some question ,but unfortunatley i didn’t receive any reponse for them..
Thanks
Rajen
Please use SSMS, Select all jobs under SQL Server Agent and Generate Script.
Hope this will help you!!
Hello Pinal,
I was tring to use Microft Upgrade Advisor with SQl SERVER 2008
clicked on :-
Launch Upgrade Advisor Analysis Wizard
after giving Instance name and sa Authentication its not going ahead.
Im getting below Error :-
SQL Server version: 10.00.1600 is not supported by this release of Upgrade Advisor, only SQL2000 or SQL2005 is supported.
Please let me knw how to resolve this
Thanks
Naina
Hi Dave
I modified about a hundred stored procedure and created about a hundred .sql files on my computer and I want to update (alter the existing one) them like a bunch or through a batch file to run directly from my computer to a different server database.
I can open the files in SQL Server Management studio and “Execute” them but I’m looking for something neater
Any suggestions please
Best Regards
Florin
Hi Dave,
I need some help regarding the locking mechanism of SQL server. There’re so many chapters written already on this… but I’m not getting what I need exactly. The problem is generating of a serial number automatically. I cannot use the auto increment field option of SQL server, as in my case the serial number field depends on other parameters also.
I used to store the last serial number in a particular table that holds other parameters too. And when in need I used to fetch the last serial number and incremented it. However, this procedure is not optimum in a network based system when more than one machine using the same remote database, as more than one user may hit the database with same query fetching the last serial number at the same time. Please note that the serial number, along with other parameters, creates a unique key for another table. So… when two or more users are reading the same serial number at a time, same unique key is generated for different records and I need to control this.
Can you please guide me how I can lock the code from other users when one user is reading and then incrementing and updating it. Also I need to know, how to release the lock when the current user is done with the process so that others can access it. It would also be very helpful if you give me some information on how SQL server prioritizes which user from the queue should get the next response. Used platform and code bases are – .NET, VB/C#. SQL Server 2000 preferable.
Thanks
Subhrajit.
I don’t see why you couldn’t use your approach of separate table or I missed something here. There is no way multiple queries could fetch the same serial number if you are doing the fetching and inserting inside a transaction. This is really easy with .NET/C#.
using (SqlConnection conn = ..)
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var fetchCmd = new SqlCommand(“SELECT Serial FROM SerialTable”, conn, tran);
var serial = fetchCmd.ExecuteScalar();
var updateCmd = new SqlCommand(“UPDATE SerialTable SET Serial = Serial + 1″, conn, tran);
updateCmd.ExecuteNonQuery();
var insertCmd = new SqlCommand(“INSERT INTO MyTable(Serial) VALUES(@serial)”, conn, tran);
insertCmd.Parameters.AddWithValue(“serial”, serial);
insertCmd.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
conn.Close();
}
Hello Dave,
Is possible to create an Access database using SQL server 2005? I’ve been Googling a lot lately for an answer but I couldn’t find any.
Please help me out.
Thanks a lot in advance.
Edward
@Edward
SQL Server does not create Access databases. You need Access for that.
Violation of PRIMARY KEY constraint ‘PK_users’. Cannot insert duplicate key in object ‘dbo.users’
/* Make sure all Jitter JC users are up to date */
DELETE FROM users
where WWID in (select WWID from juice.dbo.emp where isactive = 0)
INSERT INTO users (Username, NameGiven, NameFamily, Email, WWID, CompanyID, Mobile, IsVerified, VerifyText)
select UPPER(RTRIM(SUBSTRING(WindowsUserName,4,100))), NameGiven, NameFamily, Email, WWID, ’002010′, ’61′ + RTRIM(SUBSTRING(MobileNumber,2,100)), CASE WHEN MobileNumber IS NULL THEN 0 ELSE 1 END, NULL
from juice.dbo.emp e
where e.WWID not in (SELECT WWID FROM users) and e.Email not in (SELECT Email FROM users WHERE Email is not NULL) and e.WindowsUserName not in (SELECT Username FROM users) and e.IsActive = 1 and LEN(e.WindowsUserName) > 3 and RTRIM(SUBSTRING(e.WindowsUserName,4,100)) not in (select username from users where username is not NULL)
UPDATE users
SET NameGiven = e.NameGiven,
NameFamily = e.NameFamily,
Username = UPPER(RTRIM(SUBSTRING(e.WindowsUserName,4,100))),
Email = e.Email,
CompanyID = ’002010′
FROM juice.dbo.emp e
@Kumar.r
Run the SELECT without the INSERT to see what it returns. Does it have duplicates?
Hi Pinal,
I really appreciate your contribution.. guys like learn a lot from your blog. i have a situation to get report in sql 2000. this is my idea of doing? is there any other best way you can suggest? or else just help me in completing this query. here is my query
Create table ##table1 (orderid,product,col1,col2, col3)
select orderid, case toyid
SUM(CASE toyid WHEN 1 THEN toyCount ELSE 0 END) AS [col1]
SUM(CASE toyid WHEN 2 THEN toyCount ELSE 0 END) AS [col2]
SUM(CASE toyid WHEN 3 THEN toyCount ELSE 0 END) AS [col3]
end toyid
from vwtoys
–result gives 1 row insert as 1st row in ##table1
select orderid, case groceryid
SUM(CASE groceryid WHEN 1 THEN groceryCount ELSE 0 END) AS [col1]
SUM(CASE groceryid WHEN 2 THEN groceryCount ELSE 0 END) AS [col2]
SUM(CASE groceryid WHEN 3 THEN groceryCount ELSE 0 END) AS [col3]
from vwgrocery
–result gives 1 row insert as second row in ##table1
select orderid, case sportitemid
SUM(CASE sportitemid WHEN 1 THEN sportitemCount ELSE 0 END) AS [col1]
SUM(CASE sportitemid WHEN 2 THEN sportitemCount ELSE 0 END) AS [col2]
SUM(CASE sportitemid WHEN 3 THEN sportitemCount ELSE 0 END) AS [col3]
from vwsportitem
—-result gives 1 row insert as third row in ##table1
I have 10 table’s like this….want to insert 10rows into ##table1
like
insert into(orderid, ‘toys’, col1, col2, col3) —when it is first row(vwtoys)
insert into(orderid, ‘groceries’, col1, col2, col3) —when it is second row(vwgrocery)
insert into(orderid, ‘sport’, col1, col2, col3) —when it is third row(vwsport)
—
—
10 rows
how can i do this?
@Tom
Use UNION ALL to run all the SELECTs at the same time.
Hi Pinal…
My self Narsing working on Sql server 2005 and in mean while i want to attend for interviews in big companies,can u pls give me the Interview questions so that it is useful to me to get good jop.
Thanks & Regards
Narisng
Hi Pinal,
i need some help for the following query
i have two columns and the datatype are in varchar and the values stored in the field are 67.00-per HOUR-USD,97.00-per HOUR-USD like that.
now i have to display three coloumns in report
1st columns= 67.00-per HOUR
2ndcolumn= 97.00-per HOUR
3rd column=1stcol-2ndcol=30-per Hour
please solve this query
@Venkat
WITH
A
AS
(
SELECT
’67.00-per HOUR’ Col1,
’97.00-per HOUR’ Col2
)
SELECT
Col1,
Col2,
CAST
(
CAST(LEFT(Col2, 5) AS SMALLMONEY)
– CAST(LEFT(Col1, 5) AS SMALLMONEY)
AS VARCHAR(5)
) + ‘-per HOUR’ Col3
FROM
A;
It is usually considered good deisgn to remove the text from the COLUMN, and making it SMALLMONEY instead.
You may need to use generalised approach
WITH
A
AS
(
SELECT
'67.00-per HOUR' Col1,
'974556.00-per HOUR' Col2
)
SELECT
Col1,
Col2,
CAST
(
CAST(LEFT(Col2, 5) AS SMALLMONEY)
- CAST(LEFT(Col1, 5) AS SMALLMONEY)
AS VARCHAR(15)
) + '-per HOUR' as brian,
cast
(
cast(substring(col2,1,patindex('%[^0-9.]%',col2)-1) as money)-
cast(substring(col1,1,patindex('%[^0-9.]%',col1)-1) as money)
as varchar(10))+ '-per HOUR' as madhivanan
FROM
A;
Hi pinal,
In my project we have sql server 2000 db and we want to do data conversion to another prebuilt application which is in sql server 2005 and we want to get rid of the sql server 2000 after the conversion.we are planning for one time
loading from 2000 to 2005.
Now my question is do we need to migrate 2000 db to 2005 for doing data conversion and placing data in 2005 db?
By using ssis 2005 can we connect to 2000 database and do conversions, loading into 2005 db?
please let me know your suggestions it will be very helpful to me.can you give me any strategy doc for this kind of projects.Thank you
One option is to take a backup from version 2000 and restore it version 2005. Also make use of Upgrade advisor. Note that there can be behaviral changes between the versions. Read about them too
Hi Pinal,
Let me start with saying your site is excellent source of SQL Server stuff.
I am looking for a SQL Server Health Check software/scripts. Can you suggest any?
Thank you
i want to Query 2nd Tuesday of Current Date
How Can i query ? Plz Help Me out ,Nex_Day Function is not support with sql management
How Can i query 2nd Tuesday of Current Date ? Plz Help Me out ,Nex_Day Function is not support with sql management
select min(dates) as second_tuesday from
(
select
dateadd(day,number,dateadd(month,datediff(month,0,getdate()),0)) dates from
master..spt_values where type=’p’
) as t
where datepart(weekday,dates) =3 and day(dates)>=8
tHNX aLOT :)
I have query like below
declare @str_CustomerID int
Insert into IMDECONP38.[Customer].dbo.CustomerMaster
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′
)
select @str_CustomerID= scope_identity()
after execution it returns null in my parameter
i want to get value of identity how can i do that
the main issue over here is “IMDECONP38″- server name that i used if i remove this i can able to get the value of identity in my parameter
@pranay
maybe SCOPE_IDENTITY() doesn’t work on another server. Try the OUTPUT INTO clause instead.
Pinal has an explanation and example of it here: http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/
Hi pinal
SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot
in this if u dont mind can u suggest how to use this snapshot for other severs . is it posible to restore in linked server .I f posible how?
Hi,
I have been a fan of your blog ever since I started my carrer 4 years back. Is there a place where i can practice complex joins and writting dynamic queries. what i mean is so area where i can have a sample database which has certain predefined tasks where i can practice sample examples which help me write complex joins and dynamic queries
@Girish Jaiswal
Pinal has a link to the sample database here: http://blog.sqlauthority.com/2007/05/23/sql-server-2005-northwind-database-or-adventureworks-database-samples-databases/
I am interested in the writing queries and acheiving results. Is it possible for you to provide me tasks which I can help resolve. I have a unsual liking for writing queries.
@Girish Jaiswal
MS T-SQL forum might be a good place to start. Many questions where you get the chance to write a query and post your answer to help somebody.
http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads?filter=unread
There is a need to copy table data from db1 that resides on server1 to db2 that resides on server2.
We are using sql server 2005. Wondering what are my options. We attempted to use a job and write t-sql on server2, but unable to ‘see’ server1
@Tom Edwards
Have you tried using SSIS. It has tasks to copy data.
Ultimately, you can generate SQL scripts via the UI to copy and paste with a connection to the other server.
Hi Dave,
I am intermediate level user of SQL Server however I like to know about technology deeply.
My query is ” What changes are required to run the SQL Server if the host name changed after SQL Server installation?”
AKHILESH MISHRA
UAE
Hi Pinal
I have SQL 2005 with a db named test. DB is in Full Recovery Mode. There are two file-groups one is Details & another is Security there are two tables respectively. One of table gets deleted wrongly. I have full backup of whole db, file-group backup in which that deleted table was exist. Now I have to recover that deleted table. Every-time when I try to restore the db with file-group backup the db goes in “RECOVERING” mode. This has happened many times with me. Can you suggest me what to do ?
Hello Pinal,
Please let me know if we found some issues with SQL SERVER 2000 while running the Upgrade Advisor, and we fix that…and Upgrade it to SQL SERVER 2008…….
IS IT backward comaptible ???
If we found sme issue and cant goahead ….is it possible to go back to SQL SERVER 2000
Kindly let me know about this.
Thanks
Naina
No. It is not backward compatible. You cant restore the database over 2000 from 2008
Hi Pinal,
I’m trying to setup p2p replication on sql server 2008 but in the internal network team. The two nodes have two network cards each of them so i want replication to run in a private network. Any ideas please?
Thanks in advance.
Hello Dave, I am sort of a beginner with sql and in the work that I do I only have the option of creating multiple queries in one window to populate my reporting. I was wondering instead of highlighting sections of the query at a time, is it possible to call what lines you want the query to execute next?
Thanks Jayme
@Jayme
If you highlight a few lines, it will only execute those lines.
Hi ,
I develop this query to find string from database and chage it. Like in this case i have data where there is ‘NULL’ string and I want to convert it to NULL value.
So i write down this query to automatic fetching columname and tablename from system database and perform string operation.
query works it execute but it wont update any field.
Any help will be really appriciated
use project
Declare @tablename varchar(200)
Declare @columname varchar(200)
Declare @find varchar(20);
Declare @replace varchar(20);
DECLARE @varSQL VARCHAR(512);
SET @find = ‘NULL’;
SET @replace = Null;
Declare db2 cursor for
SELECT name AS TableName FROM sys.tables
open db2
fetch next from db2 into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
—- Cursor Db1 fetching columname from system table using Tablename—–
Declare db1 cursor for
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
open db1
fetch next from db1 into @columname
WHILE @@FETCH_STATUS = 0
BEGIN
set @varsql = ‘update’ + @tablename +’
set’+@columname+’=”replace(convert(varchar(max),@columname),’+@find+’,'+@replace+’)
where’+ @columname +’like ‘+ @find
exec (@varsql)
FETCH NEXT FROM db1 INTO @columname;
END
CLOSE db1
DEALLOCATE db1
——cursor Db1———————————-
FETCH NEXT FROM db2 INTO @tablename ;
END
CLOSE db2
DEALLOCATE db2
—–Cursor Db2————————–
@Tapan
+@replace
will add a NULL value to a string, which turns the entire string into a NULL. Instead or
SET @find = ‘NULL’;
SET @replace = Null;
Try
SET @find = ”’NULL””;
SET @replace = ‘Null’;
hi Brian,
I want to convert entire string in the Null value.
For exa. if I don’t define 2nd cursor then query will give me all columname and tablename.
use project
Declare @tablename varchar(200)
Declare @columname varchar(200)
Declare @find varchar(20);
Declare @replace varchar(20);
DECLARE @varSQL VARCHAR(512);
SET @find = ‘NULL’;
SET @replace = Null;
Declare db2 cursor for
SELECT name AS TableName FROM sys.tables
open db2
fetch next from db2 into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
—- Cursor Db1 fetching columname from system table using Tablename—–
–Declare db1 cursor for
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
–open db1
–fetch next from db1 into @columname
—WHILE @@FETCH_STATUS = 0
—BEGIN
—set @varsql = ‘update’ + @tablename +’
—set’+@columname+’=”replace(convert(varchar(max),@columname),’+@find+’,'+@replace+’)
—where’+ @columname +’like ‘+ @find
—exec (@varsql)
–FETCH NEXT FROM db1 INTO @columname;
—END
–CLOSE db1
—DEALLOCATE db1
——cursor Db1———————————-
FETCH NEXT FROM db2 INTO @tablename ;
END
CLOSE db2
DEALLOCATE db2
—–Cursor Db2————————–
But when I define 2nd cursor for converting ‘NULL’ string in to a NULL value then it wont works.
Query is executing successful but won’t perform any action on database. so without error message I cant figure it out solution.
I tried with your suggestion but It wont work.
@Tapan
The main point is the variables.
If you were to UPDATE the value ‘NULL’ to a NULL, the statement would be:
UPDATE table SET column = NULL WHERE column = ‘NULL’;
If this is done with variables:
DECLARE @from_value VARCHAR(6);
DECLARE @to_value VARCHAR(6);
SET @from_value = ‘NULL’;
SET @to_value = NULL;
UPDATE table SET column = @to_value WHERE column = @from_value;
If the SQL statement itself is also a string, it needs more quotes:
DECLARE @from_value VARCHAR(6);
DECLARE @to_value VARCHAR(6);
DECLARE @sql VARCHAR(75);
SET @from_value = ”’NULL”’;
SET @to_value = ‘NULL’;
SET @sql = ‘UPDATE table SET column = ‘ + @to_value + ‘ WHERE column = ‘ + @from_value;
select @sql;
Or, the quotes could be in the statement:
DECLARE @from_value VARCHAR(6);
DECLARE @to_value VARCHAR(6);
DECLARE @sql VARCHAR(75);
SET @from_value = ‘NULL’;
SET @to_value = ‘NULL’;
SET @sql = ‘UPDATE table SET column = ‘ + @to_value + ‘ WHERE column = ”’ + @from_value + ””;
select @sql;
But in both cases @to_value must be in quotes. If it isn’t, watch what happens:
DECLARE @from_value VARCHAR(6);
DECLARE @to_value VARCHAR(6);
DECLARE @sql VARCHAR(75);
SET @from_value = ‘NULL’;
SET @to_value = NULL;
SET @sql = ‘UPDATE table SET column = ‘ + @to_value + ‘ WHERE column = ”’ + @from_value + ””;
select @sql;
The whole string becomes NULL.
Please look at this until you understand it. As NULL is treated differently than other values. Note, all the quotes i used are regular single-quotes. They may need to be edited to single-quotes if copied from here, as wordpress changes them.
Please have a look on SP,
CREATE PROCEDURE spGetDynamicResultSet
AS
DECLARE @SQL NVARCHAR(1000)
DECLARE @ID NVARCHAR(80)
DECLARE @FEATURE NVARCHAR(80)
SET @SQL=”
DECLARE C1 CURSOR FOR SELECT DISTINCT table2.pgid,table2.FQ FROM table2 INNER JOIN table1 ON table2.pgid = table1.pgid where required = 1
OPEN C1
FETCH C1 INTO @ID,@FEATURE
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL=’SELECT pgid,a1,a2,a3,’+@FEATURE+’ FROM table1 WHERE pgid=’+@ID
EXEC(@SQL)
FETCH C1 INTO @ID,@FEATURE
END
CLOSE C1
DEALLOCATE C1
TABLE1 IS
a1 a2 a3 f1 f2 f3 pgid
dfs fdf df x y z 1
fgdfg dfgfdg fdgfdg x1 y1 z1 1
TABLE 2 is
pgid FQ required
1 f1 1
1 f2 1
1 f3 0
I NEED OUTPUT LIKE
pgid a1 a2 a3 f1 f2
1 dfs fdf df x y
1 fgdfg dfgfdg fdgfdg x1 y1
PLease send me email back or let me know how can i check answer
@MUhammad Iqbal
Here’s a quick solution:
SELECT DISTINCT
TABLE2.pgid,
TABLE1.a1,
TABLE1.a2,
TABLE1.a3,
TABLE1.f1,
TABLE1.f2
FROM
TABLE2,
TABLE1
WHERE
TABLE2.required = 1
– Join TABLE1 for information.
AND TABLE1.pgid = TABLE2.pgid;
Hello Pinal,
I want to know how to use isolation level ?
so pls help me..
Regards,
Ravi
Read about it in SQL Server help file
Sir,
I am using SQL SERVER 2005 MANAGEMENT STUDIO EXPRESS.
I am using it as a back end for ASP.NET.. I always get a login error which says the user is not associated with a trusted a trusted SQL SERVER connection.. How to fix this prob?.. plzz help me as early as possible as my work is getting affected.. Waiting for ur reply..
Thanx in advance
Make sure you set up a proper connection string
http://www.connectionstrings.com
Hey Dev,
i have a Table
age Name Loc
10 XXX AP
10 YYY MP
20 AAA UP
20 BBB HP
30 CCC GP
40 DDD TY
I NEED TO DISPLAY ALL THE COLUMNS OF THE TABLE WHICH ARE REPEATING.
THE OUT PUT SHOULD BE:
10 XXX AP
10 YYY MP
20 AAA UP
20 BBB HP
* SHOULD OMIT THE NON-REPEATED COLUMNS i,e WITH AGE 30 AND 40 in this example
@Sudarshan
WITH
Data(age, Name, Loc)
AS
(
SELECT 10, ‘XXX’, ‘AP’ UNION ALL
SELECT 10, ‘YYY’, ‘MP’ UNION ALL
SELECT 20, ‘AAA’, ‘UP’ UNION ALL
SELECT 20, ‘BBB’, ‘HP’ UNION ALL
SELECT 30, ‘CCC’, ‘GP’ UNION ALL
SELECT 40, ‘DDD’, ‘TY’
)
SELECT
age,
Name,
Loc
FROM
Data Data_1
WHERE
EXISTS
(
SELECT
*
FROM
Data Data_2
WHERE
Data_2.age = Data_1.age
GROUP BY
Data_2.age
HAVING
COUNT(*) > 1
)
or
WITH
Data(age, Name, Loc)
AS
(
SELECT 10, 'XXX', 'AP' UNION ALL
SELECT 10, 'YYY', 'MP' UNION ALL
SELECT 20, 'AAA', 'UP' UNION ALL
SELECT 20, 'BBB', 'HP' UNION ALL
SELECT 30, 'CCC', 'GP' UNION ALL
SELECT 40, 'DDD', 'TY'
)
select t1.* from data as t1 inner join
(
select age from data group by age having count(*)>1
) as t2 on t1.age=t2.age
Hi Pinal,
I am mahesh i am fresher learning sql,
I have no clarity what happens in memory during creation of clustered index, whether data in the table wil be rearranged or a seperate table wil be created for the index page in order specified in the index defnition.
Run the profile and see if you get a clue
Hi Mr. Dave,
I want to ask about master database recovery, i knew that backup master database is very important. All i know is if you want to restore & recover master and the SQL service is stopped, then :
1. Backup master database
2. Open command-line and goto “c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn” and type sqlservr.exe -m
3. open sqlcmd through command-line and then RESTORE DATABASE master FROM….
But what if the physical file of master is corrupted or broken, how i can even restore master. Well i did litle experiment, i change file name of physical file so sql instance can’t started. And i try to do step 2, running sqlservr.exe but it failed. And it was error too when i run sqlcmd to RESTORE database.
Please explain it, how we can recover & restore master if the file is broken?
Hi All,
Is it possible if I want to display cursor result set in each different columns instead rows for example,
DECLARE C1 CURSOR FOR SELECT DISTINCT ———–
—
—
—
Fetch into @id,@feature
NOw when i open this cursor its default behaviour will be
FIRST TIME result
@feature
x
x1
x2
Second Time
@feature
y
y1
y2
and so on BUT I want to display above information as below
@feature @feature
x y
x1 y1
x2 y2
Please advice if it is possible
Thanks.
Iqbal
It seems you need to use a PIVOT
Read about it in SQL Server help file
Hi Pinal,
My requirement is for removing records that are almost duplicates based on the date field. For instance:
Record 1:
Record 2:
(difference in date and text)
Record 2 could also be:
Record 2:
(difference in date only)
I need the latest date for C1 + C2 + C3 regardless of C5, so that I get the comment dated 7/30/2009 every time. I’ve got a procedure that uses a self-join and a temp table, and most times it’s okay, but if the record with the right date is not the record with the max(ID), then it’s wrong.
IF EXISTS ( SELECT * FROM TEMPDB.DBO.SYSOBJECTS O WHERE O.XTYPE IN ('U') AND O.ID = OBJECT_ID(N'TEMPDB..#TEMP2') ) DROP TABLE #TEMP2 SELECT IDENTITY(INT,1,1) AS ID ,CASE_IDENTIFIER ,DOCUMENT_TYPE ,DOCUMENT_NUMBER ,MILESTONE ,MAX(CAST(MILESTONE_DATE AS DATETIME)) AS MILESTONE_DATE ,MILESTONE_REMARKS INTO #TEMP2 FROM CASE_MILESTONE_TEMP WHERE CAST(MILESTONE_DATE AS SMALLDATETIME) BETWEEN (GETDATE() - 31) AND GETDATE() GROUP BY CASE_IDENTIFIER ,DOCUMENT_TYPE ,DOCUMENT_NUMBER ,MILESTONE ,MILESTONE_DATE ,MILESTONE_REMARKS ORDER BY ID ASC IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='CASE_MILESTONE_FOR_REPORTS3') DROP TABLE CASE_MILESTONE_FOR_REPORTS3 SELECT T.ID ,T.CASE_IDENTIFIER ,T.DOCUMENT_TYPE ,T.DOCUMENT_NUMBER ,T.MILESTONE ,T.MILESTONE_DATE ,T.MILESTONE_REMARKS INTO CASE_MILESTONE_FOR_REPORTS3 FROM #TEMP2 T INNER JOIN (SELECT CASE_IDENTIFIER ,DOCUMENT_TYPE ,DOCUMENT_NUMBER ,MILESTONE ,MAX(ID) AS ID FROM #TEMP2 GROUP BY CASE_IDENTIFIER ,DOCUMENT_TYPE ,DOCUMENT_NUMBER ,MILESTONE) TMP ON TMP.CASE_IDENTIFIER = T.CASE_IDENTIFIER AND TMP.DOCUMENT_TYPE = T.DOCUMENT_TYPE AND TMP.DOCUMENT_NUMBER = T.DOCUMENT_NUMBER AND TMP.MILESTONE = T.MILESTONE AND TMP.ID = T.ID GROUP BY T.ID ,T.CASE_IDENTIFIER ,T.DOCUMENT_TYPE ,T.DOCUMENT_NUMBER ,T.MILESTONE ,T.MILESTONE_DATE ,T.MILESTONE_REMARKS ORDER BY T.MILESTONE_DATE DESC( I hope that formatted properly)
I’ve tried the max(DATE), but the fact that the entire record is not exactly the same is what throws this off. Any help whatsoever will be MUCH appreciated.
@Teresa
Use ROW_NUMBER() in a CTE to number the records. For example:
WITH
Data(C1, C2, C3, C4, C5)
AS
(
SELECT ‘ABB’, ‘AMD’, ’001′, ’6/30/2009′, ‘remark’ UNION ALL
SELECT ‘ABB’, ‘AMD’, ’001′, ’7/30/2009′, ‘comment’ UNION ALL
SELECT ‘ABB’, ‘AMD’, ’001′, ’7/30/2009′, ‘remark’
),
Data_RN
AS
(
SELECT
C1,
C2,
C3,
C4,
C5,
ROW_NUMBER() OVER(PARTITION BY C1, C2, C3 ORDER BY C4 DESC) RN
FROM
Data
)
SELECT
C1,
C2,
C3,
C4,
C5
FROM
Data_RN
WHERE
RN = 1;
Thank you, Brian, I will give this a try and let you know. I had to run my procedure 3 times this morning to get the correct record where date and remark were different. *sigh*
Hi Brian,
I neglected to specify that I’m using this on a SQL 2000 database. We’re upgrading to 2005 this year, but the government isn’t as good at keeping up to date as the rest of the world. :\
@Teresa
An Id COLUMN would be especially helpful here.
I’m not sure off-hand how to do it. Perhaps you can ask in the Microsoft T-SQL forums: http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads
Thanks Brian, I will.
Declare @Table1 table ( C1 varchar(10), C2 varchar(10), C3 varchar(10), ProcessDate datetime, Remark varchar(10))
insert into @Table1
SELECT ‘AB1′, ‘AM1′, ’001′, ’6/30/2009′, ‘Remark’ UNION ALL
SELECT ‘AB1′, ‘AM1′, ’001′, ’7/30/2009′, ‘Comment’ UNION ALL
SELECT ‘AB2′, ‘AM2′, ’002′, ’7/30/2009′, ‘Remark’ UNION ALL
SELECT ‘AB2′, ‘AM2′, ’002′, ’5/30/2009′, ‘Comment’
select * from @Table1
Select a.*
from @Table1 A
JOIN ( Select C1
, C2
, C3
, MAX(ProcessDate) Max_Process_Date
From @Table1
group by C1
, C2
, C3
) B ON A.C1 = B.C1
and A.C2 = B.C2
and A.C3 = B.C3
and A.processdate =B.Max_Process_Date
order by A.C1, A.C2 , A.C3, A.ProcessDate
Since you have mentioned that depending on C1, C2, C3 you wanted the latest date, do an inner query that has your most uniue columns, if you think, you could have duplicates dates for the same combination of C1, C2, C3 then you need to do a group by on inner query otherwise, it should be fairly simple. I am still not sure if I understand your question correctly ?
Please let us know if this helps.
~ IM.
Thank you, Imran, this may be what I need. I did a test run against my table and tweaked it a little, and it looks good so far. I’ll know for sure when I run the report later this morning.
Thank you so much!
Hi,
We have a script that scripts out all agent jobs from one instance. The script is DMO/VB Script. I need to load these sql files into another sql server instance excluding some jobs, and disable all these after loading. When an Operation DBA runs the command prompt/Windows script it must:
1) Script out all agent jobs in primary instance
2)Load these jobs, excluding few, in secondary instance
3) Disable all the created jobs in the secondary instance
The primary instance and secondary instances are passed by the operation DBA. Can you please help me out with this Windows script.
Thanks,
Kiran
Please advice when I call following SP through MS Access its give me error some thing like FILE IS ALREADY OPEN EXC OR YOU DONT HAVE PERMISSION TO VIEW DATA ANY IDEA WHY
Thanks in advance.
ALTER PROCEDURE spGetDynamicResultSet
(
@TABLE_NAME varchar(100) = NULL
)
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @QRYSQL NVARCHAR(4000)
DECLARE @ID NVARCHAR(80)
DECLARE @FEATURE NVARCHAR(4000)
DECLARE @SQL_GET_TABLE NVARCHAR(4000)
DECLARE @GET_TABLE NVARCHAR(100)
SELECT @GET_TABLE = @table_name
–
IF EXISTS(SELECT * FROM master..sysservers
WHERE srvname = ‘NMDAccess’)
EXEC sp_dropserver ‘NMDAccess’, ‘droplogins’
EXEC sp_addlinkedserver
‘NMDAccess’,
‘OLE DB Provider for Jet’,
‘Microsoft.Jet.OLEDB.4.0′,
‘\\gfkausad\ibd\Anja\New_Model_Data1.mdb’
EXEC sp_addlinkedsrvlogin ‘NMDAccess’, ‘false’
IF EXISTS(SELECT name FROM sysObjects WHERE name = ‘product_file’)
DROP TABLE product_file
SET @SQL_GET_TABLE = ‘SELECT * INTO product_file FROM NMDAccess…’+@GET_TABLE
EXEC (@SQL_GET_TABLE)
IF EXISTS(SELECT name FROM sysObjects WHERE name = ‘outputfile’)
DROP TABLE outputfile
SET @SQL=”
–SET @SQL1=”
SET @QRYSQL=”
DECLARE C1 CURSOR FOR
SELECT DISTINCT F.pgname,F.FQ
FROM NMDAccess…Feature_Question AS F INNER JOIN product_file ON CAST(F.pgname AS NVARCHAR(255)) = CAST(product_file.pgname AS NVARCHAR(255)) where F.[use] = 1
OPEN C1
FETCH C1 INTO @ID,@FEATURE
WHILE @@FETCH_STATUS=0
BEGIN
SET @QRYSQL = @QRYSQL+’,'+QUOTENAME(@FEATURE)
FETCH C1 INTO @ID,@FEATURE
END
CLOSE C1
DEALLOCATE C1
SET @SQL=’SELECT PGNAME,PRODUCTGROUPID,ITEMID,CREATEDBY,CREATEDWHEN,LASTCHANGEDBY,LASTCHANGEDWHEN,CR.STATUS’+@QRYSQL+’ INTO outputfile FROM product_file INNER JOIN NMDAccess…Created_Released AS CR ON product_file.ITEMID=CR.ITEM_ID’
–SELECT @sql
EXEC(@SQL)
–
IF EXISTS(SELECT * FROM master..sysservers
WHERE srvname = ‘NMDAccess’)
EXEC sp_dropserver ‘NMDAccess’, ‘droplogins’
Hai Pinal ,
Hope Fine . i would like to know about how to store image manually in sql server 2008 . There is a way we can store
through Application ,can we directly store like values ..is there a way or we can’t store in sql server ? Anyway i’ll be waiting for your answer . Plz try to post it on the blog . Thanking you very much .
Regards ,
Shaik .
Read about openrowset function in SQL Server help file
I need to delete ophan records in the sys.objects system table of Sql Server 2008.
I have tried DAC but still error pops up “Ad hoc updates to
system catalogs are not allowed.”
I tried googling but cant find any appropriate answer specific for Sql Serer 2008.
Please help.
Thanking YOU IN ADVANCE.
RegaRDS,
IKhan
It is better you find out the orphan objects and drop them. The sys.objects will get automatically updated
Hi,
I have 2 RAID 1 groups disks (C and E). ( I have only 4 disks in my windows 2008 svr)
Can I place my sql log files along with windows OS and SQL Server binaries in disk C and place the user sql data files in E ?
or should I place both my sql log files and user sql data files in E and leave windows OS and SQL Server binaries in C.
Which option will give me good performance?
Thank you in advance,
Jeyakumar
Hi Pinal,
My sql server 2005 keeps producing sqldumpxxxx.txt log files and it is mentioned that user initiated this log. the rest of the message is some HEX codes. I couldn’t find the reason and I do not want to restart sql service which solved the same issue temporarily.
My google searches seems not working the rasn for this issue. approx. in 6 hours my free space is going to end up due to this log file grow and I neet to watch it non stop.
do you have any idea?
thanks
Alaatin
Hi Pinal
We are developing a web application. I want to have softe delete in my project. I dont want to physical delete any record from the database, and use it as an recovery tool for detching data.
Is it fesiable to maintain a flag Isdeleted with delete timestamp in the same Table.
Is there any other method to have such methodlogy to maintain such records without any physical deletion.
Either you can set a flag that is true for each delete or have a table that collects all deleted records
hi,
i need to write a query given input is database name,
i need to retrieve all tables last 10% of rows from the given database .
and also in need output in the format of
insert into tablename(column names’) values(value);
How are you going to determine last 10% rows?
Do you have any unique id or datetime column?
Also, what is the purpose of doing this?
hi,
Purpose is i have a database of 80Gb now i want to create a tempdatabase with all tables and stored procedures and all entities,
But i don’t want to copy all records from database , i want only 10 0r 5 % of the data and move the data to new temp db i don’t have any relationships between tables also.
so no issue of relationship….also..
@Nagarjuna.
Two Questions:
1. So you need script to insert data from one database to another database for all tables. Does this means, that you managed to get a blank database with empty tables and stored procedures OR you want us to write script to create database and then create tables and stored procedure.
2. You want latest 10% records in each table or just 10% record no matter if they are latest or old. If you want latest 10%, then is there any datetime column in every table by which I can identify where the record was inserted.
If you just want to generate script to insert any 10% of data in any format you want, its simple. Please answer above questions, should not be hard to write SQL, Please also mention which version of SQL are you using, 2000/2005/2008.
~IM.
Hi,
i can create bank database with all tables .. no issues.. but i want script to insert last 100 records or (suppose if i give 200 i should be able to retrieve last 200 records and so on )
i don’t have date time columns in all tables and also i don’t have id as primary key in all tables (i.e in some tables name is primary key)
sql server version 2005 & 2008
Thanks
If you have primary key or unique key
select top 100 * from table
order by unique_key desc
hello all,
i have ms visual studio 2008 and sql 2005 but database connectivity is working properly in windows form with (vb.net and c#) but not with asp.net what to do….
Make sure the connection string is correct
Did you get any error?
Hi Pinal,
Recently moved to a new position where we currently have 250 SQL Servers. To register these servers in management Studio manually would be very time consuming.
Can i register all my servers in management studio automatically through Powershell script or any other mean??
I can get the list of SQL Server instances into a table or text file.
Does anyone have a script that can do this?
I Would be very grateful.
versions are SQL Servers are 2000, 2005 and few of thems are on 2008.
Thanks in Advance,
A. Singh
Which one is gives better performance in large database , Max() or Rank()?
Which one is better to use?
It depends on what you want to get. Can you give more informations?
I am using execute process task inside the for each loop.
I have passing variables values into execute process task.
My question is that
when execute process task does not find file at given location (actualy i have deleted file from location which execute process task looking) it’s gives sucessvalues “1″ with error.
I don’t want to see this error, so someone help me to get out from execute process task and go for next value at foreach loop.
Actualy if you help me out to store unfound file entry into dirent location or table then that make me more happy
thanks
Hi Raju,
You can use “Script Task” to check file is exists or not. If file is not exists, you can insert record in database from “Script task” too.
If file is not exists, then you can set SSIS variable and make Conditional flow to how to proceed.
Thanks,
Tejas
SQLYoga.com
Dear Pinal Dave
I am going to work on WEB application which will use stored procedures for running any type of transactions (Simple to complex). I read the below mentioned artilce:
http://msdn.microsoft.com/en-us/library/ms179296.aspx
And found it will be useful to me as it handles Deadlocks,Exceptions. THrough out my little career i found your blog really helpful. Can You tell me :
1_ A better way to use stored procedures for complex transactions or do you recommend me the format used in above link.
2_ Do I need to handle Isolation levels in transactions in sp at db level as we do in C# code if yes How and if No Why?
Waiting for your reply .
I got a SSIS package which loads the loadfiles placed in filesystem c:\Client Data\LoadFiles\ to SQL Server table TransactionData. I would like to automate the package such that as soon as client places loadfile in said folder the very next second package should be triggered for execution. I dont need anyone to explicitly execute package as file is available.
Any approach would be appreciated
Why dont you schedule the package that periodically checks the new files and run?
Madhi,
I’m just looking for answers from Windows event front. How to catch event Windows generate as soon as file gets created / made available in file system.
Well, for job scheduling, I believe this is a costlier in terms of maintenance overhead.
So looking for some easy methods say FileWatcher etc.
Alternatively you can create an application that checks the new file and schedule it in Windows scheduler. Or look for some file watcher tools
Whats wrong with ISNUMERIC() ? It still returns 1 for non-numeric characters when I execute the below code snippet :
declare @t table ( id int, charac varchar(1), is_numeric bit)
declare @count int, @char varchar(100)
select @count = 1
while(@count<256)
begin
select @char = char(@count)
insert into @t (id, charac,is_numeric)
select @count, char(@count), isnumeric(char(@count))
set @count = @count + 1
end
select * from @t
where is_numeric = 1
More informations on isnumeric
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx
hi,
i need to do performace tuning but hear profiler is not allowed to use, so how to check performance in sql server.
i new to this…
any exports …
advance thanks…
i need to check performance tunning , but hear profiler is not allowed, what are the ways i can check ..
help me out.
any exports answer me plz
The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects
I wanted to implement data encryption/
decryption in sql server 2005 using Asymmetric keys. Can anyone please help to provide the steps. Column contains XML data.
Regards
Ashish Sinha
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID
Can anyone tell me nvarchar max limit???
Hi Pinal Dave
I have one table with a ID and one another table with foriegn key ID
means
ID KHNo,KhataNo SNO,ID,NAME
1 100 3 1 1 test
1 200 3 2 1 test1
2 210 4 3 2 testing1
2 220 4 4 2 testing2
3 240 5
i want output
ID,KHNO,NAME
1 100 test
2 200 test1
3 210 testing1
4 220 testing2
5 240 NULL
Thanks
Hi Pinal Dave
I have one table with a ID and one another table with foriegn key ID
means
Ist Table
ID KHNo,KhataNo
1 100 3
1 200 3
2 210 4
2 220 4
3 240 5
2nd table
SNO,ID,NAME
1 1 test
2 1 test1
3 2 testing1
4 2 testing2
i want output
ID,KHNO,NAME
1 100 test
2 200 test1
3 210 testing1
4 220 testing2
5 240 NULL
Hi people,
My issue is more of a SQL issue than ASP.NET.
I am trying to store a 3digit integer value with a leading Zeros in front such as 001
i have a company table , which auto increments the id as 001, 002, 003…and so on…
In the company table i also have a Empoyee ..
Which has five employee names,
eg: aa, bb..
So when creating the company..
The user types in the data and select a country eg: aa,
The Id should be I001,
The next record if he chooses bb,,, then it will be
A002 … A003, I004..and so on…till A999(as the last record).
Can anyone help me how to implement this?..
Ur help is much appreciated…
Thanks in advance..