Contact Me – Archive 3

About Pinal Dave

Pinal Dave is a common man who enjoys listening music, reading books, travelling places, watching movies and writing blogs. Pinal has over 6 years industrial experience gained through his work within the IT industry and SQL Server Community.

He is a highly respected and leading figure in Indian IT field. He is recognized through his work as founder of the highly useful SQL Server Site SQLAuthority.com. Microsoft has presented him SQL – MVP award for his extraordinary contribution as a SQL Server Expert.

Contact Pinal Dave

Email is always welcome, whether you’re interested in talking about projects, SQL Server 2008 and SQL Server 2005 technologies, NET(C#), ColdFusion, Web Architecture, have taken an online tutorial class at SQLAuthority.com, or have an event where you believe Pinal Dave would be a great speaker or panelist.

If you want Pinal Dave to review your product, book, website or code, please do not hesitate to contact him. Or, feel free to say “hi” anytime. All comments are welcome.

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.

pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com

About these ads

550 thoughts on “Contact Me – Archive 3

  1. Hello pinal sir,
    I am your frequent reader of your SQL tips & tricks.
    sir i have completed my BTech IT degree in 2008..Sir i want to do some certification in Microsoft SQL Server. i need some more tips abt exams related to SQL.i want to become as like your successful career.Kindly direct me sir.

    Thanks n thanks sir..

  2. Hi Pinal,
    Help needed
    I was asked this in an interview, I have only 1 column in a table and i need to break it into two columns..

    Plz give your ideas in this

    Thanks in advance
    Jayanth

  3. Hello sir,

    I have MCA degree and now i want to make my career in Sql server administration ( although i have a very good knowledge in C# and asp.net). Please tell me from where to do the related certification. I am sure you must be knowing some good institute/company from where to take these courses…

  4. Hi,
    iam in a fix pls help me;((
    i have 2 tables- user_table(id,name) and name_master(name).
    i want to transfer the data from name_master to user_table.
    Now id is the primary key in user_table and name_master table doesnt ahve an id field.
    I want to enter name from name_master table and id will be any integer number that keeps incrementing at run time.
    how to insert both values (id, name from name_master)at the same time to user_table

    • Hi Jeena,
      1. Set ID as Identity property through SSMS (SQL Server Management Studio) as Yes and Seed and Increment values as 1,1.
      2. Below is work around as an example.
      create table #user_table(id int,name varchar(100))
      go
      create table #name_master(name varchar(100))
      go
      insert into #name_master select ‘sa’ union select ‘b’
      go
      declare @id int
      select @id = isnull(max(id), 0) from #user_table
      insert into #user_table (id, name)
      select @id + row_number() over(order by name), name
      from #name_master

      select * from #user_table

      Replace the temporary tables with actual names

  5. Hi,

    I am developing a education portal. We are taking tests of the student whoever logs in for the first time and then save the details of the student and the related test in a table. This tests are useful in analysis for giving practice sheets for his learning purpose. So according to his sore we would give him practice sheets would be given which will also be stored in the DB. So should I store the practice sheets in the same DB or create new DBs or new table for each distinct student. Please send your contact no on my mail ID for my further reference.

    Thanking You.

  6. Hi Pinal
    I have a table Album Details
    There are some fields like UserId, AlbumId, DATE

    A user can uplaod many Album

    I want to select Top 100 Album. But If a user posted more than one album them his last posted Album should come.
    How it is possibe?

  7. Hi Pinal,

    I am very impressed with your blog and knowledge.
    I am Software devloper and living at gandhinagar Sector 3-C.
    I would like to meet you personally if you desire.

    Thanks,
    Amit Makwana

  8. Hi Pinal,

    This is Eswari.S from chennai\india….Many times i crossed your articles….Now i want to learn sqlserver2005….can you please help me out, that will very useful for my career future.

    I am Waiting for your reply.

    Thanks and Regards,
    Eswari.S
    pseswari@yahoo.co.in

  9. Dear Pinal ,

    I have one problem regarding trigger it is new for me

    i have two data base with same table on both side only name will be different

    1)Head
    2)SITE

    when i insert one record in Heads–> Item table then that record
    is also inserted into SITE –>Item Table

    and how should i pass the parameters to insert statement
    which iam writin inbetween BEGIN ans END section of trigger

    OR if any article is there regarding triggers (May be i dont got it ) for reference

    Thanks

  10. Hi Dave,
    Its Swati from mumbai
    I M in a need of job in urgent basis.
    I am a software developer working a company
    if you have any openinging for sql dba pls reply soooon
    thanks a ton

    swati

  11. Hello Pinal,

    This is a slightly strange question might be.

    But One of my friend has written storedprocedure with a

    couple of Update and Insert statements.It seems to return a

    value of 0.This seems quite natural but is there a way to

    suppress this behaviour or alter it in any manner.Please help.

    Since he doesnot want any return value.

    • Sirish,
      We can make out the actual reason unless we know what business logic / stored proc is doing ? Paste the proc if possible for further investigation

  12. hi…
    i am a programmer from indonesia
    your infomation very very very very good………………

    thank a lot

    ;-) pokoknya keren banget deh…

  13. Hi

    I am maintaining Simens Hospital Information system for a leading Medical college hospital in India. They are using lot of temporary tables for reporting the output after the insertion to the table . I would like to know wheather it will affect the peformance. Another thing they are using different date format in different tables like number in one table, date format in another and string. expects your reply.

    Regards

    Pradeep

  14. Hi pinaldeve,

    Just now I see ur sqlauthorith.com very useful for me. Recently I am learning sql server 2005 (DBA). Can u tell d/w sql server 2000 and 2005 and features of 2005.

    Am waiting for ur reply.

    Thank You,
    R.Sivagurunadhan

  15. Hi pinal,

    Thought you might be able to help me out.

    Is there a way to copy results of a union of three queries into temp table.

    It doesn’t work when I try. This is what I tried.

    select * into #temptable from
    ( query 1
    union
    query2
    union
    query3)

    I get a syntax error saying – Incorrect syntax near ‘)’.

    any other suggestions welcome -:)

    cheers,
    RK

  16. hi pinal sir
    i have a problem in Sql server 2005 plz solve my problem
    as soon as Thanks

    Problem :
    How to store an images in sql server data base table( directly like insert into ..) using DML Command

  17. Hi Pinal,

    I have a problem. I have a 80thousands records table and I have to export it to an XML file. The problem is that the data must be in a defined structure, I’m using FOR XML EXPLICIT but how can I export the data to an XML file?

    Regards

  18. Hi Pinal,

    I have installed SQL Server 2008 Express version on my machine and just baffled not to see “SQL Server Agent” in Management Studio. I didn’t stopped investigating though…., I checked services.msc and found the SQL Server Agent Service in stopped state and was end up with error while starting…

    One new thing I have noticed was the “Policy Management” that has been newly added in katmai, is there any link between PM & SS Agent

    Thanks

  19. Hello Pinal
    I am working on the project for the HR team.. U can say, We are going to build the Resume bank for them.. And i have the responsibilty to handle complete Database… our main objective is to make the search more faster and efficient according the keyword (eg. java AND .net)..
    Sir I would like to know about .. What is the best way to store the complete Resume of 1 person in the SQL Server Feild.. Should i go for nchar,nvarchar,image or something else.. please suggest me
    what will be the best way to store the resume so that search will be faster and performance will not be affected.

    Thanks

  20. Hi Pinal,
    since last few months i am reading your blog. I am interested in it. I have 1 question…
    There are two tables.. Customer with columns custid,custname,custadd and another table Product with columns prodid,prodname,prodsupp.
    Now i want to know from sales table which is Sales with
    custid,orderid,prodid,totalqty,totalamount.
    I want to know know how many customers have ordered 1 product how many have ordered 2 products and how many have ordered 3 products and so on……
    Please let me kow ASAP.
    Thanks in advance.

  21. Hey, Dave. I am going to starting blogging my TSQL tricks and snippets of codes and opened a blog on WordPress, but I am trying to find the best way to display my code.

    How do you get your SQL to not lost its color coding?

  22. @Someone I dont remember

    Yesterday I was reading at article in SQLAuthority , person asked script to find out jobs that are scheduled today.

    here is the script.

    use msdb
    select A.name Job_Name, ‘Will be running today at ‘+substring( convert(varchar(10), case when substring (convert(varchar(10),next_run_time) , 1 ,2) > 12 then substring (convert(varchar(10),next_run_time) , 1 ,2) -12 else substring (convert(varchar(10),next_run_time) , 1 ,2) end),1,2)+':’+substring (convert(varchar(10), next_run_time ),3,2)+':’+substring (convert(varchar(10), next_run_time ),5,2) ‘Scheduled At’
    from sysjobs A ,sysjobschedules B
    where A.job_id = B.job_id
    and substring(convert(varchar(10),next_run_date) , 5,2) +’/’+
    substring(convert(varchar(10),next_run_date) , 7,2) +’/’+
    substring(convert(varchar(10),next_run_date),1,4) = convert(varchar(10),getdate(),101)
    — and ( substring( convert(varchar(10), case when substring (convert(varchar(10),next_run_time) , 1 ,2) > 12 then substring (convert(varchar(10),next_run_time) , 1 ,2) -12 else substring (convert(varchar(10),next_run_time) , 1 ,2) end),1,2)+':’+substring (convert(varchar(10), next_run_time ),3,2)+':’+substring (convert(varchar(10), next_run_time ),5,2)) > substring (convert( varchar(30) , getdate(),9),13,7)

    Thanks and sorry for confusion.

    Regards
    IM.

    • Imran this is good comment.I have modified your script a bit as it was not giving accurate time for schedule job if the time was less than 12 hours.

      Again, Good Script.

      Regards,
      Pinal Dave

  23. Hi,Pinal

    I am a Jr. DBA Working in SQL SERVER 2000.Please Give me a All Information of MCDBA Exams.

    Thanks in Advance,

    Regards,
    Fazal Vahora,

  24. Sorry Sir but a link in your web site at ur home page is placed incorrectly………i give u the location and the link given in ur site,,may be i m wrong but take a look at it?

    SQL SERVER – Interesting Interview Questions – Revisited
    December 17, 2008 by pinaldave

    SQL SERVER – Interesting Interview Questions At that place the link goes onn,,,,

    http://http//blog.sqlauthority.com/2008/12/07/sql-server-interesting-interview-questions/

    sorry if i said some thing wrong,,,
    thanks///

  25. Hi Pinal,
    i need to use statement inside a stored procedure in sql 2005.Is there any way to accomplish this task?
    Or can you advise me on how to create a global stored procedure which will be commonly accessed by multiple databases on same server?

    Thanks ,

    Andy…………….

  26. @anurudha deshpande.

    I dont know what you mean by first statement.

    By Answer to your second question,

    Yes, it is possible to have a stored procedure that can be used in multiple databases.

    Create that stored procedure in master database. Name of this stored procedure should start with sp_ …..

    When you call this stored procedure from any database it will execute.

    Based on the actions the stored procedure performs, you might want to script that way. Like if database name is needed in that stored procedure, then you have to pass database name as parameter.

    If you need any help, please leave a comment I will show you an example.

    Regards
    IM.

  27. Hi Dave
    How do you do. well can u help me in my problem that how can i find tables related to each other using primary key & foreign keys as their are no of other tables in the database
    i am new to sql Database’s as for my first chance to the sql as a database developer and i need to combine all the tables as for my requirements as i need to update users for their reports for which i need to check the table name as per requirements via command
    select * from information_schema.tables
    where table_name like ‘%employee%’
    please help me with my query to find all the tables that are interrelated to each other

    Thanks & Regards

  28. Pingback: SQL SERVER - Find Next Running Time of Scheduled Job Using T-SQL Journey to SQL Authority with Pinal Dave

  29. Hi
    i have a Task of sending mails from Sql Server…;) which i had done using SMTP. but want to Send E-mail from SQL Server using Exchange Server there i dont have SMTP.some where while googling i have read that you dont have SMTP on Exchange server as it is more advance then this SMTP. So is it possible to send mail using Exchange Server,

    If yes then please let me know if any artical is there or not and please give me some Configuration regarding this.

    and Do let me know that How to do it..!!?

    Sir, please help me out with this.

    Thanks in Advance

    Parth Rawal

  30. Dear Dave,

    How do you partition a large table with parents and child refrential integrity with 4 other tables. In some cases the table in quetion is child and in some cases it is the parent of 4 other tables. How can I move the table into partitions?

    Thanks,

  31. hi pinal,

    how to read sql server password.
    the scenorio is our client changing the sqlserver password monthly once. now we hardcoded the username and password in project for connection.(VB using). each time we have to change the coding and release exe. so avoid this from where i can read sqlserver password and pass as parameter to project
    please help me

    Hariharan

  32. Hi
    Pinal

    In My database I have some 40000 rows now i need to chage the descreption for some products

    I want to update the data on where condition
    Plz help how to update the data
    Basing on Where condition
    Ex
    If ProdcatID =1 then I want chage the descreption

  33. Hi.. Is there any way to insert a bulk data (say 1 billion rows) from 1 table to another table without involving the Transaction log. The issue here is we dont have enough space for the log files and log always slows down the process too. The operation we are carrying out is not of that high risk, hence we can forego the transaction logs. Please comment.

  34. Hi,
    I Implemented fulltext indexing on seven columns in a table.I used contains keyword.Initially it worked fine but when records in the table started increasing search becomes slow.
    can you please suggest me how to improve the search performance. I used sql server 2005. please help me

  35. Hello Pinal,

    I have a stored procedure which is backing up all databases but for some unknown reason its creating two backup sets and appending the second set to first one so for database of 5GB I am getting a backup file of 10GB I am completely lost on this n need quick help. Thanks

    THis is extract from the Backup Log

    Starting Full Database Backup of Database: WSS_Content @ 12/19/2008 21:00:51 [SQLSTATE 01000]

    10 percent processed. [SQLSTATE 01000]

    20 percent processed. [SQLSTATE 01000]

    30 percent processed. [SQLSTATE 01000]

    40 percent processed. [SQLSTATE 01000]

    50 percent processed. [SQLSTATE 01000]

    60 percent processed. [SQLSTATE 01000]

    70 percent processed. [SQLSTATE 01000]

    80 percent processed. [SQLSTATE 01000]

    90 percent processed. [SQLSTATE 01000]

    Processed 673440 pages for database ‘WSS_Content’, file ‘WSS_Content’ on file 1. [SQLSTATE 01000]

    100 percent processed. [SQLSTATE 01000]

    Processed 7 pages for database ‘WSS_Content’, file ‘WSS_Content_log’ on file 1. [SQLSTATE 01000]

    BACKUP DATABASE successfully processed 673447 pages in 103.021 seconds (53.550 MB/sec). [SQLSTATE 01000]

    Processed 673440 pages for database ‘WSS_Content’, file ‘WSS_Content’ on file 2. [SQLSTATE 01000]

    Processed 1 pages for database ‘WSS_Content’, file ‘WSS_Content_log’ on file 2. [SQLSTATE 01000]

    BACKUP DATABASE successfully processed 673441 pages in 95.846 seconds (57.559 MB/sec). [SQLSTATE 01000]

    Verifying Full Database Backup of Database WSS_Content [SQLSTATE 01000]

    The backup set on file 2 is valid. [SQLSTATE 01000]

    Deleting backups older than 1 days from the “E:\MSSQL.1\MSSQL\BACKUP\WSS_Content” Folder [SQLSTATE 01000]

    Completed Full Database Backup of Database: WSS_Content @ 12/19/2008 21:00:51 [SQLSTATE 01000]

  36. Hello,

    I’m planning to write a schedule a script for Tlob backup in SQL server 2005. i have to do following 4 steps in the script

    1. Dynamically detect FULL and Bulk-logged recovery modes databases in the instance and backup the database transaction log for those databases only.

    2. If a FULL or Bulk-logged recovery mode database is added it will automatically be included the next time the job runs.

    3.If a database is changed from Full to Simple recovery mode it will automatically be excluded the next time the job runs.

    4.The job should skip databases with the following states: loading, pre-recovery, recovering, not recovered, offline, read only, emergency mode

    5.As a last step the job should delete transaction log backups older than 3 days on disk

    please help me if you have any

  37. Hello,

    I’m planning to write a schedule a script for Tlob backup in SQL server 2005. i have to do following 4 steps in the script

    1. Dynamically detect FULL and Bulk-logged recovery modes databases in the instance and backup the database transaction log for those databases only.

    2. If a FULL or Bulk-logged recovery mode database is added it will automatically be included the next time the job runs.

    3.If a database is changed from Full to Simple recovery mode it will automatically be excluded the next time the job runs.

    4.The job should skip databases with the following states: loading, pre-recovery, recovering, not recovered, offline, read only, emergency mode

    5.As a last step the job should delete transaction log backups older than 3 days on disk

    please suggest

  38. Hi Pinal,

    I had for 2 days this problem and I tried almost everything.

    I have SQL 2005 with the following info:
    Microsoft SQL Server Management Studio 9.00.4028.00
    Microsoft Analysis Services Client Tools 2005.090.4028.00
    Microsoft Data Access Components (MDAC 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
    Microsoft MSXML 2.6 3.0 5.0 6.0
    Microsoft Internet Explorer 7.0.5730.13
    Microsoft .NET Framework 2.0.50727.42
    Operating System 5.1.2600

    After installing MySQL which uses the port 3306 (and is working fine), now my MS SQL 2005 is not connecting and I can not Start the service SQL Server.

    While trying to connect to my SQL server by running the Sever Management Studio it is giving me:
    ….SQL Server does not allow remote connections …Named Pipes Provider, error: 40 – Could not connect; Microsoft SQL Server Error: 2

    I tried all posibilities, runing Local connections only, and/or local and remote. I use this server only for my own use, not shared and not used by remote.

    Please advise,
    Abe

  39. Hi

    i want to import data from excel to sql server 2005.I am dong it by using DTSWizard, butthe problem with DTSWizard is that i am lossing the data in transferring.
    What is the best way to copy data(without going into the detail of data type) across?

    I am strugling to od so and my application is stuck.Can anybody help?

  40. Hi Pinal,

    I am using sql 2005.

    I want to search particular word in a column. e.g

    Suppose below table

    Product_Id Product_name

    101 New bath soap 200g
    102 Old bath soap 100g
    103 Bath soap 125g new
    104 Bath soap 125g old
    105 New bath soap 200g gold
    106 New bath soap 125g Rose

    Now i want to search with keyword like “bath” ,”200g” ,”125g”, “soap” etc.

    Please advice me.

    Thanks in advance

  41. Hi,
    i cannot past into the microsofot office 2003 .
    i can only past into an text .txt and all other applications, like word excel .ect.
    only i cannot when i want to past in the microsoft office outlook .
    thx if you have any resolve for this problem ..ca

    roger.

  42. m using vb.net and sql server 2000 with windows authentication mod on XP. m getting error while connection from client “Cannot open database request in login ‘accounts’. Login fails. Login failed for user ‘Server\Guest’.”

    plz suggest me for vb.net and sql server 2000 which authentication i shd use. how i can resolve the problem. my connection string is as under.

    cn.ConnectionString = “data Source=server;database=accounts;Integrated Security=true;user=sa;password=;Connect Timeout=30;”

  43. Hello Pinal,

    My DB server (HP prolaintG5)faced harddisk problem. OS got corrupted. database was in suspect mode and server rebooted. Cannot access database.

    By any chance i can recover this database?

    Please reply.

    Thanks in advance.
    Kinnari.

  44. Sorry Pinal,

    I forgot to mention error in my previous mail that i occur when i do anything to recover the db

    follwoing error:
    I/O error (bad page ID) detected during read at offset 0x00000000030000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\IT_Data.MDF’.

    [db server faced HDD problem. OS got corrupted. server rebooted when db was in suspect mode]

    Thanks.
    Kinnari.

  45. Pinal,

    I have a query.How to fetch last two years from a current year. for instance,current year is 2008 i need to fetch 2007 and 2006.
    Looking forward to hear from you.

    Regards
    Ram

  46. hi i downloaded your file Data Synchronization Stored Procedures and views – Part II.sql. I search your blog to read more about how to implement this but it seems like you removed it. I would like to learn how to synchronize two database without having to buy sql server. I mean, just use the express edition to synchronize two database. can i do that?

  47. Hello

    Mr . Pinal Dave first of let me Congratulate u for getting married ‘ wish u happy married life ‘. i’ll Come to point now
    Mr pinal , some times you are not taking care about some comments/Queries who eagerly wait for ur solutions . you are avoiding them , what is the reason for it tell me right away ..what is the mistake they ‘ve done .

    some time before Ritesh , Paul and shaik they asks the question , but u didn’t give the answer on the blog. plz let me know.

    Thank you very much.

    • Dear John,

      Thank you very much for your wishes.

      I always take time to answer all the question asked on this blog. There are plenty of the comments I receive every day. More than 300 comments every day. I publish all of them, as it creates good community between readers. Many time smart readers like Imran and others right away answers other readers and some time good communication is on blog comments itself.

      I reply most of the answers either by email or commenting on blog itself. I do get many many calls every day and I take time to answer them as well.

      I have visited many readers work place to solve their problem as well.

      Let me know if you want me to answer any question and I will reply in 48 hours.

      Kind Regards,
      Pinal Dave ( http://blog.sqlauthority.com )

  48. Hi Ram,

    You can use this query to get the years as you want:

    SELECT YEAR(GetDate()) As CurrentYear,
    YEAR(DATEADD(yy,-1,GetDate())) As LastYear,
    YEAR(DATEADD(yy,-2,GetDate())) As LastOfLastYear

    Thanks,

    Tejas

  49. Hi pinal,

    I have to create a Stored proc which selects data from Two tables which lies on two different server.

    I need to do comparision sort of work..

    how this can be possible.,.??

    Or either ,
    can i manage this from my code behind side..while working in Asp.net ?/

  50. i installed sql server 2005 in my leptop (DELL 1525)
    but while installing on my local machin i dont know what to write in domain
    so i let it remain blank.But know i getting error microsoft
    sql 18456 while connecting to the batabase engin
    im loging through window authentication
    please help me
    bcz im installing first time
    ill wating for u r reply
    thanks

  51. hello,

    i am woking on such projects which needs to know how many times table altered and which field has been changed /modify. I tried using following queries

    select top 10 *
    from ::fn_dblog(null, null)
    where Operation = ‘LOP_MODIFY_COLUMNS’

    but it does not retrive any thing which can be useful to get such information.
    I can not add any other table like audit manager which tracks on such operation. And if I add such table then also my previous changes i could not get.

    Plz help me thanks in advance.

    :-)

  52. Hello pinal,

    I just wanted to know one resolution from your side. I have tried to change the SQL-Server Name by below commands
    @@Servername — to get the server name

    sp_dropserver ‘oldservername’- delete the old server name
    sp_addserver ‘newservername’ ,local – add a new server

    net stop mssqlserver — stop the sql serever
    net start mssqlserver — start the sql server

    net start sqlserveragent – start the sql agent

    When I check in query analyser by “Select @@Servername” command it already changed the server name with new name but it does not change the SQL Server Management Studio Object Explorer with the new name. How do I change the name here???

    Here it is displaying the old name of sql server?? Please let me know how do i Change??

    Thanks
    Himanshu

  53. Hi Pinal,
    Is it possible to update procedures or functions on sqlserver database using a batch file. for example, i have 100 procedures in a folder (proc1.sql, proc2.sql ….. proc100.sql) and i need to update all these procedures in test database using only a single batch file. Is this possible?

    –Irfon

  54. Hi Pinal,
    I got the solution for what i had asked earlier. thanks anyway

    set source=C:\procedures\

    for %%a in (“%source%/*.sql”) do (

    echo %%a
    sqlcmd -S compname -U username -P password -i “%%a”

    )

    pause

  55. Hello Sir,

    I am a beginner in SQL 2005 and would like you to help me out in the following issue.

    I have two columns both of datatype ‘datetime’ as start time and end time. I want the time difference from the two columns and then add the hours or time what I have got.

    I have already got the time difference from below query

    select convert(varchar(5),schetm-schstm,114) from psschtbl

    here, schetm -> End time
    schstm -> Start time
    psschtbl -> Table name

    Now how do i get the add the time i have got from the above query.

    Please Help.

  56. I have a database named db_ERP . In that database number of tables and SPs are there.In that database i modified field names of some table columns.But i have a lot of SPs using those table fields.I need a query for getting the Sps which are now incorrect.

  57. Himanshu
    Hello pinal,

    I just wanted to know one resolution from your side. I have tried to change the SQL-Server Name by below commands
    @@Servername — to get the server name

    sp_dropserver ‘oldservername’- delete the old server name
    sp_addserver ‘newservername’ ,local – add a new server

    net stop mssqlserver — stop the sql serever
    net start mssqlserver — start the sql server

    net start sqlserveragent – start the sql agent

    When I check in query analyser by “Select @@Servername” command it already changed the server name with new name but it does not change the SQL Server Management Studio Object Explorer with the new name. How do I change the name here???

    Here it is displaying the old name of sql server?? Please let me know how do i Change??

    Thanks
    Himanshu

  58. Dear sir

    I saw your web site and exicited to get some solution so sending mail.
    For the last few weeks I have been suffering with the problems of SQL Host_ID().
    This query (SELECT Host_ID()) is generating Unique host id on each Exe on Windows Application
    Where as it’s not generating from Asp.Net C# on each user Login. Every time it return same Host_Id on different browser. I also Configured in IIS Pooling recycle worker process but it returns different Host_ID on each request which is also not good.

    I want Onces Request should create new host_ID with in that browser and until and unless the browser is closed the same host_ID should be remain at last and If different browser is opens, it’s calculated next request and again New Host_ID should be created.

    Onces I found that This all happening due to the Pooling method. So If you have any solutions then plz forward me..

    Thank you

    Rupendra nath Shrestha
    Software Programmer

  59. Hi sir,
    It’s Nicework.
    I’m a strating learner. what way i can learn.
    Whats is the main purpose of the SQL Server 2005 backward compatibility ?

    plz send to me, all the interview questions.

    ThankQ Sir.

  60. Hello Pinal.

    I have a schema.csv file. Is there a way to create a blank database using that file?

    I am just starting off with 2005 and am still in the process of learning.

    Thanks and Regards,
    Vijay.

  61. hi Pinal

    Can you please explain why indexes having avg_fragmentation_in_percent > 98 is not changing the value even after reindexing or rebuilding.
    Came to read that small tables will not affected by rebulding or reindexing

    So I am just mentioning some more details like page_counts below.
    name avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count
    xxxx1 98.8538681948424 346 7.5606936416185 2616
    xxxx2 98.6381322957199 511 6.5812133072407 3363
    xxxx3 84.3016069221261 684 9.25292397660819 6329

    All the advice and tips will be greatly appreciated.

  62. Hai, Pinal
    Nice to see your great articles.
    I’m new in SQL Server, so Iwould ask how to get value from 2 column to be 1 column.
    I used “select Column A + ‘,’ + space(1) + column 2″
    the problem is when there is no value in colum2.
    How to split/delete string ‘,’ if the value of column 2 is null

  63. How can i delete the MDF and LDF file from c: Drive.It shows a error messge “Cannot Delete tempdb2:It is being used by another person or program “….
    Sir plz help me out to resolve this problem.

    Thanks
    Abhishek

  64. Hi Pinal,

    I have to pull the records from one server’s table to another server’s table.
    I am using SSIS package to do this.
    But the problem is one of the column is of Datatype “SQL_Variant”.
    Because of that I am getting this below warnings..
    . Warning 1 Validation warning. DFT-ROW_OpenAccessor: {8872BE20-F843-4D47-A2A1-D6364E528FA8}: The input “OLE DB Destination Input” (29) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead. OpenAgreementRefresh.dtsx 0 0
    ..how should i avoid them!!

  65. Hello,
    I need to do a data backup (complete backup means records with table structure) of ONE PARTICULAR Table in SQL Server 2005.
    Is it possible? if yes,please explain the method.

    Thanks & Regards,
    Ravinder

  66. How to get the last part of a stirng after the last occurence of the underscore
    eg: End of Specific Month_9_LatestDate

    I need to get just the LatestDate from the above string. I have several other different strings of same type and I need to get the last part of the string after the last underscore.

    Any help is appreciated. Thanks in Advance.

  67. Hi Pinal,

    I’m uploading data from a flat file of 2 lakhs records to a table also the table contains more fields than in the flat file..

    Can you please suggest the best way to do this …

    Thanks ,
    Bhanu Prasad M

  68. Hello Pinal,

    I am working on an application and learned something about SQL Server handling the default constraint.

    If you have a date column in a table which has a default of getdate() specified, I would expect that whenever a value is not given the default constraint will become effective and the current date is filled in.

    However when I tested, I learned that the value specified in the default value is only used when the column is omitted from the SQL statement. If the column is specified with a NULL then the default value is not used.

    example : insert into T1 (c1, c2) (c2 is a date column)
    values (1, null)
    insert into t1 (c1) values (2).

    only the 2nd row c2 has the default value, not the first row.

    Your thoughts?

    Regards,

    • That is the expected behavior
      You can give any valid value to a column that has default value. However default value will be considered only if you dont input value to it

  69. I was asked in an interview and want to have some feedback to see if better method exists to do this.

    We have 2 tables.
    table 1 – mgr (id, name)
    table 2 – emp(id, mgrID, name)
    where mgrID is foreign key from mgr table.

    Now ,
    how to find the name of manager who has maximum number of employees.

    My solution :

    select m.* from mgr m
    join (select top 1 m.id, count(m.id) as cnt
    From mgr m
    join emp e
    on m.id = e.mgrID
    group by m.id
    order by 2 desc) x
    on m.id = x.id

    is there a better solution ?
    Thanks
    Nanku

    • please check if this helps :
      table 1 – mgr (id, name)
      table 2 – emp(id, mgrID, name)

      select m.*, e.emp_count
      from mgr m
      inner join (select top 1 mgrid, count(id) as emp_count
      from emp
      group by mgrid
      order by 2 desc) as e
      on m.id = e.mgrid

  70. In my product there in one procedure in which we are calling three different procedures from three different databases used to fetch client’s ledger balance and printing data by using XML. If I give duration of six months in front end its give server time out error. ( Not scrip time out) If I run same procedure from back end get output within 30 seconds and also in range of 15 days report is working properly.

  71. Hi Pinal,

    Thought you might be able to help me out.

    Is there any possibility to get the execution plan Subtree cost of the Stored procedure by using any sql script or query.Actually i need to use this from front end and to display the subtree cost in my windows application.

  72. Hi,

    I always used to go through all your blogs, and its really helpfull.

    Can you please provide me some document or presentation on sql server 2005.

    Thanks

    Anil

  73. I was wondering if you have script ready to generate a script of all stored procs changed in 2 days. Since we have the names now, thanks to sql server2005.
    Can you help me with that..

  74. Hi Pinal
    I am using MS SQl Express 2005 Database where I have written own DTS Program to Transfer data from flat file to MS SQL 2005 Express. some reason , there are some tables not empty from DTS in MS SQL table. those tables have more than 300,000 records each.
    do you know the reason.
    Please help me
    Regards
    Sutha

  75. Hi pinal,
    I want to install the SQL Server 2005 free edition on my home PC. can you please tell me from where i will download my free copy of SQL Server 2005.
    Regards
    Saurav

  76. I want to grant permissions to the function GetFirstNames to the user “domain\xyz”

    GRANT EXECUTE ON [dbo].[GetFirstNames] TO [domain\xyz]

    How do i do it by passing as parameter

    Some thing like this

    GRANT EXECUTE ON [dbo].[GetFirstNames] TO [@user]

  77. Hi Pinal,

    I have just installed Sql Server 2008 server. When I am trying to create Maintenance plan from management studio, when I click on save butoon getting error catastropic failure message.
    How I can resolve this error, I am able to too every singal function like backup running query everything working fine.
    I am not DBA beginner only.

    Thanks,

  78. Hi pinalbhai,
    Congratulations ,

    This is maitry , deval’s cousin . Best wishes from me, Atul uncle & aunty for successful married life. I was searching some sql query on net to findout day starting from sunday from the hiredate while doing this i found ur site.I had done mca and i m dba beginner. U r knowledge in sql is tremendeous , i just come to know this site about ur educational success. Very few ppl from india reaches upto this stage. Now onwards whenever i will stuck up i will send u my queries .

    Cheers,
    Maitry.

  79. DECLARE @iExchangeID INT
    SET @iExchangeID = 1
    SELECT CASE WHEN @iExchangeId != 5 THEN CAST(1200.0001 AS DECIMAL(19, 2))
    ELSE CAST(1200.0001 AS DECIMAL(19, 4))
    END

    hello Pinal sir,
    Plz help

    here i am expecting an output as 12.00
    but i am getting 12.0000
    i dont want last 2 extra zeros

    now if i modify the query as follows then i am getting the desired output but i will have to change the second cast/ convert statement and it dosent solve my pupose…

    DECLARE @iExchangeID INT
    SET @iExchangeID = 1
    SELECT CASE WHEN @iExchangeId != 5 THEN CAST(1200.0001 AS DECIMAL(19, 2))
    ELSE CAST(1200.0001 AS DECIMAL(19, 2))
    END

    i want output as 12.00 when iExchangeID!=5
    and 12.0001 when iExchangeID=5

    what is the solution??????????????????

  80. Hi Aditya..

    I have modied your script a bit..

    Hope it helps you..

    DECLARE @iExchangeID INT
    DECLARE @myval decimal (10, 4)
    SET @iExchangeID = 1
    SET @myval = 1200.0001
    IF @iExchangeId != 5
    SELECT CAST(CAST(@myval AS varbinary(20)) AS DECIMAL(10, 2))
    ELSE SELECT CAST(CAST(@myval AS varbinary(20)) AS DECIMAL(19, 4))

    Shree

  81. Hi ,

    I am an ASP.net Developer , i am working on a Software that has a SaaS Module. In our software there are lots of instance where i need to insert multiple records into database. While inserting data into database i wanted to know, how can I increase the performace/ integrity of my appplication when then are multiple insert into database.

    1. I am Processing all the data Initially and then using transaction to insert data.
    2. I can pass all the data to 1 store procedure and in the store procedure i can have the transaction that will loop all the data and inset it into database.

    Please help me which is the better option or is there any option better then this.

    awaiting reply.

    • @Dasharath Yadav,

      I will go for the second option : I can pass all the data to 1 store procedure and in the store procedure i can have the transaction that will loop all the data and inset it into database.

      Performance of this will not be optimal, however, it will give you better control over error handling.

      If you are confident about handling error in processing all the data, I will suggest to go for option 1.

      Overall, as I am not sure how things will work, I will go Row by Row. This will prevent me for having any accidents.
      One more advantage it has is it will not lock complete table and put lock at row level, so your database table will be available when data is inserted.

      Regards,
      Pinal Dave ( http://blog.SQLAuthority.com )

  82. Hi
    I work with VisioalStudio6 and sql2005
    the connect with the sql i do with ADO

    I am excute ,a transactions that contain severl store-pocedures,from vb6 aplication.
    and i want to catch the error if done in transaction. if one procedure is succeeded and the other is fail i did not get response aboute the failer on.
    or if i excute sp from my vb6 application,if the sp
    contain 2 commands “isert” and “delet”.
    the “insert ” success and the “delet ” fail ,
    the fail MSG did not over to the vb6 .
    how can i catch the Messages

    Thank you in advance
    yacov

  83. HI Pinal,

    I wanted to create a directory and share this directory to users and also give permission to this directory.

    I was able to create the directory using

    EXEC master.dbo.xp_cmdshell ‘MD E:\Praveena’

    But now am not able to share this directory and also give permission to this directory using a sql server script.

    Can you please help me out in doing so
    thanks in advance,
    Praveena

  84. HI Pinal,

    I wanted to create a directory and share this directory to users and also give permission to this directory.

    I was able to create the directory using

    EXEC master.dbo.xp_cmdshell ‘MD E:\Praveena’

    But now am not able to share this directory and also give permission to this directory using a sql server script.

    Can you please help me out in doing so?

    thanks in advance,
    Praveena

  85. hai

    my name is pavan.i am trying job in sql dba
    can u tell guid me regarding that
    and i need some interview questions in sql dba

    regrads
    pavan

  86. Hi

    Thanks for the great site.

    I am trying to install SQLServer2005 on my Windows Vista Business(32Bit) OS system

    Is vista supports this or not? Can you please assist me in this ?

    thanks..
    venkat

  87. @Venkat,

    From what I believe and what I have read online, I can tell you , any Edition of SQL Server 2005 can be installed on any Edition of Vista.
    1. It will prompt you an error saying some components are missing, what you have to do, once you install SQL Server 2005, you have to apply SP2.

    2. If you are trying to install SQL Server reporting services then You should have IIS 7.0

    Look on internet, there are plenty of forums.

    Which Edition of SQL Server 2005 you are trying to install, like : Enterprise Edition, Standard Edition, Developer Edition, Express Edition, Evaluation Copy ?

    On which Edition of Vista you are trying to install , like Vista Ultima, Vista basic Home, Vista Home premium, Vista business ?

    Please provide information above information, if you are getting error while installing, please post that error here.

    Regards,
    IM.

  88. hai pinal ,

    I have a problem in Sql server 2005 ,

    iam using Linked server For Data transaction through internet

    I Set the MSDTC In Both Server and Node System..

    Below i mension Stroe Proc

    I am using Linked server For transferring data using MSDTC

    Alter Proc [dbo].usp_Select_TransferingDatasFromServerCheckingforExample]

    @RserverName varchar(100), —– Server Name
    @RUserid Varchar(100), —– server user id @RPass Varchar(100), —– Server Password @DbName varchar(100) —– Server database

    As

    Set nocount on Set Xact_abort on

    Declare @user varchar(100) Declare @userID varchar(100) Declare @Db Varchar(100) Declare @Lserver varchar(100)

    Select @Lserver = @@servername Select @userID = suser_name() select @User=user

    Exec(‘if exists(Select 1 From [Master].[' + @user + '].[sysservers] where srvname = ”’ + @RserverName + ”’) begin Exec sp_droplinkedsrvlogin ”’ + @RserverName + ”’,”’ + @userID + ”’ exec sp_dropserver ”’ + @RserverName + ”’ end ‘)

    declare @ColumnList varchar(max)

    BEGIN TRY BEGIN DISTRIBUTED TRANSACTION

    set @ColumnList = null

    Select @ColumnList = case when @ColumnList is not null then @ColumnList + ‘,’ + quotename(name) else quotename(name) end From syscolumns where Id = object_id(‘Crnot’) order by colid

    Set identity_Insert Crnot On exec (‘Insert Into ['+ @RserverName + '].’+ @DbName + ‘.’+ @user +’.Crnot (‘+ @ColumnList +’) Select ‘+ @ColumnList +’ from Crnot ‘) Set identity_Insert Crnot Off

    COMMIT TRANSACTION Select 1 End try Begin catch if (@@ERROR 0) Begin
    if @@trancount >0 Begin Select 0
    Rollback transaction END End End Catch

    Exec sp_droplinkedsrvlogin @RserverName,@userID Exec sp_dropserver @RserverName

    I am Getting An eroor “No Tansaction active”..

    Plz Help Me . it is very Important to Me
    Then Only i can Complete My Project …

  89. I Just Create An Data Base Mail Account, In SQL 2005 all are Set I configure This Account on My Local Computer all goes well well i when see these process

    SELECT *
    FROM sysmail_log
    GO

    then there is a messeage

    message could not sent to the recepient,,,,,

    This is because the connection is refused by the targeted machine (I.P.)…so
    i cant figure it out wat is this,,,

  90. Hi Mr. Pinal,
    Thanks for providing this opportunity for all. Espacially
    this site is very useful for me.

    How can I track client machine IP address from Server(using SQL Server 2005). Automatic IP should insert on particular table.

    I tried on this but I am getting server IP not Client machine IP.

  91. Hi Pinal

    I am Sunil from Chandigarh.

    tell me any website which provide me good knowledge of SQL-Server from beginner level to advance level.

    Thanks and Regards
    Sunil

  92. We are looking to migrate a mySQL db to SQLServer 2008. This db is about 100 gb in size and has several large (330+ million row) tables.

    What hardware setup do you recommend to optimize performance. What kind of disk setup (raid 1, raid 0, raid x, etc), recommended processors and count, how where to place the os vs the data files vs the tempdb files vs the tlog files.

    Thanks so much!

    David Fordham

  93. Hi,

    i have some questions.

    i want to move a database from one server to another databse in sql 2008
    and to create a script to import data into the database and able to run automatically to update the database.

    thanks

  94. Hi,
    i have another question,

    i have a query which is displayng results
    such as value 2, 3,6,8 ,15, etc. and for each client the value is different.

    Now , i want to display the difference in the same query as well
    then the output will be

    value dff
    2 0
    3 1
    6 3
    8 2
    15 7

    Thanks

  95. Pinal:
    I get following error while installing SQL Client tools. Please help me!!!

    TITLE: Microsoft SQL Server 2005 Setup
    ——————————

    There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information.

    For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=50000&EvtType=packageengine%5cinstallpackageaction.cpp%40InstallToolsAction.10%40sqls%3a%3aInstallPackageAction%3a%3aperform%400×643

    ——————————
    BUTTONS:

    OK
    ——————————

  96. Thanks for putting up a truly wonderful reference site for SQL Server. As part of my new job responsibilities, I have started on the DBA path and your site has been very valuable.

    Great Job!

    – Marco

  97. Pinal:

    I have following table
    complain(autoid int ,ComplaintDate dateTime
    ComplaintType (Foreign ,Product and Service) varchar(15))

    by this table i have to build the following out put using PIVOT

    Foreign
    —————
    MonthName ThisyrTotal LastYearTotal Change
    January 10 10 0
    February 8 12 -4
    March 11 9 3
    April 5 8 -3
    May 8 14 -6
    June 12 15 -3
    July 6 11 -5
    August 10 4 6
    September 13 8 5
    October 9 9 0
    November 7 4 3
    December 12 4 8

    —————————————————————–

    Product
    —————
    MonthName ThisyrTotal LastYearTotal Change
    January 50 48 2
    February – – –
    March – – –
    April – – –
    May – – –
    June – – –
    July – – –
    August – – –
    September – – –
    October – – –
    November – – –
    December – – –

    So, can u give any idea or any sql query for this.
    Please help me.

    Thanks.

  98. hi pinal

    i have a doubt .

    i am stopping and starting the sql server serices.

    does is there any system table or any view to find out the details.

    thanks & regards.

  99. Hi Dave,
    InSQL Server 2005 there is a table with primary key that contains two columns. I need programmatically add third column to this primary key. How can I do this?
    Thank You for advance,
    Igor

  100. Hi Pinal Dave

    In the sql i have table its name temp with 2 colomns
    id (samllint) and VALUE_NAME(varchar(5)).

    i have procedure with the name spInsTemp

    create proc spInsTemp

    as
    delete from temp where id=1
    insert into temp(id,value_name) values (1,’dfdfdfdfgdfgdfgdfgdfgdfgdfg’)

    when i execute the procedure – exec spInsTemp
    i get the mesege
    (1 row(s) affected)
    Msg 8152, Level 16, State 14, Procedure spInsTemp, Line 4
    String or binary data would be truncated.
    The statement has been terminated.


    and its ok

    when i execute the procedure from vb6 with ADO
    i cant get the error msg . iget only the first msg.
    in this case i dont have controle on the procedure i dont know if the procedure succeed or not

    please advice ,how can i get evrey msg from the sql
    in transactions or Storeprocedures

    Thank you verey much inadvance
    yacov

  101. Hi,
    I know primary level knowledge of transactional log….I want to know more about transaction log …can i see transaction log in server like we see table ……..how it is ?

  102. I need some advice and after reading your blog, thought you might have some insights.

    A new server that we’re installing for Navision 2009 will have 2 73 gig drives and 6 146 gig drives. Since I want to run 2-tier, it seems like I have the following choices for setup:

    1. Mirror the 73 gig drives and use them for op system (server 2008)
    Mirror two of the 146 gig drives and use them for Navision
    Raid 10 the remaining 4 drives and use them for SQL Server (2008)

    2. Mirror the 73 gig drives and use them for BOTH the operating system and Navision
    Raid 10 the remaining 6 drives and use them for SQL Server

    3. Mirror the 73 gig drives and use them for op system (server 2008)
    Raid 10 the remaining 6 drives and use them for BOTH Navison and SQL Server (2008)

    I’d really like to find out if any of these is significantly better than any of the other. I guess left to my own devices, I would go with option 1, but I really don’t have enough experience with Raid (and Nav2009) to know for sure.

    Thanks.

  103. Hi Dave,
    One column in my SQL has an integer data, something like 123456789. How to display it as $123,456,789 ?

    i.e add ‘$’ symbol infront of the value and separate data with commas(,) as shown above.

    Thanks

  104. @yacov.

    You have a table temp ( id samllint , VALUE_NAME varchar(5) ).

    Here column Value_Name can take max of 5 characters only. It cannot take values more than 5 characters, when you give more than 5 characters, sql server will trim the data and it will remove the extra characters from that value.

    In your example, you are inserting ,

    insert into temp(id,value_name) values (1,’dfdfdfdfgdfgdfgdfgdfgdfgdfg’)

    here you are trying to insert a value which has more than 5 characters,

    In your stored Procedure try this statement,

    insert into temp(id,value_name) values (1,’dfdfd’)

    This should work, because the value which we are passing into Value_name column is 5 characters.

    Hope this helps,
    IM.

  105. We ran BPA scan on our new server. We got the following message. Can you please advice.

    Thank you

    Message from BPA scan

    This message indicates that SQL Server has issued a read or write request from disk,
    and that the request has taken longer than 15 seconds to return.
    This error is reported by SQL Server and indicates a problem with the disk I/O subsystem.
    Delays this long can severely damage the performance of your SQL Server environment.

  106. @Tejal,

    I personally never came across this error.

    you can do one of following two things,

    1. Google Error. copy full error message and google it as it is.

    2. From what I remember when I last used BPA. Solution to error message will be included in the error message only. Error message will be in a form of link, if you click that link, it will direct you to solution page, If you are using BPA (SQL Server 2000) then try using Help File. I am sure you will find a solution.

    Once solved, please post solution so that it is helpful for every one of us.

    Hope this helps,
    Imran.

  107. Hye mr pinal dave,

    i need some guidance on sql command. i accidently update all data in 1 table to be as one data only. may i know how i want to revert back to the previous data. or at least what is the sql command to use to make the data in that table updated according to their own data by referring to another table. fyi, the another table has one column which match another column in that affected table and the data affected is around 85000 data. should i use ‘insert’ command to update back the table. and what is the fastest way to update back the table to the original one. Thank you in advanced for ur help….

  108. Hello Sir,

    I had done 1st semester in NIIT and completed my SQL Server 7.0 & JAVA, HTML, Front Page.

    SO i want to make a career in SQL DBA, So please tell me what can I do,

    or how to kick my career in this field.

    Best regards,
    Manoj Pal

  109. @Charry

    Use this function to get the required format.

    create function currencyFormat (@value int)
    returns varchar(100)
    as
    begin
    declare @index int
    declare @str varchar(100)
    set @str = convert(varchar(100),@value)
    if(len(@str) > 3)
    begin
    set @str = left(@value,len(@value)-3) + ‘,’ + right(@value,3)
    while(charindex(‘,’,@str)>4)
    begin
    set @index = charindex(‘,’,@str)
    set @str = left(@str,@index-4) + ‘,’ + right(@str,(len(@str)-@index)+4)
    end
    end
    set @str = ‘$’ + @str
    return (@str)
    end

    Best Regards,
    Muppidi.

    • This is as simple as

      declare @n decimal(12,2)
      set @n='123456789'
      select '$'+convert(varchar(20),cast(@n as money),1)

      But as I told earlier, this is the formation isses that should be done in front end application

  110. @VijayaKumar,

    exec sp_change_users_login ‘report’
    will give you a list of orphaned users ( only for sql server logins)

    in order to fix these orphaned users, run this script.

    step1: First create the sql server login if it does not exists, if it does exists already then go to step2

    step2 : exec sp_change_users_login ‘user_name’ , ‘login_name’

    for more details look at sp_change_users_login in books online.

    Hope this helps.
    IM

  111. Hi Pinal,
    I need you help in writing a query. I have to return TOP N result. If the user pass me 0 (zero) then I have to return all the results. Along with this I have to dynamically sort the data as well. I have written a query but the problem is that TOP with ORDER By clause is not returning me the correct data. Here is my query. Please help me if you have any suggestions:

    IF @MaxCount = 0
    BEGIN
    SELECT
    FloatValue AS DataValue
    , @EstimatedRecordCount AS EstimateRecordCount
    FROM
    @DataItemList DataItemList
    INNER JOIN
    tbDSNET_DIM_DataItem_IndexValue_Float IndexValueFloat
    ON
    DataItemList.[PK_idDataItem]= IndexValueFloat.[FK_idDataItem]
    ORDER BY
    CASE @SortDecending WHEN 0 THEN
    FloatValue
    ELSE NULL
    END ASC,
    CASE @SortDecending WHEN 1 THEN
    FloatValue
    ELSE NULL
    END DESC
    END
    ELSE
    BEGIN
    SELECT
    TOP (@MaxCount) FloatValue AS DataValue
    , @EstimatedRecordCount AS EstimateRecordCount
    FROM
    @DataItemList DataItemList
    INNER JOIN
    tbDSNET_DIM_DataItem_IndexValue_Float IndexValueFloat
    ON
    DataItemList.[PK_idDataItem]= IndexValueFloat.[FK_idDataItem]
    ORDER BY
    CASE @SortDecending WHEN 0 THEN
    FloatValue
    ELSE NULL
    END ASC,
    CASE @SortDecending WHEN 1 THEN
    FloatValue
    ELSE NULL
    END DESC

  112. Hi Pinal,

    I need your help to develop my SQL skils.I 2007 passed out and got placed in an MNC.I am NON-IT student.I got training in unix.But in my firm they appointed as an SQL database admin.I dont have much knowledge about SQL.Please help me to develop my SQL Skills.

  113. sir,
    i installed SQL Server 2005, and in server name local server name e.g “pcname\sqlexpress” is missing, my sql connects with main server and works proper, but for local server how can i add this in the list?

  114. Hi Pinal,

    I have 2 table, table1 have 4 records with same id but diff. fields and table2 have 1 record with same id to table1 and diff fields, now when i use inner join on id then all columns from table 1 comes fine but columns for table2 have repeating values.

    please suggest any query.

  115. Hi Pinal,

    I have a database named db_ERP . In that database a number of tables and SPs are there.In that database i modified field some table column names.I do the same for some other table columns also.But i have a lot of SPs using those table fields.
    The columns changed not for a single table,but it done for many tables. How i can get the list of SPs which are now incorrect by modifiying the table’s columns ? .

    please suggest any query as early as possible.

  116. hello,

    How to use variable content as fieldname in any sql query?

    e.g.

    Insetead of using select query as
    SELECT OrderNo, Orderdt FROM tblOrders;

    I want to provide column name dynamically like:

    declare @colname as varchar(10);
    Set @colname=’OrderNo';
    SELECT @colname, Orderdt FROM tblOrders;

    but this query returns string “OrderNo” for all rows.

    How to solve this problem? Pls suggest any solution.

    • This takes call for Dynamic SQL. Well, why do you want column name to be dynamic when you can easily write actual name in SELECT.

      declare @colname as varchar(10);
      declare @sql nvarchar(1000)
      Set @colname=’OrderNo’;
      set @sql = ‘SELECT ‘ + @colname + ‘, Orderdt FROM tblOrders’
      exec sp_executesql @sql

      But please note that, if you embed dynamic sql in a proc, this leads to a serious SQL Injection attack

  117. Hi Sir

    Its an honour to write to you. I installed SQL SERVER 2005 Enterprise on Windows server 2008 machine. After that installed Service Pack 3 directly without installing prior SPs ie 1 and 2. To connect graphically i just installed Express Management Studio things worked fine as i normally connect to the Server with my local system where i upgraded from Express to Standard. On server i dont have the SQL server management studio thus i dont have many facilities like back scheduling, SQL profiler etc. Could you please help me how to make things right at the Server end.
    Thanks in advance

    Emad

  118. Hello Sir,

    Am a regular reader of all the blogs discussed here. It is very useful to me and to all our friends..

  119. Hello Sir,

    I am very new for Windows and SQL. I’m working as SAP Basis Admin and have to update the SP of SQL (2005) in my current landscape.

    Can you please help me in this. I searched the same in SQL 2005 BOL and net but got nothing on this. I need a step-by-step process list for the SQL 2005 Support Pack upgrade.

    Thank you Sir …

    Anil

  120. Imran Mohammed

    we used sql 2005. I copy past the message in google , didnt come across any soultions or any message that can guide me or point me to this BPA message.

    on January 30, 2009 at 2:59 am Tejal
    We ran BPA scan on our new server. We got the following message. Can you please advice.

    Thank you

    Message from BPA scan

    This message indicates that SQL Server has issued a read or write request from disk,
    and that the request has taken longer than 15 seconds to return.
    This error is reported by SQL Server and indicates a problem with the disk I/O subsystem.
    Delays this long can severely damage the performance of your SQL Server environment.

    Anyway thank you for your suggestion.

    Tejal

  121. We currently upgraded our database from 2000 to sql 2005. We have notice some sql procedure run take 3 hours to run. We contact tech.support they requested to do maintenance on index and stattics.

    We rebuild all the indexes. It still didnt help.
    Another soultion they offered is

    Enable RCSI. (probably the most important from a performance standpoint)

    You must now activate the READ COMMITTED SNAPSHOT ISOLATION level for the upgraded database. To enable
    ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON
    To verify if the database is using RCSI you may run the following SQL command:
    select is_read_committed_snapshot_on from sys.databases where database_id = db_id (DATABASENAME)
    The output must be the number 1 (one), which means, RCSI is enabled.

    we are running above sql script in single user mode. It’s been 48 hours it’s still running.

    Any help appercaited to improve the performance.

    Thank you
    Tejal

  122. – Hi, I have some many times benefited from the code snippets from your blog so I thought to share one mine with you

    — what the … I have been doing lately on sql server ( 2005 or 2008) ?!

    use db
    go

    select o.name ,
    (SELECT [definition] AS [text()] FROM sys.all_sql_modules where sys.all_sql_modules.object_id=a.object_id FOR XML PATH(”), TYPE) AS Statement_Text
    , a.object_id, o.modify_date from sys.all_sql_modules a left join sys.objects o on a.object_id=o.object_id order by 4 desc

    Br,

    Yordan Georgiev

    P.S. Keep going with the best sql server dedicated blog !

  123. Hello Mr. Dave,

    Greetings!

    I am quite new to web development and coding.

    I want to try a simple function – to save data from a web from into an ms sql database called WebData (table called Test)

    The web form has 4 fields Name, email, job title and company.

    I want to use html and php scripting. Can you please help me with a sample code for the same.

    Kind Regards,
    Rishab Varma

  124. Hi Pinal,

    I am a regular visitor to your site and I got many benefits from your articles. I have a query could you please help me

    I’ve a db on postgresql and i want to export the db to sql server 2005 I could export all the tables but I am unable to export Triggers,Views, Indices and stored proc’s.
    Your kind help is greatly appreciated

    With regars,

    Raveens

  125. hi pinal.
    u have created the most amazing, helpful,knowledgeable, excellent , conviniently understandable database material.
    this could helo masses in cracking their interview and making their dreams tru.
    May GOD fulfil all your wishes.
    Keep up the good work.
    Thanks for sharing such a great information ……
    ;-) Cheers to Pinal Dave

    Regards
    HSingh

  126. I want to autogenrate data in a table column like ‘MMYY001′ in this format
    ex:- ‘0209001’

    if it is march 1st then it should be ‘0209001’

    an what datatype can it be
    Please reply asap

  127. Thanks in advance for your assistance.

    I am defining an openquery to a linked server with a @SqlStmnt VARCHAR(8000) declare statement. I am getting a

    Msg 105, Level 15, State 1, Line 3
    Unclosed quotation mark after the character string ‘

    error message which I believe is caused by my query being over the 8000 limit. I have tried varchar(max), but get the same error. My select statement has about 100 lines & I’d say the avg # of characters on each line is 50, so maybe 5,000 characters…although I am also using a looping routine to use AcctNbrs in my where criteria – WHERE Account IN (‘ + RTRIM(@AcctString)+ ‘).

    I believe there are 300 accounts in each batch of records in my looping routine. Not sure if this is increasing the number of character interpreted by Sql in for the @sqlstmt variable.

    When I run the code and only select 10 fields it works, but now I need all 100 or so fields from the table I am querying against.

    I cannot select * from the table as I have to convert some date values so I have to specify each field.

    Not sure where to go from here…thanks.

  128. Can’t find much documentation for this out there for this. Why or Why don’t DBAs use WHERE clauses that contain case statements? Is their a performance hit or is it just something that DBAs generally look down on? It would seem logical to me to pass a parameter and depending on its value use a CASE statement to supply the correct WHERE clause. Don’t see much of people doing this out there however. I would love to hear points for and against this scenario. I am guessing it is a read ability issue, but if not what is the best practices for scenarios like this? Example below.

    select *
    from YourTable
    where SomeColumn = case
    when Condition1 then Value1
    when Condition2 then Value2
    else Value3
    end

  129. Dear All,

    Could you please provide me SQL SERVER 2005 Interview question of DBA ( production ) not for DBA (Developer )..

    thanks in advance

  130. Dear Pinal,

    I am working in SQL SERVER 2005 ,

    i have found out difference between DBMS and RDBMS …

    DBMS Support Single user and RDBMS support Multiuser..
    can you please explain this answer with realtime example?

    why DBMS not allow for Multiuser?

  131. Hi Pinal Sir,
    I am frequent visitor of SQLAuthority.com.You do a graet job.
    I have found answers to most of my issues at work thru your site.
    Now I have a issue that I cant find a solution.

    I have a RunSQL.bat file that calls the .sql files and creates stored procedures in the Database.But when I try to execute the SP,I get error that I don’t have access.(I can grant permission manually,but this is going to be implemented by another person,so I have to script everything).
    So I tried to create another .sql file that will grant execute permission for the 3 stored proceudres that will be be created when I run the .bat file.Also this grant permission sql file will also be executed as part of the RunSQL.bat.
    The bat file will generate a .out file where it will have errors if any.

    The grantexec.sql file:

    USE databasename
    Go
    Grant exec on [dbo].sp_PersonAddresses] to public
    go

    I get the following error in the grant permission sql.out file,but when I run the same command thru SQL QUERY analyzer,the command is successful.

    ‘You can only GRANT or REVOKE permissions on objects in the current database’.

    Any suggestions to grant permissions to the SP while I create.
    Your suggestions and tips would be much appreciated.

    Thanks,

  132. Hi Pinal
    Great site, I was wondering , why i could not discover you few years back? I have found many useful articles from your site , and will mention it at US East coast sql forums.

    Would you be interested to do a little favor? I have searched your entire site ( and web too) but could not figure out the best way to do it.

    assume following three tables

    Vendor=>
    VendorID (int)
    Name (varchar)
    State (varchar)
    AddedOn (varchar)
    __________________________________
    VenDorDescription=>
    VendorID (int)
    VendorDescription (Text)
    __________________________________
    VendorItems=>
    VendorID (int)
    ItemID (int)
    ItemName (varchar)
    ItemSize (int)

    Now assume following query…
    Query is something this , find all vendors added in last three months (Addedon) in state (State) , who have some licenses (Text column)
    and they supply ItemA with at least size 4 or itemB with at least size 6

    For first part a inner join and contains will do the trick, for later part (find parents where detail have matching criteria) is getting tricky.

    What do you suggest? UDF , CTE or something else
    ‘I have experimented with temp tables and UDF (single value) and execution time is horrific.

  133. Hi Pinal
    I am working on a project where the column and table have the same name. That is say the table name is Employee and there is a column in the table called Employee. I was under the impression this is not a good practice because it is confusing but would like to know if there is any performance constraints because of this. I spoke with the DBA whose thoughts were other than (maybe) confusing this is an OK practice – do you have any insights that you might be able to share on this?

  134. hi, pinal.

    i would like to see my self as database administrator.
    can you guide me basic steps which i need to take up.

    it can be your online course or other..

    regards

    vijay

  135. i have a website Server and a data base server. I connect the database server from the website server using DSN.
    This is how our system has been running for sometime.
    I keep observing the error log file quite frequently.

    These days i come across a very umfamilar error which says as follows:

    <![CDATA[ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

  136. i have a website Server and a data base server. I connect the database server from the website server using DSN.
    This is how our system has been running for sometime.
    I keep observing the error log file quite frequently.

    These days i come across a very umfamilar error which says as follows:

    <![CDATA[ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    ERROR errormsg – A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

  137. I want to perform backup to a network share. Does the network share need to be on the server or client side? I have mapped a drive on the sql server, however when performing the backup the drive is not listed. Also, I don’t see the client side network share either. The only drives I see listed in backups is the physical drives located on the server. Do to space limitations I would rather not use these.

  138. Hi Dave

    Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

    I need help,,,,, I follow yours steps but my sql show an error. Exist another source for make this Import CSV File Into SQL???
    ok thanks and Congratulations for you god job!!!

  139. Hi,

    I am having a scheduled job running DTS packages on my production environment.
    My production server server is having Window 2003 Sp2 installed on it.

    Before rebuilding the server I was getting more details error messages in case job failed
    the error description is having more detailed error message.

    for example:

    Step ‘DTSStep_DTSExecuteSQLTask_197′ failed

    Step Error Source: Microsoft Data Transformation Services (DTS) Package
    Step Error Description:Microsoft OLE DB Provider for SQL Server (80004005): Connection failure.
    Step Error code: 8004043B
    Step Error Help File:sqldts80.hlp
    Step Error Help Context ID:1100

    Now I am getting error message like below:

    Step ‘DTSStep_DTSExecuteSQLTask_17′ failed

    Step Error Source: Microsoft Data Transformation Services (DTS) Package
    Step Error Description:The task reported failure on execution. ( (80004005): )
    Step Error code: 8004043B
    Step Error Help File:sqldts80.hlp
    Step Error Help Context ID:1100

    The same error message is there in Job History and DTS log.The error codes I am getting in the error messages are the generic ones which can
    contain several errors.

    I have checked everything and could not figure out any differences in the configuration before and after building the server.

    Please can someone help me to find the out the reason why now I am not getting the detailed error description
    and how to rectify this as without the actual errors it wil be difficult to investigate the reason for failure and
    to convince the client.

  140. I have a problem with a database I inherited. It is for a web app that is subscription based. Each account has its own set of tables. Table1_Username, Table2_Username, …. ect. So the problem is that the procedure cache is pretty large since I have to use dynamic sql in the stored procedures to reference a specific users table. I wanted to parameterize these so I don’t add each instance to the cache but I can’t do so with a table name. Do you have any suggestions other than redesigning the whole db.

  141. why stored procedure encrypted is very weakly.
    it would decrypted very easy with decrypted Software .
    is there way that robust?

  142. why stored procedure encrypted is very weakly.
    it would decrypted very easy with decrypted Software!
    is there way that robust?

  143. Hi Pinal,

    I was wondering if you can refer me anything related to how images can be handled in SQL 2005. I want to store image in a field so that when its called from the application the image shows up.

    Thanks for your help in advance.

    • The best approch is to store image file path in a table and store images in the server’s directory. If you want to store it in a column, use varbinary(max) datatype and openrowset function to convert it to binary

  144. Hi sir,

    Can you help me with importing a log file into mSSQl . The log has following format :-

    [INFO ] [2009-02-20 11:16:52,356] [http-12080-1] [categorization.CategorizationUtil] – [CMR String:: -1,0,1,1,65,66,433,450][-1,0,1,1,66,67,443,457][-1,0,1,1,150,151,992,1008][-1,0,1,1,283,471,1907,3283][-1,0,1,1,471,472,3274,3290][-1,0,1,1,472,474,3284,3304][-1,0,1,1,474,475,3295,3311])(7[-1,0,1,1,5,5,30,39])}]]

    [ERROR] [2009-02-20 11:16:52,356] [http-12080-1] [categorizer.StagedCategorizerUtils] – [Stream is Corrupted]
    java.io.IOException: Stream is Corrupted
    [ERROR] [2009-02-20 11:16:52,356] [http-12080-1] [categorizer.StagedCategorizerUtils] – [Stream is Corrupted]
    java.io.IOException: Stream is Corrupted

    Thanks
    Ajay

  145. Pingback: SQL SERVER - UDF - Function to Convert Text String to Title Case - Proper Case - Part 2 Journey to SQL Authority with Pinal Dave

  146. Hi Pinal – I have a problem that baffles me. I had a sql server 2008 install that was bad. I uninstalled, reinstalled. When I go into Management studio, it lists two entries for my server and two entries for the MSSMLBiz. I think the extra one is from the bad install. Know anything to fix this?

  147. i need a help pliz,
    to create SQL scripts whereby the shipmentdate should be updated to two days after the transaction date after the transaction is successfully completed

  148. @Kathy

    Issue:

    Mru.dat file tracks to all Server names that you connected previously, just like our Internet browser, it records all the websites we visited. SQL Doesn’t checks if that server is available or been unstalled. The information you see is purely historical information saying you once connected to that server.

    Solution: To get rid of this list, delete mru.dat file:

    Location of mru.dat file
    C:\Documents and Settings\user_name\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

    Nothing will show up in drop down list when you try to connect SQL Server through Management tools after you delete mru.dat file.

    In the above location path change the following

    1. Replace user_name with your windows account name.

    2. You might not be able to see Application Data folder, to see that folder,do the following.
    a) double click MyComputer.
    b) Click Tools on the top bar of the window
    c) Click Folder Options
    d) Click View
    e) In the list, click Show Hidden files and folder
    f) Click OK

    After you perform step 2(a) to 2(f) you should be able to find this file and then delete this file.

    Hope this helps.
    Imran.

  149. HI

    I’m looking for a solution to deal with Data import from flat File(s) in to sql server 2008 DB, Please help…

    There are various flat files that have to be imported into the database (I can use SSIS for import); the real problem is with the data in the flat files.

    Each row in file is identified by different row types and each row type’s columns are defined in a separate document.

    In detail:

    First Row data:

    “A00″, 399,”COY”, 20090113,093605,330

    Second row data:

    “V01″,”008″, 15.0, 1265129.89

    Please observe the first column(s) A00 and V01 in both the rows

    There is a separate document in which the column names for A00 and V01 are defined.

    What I want to achieve…

    I want a suggestion from the members to know what can be the possible destination table architecture for this scenario and what procedure can I follow to import all Files in to SQl Server tables through SSIS or through any other possibilities.

    I cannot create a table for each row type as there are few files with over 60 row types.

    Please also note that there will be multiple records under each row type

    Ex:

    First Row set:

    “A00″, 399,”COY”, 20090113,093605,330

    “A00″, 400,”COY1″, 30090113,193605,430

    Second row set:

    “V01″,”008″, 15.0, 1265129.89

    “V01″,”002″, 13.0, 1244.34

    “V01″,”003″, 16.0, 177126.09

    “V01″,”009″, 19.0, 1565129.89

    Etc,

    Please excuse me if im not clear in my description, Please post your suggestions.

    Thank you.

    JG Kiran

  150. Hi ,

    I was trying deny query on SQL Server 2005 Standard edition.

    deny select on test..testtable to A1

    eventhough it showed command was successfully executed, the command was not effective ie user can still successfully run select query.

    But when I run the same queries for SQL Server 2005 Enterprise edition it worked perfectly.

    Please advise me.

    I used management studion too for denying a user fomr accessing a table as weel as column from denying through securables. But it is not working for Standard edition but working for Enterprise edition.

    Please advice me on this.

    Thankyou
    A

  151. Hi
    i am using sql server 2005,
    i have created two table . i have created primary key constraint on tableone and on tabletwo i have created foreign key constraint that refernces to tableone . now i am inserting into table two with null value it allows me .it violates the parent-child relationship. is there any solution?

    Thank you

  152. @Kaushik

    create primary key constraint on foriegn key column of tabletwo

    or

    create not null constraint on foriegn key column of tabletwo

    Regards,
    IM

  153. I have a department store database with the following tables:

    tbl_branch —> branch_id, name, location
    tbl_category —> category_id, name
    tbl_items —> item_id, name, price, branch_id, category_id

    ISSUE:
    1. I have backup files of more than 2 years of my database. (around 6000 .BAK files)
    2. I need to retrieve data of the mentioned tables.
    3. Note that tbl_category & tbl_branch data have been modified several times by the administrator over the period.
    4. I want to save those tables in a separate database from the backup files, so that i can run queries for statistics

    purpose.

    Database engine used: MS SQL 2000 Standard

    What process should i follow to automate the task? Are there any tools that can help me achieve this?

  154. Hi ,

    I am using SQL Server 2005.My report server is not working.When I am starting my reporting services it is throwing some error like Error 1053:The service didnot respond to the start or control request in a timely fashion.
    Could you please help me out.

  155. @Kaushik.

    I Understand what you must be thing, you cannot insert NULL value in Primary key Column, so your question is how can you insert NULL in column that has Foriegn Key constraint because foriegn key column should have only those records that exists in Primary key and Primary key will not allow NULLS.

    According to what I understand.

    Following are important Features of Primary Key constraint.

    1. One Primary key per table.
    2. Data inside that column ( or combination of columns) must be unique.
    3. Data must not contain NULL values.
    4. Duplicates cannot exists.
    .
    .
    .
    .

    Foreign Key constraint.
    1. Will contain all values that exists in primary key column to which it is refering.
    2. Duplicates can exists
    3. Can have NULL values.

    We also have to understand that NULL itself is not a value. 0 (zero) is a value, but NULL is not a value. And I have not read any where saying Foriegn Key constraint should not have NULL, because primary key column cannot have NULL values.

    Remember to have no NULLS in column is imposed by Primary Key constraint, Foriegn Key constraint does not impose any constraint like that, Similarly Unique key constraint will allow only one NULL value in that column and NOT NULL Constraint will now allow any Nulls in that specific column.

    There is alot of material available on internet, I suggest you please read it.

    Regards,
    IM.

  156. @ shruti , please follow this ,

    From online forums, I saw this solution for your problem,

    Click Start, click Run, type regedit, and then click OK.

    2.
    Locate and then click the following registry subkey:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

    3.
    In the right pane, locate the ServicesPipeTimeout entry.

    Note If the ServicesPipeTimeout entry does not exist, you must create it. To do this, follow these steps:

    a.
    On the Edit menu, point to New, and then click DWORD Value.

    b.
    Type ServicesPipeTimeout, and then press ENTER.

    4.
    Right-click ServicesPipeTimeout, and then click Modify.

    5.
    Click Decimal, type 60000, and then click OK.

    This value represents the time in milliseconds before a service times out.

    6.
    Restart the computer.

    source :
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/b57ee42d-42ef-44a4-9670-be9088dbf9d4/

  157. Hello Pinal,

    I have some problem with sql server performance.
    We have a 300 million records in our db table.

    How do I write select * from this table.

    I am working designing paging concept in our framework development.
    We are using .Net 3.5 and SQL server 2008.

    Any suggestions for paging concept at SQl server side are welcome.

    Thanks & Regards
    Hasmukh

  158. Hi Pinal,
    I need help about email alert for Failed backup.
    How we can do that ?
    I refered ur document for database mail and it worked fine but how to send mail when backup failed.

    Thanks and regards,
    Atil

  159. Hi Pinal Dave,

    Could you please tell me, is there any query that will convert the data in a Row of a table to XML file format.

    Example: Table name : Employee

    EmpNo Emp2 Emp3
    1 Kumar DBA

    I want the above column convereted as follows:

    Please give me a suggestion

    Thanks in Advance
    Prashanth

  160. hi,

    i would like to see my self as database administrator.
    can you guide me basic steps which i need to take up.

    it can be your online course or other..

    regards

    obu

  161. Hello,

    First of all, thank for maintaining such and interesting and completed blog Pinal, it is great to see the dedication you put into SQL.

    I am calling a Object execution statistic report from the SQL studio management and all the statistics returns correctly on my databases except on one, where it says currently no executable object has consumed CPU time.

    I have updated all the stats already and auto update is set to ON.

    Have you faced this ever before ?

    All the best !

  162. Hi,

    I have raw data(sheet1) in an excel sheet in one tab. In the same excel sheet on a different tab(sheet2) i a using a ms-query to pull records from the rawdata(sheet1) in and build graphs on them. This works fine on my system….however if i email this file to another person they are unable to refresh it . they get an error message stating ” The Microsoft jet database engine cannot open the file ‘…../…./….’ .Its already open exclusively by another user or you need permissions to view the dat”

    There is no password for this file.

    Please advise.
    regards,
    conrad

  163. Dear Sir,

    I am working on a billing Module and just cannot get the recurring billing dates right, the end dates always mess ups. I use the following code, which has a bug.

    ‘For Next Start (i is the counter to repeat number of times)
    Format$(DateAdd(“M”, i, @SDate), “dd/mm/yyyy”)

    ‘For End Date
    Format$(DateAdd(“M”, i, DateAdd(“M”, i, @SDate) – 1), “dd/mm/yyyy”)

    Please Help. thanks

    Iqbal Zariwala

  164. I have a production server.I want to get the data from application server for updation and reviweing. When I tried establsih remote database access with my computer to the application computer, I coudn’t connect it. It shows the following problem :

    “A network-related or instance-specific error occurred while establishing a
    connection to SQL Server. The server was not found or was not accessible.
    Verify that the instance name is correct and that SQL Server is configured
    to allow remote connections. (provider: Named Pipes Provider,
    error: 40 – Could not open a connection to SQL Server)
    (.Net SqlClient Data Provider) ”

    I made the changes in both the computer to enable remote access. But it is not working. Please provide a solution

  165. Thank you for all your efforts!!! You have made my life easier on so many occasions! I can thank you enough – please keep up the outstanding posts.

    IF you have time (some other green sql people may want to know this as well) I have run the DBCC CHECKTABLE and it prints out stuff in the message area, it does not say there is any errors, but I would just like to know what does it all mean?

    Here is what I recieved…
    USE DATABASENAME DBCC CHECKTABLE ([TableNAme])
    DBCC results for ‘TableNAme’.
    There are 48127 rows in 165 pages for object “TableNAme”.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Any insight to this would be great, I am just looking to understand a little more.

    Thanks again
    Bill

  166. Hello Anand

    Check whether your Sql Server Service is started or not. Start –> Control Panel –> Administrative Tools –> Services –> Sql Server Service

    Regards
    Vijay

  167. Hi Pinal,

    I found your website through search engine in January and from then till today i have been visiting your website everyday. I have learned alot from articles, scripts… So thanks for good work, keep it up.

    I need ur advice on the following:
    1. ARCHIVING – is very imp – what thing needs to be considered and discussed with client… as the database would have million of records in couple of days.

    2. SYNCHRONIZING – We got 50 developers working on REMOTE location. We used Redgate in the past to keep everything in sync (dev, staging and production) but we faced lot of issues with it.

    What would you suggest on this – so that we keep everything in sync….

  168. Hi Dave,

    How can we check whether a table (upto row level) has been locked for insertion , updation or deletion?

    Thanks in advance.

    Jacob

  169. Hello,

    I am a .net profession working in chandigarh. I want to switch into database. I would like to devote maximum of my time into database work. What would you recommend me to do in order to be a database professional. Shall i do SQL DBA training or SQL Database development training. As i already have over 3 yr. of sql experience what peace of suggestion you would recommend me.

    Thanks

    Anil

  170. Hi Pinal:

    I need an advice from you. I’m having a DB which is updated daily. Some time I face an issue. While updating, the size of log file increases horizontally & this stops the application from working. Until no one truncate or shrink the log file, it will not start work. What I want, I want to develop a USP which will monitor the size of log file & if it increases beyond a limit or increases sharply, either I should shrink the file during updation or logging of log should stop.

    I can get the size of log file & check whether it is increasing sharply or not, but problem arises when I want to shrink the file. I can’t shrink the file while updation is going on as the transaction is not committed yet.

    Could you pls provide me your input to come out from this showstopper. I will be thankful to your for the same.

    Thnxs

    Mint

  171. Hi doctor,

    I would like some help for transact replication in sql server 2005. I’m mcts and mcdba. This error is very complicated. About conflits in replication monitor. I used verboselevel 0.
    I woulk like to send a txt, can I?
    thanks.

  172. I have two database (same structure with same name in different location.) But different data. Now my problem is I need to merge through some script or stored procedure.
    so please help me.

    eg

    database name:testdb
    table name: testtbl
    data inside table: Data are different in two testtbl (totally different )

  173. hi pinal

    actually i search so much for this problem but can’t find solution for that…so writing to you..

    actually i have make one trigger on table for after insert…this trigger is simple when any row inserted into table
    i m doing some calculation and put data into another table during trigger….

    but when i m going to insert data into triggered table using import and export wizard that time don’t trigger not fired…means it’s completely bypass trigger can you tell me what’s reason behind that pls tell me i have provided my mail id …so if you can reply here or there both are very helpful to me…

    thanx in advance…

  174. Sir I have a requirement that I have 2 tables and I want to update and insert from the another table .
    I have table A and table B
    Table A have 3 colms and table B is same as table A
    But I want to replace the data of table A which is in table B and at the same time I want to insert the data which is not in table A but in Table B

  175. Hi Pinal,

    I have a set of tables

    –create table Acct(Accont varchar(2),Parent varchar(2))
    –create table Fact(Account varchar(2),Value numeric)
    –create table Agg(Account varchar(2),Value numeric)

    The Account table will have the data for parent as well as child accounts.
    The Fact will have values for only child account codes.
    But the Agg will have Values for both child account codes and also their parents.

    Can you please let me know how this can be done.

    Thanks,
    Priyadarshan Mishra

  176. Hi Pinal:
    I am looking for a way to audit all logins in all production servers and sql servers, make a list , for each login, their respective server roles and database permissions. What about scanning through a whole group of servers? I have about 1500 SQL Servers to check through.

    How can i achieve this using script. I have to automate also so that i will monitor all servers.

    Please help me in this.

    Thanks

  177. Hi Pinal
    I have a question about the message returned in this update:

    update s set s.bill_amount = 1, s.balance = 1
    from subscriptions s, name n
    where s.balance = 0 and s.bill_amount = 0
    and s.id = n.id
    and n.member_type in (‘MU’,’MRU’) and n.status = ‘S’
    and s.product_code = ‘USDUES’

    Result:
    (1086 row(s) affected)

    (1476 row(s) affected)

    When I executed a select query with same conditions before the update it returned 1,476 rows
    What is it 1,086 records affected

    Thank you

  178. Hi Pinal,

    I’m a big fan of your work and for a novice developer like me, your work and examples have been a ton of help.
    Recently I came across a problem with SQL 2005 that how do you compare two rows within the same table?

    What my problem is, how do i get the date and the values it changed by between 2 rows within a same table.

    I figured running a distinct will give me the unique results but when trying to get the date, data chaged, it’ll destroy the usage of distinct function.
    (Note: this table contains transactions everyday)

    I know it’s a bit strange question, but i can’t think of a way of accomplishing this?

    Thank you
    Damiththa

  179. hi Pinal,

    do you have by chance steps for sql 2005 mirroring.
    I need the step by step .I greatly appreciated you help in advance

  180. BACKUP LOG db-name WITH TRUNCATE_ONLY

    DBCC SHRINKDATABASE (db-name, TRUNCATEONLY)

    Can I use this, in order to reduce log.ldf file?

    please send me any suggestion to me email

    best regards

  181. @Damittha,

    If you know the where clause for the two rows even if it is a same table, you can still make the same table as two tables. and then using join you can see what values have been changed.

    Example:

    create table example1 ( ID int, Value1 int, Date int, Changed_By varchar(40))

    insert into example1 values ( 1, ‘3000’ , ’12/31/2008′ , ‘Imran’)
    insert into example1 values ( 2, ‘4000’ , ’12/31/2008′ , ‘Imran’)
    insert into example1 values ( 3, ‘3000’ , ‘1/3/2009′ , ‘Taher’)
    insert into example1 values ( 4, ‘5000’ , ‘1/3/2009′ , ‘Imran’)

    Now lets consider, for example above, lets says you came up with a where clause something like this,

    where ID = 1 and Value1 = 3000 , this will be your first table.

    Where ID = 2 and Value1 = 3000, this will be your second table.

    your select statement can be something like this,

    select A.Date Previous_Date,
    B.Date New_Date,
    A.Changed_By,
    B.Changed_By
    From (select * from example1 where ID = 1 and Value1 = 3000 ) A join (select * from example1 Where ID = 2 and Value1 = 3000 ) B on A.Value1 = B.Value1

    So, all you need to find out is how will you be able to build your where statement.

    ~ IM.

  182. @ IM (Imran Mohammed)

    Thank you for the quick response. Seems like your solution did solve the problem.

    Let me give it a shot and will let you know whether I am able to tweak it a little for my purpose.

    Thanks again
    damiththa

  183. Hi Pinal !

    I have a database which has long-text data and I am using full-text search on it.

    I need to remove duplicate strings from it.

    When i use direct string comparison it takes long time to retrieve distinct records for removal, I am using CTE for removal the duplicates.

    How can I speed it up ?

    Please help me.

    Waiting for your kind response.
    Regards,
    Musab
    Database Administrator

  184. Hi Pinal,

    I learnt about piecemeal restore recently. Can you explain this feature more details and what is the benefit of using it? Also, I want to know if it works in the subsciber databases if I’ve replication setup.

    Thanks,
    Roy

  185. Hi Pinal,

    i have a three tables of same structure(alls having same

    fields).And all three tables have data.

    Now i want to merge all three tables into one.

    These three table may have some common records thats i

    have to put in final one.

    I m using sql server 2005.

    Please tell me how do i go about it ?

    Thanx,
    Lalit

  186. Hi Lalit,

    I think you need to find common values among the three tables. You can use Row_Number() of SQL 2005 to identify the common row. you can find more details at : http://tejasnshah.wordpress.com/2008/12/31/find-duplicate-rows-with-row_number-sql-server-2005/

    let me give you one example by dummy data:

    DECLARE @a TABLE(ID INT, Value VARCHAR(50))
    DECLARE @b TABLE(ID INT, Value VARCHAR(50))
    DECLARE @c TABLE(ID INT, Value VARCHAR(50))

    INSERT INTO @a
    SELECT 1, ‘AA’
    UNION ALL
    SELECT 2, ‘AB’
    UNION ALL
    SELECT 3, ‘AC’
    UNION ALL
    SELECT 4, ‘AD’

    INSERT INTO @b
    SELECT 1, ‘BA’
    UNION ALL
    SELECT 2, ‘BB’
    UNION ALL
    SELECT 3, ‘BC’
    UNION ALL
    SELECT 4, ‘BD’

    INSERT INTO @c
    SELECT 1, ‘CA’
    UNION ALL
    SELECT 2, ‘CB’
    UNION ALL
    SELECT 3, ‘CC’
    UNION ALL
    SELECT 4, ‘CD’

    ;with cte AS
    (
    select ROW_NUMBER() OVER(PARTITION BY A.Id, A.value, b.Id, b.Value, c.ID, c.Value ORDER BY a.ID) AS RowID,
    A.ID as AID,
    A.Value as AValue,
    b.ID as BID,
    b.Value as BValue,
    c.ID as CID,
    c.Value as CValue
    from @a a
    inner join @b b ON b.ID = a.ID
    inner join @c c ON c.ID = a.ID
    )
    select *
    from cte

    Let me know if it helps you in any way.

    Thanks,

    Tejas

  187. Dear Pinal Sir,
    I am swati Bhoyarkar from mumbai.I am a soft Developer. keen to be the most successful DBA .I just want to know if you conduct and seminor or class for the needy job seekers like me.I just need a guidance on how to move successfully wihtout leaving anything behind.

    Thank you regards
    swati

  188. hi sir,
    I am getting this error while taking connection string from udl file..
    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied..
    actually i installed software on client side and i configure udl file client format their system now when i configure the udl file the same error i got while configuring udl file.. my application take connection string from the udl file…… i am using sql server 2000……

    plz help me
    thanks
    nitin sharma

  189. hi sir,
    I am getting this error while taking connection string from udl file..
    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied..
    actually i installed software on client side and i configure udl file client format their system now when i configure the udl file the same error i got while configuring udl file.. my application take connection string from the udl file…… i am using sql server 2000……

    plz help me
    thanks
    nitin sharma

  190. Hi sir,
    I want to use osql in sql server 2000 for my job i m able get output. As i m giving input from one file using -i command and saving tat bunch of query in one file i.e -o command but the pblm is tat i hav lots of query so i m not to able to knw whch query output is this and another is i want some command which can be used for formating my output. plz help and rply to my email id.
    plz help me
    thanks sir
    Amit Shah

  191. Hi pinal
    I have tried your help related to view all the tables of all databases of sql server 2005 but it bring results of only master data base’s table while i need to view and access all the tables of all databases how is it possibel please help

  192. Hi Pinal,

    I am new to SQL Server, just started using it since last 2 months… I have few queries, hope you don’t mind to spend your time to look into it. Thanx in advance for your reply..

    1 – How could I get the Index, given on a table using query? Like in MySQL we are using “SHOW INDEX FROM route”

    2 – How could I get Full Text Catalogs name for a database, through query?

    3 – How could I get, name of the Tables on which Full Text Index is applied, along with column name on which Full Text Index is given, through query?

    4 – We have a Central Data Base Server, we all are using it through our PC, with same login name and password. There are few procedure and user defined function in this database. Now is there any way get this procedure or functions last modified time, with the IP address of machine through which it is modified?

    Thanx,
    Ekta…

  193. Need help how to get previous row value for caluclutions in next row

    Example

    Int varchar int int int int

    ID Month Pend_Start Additions Completions Pend_End
    1 JAN08 11 12 13 14
    2 FEB08 NULL 12 13 NULL
    3 MAR08 NULL 12 13 NULL

    I need to find cummulative calculation like

    (Pens_Start+Additions)-Completion = Pend_End

    Pend_End for JAN 08 I need to use it as Pend_Start for FEB 08……then so on.

    Please help.

    Krunal

  194. hi there
    i want to import some table from access file(mdb) info my SQL Server 2005 DB.because some of my biz Requrements, i can`t use, import/export wizard in SQL Server, and must use SQL Script.
    please help me or sent me some page that show how i can do it
    best regard
    bye

  195. Hi Krunal,

    I hope following could hepl you out…

    ——————————————————-
    ;WITH TMP(ID, Month, Pend_Start, Additions, Completions, Pend_End) AS
    (
    SELECT ID, Month, Pend_Start, Additions, Completions, Pend_End
    FROM tmp1
    WHERE ID = 1
    UNION All
    SELECT t.ID, t.Month, d.Pend_End, t.Additions, t.Completions, d.Pend_End + t.Additions + t.Completions
    FROM tmp1 t, TMP d
    WHERE t.ID = d.ID+1
    )
    SELECT ID, Month, Pend_Start, Additions, Completions, Pend_End FROM TMP OPTION (MAXRECURSION 0);
    ;
    ———————————————————-

    The only problem with this approch is your ID should not break in between, and I am considering it starts with 1 and goes like 2,3,4,5,6….

  196. Hi Krunal,

    You can use Self Join to get rows with another row like:

    SELECT A.*, B.*
    FROM TableName A
    LEFT JOIN TableName B ON A.ID = b.ID-1

    This way you will get the result as you want.

    Let me know it it helps you in any way.

    Thanks,

    Tejas

  197. Is there any function in MS SQL server which can replace the regular expression with required text? For e.g. if the text contains \n [enter] in between, then how can I search and replace \n[enter] with ‘.’?
    Please help?

  198. Our SQL Server 2003 configuration is 12GB RAM Intel Xeon CPU E5310 running at 1.60GHz. OS is Microsoft Windows server 2003, Standard x64 edition with SP2. It’s a dedicated SQL server and no other software is installed on it.
    We have third party software , Idera to monitor sql servers.
    The problem is Idera always shows the memory utilization on this server to be critical, i.e. more than 90%. I guess this is due to Windows server tendency to reserve all OS memory for SQL server (if it is installed on that server) and dynamically allocate the required memory for other applications on demand basis. So in this case, that server will always show that high memory utilization on it.

    Can we find out the actual memory used by the SQL Server?
    i.e. is there any way, we can conclude that present memory on SQL server is not adequate and need to upgrade.

  199. Dear Pinal,

    I am facing a problem with connecting it with the Sql server 2005. Actually the problem is that i have newly installed the Sql Server 2005 and already have installed Sql Server 2000 on my pc. Now when i trying to connect with Sql Server 2005 it get connected me with Sql Server 2000 database. Any help greatly appriciated.

    Regards,
    Rupesh Maheshwari

  200. I have a tabe articles :

    CREATE TABLE [dbo].[articles](
    [articleid] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [sdesc] [varchar](250) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
    [filepath] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [sortorder] [int] NULL,
    [endeffdt] [datetime] NULL,
    CONSTRAINT [PK_articleid] PRIMARY KEY CLUSTERED
    (
    [articleid] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    Now when I execute following query :
    “select name + sdesc from articles”

    It gives me following error,
    “Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.”

    Is there any way to solve this error without changing collation of columns?

  201. Hi,

    Myself Shrikant v Sakpal.
    Working with one of the leading MNC as an IT-Asssociate(DATABASE). I want to improve my SQL server 2005 or 2008 developers as well as admin skills.I can also opt for certification program.
    Kindly suggest where will i get the excellent Training and certification Assistance in the Central region of Mumbai(NEAR THANE).

    Regards,
    Shrikant S

    Please feel free to mail me your suggestions….

  202. Hi Pinal,
    I am trying to disable table locks in sql server 2005 by enabling trace flag 1211, but still I see table locks when I query syslockinfo table while executing a SP.

    Please help me in disabling table locks at DB level(as oppose to query level).

    Thanks,
    Anjali.

  203. Hi

    I want to lock the table in a database using Microsoft SQL Server Management, where the client should not be able to view the table structure or any the contents of the table (ie) the client should not be able to view the table structure.

    Please help me

    Regards
    Prity.R

  204. Hi Pinal,
    I am a newbie to Sql Server.
    The query I am looking to create is with sql server 2005
    Below is a sample TestData table:

    Batch Color Value
    8440 Blue 99
    8440 Red 89
    8440 Green 79
    8441 Black 48
    8442 Blue 98
    8442 Red 78
    8443 Purple 65
    8443 White 91

    I have the following successful sql statement:

    Select Batch + ‘,’ + Color + ‘,’ + convert(varchar, Value) from TestData

    which produces

    8440,Blue,99
    8440,Red,89
    8440, Green, 79
    8441,Black, 48
    8442,Blue,98
    8442, Red, 78
    8443,Purple, 65
    8443, White,91

    How do I construct an sql statement to generate following results? (same as above but in one row for each batch)

    8440,Blue,99,Red,89,Green, 79
    8441,Black, 48,
    8442,Blue,98,Red, 78
    8443,Purple, 65,White,91

    Thank you in advance,

    Elle

  205. Hi Elle,

    Please find the solution:

    DECLARE @t AS TABLE(
    Batch INT,
    Color VARCHAR(50),
    Value INT
    )

    INSERT INTO @t
    SELECT 8440, ‘Blue’, 99
    UNION ALL
    SELECT 8440, ‘Red’, 89
    UNION ALL
    SELECT 8440, ‘Green’, 79
    UNION ALL
    SELECT 8441, ‘Black’, 48
    UNION ALL
    SELECT 8442, ‘Blue’, 98
    UNION ALL
    SELECT 8442, ‘Red’, 78
    UNION ALL
    SELECT 8443, ‘Purple’, 65
    UNION ALL
    SELECT 8443, ‘White’, 91

    ;with a AS(
    SELECT ROW_NUMBER() OVER(PARTITION BY Batch ORDER BY Batch) AS RowId,
    *
    FROM @t
    )
    SELECT SUBSTRING((select (‘,’ + CAST(b.Batch AS VARCHAR)+ ‘,’ + b.Color + ‘,’ + CAST(b.Value AS VARCHAR))
    from a b
    WHERE b.Batch = a.Batch
    FOR XML PATH(”)
    ) ,2,1000)

    FROM a
    WHERE RowID = 1

    Let me know if it helps you in any way.

    Thanks,

    Tejas

  206. Hi Pinal… Hi have a problem with two sqlserver instance (2000 and 2005) I have a function on first istance(2000), I try to call it from the second instance… but I’ve got this error “The multi-part identifier “sqlserver.DBtest.fn_test could not be bound.”
    How can I solve this?

    Thanks!

    Gus

  207. hi pinal,
    i want to develop messaging system like Facebook messaging system for my application ..
    it should be in Clild parent form and it also display all history for that message ..
    i created this table for my messaging system..
    i.e
    Message_id int no 4 10 0 no (n/a) (n/a) NULL
    Message_Subject varchar no 500 yes no no SQL_Latin1_General_CP1_CI_AS
    Message_Body text no 16 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
    Message_from int no 4 10 0 yes (n/a) (n/a) NULL
    Message_to int no 4 10 0 yes (n/a) (n/a) NULL
    Message_Isread_From bit no 1 yes (n/a) (n/a) NULL
    Message_Isread_to bit no 1 yes (n/a) (n/a) NULL
    Message_Isdelete_from bit no 1 yes (n/a) (n/a) NULL
    Message_Isdelete_to bit no 1 yes (n/a) (n/a) NULL
    Message_from_vdate datetime no 8 yes (n/a) (n/a) NULL
    Message_to_vdate datetime no 8 yes (n/a) (n/a) NULL
    Message_from_ddate datetime no 8 yes (n/a) (n/a) NULL
    Message_to_ddate datetime no 8 yes (n/a) (n/a) NULL
    Message_Created datetime no 8 yes (n/a) (n/a) NULL
    Message_Parent int no 4 10 0 yes (n/a) (n/a) NULL
    but i have one issue that how to maintain parent child relational

  208. @ergus

    Use four Part Name.

    Correct four Part Name
    Format : Server_Name.Database_Name.Object_Owner_Name, Object_name

    So four part name in your query will be changed to this,

    sqlserver.DBtest.object_owner_name.fn_test

    @Jayesh L Lolariya

    Parent Child Relation is created by creating foriegn Key on child Table.

    Create a primary key or unique key on the key column in Parent table and then create a forign key on key column of child table referencing primary key column of parent table.

    ~ IM

  209. I have a query like this

    both tables are indexed on ani and the timestamp column

    SELECT *
    INTO CWC1
    FROM CIB A
    LEFT JOIN CVT B
    ON A.ANI = B.ANI
    AND A.TIMESTAMPCREATION BETWEEN B.TIMESTAMPCREATION AND DATEADD(S, 120, B.TIMESTAMPCREATION )

    It runs for too long – over 5-6 hours.

    I noticed one thing that if I split the CVT table into two parts – of about equal records and do the above query.
    One part runs for 30-40 min and the other runs for 4-5 hours.

    is there a way to figure out why this other parts runs for too long??

  210. Hi Pinal,
    I am searching for Datawarehouse specific sql operators.
    Can you please tell me which are sql operators specifically used for Datawarehouse operations.

    Regards,
    Girish

  211. Hello Sir,

    Could you please explain the operation of Distributed partition Views or where i can find the source for this.

    Regards.
    Sanjay

  212. Hi

    Col1=’Test’
    Col2=’Value’

    Select Col1+ ‘ ‘+Col2

    Output will be Test Value

    But i want out one below the other

    like

    Test
    Value

    How to do it at the query level ?

    Please can u help me out.

    Regards
    Mohammed Irfan

  213. Hi
    Thanks for the quick reply..

    Actually i dont wont to do it like this the query is very huge i cant use the union all for the query.

    Ex: i have string ‘Hello world’ in one column only

    i want output like:
    Hello World ..

    is there any new line chrecter in SQL 2005 using which i can split it into two line ?

  214. Hi
    Thanks for the quick reply..

    Actually i dont wont to do it like this the query is very huge i cant use the union all for the query.

    Ex: i have string ‘Hello world’ in one column only

    i want output like:
    Hello
    World

    is there any new line chrecter in SQL 2005 using which i can split it into two line ?

  215. what is the difference between two
    print getdate()
    select getdate()
    i want to select date in yy/mm/dd format and assign to a datetime varibale but the output is like Apr 11 2009 3:16PM

    plz reply ASAP

  216. @ Irfan

    select substring ( ‘Hello World’, 1, charindex (‘ ‘ , ‘Hello World’) -1) Output_Column
    Union
    select substring ( ‘Hello World’, charindex (‘ ‘ , ‘Hello World’ )+1 , (len(‘Hello World’) – charindex (‘ ‘ , ‘Hello World’)) ) Output_Column

    You can replace ‘Hello World’ with column Name and add from Table Name at the end of script. It will work fine.

    @Rahul

    Print – prints message in message window. Where as
    Select – displays output in result tab.

    Select can be used to assign a value to variable.

    if you want assign a datetime value to variable then do this,

    declare @DateVar varchar (10)
    select @DateVar = convert ( varchar(10), getdate (), 101)
    print ‘Print – Prints this Message in Message Tab’
    print @DateVar
    select ‘Select – Select is Displayed in Results Tab ‘
    select @DateVar Ouput_Column

    ~ IM.

  217. Hi there,
    Can you please tell me what will be the best way to search for the string in the where clause without using LIKE clause.
    eg. select * from test where ____ (compare characters or something) simple search without using like.

    Thanks
    Sam

  218. @ Sam,

    If you have a full text Index build on that column for that table, then answer is yes,

    You can use Key word contain.

    This is much powerful than Like.

    You can see a simple example here.

    http://www.databasejournal.com/features/mssql/article.php/3441981/Full-Text-Search-on-SQL-2000-Part-1.htm

    If you do not have a full text search index or do not want to use full text index search then try using charindex. This will work if you are searching for only one character.

    select column_name from table_name
    where column_name in ( select column_name from table_name where charindex (‘mention_character_you_want_to_search_for’, column_name ) > 0)

    Remember Full text index can be used in SQL Server 2000 and SQL Server 2005 and SQL Server 2008.

    ~ IM

  219. Hi Pinal,

    Hope u r doing well. I have one question related sql server 2005, I uploading my site on godady.com. i have authorized.net(payment gateway) code in my web service. i want to call that web service from sql server 2005. because i want to fire that web service once in a day. for that i can create sql job. is thare any way to call web service from sql server 2005. or can we directly execute authorized.net code from sql server procedure.

    Plz reply asap on my id.

    Thanks & Best Regards,
    Jiten Chudasama

  220. hi
    i am trying to insert data from one table to another table with same field name but the datatype for one field is different that is for TravelDate field in the 1st table it is Varchar(15) & in the 2nd table the Datatype of TravelDate is Datetime so when im trying to insert data from 1st table to 2nd table im getting error as
    “Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting datetime from character string.”

    when im trying to insert from this query

    insert into linkinvoicesector(InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag)
    select InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag
    from linkinvoicesectors

    i have tried even by use convert while inserting but no use still same error.
    can i have any solution regarding this problem. I would be very thankfull to u if my problem is solved

    thankq

  221. Hi Vani,

    I think field “TravelDate” contains the data which is not proper for DateTime column.

    Please find it with ISDate(“TravelDate”) and if you get 0 in any column, update that data to proper datetime format.

    Thanks,

    Tejas

  222. Hi Teja

    Thank u for giving me reply.I have tried the ISDate(”TravelDate”) but the time is entering as 1900-01-01 00:00:00.000 which is not correct.
    Yes the date formats in the 1st table is not in correct format. for some of them its like eg:- 20NOV, 03Jan08, 25December2006, 19/05/08
    So now i have to convert the dates while inserting the data from 1st table to 2nd table.if the date is not in correct format then it can enter as null there.Is there anyway to convert the dates of different formats to datetime format while inserting.

    Thanks
    Vani

  223. Hello Imran

    my problem is that i am getting month,year saparately in
    S.P as parameter and conacat it like
    select @DateVar = convert ( varchar(10),@ForYear + ‘/’ + @ForMonth + ‘/’ + 1, 101)

    Error is:Conversion failed when converting the varchar value ‘/’ to data type int.

    plz help me

  224. hi,
    I have a probelem in SQL server 2005.
    I have made a Sql server Agent JOB, but when it fails it desnot rollback the changes made in the database.

    For Example: It has 10 steps but if it fails on 5th step then database remains in that state only.

    Is it possible if it fail on step 5 it rollback the changes made by it in the database.i.e., Can it bring the database in state before the start of step 1.

  225. My “Web Server” is in UK and “Database Server” is in US. The problem I am facing is, using GETDATE() function the the date and time I recived is of US. I need it to be UK time.
    Is there any way, I can change timezone setting for DB? Or any other alternative for this?

    Thanks
    Ekta

  226. Hi
    I want to know about database designing process .What points should keep in mind when we are going to design a new data base.

  227. Hi,
    I have a very simple query and it seems valid to me but it is not working and giving a syntax error. I looked on different sites but I couldn’t find anything. And finally have decided to ask you.

    SELECT ISNULL((Select Id from Student Where StudentId = 1), (EXEC(’SELECT Id From Student Where StudentId = 0′)))

    If we run only EXEC(’SELECT Id From Student Where StudentId = 0′) then it works. But whole query togethere doesn;t work.

    Please let me know what should I use?

    Thanks,
    Hitesh Savalia, MCAD

  228. Hi Hitesh,

    Try following
    SELECT ISNULL((Select Id from Student Where StudentId = 1), (SELECT Id From Student Where StudentId = 0))

  229. I have a table, with field
    id, pid <-refers to existing id, name, type,order.

    This is a table used to create the main menu for the application.

    how do we write a sql query to display the output in a tree manner.

    ex:
    id; pid; name; type; order
    10; 0; mnu100; 1; 1
    20; 0; mnu200; 1; 2
    30; 0; mnu300; 1; 3
    11; 10; mnu110; 2; 1
    12; 10; mnu120; 1; 2
    13; 12; mnu121; 2; 1
    14; 20; mnu210; 2; 1
    15; 30; mnu310; 2; 1
    16; 12; mnu122; 1; 2
    17; 16; mnu123; 2; 1

    should be displayed as a tree,

  230. Hi Pinal

    Hope you are doin well..I am daily reader of your blog.I just want to know how to join your SQL Server Ahmedabd User group..I want to be member of your group..Let me know
    bye

  231. Hi gpshiburaj (Is this your name?),

    Hope following could help you, just change table and field name as per your requirement. And let me know if it work?

    ———————————————————
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    CREATE FUNCTION [dbo].[FN_CategoryTree]()
    RETURNS
    @categorytree TABLE
    (
    — Add the column definitions for the TABLE variable here
    categoryid bigint,
    parentid bigint,
    name varchar(250)
    )
    AS
    BEGIN
    DECLARE @categoryid bigint

    DECLARE parentid_cursor CURSOR FOR
    SELECT categoryid
    FROM category
    WHERE parentid = 0

    OPEN parentid_cursor
    FETCH NEXT FROM parentid_cursor INTO @categoryid
    WHILE @@FETCH_STATUS = 0
    BEGIN
    ;WITH cattree(categoryid, parentid, name) AS
    (
    SELECT categoryid, parentid, name
    FROM category
    WHERE categoryid = @categoryid
    UNION ALL
    SELECT c.categoryid, c.parentid, c.name
    FROM category c
    INNER JOIN cattree d
    ON c.parentid = d.categoryid
    )
    INSERT INTO @categorytree
    SELECT categoryid, parentid, name FROM cattree;

    FETCH NEXT FROM parentid_cursor INTO @categoryid
    END
    CLOSE parentid_cursor
    DEALLOCATE parentid_cursor

    RETURN
    END

    ———————————————————

  232. Thanks Ekta,

    I am able to achieve exactly what I require with the function. I am not clear with the “with” (CTE) that you have used, but that is not a problem, I will read the BOL to get more information on that. Thanks for giving me a good lead.

    And my name is Shiburaj G Purushothaman.

  233. Hello Pinal Dave,

    I am a frequently used your blog for my solutions.

    I have a doubt regarding the timeout for connection in sqlserver 2005.
    Actually, what is the Default Timeout for connections string
    if i am not mentioned,

    And when i give 40 in ConnectionTimeOut is it taken as Seconds or Minutes.. I am very confuse in this scenario..

    Can u tell me regarding this with full Explanation.

    Thanks & Regards
    Ravikumar

  234. Sir

    Hope u r fine. Am a beginner in SQL and interested in becoming a fulltime DBA. Though I know something in SQL (both query & enterprise manager) they are very basics only. So please advice me what should I do and from where should I start. Also for any of my doubts now am using your tips and site for refference and find it as ONE STOP SHOP for sql related doubts.
    Can you please tell me the ways to contact you and attend any course if you are conducting. Also please advice me whether you have any books written by you.

    Hopin for your earnest reply.

    Thank you in advance
    Chakravarthy.

  235. Hello Sir,

    I have to develop keyword search..Requirement for this search is:
    As Example Keyword is : test of travel

    Combination of Keyword is :
    1)test of travel
    2)test
    3)travel

    Now result should display in order like “test of travel” match case display first & then after other keyword match cash list.

    This Search applies on multiple tables and fields.

    Can you please guide me what kind of query will work in this keyword search?

    Thanks in advance,

  236. Hi Pinal,

    I am very impressed with your work. I’d like to be more familiar with SQL Server via this blog.

    I have this scenario in my web app.

    In my application the database is year dependent. i.e. the database is meant to be used for current financial year only. When the financial year ends, the database needs to be recreated with new name (e.g. if current year database name is DB2009 then for next year it should be DB2010)

    Another situation is, based on certain conditions I need to carry forward some data to new database (like dues of certain customers etc.)

    How do I do it?

  237. We are developing a multi language website. The problem I am facing is, if I am directly adding japanish character in a table it is being displayed properly on the website.
    But if I am adding the record using the insert statement like following, the characters are converted to question mark.
    INSERT INTO product (code, name, sname) VALUES(‘aaaa’, ‘ようこそゴールデン・ツアーズへ!’, ‘ようこそゴールデン・ツアーズへ!’)

    If any one has any idea, please help me…

    Thanx…

  238. Hello Sir,

    I am working on transactional replication of SQL 2008 over the internet. But I am getting error when try to add a subscription over the internet when I give the login details with server name\ instance name then it is not connecting when I give only instance name then it iwll ask me for the server name and instance name.

    With the same login information when I started the management studio and try to connect to the server over internet then it is connecting properly.

    Please help me in this scenario.

    One more question when I change the server and try with another server and add a subscription then while starting the log reader agent at publisher it display the error

    “The process could not execute ‘sp_replcmds’ ”

    I am using SQL 2008 server 64 bit enterprise edition and Windows 2008 Server 64bit as OS.

    Thanks.

    Regards,
    Vinod Chakote

  239. Well I got the solution to above problem. This is just to help if any need it…

    What I have to do is, have to mention N before inserting and retrieving column values. So now the above query will look like,

    INSERT INTO product (code, name, sname) VALUES(’aaaa’, N‘ようこそゴールデン・ツアーズへ!’, N‘ようこそゴールデン・ツアーズへ!’)

    And while retriving the data it should be like,
    SELECT * FROM product WHERE name = N’ようこそゴールデン・ツアーズへ!’

    But I have one more question, using following function I am not getting proper result.
    Note : Full text Index is applied on code, name, sname fields.

    CREATE FUNCTION [dbo].[FN_SearchProduct] (@searchstr nvarchar(50))
    RETURNS TABLE
    AS
    RETURN (
    SELECT productid
    FROM product p
    WHERE freetext(*, @searchstr)
    )

    I can not use N infront of @searchstr, it gives error. Please let me know if any one has any idea.

    Thanx…

  240. Ekta

    Try converting “@searchstr” to “convert(nvarchar(50),@searchstr)”

    SELECT productid FROM product p WHERE freetext(*, @searchstr)
    to
    SELECT productid FROM product p WHERE freetext(*, convert(nvarchar(50),@searchstr))

    Convert it explicitly

    hope this will solve the issue

  241. Hi Ravikumar

    Default connection Timeout is 60sec.
    ConnectTimeout property specifies the connection time-out period in seconds only.

    Thanks

  242. hi Ekta

    Try converting “@searchstr” to “convert(nvarchar(50),@searchstr)”

    SELECT productid FROM product p WHERE freetext(*, @searchstr)
    to
    SELECT productid FROM product p WHERE freetext(*, convert(nvarchar(50),@searchstr))

    Convert it explicitly

    hope this will solve the issue

  243. I have table employee.

    Eid
    18241
    18245
    18246
    18247
    18248
    18249
    18250
    18261
    18262
    18263
    18264
    18265
    18266

    I want output as follows (in range)

    18241 1841
    18245 18250
    18261 18266

  244. Hi Sanjay…

    Thanx for your reply. But it’s not working here @searchstr variable is already of nvarchar type and the value I am storing in it is also of nvarchar. The thing that I need is to apend N infront of the value of @searchstr.

    Right now the
    @searchstr = ‘エアコン付きの快適な大型バス使用’

    Actually which should be like
    @searchstr = N’エアコン付きの快適な大型バス使用’

    But I am not getting any way to convert it like this….

    Well using convert function within Freetext is giving an error , I also tried it in following way…

    EXEC(‘SELECT productid, code, name, sname
    FROM product p
    WHERE freetext(*, ‘ + convert(nvarchar(50),@searchstr) + ‘)’ )

    Any ways, thanx again for your reply…

  245. I have table employee.i have one table only
    Eid
    18241
    18245
    18246
    18247
    18248
    18249
    18250
    18261
    18262
    18263
    18264
    18265
    18266
    I want output as follows (in range) table format like below

    Start End Total unit
    18241 18241 1
    18245 18250 6
    18261 18266 6

  246. @Amit

    Is this what you wanted?

    WITH
    Employee(Eid)
    AS
    (
    SELECT 18241 UNION ALL
    SELECT 18245 UNION ALL
    SELECT 18246 UNION ALL
    SELECT 18247 UNION ALL
    SELECT 18248 UNION ALL
    SELECT 18249 UNION ALL
    SELECT 18250 UNION ALL
    SELECT 18261 UNION ALL
    SELECT 18262 UNION ALL
    SELECT 18263 UNION ALL
    SELECT 18264 UNION ALL
    SELECT 18265 UNION ALL
    SELECT 18266
    ),
    Grouper(RN, Eid)
    AS
    (
    SELECT
    ROW_NUMBER() OVER(ORDER BY Eid),
    Eid
    FROM
    Employee
    WHERE
    NOT EXISTS
    (
    SELECT
    *
    FROM
    Employee E2
    WHERE
    E2.Eid = Employee.Eid – 1
    )
    UNION ALL
    SELECT
    RN,
    Employee.Eid
    FROM
    Grouper,
    Employee
    WHERE
    Employee.Eid = Grouper.Eid + 1
    )
    SELECT
    MIN(Eid) Start,
    MAX(Eid) “End”,
    COUNT(*) “Total Unit”
    FROM
    Grouper
    GROUP BY
    RN

  247. Hi Pinal,
    I am trying to connect SSRS by SSMS with windows authentication on remote server but I am getting following error
    The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)
    I am able to connect remotely using basic authetication through SSMS. Is there any place to look why am I unable to connect with windows authentication?
    Thanks
    Sanjeev

  248. Thanks Brian Tkatch for solution.

    But this does not solve my problem.
    You have taken static data but my data is dynamic not fixed
    like
    18241
    18245
    18246
    18247
    18248
    18249
    18250
    etc

    so i want solution in SP in MSSQL 2000

  249. Hi Pinal,

    we are a team of developers working in my team. Being a small organistaion we dont have a DBA. so everybody has access to the procedures in the servers.

    we do maintain a copy of the procedures where is is modified and commented for reference. But some developers directly modify the procedures on the server using the sp_helptext command.

    I want to restrict the use of sp_helptext by every body except the administrators.

    How can I accomplish this?

  250. Thanks Brian Tkatch for solution.
    But this solution does not solve my problem.
    I want this solution in MSSQL2000.

    Also data is dynamic not static as you have used

    SELECT 18241 UNION ALL
    SELECT 18245 UNION ALL
    SELECT 18246 UNION ALL
    SELECT 18247 UNION ALL
    SELECT 18248 UNION ALL
    SELECT 18249 UNION ALL
    etc…..

    Please provide updated solution
    Thanks in advance.
    Regards,
    AmitMakwana

  251. hi ekta

    I think there is no way to prefix ‘N’ to a sql variable inside T-SQL Script

    Can i know from which instance your passing the japanish data ( asp.net?), If so, no need to prefix ‘N’, .net framework by itself handles passing of data by Prefixing N automatically.

    Meanwhile I’ll try to find the solution.

  252. Hi AmitMakwana,

    Please create the following procedure.

    /***************************Start Procedure**********************/
    CREATE procedure usp_findrange
    as

    declare @ItemNumber int
    declare @total int
    declare @temp int
    declare @initval int
    declare @temp1 int
    set @total = 0
    set @temp = 0

    if exists (select * from sysobjects where id = object_id(N’[range_tbl]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
    drop table [range_tbl]
    create table range_tbl (fromval int, toval int, total int)

    DECLARE c1 CURSOR local FOR
    /* You can change table name and set field on which you want to perform */
    select Eid from employee
    OPEN c1

    FETCH NEXT FROM c1 INTO @ItemNumber

    WHILE @@FETCH_STATUS = 0
    BEGIN
    if @total = 0
    begin
    set @initval=@ItemNumber
    end
    if @temp 0
    begin
    set @temp1=@temp+1
    if @temp1=@ItemNumber
    begin
    set @temp=@ItemNumber
    set @total=@total+1
    end
    else
    begin
    insert into range_tbl(fromval,toval,total) values(@initval,@temp,@total)
    set @temp=@ItemNumber
    set @total=1
    set @initval=@ItemNumber
    end
    end
    else
    begin
    set @temp=@ItemNumber
    set @total=@total+1
    end

    FETCH NEXT FROM c1 INTO @ItemNumber

    END
    insert into range_tbl(fromval,toval,total) values(@initval,@temp,@total)

    CLOSE c1
    DEALLOCATE c1
    select * from range_tbl
    GO
    /***************************End procedure**********************/

    With Best Regards,
    RupeshPrajapati

  253. @Amit

    WITH
    Employee(Eid)

    just provided sample data. Remove that CTE and change the FROM in the other CTE (Grouper) to refer to the actual TABLE.

  254. Hi,

    I know there are tools that squeeze SQL backup on the air. But I am looking for a way to force SQL server to produce smaller SQL backups or compressed backups? Is there a coding approach?

    Thanks,
    Farhad

  255. Hello,
    how is timestamp in mysql implement in ms sql server….

    ms sql server timestamp puts binary data… i hope you can give me enlightenment here…

  256. Hi Pinal,

    I am working as analyst and currently working on SQL. Its new for me. I want to load data from excel into SQL Server 2008. Openrowset/Opendatasource commands has been turned off by the admin for security reasons. is there any other way of doing it? i tried SSIS but the server i want to load onto is a remote one. please help me with this

  257. Hi Pinal,

    I am posting it here again:

    I got error 15401 when I tried to manually change the Window group name after the physical server got renamed.

    The Microsoft Article ID: 324321 (http://support.microsoft.com/kb/324321) seems not help with my problem, I would appreciate your help :), thank you!

    After renaming the server, I use sp_dropserver and sp_addserver to bring SQL in sync with the new server name. Then I restart SQL Server. Everything seems to work fine. However, under Security -> Logins there are 3 groups that are prefixed by the old server name and also contain the old server name within their name (for example: oldservername\SQLServer2005SQLAgentUser$oldservername$MSSQLSERVER)

    Can you please advise how to fix it?

    I found out lots of useful information from your website.
    Thank you very much for your help!

    Alice

  258. Hi Pinal,

    Should I change them through Computer Management -> System Tools -> Local Users and Groups -> Groups ?

    I have local admin right.

    I see all of the SQL Serevr 2005 related group names are still with the old servername embedded.

    Many thanks!

    Alice

  259. Pinal,
    Thanks for all the help.

    I am having a problem restoring sql server 2005 database backup. the file name is myProdDB.dat.

    In SQL Server 2008 Management Studio, I created a database, myProdDB using the wizard. On database right clicked and Tasks and Restore from File group and selected the above mentioned .dat file.

    I selected the replace in options, selected the database as you mentioned in one of your post.

    I still am getting the 3154 error, that says the backup file contains more than 1 database blah blah.

    I did search in the internet and I am not able to find a good solution for this.

    I would really appreciate your help..
    Thanks
    Srini

  260. HI Pinal,

    i am trying to truncate the log file in sql server 2000….

    i m getting the message as [SQLSTATE 01000] (Message 0) Cannot shrink log file 2 (PER_LOG)
    because total number of logical log files cannot be fewer than 2. [SQLSTATE 01000] (Message 9006)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)

    please help to correct this

  261. HI Pinal,

    the following is the Script that is setup has a Job in SQL Server to clear all the log of all the Application DB’s

    DECLARE @DBNAME VARCHAR(100),
    @Query NVARCHAR(4000),
    @SqlParams Nvarchar(4000),
    @LogFileName VARCHAR(100)
    DECLARE DBLog CURSOR FOR
    SELECT LTRIM(RTRIM(convert(varchar,NAME))) FROM MASTER..SYSDATABASES WHERE DBID > 5 OR name = ‘tempdb’
    Open DBLog
    FETCH NEXT FROM DBLog INTO @DBNAME WHILE @@FETCH_STATUS = 0
    BEGIn SET @Query = ‘BACKUP LOG ‘ + ltrim(rtrim(@DBNAME)) + ‘ WITH TRUNCATE_ONLY’
    PRINT @Query EXEC (@Query)
    select @SqlParams =’@LogFileName varchar(100) out ‘
    select @Query = N’select @LogFileName = ltrim(rtrim(name)) from ‘ + @DBNAME + ‘..sysfiles where fileid = 2′
    –PRINT @Query
    exec sp_executesql @Query,@SqlParams, @LogFileName OUTPUT
    –PRINT @LogFileName
    SET @Query = ‘USE ‘ + @DBNAME + ‘ ‘ + char(10)+”+char(10) + ‘ DBCC SHRINKFILE (”’ + @LogFileName+ ”’,1)’
    — PRINT @Query
    EXEC (@Query)
    FETCH NEXT FROM DBLog INTO @DBNAME END close DBLog deallocate DBLog

  262. Hi,

    i have couple of questions in SSIS,

    1. How to get the current configuration file name and path from the Script task component

    i am running the package thru DTEXEC /config . Is there any way to get this info inside the package. Also i need to get the package path.

    2. How to get the current package tasks list(in execution order) in the script task…

    i am able to get it for other package by creating the object instance.. but that too gives the tasks in alphabetical order.. not in the execution order.

    would appreciate if you could help me on this.

    Thanks.

  263. Eid Ename Parentid
    1 Amit 0
    2 Jayesh 1
    3 Mitesh 1
    6 Rupesh 2
    10 Raj 6
    12 Ritesh 1

    I have one table with child and parent relationship

    eid is autoid and parent id is parent id.

    i want output as follows(tree) in MSSQL 2000

    Amit
    -Jayesh
    -Rupesh-Raj
    -Mitesh
    -Ritesh

  264. Hi Pinal!

    I am new to this and i need to create a table with 1,000,000 entries wherein in the first column (CardNo) it starts at 000000001 and so on. It has to be sequential. The second column (Pin) must be composed of 3 random numbers from 000-999.

    Does any of you have an idea on how i can achieve this?

    I am really hoping you can give me a solution to my problem.

    Thanks a lot in advance. :)

    *I already posted this in another page but i think i posted it in the wrong topic.

  265. I Tried using the code below but it doesn’t work.
    I don’t know what code to use so i put together some of the codes i found in the internet. The first insert statements works when i separate it from the pin.
    The random number generator works when it is not inside the insert statement.
    Can somebody tell me what i am doing wrong here?

    DECLARE @Number As int
    DECLARE @NumberPin As int
    SET @Number = 000000001
    WHILE @Number < 000000003
    BEGIN

    INSERT INTO CardTemp(CardNumbers)
    VALUES (@Number)

    INSERT INTO CardTemp(Pin)
    VALUES (@NumberPin)
    SET @NumberPin = (SELECT FLOOR(999*RAND()))

    SET @Number = @Number + 1

    END

  266. – Mirei

    You can add leading zero’s into VARCHAR but not INT column. the alternate ways is to have like this.

    create table CardTemp
    (
    id int,
    CardNumbers as (right(‘00000000′ + CONVERT(varchar(10),id),10)) PERSISTED NOT NULL,
    Pin int
    )

    DECLARE @Number As int
    SET @Number = 000000001
    WHILE @Number < 000000003
    BEGIN

    INSERT INTO CardTemp(id,Pin)
    select @Number,FLOOR(999*RAND())

    SET @Number = @Number + 1
    end

    Results will be

    id CardNumbers Pin
    ———– ———– ———–
    1 000000001 99
    2 000000002 170

    Hope this helps ur problem

  267. @praveen kv

    To write a stored procedure,following syntax will help:

    CREATE OR REPLACE PROCEDURE[Schema.]
    ( {IN,OUT,IN OUT} ,…) {IS, AS}
    declarations;
    declarations;

    BEGIN
    ;
    EXCEPTION
    ;
    END;

    Jeet.

  268. To write a stored procedure,following syntax will help:

    CREATE OR REPLACE PROCEDURE[Schema.] ProcedureName
    ( Argument {IN,OUT,IN OUT} Data Type,…) {IS, AS}
    variable declarations;
    constant declarations;

    BEGIN
    PL/SQL subprogram body;
    EXCEPTION
    Exception PL/SQL block;
    END;

    Jeet.

  269. Hi

    I have created a stored procedure that monitors a table with “posttime” column.

    this procedure takes as input “date” from user and it should return all records on that date .

    post time uses getdate function to enter date value in it.

    Problem is that i only want user to enter date part of getdate and procedure shd return all values with time atamps on that date ??

    can you help ?

    My sample script is

    create procedure check_DDL
    @date datetime
    as
    select * from ddl_log
    where postTime = @date
    Go

    • It should be

      create procedure check_DDL
      @date datetime
      as
      select * from ddl_log
      where
      postTime >= dateadd(day,datediff(day,0,@date),0)
      and
      postTime < dateadd(day,datediff(day,0,@date)+1,0)
      Go

  270. @Zubaria,

    u can use the Convert function in ur procedure to remove the time from the date

    create procedure check_DDL
    @date datetime
    as
    select * from ddl_log
    where cast(convert(varchar(10), postTime,101)as datetime)= cast(convert(varchar(10),@date,101)as datetime)
    Go

    Hope this will help ur query

  271. select * from v$session where upper(program) like ‘%APP%’;

    The above statement is oracle database. Does anyone have an idea how do i use in sql ?

    Thank you

  272. @Tejal.

    SQL Server by default is not case sensitive, so you need not use Upper function, You can use same script with little modification.

    Select *
    From V$session
    where Program like ‘%APP%’

    This should work.

    ~IM.

  273. Dear Pinal,
    I regullary visit your blog and I find it very useful. I am living in Serbia, so it would be very difficult to visit yours Gandhinagar SQL Server User Group Meetings. It would be very useful if your meetings are recorded with camera and then published here or somewhere else, or if you could attach all presentations materials from meetings. Just keep up with your excellent work !!!
    Sincerely
    Aleksandar Joksimovic

  274. @ganesh,

    to select a record which has the row number 5 in the database:
    It is not possible without making reference to some sequence field.Unless you specifically design a “row number” or other sequence field into the table, the rows have no inherent sequence.So if there is a field that you can sequence the records on, you can ask for record number 5.
    Jeet.

  275. Hi pinal

    1.

    I have SQL server 2005. In my network anothere system SQL server 2008 is installed. Can I connect to that system?

    2.

    If first one not possible, Can I take backup of 2008 server and dump in 2005 server?

    Please replay.

  276. hi
    i have two tables

    first tabe

    Productfamilyid productfamilyname

    1 fam1
    2 fam2
    3 fam3
    4 fam4
    5 other
    secon table

    ID Productfamilyid Productname

    1 1 a
    2 1 b
    3 2 c
    4 2 a
    ..


    10 5 x
    11 5 y
    12 5 z

    i want output like this

    productfamilyid productfamilyname

    1 fam1
    2 fam2
    3 fam3
    4 fam4
    5 x
    5 y
    5 z

    note:-instead of others i want to display the productname based on that others id

    could u please help me
    thanks
    Prasad Gopathi

  277. Hi pinal

    I am not able to connect to sql server 2008 from sql server 2005. I am getting follwoing error:

    TITLE: Connect to Server
    ——————————

    Cannot connect to PC5\SQLEXPRESS.

    ——————————
    ADDITIONAL INFORMATION:

    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. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    Any idea!!! please.

  278. @Prasad

    Here’s one method:

    WITH
    first_tabe(Productfamilyid, productfamilyname)
    AS
    (
    SELECT 1, ‘fam1′ UNION ALL
    SELECT 2, ‘fam2′ UNION ALL
    SELECT 3, ‘fam3′ UNION ALL
    SELECT 4, ‘fam4′ UNION ALL
    SELECT 5, ‘other’
    ),
    secon_table(ID, Productfamilyid, Productname)
    AS
    (
    SELECT 1, 1, ‘a’ UNION ALL
    SELECT 2, 1, ‘b’ UNION ALL
    SELECT 3, 2, ‘c’ UNION ALL
    SELECT 4, 2, ‘a’ UNION ALL
    SELECT 10, 5, ‘x’ UNION ALL
    SELECT 11, 5, ‘y’ UNION ALL
    SELECT 12, 5, ‘z’
    )
    SELECT
    Productfamilyid,
    productfamilyname
    FROM
    first_tabe
    WHERE
    productfamilyname ‘other’
    UNION ALL
    SELECT
    Productfamilyid,
    productname
    FROM
    secon_table
    WHERE
    EXISTS
    (
    SELECT
    *
    FROM
    first_tabe
    WHERE
    first_tabe.Productfamilyid = secon_table.Productfamilyid
    AND first_tabe.productfamilyname = ‘other’
    );

  279. 1. have two tables
    First table
    Productfamilyid productfamilyname
    1 fam1
    2 fam2
    3 fam3
    4 fam4
    5 other
    second table
    ID Productfamilyid Productname
    1 1 a
    2 1 b
    3 2 c
    4 2 a
    ..


    10 5 x
    11 5 y
    12 5 z
    I want output like this
    productfamilyid productfamilyname
    1 fam1
    2 fam2
    3 fam3
    4 fam4
    5 x
    5 y
    5 z
    Note:-instead of others i want to display the product name based on that others id

  280. Thank you IM. I run the following statement against master database in sql 2005.

    Select *
    From V$session
    where Program like ‘%APP%’

    it gave me following message
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘V$session’.

    is there any sql equalvilent table for V$session ? or this table need special permission to view in sql 2005. ?

  281. hi
    I have 2 tables
    1)
    FamID FamName
    1 fam1
    2 fam2
    3 fam3
    4 Others

    2)
    prodid FamID Prodname
    1 1 A
    2 1 B
    3 2 M
    4 2 N
    5 4 x
    6 4 y
    7 4 z

    i want out put like this

    FamID FamName

    1 Fam1
    2 Fam2
    3 Fam3
    4 x
    5 y
    6 x

    note:-i wnat distinct famalies of first table
    and based on that others ID i want to display product name in one query
    like above o/p
    instead of OTHERS i want to show secong table product names
    could you please help me .

    Thanks & Regards,
    Prasad Gopathi

  282. is there any sql table or procedure to monitor web server ?

    From command line i get result whether web server/services is up or down.

    Monitoring Weblogic Web Server
    java weblogic.Admin –url $HOST:$PORT -username
    USER –password PASS GETSTATE

    any other help grealty apperciated.

  283. Hi,

    I have an issue related to report paramters

    i have a report which group by data based on 2 paramters and also from and to dates The Issue is that whenever during the report execution I do a “Select all” from the second parameter List–and when I click view reports it does not display the report. it throws an error

    Default value or value provided for the report parameter ‘ProcessNm’ is not a valid value.
    But this error is not thrown always. It is weird that sometimes it works perfectly with “Select all” the parameters.

    I assume that the above error could be occurring since it is trying to pass a comma separated parameter string into the dataset query which accepts this parameter.

    If we may think that this is a data parsing issue due to the fact that staging database has different data than development, but then it should never execute it from SSRS at all. The issue is that it execute same query with all the parameters selected sometimes.
    The interesting part is that The error mentioned above does not appear always and instead it just clears out the parameter
    Another thing observed was that if the refresh button (not view report button) continuously it displays the report correctly once and next time it throws error then again displays correctly.
    The SSRS environment on staging and development are SQL 2005, but on staging SSRS is 2005 but database is SQL2008.

    The SSRS logs shows the error which appear randomly appear while calling GetReportParamters internal function

    The same query was captured from tracer when it worked on staging and was executed and works fine from backend.

    Also there are no null values in the parameter lookup tables

    Anyone faced this situation in your report with muliple paramter “select all”
    Appreciate any help or clues towards the resolution.

  284. @Prasad,

    Firstly, Please post your question once.

    Secondly, There is no consistency in your question which I do not understand, was that a mistake or your requirements was wrong, anyways here is the script.

    Lets First create table and insert some sample data which you provided.

    create table Table1 ( FamID int , FamName varchar(20))

    insert into Table1 values ( 1, ‘fam1′)
    insert into Table1 values ( 2, ‘fam2′)
    insert into Table1 values ( 3, ‘fam3′)
    insert into Table1 values ( 4, ‘Others’)

    create table Table2 ( Prodid int, FamID int, Prodname varchar(20))

    insert into Table2 values ( 1, 1, ‘A’)
    insert into Table2 values ( 2, 1, ‘B’)
    insert into Table2 values ( 3, 2, ‘M’)
    insert into Table2 values ( 4, 2, ‘N’)
    insert into Table2 values ( 5, 4, ‘x’)
    insert into Table2 values ( 6, 4, ‘y’)
    insert into Table2 values ( 7, 4, ‘z’)

    — This is response to your first Question

    select distinct A.FamID
    , A.FamName
    from Table1 A
    where A.FamName ‘others’
    union

    select A.Famid
    ,B.ProdName
    from Table1 A
    join Table2 B on A.Famid = B.FamId and A.FamName = ‘Others’

    — This is response to your last Question

    select identity ( int , 1,1) FamID
    ,FamName
    into #DisplayResults
    from (
    select A.FamName
    from Table1 A
    where A.FamName ‘others’
    union

    select B.ProdName
    from Table1 A
    join Table2 B on A.Famid = B.FamId and A.FamName = ‘Others’
    ) X

    select * from #DisplayResults

    ~ IM.

  285. @Tejal,

    Syntax that I provided is right. But the table name you used in that query might not exists in sql server database on which you are executing that query.

    You can get details of what sql quiries are currently executing in sql server from DMV, sys.dm_exec_requests , you can refer below link to get complete script.

    http://www.sqlservercentral.com/articles/DMV/64425/

    ~ IM.

  286. @ MP.

    I dont have an answer for you at this time.

    You said in your description, you ran a trace when report was running fine from SSRS. When the same query executed on back end, it executed fine with no issues.

    Sir, why are you tracing when everything is working fine. Trace when report does not run. When it gives you error, Trace at that time. And then run the same script on database back end.

    Also, test internal function GetReportParamters with different input values.

    it would be easy for us, if you share sql of that query.

    ~ IM.

  287. Hi ,

    I have Three Tables

    1 Table:-

    ProductID ProductName

    1 Prod1
    2 Prod2
    3 Prod3
    4 Prod4
    5 Prod5
    6 prod6

    2 Table:-

    ProdFamID ProdFamName

    1 Fam1
    2 Fam2
    3 Fam3
    4 Fam4
    5 Other

    3 Table:-

    ProdTransID ProdFamID ProductID

    1 1 1
    2 1 2
    3 2 1
    4 3 4
    5 3 5
    6 5 4
    7 5 5
    8 5 6

    I WANT OUTPUT LIKE THIS:-

    ProdFamiID ProdFamName

    1 Fam1
    2 Fam2
    3 Fam3
    4 Fam4
    5 Prod4
    5 Prod5
    5 prod6

    NOTE:-

    i want to select distinct of ProdFamname in second table
    at the same time i want to select instead of OTHER
    i want to select productname based on 3rd table

    that means in third table Other contain three ProductID’s
    based on that other id’s i want to select Productname at the same time i want to select distinct of ProdFamname in second table

    as per above output

    pla help any one it’s urgent in my application

  288. Hi,

    I am posting this again.

    I want a database, like Access ie it can work with out client-server architecture and which has some features like indexing, views, SP.

    Where can i look upto for such DB ?

    Thanks.

  289. Hello Pinal.
    I am a frequent reader of your blog.

    I have question for you:
    Can we upgrade a production server which is on sql server 200o to sql server 2008?.

    If we upgrade, do the DTS packages work without any compatibility problems in sql server 2008 SSIS ?

    And what are the major precautions to be taken for the upgrade from 2000 to 2008?

    Thank You
    Tom

  290. Thank you IM for your help. I’m using following sql statement to monitor my application services for ERP system.

    /* Monitor Application server services.*/

    DECLARE @count INT

    SET @count =(select count(*) FROM sys.dm_exec_sessions WHERE host_name = ‘myserver’)

    /* The application services running are > 8 */
    if @count < 8
    BEGIN
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = myservermailprofile,
    @recipients = ‘test.yahoo.com’,
    @subject = ‘Application services on my server (Application Server) is Down’,
    @body =’Application services on myserver (Application Server ) is down according to the established “resource monitoring rules”.’,
    @importance = ‘High’
    END

  291. How do i find out What time of day has the most active users in our application through sql statement.

    Thank you
    Tejal

  292. Hi PinalDave
    I have seen the below mentioned in a coding standards document.
    “Ensure that large text and binary columns are listed at the end of the columns list in the SELECT statement.”
    Why we have to use like that

    Thanks in advance
    Robin

  293. Hi Pinal,

    Good to see this type of informative site which is to the point.

    I would like you to publish a artical related to SQL script versioning.

    Today I saw a Pool at http://www.SQLServerCentral.com and question is :-

    Which Version Control System do you currently use to store you database scripts?

    I am a single software developer (without any team), using VB.Net 2008 and SQLServer 2008 Express

    I hope this kind of artical will help Script programmer to maintain the version history.

    :)

  294. Hi Pinal.

    I need the procdure of how to do replicate tables from Oracle Database to SQL Server 2008. (Transactional Replication)

    What is required for the replication to take place.
    Can you list me the procedures to do for establishing the Replication.

    Have A Nice One
    Thank You
    Tom

  295. Hi Pinal, I like you solution for the “Challenge 4″, actually all good information on the blog, but you solution actually fails when A,B or C have alphanumerics on it except when in the first byte. Try ‘1AB-2C-3DEF’.
    -Cleber.

  296. @ ParameJeet

    Very Good Question.

    I been looking for this information too, this is what I have concluded, I did not do any research but I been hearing from my colleagues.

    1. Third Party Client tools for SQL Server like TOAD for SQL Server (I believe they provide version control for scripts) I am not sure, I heard some one saying this for Toad – Oracle.

    2. We use IBM Rational Clear Case for version control in my company.

    SQL Joke : How about storing scripts in ExecuteSQLTask in DTS package. DTS package is the only place in Sql Server which has version control feature. (Just joking).

    ~ IM.

  297. I’ve read that SQL Server 2005 supports statement-level recompile. Instead of having all queries in the stored procedure recompiled, SQL Server can now recompile individual statements.
    When I create following procedure
    SET NOCOUNT ON;
    USE Northwind;
    IF OBJECT_ID(‘dbo.useKeepPlan’, ‘P’) IS NOT NULL
    DROP PROC dbo.useKeepPlan;
    GO
    create procedure useKeepPlan as
    create table #t (a int, b char(3))

    — Make greater than 6 changes to #t
    insert #t values (1, ‘abc’)
    insert #t values (2, ‘abc’)
    insert #t values (3, ‘abc’)
    insert #t values (4, ‘abc’)
    insert #t values (5, ‘abc’)
    insert #t values (6, ‘abc’)
    insert #t values (7, ‘abc’)
    — Now reference #t
    select count(*) from #t
    go

    and run it for first time I get
    SP:Recompile
    SQL:StmtRecomplie events
    when select count(*) from #t statement is reached.
    Should there only be SQL:StmtRecomplie event withouth entire procedure recompilation?

  298. i have to combine records from two table,how can i do it,for ex:table 1 gives username,address and table 2 gives passportno then i want the results in a format username,address,passportno..plz give the reply ASAP.

  299. Thanks Imran Mohammed for your reply.

    What I meant was, I need a database engine like SQLce (compact edition) which I can deploy with my application and I don’t need to install SQL server to use the DB.

    And which has features like indexing, views.

    Hope I am not asking too much. :)

    Thanks again.

  300. Hi Pinal,

    Kindly let me know the detail concept of SQl Execution plan.

    Thanks and Regards,
    Jyoti

  301. Dear Sir

    I am create a database then create more tables in same field in more tables. How To Delete Particular Record in a database. How to use Sql Server Query Pl Reply
    me.

  302. i have create unique field in more table. how to check unique fields how to use sql server 2005 query reply me.

  303. Sir,

    I wanted to know that developing the Database. why we should create store procedure on table what is the purpose it is can you show you me this

  304. hi
    i have some probleme in that
    i want to put search engine in my site that enable users to search for jobs by write the job name and determine the job fields and cities and if this job found in my site it will be displayed and if not the message will be displayed to the user to inform them that job is not found
    pls help ,ineed the answer in these days because ihave xsame in this issue
    thanx alot

  305. Hello, i would like to ask you about Bidirectional Transactional Replication how difficult could be, if it is recommended, the version of sql server and what about conflicts?
    Thank you.

  306. Hi Pinal,

    I have small query regarding SQL 2000. We used to send mail through xp_sendmail stored procedure on Windows server 2000. But past many month job is failing:
    xp_sendmail @recipients=’abc@xyz.com’,
    @subject=’test’
    I got a fatal exception error. I tried it again straight away and it worked. Some times we need to forcefully restart mail session by xp_stopmail and xp_startmail.

    Can you please guide me for the same.

    Thanks
    Gaurang

  307. Hi Sir.

    can i use a column name in freetext predicate ?

    like here :
    select cat_name,count(*) tot from tbcat_res c,tbanswer a where freetext((a.ans_Ques,a.ans_quesdesc),’xyz’) and freetext(c.cat_key,a.ans_cat) group by c.cat_name

    please help.

  308. Hay Man,
    I am a big fun of your posts. Thanks for all the great job. You are a big help to the community.
    I have question and that is:
    How can I set up a schedule task to run a SP every 24 hours? Now, I can use a .bat file and use task scheduler of Windows or use SQL servers built in functionality if there is any.

    Many thanks

  309. @Shuaib

    SQL Server Agent service in SQL Server executes scheduled jobs.

    You can create a new job under sql server Agent. In steps for the job, you can include execute stored procedure script.

    And then schedule that job, SQL Server Agent will run that job

    IM.

  310. @Anil

    I installed the Developer Edition of SQL Server instead of the SQL Server Express edition that comes with VS2008.
    I found that the install works better if you install SQL Server first, that way there is no need to uninstall the Express edition before installing SQL Server Developer edition. I don’t recommend having both editions installed. I had no end of problems until I figured out that having two versions installed was the issue. Uninstalling VS and SQL Server Express may be necessary, but I believe it was enough just to uninstall SQL Server Express and (re)install SQL Server Developer edition.

    Jeet.

  311. @Munaf

    Stored procedures are very similar to user-defined functions, but there are subtle differences.
    Stored procedures can be used by multiple users and client programs.Utilizing them properly in our code,we can decrease the time taken in development cycle.
    Also,users can be granted permission to execute a stored procedure independently of underlying table permissions.

    For example,consider the query:
    SELECT Product, Quantity
    FROM Inventory
    WHERE Warehouse = ‘FL’

    We need to execute this query every time we need to select the Product,Quantity frm the ‘Inventory’ table without using a Stored Procedure,but when we use the stored procedure as follows,we get the same result with less execution time:

    CREATE PROCEDURE GetInventory
    @location varchar(10)
    AS
    SELECT Product, Quantity
    FROM Inventory
    WHERE Warehouse = @location

    We can execute it as given here:

    EXECUTE GetInventory ‘FL’

    Jeet.

  312. Hi,

    I have a doubt on performance of a query.

    Will the performance DETER or vary a lot if we use WITH function instead of table variable @Table in stored procedure.

    Note : I know WITH function is used for recursive queries.But In the above example there is no recursive action at all.

    So which one is better if no recursive action is there and just needed for temporarily storing the data ?

    Any help is appreciated.

    Thanks
    Sudha

  313. Hi Pinal,

    I am your frequent reader of your SQL tips & tricks.

    you have very good knowledge sharing scripts.

    Pinal is it possible to have more DBA Related Knowledge on different Link or different tab.

  314. Dear Sir,

    I am developing one application which gets data from another application.In this i am retrieving data from one database (say db1) and inserting into another database (say db2).When i again insert into the db2 from db1 that time data repetition happens which creates problem.
    And also i dont want to delete data from db2 as I am modifying it.

    Kindly suggest any solution to retrieve data from db1 to db2 without repeating it.

  315. Hello Sir,

    I am a regular reader of your articles.
    I want to write a select query to select data from an xml file.

    Please tell me……

    Thanks & Regards
    Brijendra

  316. Mr. Pinal,

    I have gone through your one of the good article for sql that is SQL Server Cheat Sheet. thats good for me as a developer reference. i want to give one suggestion that you just give overview description of each. and if possible then for functions,constrains,TSQL statements and object operation with example.

    Rgds,
    Bhavank

  317. Hi Pinal,

    Which Index takes much disk space in SQL Server DB. Please explain the reason if applicable?

    This was the question which my client for which i am not too sure about the answer.

    Please clarify at the earliest!

    Regards,
    Varun.C

  318. Hi Pinal,

    I’m doing a project in c# and the back end is sql server2000 and my question is.. in my project im saving a whole bunch of text from richtextbox control in the db..in that the end user will be formatting the text like bold,italic,and underline and changing the font size and my problem is i wasnt able retrieve the format text..its coming without the formating made by the end user…can i know wat is the issue here..im saving the data in table with text as datatype

    thank u

  319. @Brijendra,

    You can read XML values from xml file as:

    declare @doc XML
    set @doc=

    SELECT x.v.value(‘@id[1]‘,’INT’) AS OrderID,
    x.v.value(‘@ostatus[1]‘,’INT’) AS OrderStatus
    FROM @doc.nodes(‘ord/order’) x(v)

    Thanks,

    Tejas

  320. Hello Pinal ,

    Personal request that you add a ‘subscribe via email’ option not only RSS as in office RSS feed reading is blocked for me ..

    Regards

    Siddanth
    MCTS-70-431

  321. hi

    How To Return String value in sql Server 2005 as like this

    Create Procedure vLogin
    @UsrNa varchar(20),
    @Pwd varchar(20),
    @Result varchar(20) Output
    as
    if exists(Select UserName,Password from login Where UserName=@UsrNa and Password=@Pwd)
    print ‘Valid UserName’
    else
    print ‘Invalid UserName’

    Sql server Fine work but how to retrieve asp.net pl reply me

  322. Sir
    i’m software Trainee.I’m developing application with sql server as a back end.I want to ask , do i have to install complete sql server on clients machine so that he can easily work with my application or i can customize it.And also my client should be able to take back of data and zip the back files so that i can work on them in my office.

  323. I have a table like follow
    ————————————————
    DECLARE @t AS TABLE(
    ROWID int, tmpdate datetime, discount float,
    discountlevel char(1), discounttype char(1)
    )
    INSERT INTO @t
    SELECT 1, ‘2009-04-01′, 20, ‘C’, ‘T’
    UNION ALL
    SELECT 2, ‘2009-04-02′, 20, ‘C’, ‘T’
    UNION ALL
    SELECT 3, ‘2009-04-03′, 20, ‘C’, ‘T’
    UNION ALL
    SELECT 4, ‘2009-04-04′, 20, ‘P’, ‘T’
    UNION ALL
    SELECT 5, ‘2009-04-05′, 20, ‘P’, ‘T’
    UNION ALL
    SELECT 6, ‘2009-04-06′, 20, ‘C’, ‘T’
    UNION ALL
    SELECT 7, ‘2009-04-07′, 20, ‘C’, ‘T’
    UNION ALL
    SELECT 8, ‘2009-04-08′, 20, ‘C’, ‘T’
    UNION ALL
    SELECT 9, ‘2009-04-09′, 25, ‘C’, ‘B’
    UNION ALL
    SELECT 10, ‘2009-04-10′, 25, ‘C’, ‘B’
    UNION ALL
    SELECT 11, ‘2009-04-11′, 20, ‘C’, ‘T’
    ————————————————

    And the required output is like
    ————————————————
    FromDate ToDate Discount DiscoutLevel DiscountType
    ‘2009-04-01′ ‘2009-04-03′ 20 ‘C’ ‘T’
    ‘2009-04-04′ ‘2009-05-03′ 20 ‘P’ ‘T’
    ‘2009-04-06′ ‘2009-04-08′ 20 ‘C’ ‘T’
    ‘2009-04-09′ ‘2009-04-10′ 25 ‘C’ ‘B’
    ‘2009-04-11′ ‘2009-04-11′ 20 ‘C’ ‘T’
    ————————————————

    In short I need to club the rows, from where information other then date is changing.

    What could be the best way to achive this?

    Many thanks…

  324. sir,
    I am your frequent reader of your SQL tips and its very helpful .
    Iam an non degree holder but very much interested in database aplications with sql . As iam working on sql from past 5 yrs and have a very good knowledg relating to sql but being an non degree holder(10+2 fail) .Sir pz guide me
    to do some certification in Microsoft SQL Server relating that i can get a good job(as iam working in a domestic company).
    I need some tips related to SQL DBA . i want to become as like your successful career.Kindly guide me sir.

    Thanks sir

  325. Hello magz,

    just try this following code into your application for inserting RTF formatted text into SQL database.

    Just follow this code n Try it out !!!

    Dim conn As New SqlClient.SqlConnection(“Server=myserver;Database=mydb;Trusted_Connection=yes;”)

    Dim command As New SqlClient.SqlCommand(“insert into my_table(controlnumber,id_1, cde, de2, wa,stmp, eml) values ( ‘” & controlnumber.Text & “‘,'” & j1.Text & “‘,'” & bl.Text & “‘, ‘” & rm.Text & “‘, ‘” & wd.Text & “‘, ‘” & time100.Text & “‘,'” & (@RTFData) & “‘)”, conn)

    Command.Parameters.Add(“@RTFData”, SqlDbType.Text).Value = richtextbox1.Rtf
    conn.Open()
    Command.ExecuteNonQuery()
    conn.Close()
    conn.Dispose()

    Rgds,
    Bhavank

  326. Hi dave,

    I would like to consult your professional SQL Server skill on this matter. Have you ever encountered this type of problem in SQL Server 2008 Ent Ed; Just recently we added another DB Instance with RS(Sharepoint Integrated) then for some reason we are having this weird log entry on our windows application keeps repeating every 5 secsonds which says:

    Event ID 5084: “Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$TempDB” and
    Event ID 5084: “Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$”

    take note that these dbs are indeed set to 100 for compatibility level. Googled for answers but could not found any.. only same people having same issues..

    Thanks for reading my post and hope to hear from you soon.

  327. Hello Pinal,

    UsreID UserName
    640 Ram
    641 bingo
    742 cinu
    743 zuli
    744 kavi

    The above result is based on order by UserID

    But I want to get result like belwo

    UsreID UserName
    743 zuli
    744 kavi
    640 Ram
    641 bingo
    742 cinu

    how can I write query for this

    please advice me.

    My requirement is
    Iam getting 743 and 744 these two id’s are from previous page
    So I want to show that two records (743, 744) on top and remaining recodrs below (743, 744) records.

    Thanks,
    Venkat

  328. Hi Pinal,

    I am getting error messgaes like below when executing script,how to suppresss these error meesges in sql 2000,if we cannot what is the work around

    error

    Msg 61110, Level 19, State 1, Line 0
    The backup file is encrypted, and you have not supplied the correct decryption key for this file.
    Note: The decryption key is the same as the one used to create the backup file.

    script

    — is backup file encrypted…?
    declare @filename varchar(2000)
    declare @verifystmt nvarchar(2000)
    declare @physical_device_name varchar(2000)
    select @physical_device_name = bh1.physical_device_name
    from dbautils..backupHistoryRS_vw bh1, master..sysdatabases sd
    ,dbautils..backupHistoryRSlast_vw bhL
    where sd.name not in (‘master’, ‘model’, ‘tempdb’) and
    bh1.database_name = sd.name and — used to skip deleted db’
    bh1.database_name = ‘dbocap’ and
    bh1.type = ‘D’ and
    bh1.database_name = bhL.database_name and — gets the last backup date to determine RECOVERY OR NORECOVERY
    bh1.backup_start_date >= (select max(backup_start_date)
    from dbautils..BackupHistoryRS_VW bh2
    where type = ‘D’
    and bh2.database_name = bh1.database_name
    )
    order by bh1.database_name, backup_finish_date

    select @verifystmt = ‘xp_restore_verifyonly
    @filename = ‘+””+@physical_device_name+””

    exec(@verifystmt)

  329. @varun

    The above script is trying to verify the backup by restoring a backup file, backup file name and device name are passed as input parameters to this stored procedure xp_restore_verifyonly (User written stored procedure).

    I believe when taking backup, some one has encrypted the backup, meaning password was provided at the time of taking backup, and when you are trying to restore that backup using above script, sql server needs that password to unlock backup file.

    You have to find out what password was used while taking backup in order to decrypt that backup file

    Also check script of stored procedure, xp_restore_verifyonly , may be password is hard coded in this stored procedure.

    Let us know if this doesn’t solve your problem.

    ~ IM.

  330. I have written the query .
    ALTER FUNCTION [dbo].[fn_ComplexItemsTree03](@int_JobNo Int, @int_xBaseItemId int)
    RETURNS
    @tree_ComplexItems TABLE
    (
    int_Level int,
    int_pBaseItemId int,
    int_BaseItemId int,
    chr_BaseItemId varchar(100),
    chr_BaseItemDesc varchar(250),
    dbl_Selected_Qty numeric(18,3),
    dbl_Amount numeric(18,3),
    dbl_Labour numeric(18,3),
    dbl_Plant numeric(18,3),
    dbl_pMaterial numeric(18,3),
    dbl_tMaterial numeric(18,3),
    dbl_SubContract numeric(18,3),
    dbl_Staff numeric(18,3),
    dbl_Gen_Expense numeric(18,3),
    dbl_Prov_Sums numeric(18,3),
    dbl_onCost numeric(18,3)
    )
    AS
    BEGIN

    DECLARE @int_pBaseItemId bigint
    DECLARE @int_BaseItemId bigint
    DECLARE @int_Count bigint

    ;WITH cattree(int_Level, int_pBaseItemId,
    int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
    dbl_Selected_Qty, dbl_Amount, dbl_Labour,
    dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
    dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost) AS
    (
    SELECT
    int_Level, int_pBaseItemId,
    int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
    dbl_Selected_Qty, dbl_Amount, dbl_Labour,
    dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
    dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost
    FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) A
    WHERE int_pBaseItemId = 0
    UNION ALL
    SELECT c.int_Level, c.int_pBaseItemId,
    c.int_BaseItemId, c.chr_BaseItemId, c.chr_BaseItemDesc,
    c.dbl_Selected_Qty, c.dbl_Amount, c.dbl_Labour,
    c.dbl_Plant, c.dbl_pMaterial, c.dbl_tMaterial, c.dbl_SubContract,
    c.dbl_Staff, c.dbl_Gen_Expense, c.dbl_Prov_Sums, c.dbl_onCost
    FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) c
    INNER JOIN cattree d
    ON c.int_pBaseItemId = d.int_BaseItemId
    )
    INSERT INTO @tree_ComplexItems
    SELECT int_Level, int_pBaseItemId,
    int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
    dbl_Selected_Qty, dbl_Amount, dbl_Labour,
    dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
    dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost FROM cattree;
    RETURN
    END

    The output I get is like
    int_Level int_pBaseItemId int_BaseItemId dbl_Amount 1 0 1 100
    2 1 2 50
    2 1 3 50
    3 2 4 25
    3 2 5 25
    3 3 6 20
    3 3 7 20
    3 3 8 10

    What I want is

    int_Level int_pBaseItemId int_BaseItemId dbl_Amount 1 0 1 100
    2 1 2 50
    3 2 4 25
    3 2 5 25
    2 1 3 50
    3 3 6 20
    3 3 7 20
    3 3 8 10

    The problem is with the order of the out put.
    Please help me identify, where I am going wrong in the function?

  331. how to solve this problem below, and check what are the FK pointing to this PK.

    Msg 3725, Level 16, State 0, Line 1
    The constraint ‘RHPLCA_EIXO_P’ is being referenced by table ‘RHCOMP_PERFIL’, foreign key constraint ‘RHCOMP_PERFIL_F11′.
    Msg 3727, Level 16, State 0, Line 1
    Could not drop constraint. See previous errors.

    thank you very much.
    celio. sao paulo – brazil.

  332. The output I get is like
    int_Level – int_pBaseItemId – int_BaseItemId – dbl_Amount
    1 – 0 – 1 – 100
    2 – 1 – 2 – 50
    2 – 1 – 3 – 50
    3 – 2 – 4 – 25
    3 – 2 – 5 – 25
    3 – 3 – 6 – 20
    3 – 3 – 7 – 20
    3 – 3 – 8 – 10

    What I want is
    int_Level – int_pBaseItemId – int_BaseItemId – dbl_Amount
    1 – 0 – 1 – 100
    2 – 1 – 2 – 50
    3 – 2 – 4 – 25
    3 – 2 – 5 – 25
    2 – 1 – 3 – 50
    3 – 3 – 6 – 20
    3 – 3 – 7 – 20
    3 – 3 – 8 – 10

    I was not sure how to sorting the output of the function,
    The out put I got was sorted as
    order by int_Level, int_pBaseItemId, int_BaseItemId

    What I have done now is to add a field that will help me sort the records.
    now my funtion is,

    ALTER FUNCTION [dbo].[fn_ComplexItemsTree03](@int_JobNo Int, @int_xBaseItemId int)
    RETURNS
    @tree_ComplexItems TABLE
    (
    int_Level int,
    int_pBaseItemId int,
    int_BaseItemId int,
    chr_BaseItemId varchar(100),
    chr_BaseItemDesc varchar(250),
    dbl_Selected_Qty numeric(18,3),
    dbl_Amount numeric(18,3),
    dbl_Labour numeric(18,3),
    dbl_Plant numeric(18,3),
    dbl_pMaterial numeric(18,3),
    dbl_tMaterial numeric(18,3),
    dbl_SubContract numeric(18,3),
    dbl_Staff numeric(18,3),
    dbl_Gen_Expense numeric(18,3),
    dbl_Prov_Sums numeric(18,3),
    dbl_onCost numeric(18,3),
    chr_Tree varchar(100)
    )
    AS
    BEGIN

    DECLARE @int_pBaseItemId bigint
    DECLARE @int_BaseItemId bigint
    DECLARE @int_Count bigint
    DECLARE @int_MaxLevel bigint

    SELECT @int_MaxLevel = MAX(int_LEVEL) FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId)

    ;WITH cattree(int_Level, int_pBaseItemId,
    int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
    dbl_Selected_Qty, dbl_Amount, dbl_Labour,
    dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
    dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost, chr_Tree) AS
    (
    SELECT
    int_Level, int_pBaseItemId,
    int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
    dbl_Selected_Qty, dbl_Amount, dbl_Labour,
    dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
    dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost,
    convert(varchar(100),cast(ABS(int_pBaseItemId) as varchar(10)) + ‘.’ + cast(ABS(int_BaseItemId) as varchar(10))) chr_Tree
    FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) A
    WHERE int_pBaseItemId = 0
    UNION ALL
    SELECT c.int_Level, c.int_pBaseItemId,
    c.int_BaseItemId, c.chr_BaseItemId, c.chr_BaseItemDesc,
    c.dbl_Selected_Qty, c.dbl_Amount, c.dbl_Labour,
    c.dbl_Plant, c.dbl_pMaterial, c.dbl_tMaterial, c.dbl_SubContract,
    c.dbl_Staff, c.dbl_Gen_Expense, c.dbl_Prov_Sums, c.dbl_onCost,
    case when DataLength(d.chr_Tree) > 0
    then convert(varchar(100),d.chr_Tree + ‘.’ + cast(ABS(c.int_BaseItemId) as varchar(10)))
    else convert(varchar(100),cast(ABS(c.int_pBaseItemId) as varchar(10)) + ‘.’ + cast(ABS(c.int_BaseItemId) as varchar(10)))
    end as chr_Tree
    FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) c
    INNER JOIN cattree d
    ON c.int_pBaseItemId = d.int_BaseItemId
    )
    INSERT INTO @tree_ComplexItems
    SELECT int_Level, int_pBaseItemId,
    int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
    dbl_Selected_Qty, dbl_Amount, dbl_Labour,
    dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
    dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost,
    chr_Tree + REPLICATE(‘.0′,@int_MaxLevel-dbo.fn_CountChar(chr_Tree,’.’)) chr_Tree
    FROM cattree;

    RETURN
    END

    now if i use the query
    select * from [dbo].[fn_ComplexItemsTree03] (7106, 1)
    order by chr_Tree

    I will get my desired output. I expected the CTE to do the sorting on its own. I guess, for that I should have used a cursor and a recursive call.
    Thanks

  333. @Celio.

    Error Message you posted : The constraint ‘RHPLCA_EIXO_P’ is being referenced by table ‘RHCOMP_PERFIL’, foreign key constraint ‘RHCOMP_PERFIL_F11′.

    It clearly says, Constraint Name : RHPLCA_EIXO_P is referenced by a table RHCOMP_PERFI, with foreign Key constraint , Constraint Name, RHCOMP_PERFIL_F11

    So If you want to drop RHPLCA_EIXO_P constraint, then you first have to go to RHCOMP_PERFI Table and drop Foriegn key constraint RHCOMP_PERFIL_F11.

    You can use below script to drop this foreign constraint on that table,

    ALTER RHCOMP_PERFI
    DROP CONSTRAINT RHCOMP_PERFIL_F11

    Then you can drop your first check constraint RHPLCA_EIXO_P.

    Warning: By executing above script, you are actually dropping constraint on a table, this will lead to data inconsistency, Foreign key constraints are created to maintain Data consistency. Make sure you know what you are doing, dont be in a situation in which you try to make one thing and you break 3 other processes.

    Check with senior DBAs, ask for assistance if needed.

    ~ IM,

  334. @gpshiburaj

    Looking at the sample data you provided as output, Looks like there is no specific order .

    No Specific fashion is being repeated.

    I have no clue, I looked at it, but again to me it looks like there is no repeating fashion in the data,

    if you have more info, please provide. I will try to look into this again.

    Is there any other column which makes order of the out put like this ??

    ~ IM.

  335. hi pinal

    I am a sailent reader of your site .I had seen your site one month back only.
    it is having a lot of very good and essential information which is very much useful to everyone.
    thanks for your information
    this site is very good.

    thanks
    rajeev

  336. Hi,

    Thanx for your informative site – I really appreciate the time and effort you have put in to help the community.
    I have a question about cursors and stored procs that I’m hoping you can assist me with.
    I would like to write T-SQL script that I can schedule to run after my database backups are run, to force the log files to be shrunk as small as phsyically possible.
    My database backup process is a FULL backup – so I’m not worried about the log files after the backups are done, and in one case, the log file grew to over 300 GB.
    I basically want to select all the user database into a cursor, and then loop through them, shrinking the log file for each database.
    This is what I have so far, but the part that is missing is getting the log file name from sys.database_files into a variable that I can then use in the EXEC statement to execute DBCC SHRINKFILE (@logFile, 1):
    — declare some local variables to use
    DECLARE @useDb varchar(60) — use statement
    DECLARE @db varchar(50) — database name
    DECLARE @sql varchar(2000) — sql queries to be executed
    DECLARE @logFile varchar(100) — name of log file

    — declare the cursor
    DECLARE dbs CURSOR FOR
    SELECT Name
    FROM sys.databases
    WHERE Name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’)
    ORDER BY Name

    — open the cursor
    OPEN dbs

    — get the next record into local variables
    FETCH NEXT FROM dbs INTO @db

    — while there are more records to process
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print @db
    SET @useDb = ‘USE ‘ + @db + ‘; ‘
    SET @sql = @useDb + ‘CheckPoint’
    print @sql
    exec (@sql)
    SET @sql = @useDb + ‘BACKUP LOG ‘ + @db + ‘ WITH TRUNCATE_ONLY’
    print @sql
    exec (@sql)
    SET @sql = ‘SELECT @logFile = Name FROM ‘ + @db + ‘.sys.database_files WHERE TYPE = 1′
    print @sql
    –exec(@sql) — need to get this result into @logfile in order to proceed as required
    print ‘———————‘
    — get the next record to be processed
    FETCH NEXT FROM dbs INTO @db
    END

    — close the cursor
    CLOSE dbs

    — and cleanup
    DEALLOCATE dbs

  337. @Alon,

    Execute below statement after you take log backup with truncate only option. Put these statements in cursor.

    Should work fine,

    DECLARE @ParmDefinition NVARCHAR(500)
    DECLARE @SQLString NVARCHAR(500)
    DECLARE @LogFileName varchar(128)
    DECLARE @Db varchar(128)
    DECLARE @Sqlcmd2 NVARCHAR(500)
    — set @Db = ‘AdventureWorks’
    SELECT @SQLString = N’select @LogFileName = ”[''+Name+'']” from ‘+@Db+’..sysfiles where substring (filename , len(filename)-2 , 3) = ”ldf”’
    SET @ParmDefinition = N’@LogFileName varchar(128) OUTPUT’
    EXEC sp_executesql @SQLString,@ParmDefinition,@LogFileName=@LogFileName OUTPUT
    SELECT @Sqlcmd2 = N’DBCC SHRINKFILE (‘+@LogFileName+’ , 1) ‘
    EXEC Sp_executesql @Sqlcmd2

    Let us know, if you need more help with this.

    ~ IM.

  338. @Alon,

    Forgot to mention one important note, the above script that I posted will work fine if you have only one log file per database.

    If you have multiple log files, then you need to store results (Log File Names) in temporary table instead of variable, and then write another cursor or while loop and execute dbcc shrinkfile command on each log file.

    ~ IM.

  339. Hi Dear,

    Its very nice site to seee anything about SQL Server.

    Thanks for made such type of Site.

    Regards,
    Anil Chauhan

  340. Hi there,
    I am new in SQL server world. I have 1 problem, maybe someone can help me.
    I have around 20 jobs in SQL Server Agent of 2005. I want to take backup of all those jobs.
    I can take the backup of databases, but don’t know how to take the backup of jobs.

    Thanks..

    somia

  341. @Somia,

    Job information is saved in msdb database.

    If you take backup of msdb, it is same as you took backup of all jobs scheduled through SQL Server Agent.

    ~ IM.

  342. Hi Pinal –

    Thanks for responding to my query posted in MSDN. I really appreciate that.
    I followed all the instructions exactly as specified in the article (http://blog.sqlauthority.com/2008/12/02/sql-server-2008-install-sql-server-2008-how-to-upgrade-to-sql-server-2008-installation-tutorial/ ). I’m still getting the error.

    PrepInstance() failed for .
    The following features are missing: Full Text Search
    Fix the problem and re-run the setup

    I don’t understand why the error is coming with “.” insted of the instance that I have selected during installation “MSSQLSERVER” .

    Please let me know if you want to know more details .. My OS and other specs.

    Thanking you in advance

    Best Regards

  343. Hi Dave,

    I have followed your website and I really like the way you are able to break down complex information into simple step by steps instructions.

    I am trying to implement a trigger that would populate another table with the additional information once the item number is selected. But I am getting a some errors can you tell me what I am doing wrong here?

    CREATE TRIGGER trg_FoodJournal
    ON FoodJournal
    AFTER INSERT
    AS
    BEGIN

    SET NOCOUNT ON;

    Insert into FoodJournal (Foodtype,Food, Calories, FatGrams, ProtienGrams,CarbGrams, pFat, pProtien, pCarbs)

    Select Foodtype, Food, Calories, FatGrams, ProtienGrams,CarbGrams, pFat, pProtien, pCarbs

    FROM FoodData
    Where FoodID.FoodData = FoodID.FoodJournal

    END
    GO

  344. Hi Eric,
    What table do you want to populate when the item number is selected, FoodData or FoodJournal?
    The trigger that you’ve written is wrong because you’ve created a trigger on FoodJournal. This trigger will get fired when any row is inserted into FoodJournal. But the trigger inturn is re-inserting back into FoodJournal by selecting some row from FoodData…
    Moreover, the query is also wrong as you are selecting from FoodData but in the where clause you are using an alias “FoodID” ??
    Should the from clause be “FROM FoodData FoodID” ?
    And i think you need to create your trigger on FoodData table. Can you give me details of the tables and what you want to insert and where please..

  345. Hi Ryan,

    Thanks for the reply!

    So basically, when a user enters

    The user enters the food and the serving size of what he or she has eaten into the FoodJournal.

    Since most users dont know the nutritional value of what they have eaten in detail.

    I want to populate the remaining nutritional information that is in the FoodData into theFoodJournal.

    The FoodID is the Unique Identifier for foods entered in both tables. So the trigger is supposed to use the FoodID to identify the entry that needs the info and copy the remaining data from the FoodData into the FoodJournal.

    I hope this helps.

    Thanks

    E.

  346. Hi Eric,
    If i undertand correctly, you insert just the Food & ServingSize into FoodJournal.. The rest of the data you want to pull from the FoodData table right? I have modified your trigger to do that, see if you understand it.
    Just a word of caution, I wouldn’t advice you to use a trigger for this purpose, if you are inserting Food & ServingSize into FoodJournal via a Stored Procedure, then i would advice you to modify the stored proc and do an inner join with the FoodData and get all the information you need and then insert into the FoodJournal table in one go. In this case you don’t need any trigger to update the remaining additional columns.

    CREATE TRIGGER trg_FoodJournal
    ON FoodJournal
    AFTER INSERT
    AS
    BEGIN

    SET NOCOUNT ON;


    — Since the row is already inserted,
    — we have to update this row with additional info from the food table

    Update FJ
    Set FJ.Foodtype = FD.Foodtype,
    FJ.Food = FD.Food,
    FJ.Calories = FD.Calories,
    FJ.FatGrams = FD.FatGrams,
    FJ.ProtienGrams = FD.ProtienGrams,
    FJ.CarbGrams = FD.CarbGrams,
    FJ.pFat = FD.pFat,
    FJ.pProtien = FD.pProtien,
    FJ.pCarbs = FD.pCarbs
    From FoodJournal FJ — The Food Journal Table that has the new entry
    Inner Join Inserted NewRow — This magic table ontains the newly added row(s) into the food journal table
    On ( FJ.FoodID = NewRow.FoodID )
    Inner Join FoodData FD — This table has the additional info for food data
    On ( FD.FoodID = FJ.FoodID )

    END
    GO

    Hope this helps..

    — Ryan Thompson

  347. Ahhh! Got It! Thanks Ryan For All Your Help! I will give this a try and let you know how it goes!

    Thanks Again!

    E.

  348. Dear Mr.Pinal,

    I am using Asp and ms sql server 2005 for one of my project. In the project iam saving date from one form to multiple table where i am referring One perticular column id in all other tables . there are many people using the system so some times the id gets duplicated . How to solve the issue Pls help me out. Following is the save query iam using.

    rsDwrNO.open “select max(Dwr_Id)+1 from Dwr_Master”,conn,adOpenForwardOnly
    DwrCode=rsDwrNO(0)

    sqlDwM=”Insert into Dwr_master(Dwr_Id,Mtp_Id,Hq_Cd,Hq_Name,Prmn_id)Values(“&trim(DwrCode)&”, “&trim(Sroute)&”,”&trim(request.form(“Rhqcode”))&”,'”&trim(request.form(“HqName”))&”‘,”&trim(session(“Emp_ID”))&””

    Insert into Dwr_Doc_Product(Dwr_id,Dwr_Doc_id,Dwr_Doc_Name)Values(“&trim(DwrCode)&”, “&trim(request.form(“DDcd”&i))&”,'”&trim(request.form(“DDnam”&i))&”‘)”

  349. Hello Sir,

    You r great hope,
    I was trying to write a stored procedure as under

    I have a database named family, having table itemlist
    I wanted to generate Crystal report lables data with blank lables at top

    USE [Family]
    GO
    /****** Object: StoredProcedure [dbo].[sp_ItemLables] Script Date: 06/05/2009 10:41:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_ItemLables]

    (
    @repeatlables numeric( 3,0),
    @blanklables int
    )
    as
    declare @i integer
    set @i = 0
    delete from Family.dbo.Repeater

    while @i < @repeatlables
    BEGIN
    insert into [Family].[dbo].[Repeater] (how_many) values (1)
    SET @i = @i + 1
    END

    /*
    @itmeui varchar(5000)
    declare @pos int
    set @pos = 0
    CREATE TABLE #TempItemLables
    (
    ui uniqindentifier
    )
    */
    IF OBJECT_ID (N'#TempLables',N'U') IS NOT NULL
    DROP TABLE #TempLables ;

    select line1, line2, line3, line4, line5, line6, line7, line8, itemlist.ui
    INTO #TempLables from [Family].[dbo].labels itemlist, [Family].[dbo].[Repeater]

    /* insert top( @blanklables) into #TempLables( line1)
    SET NOCOUNT ON
    set @i = 0
    while @i < = @blanklables
    BEGIN
    insert into #TempLables ( line1 ) value ( '')
    END
    */
    RETURN

    can u please correct this procedure

  350. HI, Imran Mohammed

    I knew the backupfile is encrypted, I am actually writing a script like this.
    ex:
    if ( backupfile is encrypted )
    print ‘encrypted’
    else

  351. HI, Imran Mohammed

    I knew the backupfile is encrypted, I am actually writing a script like this.
    ex:
    if ( backupfile is encrypted )
    print ‘encrypted’
    else
    print ‘not encrypted’

    but actually in output I am getting.
    output:

    encrypted

    Msg 61110, Level 19, State 1, Line 0
    The backup file is encrypted, and you have not supplied the correct decryption key for this file.
    Note: The decryption key is the same as the one used to create the backup file

    I want output as only as below to be printed and supress the error meesage.

    output :

    encypted

  352. hello,

    sir i have a problem in my query and did’t found the result.

    i want to sum the column of a table with respect to the type

    1- purchase
    2- payment

    i write this query..

    select supplierId,sum(Amount) as ‘Amount’
    ,(select sum(Amount) from tbl_supplierLedger where Type=’Payments’ group by SupplierID ) as ‘Payment’
    from tbl_supplierLedger
    where Type=’Purchase’ group by SupplierID

    but the problem is that the subquery that i am using “select sum(Amount) from tbl_supplierLedger where Type=’Payments’ group by SupplierID” return 2 records and 1st query return 4 records thats y i am facing an error…

    please replay me as soon as possible

  353. Hi Pinal,

    I have a heavy table on sql server 2005 with enable full text search on it, I want to get top N newest data by id or insert date with specific full text.

    some query result with “Containstable” return over 500,000 record and take over 1 min, that is not good, also top n by rank not good for me because maybe return old data

    Table info: Auto increment field, Id bigint as primary key Info Field type is NVARCHAR(MAX) to using full text Insert Date Field and desc index on it

    * More than 28 million record now
    * Insert More than 600,000 record per day

    Hardware:

    * 8 CPU 2.2 GHz (really 2 quad core cpu)
    * 16 GB RAM

    Software:

    * Windows 2003 x64
    * SQL Server 2005 Standard

    Have any idea

    Or can we modify (override) full text search rank method?

    Thanks

  354. @Salman,

    Please post error message,

    Mean while you can try this,

    Replace your script with this script.

    select supplierId
    ,SUM(Amount) as [Amount]
    ,( select sum(Amount) [Internal_Amount]
    from tbl_supplierLedger
    where [Type] = ‘Payments’
    group by [SupplierID]
    ) Payment
    from Tbl_supplierLedger
    where Type = ‘Purchase’
    group by SupplierID

    — Try this example, I am doing the same

    create table example1 ( eid int, ename varchar(10), Month_Name varchar(20), salary money )

    insert into example1 values ( 1, ‘Imran’ , ‘Jan’ , 20000)
    insert into example1 values ( 1, ‘Imran’ , ‘Feb’ , 20000)
    insert into example1 values ( 1, ‘Imran’ , ‘Mar’ , 20000)
    insert into example1 values ( 1, ‘Imran’ , ‘Apr’ , 20000)
    insert into example1 values ( 2, ‘Taher’ , ‘Jan’ , 30000)
    insert into example1 values ( 2, ‘Taher’ , ‘Feb’ , 30000)
    insert into example1 values ( 2, ‘Taher’ , ‘Mar’ , 30000)
    insert into example1 values ( 2, ‘Taher’ , ‘Apr’ , 30000)

    GO

    create table example2 ( eid int, ename varchar(10), Month_Name varchar(20), salary money )

    GO

    insert into example2 values ( 1, ‘Imran’ , ‘Jan’ , 20000)
    insert into example2 values ( 1, ‘Imran’ , ‘Feb’ , 20000)
    insert into example2 values ( 1, ‘Imran’ , ‘Mar’ , 20000)
    insert into example2 values ( 1, ‘Imran’ , ‘Apr’ , 20000)

    GO

    select Eid
    ,SUM(salary) as [Salary]
    ,( select sum(Salary) [Internal_salary]
    from example2
    group by [Eid]
    ) Payment
    from Example1
    group by Eid

    ~ IM

  355. in

    Design table
    (Design_nm,Design_Desc)

    Work table
    (Work_title,Design_nm,Client_nm)

    i want

    “Design_nm,No.of.Client.No.of.work”

    notes:

    in above result Display all the row of Design_table

    if any design_name(design table) match with Design_nm(work table) than display total no of work and client else display only Design_nm(design table).

    example:

    (Design TABLE)

    Design_nm,Design_Desc

    (logo design,—-)
    (website design,—)
    (video design,—-)

    (WORK TABLE)

    Work_title,Design_nm,Client_nm

    abc1,logo design,jayesh
    xyz1,logo design,sapan
    abc2,logo design.jayesh
    zzz1,website design,jayesh

    i want

    Design_nm,No.of.Client.No.of.work
    logo design,2,3
    website design,1,1
    video design, 0,0

  356. Hello pinal,

    I have a question,
    I need to replicate few tables from production server to different server(RPTS Server). Can you tell me the easiest way to do it ? without usage of SSIS

    regards,
    Sri.

  357. Hello Pinal,

    I have been using database (e.g. ABC) for my web application. Web application is accessed by customers as well as internal users. I need to have a different database (XYZ) on the same server having some of the tables of database ABC. Database XYZ will be accessed by customers. I want to sync these databases in such way that data inserted into ABC will also be added to XYZ immediately and vice versa. So that data added/ updated by internal users and customers will be available to each other right away. I can’t have triggers on both sides because SQL server does not allow to do so.

    Please suggest a solution.

    Thanks a lot !

    Prashant

  358. Here is situation which you can come up with a solution

    I have about 4000 customers out which there are 160 customers in a Group A and 200 in Group B which I can extract into Excel.

    Now my issue is that I wish to change these customers ” terms of payment” let’s say from “net 30″ to “prepayment”.

    How can I write a query and can pull the data from excel column in my where clause?

    e.g.

    update rm00101 set tor = “prepayment” where custid = “????’ and “?????’ and so on until the end of line.

  359. Hi Pinal.
    h r u ?
    I am a dot net developer and also working in database (Sql Server 2005). Now I m thinking join a course in database (Sql Server) where I can improve my database (sql server) skills so that I can design my database in well manner and in a efficient way. So that my database can handle the millions of data in efficient way.
    In short I want gud hand in database and to improve my skills.
    So can u provide me some professionals who provide the facility in Delhi.

    Regards,
    Kailash Paliwal

  360. Hi Pinal,
    Can you explain what is the best way of fetching the orders with their total as last row with single sql statement. I did it using union, but it takes two queries to accomplish that.

    What is the better way of doing it?

    e.g.
    select OrderNumber,OrderTotal as Total from tb_Order
    union
    select 9999,sum(OrderTotal) as Total from tb_Order

  361. Dear Pinal,

    I hope you are reading this message in best of your health,

    Your Site is very informative and most of the solutions found here.

    I hav 2 issue, would you be very kind to provide me some tips:

    1) Protect SQL 2005 SP2 Database Access for BuiltIn Admins.

    2) Create New SQL user Same as Existing SQL user with all previlleges and Accesses on Tables, SP’s, Functions and Views.

    Looking forward for your reply

    Thanks and Regards
    Murtaza Rana

  362. Dear Sir,

    I want to know what is database commitment control in SQL2005. How can I use it for my database?

    Prashant

  363. @@Ganesh

    Hi you cal do it by just set up linked server and ya you have to have access to that remote server. you can write a query

    then you can write the query

  364. @@sri

    hi if you want to replicate once the ues bcp command or select * into statement
    but if you also want to send changes then you have to set up replication

  365. Hi Pinal,

    I am a LAMP/ WAMP developer since past 3+ years. Right now I am working on SQL server 2005 to export data and import it to MySQL server (version-5).

    I have to get one tables data only in which first I get the all fields name from SQL server. Same when I fired a query to get data of those fields in order of fields name which I get, it will return me an error that ” MSSQL query failed ”

    If I removed few fileds from the query and run it again then I will get all the data successfully. And if I add again those removed fields in to query then again it will return same error.

    Please reply me ASAP I am hanged here.

    Thanks in advance

  366. Hi Pinal,

    I Have to capture events of a particular table using SSAS. Events are specified as conditions.
    I have been asked to use Sequence clustering algorithm in SSAS . Can u please help me how to go about doing this task. The information available on the net is insufficient. Thanks

  367. Sir,
    I am a begginner to SQL Server, when i execute my stored procedure i got the error like this,

    Location: tmpilb.cpp:2530
    Expression: fFalse
    SPID: 159
    Process ID: 1664
    Description: Attempt to access expired blob handle (3)
    Msg 3624, Level 20, State 1, Procedure SP400_SEARCHAPPLICANT, Line 787
    A system assertion check has failed. Check the SQL Server error log for details
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.

    I am using temporary table in this stored procedure when i searched in google i am not getting the way to handle it, i thought it is better to ask you.
    Please help me to solve this because this is a big procedure for having many search criterias.

    Thanking You
    ShameerKhan.S

  368. Hi Pinal,

    Im planning to take certification in Microsoft Sql Server DBA. I want the latest dumps to prepare for the exam. Can you please help me in this.

    Thanks
    kumar

  369. I tried to connect to Reporting Services 2005 server (windows 2003 server 32bit) from MS SQL SMS (windows 2008 server 64bit). I got the error:
    Cannot connect to the SSRSServerName.
    The request failed with HTTP status 401:Unauthorized. (Microsoft.sqlserver.management.UI.RSClient)
    I m not sure that I missed something here.
    Thanks

  370. Thank you for your very timely post!

    This post of MSDN saved me!

    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/9e01e870-0bff-4669-a385-2d9349b1a4f0

    My buddy googled an error number and youir post came up!

    The error generated as the party stated on an attempt to right click APP_DATA in VWD and create a new Data Base.

    Error: 15372 on adding new sql database in VWD

    Is there an equivalent in VWD in XP? Or is it indenpendent of Windows version?

    Thanks!

    –John

  371. @Kumar,

    My Personal Suggestion don’t ever study from Dumps to do certification , You will not learn anything, Try reading books. There are many books for specific exams, try reading those books.

    ~ IM.

  372. Hi
    Pinal

    I have posted one doubt on “on June 18, 2009 at 1:23 pm” about the blob handle error i got while running a stored procedure.
    Please help me how to solve this error.

    Thanks
    Shameer Khan

  373. @somia

    yes you can edit the ssis packege. just open and make changes .if it is saved in msdb database then dont forget to deploy again package. if it is saved in file system you dont have deploy it again.

    Thank you

  374. hey please help me out from this problem… please

    I have some knowledge on sql server 2005 in my masters and now i graduated and I am in a confusion about what to choose for training… sql dba + MSBI or .net + sharepoint…

    which one has more jobs and future??? please help me.. i am in a great confusion and i need help ASAP.

    Thanks,
    kiran.

  375. Hi Pinal,

    Could you please let me know how can i change language locale settings after the installation of the SQL Server 2000?

    Thanks & regards,
    Suresh

  376. i have a data entry system for patient records. in my system there are several forms through which data is entered in different tables. and there is a form which takes the present condition of the patient (i call it PresentAssessment). now i what i want is when the user enters a new record i want to compare the values of the attributes (database table fields in the same table) of the newly entered patient and from all the matching records i want to calculate and return the chance(in terms of %age) of particular disease(s) (stored in PresentAssessment) to newly added patient. e.g. if i have two tables i.e. PatientDrugHistory and PresentAssessment. and say each of them already have 10 records with 10 columns each. now when entering the 11th record in PatientDrugHistory i want to calculte and store how much chances (in terms of %age) exist that this 11th patient is suffereing from disease1 (a column in PresentAssessment). please if anybody can tell me what query to write for this purpose.

  377. Hi,
    I need some help. I have about 10 databases for which I
    created database backup maintenance plan and run it once a day on principal server.
    We have a mirror server and a witness server for failover, when fail over happened I would like to create a script to automatically determine which server is Principal.
    I can use master.sys.database_mirroring to determine life server, but what should I do next?

  378. Transfer data from sql express 2005 to sql server 2005.

    What is the best way to transfer data from sql express 2005 to sql server 2005.

    We have a set up where we have “N” site office, each site office has an instance of sql express installed, and the site has only the information that it requires. In the main server at the head office, the data has to be updated every week.

    What would be the best way to design this replication of data.

  379. Hi Pinal,

    Is there any advantage of writing 70-431 and 70-443, if its useful let me know what is the next one i need to take.

    Thanks in advance,
    Gupta

  380. hi,
    this is my sqlquery i want sum(average) for 3 but it gives one value

    select date,(select sum(average)/3 from CCAnalysis_Internal_USR where date =ca.date and msc_name=ca.msc_name)as total from CCAnalysis_Internal_USR ca where date between ’09/11/2009′ and ’09/15/2009′ and msc_name in (‘mscbas1′,’gcsdiw2′,’mscbag1′) group by date,msc_name

  381. Hi Pinal,

    I’m a daily visitor to this blog and its excellent.

    I need advice from you.

    I have to migrate an oracle 10g database to sql server 2005. Do I need to use SSMA or SSIS? Please suggest.

    Regards,
    Vani

  382. i want to know about certification in Microsoft Sql Server DBA.
    so give some idea and I want the latest dumps to prepare for the exam

    Thanks
    Ashok

  383. Hi Pinal, I just wanted to thank you for all the wonderful articles you’ve written: they’ve helped me immesely over the last year after I took over my first big SQL database. They are concise and clear, and I really appreciate you taking the time to write and publish them for the DBA community at large. Keep up the good work!

    -Russell

  384. Hi pinal,

    I have a ssis package and it contains a for each loop container and it loops through each and every csv file and loads the csv file data into tables. Now I want this package to be transactional based.

    Without transactional based option it is running fine and when I add the transaction option to the package and also to individual tasks then it is giving error.

    The transactionoption for my package is set to required and the order of my package items are as follows:

    1. For each loop container: I defined two variables the csvfilepath and csvfilespec which defines the folder path and type of files it need to extract. Inside the for each loop container I have 2,3,4,5 items. The transactionoption is set to supported.

    2. Execute sql task1: Which inserts the the record with job details like jobno , filename into job table . This filename is coming from the foreachloop container as I defined a vairable which captures the filename it is processing and this execute sql task has ole db connection. The transactionoption is set to supported.

    3. Execute sql task2: This task helps in retrieving the max jobno from job table and stores in a variable. The transactionoption is set to supported.

    4. Data flow task: This task has one flat file source, derived column transformation(to create a new column and max job no will be placed in this one and then the data is sent to ole db destination) and ole db destination. The target table is sql server and I used ole db connection. The transactionoption is set to supported.

    5. Executte sql task3: This task has selectstatment as ‘select 1/0′ The transactionoption is set to supported.

    Now I want to have transactional system for my package. As I know that my last sql task going to fail I want the whole package to get rollback.

    But I am getting the following error:

    [Execute SQL Task] Error: Failed to acquire connection “xxxx”. Connection may not be configured correctly or you may not have the right permissions on this connection.
    Task Execute SQL Task1 failed

    [Connection manager “xxx” Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 “The partner transaction manager has disabled its support for remote/network transactions.”.

    Please help me in resolving this issue and it is very urgent to complete this task. I request moderators to please help me urgently.

    Your help is appreciated.

    Thanks

  385. Hi sir ,

    Please help me to solve this…

    prod_cd prod_type prod_nom prod_base Prod_diam
    101 L 1.00 5 70
    101 L 1.00 5 65
    101 L 1.00 5 75
    101 L 1.00 5 80
    101 L 2.00 5 60
    101 L 2.00 5 70

    Result should be

    101 L 1.00 5 70 65 75 80
    101 L 2.00 5 60 70 Null null

    can you please give me the sql server 2000 query.

  386. Hi Pinal,

    Good day, i will like to know if the Logshiping and DB partitionning can work together in MSSQL server 2005?

    if yes what would be the sequence? should do first the partitionning then the logshipping?

    Thanx

    Patrick

  387. hello sir,

    i want to know about d2k
    can u plz provide me information regarded it
    can u give me links from where i can read and aquire knowledge about it

  388. Hi Pinal,

    I am a Techincal Lead and Sr. Dot Net Developer (Using database SQL Server for the last 10+ years) working with one of the biggest bank in US. For the last 1 year (Approx.) I am reading your articles and I found very impressive n technically very sound. You are doing a very good job for the guys who are in Microsoft SQL.

    Hope you always guide and give your tips n ticks to the guys in the same…..!!!

    Thanks so much…..

    –Mohit

  389. Pinal,

    I develop some applications for personal usage and with no intention of selling or distributing them. Until now, I use Access as a database store. But lately, I got convinced that SQL server is a better choice: more flexibility, more powerful SQL, etc… (Your excellent blog was part of my conversion). Especially the Enterprise Edition offers some features I want to explore. But there is an obvious snag: its price! I simply cannot afford it.

    I know there is a “developer edition” which could solve that problem (cfr: http://blog.sqlauthority.com/2008/12/23/sql-server-2008-download-copy-of-developer-edition-for-free-is-myth/)

    In principle that edition can only be used for development only: (cfr:http://www.microsoft.com/sqlserver/2008/en/us/developer.aspx). I only read that one license can be used on several systems, so I can use a physical backup system.

    But – and now comes the 2 cent question – what is the difference between development and production? As a single (home) user, I’m continually developing. At the same time some queries/reports are useful to me (and can be considered as my “production”).

    Can you help and clarify the issue.

    I also can add that Microsoft was rather “annoyed” and even unhelpful to supply to answer.

  390. Mr. Pinal? I have used an evaluation edition of SQL 2005 from the textbook that I loaded on my desktop at home so I would not have to go into a training center that I go to learn it. The time period for it ran out and I am in week 5 of a 6 week course. Tried to reload it and end up getting errors with one that is prominent is the following “..Failed to get ModuleID_RS_Server when getting the ASP Temp directory..” Seeing the ASP error I thought it was a problem with Framework so I deleted and redownloaded .Net Framework 2.0 x86. Still got errors and now the Framework 2.0 is labled 2.0 (2). Renaming that to get rif of the “(2)” was no help. Doing a search on that original error gives 17 different posts on it on the internet that are 2 to 3 years old but no solutions. I have checked in the registry for anything and have not found any anamolies. IS there anything that can be done to correct this?
    Thank you,
    Tom

  391. Mr. Pinal, now after getting backfrom the building I went to to get MCSE certified and donwloading the Express version, I get “Error 1603 installing Microsoft SQL Server 2005 Setup Support Files,” and in the log there is “..Failed to query registry value (SOFTWARE\Microsoft\Microsoft SQL Server\90\Bootstrap\MSIRefCount\Uninstall)
    Error: The system cannot find the file specified..” Basically because it is not there. Now searching on this for the most part I am getting messages that it is in the registry. IS it possible to repair this or is it becase it was a trial version and something was done to make it impossible to reload it. I just want to complete this course.
    Thank you,
    Tom

  392. ramu

    hi pinal,
    \12353-435764.tif.tif in one column but iwant same thing in another which is existing column \12353-435764.pdf.
    i want only .pdf extension only that to only one time
    if i tis like this i done like

    update tbl
    set col2=replace(col1,’.tif’,’.pdf’)
    \12353-435764.tif
    but i dont know wen it is in \12353-435764.tif.tif
    how to convert into
    \12353-435764.tif.tif–>\12353-435764.pdf
    i amnt able to do like this by using patterns

  393. Hi Pinal,

    my requirement is solution for the following problem.
    my problem is,

    I have a store procedure say Proc_Main, Proc_Main calls to many stored procedure inside. the stored procedure i have written in sql server 2005.

    For example my proc_main looks like

    create proc proc_main (@userid varchar(10), @ipxml xml)
    as
    begin try

    exec proc1
    exec proc2
    begin try
    declare @ip xml(xsdex)
    set @ip = @ipxml
    end try
    begin catch
    insert into errormaster
    select error_number(), error_severity(), error_state(), error_procedure(), error_line(), error_message()
    end catch
    end try
    begin catch
    select error_number(), error_severity(), error_state(), error_procedure(), error_line(), error_message()
    end catch

    the procedure created successfully.
    now for testing i will execute the procedure by the following commands

    BEGIN TRAN
    BEGIN TRAN

    EXEC Proc_Main ‘Prakash’,@inputxml

    when i execute the procedure i’m getting following error message “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.”

    i dont know what is the exact problem. thing is if i execute the same procedure without begin transaction infront then its executing without any problem. but i need to execute with begin transaction only. please give me answer what i have do to and what is the problem.

    I’m in production environment, already my dead line got finished please provide me the solution

    Thanks & Regards,
    Prakash

  394. Hi pinal,

    Thought you might be able to help me out.

    Is there a way to use a .bak file that was recovered from a formated hard drive?. The file seems to be complete because of his size but when a try to restore it the following message appears:

    Mens. 3201, Nivel 16, Estado 2, Línea 1
    Cannot open backup device ‘backup_200906271300.bak’. Operating system error 2(error not found).
    Mens. 3013, Nivel 16, Estado 1, Línea 1
    RESTORE DATABASE is terminating abnormally.

    Thanks in advanced.

  395. Dear Sir,

    I have read many articles from your blog. It is very helpful for fresheres as well as experience developer.

    Thanks a ton.

    Regards,
    Yugantar

  396. I am a SQL developer who has an intreguing problem with temp tables (I have read a lot on your site about these). Put most simply it is this:
    1. Copy Pubs table to Table in TEMPDB
    2. Copy this to real local TEMP table
    3. SELECT from this real local TEMP table – fails!

    SET QUOTED_IDENTIFIER OFF
    GO
    USE TEMPDB

    declare @somename varchar(6)
    set @somename=’james1′
    print @somename

    Declare @TSQL as Varchar(400)
    SET @TSQL = “SELECT top 1 * INTO ” + @somename + ” FROM pubs.dbo.authors ”

    print @TSQL
    EXEC (@TSQL)

    select * From james1 — static version – works
    SET @TSQL = ” SELECT * from ” + @somename — dynamic version – works
    print @TSQL
    EXEC (@TSQL)

    select * INTO #localTemp1 From james1 — static version – works
    SELECT * FROM #localTemp1 — Works

    SET @TSQL = ” SELECT * INTO #localTemp2 from ” + @somename — dynamic version – seems to work
    print @TSQL
    EXEC (@TSQL)

    SELECT * FROM #localTemp2 — FAILS!!!
    /* Server: Msg 208, Level 16, State 1, Line 24
    Invalid object name ‘#localTemp2′.
    */
    drop table james1
    drop table #localTemp1
    drop table #localTemp2

    This is condensed version that is stopping multiuser stored procs from woking on a large account (IBM)

    Thanks just for looking.

  397. This is a response to John Goodstadt’s post above:

    This will probably work if you create the entire procedure within the @TSQL variable:

    DECLARE @somename varchar(30)
    SET @somename = ‘james’

    DECLARE @TSQL varchar(400)

    SET @TSQL = ‘ SELECT * INTO #localTemp2 from ‘ + @somename + ‘; SELECT * from #localTemp2; DROP TABLE #localTemp2′

    EXEC(@TSQL)

    I got this to work just as selecting it would have.

    Hope this is what you’re looking for.

  398. Pinal Dave,

    I’m wondering if there may be some way to increment ID numbers on subsets without using ROW_NUMBER function. I have data in a similar structure as that below:

    Subset1 | Subset2 | Identity
    ————————————–
    Jan | 2008 | 1
    Jan | 2008 | 2
    Jan | 2008 | 3
    Jan | 2009 | 1
    Jan | 2009 | 2
    Jan | 2009 | 3
    Feb | 2009 | 1
    Feb | 2009 | 2
    Mar | 2009 | 1
    Mar | 2009 | 2

    In the case of this project, each of these must remain unique as a combination key within the same table. Is there a way to automatically calculate these rows in the case that some records are at some point removed? Incrementing must continue from the highest ID submitted within that group. The project must not renumber or else… dire consequences!

  399. @John

    Your Script :

    Set @TSQL = ‘ Select * into #LocalTemp2 from ‘+ @Someone
    Print @TSQL
    Exec (@TSQL)
    SELECT * FROM #localTemp2 — Will fail

    Replace above script with below script.

    Set @TSQL = ‘ Select * into #LocalTemp2 from ‘+ @Someone
    Print @TSQL
    Exec Sp_ExecuteSQL @TSQL

    — Remember @TSQL Should be nvarchar data type, in order to use in Sp_ExecuteSQL.

    SELECT * FROM #localTemp2 — Will work.

    Exec (@DynamicSQL) : is one seperate session. This Creates #LocalTemp2 temporary table and then drops it as soon as this dynamic sql executes successfully. But Sp_ExecuteSQL Preserves this temporary table, even after Dynamic sql is executed.

    Sp_ExecuteSQL is also used, when you want to return an output from a dynamic sql script.

    Exec(@DynamicSQL) could be risky at times, this could become one of the reason for SQL Injection. So better stay away from Exec(@DynamicSQL), unless there is a need.

    Since Exec(@DynamicSQL) Creates temporary tables and then drops it, you can try doing this, just to satisfy yourself.

    Set @TSQL = ‘ Select * into #LocalTemp2 from ‘+ @Someone + ‘ SELECT * FROM #localTemp2′
    Print @TSQL
    Exec (@TSQL)

    This should give you result.

    But, If a temporary table is already created, you can use that temporary table in your dynamic script, that works…

    For Ex:

    Select * into #LocalTemp2 from james1

    Declare @TSQL varchar(1000)
    Set @TSQL = ‘Select * from #LocalTemp2′ — Should work.

    ~ IM.

  400. Dear pranil,

    i need a logic for image file compression and encryption storage in sql server 2005.we can store the image file in the
    “image” datatype but i need to compress and retrive that
    later in same resolution ,i try that with “image thumb nail” option but there is a problem in the image resolution ,so
    i request you to send a reply for this query.

    Thanks in advance :)

  401. Hi Pinal,

    Its really good to have u on board for all SQL help.

    I happen to play around with SELECT …… INTO statement and I did not understand that NULL will get the datatype of int whereas rest of them are not… To put into simple terms here I leave the statement

    SELECT ‘Pinal’ AS ‘VARCHAR data type’,
    100.50 AS ‘NUMERIC data type’,
    25 AS ‘INT data type,
    NULL AS ‘DON’T KNOW data type’
    INTO
    DUMMY_TABLE_WITH_DATATYPES

    when I select the table name (DUMMY_TABLE_WITH_DATATYPES) and press ALT+F1 key I could see that for NULL the datatype is INT. Could you please justify on this.

    Regards
    Vinod Andani

  402. Sir,
    We are creating SSIS packages which will drop and create a dbf table and then copy the data from SQL server to dbf table. The dbf files are located in the same server where my packgaes are created. For DBase i am using OLEDB Jet provider. When i execute the packages, i am getting the following error:
    [Execute SQL Task] Error: Failed to acquire connection “DBConnection”. Connection may not be configured correctly or you may not have the right permissions on this connection.
    I checked all my permission and all is fine.
    Could you please let us know the solution got this?

  403. Avinash,

    Chek out the below code snippet ….

    select ‘2009/04/01′ as ‘Current Financial Year Started on’,
    datepart(dd,’2009/04/01′) as ‘First Date of April’,
    datename(dw,’2009/04/01′) as ‘Day_Of_First_Date’,
    ‘2010/03/31′ as ‘Current Financial Year Ends on’,
    datepart(dd,’2010/03/31′) as ‘First Date of April’,
    datename(dw,’2010/03/31′) as ‘Day_Of_Last_Date’

    Regards
    Vinod Andani

  404. I am a regular visitor of your blog and have been able to resolve a lot of SQL related problems. I am currently facing a problem in one of our application which involves processing of around 1-2 lakh records. This application is developed in VB6 and DB used is SQL Server 2005. I am executing a stored procedure in the form, the Stored Proc contains the code of data manipulation. Earlier I was using CURSOR’s for record-by-record processing, which later on started giving “TIMEOUT EXPIRED” error as the no. of records increased.

    I then rewrote the code to optimize by using bulk data manipulation. I now fetch the resultset and perform mass insert/update in one go. This method has reduced the time drastically from hours to seconds.

    I have tried it and it works excellently in the Query Analyser. I tried to execute the same from the VB application which executes the stored procedure. I again faced the same “TIMEOUT EXPIRED” error, even though the actual execution time of the Stored Proc in Query analyser is just 45 sec. I even tried increasing the “CONNECTION TIMEOUT” of the connection object, that still did not help us.

    I have also checked with the relationship between the tables used to create the resultset. I have also put extra indexes for faster scanning.

    I hope you can help me out in this problem. Can you please guide me in the methods to be used where data manipulation takes place on large resultsets. What kind of method is being used in the industry, e.g. banking domain?

  405. I have a question for a project that I am working on for my class a I hope you can help me with.

    Here is the scenario we have.

    The user would be able to send a message via email or text using four key words and some corresponding codes for each
    keyword to get specific data from the table. Each key word relates to a specific table in the database, and each corresponding
    code relates to specific information in that table.

    For example: “Keyword”, “Corresponding Code” is received by SQL via a table called “MessageIn”.
    MS SQL will then return specific information to that user via the “MessageOut” table based on the Keyword and corresponding code.
    MessageIn —- Route to appropriate table based on keyword and Corresponding Code.

    Keyword 1 -> Table 1 in database
    code1 = Get data1 from Table 1
    Code2 = Get data2 from Table 1
    Code3 = Get data3 from Table 1
    Code4 = Get data4 from Table 1

    Keyword 2 -> Table 2 in database
    code1 = Get data1 from Table 2
    Code2 = Get data2 from Table 2
    Code3 = Get data3 from Table 2
    Code4 = Get data4 from Table 2

    Keyword 3 -> Table 3 in database
    code1 = Get data1 from Table 3
    Code2 = Get data2 from Table 3
    Code3 = Get data3 from Table 3
    Code4 = Get data4 from Table 3

    Keyword 4 -> Table 4 in database
    code1 = Get data1 from Table 4
    Code2 = Get data2 from Table 4
    Code3 = Get data3 from Table 4
    Code4 = Get data4 from Table 4
    MessageOut —- Send appropriate information to user based on keyword and Corresponding Code.

    Can this be done using SQL and if so can you provide some guidance as to how?

  406. Hi

    When I run below command it shows me particular table but when I try to delete that table it says

    Cannot drop the table ‘?????’, because it does not exist or you do not have permission

    SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’

    Thanks,
    Sunil

  407. @Sunil.

    Please give complete name of table.

    For Ex:
    Drop table Product.ProductDetail

    Here Product is schema (SQL Server 2005) , or owner of object (SQL Server 2000).

    If you execute this command
    Drop table ProductDetail
    It will give you the same error message, if your default schema is not product.

    Either change your default schema from dbo to schema ( object Owner) or mention complete table name while dropping.

    Dont use,
    SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’

    Use

    SELECT * FROM information_schema.tables WHERE table_type = ‘base table’

    In this you will get the owner name as well as the table name.

    Hope this helps.
    ~ IM.

  408. Hi Pinal,

    Hi. I’ve a table Project(ProjectId, ParentProjectId, ProjectName)

    the sample data are –

    ProjectId ParentProject ProjectName
    1 0 X
    2 1 Y
    3 2 Z
    4 3 W

    now for a given project id, say 4, i want to get projectid & projectname whose projectid is 0.

    e.g. i want 1,X for projectid = 4

    i want to SQL query for this. plz help me.

  409. Hi Pinal.

    First of all thank you very much for all of your articles.

    I am planning to develop an MLM software but I am very much confused in designing a database for the same.

    Could you please give me some informational links about MLM (Multi level Marketing ) database design.

    Thank you
    Sandesh Daddi

  410. hi pinal sir ,

    iam new in sqlserver DBA ,

    previous i worked in Biztalkserver and .net

    in the recession i forget my job

    iam converted to sqldba

    iam searching for job.

    please help me sir

    regrading some in interview questions .

    i attened some interview that people asking in

    1) how to find out uncommitted transcations in Sqlserver using DBCC command.

    2) How to find out which table is updated how to find out .

    3) SLA tickets senario asking please tell me some Example s

    Thank you sir

    HARISHKUMAR.M

  411. hi pinal,

    1) how to find out in my table or database in indexes through using DBCC command.

    2) i have ten lacks records in my table that rows move to warehouse without using import&export

    i want spilt my records to 1 to 100000

    2)100000 to 200000
    3)200000 to 300000
    ………….

    …………

    n)900000 to 100000

    with using hints ,i don’t barden to my production server .

    use any commands …………………………

    please tell me

    thank you

    harishkumar.M

  412. Dear Sir,

    On windows 2003 Standard edition SP2, I have installed SQL 2005 & restore the database but while installing .net framework3.5 it gives following error.

    Re-connecting to server (1 OF 5) & finally it gives message cant install.
    .net framework 2.0 is installed , .net framework 2.0 SP1 is installed but .netframework 3.5 not installed.

    Sir please provide me solution ASAP.

    Regards,
    Pavan

  413. I create a Database in MS SQL 2005 and used the database for the developement. But some of the memeber in my office change the table,Procedure in the database. How to find the database changes and Who change the datase. And find who Open the database.

  414. Hi,

    I am Suresh. I installed sqlserver 2008 in windows7 and also windows server2008.

    After installed into my system, i typed “http://localhost/reports” in browser then it is running fine.

    but, it giving the following error while i am accessing report server (http://localhost/reportserver)

    Error: The permissions granted to user ‘SURESH\Suresh P’ are insufficient for performing this operation. (rsAccessDenied)

    Why it is giving this error. Please give me the solution as early as possible.

    Thankyou

  415. Hi,
    This might be the basic thing but not working for me…

    I m dealing with the tables having column names like “a1, a2, a3 ” and trying to fetch result in the following way, but when i “execute” it give me error “Must declare the scalar variable “@a1″….

    Is there any way to iterate through the columns like this…
    Thanks
    Khurram
    Dublin, IRL

    declare @i int
    declare @test varchar(200)
    declare @a1 int
    declare @a2 int
    declare @a3 int
    set @a1=4
    set @a2=5
    set @a3=6
    set @i=1

    while @i<=3
    begin

    set @test = ' select @a'+ convert(char(1),@i) + ' a'
    exec (@test)
    –print @test –'' Print works
    set @i=@i+1
    end

  416. Hi,
    I need a single procedure for adding records that wil be used by 7 forms,it has to identify the form, depending on the form insertion has to be done in that corresponding form..

    Can any one Please help me out for this,

    Thanks in Advance,

    Anisha

  417. Hi,

    I need a single stored procedure that should perform addition, for 7 different forms,So it has to find out which from has been performed depending on that its corresponding table has to inserted

    Please help me n solving this

    Thanks in Advance,

    Anisha

    • Use if statement
      Have a common parameter @form_name that identifies the form and based on that insert into different tables

      But I think it is better you create seperate stored procedures for each form so that it is easy to maintain

  418. hi, I have a problem whe I used a stored procedure, I stay use aplicacion functions with sql2005 , but when the program execute the consult show me a error “Fatal error in the current command. The results, if any, should be ruled” , please help me

  419. Hi,

    i have been using Mssql2000,

    My mdf file length greater than 20Gb.

    Usually Log file(.Ldf) have Huge Length.

    Here My data file length over than 20gb.

    I have Shrink my Log file with Dump Transaction.

    But i dont know,

    How can i shrink My Data File length less than 10Gb?

    Regards

    Nazeer.

  420. I want to synchronize two database in which one is local and another one is global. If I am doing any operation like Insert,Update and Delete, that affects to another database.

    The trigger is working when I am using two local servers which is connected by LAN. But when one is global than it gives me an error.

    An error like
    “The OLE DB provider “MSDASQL” for linked server “(null)” does not contain the table “”Database_Name”.”dbo”.”Table_Name””. The table either does not exist or the current user does not have permissions on that table.

    My Trigger is :

    Create TRIGGER
    [dbo].[Fromlocaltoserverupdate]
    ON
    [dbo].[Table_Name]

    AFTER UPDATE
    AS
    BEGIN
    DECLARE @ID Int,@INAME Varchar(50),@PDESCRIPTION Varchar(150),@Price Int
    SET @ID = (Select ID From Inserted)
    SET @INAME = (Select INAME From Inserted)
    SET @PDESCRIPTION = (Select PDESCRIPTION From Inserted)
    SET @Price = (Select Price From Inserted)

    –PRINT(@ID)
    DECLARE @sSQL VARCHAR(MAX)
    DECLARE @server_name VARCHAR(100)
    DECLARE @user_name varchar(50)
    DECLARE @password VARCHAR(50)
    DECLARE @db_name VARCHAR(100)
    DECLARE @table_name VARCHAR(100)

    SET @server_name=’Server_Name’
    SET @user_name=’sa’
    SET @password=’Password’
    SET @db_name=’Database_Name’
    SET @table_name=’Table_Name’

    set @sSQL=’UPDATE OPENROWSET(”MSDASQL”,”DRIVER={SQL Server};SERVER=’+@server_name+';UID=’+@user_name+';PWD=’+@password+”’,
    ‘+@db_name+’.dbo.’+@table_name+’) SET INAME=”’+@INAME+”’,PDESCRIPTION=”’+@PDESCRIPTION+”’
    ,Price=”’+ Cast(@Price as Varchar(100))+”’ WHERE ID=’+Cast(@ID as Varchar(50))

    print @sSQL
    exec(@sSQL)

    END
    Go

  421. Hi Sir,
    I am getting problem with SQL Host_ID() for web version,
    SELECT Host_ID() is generating different ID number from different workstation and that is good in Standalone Apps.
    But not getting same result in web version. It is generating same ID number from different workstation in Web Apps.
    Please help me out?
    Thank you.

  422. Kurdi

    hello >>>>>>>>>>>>how i can get and display data from sql server 2005 on 2 computer to 1 computer (using vb6)

    ……….Thank you……………..

  423. Hi Pinal,

    I am having a strange error…. I am trying to execute a DB2 stored proc on a SQL Server.. Here is SQL Stored proc

    USE [tx_MS_Prod]
    GO
    /****** Object: StoredProcedure [dbo].[NKN952P] Script Date: 05/15/2012 08:16:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    — =============================================
    — Author: XXXXXla
    — Create date: 05/09/2012
    — Description:
    — =============================================
    ALTER PROCEDURE [dbo].[NKN952P]
    @PI_PROD_ASM_LINE_NO CHAR(1),
    @PI_ROUTE_NO CHAR(6),
    @PI_ROUTE_SEQ DECIMAL(11),
    @PO_RETURN_CODE CHAR(6) OUTPUT,
    @PO_RETURN_MSG CHAR(80) OUTPUT,
    @PO_RECORD_COUNT INT OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;

    declare @var1 char(6)
    declare @var2 char(80)
    declare @var3 int

    EXEC(‘CALL SYSPROC.NKN952P(?,?,?,?,?,?)’,@PI_PROD_ASM_LINE_NO ,@PI_ROUTE_NO ,@PI_ROUTE_SEQ ,@var1 output,@var2 output, @var3 output) AT HAMDB2

    set @PO_RETURN_CODE = @var1
    set @PO_RETURN_MSG = @var2
    set @PO_RECORD_COUNT = @var3

    select @PO_RETURN_CODE
    select @PO_RETURN_MSG
    select @PO_RECORD_COUNT
    END

    I got the following error::

    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.

    Here is the input data

    DECLARE @RC int
    DECLARE @PI_PROD_ASM_LINE_NO char(1)
    DECLARE @PI_ROUTE_NO char(6)
    DECLARE @PI_ROUTE_SEQ decimal(11,0)
    DECLARE @PO_RETURN_CODE char(6)
    DECLARE @PO_RETURN_MSG char(80)

    DECLARE @PO_RECORD_COUNT int

    SET @PI_PROD_ASM_LINE_NO = ‘2’
    SET @PI_ROUTE_NO = ’21’
    SET @PI_ROUTE_SEQ = ‘20120514001’

    EXECUTE @RC = [tn_MS_Prod].[dbo].[NKN952P]
    @PI_PROD_ASM_LINE_NO
    ,@PI_ROUTE_NO
    ,@PI_ROUTE_SEQ
    ,@PO_RETURN_CODE OUTPUT
    ,@PO_RETURN_MSG OUTPUT
    ,@PO_RECORD_COUNT OUTPUT

    Please help me in this regards…

  424. Respected sir,

    Sir How can I AutoIncrement the varchar…like i just want output like this

    Empno Empname
    ————————————-
    Emp001 aaaa
    Emp002 bbb
    ———–
    ———-
    ———
    ———–
    like this sir.
    plz reply me sir.

  425. Hai Sir,

    i have an error “Conversion failed when converting the varchar value 6d6 to data type int” in sql server 2005 & im working in c#.net 2.0 ….

    can you please help me to fix this error…..

  426. Dear sir,
    I installed in windows 732-bit operating system. After installing mssqlserver2005 in my system, if i connect that server by using server management studio i am getting this error message: TITLE: Connect to Server
    ——————————

    Cannot connect to SHRUTHI-PC.

    ——————————
    ADDITIONAL INFORMATION:

    Login failed for user ‘Shruthi-PC\Shruthi’. (Microsoft SQL Server, Error: 18456)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476.
    Please help me sir how to connect with server.

  427. Hey, I think your blog might be having browser compatibility issues.
    When I look at your website in Opera, it looks fine but when opening in
    Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up!
    Other then that, excellent blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s