Contact Me – Archive 2

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.

Child Rights and You:

If this blog has been helpful to you and if you want to help me. Please stand up for the child rights. Donate money to Child Rights and You by visiting their site directly.

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

About these ads

403 thoughts on “Contact Me – Archive 2

  1. I need some help with performance issues.

    we have sql server 2005 running;

    kindly suggest me some scripts that I can run
    and also a place I can get some scripts that I use.
    or
    pl suggest me some books.
    thanks
    sri

  2. I am trying to restore a database that was provided by a vendor. I am running in a SQL 2005 environment and get the following message when trying to restore.

    Msg 3241, Level 16, State 0, Line 1

    The media family on device ‘C:\eMason Backup Files\eMasonClarifire_BOA_backup_200807110015.bak’ is incorrectly formed. SQL Server cannot process this media family.

    Msg 3013, Level 16, State 1, Line 1

    VERIFY DATABASE is terminating abnormally.

    Please advise

  3. Hi Dave:

    I am having a serious issue with merge replication on an SQL Server 2005 publisher/subscriber setup. The problem is on inserting a row into a table I get the following error:

    523: A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding resultset was active.

    There are only merge replication triggers on this table.

    Have you ever seen this and/or know of a possible reason/solution for the problem?

    Thanks for any assistance you can provide.

  4. Hi Pinal,

    I am in serious issue with writing a procedure for Slowly Changing Dimension Type 1 and Type 2 and Fixed Attribute. I have two table new table (i.e reference table) and old table. I want to compare them and if a column is changing attribute it should append the data. If it Type 2, then it should insert a new row with Statusflag column with Current and the old record with a Statusflag ‘Expired’. and if for the fixed attribute column the value is changing, it should not change.

    Can you please help me guiding me with the logic for solving this issue.

    Thanks a lot.

    Zee

  5. Hi Pinal,
    I want to know that how we can find maximum no. from char field in sql Express 2005.

    I am using stored procedure to find max. value which works well for int value but it not work for char value.

    Thanks.

  6. Pinal,
    Can you tell me how do i convert data from excel sheet into a web page by using ASP.net, I need solid solution for this please help me yaar

  7. Hi Pinal,

    Could you please give some notes on replication in SQLserver & Transact sQL made easy … would be very helpful…

    Thanks.

  8. hi,
    i read this site and learn too concept in sql server.

    i have 3 + exp. in .net with sql server exp.

    i want to good job in good it company,

    if any oportunity like me , please info me

    thanks

  9. hi sir,
    this is anil yadav from mumbai.Actually i m doing a coures of SOFTWARE ENG. which all kind of software knowledge.I want to which topic will be help for me in future i mean to say that whether SQL will be better or .NET.Which one more favourable and profitable in future for me.On which subject should i concentrate more.
    thank you

  10. @Naresh,

    I have no idea how one can pick up a maximum value from characters,

    I guess you are trying to find out a record which is very long in length and that column has a char or varchar or nvarchar field,

    I would use this,

    CREATE TABLE EXAMPLE11 ( EID INT, ENAME VARCHAR(100))

    INSERT INTO EXAMPLE11 VALUES ( 2, ‘THIS IS SMALL’)
    INSERT INTO EXAMPLE11 VALUES ( 1, ‘THIS IS VERY VERY BIG’)

    SELECT * FROM EXAMPLE11
    WHERE LEN(ENAME)= (SELECT MAX ( LEN(ENAME))FROM EXAMPLE11)

    I am using len function, which calculates the len of the character and I am findingout which record has maximum characters.

    Hope this helps,
    Thanks,
    Imran.

  11. Pinal,
    Can you tell me how do i import the data of single excel sheet into a multiple tables in sql server.
    For example if i have a sheet with fields branch name, district name ,state name,branch address …
    I need this sheet to be converted to 3 tables(sql server) of branch , state, district .. and branch table should include ids of state and district
    I need solid solution for this please help me..

  12. Hi Pinal.. one question..

    who is the best for performance?

    If exists (select * from where )
    statement1
    else
    statement2

    or
    If exists (select 1 from where )
    statement1
    else
    statement2

    or
    If exists (select from where )
    statement1
    else
    statement2

    or
    If exists (select top 1 <anyone from previous (*, 1 or from where )
    statement1
    else
    statement2

    Thanks in advance!
    regards.
    Adrian

  13. Hi Pinal,

    Could you please give me some notes on comparison with transactions written in TSQL & the transactions written in ADO.NET.
    Thanks in advance
    Divz

  14. Hi Anoo,
    I dont know what exactly you need to do here. But still if it is just importing of Excel Spread sheet into tables, I think DTS package will be the best option for you. Specially if you are going to do it regularly.

    Let me know if you have questions on this.

    Thanks!!

    Hardeep Singh.

  15. Hi Pinal,

    Nowadays I am working on SQL 2000 DTS to SSIS packages. During some of the transformations in SQL 2005 I am receiving following error:

    Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
    Error: 0xC0202009 at Data Flow Task, OLE DB Destination [76]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
    An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Communication link failure”.
    An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “TCP Provider: An existing connection was forcibly closed by the remote host.
    Error: 0xC0209029 at Data Flow Task, OLE DB Destination [76]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “OLE DB Destination Input” (89)” failed because error code 0xC020907B occurred, and the error row disposition on “input “OLE DB Destination Input” (89)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
    Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “OLE DB Destination” (76) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    During development of SSIS packages we are using the SQL 2000 DTS packages for transferring data in SQL 2005 server and it’s giving the same error during transformations. So I have tried the same transformation by using SSIS package and it’s also giving same error.

    I have read different articles on the sites but not yet fixed my problem. Can you please help me out for the solution to fix this problem ?

    Thanks
    Vijay

  16. Hi Pinal,

    I got error related to pool connections max limit in SQL server 2005,
    would you please help me in SQL server restart scheduling automatically to release unused pool connections.

    Regards and Thanks,
    Priyank

  17. Hi Pinal,

    I am trying to execute a Stored procedure using vb 6.0 & database being Sql Server 2005, wherein a Field is been inserted into selected number of database from the list box…
    DB_LIST = DB_LIST + “‘” & LST_DBNAME.Text & “‘, ”
    db_list holds database name seperated by commas & passing a single quote to da Database name.
    its like

    exec UPDATEFIELDS ‘temptable’, ‘databasename’, ”’databasename1”, ”databasename2”, ”databasename3”’
    here databasename1,2,3 is selected from listbox
    This runs very well in Sql Server 2000 but in Sql server 2005, it does not get executed & holds itself at start inside declare cursor where it searches for Database Name from master -> sysdatabases

    when i run da same in Management Studio it Runs smoothly

    really confused…
    please help me fix the problem

    Thanx anyways…..
    Good Day

  18. Hi Dave,

    I’m using SQL with the Northwind database.

    I’m trying to do a query that displays the top 2 invoices for each vendor, in descending order, by invoice total.

    The trouble is, for the result set I’m only getting 1 vendor with 2 invoices and that’s it.

    How would you suggest I write the query that would show each vendor, with their Top 2 invoices?

    A reply to my e-mail would be a huge help! Many thanks in advance.

    KK

  19. HI Pinal,
    I’m new to writing queries in mssql. Need some help in writing a cursor. There are 2 cols in my table 1) CaseDate and 2) CaseId. CaseId have multiple entries with a different timestamp in the CaseDate col. i’m trying to create another col which will contain the min(CaseDate) of that CaseId.
    Eg: below is the existing data

    CaseDate CaseID
    2008-04-16 11:07:36.000 1600195687
    2008-04-16 11:07:58.000 1600195687
    2008-07-21 15:15:55.000 1600331971
    2008-04-05 16:16:06.000 1600331971
    NULL 1600331971

    What i’m lookin @ is
    CaseDate CaseID CaseDate2
    2008-04-16 11:07:36.000 1600195687 2008-04-16 11:07:36.000
    2008-04-16 11:07:58.000 1600195687 2008-04-16 11:07:36.000
    2008-07-21 15:15:55.000 1600331971 2008-04-05 16:16:06.000
    2008-04-05 16:16:06.000 1600331971 2008-04-05 16:16:06.000
    NULL 1600331971 2008-04-05 16:16:06.000
    Can u please guide me to write a cursor (or whatever performs good)

    Best wishes,
    Vallabh

    • Hi Vallabh,
      Please find below code snippet for your answer. Also note that, example below create a temporary table. You can replace this with the actual table.
      create table #temp (
      CaseDate datetime,
      CaseID int,
      CaseDate2 datetime )

      insert into #temp(CaseDate, CaseID)
      select ’2008-04-16 11:07:36.000′, 1600195687
      union all
      select ’2008-04-16 11:07:58.000′, 1600195687
      union all
      select ’2008-07-21 15:15:55.000′, 1600331971
      union all
      select ’2008-04-05 16:16:06.000′, 1600331971
      union all
      select NULL, 1600331971

      update t1
      set t1.CaseDate2 = t2.Min_CaseDate
      from #temp t1
      inner join (select CaseID, min(CaseDate) as Min_CaseDate
      from #temp
      group by CaseID) as t2
      on t1.CaseID = t2.CaseID

  20. Hi Pinal,
    First of all, thanks for all knowledge and articles and most important for sharing with us. I always look forward to new post and always learn something from them. I did not find much on SQLDiag. Could you share some tips on how to start using SQLDiag?

    Thanks again,
    Will T

  21. Hi Pinal,
    I need a SP which inserts IP Addresses which are not found in the table. When we supply the range the SP should insert the IPs found with in the range. With all the combinations.

    Example: 192.168.0.0 – 192.168.255.255

    Please reply soon. Your help would be of much use.

    Thanking you!

    Regards
    Titus

  22. Hi Pinal. Just checking to see if this is the same Pinal that worked with me on Eva’s MS Access Anesthesiology database about 5 yrs ago.

    If it is, HELLO !!!

  23. Hello Pinal,
    I am working on a Financial Project which deals with rural branches .The database size is incresing day by day and it takes about 30 min to do the SOD 2 months back it used to take only 5 min.I think do we have to rebuild all the indexes.Can u tell me how perfomance and Tuning is done in this scenario.
    Actually the whole project is set up on merge pull replication which is running with 6 branches live on Production.The whole end of day and start of day is done from the Haedoffice.Plz revert me if I mislead u somewhere.
    Thanx,
    Amit

  24. Hello Mike Weltman,

    Yes, I am the same Pinal Dave. You were one of the first person to introduce me to database systems. I thank you for the same.

    Kind Regards,
    Pinal

  25. Hello Pinal,
    I have read your blog and it’s very useful for my knowledge. I am very interest with your personal profile and track record. I have problem with my SQL Syntax. How can i copy the data from one table to another table but the structure a both of table are different. Some field are same.

    Please help my syntax..

    Thanks,
    -BS-

  26. Pinal, I am a beginner and just wanting to get my foot wet in learning SQL and wanting to learning more programming. I was looking for some quick tips on how to start learning SQL. How or what steps do I need to take to download a SQL server database to start practicing. I also emailed you on this question. Thanks!

  27. hi
    I want to generate a script,should contains both the structure and the data existing in the database.Is ii available in sql server 2005

    thanks in advance ?

  28. Hello
    Can I insert multipla language rows in their language syntax or charset in a table

    For example I have A table with 1 field varchar(50)

    Rows are

    Example ( in English)
    Örnek ( in Turkish)
    BeiSpiel ( in German)
    ??????? (in Russian)

  29. Hello! I have read your site and have seen some very nice suggestions and tips! One thing I was hoping to see was how would you suggest importing a text file that has no column delimiters (its fixed columns, which I know the definitions of) but the record LENGTH is variable. Some records are 127 bytes and some are 323 bytes.

    DTS, Bulk Import, SSIS, nothing seems to be able to handle this sort of scenario. Variable record length files are normal and common but with SSIS having a problem loading them, how would you recommend getting around it?

    Thanks!

  30. Hello Pinal,

    I searched your website for CDC (change data capture), to learn what SQL server has to offer in this respect. I haven’t found any articles. Do you have any in pipeline regarding this topic?

    Thanks,
    Mahi

  31. Hi Pinal,
    I was wondering how in SQL we will select the top 10 salary people from a database table.

    Thanks in Advance
    Daniel

  32. hi Pinalkumar,

    i need some clarifications for how to access two different database table in a Quey

    Another one Question

    i wrote the split function for the sql2005 that function i need to call query its possible or not some more details for that

  33. Pinal Dave,

    Thanks for providing the valuable Articles. I am new to SQLServer2005, I worked as a Oracle DBA. How to change the TIMEZONE to UTC TIMEZONE in SQLServer2005.

    In Oracle we can issues the ALTER DATABASE SET TIME_ZONE = ‘ ‘ …is there any commands to change in SQLServer2005.

    Thanks in Advance.

    DBAtor

  34. Pinal Sir,

    You are true god of SQL. I wish to meet you once. You and your site has helped me so much that if I can just see you once, my whole life will be worth of it.

    I can only manage my job and feed my family because of you. I read your site and teach developers SQL.

    You are true Hero!

    Joe

  35. I am trying to make a query

    tarih=datetime;
    exp ’01.01.2005 22:00:00′

    “select tarih from th_randevu”
    ı only wantto select date like ’01.01.2005′ from table”
    ı dont want to select time
    please help ,thx.

  36. Hi Pinal

    Please can you help!

    I have an SQL Query which exports records, using ODBC, from a business package called ‘De Facto into a FileMaker 6 database.

    Part of the query says :-
    - where sltr_date >= ’01 jun 08′ and sltr_date = ‘&start’ and sltr_date <= ‘&end’
    (‘start’ and ‘end’ being the FileMaker databse fields)

    Thanks in advance!
    Duncan

  37. @ugur oral

    create table example ( cola datetime)
    insert into example select ’01.01.2005 22:00:00′
    select convert(varchar(10), cola , 104) from example

    Result: 01.01.2005

    Hope this helps,
    Thanks,
    Imran.

  38. @mohan

    use database_name
    Sp_help table_name

    This stored procedure gives all the details of column, their types, any indexes, any constraints, any identity columns and some good information for that particular table.

    Second method:

    select column_name ‘Column Name’, data_type ‘Data Type’, character_maximum_length ‘Maximum Length’ from information_schema.columns where table_name = ‘table_name’

    Hope this helps,
    Imran.

  39. Very nice Banner.

    Sir, In Bangalore we have created fan club of yours.
    We meet twice a week. Currently we have 46 members in our group. We pick any of your article and discuss in depth.

    We are learning group so we are going to stay away from fancy websites or sponsers or funding. We just meet and learn. We use your website as our base.

    Would it be possible for you to visit us once? Please please sir, it will be our dream come true.

    Maya Thanki
    (Member – SQLAuthority Fan Club, Bangalore)

  40. Dear Sir,

    Thank you for sending us email. I do understand that you are skeptical about SQLAuthority Fan Club. Believe me it exist and I will send you photos of it next time when we meet this week.

    Maya Thanki
    (Member – SQLAuthority Fan Club, Bangalore)

  41. Pingback: SQL SERVER - 2005 - Get Field Name and Type of Database Table Journey to SQL Authority with Pinal Dave

  42. Help please with a dynamic data source in Reporting Services/SQL2005.

    1. I have a completed report that works with the following connection string:
    Data Source=BUTTERFINGERS\SQLEXPRESS;Initial Catalog=VC
    2. I am trying to replace the database name, in this case VC with a variable so the user can select which DB to run the report against as per the instructions from MSDN and a few other sites.
    3. When I change to:
    Data Source=BUTTERFINGERS\SQLEXPRESS;initial catalog=Parameters!DatabaseName.Value and add a report parameter in VS2005 and run I get the following error:
    Cannot open database “Parameters!DatabaseName.Value” requested by the login. The login failed. Login failed for user ‘ADPRO.COM.AU\steveha’.
    4. What I find interesting is that of course it cannot login with “Parameters!DatabaseName.Value” as this is not a valid DB, it is the variable name. It never seemed to riun the report and prompt me the report parameter to enter a valid DB name.
    5. I would have thought that when the report was run it would ask for a DB name via the report parameter, replacing the variable name with the actual string or name of the DB and then run so in essence the connection string would be the same as my original working string? Credentials set to Use Windows Authentication, Type: Microsoft SQL Server, Use shared data source checkbox off.
    Note I am a beginner.

    Have I lost the plot here:)

  43. Pinal,

    I want to combine two colums from two different tables where is no unique identifier.

    Eg:

    Table 1 :pen

    item description Qty

    Table 2: Pencil

    Product supplier orderdate

    I want to combine item from table 1 and orderdate from table 2.

    Thank you

  44. Hi Pinal,

    SQL HELP

    We have some issue regarding sql server on our website due to which our site is down since morning.

    Could you please mail me your contact number so that I can brief you about the same .

    Thanks ,

    Vishal

  45. i want to Set first day of week to Monday as oppose to default Sunday.

    Is it possible through any sql setting.
    (for permanent setting according to user)

    example:- if i change in windows registry start date 0 to 7 . then our calender show according to my setting. i want same thing in sql server 2005??

  46. Hi,

    I want to create new copy of database on same server with data and all elements i.e. views, stored procedure etc.

    It is SQL server 2000.

    Is there any way to achieve this using SQL that I can run from VBScript?

    Thanks
    Chetan

  47. Hi,
    I want to create a temp table and wants to add columns dynamically with autogenerated columns names.

    example: i have given StartTime and EndTime for a day
    @StartTime = 9
    @EndTime = 6
    now i want to create table with autogenerated columns from 9 am to 6 pm [ columns names should be 9, 10, 11, 12, 1, 2, 3, 4, 5, 6 else it can be 9 am, 10 am, 11 am, 12 pm, 1pm, 2pm, 3pm, 4pm, 5pm, 6pm]

    how can i achieve this, its urgent
    Thanks in advance

  48. Hi Sir,

    i want to Set first day of week to Monday as oppose to default Sunday.

    Is it possible through any sql setting.
    (for permanent setting according to user)

    example:- if i change in windows registry start date 0 to 7 . then our calender show according to my setting. i want same thing in sql server 2005??

  49. hello pinal sir,
    i am really thankful of yours as whenever SQL server have problem with me your posts work as a guide for me.
    But since last 2 week i am continuously fighting with the SSIS package.

    As i have made SSIS Package for Traansfering data from .CSV file to table in my database.
    While creating these SSIS PAckages I have tried with all the Package Protection Level,but still while executing these SSIS packages i m not getting my password for the User in connection string.every time i have to update it manually.

    Bcoz of this i m also not able to make SP pr JOb for automation of Package Execution.
    So can YOu plzplzplz help me in this.
    i will be relally a grate thank full of urs.
    as i m trying since two week i not successed.
    any kind of help from any one should be appriciated.

    Thanks in advance.
    Regards,
    Jigar

    • Jigar,
      When you are referring passwords w.r.t packages, these are meant for security purposes to prevent others to modify the package. Package Passwords comes handy. Once you set this, every time you open the package, it asks. However, the password for User in Connection string is something to be configured dynamically. Refer Package Configuration in SSIS

  50. I want to generete the time with 30min gap and add that column to #temp table
    like this 09:00am, 09:30am, 10:00 am ….. 04:30 pm, 05:00 pm

    I wrote this SP but its adding @test as column name but i want to value inside the variable should be column name. if i set @test = 10 then the column name should 10 and if i set @test = 11 then the column name should be 11. how can i achieve this
    —————————————————————–
    CREATE PROCEDURE [dbo].[TestingAvailability]

    AS
    BEGIN

    DECLARE @VisitingHoursStartTime AS INT
    DECLARE @VisitingHoursEndTime AS INT

    SET @VisitingHoursStartTime = 9 — morning 9 am
    SET @VisitingHoursEndTime = 18 — evening 6 pm

    IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[t1]‘) AND TYPE IN (N’U’))
    DROP TABLE t1
    ELSE
    CREATE TABLE t1(Id NVARCHAR(50))

    DECLARE @test NVARCHAR(100)

    – Loop to add columns to temp table
    WHILE (@VisitingHoursStartTime < @VisitingHoursEndTime)
    BEGIN

    SET @test = @VisitingHoursStartTime

    IF NOT EXISTS ( SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(‘[dbo].[t1]‘) AND NAME = ‘@test’ )
    ALTER TABLE t1 ADD [@test] NVARCHAR(100) NULL

    SET @VisitingHoursStartTime = @VisitingHoursStartTime + 1;

    IF @VisitingHoursStartTime = 19
    BREAK;
    END

    SELECT * FROM t1

    DROP TABLE t1
    END

    ===================
    its very urgent

  51. I changed my code and created dynamic sql, it worked
    ————–
    SET @DynamicSQL = ‘ALTER TABLE #Temp ADD ['+ CAST(@VisitingHoursStartTime AS NVARCHAR(100)) +'] NVARCHAR(100) NULL’

    EXECUTE (@DynamicSQL)

  52. I have a problem in writing a query for the following logic.

    For the same custID and transactionNO there are few records matching as shown below.

    In a such case i have to get data to Prd Column from other lookup table and update into Dest Column based on the following condition

    1) If the value in Indicator coloumn is 1 and RelKey column is having 4005 then i need to set the value in the dest column for the respective row with Data from lookup table

    2) If the value in Indicator coloumn is 0 and RelKey column is having 4005 then i need to set the value in the dest column for the respective row with 0

    3) If the value in indicator column is 0 and relkey column is having Null then also i need to set the value in the dest column for the respective row with Data from lookup table

    I have millions of records in this table so without using cursor, how do i write a qurey for this logic.

    CUSTID TransactionNO Indicator RelKey Prd Dest

    123456 Trans12345 1 4005 A1 X
    123456 Trans12345 0 4005 A2 X
    123456 Trans12345 0 4005 A3 X
    123456 Trans12345 0 NULL A4 X
    98765 Trans98765 1 5675 A5 X
    98765 Trans98765 0 5675 A6 X
    98765 Trans98765 0 5675 A7 X
    98765 Trans98765 0 NULL A8 X
    98765 Trans98765 0 NULL A9 X
    98765 Trans98765 0 NULL A10 X

  53. Hi Pinal,

    Thank you very much for an awesome website. It has certainly helped me out of many a jam.

    I am trying to get this stored procedure to execute but am having some difficulty.

    I call the stored procedure from an ASP page using the ADODB.COMMAND and passing two parameters to the sproc.

    The code works fine but the sp does not execute.

    Here is the sp:
    ******************************
    USE [xxxxxx]
    GO
    /****** Object: StoredProcedure [dbo].[xxxxxxx] Script Date: 08/08/2008 07:55:25 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[xxxxxx]
    @inputfile VARCHAR (500),
    @outputfile VARCHAR (500)
    AS
    DECLARE @ifile VARCHAR(500)
    DECLARE @ofile VARCHAR(500)
    DECLARE @sqlcmd VARCHAR(8000)

    SET @ifile = @inputfile
    SET @ofile = @outputfile
    SET @sqlcmd = ‘SQLCMD -Sxxxxx -d”xxxx” -Usa -P”xxxxx” -i”‘ + @ifile + ‘” -o”‘ + @ofile + ‘” -h 2000000 -k1 -W -s”,”‘

    EXEC master..xp_cmdshell @sqlcmd

    **************************

    Any comments will be greatly appreciated

  54. Hi,

    is there a stored procedure to display the records of all queries executed in an sql server

    if not, can anyone help with a script to display the history of logins and the queries executed by the login

  55. Hi

    I am in here big probleam my database is replicated and my log size is 55gb how i can shrink.please Help me As soon as possible i am in here denger position about this issue
    please Help me

    Regards
    Tito

  56. Hello,

    I’d like to have on the same PC :
    - SQL Server 2005 Standard
    - SQL Server 2008 Standard

    At this time, I have “only” the 2005 version. Could you tell me, if it’s possible, how to install the 2008 version ? As .NET developer, I’d like make some tests (with VS2008) on both database. I know to use SQL2008 we have too wait the VS2008 SP1.

    Regards,

  57. Is it possible to take Backup and update using table at the same time ms sql 2005? If it is yes please let me know the updated rows in the table also available in backup file.

    Thanks

  58. Hi Pinal
    Well you doing great job by helping others here ……
    i wanted some information like how to run sql server from sqlcmd i mean in 2005 and how to run sql server in a single user mode and muti user mode and one more thing there is a option to disable graphival view when sql server starts in sql cmd what is that….

    please show me with examples…..

    thanks a ton

    Vaibhav Mathur
    9227900650

  59. Hi my name is sushma,

    I am very new to SQL SERVER. How can i approve for best result. please help me for programs and i need some notes . Please help me on this . I am very much interested to do job so please help me.

    Thanks ,
    Sushma

  60. I have a problem. I had SQL 2000 set up a database maintenance to back up the mdf and ldf files every night. It was backing up but not truncating. Now, our data does not have current data. We do have the log file and I did manually truncate it, thus committing the transactions so going forward we are ok.
    Here is the problem. We need the february 29th database. Our back up software has it but the LDF file is current and the MDF file never updated. How do I selectively update a database from the log file of only the transactions up to February 28th? I have a test server with SQL installed. I just dont know what to do.
    To recap.
    Until Yesterday our production database has only been updated by the log through the end of the year. The LDF file just grew. I made copies of these files (backups) then I manually truncated the log file and commited those transactions.
    Now, how do I commit transaction from the log file to the database only through Feb 29th. Does that make sense?
    Any help would be great.

    Thanks!

    _Chris

    If you are wondering why, it’s because the software does a monthly export and we think something was wrong with that month but need to look at it before the March 1′st export of GL transactions.

  61. @vaibhav
    By default sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

    If you still have that file located at the same location then you can run this command in command prompt.

    1. For named instance :

    sqlservr.exe -m -s

    2. For default Instance :

    sqlservr.exe -m

    If you changed the location of the file, I dont know how to do this, I usually do it through services,

    Click start, run, type services.msc

    select the sql server you want to run in single user mode, double click server name, click stop and then in parameters give -m value and then start, sql server will start in single user mode.

    if you want to remove sql server from single user mode, stop sql server and this time dont give any parameter. sql server will start in normal mode.

    Hope this helps,
    Imran.

  62. I have 4 indexes (1 clustered and 3 non-clustered) on a table which is 105 GB. I want to run the following code to rebuild all the indexes on this table:

    ALTER INDEX ALL ON Product REBUILD

    Question is – During this operation will other users be able to access the product table? I know SQL 2005 supports online index operations. But wasn’t sure if it supports the following data types:

    7 columns and the data types for them are as follows:

    3 – uniqueidentifier
    1 – varbinary(16)
    1 – int
    1 – image
    1 – tinyint

    Can index be created with online option for this table? My SQL version is 2005-64 bit.

    Appreciated any input

  63. Hi Pinal,

    I want to discuss about indexes

    what are the best techniques to make indexes because sometime performance increase or sometimes decreases if index will not make properly

    kindly comments on this

    thanks,
    SJ

  64. Hello Pinal

    I am Using SQL SERVER 2005 and I have made 2 instances

    1.Default(MSSQLSERVER)
    2.SQL05

    Default instance is working fine with remote connections but
    named instance is not working.It uses another port i have checked with SQL Surface Area configuration and i have checked also TCP/IP dynamic port 0 and TCP/IP port is a copy of below defined port through Configuration Manager

    All in one

    I have a problem with remote connection on named instance

    thanks,
    SJ

  65. @SJ

    1. Make sure you have sql server broswer service running ( but if you can connect to default instance that means your browser service is on ).
    2. IN SQL Server 2005, when you create a names instance, the named instance will be the name given inluding the machine name,

    lets say the machine name is PROD and you named the instance as SVR1 , then your server name will be

    PROD\SVR1. try using this method.

    3. can you please post the error you got when you tried connecting to named instance, remotely.

    Thanks,
    Imran.

  66. Hi Pinal,

    Interview questions are really useful.Could you please provide examples for joins and keys(primary,foreign and unique).In most of the interviews they give tables and as us o join those tables.Please provide examples.

    Thank You.
    Raji.

  67. Hello Sir,

    when i am working the below task it gives result as below
    please advise the

    task : to find the size of a table?
    query : exec sp_spaceused [tablename]
    result :
    name rows reserved data index_size unused
    Tablename 145 888 KB 248 KB 16 KB 624 KB

    here
    name means — tablename
    rows means — no of rows in that table

    i have little confusion on “reserved,data,index_size,unused”
    please advice how it calculated.

    Thanks,
    venkat

    • FROM SQL Server help file

      reserved
      varchar(18)
      Total amount of reserved space for objname.

      data
      varchar(18)
      Total amount of space used by data in objname.

      index_size
      varchar(18)
      Total amount of space used by indexes in objname.

      unused
      varchar(18)
      Total amount of space reserved for objname but no yet used

  68. Hi Pinal,

    I have 3 tables in one server database and want to copy that data in to my Local database (b’cos I want to work with that local data with out effect that original data)
    Please help me do this.

  69. Hi

    I have an issue i have a table that has many rows
    for example

    table A

    a b
    xfactor 20
    xfactor 20
    media 30
    media 30
    how can i sum the individual eg i wana add the value of all the xfactor in a table how can i get that
    thanks Namita

  70. Sub : MSSQL DBA vs. MYSQL DBA

    Dear Pinal ,

    I would like to thanks & appreciate from the bottom of my heart for your blog.

    Currently I am working as a System Administrator on Windows Platform

    I am very interest & Passion to start to work as a DBA.

    As some of my colleagues are said that go for MYSQL DBA
    I am in bit confuse to choose

    MSSQL DBA vs. MYSQL DBA which will have sound bright carrier and future?

    I am kindly requesting to give you advice

    Thanks in advance

    Regards
    Kalyan.

  71. Hi pinal,

    i have a table with pvn_no, alert_name, and email_addr.

    pvn_no alert_name email_addr

    10 AL1 AKS@C.C0M
    10 AL1 LAK@V.COM
    10 AL1 HSJ@B.COM
    10 AL2 YET@J.COM
    10 AL2 KOI@D.COM
    20 AL3 POL@M.COM
    20 AL3 WGE@S.COM

    NOW I WANNA A RESULT SET LIKE

    pnm_no alert_name email_addr
    10 AL1 AKS@C.COM,LAK@V.COM,HSJ@B.COM
    10 AL2 YET@J.COM,KOI@D.COM
    20 AL3 POL@M.COM,WGE@S.COM

    for each combination of pvn_no & alert_name we have 8 to 10 email. how can i concatenate them in a single row.

    Thanks in advance
    Aks

    • Hi,
      Please find below code snippet for your question. Run the same and experience the solution

      create table tab_example (
      pvn_no int,
      alert_name varchar(10),
      email_addr varchar(100))

      insert into tab_example
      select 10, ‘AL1′, ‘AKS@C.C0M’
      union all select 10, ‘AL1′, ‘LAK@V.COM’
      union all select 10,’AL1′, ‘HSJ@B.COM’
      union all select 10,’AL2′, ‘YET@J.COM’
      union all select 10,’AL2′, ‘KOI@D.COM’
      union all select 20, ‘AL3′, ‘POL@M.COM’
      union all select 20, ‘AL3′, ‘WGE@S.COM’

      create function udf_csv_data(@pvn_no int, @alert_name varchar(10))
      returns varchar(1000)
      as begin
      declare @sql varchar(1000)
      select @sql = coalesce(@sql + ‘, ‘, ”) + email_addr
      from tab_example
      where pvn_no = @pvn_no and alert_name = @alert_name
      return @sql
      end

      select pvn_no, alert_name, dbo.udf_csv_data (pvn_no, alert_name) as csv_email_addr
      from tab_example
      group by pvn_no, alert_name

  72. Dear Sir,

    Thank you for visiting our organization and teaching our kids SQL. We are ready to pay any fee if you can visit us one more time.

    I think you have inspired people here.

    Thanks,
    Roger Ben Silva
    .NET Learning Institute of India

  73. I have to create maintenance plan on sql 2005 to check the log and mdf file size ,I want sql script to chk this and notify admin

  74. Pinal,

    Thank you for the great site. Here is my query question:

    How do I add an extra row to a query restult set that is not part of the table being queried?

    i.e.

    I have a query that returns this:

    2008
    2007
    2006
    2005
    2004
    2003

    I would like the query to return this:

    2008
    2007
    2006
    2005
    2004
    2003
    All

    So I want to add the string ‘All’ to my query result. ‘All’ is not, and should not be, part of the table I am querying.

    Here is my current T-SQL code:

    SELECT DISTINCT [Program_Year] FROM [DB1].[dbo].[Projects_All]

    How can I modify that query to add ‘All’ into the results?

    I could have sworn I knew how to do this in Access, but am having trouble getting it to work in SQL Server 2005.

    Thanks,

    Scott

    • Why do you want to do this?
      Note that you should do this in your front end application.

      If there is no option

      SELECT DISTINCT cast([Program_Year] as varchar(10)) as [Program_Year] FROM [DB1].[dbo].[Projects_All]
      SELECT 'All'

  75. Hi Pinal
    Good to see MVP with your name.
    i have 3 question.

    I want to do some data migration , for that purpose

    1- i want to disable all relationships present in my database. so i can do my migration. ?

    2- and also after complete migration , how i can enable the relationship again ?

    3- I have a database which have more than 50 tables , and fist colum of everytable contains a serial number, but serial number is varchar field,

    I want access maximum amount of number which is present in all table’s first colums.

    Waiting for your earliest reply.

    Best Regards
    Sharjeel

    • Also, below is one more method which is much faster
      set rowcount 10
      select salary from mytable
      order by salary desc
      set rowcount 0

  76. We are mainly a SQL Server 2005 shop. Most SQL installations are on Windows 2003 and we are using Active Directory for SQL Logins.

    Our problem comes with the way SQL Server Integration Services work. It requires Local Admin rights on all SQL Servers for users to be able to view running packages or depoly them.

    Is there away to grant these rights (view/deploy packages) without having these users as local admins on the servers?

    Thanks for any suggestions or ideas.

  77. Dear Pinal,

    I’m using SQL Server 2000. how to convert the following sample 1 to sample 2 ??
    Sample 1:
    Id Tag

    1 a
    1 b
    1 c
    2 a
    3 a
    3 c
    4 d
    4 e
    4 a
    (No null values in Id/Tag)
    Sample 2:
    Id Tag

    1 a,b,c
    2 a
    3 a,c
    4 d,e,a

    Kindly advice me

    Thanx in advance

    • Nandha,
      Please find below code snippet
      create table tab_example (
      id int,
      tag varchar(10))

      insert into tab_example
      select 1, ‘a’ union all select 1,’b’
      union all select 1, ‘c’
      union all select 2, ‘a’
      union all select 3, ‘a’
      union all select 3, ‘a’
      union all select 3, ‘c’
      union all select 4, ‘a’
      union all select 4, ‘d’
      union all select 4, ‘e’

      alter function udf_csv_data(@id int)
      returns varchar(1000)
      as begin
      declare @sql varchar(1000)
      select @sql = coalesce(@sql + ‘, ‘, ”) + tag
      from tab_example
      where id = @id
      return @sql
      end

      select id, dbo.udf_csv_data (id) as csv_email_addr
      from tab_example
      group by id

  78. Dear,
    I have faced some problem.
    In my code I used unicode for type Bangla. When I save it into Access Database,
    show error: Syntax error in INSERT INTO statement.

    I used Visual Studio.Net, C#.Net.
    Please, help me how can I solve this proble.

    Zahid.

  79. Hi.

    Database Mirrioring Problems
    I am getting the problem for database mirrroring, after the configration of the mirrioring

    the error code is : 1498

    configuration of principle endpoint is failed
    configuration of mirror endpont is failes

  80. Hi Pina,

    I just migrate my database from sql 2000 to sql 2005 and ran a test for writing data to dbf file (ms foxpro). The data in sql is ‘float’
    for ex. 4000. but when this data is writen to dbf the format change to 4,000.00. this is a problem because now my dbf database see it as 4 instead of 4000. This is a major concern since we are dealing with inventory.

    Please help me on how/what setting I need to make

    Thank you in advance for your help.

    Dat

  81. Hello Dave!!!
    I’ve been meaning to write to you for a while now!
    me and my partner are working on our thesis and everytime we encounter a problem with some query I google it and There you are! with the solution or at least most of the times!!
    so Really apreciate it that you have this really really nice website!!!
    I mean it ! it’s like 4 out of 5 times that your site comes to solve our doubts!!!
    So Thanks a lot!
    Regards From Caracas – Venezuela
    Corina

  82. iam using sqlserver 2005 .can u tell me how to get the first letter capital in a word using T-sql.for eg: good morning as Goodmorning

  83. @Siju,

    I am sure there must be a good way of doing this, I would use something like this,

    create table example ( ename varchar(100))

    insert into example values ( ‘good morning’)
    insert into example values ( ‘hello’)

    select upper(substring (ename ,1, 1))+substring(ename,2,len(ename)) from example

    Result:

    Good morning
    Hello

    Hope this helps.
    Imran.

  84. Hello,

    I am new to SQL Server2005 and am in the midst of importing 12 separate Access Tables into one database SQL Table.

    I have created a SSIS package to loop through my Access Tables, but instead of it looping through EACH Access Table…it is looping through only the first Access Table 12 separate times, thus multiplying the data in this table 12 times!?

    Please help…I don’t know where I am going wrong.

    Thanks!
    Zin

  85. Hello
    I want to apply search feature on my website.How to write stored procedure for the same.
    Main problem is that i can easily select data from one table but when i have to select data from multiple tables and these tables are not linked with each other.How can it be possible.
    Please help me or drop me mail at above mentioned addreess.
    Thanks

  86. Hello Pinal,

    Need help with one query.
    This is my input table

    Item—————–Qty
    xyz —————— 0
    xy1 —————— 0
    xy2 —————— 0
    abc —————— 1
    ddd—————— 1
    eee—————— 3
    xys—————— 4
    yyy—————— 4
    nnn—————— 4
    ppp—————— 4
    ggg—————— 6
    kkk—————— 6
    lllllll—————— 7

    I want the 3 least ordered Qtys,
    This should be the output
    Item—————–Qty
    xyz —————— 0
    xy1 —————— 0
    xy2 —————— 0
    abc —————— 1
    ddd—————— 1
    eee—————— 3

    Help much appreciated.
    Thanks

  87. @tibya

    I am sure there must be a good way for the same,

    Ex:

    create table example11 ( item varchar(100), qty int )
    go
    insert into example11 values (‘xyz’, 0)
    insert into example11 values (‘xy1′, 0 )
    insert into example11 values (‘xy2′, 0 )
    insert into example11 values (‘abc’, 1 )
    insert into example11 values (‘ddd’, 1 )
    insert into example11 values (‘eee’, 3 )
    insert into example11 values (‘xys’, 4 )
    insert into example11 values (‘yyy’, 4 )
    insert into example11 values (‘nnn’, 4 )
    insert into example11 values (‘ppp’, 4 )
    insert into example11 values (‘ggg’, 6 )
    insert into example11 values (‘kkk’, 6 )
    insert into example11 values (‘lllllll’,7 )
    go

    select * from example11 where qty in ( select distinct top 3 qty from example11 )

    Result:

    xyz 0
    xy1 0
    xy2 0
    abc 1
    ddd 1
    eee 3

    Hope this helps.
    Imran.

  88. Hi,

    I have create a linkserver to read a vfp file with a photo field, the name and file STRUCTURE of vfp as follows:-

    /*
    ITEMPHOTO TABLE STRUCTURE
    VFP: (ITEM_NO CHAR(10),ITEMPHOTO GENERAL)
    SQL: (ITEM_NO CHAR(10),ITEMPHOTO IMAGE)
    */

    The link server as follows:-

    –EXEC SP_ADDLINKEDSERVER
    – @SERVER = N’FOX2′, — YOUR LINKED SERVER NAME HERE
    – @SRVPRODUCT=N’VISUAL FOXPRO 9′, — CAN BE ANYTHING
    – @PROVIDER=N’VFPOLEDB’,
    – @DATASRC=N’”C:\BTM\123\DATA1.DBC”‘
    –EXEC SP_ADDLINKEDSRVLOGIN ‘FOX2′

    After the server is created i checked to read the file without the photo field it is he read the file with no error, but with the photo field i have the below error :-

    SELECT item_no,photo FROM OPENQUERY(FOXPRO,’SELECT item_no,photo FROM ITEMPHOTO’)
    the error message as follows:-

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “VFPOLEDB” for linked server “FOXPRO” reported an error. The provider ran out of memory.
    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider “VFPOLEDB” for linked server “FOXPRO”.

    best regards

  89. Hi All,

    Has any one got a template of sql server build documentation/Disasater recovery plan that he/she would like to share.

    I just need a tempplate I can use as a base for my own documentation.

  90. Hi all,

    Im using SQL 2005, when runninig a trace using Profiler and using the ‘tunning’ template the duration column is displayed in microseconds. I am running the trace to determine how long it takes for stored procedures to complete. I want to convert the duration column to seconds, how do i do that?

    Thanks

  91. Hi Pinal,

    I have a problem about migration from SQL-2000 to SQL-2005.

    Could you please give some idea about what are the changes are required (code/server level) after migrating the database from 2000 to 2005 with compatibility level 80? This would be really appreciated.

  92. @ Hitesh,

    If you migrate sql server 2000 database into SQL Server 2005, I am quite sure the compatibility of that database will still be set to 80.

    70 – SQL Server 7.0
    80 – SQL Server 2000
    90 – SQL Server 2005
    100 – SQL Server 2008

    IF Compatibility is set to 80, NO matter if you are using SQL Server 2005, your database will still act as if, it is in SQL Server 2000.

    To change compatibility, Right click database -> properties -> On left side of the dialog box, select option -> right side set compatibility to 90 ( select from drop down list) -> click ok …. now your database will act as if it is in SQL Server 2005.

    Just to confirm, right click database -> click reports->standard reports – > disk usage, If you see a report then it is confirmed that your database is in SQL Server 2005 compatibility level.

    Just do the reverse, set the compatibility to 80 and try to run a report, you will see an error saying, database is in compatibility 80.

    Hope this helps.
    Imran.

  93. hi iam new to sql server and want to know how solve this can u give me the procedure for this

    i have two tables A and B i have to join these tables in to
    a another table C (append) and delete the duplicates and there are no primary key, can some on e give me the query for this and explain how it works

    thanks in advance

  94. Is there a way to move the data from the secondary database file to the primary database file? I asked this because we have a database were the secondary database was created by mistake and that has caused the full backups to fail.

  95. Hi,
    I have a problem with connection microsoft sql server 2005 and php, i need to retrive some data from mssql 2005 tables by using php the problem when i save the php file into htdocs file and open a browser by local server the browser display empty page or give a fatal error:
    Fatal error: Call to undefined function mssql_connect() in C:\AppServ\www\gggg\adminloginz.php on line 12

    could you please help me to solve this problem as i need to finish my project urgently.

    note: i am not understand microsoft sql server 2005.

  96. hi Mr.Dave,

    How to compare the rows to columns.

    Eg:
    Table1
    ColumnId

    Column1
    Column2
    Column3
    Column4
    Column5
    Column6

    Table2
    Column1 Column2 Column3 Column4 Column5

    I want to know which are the Columns are not there in the Table2 when comparing with the Table1 rows
    plz give me Query for this.

    Regards,
    Malik

  97. @ Malik,

    I am sure there must be a good way to do this, I might try something like this,

    create table example1 ( columnid varchar(max))
    go
    insert into example1 values ( ‘column1′)
    insert into example1 values ( ‘column2′)
    insert into example1 values ( ‘column3′)
    insert into example1 values ( ‘column4′)
    insert into example1 values ( ‘column5′)
    insert into example1 values ( ‘column6′)
    go
    create table example2 ( column1 varchar(max),column2 varchar(max),column3 varchar(max),column4 varchar(max),column5 varchar(max))
    go
    create table #result ( columnid varchar(max))
    go
    insert into #result select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = ‘example2′
    go
    select columnid As ‘Column Missing in Example2′ from example1
    EXCEPT
    select columnid from #result
    go
    drop table #result, example1 , example2

    Result:

    Column Missing in Example2
    column6

    Hope this helps,
    Imran.

    • Check this out :
      select t1.ColumnId from Table1 t1
      where not exists (select 1 from sys.columns s
      where s.name = t1.ColumnId
      and object_id = object_id(‘Table2′))

      • or

        select t1.column_name from information_schema.columns where table_name=’table1′
        where not exists(select * from information_schema.columns where table_name=’table2′ and column_name=t1.column_name)

  98. hi,

    I need a help or suggestion from you.

    I have a table, in that a column is encrypted(SYMMETRIC KEY encryption) in binary data format in the database. I have a SP to encrypt the plain text which receive an input and gives the encrypted data.

    I have to copy the whole table to same structured table in the different database as a batch update. I created a SP which receive the source table in XML. Everything working fine except the encrypted data.If try to decrypt the data after the copy, I am not getting the original data.

    So have plan to encrypt the plain text once again before insert into the destination table.But stuck in calling the SP in the batch insert(which provide the required encrypted by receiving the plain text). I am not able to convert that procedure to function also.

    I tried with cursor by looping each record, encrypting and insert into destination table.Its working but take so much of time.

    my questions:
    how to maintain the format of encrypted data while passing thru XML, or any way to convert the binary encrypted data to XML string and in the SP again convert it into binary data in batch insert and update.

    Is there any possibility to call the stored procedure within the batch insert statement like user defined function

    Insert into table_1 values(number, sec_data)
    (@row_num,sp_encryp(@plain,@encrpy))

    Please advice me

  99. Hello Imran Mohammed, Pinal Dave,

    Imran has already give me a solution to my previous query and this one is the similar to it (actually, this is the one I was looking for), so addressing this to Imran.
    I am a Intern and trying to learn sql as much as I can in this project. My queries may seem very simple to you all, but for me this is the first step.

    Anyways, here I go:

    This is the output I need:
    BOTTOM 3 Items ——————————–QTY
    xyz- ———————————————— 3
    xyz1————————————————-3
    xyz2————————————————-3
    xyz3————————————————-6
    xyz4————————————————-6
    xyz5————————————————-10
    xyz6————————————————-10
    xyz7————————————————-10
    xyz8————————————————-10
    xyz9————————————————-10

    I have 3 tables, Order_Master , Order_Detail and Stock

    Order_Master – has fields client_id, order_id
    Order_Detail – has order_master_id (linked to order_id of Order_Master), Qty

    NOTE : Qty shown in desired output is sum(Order_Detail.Qty) for multiple orders.

    I can get all the data using this query below, but I am unable to derive the Bottom 3 Qtys (Note: its not the bottom 3 rows that is needed, but all the Qtys that are the least 3).

    Select Sum(Order_Detail.Qty) As Qty, Stock.Item_Description As
    All_Item_Description
    From Order_Master Inner Join
    (Order_Detail Inner Join
    Stock On Order_Detail.SKU = Stock.SKU) On Order_Master.Order_ID =
    Order_Detail.Order_Master_ID
    Where Order_Master.Client_ID =999
    Group By Stock.Item_Description
    Order By Qty ASC

    This gives the output as:

    xyz- ———————————————— 3
    xyz1————————————————-3
    xyz2————————————————-3
    xyz3————————————————-6
    xyz4————————————————-6
    xyz5————————————————-10
    xyz6————————————————-10
    xyz7————————————————-10
    xyz8————————————————-10
    xyz9————————————————-10
    xyz10————————————————70
    xyz11————————————————70
    xyz11————————————————70
    xyz11————————————————70
    xyz11————————————————70
    xyz11————————————————70
    xyz11————————————————70
    xyz12————————————————100
    xyz13————————————————100
    xyz14————————————————100
    xyz15————————————————278
    xyz16————————————————300
    xyz17————————————————300
    xyz18————————————————300
    xyz19————————————————300

    Thanks for all your help.

  100. Hello

    I am not SQL Expert either,

    Add one line to your query and you should get desired output.

    Select Sum(Order_Detail.Qty) As Qty, Stock.Item_Description As
    All_Item_Description
    From Order_Master Inner Join
    (Order_Detail Inner Join
    Stock On Order_Detail.SKU = Stock.SKU) On Order_Master.Order_ID =
    Order_Detail.Order_Master_ID
    Where Order_Master.Client_ID =999 and Order_Details.Qty in (select distinct top 3 Qty from Order_Detail order by Qty ASC)
    Group By Stock.Item_Description
    Order By Qty ASC

    Hope this helps.
    Imran.

  101. Hi,

    I’ve got database with me which was created in MSDE initially and by mistake MS SQL 2005 Express edition was loaded on same machine. now when i am trying to attach same database with MSDE. It is not getting attached. Please help how to attach the database with MSDE again. Is there any way of downgrading database for MSDE. I am not expert on scripting.

    Its little critical please help out.

  102. @Narender

    MSDE is free edition of SQL Server 7.0 and SQL Server 2000. And SQL Server Express is free edition if SQL Server 2005.

    By default I dont think there is anyway to restore 2005 database to SQL Server 2000.

    But this article gives a very good details about how to do this… follow these instructions they are very easy and simple, and everything is through wizard, so you just have to follow the wizard.

    Hopefully your issue will be solved.

    Here is the link : http://www.cmsdev.com/node/20

    Starting few lines just ignore it….

    Hope this helps.

  103. Hi

    i have copied a db from one server to another long with all the log on and jobs. but alot of the jobs are getting the error mesg below – any suggestion welcomed.

    “Executed as user: SIVLTD\SQLSERVICE. SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.”

    thanks, amanda

  104. @111

    7th line in my code, by mistake I wrote Order_details, instead of Order_Detail table name. so query might need to be changes. If you figured out that fine, if not please change 7th line of script.

    Apologies if I gave you a hard time in figuring out….

    Thanks,
    Imran.

  105. @Amanda

    To enabble stored procs, triggers and Application to reference custom OLE Automation objects,
    you have to Enable OLE Automation if Application or T-SQL uses OLE Automation.

    TO do so, follow this step,

    Select Start,–> All programs –> Microsoft SQL Server 2005 –> configuration tools –>
    Surface Area Configuration for features

    The click on the instance you wnat to enable OLe Automation.
    Select OLE Automation, on the right pane, Check enable OLE Automation and Click OK.

    To this, you must be a memeber of sysadmin server role

  106. I need to create a diagnostic package using cursors using this logic
    1.Loop through and load clean data from source table destination table i.e while the @@fetch_status =0
    2.if the @@Fetch_status 0,add the current row to Error table
    3.Restart the cursor at the next record to avoid reloading the same records

    My brain is blowing out about this :( .I’ll probably add as the responses pour in
    Thank you

  107. Hi Dave,

    Is there a way to get details like login/UID/User Name etc of who created a table (not owner). By the way, I am using SQL 2005 server. Thanks

  108. Hello Imraan,
    @111

    Thanks for the solution. I tried the query. It does not return the top 3 qtys. It gives me some different result. I am not sure what change is needed – cannot think of any, that’s why I am here again. Please help!!!

    Thnx
    Tibya

  109. Hi,

    I am working with SQL server 2000 database,
    I was assigned unrestricted file growth in data file, but some how it does not growth.
    As a result it will stuck the database and giving me the time out expire error in my programs.

    How should I fixed this error

    Thanks

  110. Hi pinal
    i want to retrieve the last inserted value of an identity column how can i do that without using max/top clause plz help me out

  111. @Arvinder

    This could give you a start….

    create table example1 ( id int identity, ename varchar(100))
    go
    insert into example1 (ename) values (‘value1′)
    insert into example1 (ename) values (‘value2′)
    insert into example1 (ename) values (‘value3′)
    insert into example1 (ename) values (‘value4′)
    insert into example1 (ename) values (‘value5′)
    insert into example1 (ename) values (‘value6′)
    go
    select * from example1 where id = @@identity
    go
    drop table example1
    go

    Hope this helps,
    Imran.

  112. @Tibya

    I also wrote a patch for @111 comment in @115 comment.

    Please read @111 and make changes according discussed in @115 comment.

    That should help, if not please let me know I can try something for you again.

    Thanks,
    Imran.

  113. This is in SQL 2000. I need to select top (some) records from a view into a variable. I use:

    declare @lastzip int
    –@lastzip value obtained from another ‘select’ stmt

    select @zip = top (@lastzip) zipcode + ‘,’ From vwSalesTeam v
    where v.userid = @userid order by zipcode asc

    I get an error
    Msg 170, Level 15, State 1, Procedure usp_search_ver5, Line 386
    Line 386: Incorrect syntax near ‘=’.

    I was working in SQL 2005. I would have used the above code in SQL 2005. But now I’m using 2000 and I’m getting this error.

    Could you tell me what I’m doing wrong here???

    Any ideas/Suggestions are highly appreciated.

    Thanks
    Vijeya Shobana

  114. @Vijeya,

    Correct me if I am wrong.

    The above code will not work because of the following things,

    1. @zip is a variable and a variable can store only one value, but if you see passing more than one values into @zip, for example if your @lastzip value is 3, then you are trying to insert 3 values into a single variable.

    2. I think zipcode is a int datatype and you are performing a concatination action which is only possible with varchar datatype.

    I wrote something like this and I assumed some fake values, just to show you some output.

    create table vwsalesTeam ( zipcode int, userid int )
    go
    insert into vwsalesTeam values ( 11111, 1234)
    insert into vwsalesTeam values ( 22222, 3456)
    insert into vwsalesTeam values ( 22223, 3456)
    insert into vwsalesTeam values ( 22224, 3456)
    insert into vwsalesTeam values ( 22225, 3456)
    insert into vwsalesTeam values ( 33333, 2345)
    go
    create table #zip ( zipcode1 varchar(10))
    go
    declare @lastzip int
    declare @userid int
    set @lastzip = 3
    set @userid = 3456
    insert into #zip select top 3 convert( varchar(10), zipcode) + ‘,’ From vwsalesTeam v where v.userid = @userid order by zipcode asc
    go
    select * from #zip
    drop table #zip

    This works in SQL Server 2000, I did three changes, first I changed @zip to #zip ( i.e. create a temporary table) and second I changed zipcode to varchar datatype. Third I dont know what is wrong with “select @zip = top”, it never worked for me, so I changed it to, insert into #zip select top…….

    I know this is not the answer you are looking for, may be this would give you an idea where to start …..

    Hope this helps,
    Imran.

  115. Help me, please.

    I have any a problem. I don’t know, how do I do convert (downgrade) from SQL Server 2005 to SQL Server 2000.
    I tried with detach in SQL Server 2005 and sequentially attach in SQL Server 2000 data file (*.mdf).
    Then I tried with full backup in SQL Server 2005 and sequentially restore in SQL Server 2000.
    And NOTHING :(

    I know.
    May I create new database, then turn off relationships, constraints and … , then copy dates between old and new database, then turn on relationships, constraints and …, but
    I seek anything intelligent working out.

    Help me, PLEASE.

    PS: I’m sorry, in my English. :((((

  116. Hi,

    LOG shipping between a 2000 and 2005 console but the databses are in compactible mode 8

    I would like to do a logshipping from a 2000 enterprise console on a server to a 2005 management studio console on another server . The databse that I want to do log shipping on is a 2000 sql databse on both the server. I have not upgraded the databse to 2005 .
    My problem is I can’t connect the 2005 destination server on to my 2000 management studio (naturally) so I tried to open my 2000 databse on the 2005 management studio in the other server but I can’t find any log shipping option .
    Would you be able to help me out .
    Thank you so much ,
    Anitha

  117. Looking for advice on purchasing a new server for SQL databases and I’m at a complete loss as to what I’m looking for.

    I have a Dell 1850, which has worked well for years and works pretty good still. I have 16 gigs of ram, and pleanty of hard drive space.

    Issue is that I have a program that runs up against my SQL database and when I try to run the program multiple times at once, I get i/o errors. I can run 3,000 queries per minute, (program does that) but I need to get to 60,000 queries per minute. They are very small queries, primarily append and updates, but none the less, they run constantly.

    Knowing that I need to get the speed on the queries, without getting i/o errors, I’m at a loss for what features I should be looking for in a new server.

    Obviously raid 10 capabilities, but not sure if I should be looking for a disk array, or a completely new quad core server with more ram.

    Any input would be greatly appreciated.

  118. Hi Pinal,
    in a replicated database, want to insert values.In a table, Identity specification is set to yes for a field n so can’t able to insert values.
    Tried this,
    First identity is set to on,inserted values again set to off..It works great for individual database but not for replicated database..
    how to overcome this problem..

    Thanks,
    Jeevika

  119. Hello sir,

    I am a beginner for SQL server , and i want to learn the SQL commands, stored procedures and all such related stuffs, so can you guide me on how should i start.

    Thanks,
    Abhishek

  120. @ Eric,

    In SQL Server 2000 and 2005 you can create logins using scripts,

    This is a sample script.

    create login login1 with password = ‘H512kIHn’

    by default check_policy option will be on and Check_expiration policy will be off, what this means is,

    all windows server 2003 and later version has security policy which can be applied to SQL Server logins,
    Check_policy will ensure that your passwords,

    1. meet password complexity,( any 3 of below 4.)
    a) atleast 8 characters, ( what ever you have set at OS level 8 or 6)
    b) atleast one character
    c) atleast one digit (1-9)
    d) atleast one special character

    Any 3 out of 4.

    2. Account lock out policy : what ever limit you have set at OS level, after that many failure attempts windows account will locked, this logic is also applied to SQL Server logins,

    3. Lock out expiration policy. same as OS level.

    But for Check_expiration which is by default off, you need to mention explicitly if you want turn it ON, this policy keeps record of date when login was created and when user has to change password next time… , same concept as in OS ( Server 2003)

    script1: (SQL Server Logins)
    create login example1 with password = ‘H512kIHn’

    Check_policy ON, Check_exipration OFF ( by default)

    script2:(SQL Server Logins)
    create login example2 with password = ‘H512kIHn’, check_policy = on, check_expiration = on

    Check_policy ON, Check_expiration ON , you cannot keep Check_expiration ON, untill you have Check_policy ON.

    script3: ( Windows Logins in SQL Server)
    create login [domain\example] from windows

    This is when you want to create a windows login. you have to use square brackets in order to create a windows logins, here you dont provide any information for check_policy or check_expiration because all this will be taken care by active directory and OS.

    We also have something called, must change option, ( for SQL Server Logins) when users connect to SQL Serverfirst time, they are prompted to change their password ( on first login) , this is similar to user must change their password option in windows.
    Check_Policy and check_expiration both should be ON, in order to keep MUST_Change option, this can easily be done by using GUI.

    Check_policy and Check_password works with Windows Server 2003 and later versions.

    Books online is best source.

    Hope this helps,
    Imran.

  121. Hi pinal ,

    Am having some doubts ,

    Can we call stored procedures within a function?

    Plz give me some help notes for stored procedure wiithin stored procedure…

    thanks

  122. hello,

    could you please tell me which is the better option to use
    ‘index rebuild’ or ‘index defrag’ at production environment

  123. dear pinal,

    i am working on a finacial project in which we r using BI (SSAS) enviroment.we don’t have any knowledge how to create cubes and mining structure
    will u help me to how we can create a cube in ssas

    or will u provide some refrence to help us …

    dileep singhal

  124. Hi Pinal

    I believe you might have answered a similar question in the past, if so could you please forward the same, or if have not answered please reply back.
    I have two SQL SERVER databases ,one in 2000 and another in 2005 on two different serveres
    And the need for me is to contantly access data across the two versions and also preform DML operations between the two databases.
    What is the best way for me access the databases across different versions. In the past I have been using linked servers, I would like to know if I could a similar approach, and how do we create a linked server across different versions?

    Thanks
    Bala

  125. I am curious if you are interested in working on a CFMX7 / SQL05 project as a development consultant.

    Please contact me and let me know if you have interest.

  126. Sir

    Please solve my problem. I want to create storeprocedure in which i want to use another database. How is it possible.
    Please give me reply.

    Thanks in advance.

  127. Dear Pinal, thank you for giving this chance, first of all.
    My problem which I should resolve it urgently, is about decimal symbol of tables in my databases. Now, the decimal part of the numbers is separated by “.” But I need to change it as “,” Because it is not compatible for my other programs which I use sql tables by linking..
    If you would give me some clue about how to make this change, I will appreciate..
    Thank you in advance..

  128. Hi Dave,

    I have currency fields stored as text data in an access database. where the last position of the field is in zoned decimal format. (f.i. J = -1, K = -2 ) .

    How do I extract numeric values with two decimal places from the access database ?

    Thanks for your advice.

  129. hi pinal,

    i have 2 simple questions…

    1. can a foregin key in any table can be a foregin key in any other table…i think the question is clear…i will explain in other words…..Primary key of table A, which is foregin key in table B. can it be a foregin key of table C ? here there is no relation between table A and table C.

    2. In junction table, the primary key is made up of composite key, can any one the composite key be a foreign key in any other table….

    thanks for your website…..its really very helpful…….

  130. Hi Pinal,

    I have a request to move the mdf/ldf from one drive to another using xcopy. Can you help me in using this in SQL Server script or tell me any other command to move the files? This is kinda urgent. Any support will be much appreciated!

    Thanks,
    Sagar

  131. Hi,
    I want a help in sql query. i am developing a jobsite portal so i want to put search for jobs in jobseeker panel. my problem is that..
    i made city master for job posting.
    when a recruiter post a job then i store job cities in another table with cityid and jobid.

    i want that when an jobseeker search like jobs in jaipur
    then all the jobs which are available in city jaipur comes in search results.

    so plz help me in written query..
    its urgent..

  132. Hey Pinal,

    Just to let you know that there is a mispelled word in your resume(Webpage resume not the pdf version)

    Under Software Outsourcing Manager (3rd last point)

    interption should be interruption.

    cheers!

  133. Hi Pinal,

    I just started working as a Junior DBA for 2 years. I want to know what kind of inteview questions they will ask in a interview because I’m changing company. Are the questions mid level or senior level dba questions?

    Thanks

    Ben

  134. Hi Pinal,

    I have been assigned task to find what service accounts are being used on all SQL servers . This would be for every SQL Server service that is running (SQL Server, Agent, Notification Services, etc.).

    It would be a lot of work and I’m not sure there is anything that could be done to automate it so that we can pull that information without manually checking each server instance. Any thoughts? Please assist if there is any automated script to fetch account info. for all SQL servers.

    With best regards,
    Vivek

  135. Hi Pinal Dave,

    Thanks for all your great input. I am new to SQL and data importing and was wondering if you had any feedback for me on this issue.

    I need to import data from .csv files into a SQL database. (SQL 2000)
    A script was provided to me that set up all the tables and set the column names in the SQL DB. The .csv files that I need to import have the identical names as the table names in SQL and the column names in the .csv relate to the column names in the SQL tables. That said, I was hoping to use the steps outlined in

    http://www.databasejournal.com/features/mssql/article.php/10894_3325731_1

    However in my project each .csv has different column names to each other, but they do relate directly to the column names in the SQL DB (as elaborated above) – How would I go about importing this data, or is there a better method for me to implement to achieve the result?
    Thanks in advance,

    Jonathan

  136. hi Pinal ,

    i have one query regarding index, for example i have one table on which have 1 cluster index and 1 non-cluster index , cluster index is based on the id column and non cluster index based on id column and location code column , when i will fire select query and in the where clause of this query both id and location code column include , so in that case which type of index will be used to return the result set .

  137. I have created a function in sql which accepts a string removes all the special characters and returns string without special characters….i have used while loop in it….Normally when i run the code it gives me right results but when i call the function it returns the first character of the string passed…Issue here is why the while loop doesn’t work when i call the function. I have used the word continue also in it….

  138. hi! Pinal,

    I am looking for a sql backup solution.

    I have a 30gb db in 2005 sql ent version. Currently it was configured db mirroring and log shipping.

    I need to backup the db regularily as I think if the db having problem..like coruption. This info will replicated to the mirror server as well as to the log shipping server. This is the reason why I need to back.

    But when I try to setup a backup job on principal server from maintenance plan and I cannot select the actual db. I think this is due to the db was in mirroring configuration. Backup plan not available in mirror server as well…

    your reply is greatly appreciate

    thanks….Metthew

  139. Some examples re item 149

    The first csv file which is named abntemp.csv has the following:
    FNMASTER_CODE,ABN_NUMBER
    102276,22002072104,
    102863,89008921926,
    etc.

    The table in SQL that needs to be populated with the data from abntemp.csv is named dbo.abntemp and has the following columns defined:
    fnmaster_code abn_number

    The second csv file which is named algra.csv has the following:
    ALLIG_CODE,COAUTH_CODE,COUPDATE_DATE,COUPDATE_TIME,COUPDATE_USER,COUPDATE_TERM,ALGRA_RSN_ENDED,COEND_DATE_BY,COEND_DATE_TRMNL
    ESS,AD,28/04/2000,12:01:13,gems_live,WCCNTS5,,,,
    ESS,PRA,25/01/2000,14:07:09,gems_live,WCCNTS5,,,,
    CLT,PRA,19/08/1999,13:49:54,gems_live,WCCNTS5,,,,
    FPR,PRA,19/08/1999,13:50:16,gems_live,WCCNTS5,,,,
    etc.
    the table in SQL that needs to be populated with the data from algra.csv is named dbo.algra and has the following columns defined:
    allig_code coauth_code coupdate_date coupdate_time coupdate_user coupdate_term algra_rsn_ended coend_date_by coend_date_trmnl

    thanks, Jonathan

  140. Hi Pinal,

    Thanks for SQL Server 2008 Intv Q & A.

    I need a help in SQL Server 2005.

    I am trying to create new maintenance plan. But when I right clicking on Maintenance Plan folder (SQL Server Management Studio -> Management ->Maintenance Plan), no option of creating a new maintenance plan from scratch or creating one via the wizard.

    Actually I am very novice in SQL Server 2005. Please help me.

    Thanks & Regards

    Koushik

  141. Koushik,

    If I am not wrong, you are trying to create maintenance plan in SQL Server 2000 using SQL Server 2005 client tools, which is not possible.

    If you are using SQL Server 2005 client tools and you are connected to SQL Server 2000 then there is no way that I know to create new maintenance plans.

    The only way to create new maintenance plan in SQL Server 2000 is using SQL Server 2000 client tools ( Enterprise manager).

    Please correct me if I am wrong.

    Thanks,
    Imran.

  142. hi pinal,
    i am raghuram, working as dba in pune. i am reading all of your interview questions and answers. it is helping me in learning sql in detail. like that do u have any kind question bank for MCDBA examination. i want to write that exam.can you give me any tips for that exam preparation.
    expecting reply soon

    with regards
    raghuram

  143. According to Imran Mohammed, Imran you are wrong, because when execute
    “Select @@Version” in my query window , result is

    ” Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ”

    And other details are

    Microsoft SQL Server Management Studio Express-> 9.00.3042.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-> 6.0.2900.2180
    Microsoft .NET Framework-> 2.0.50727.42
    Operating System->
    5.1.2600

    When I am right click on Maintenance Plan,only two menu are appear.one is “Report” and other is “Refresh”.

  144. Hi Koushik,

    I have not tried it myself but may be your management studio is of express and your SQL is developer (Acts like enterprise) that could be the reason. Express edition management studio can have limitation.

    Regards,
    Pinal

  145. hi pinal

    how r u . I hope ur fine with busy work with enjoyment .
    i ve query that ”

    i want to insert a complete row between the rows . i mean to say that

    Empid Empname
    ———- —————–
    1. 111 pinal
    2. 112 imran
    3. 113 Raju
    4. 115 Ravi

    now i want to insert a row between(113 and 115) the row . output sholud be like this

    Empid Empname
    ———- —————–
    1. 111 pinal
    2. 112 imran
    3. 113 Raju
    4. 114 David
    5. 115 Ravi

    can it be possible.
    Plz give me response .

    ————” Drive into d ocean of SQL SERVER “————-

  146. Not to worry, have a solution:

    use GEMS
    GO

    DECLARE tnames_cursor CURSOR

    FOR
    select TABLE_NAME
    from INFORMATION_SCHEMA.TABLES
    OPEN tnames_cursor
    DECLARE @tablename sysname

    FETCH NEXT FROM tnames_cursor INTO @tablename
    WHILE (@@FETCH_STATUS -1)
    BEGIN
    IF (@@FETCH_STATUS -2)
    BEGIN
    PRINT @tablename
    EXEC(‘BULK INSERT ‘ + @tablename +
    ‘ FROM ”D:\GEMS_IMPORT_FILES\’ + @tablename + ‘.csv”’ +
    ‘ WITH (fieldterminator = ”,”, rowterminator = ”\n”, FIRSTROW = 2)’)

    END
    FETCH NEXT FROM tnames_cursor INTO @tablename
    END
    CLOSE tnames_cursor
    DEALLOCATE tnames_cursor

  147. my table
    ————————————————————————
    ID | tpic1 | tpic2 | ……. | tpic20 | s_pic |
    ————————————————————————
    1 | sun.jpg | gun.jpg | ……. | win.jpg | 2 |
    ————————————————————————
    2 | rat.jpg | fun.jpg | ……. | run.jpg | 1 |
    ————————————————————————
    3 | see.jpg | row.jpg | ……. | hill.jpg | 3 |
    ————————————————————————
    4 | ice.jpg | tea.jpg | ……. | bill.jpg | 20 |
    ————————————————————————
    . | ……….. | ………….. | ……..| …………………|…………|
    . | ……….. | ………….. | ……..| …………………|…………|
    . | ……….. | ………….. | ……..| …………………|…………|
    ————————————————————————
    n |rose.jpg | leaf.jpg | ……. | root.jpg | 18 |
    ————————————————————————

    i need query for like this selection. pls help me
    ————————————————————————
    ID | s_pic | main_pic
    ————————————————————————
    1 | 2 | gun.jpg — ( tpic2)
    ————————————————————————
    2 | 1 | rat.jpg–( tpic1)
    ————————————————————————
    3 | 3 | sss.jpg–(tpic3)
    ————————————————————————
    …. | ………. | ……………………
    …. | ………. | ……………………
    …. | ………. | ……………………
    etc

    what is the query for above one..

    Thanking you.
    D.vediyappan.

  148. Hi Pinal,

    I have written following stored procedure:

    **************************************************

    CREATE PROCEDURE sp_OpenItemBrkDwn
    AS
    BEGIN

    SET NOCOUNT ON;

    if exists (select * from dbo.sysobjects where id =
    object_id(N’[dbo].data1′) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
    drop table [dbo].[data1]

    if exists (select * from dbo.sysobjects where id =
    object_id(N’[dbo].data’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
    drop table [dbo].[data]

    declare @sql1 varchar(max)
    declare @sql2 varchar(max)
    declare @sql3 varchar(max)

    CREATE TABLE data (CLT_SITE nvarchar(50), CLT_CD nvarchar(50), TOTAL INT,
    IMG_STAT nvarchar(50))

    select @sql1 = ‘insert into data SELECT CLT_SITE,CLT_CD,COUNT(IMG_ST) AS TOTAL, ”P”
    as IMG_STAT ‘ + ‘FROM IMG_INFO WHERE IMG_DT < GETDATE() AND IMG_ST = ”P”’ +
    ‘ GROUP BY CLT_SITE, CLT_CD’
    select @sql2 = ‘insert into data SELECT CLT_SITE,CLT_CD,COUNT(IMG_ST),”R” AS IMG_STAT ‘ +
    ‘FROM IMG_INFO WHERE IMG_DT < GETDATE() AND IMG_ST = ”R”’ +
    ‘ GROUP BY CLT_SITE, CLT_CD’
    select @sql3 = ‘insert into data SELECT CLT_SITE,CLT_CD,COUNT(IMG_ST),”Y” AS IMG_STAT ‘ +
    ‘FROM IMG_INFO WHERE IMG_DT < GETDATE() AND QA = ”Y”’ +
    ‘ GROUP BY CLT_SITE, CLT_CD’

    exec (@sql1)
    exec (@sql2)
    exec (@sql3)

    ;with cte

    as
    (
    select *, row_number() over(partition by CLT_CD order by CLT_SITE,CLT_CD) rid from data
    )

    select

    isnull([CLT_SITE],”) as CLT_SITE
    ,isnull([CLT_CD],”) as CLT_CD
    ,isnull([P],”) as P
    ,isnull([R],”) as R
    ,isnull([Y],”) as Y
    into data1
    from
    (select CLT_SITE, CLT_CD,IMG_STAT, rid, TOTAL from cte) as data

    pivot
    (
    max(TOTAL)
    for IMG_STAT in
    ([P],[R],[Y])
    )as pvt

    select clt_site as ‘CLIENT SITE’,clt_cd AS ‘CLIENT CODE’,max(p) AS ‘PENDING’,
    max(r) AS ‘REVIEW’,max(y) AS ‘QA’, max(p) + max(r) + max(y) as ‘TotalWReview’,
    max(p) + max(y) as ‘TotalWOReview’
    from data1
    group by clt_site,clt_cd

    end

    ***********************************************

    When I execute it independantly from SQL Server 2005 it is giving me correct output in about 2 minutes but when I execute it from a VB.net 2005 form I am getting following error:

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

    I have given connection timeout = 0 in the connection string and also set sqlcommands command Timeout to 0.

    Please provide me a solution to the above problem.

    Regards,
    Pranjal

  149. Hi Pinal

    I need a clarification on indexing. Recently i got this question from my interview. I’m having two tables.

    One is #table and another one is @table.

    In both, which table can i set the index?

  150. I have to design a script which would go thru the DB and extract a flat file of each pertinent table’s data(1 file/table).I have used DTS to export data from databases.I have never written a script for extraction.Could you please guide me on this and help me in getting started on how to write a script with a small example.Could you also please help me on how to run the script against the database so that it could generate a data file

    Thanks

    Shveta

  151. Hi Pinal
    I have created strored procedure in SQL Server 2005.It would execute other stored procedure for it….
    It takes 4min to execute in SQL Express Management Studio.Is there any way in Managemet studio so that I can do step by step debugging….or reson why its taking so long …..
    Otherwise when i execute it from my frontend it would give me timeout session error…

    Thanks
    Regards
    Montu

  152. Pinal,

    I have two questions for you.

    I have one view Table having 1000 + records.

    1. Can you tell which is the proficient way which consumes less time to fetch all the records.

  153. I am continuing my 2nd question.

    In the same view table having 4 columns. I want to display the records based on two column restriction such that,
    Right know this view table is ordered by “Name” Column.
    Here is the sample example of my query output.

    id Name tickername
    5 Diya 2.rt
    4 Kiran
    3 Manu er.t
    2 Rahul
    1 sachin p1.st

    I want to display the records from the following priorities.

    priority 1: records should be displyed : order by NAME column [asc or desc]

    Priority 2: If the column “TICKER NAME” is blank that record should come at the bottom.

    Like

    id Name tickername
    5 Diya 2.rt
    3 Manu er.t
    1 sachin p1.st
    4 Kiran
    2 Rahul

    Can you help me out how can write the query to get this output?

    I appreciate ur reply.

    Regards
    Sachin K

  154. Hi Sir,

    I would like to ask your help regarding SQL queries:

    My current code:

    declare @W nvarchar(50)
    declare @S nvarchar(50)

    Set @W =’Week 27′
    Set @S =’SESTW056′

    – Weekly Reports Summary per day
    Select ServerName,DateName(weekday,[datetime]) as ‘Day’, count(*)as ‘No. of Unschedule Restart’ from LogServerrestart
    where weekcovered = @W and outageinterruption =’IISRESTART’ and servername =@S and [action]=’1′
    Group By ServerName,DateName(weekday,[datetime]),DateName(dayofyear,[datetime])
    order by ServerName,DateName(dayofyear,[datetime])

    —————-

    Select DateName(weekday,EventStartTime) as ‘Day’, count(*)as ‘Nagios Logs’ from Nagioslogs
    where weekcovered = @W and eventtype = ‘SERVICE CRITICAL’ and servername =@S
    Group By DateName(weekday,EventStartTime),DateName(dayofyear,EventStartTime)
    order by DateName(dayofyear,EventStartTime)
    —-

    My problem is how to combine this queries.. thanks in advance

  155. Hi Pinal,

    I am wondering if u can post some pre-defined stored procedures. I searched alot but didn’t find all the important one’s.

    Thanks,
    Rajesh.

  156. Dear sir,
    i have a table named table it has two fields empno is a primary key it is automatically generated starting from 1, 2,3 etc.and also a empname fields. if there is10 records with empno starting from 1 to 10 there. i have deleted the 5 th record. now i want to insert exactly at the fifth position .what i have to do this is interview question asked me pleas tell me the answer
    second question if there is error in a trigger how u can correct it , i said alter trigger but interviewer it is not the correct answer
    please send me the answers
    yours faithfull
    ragesh

    • SET IDENTITY_INSERT ON
      insert into table(empno , empname )
      select 5, ‘fifth_emp_name’
      SET IDENTITY_INSERT OFF

      Please note that, empno should be IDENTITY column

  157. Hi Pinal,
    Following is my scenario,

    Suppose Table 1 and Table 2 has same column e.g. Column1

    Following is the query,

    1. Select column1,column2 From Table1

    2. Select column1 From Table2

    I want to find common records from these tables, but i don’t want to use Join clause bcoz for that i need to specify the column name for Join condition,

    Will you help me to get common records without using Join condition.

    I am using SQL Server 2005

    Thanx in advance
    Sachin.

    Unlimited freedom, unlimited storage. Get it now

  158. Hi Pinal

    I have a question regarding finding data difference between two identical tables.

    I have two identical tables. One table(Table1) contains redundant data , another one contains exact data(Table 2). I need to select only rows from Table1 which is not present in Table 2 and load the selected data in Table 2. How can this be acheived in sqlserver 2005. I want to write a Stored Procedure for this.

    Thanks in Advance.
    Debarati

  159. Hi Dave sir,
    With due respect I want to state that your answer to each questions are satisfactory.Sir i need an ebook by u so that i can become as smart as u

  160. @Sachin,

    If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.

    select column1 from table1
    intersect
    select column1 from table2

    if you want in the output both column1 and column2 from table1 which has common columns1 in both tables.

    select column1, column2 from table1 where column1 in
    (
    select column1 from table1
    intersect
    select column1 from table2
    )

    To do this, make sure your column1 is unique and do not have duplicate records.

  161. Hello Mr. Pinal,

    I have a small query in Sql Server 2005 Express edition that is, i have a database back up named gharabhada.bak which was created in a server named ESELSRV and now am trying to restore in my system named HOME but i was unable to as it is giving error as i can restore in the server named ESELSRV only. So how can i create a SERVER WITH ESLESRV name as most of my .Net projects i have created in that server only. So, culd you help in creating a server. If possible please mail me the solution.

  162. Hi,

    Iam using SQL Server 2005

    SELECT
    Col1,
    col2,
    ROW_NUMBER OVER(PARTION BY Col3 ORDER BY Col2) AS RowNumber
    GROUP BY Col1, Col2
    ORDER BY RowNumber

    Iam getting the exception

    Column “RowNumber” is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

    Help me to solve this.

  163. Hello Mr. Pinal,

    I was hoping you could answer a question for me. In SQL2005, can you lock only certain records within a sql server table?

    Thank You for your time,
    ~Jessica

  164. Hello Pinal,

    I saw this entry in your older thread and we have the same issue at a client site. Can you shed some light on it and how to avoid or rectify it if possible.

    Thanks
    Steve

    ————————–

    We are getting errors in our production database when we restarting the sql services

    Could not find database ID 2, name ‘tempdb’. The database may be offline. Wait a few minutes and try again.

    can you please tell me why this occuring
    ——————–

  165. Hello

    I am facing one problem while bulk inserting the data.

    my text file looks like this.

    START-OF-FILE
    DATEFORMAT=20081003
    DATARECORDS=93
    FIELDSEPARATOR=TAB
    id|name
    START-OF-DATA
    1 AGB
    2 ANB
    3 ARHUS
    4 AITOV

    …….

    END-OF-DATA
    DATARECORDS=93
    END-OF-FILE

    FIELDTERMINATOR = ”\t”,ROWTERMINATOR = ”\n”, KEEPNULLS, FIRSTROW = 2 ,

    I am using bulk insert method to upload the data by specifying . It is working fine but my first record is always getting skipped.Its loading data from 2nd row (2 ANB). Can you pls suggest why this is happening.

    Thanks Debarati

  166. Dave,

    How do you feel about installing SQL Server 2005 and 2008 on Virtual Servers; both Microsoft’s and third parties.

    Thanks,

  167. Hi Pinal,
    I am Nagashree. I want to do certifiaction on SQL server. I need complete info regarding this..
    please guide me… (I mean how many exams, can I learn without going to learning center?)..etc etc..
    I am waiting for your reply…

  168. Sorry about this.
    I fear my spelling errors might make it difficult to understand the problem.
    Corrected version below.
    —————————————————————————-

    Dear Pinal,

    I am trying to assess the best method for copying a client record and all its related dependencies from a database on one server to an identical database on another server using SQL 2005.

    The database consists of about 12 tables.

    A client record in the table at the top of the hierarchy has numerous related records throughout the remaining tables.

    I wanted to use a stored procedure to be able to copy a client and all of its related data from all tables on the one server to an identical set of tables on another server.

    All constraints and referential integrity must be maintained.

    I am new to SQL and simply wanted pointing in the right direction.

    Thanks,
    Dawn

  169. @Dawn,

    What I understood from the information you provided is,
    you have a database with 12 tables, which has client information and all the tables are interlinked with each other. Table also have some dependencies ( triggers, views, other tables).

    And you also have one more sql server 2005 to which you want to copy this data with all dependencies,

    The solution might with var with your requirements.

    1. Database Mirroring : Copy database to destination server but you cannot access database on destination as it will be in recovery mode.

    2. LogShipping : copy database to destination server, but you can only read data from destination database as it can be in recovery or read only mode.

    With the above two process you can only send data to destination database, you cannot recieve data from destination to source.

    But with replication ( Transactional replication with updatable subscription ) you can have data flow in both direction, but there are some restrictions.

    1. Identity columns
    2. Table with out primary keys.
    3. No replication of stored procedures.

    And few more. If you could tell us more, how you want your destination database to be. that would give us more idea…

    Thanks,
    Imran.

  170. Imran,

    Thanks for your response.

    The database does have about twelve tables with information about individual clients spread over the various tables and linked via primary-foreign key relationships.

    As an example the dbo.Client table contains two columns, ClientID (PK) and ClientName. This is more or less the top-level table.

    The dbo.ClientSchemes Table contains four columns including ClientID (FK) and SchemeID (PK).

    The dbo.ClientSchemeExercises Table contains nine columns including ClientID (FK), SchemeID (FK) and ExerciseID (PK).

    What I want to do is this;

    Given a particular ClientID I want to be able to obtain all records in all tables (maintaining referential integrity) that pertain to the particular ClientID and copy them into the table with the same name on a destination server which contains an identical database.

    In essence a copy of all the data for the specific ClientID will be created on the destination server.

    I had tried to do some research on replication. However it seemed to be more relevant to the case of copying an entire database from one location to another as opposed to copying a few related records into individual tables on the destination database.

    Thanks,
    Dawn

  171. I recently began working with SQL Server and have a little experience and training with 2005. Now, I have a few 2000 databases to maintain, until I get rid of them. I have one quick question:

    In 2005 I am able to clear history as part of a maintenance plan. How do I clear the history in 2000?

    Thanks,
    Donna

  172. Hi Pinal,
    I want to set timeout.

    i am having a SQL Server 2005 stored proc in which i am using OpenQuery to insert data into sql 2005 table from sybase table. I am using Linked Server.

    I am calling this stored proc from SSIS using Execute SQL Task.

    sometimes the process hangs.

    I want to error out (timeout) the process if the SP (or openquery) takes more than a given time.

    how to do?

    Thanks a lot in advance.

  173. add the following statement immediately after a query. this will give you total records affected by the query.

    print ‘Total records returned ‘ + convert(varchar(100),@@rowcount)

  174. Hi Pinal

    first of all ……Thanks for creating very supporting website for SQL guys. somemany times I had gone through your website ………its really helpful ………..

    could I get details about included option in index in SQL Server 2005 and CTE (say new features of SQL 2005) and would like to know more depth about DBCC UPDATE STATISTICS

    could I get your office address……..bcoz i m also from Ahmedabad….

  175. Hi Dave,

    Thanks for your tips and tricks with regards to sql server. really enlighting.

    I have a question though. Its about when restoring a back up database to a sql server 2005.

    Do you know what are the scripts being executed after a database has been restored? I placed a database trigger on my database so that if anyone who alters the same sp without any changes it will throw an exception. the problem is that when i restore a database, somehow a script is being executed and an exception is being thrown. Do you know what that script is?

    Thanks Dave, more power!

  176. @ Dawn.

    Apologies for late reply.

    Thanks for describing the situation, When you decided to copying the database. I would suggest you try one of the following. Dont go with Replication, Its good when you are doing specific tables. but in your case you want all database to copy at destination, so try one of the following.

    1. Logshipping: Easy to set up, less maintenance. Works very smooth, Data latency ( data gets updated every 15 minutes, depends how much time interval you specify when configuring Logshipping ).

    Here is a pictorial step by step procedure, how to configure log Shippping

    2. Database Mirroring: Little bit tough concept. Easy to configure , little bit tough to solve errors, difficult to manage.

    Latency will be less than 3 seconds

    So I would suggest you to go with Log Shipping. As it is hassle free.

    But you did mention that you will insert this data into a table. well if you are doing Either logShipping or Database Mirroring, you CANNOT perform write operation on Destination database. What you can do, you can create one more database and then copy data from destination database into your main database.

    Or you can also do this,

    write a stored procedure on source server inserts data to destination server after joining s all the tables on which you have build primary keys and foriegn keys according to your business needs , place it in a job and schedule that job to run every 5 minutes , Before you create thsi stored procedure you have to create a linked server between source and destination servers.

    Also in stored procedure you have to use four partname,

    servername.databasename.ownername.objectname

    There are many ways to perform this task. depends which one you feel is easy for you.

    Thanks.
    Imran

  177. Hi Pinal,

    I have been following you blog for quite some time now and appreciate our work towards SQL Server Community.

    I am looking for answers for three inter related questions

    1) what is the advantage of SQL Server 2005 over SQL Server 2000? ( in respect of migrating server from 2000 to 2005)

    2) now as SQl Server 2008 is about to be released.. why one should move its database from sql server 2005 to sql server 2008. i mean advantages SQL server 2008 database will have.

    3) SQL Server 2005 was considerable swift from sql server 2000. SO which migration will worth more a) 2000 to 2005 or
    b) 2005 to 2008

    thanks
    Rahul Kumar
    PS: mail ur reply to my email also.

  178. Hi,Pinal i want to Backup my database with security and while Restoring in the Client place the database must restore only and i must deny the permissions for accessing the database…?
    How is it possible pls reply as soon as poss…?

  179. Hello Subu,

    You question is not clear, I just assuming your question to be ” After you take a backup of any database, you want to restore a database but you dont want any one to access this database”

    Yes This can be done, this is very easy. we call this mode of the database as single user only.

    First you restore this database, follow the normal procedure and once you are done, then do the following.

    IN SQL SERVER 2005:

    1. Right click database- click properties- click options from left side of the dialog box – scroll down right side of the box to the last point, under state subcategory, you will see an option :
    Restrict User: whose value will be set to MULTI_USER ( By default) , click on MULTI_USER and select SINGLE_USER from the drop down list, click OK. On Message Box, click YES.

    Now in this state only one user can connect to this database, so when you are connected to this database no one else can connect to this database as this is in single user mode.

    IN SQL SERVER 2000:

    1. Right click database- click properties- click options from top tabs of the dialog box -Under Access session you will see Restrict User: check this option and then check Single – user , click ok.

    By this method you can restrict users to connect to this database. but one user can connect to this database. No matter if other users have more privileges than you ( SA), SQL Server will not allow other users to use untill you come out of this database.

    If you want your database to be in multi_user mode how it was previously, follow the same process as above,

    IN SQL SERVER 2005.
    1. This time instead of SINGLE_USER select MULTI_USER.

    IN SQL SERVER 2000.
    1. Uncheck Restrict Access.

    This would be a temporary solution to your problem, if you dont want any user to access this, remove gues account ( In SQL Server 2000) or Disable Guest Account ( In SQL Server 2005) and drop all users.

    By doing this only dbo;s ( Logins with SA privileges) will have access to this database.

    If you dont wany any user to connect to this database, not even dbo’s not even SA’s, not even a single user mode, then the best solution is take that database offline.

    to take database offline, do the following.
    IN SQL SERVER 2000.
    1. Right click database – All Task- Take offline.

    IN SQL SERVER 2005.
    1. Right Click database- Task- Take offline.

    Note: Dont stop the process when the database is going offline, if you stop the process in the middle, you will loose your database for ever, be careful.

    Hope this helps,
    Imran.

  180. Hi Pinal,
    Please provide me some good links so that i can read the execution plans effectively which will help me a lot in solving performance related issues.

    Balaji.J

  181. Microsoft OLE DB Provider for ODBC Drivers error ‘80004005′

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    This keeps coming up when I try to host my ASP pages on Godaddy.com I used dreamweaver to create them and I uploaded the db to the host . Can you please help me

  182. I am very much impressed with your blog. I have small requirement in query.

    In one of my table schema like ApprovalID,RFA Number Approved Status

    Data lilke
    Apporval ID ,RFA Number, Status
    124 100 4
    125 100 3
    126 100 2

    Status is numeric field while displaying the content of Status in numeric values I want to display related status names like Approver for 4 , Rejected for 3 like this. I don’t have any table to represent the status names for those values.

    how can i do this

  183. Hi Pinal

    Actually would like to more about in sQL SERVER 2005, in index say cluser index Included column ….

    more depth about included column…

  184. Hello,

    Can we get the table names in which records just inserted/updated/deleted?

    For example, the employee information is added in two tables (PersonalInformationTable and EmployerHistoryTable).

    Is there any way to find out the tables’ names in which data is inserted in last 10 minutes?

    Bye,

  185. Pingback: SQL SERVER - Get Common Records From Two Tables Without Using Join Journey to SQL Authority with Pinal Dave

  186. Dear sir,

    I am in the middle of restoring… state of my database (which I initiated through one application).,….now it does not terminate…after waiting for ages! :(
    I tried restarting server and all…but still is shows the same status….help me get out of this ASAP.

    I am using SQL Express 2005.

    thanks
    Jeet

  187. Hi pinal,

    I need some help in Updating the tablw which has both primary key and Unique key constraint on diffrent coulmns.
    In the source we have records with diffrent value for primary and same value for uniqu key when we try to update the target using these records using primary key it is violating unique key constraints.
    how to update the target in this scenario.

  188. hi pinal
    i want to know how can i use like statements with dates ….i want to search stock between two dates and i have to use like nothing else and i am using sql server 2000 ..plz help me its urgent…….
    i am very thankful to u…plz make me help…….
    i am using (RecievingDate between like ‘%10/14/2008%’ and like ‘%11/14/2008%’). but it doesnt work…..again i m very thankful to u….

    nitin

  189. @Ram kumar,

    You can use case function to display the same. I am writing script for the example which you discussed.

    CREATE TABLE EXAMPLE1 ( [APPORVAL ID] INT ,[RFA NUMBER] INT, STATUS NUMERIC )
    GO
    INSERT INTO EXAMPLE1 VALUES ( 124 ,100, 4)
    INSERT INTO EXAMPLE1 VALUES ( 125 ,100, 3)
    INSERT INTO EXAMPLE1 VALUES (126 ,100, 2)
    GO
    SELECT [APPORVAL ID],
    [RFA NUMBER],
    [STATUS],
    CASE WHEN STATUS = 4 THEN ‘APPROVED’
    WHEN STATUS = 3 THEN ‘REJECTED’
    WHEN STATUS = 2 THEN ‘PENDING’
    END AS STATUS_NAME
    FROM EXAMPLE1
    GO
    DROP TABLE EXAMPLE1

    Thanks,
    Imran.

  190. Hi Pinal,
    Here is my question. I have one table A with 52 columns and based on the other table B, I need to populate table1 and table 2 with table A data.
    Table A:
    client_id
    Table B:
    client_id, client_type
    where Table A.client_id = Table B.client_id then get the client_type
    if client_type = 8 then populate Table 1 with Table A
    else populate Table 2

    I am new to the SQL server. Can you please reply me as soon as possible.
    Thank you in advance.

  191. Hi Dave

    I want to export my query from sql directly to excel (Excel SpreadSheet). i can do it with .Net (Export content of a datagridview to excel OR export a generic list with reflection and microsoft.office.intropt.excel) but it doesn t have good performance with many records.

    So Thanks Inadvance.

  192. Dear sir,
    I want to create a stored procedure which will insert one record into master table and multiple records into other table.Master tables insert statement i want to call once and other table insert statement i want to insert multiple times.

    Thank you.

  193. Hi Pinal,

    I am trying to execute an SSIS package. This ssis package has only one step Execute Sql 2000 DTS task and it has a DTS in it. This DTS is working in SQL 2000 & 2005 both if executed as DTS. But when tried to execute the SSIS package I am getting the following error message:

    Error: 0×0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0×80040427): Execution was canceled by user.

    at DTS.PackageClass.Execute()

    at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

    Task failed: Execute DTS 2000 Package Task

    Warning: 0×80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package “Package.dtsx” finished: Failure.

    The program ‘[4836] Package.dtsx: DTS’ has exited with code 0 (0×0).

    Please help me.

    Thanks & Regards

    Sudha

  194. 1. whar are the configuration you do before and after installation of sql server service pack?

    2. what is the reason behind log file size is increasing even after configured Log shipping?

    3. what is .TUF file in sql server?

    4. If querey execution time increases …what will u do?system id idle..no blocking issues…what is the reason behind it??

    5.Diff b/w mirroring,Log shipping,Replication?

    6. clustering in sql server .
    7.How to monitor sql server performance without using GUI?
    8.where do we monitor log shipping??how to trouble shoot Log shipping?

  195. Dear Pinal,

    Sometime I see following error while processing any webpage.

    ***************[Error]*********************
    Microsoft OLE DB Provider for SQL Server error ’80004005′

    Transaction (Process ID 102) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    ***************[/Error]*********************

    I would really appreciate if you would be able to let me know, what should I be doing in order to fix the error.

    Thanks in advance.

    Su

  196. Dear sir

    Why is a complete snapshot being generated when a new article is added for Transcation Replication (SQL 2005) .

    i need query to prevent from this only run query it add it.

    This is my first post hope for good and quick replay

  197. Hi Pinal, I’ve an apllication that was installed with sql server 2005 trial version, and it expired, so i copy the data and log file i install the retail version and attach the data base using data file i backup the data base, and from another pc i restore the data base using that backup, but now, i’m not able to login to the the application, it says: “problem connecting to data base, please try logging in again”

    what should i have to do??

    Please help me :)

  198. I’m trying to do 2 things in sql server that i know can be done in Oracle:

    1. SQL Server 2008 won’t let me run a windowed function with a distinct clause. for example:

    select count(distinct customer) over(partition by store)
    from table

    I’ve read that SQL Server 2008 was supposed to have this feature added – I see that i can do Cubes and Rollups with count(distinct), but i still can’t run the query above.

    2. Is there a way to see how much time is left for a query to run as it’s running. In Oracle Enterprise Manager there was a window that counted down the time for the query to stop running and it was super accurate. Is there such a feature in SQL Server 2008?

    Thanks,
    Mike

  199. Hi Pinal,

    Is there any chance to create a script for each “UPDATE” process in each table?..

    Example:
    Case1:
    if new data saved in table “Suppliers” we need to create a script for that new entry…
    Case 2:
    If any editing occured in table “Suppliers” we need to generate script for that edited entry..

    Actually our purpose is to transfer the new/edited datas to our Branches which have the same DB structure.
    So if we can get only the New/edited data scripts from each table in source server; we can run these scripts in target server.

    thanx…

  200. hi

    iam new to this website.i want to learn sqlserver.2005 or 2008 which one is better.can any one give an idea how to start and where to start.plz help me
    thanks in advance

  201. Hai GUDMNG,

    Hai i am anil.Pls send interviews questions,tips & how face to interviews.pls send my mail as soon as possible..

    Thanks u
    u’s
    ANIL….

  202. Hai Latha,

    You should learn what is SQL SERVER…then you will know what are the diff b/w 2000,2005,2008….The functionality is same in every verion…but it is upgrading…thats it…ok

    All the best for ur future,,

  203. Im surprised that I cannot find an article about Change Data Capture (CDC) on your website. I think this is a great new function that has been added to SQL Server 2008. It would be great to get your insight into this and possibly learn more about all the different components behind CDC. Thanks!

    -Theresa

  204. Hi pinal

    How are u , i hope so fine , This is shaik. i ve query that consider there 2 tables A and B.

    A is having 10 records , b is having 5 records , now i want to append 10 records of table A to exhactly(means after all the records of table B) after the 5 records of table b , can it be possible , if it is plz show me . plz kindly forward a mail to my email id .

    Thankx alot
    SHAIK .

  205. Hi Pinal,

    I gained lot of knowledge on SQL Server by studying your articles.I am very thanks full to you.

    Can send me some FAQS on SSIS and SSRS please please..

    actually i have interview so can you do it please…………or any body can send me please…………….

  206. Hi dave

    I do have server problem when i make a dbcc checkdb allow data lose script. the log of the database was increasing currently 114gig it started with I think 5MB log, then it has
    server error message “could not continue scan with no lock due to data movement”
    server message 601.

    Now its been 3days running, Im running out of space..your help would very much appreciated..thanks

    Regards,
    Vincent

  207. Hi Pinal Wish U the same.

    I have one query. I want to find out which databases has been recently restored from which databases in SQL SERVER 2005.

    Thanks,

    rajesh

  208. I’m using the ‘Group By’ to group by month. The values I am selecting are ‘high’, ‘low’ and ‘close’. When I group, I want the highest ‘high’ (MAX), the lowest low (MIN), and the last close (date wise) in the month. Obviously I have the ‘high’ and ‘low’ figured out, MAX() and MIN() respectively, but I’m completely baffled by the ‘close’ value. Any help would be appreciated; I’ve spent too many hours on it already, with no solution coming to me.

    Thanks,
    Ken

  209. Hi,

    How do i check a record before updating.
    i want to check if field has a value in it, if not than go ahead do the update. If the value already exist than stop the update.

    I have one table CMR, and 5 columns with the following name.

    Cmrid
    Name
    Owner
    A.Date
    B.Date

  210. Hi Pinal,

    I’ve been dealing with this issue for a while!

    I have backup files (.bak) with postfix of year-month-day-hours at the end of files.
    e.i. backup_file_200810311230.bak

    The backup scripts have been generated by MP, that’s why they all have date at the end.

    I need to load my database in the target server with the latest dump file.

    This is a server to server loading…I’ve already automated a script to bring the backup files to my target server.

    In order to load my database in the target, I need to add some coding before the load SQL-script so it goes and grab the latest backup file.

    I’m very new to sql server and need more direction with the solution.

    I really appreciate your help!

    • Clue

      select top 1 backup_file from
      (
      select 'backup_file_200810311230.bak' as backup_file union all
      select 'backup_file_200811316230.bak' union all
      select 'backup_file_200801321230.bak' union all
      select 'backup_file_200810322430.bak'
      ) as t
      order by substring(backup_file,patindex('%[0-9]%',backup_file),patindex('%[.]%',backup_file)-1) desc

  211. Hello Pinal ,

    This is shaik . I written the query twice or thrice , till now there is no response from ur side , i dont know whether ur out of city or some other busy works .

    plz kindly could u refer my comment no 229 and answer me .

    waiting for ur response ……..

    Thanking u very much
    Shaik .

  212. Hi Pinal,

    I would like to know the performance impact of using temp tables in stored procedures. I believe temp table cause SP recompilation.

    I would like to why and when this causes the stored procudre to recompile? I would also like to know the improvement in SQL 2005.

    Thank you.

  213. Hi Dear Pinal,
    How are you?
    I want to congratulate on your all success in life.
    after success most people forget forever who helped them .
    when i saw your site i found u exaclty opposite of it. wants to congrats you on this.Please be this way only

    I m a new visitor to your site and very eagar to get a Job of sql dba currently working as a soft developer.

    i have no source where i can get a good knowlege of sql and was so scared if i can accomplish my goad of being dba

    ur site is as a personal trainer to me from today.

    i just wish hard that i reach my goal with your help. i have given myself this november and december month for this.

    hope that you wud help me get it

    lots of wishes
    swati.

  214. hello sir,
    my problem is that i have to make matrimonial website in which i need to store all the saved searches of user in single column using sqlserver 2005 and asp.net .
    plaese help me

  215. Hi Dear Pinal,

    How are you?

    I have a big problem with full text search of SQL Server 2005,

    We using CONTAINSTABLE with TOP_N_BY_RANK to get result with best performance.

    It’s return right data with high rank, but i need it return last record that added to DB (newest first , not by relevance)

    my DB has over 7,000,000 record, and we must using full text search and top n by rank,

    could you please help me.
    Thanks
    Vahedi

  216. Hi Pinal,
    Can we refresh a database (like we do by right clicking database node in object explorer and clicking on refresh) thru SQL Query?
    If yes, can you please tell me the query?

    Thanks,
    Ashish Agarwal

  217. Hi,

    I am here as a Database designer. I have to convert existing databse which is in MYSQl into SQL Server 2008.

    I am not getting any relevat info regarding that.

    The major challenge is converting data from mysql to ms sql server2008.

    Will you please help me?

    Thanks
    Shailesh

  218. Hi Pinal,

    I have a doubt. My company database is in MYSQL.
    Now we want to switch all data into SQL Server 2008.
    I am not getting any answer that how will I convert this?

    I can do this but that a long procedure (First create table exactly same as in MYSQL then by ssis or dts we can do this)

    CAn you help me or tell me any other method?

    Waiting for your reply.

    Thanks
    Shailesh

  219. Pingback: SQL SERVER - Refresh Database Using T-SQL Journey to SQL Authority with Pinal Dave

  220. Hi Pinal,

    I am new to databae programming.

    I gained lot of knowledge on SQL Server by studying your articles.I am very thankfull to you.

    I have a requirement to use SQL Jobs,i am not having any idea about it.

    can u please tell how to work with SQL Jobs in sqlserver 2005.

    Waiting for your reply.

    Thanks
    Pushpa.

  221. Hi Pinal,

    I have a Hp Proliant server with 8 processors(amd) and 120 GB ram with windows 2003 active passive 64 bit cluster .
    I Installed sql 2005 on it.I noticed an error message..
    The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.

    I read a few blogs and I would like to set the affinity mask and affinity I/O mask option using sp_configure..What would be the setting value for affinity mask and affinity I/O mask considering that i have 8 processors.Please suggest if there are any other options to eliminate the errors.

    Thanks a lot
    Purushotham

  222. Reply for the question 248

    Hi Pushpa,

    Jobs are available on Object Explorer. You have to expand SQL Server Agent, under which you will find Jobs. Roght click there and you will find new JOB , click on the new job and you will find New Job window.

    There are several pages available on left side.

    First on general page type the name and description.

    Second on step page you have to create a new step.

    Just go through different pages , its very easy to schedule a job from the earlier version of SQL server.

    Thanks
    Shailesh

  223. Hi Pinal,

    Is there a way to query the SQL Server to get the date time format – I would like to know whether the date format is mdy,ymd or dmy?

    Thanks,
    Kshitij.

  224. Hi Pinal Dave,

    i have a table ‘emp’ with fields empno,empname, sal and bonus.. my requirement is to add the sal and bonus for individual record..

    ex: empno empname salary(sal+bonus)

    how can i get it? plz help me

    with regards,
    pavan

  225. Hope you are doing good. You have done a wonderful job and all the articles in your blog are purely knowledge based.

    I want to send data in pdf files to database (SQL Server) directly. I am looking for code in C#.Net VS 2005.
    Please can you help me in getting the code.I tried searching in internet but i couldn’t find any.
    It will be helpful for me if you can also guide me in converting data in PDF files to Excel in C#.
    What are the plugins that are needed to be used for pdf files.

    Waiting for your reply.

  226. Hi Pinal Dave,

    I am facing some problem with data types defined for temporary table.

    e.g.

    (Original Table)
    Table name: tblTask
    Column name: TaskTitle Varchar(200)

    I have created one temporary table, which is used in a stored procedure.

    (Temporary Table)
    Table name: TmpTable
    Column name: TempTitle Varchar(200)

    Now problem is.

    I want to change Size of column “TaskTitle” (from Original table “tblTask”) to 400.

    I forgot to change the size of column “TempTitle” (From Temporary table “TmpTable”), and while executing the stored procedure it throws an error.

    So can we set the datatype of column “TaskTitle” (from Original table “tblTask”) to column “TempTitle” (From Temporary table “TmpTable”) at runtime?

    If above thing is possible, so we will just have to change size of column in original table and do not have to worry about temporary table, temporary table will take size of column from original table.

    Can you please suggest the solution or alternative for this problem?

    Best Regards,
    Jayraj Todkar

  227. Hi Pinal,

    This is a wonderful site for aspiring DBA’s.
    You’ve got almost all topics covered.
    I was looking for some article on SQL Data scrambling.
    But i could not find anything.
    Would be pleased if some article is published on Data Scrambling.

    Regards,

    Sarang.

  228. Hello Pinal,

    The site is really helpful to all the people , who works on SqlServer(as a DBA or programmer). Well I am working on MS.Net , and I am looking for a stored procedure or function that generates auto generate number. (Eg.) Suppose the first prefix always GRV Like GRV0001, the next number should GRV0002. I have made a function which is not generating number after GRV9999,the actual result should be GRV10000 and so on. But the function that i ve used generates GRV1000(after GRV9999). Please help me. Thanks in advance

  229. Hi Pinal

    I am looking for good training center in Gujarat/ india for SQL SERVER 2005-2008 ….could i get inforn regarding this

    Thanks
    JAY

  230. I am very new to SQL and would like help on the following.
    Below is the table I am using.

    person Table Structure
    person ID (primary key)
    person Name
    City Id (foreign key)
    State Id (foreign key)
    District Id (foreign key)

    City table structure
    City ID(primary key)
    City name

    State table structure
    State Id(primary key)
    State Name

    District Table structure
    District Id
    District Name

    I want to write a SQL statement where I can get the person name, name of the city, name of the state and name of the district.

    • select p.person_name, c.city_name, s.state_name, d.district_name
      from person p inner join city c on p.city_id = c.city_id inner join state s on p.state_id = s.state_id inner join district d on p.district_id = d.district_id

  231. Hi Pinal,

    I have a big problem with our Database Server. Database Server machine around 2 pm, memory paging / sec is too much high. After 3 / 4 hour later, memory paging/ sec again normal state. Last one month, each an every day in same process going on. All Application level transactions in a day ( 9 am to 7 pm ) are identical. No special activity done for this (2 pm to 6 pm) time.

    All other SQL Server / OS level Performance Counter are normal in a day, except Disk Queue Length.

    Disk Queue Length always high in a day.

    Can u give a suggestion / Solution to resolve this problem?

    We are using

    MS SQL Server 2000 Enterprise Edition with SP 4 (included major hot fixes).

    Windows Server 2003 Enterprise Edition with SP 2.

    Physical RAM 16GB, Allocating 12 GB for SQL Server.

    Virtual Memory = Physical RAM * 1.5

    Normal – memory paging/sec (6 pm to next day 2 pm) = less than 50 / sec

    High – memory paging/sec (2 pm to 6 pm) = more than 1400 / sec

    Thanks

    Koushik

  232. hi

    could you give the solution regardiing this error;
    eventid 12291
    source SQLISPackage

    or suggest any book for troubleshooting.

    thanks in advance

  233. Hi Pinal

    I need a script that can/will delete schemas in the various dbs
    (Security -> Schemas) as I have problems deleting user accounts using Great Plains front end

    Deleting the Security -> Schemas works as I can now delete the user account in Great Plains without any issues, the problem is that I have 250 such accounts to delete.

    I will appreciate it if you can email me a solution and or script

    Kind regards
    Colin

  234. another one…..(Great Plains V 9) can the default Account Format Setup be changed without having to do a re install

    Kind Regards
    Colin

  235. Hi Pinal,

    I am trying to get results wihout printing the column header using T-sql. Is there any command which we can use?
    As in osq there -h-1 or by unchecking option “Prints Column header(*)”.

    Many Thanks
    Aj

  236. HI PINAL,

    There is a very little Query i want to launch SQL Profiler from batch file.
    the command i use is
    @ECHO OFF
    profiler90 /Sserver /Uuser /Ppassword /Tx

    When ever i pass values & run it from batch file it launches the profiler successfully. But i need to open more than one servers in same profiler window is it possible to open multiple traces in single profiler window by the same command.

    Kunal

  237. Hi,
    Pinal, Imran

    I just love to read your blog.
    The information provide in this is really intresting & useful.
    I did so many of practices using your examples.

    Thanks.

    Regards,
    Neetu

  238. Hi,

    I’m new to Sql server, but task given to me is not a small one.

    My task is to check a table, whether it is inserted/deleted/updated. if any DML action performed i want to send mail to admin or particular person.

    I decided to do this through “Triggers”. i got a sample code from net. i’ve given the code below. when i run that code i received an error
    “Server: Msg 170, Level 15, State 1, Procedure CustomerUpdateMail, Line 48
    Line 48: Incorrect syntax near ‘@CustomerID’.”

    Code is :

    CREATE TRIGGER CustomerUpdateMail

    ON CustomerInfo

    FOR UPDATE

    AS

    declare @CustomerID varchar(10)

    declare @CustomerEMailID varchar(2000)

    declare @body varchar(2000)

    declare @CustomerName varchar(10)

    declare @CustomerNewName varchar(10)

    SELECT @CustomerID = Customer_id,

    @CustomerName = d.First_Name,
    @CustomerEMailID=d.Email_id

    FROM deleted d

    SELECT @CustomerNewName = First_Name

    FROM inserted

    SET @body = ‘Customer with ID=’ @CustomerID ‘ has been updated

    with previous First Name is ‘ @CustomerName ‘

    and the new First Name is ‘ @CustomerNewName

    –xp_sendmail is the extended sproc used to send the mail

    EXEC master..xp_sendmail

    @recipients = @CustomerEMailID,

    @subject = ‘Customer Information Updated’,

    @message = @body

    GO

    please help me to solve this problem

    Thanks & Regards,
    S. Ramkumar

  239. While taking append backup, when every 6th backup is completed, i want 1st backup to be removed from that backup file. when 7th backup is completed, 2 nd backup to be removed, when 8th backup……. How to do/set it for append backup.

  240. I have multiple database i think more than 40-50 dbs, I have to backup some dbs name LIKE ED% and delete tables LIKE REL% from each db.

    Anybody can solve this code?

    USE Master
    GO
    DECLARE @name VARCHAR(50)

    DECLARE @path VARCHAR(256)

    DECLARE @fileName VARCHAR(256)
    DECLARE @fileDate VARCHAR(20)
    DECLARE @sql NVARCHAR(4000)

    SET @path = ‘D:\Db_Backup\’

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    DECLARE db_cursor CURSOR FOR

    SELECT [name]
    FROM sys.Databases
    Where [name] LIKE ‘ED%’

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN
    SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
    BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5

    print ‘Db Name is : ‘ + @name
    –I can not get sys.tables from @name db on following lines
    –First i have to backup db name ED% than drop the tables REL%

    WHILE EXISTS (SELECT TOP 1 * FROM @name .sys.tables WHERE [name] LIKE ‘REL%’)
    BEGIN
    SET @sql = ‘DROP TABLE EDBO.[‘ + (SELECT TOP 1 [name] FROM @name .sys.tables WHERE [name] LIKE ‘REL%’) + ‘]’
    EXEC (@sql)
    END

    FETCH NEXT FROM db_cursor INTO @name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

  241. hi Pinal

    i’m a newbie to sql server. i’m having two versions sql server 2000 and sql server 2005. i want to find out the installed versions of sql server.

    i tried this “select @@version” but the result was only MS sql Server2000 not 2005!!

    i tried this also in command window

    c:\>osql -Q “select @@version”

    i need ur help!!

    thanx in advance!!!

  242. Dear Sir,

    I am using SQL Server 2005 and I want to have periodical backup (based on condition) of a particular table with appending mode. And how can I do the following using script/SP or any other solution?

    Table1 data should be transferred/appended to Table2
    before truncating them from Table1

    could you please assist me in this?

    thanks

    Jeet

    • I’m not sure what are you trying to achieve when you say periodical backup. If you would like to go for job scheduling then embed the code in SP and schedule in SQL Server Agent specifying the timeline.
      simple solution :
      insert into table2
      select * from table1
      go
      truncate table table1

      make sure columns, data types etc are proper…

  243. Hello,
    I want to create an sql database from vb.net 2005 through windows application. In connection string i need to specify a default database name first but i dont want to include the database name the first time i create the database. After creating my database i want to use it in connection string on the basis of which all the other project queries are executed.
    What do i do to create the database for the first time without passing the database name in the connection string.
    Do i have some other way out to create database from vb.net 2005.
    Pls, reply me.
    Rachana

  244. sri

    i need your help. we found following errors in running qury thru VB 6 exe. we use SQL Server 2000 with service pack 4 and OS 2003

    error

    “query processor could not produce a query plan because of the hints define in this query. Resubmit the query without specifying any hints. and without specifying any hints and without using SET FORCEPLAN ”

    waiting for your reply

    Regards
    Samip Shah

  245. I need your help regarding an issue on IDENTITY column. I have a Order table with an identity column. When ever a new record is created in order table new identity column value is generated written in a stored procedure. I also have a product table updation written in a separate stored procedure which requires the identity column generated in Order table. Both these stored procedures are running in a single transaction( Order will be saved first and then product will be saved). So i am not able to retrieve identity col value req for product table till the whole transaction is committed.

  246. HI PINAL,

    I am big fan of your blog.

    But I am sorry to say that You did not even mention about Mumbai terrorist attacks.
    As a citizen of India at least we can do console .
    I expect article /note from your blog .

    Thanks & Regards
    Kalyan

  247. HI PINAL,

    I am big fan of your blog.

    But I am sorry to say that You did not even mention about Mumbai terrorist attacks.
    As a citizen of India at least we can do console .
    I expect a article /note from your blog .

    Thanks & Regards
    Kalyan

  248. Hi,

    From past 3 months I am visiting your site regularly. I learn lot of things from your site. I need your help and guidance for learning SSRS, SSIS and SSAS. Can you help me in this.

    Thanks
    Nag

  249. Hi Pinal,

    I am trying to implement the transactions in ssis packages.The problem i am facing is when i run the package which uses bulk inserts tasks(3) and a bunch of execute sql tasks.When i run the package from visual studio the package fails in the last step sothat i can test the transaction rollbacks.as long as i am in the debug mode and package is in running mode, in sql server 2005 management studio the database to which the ssis is operating on is not accisable and it gives lock time out error 1222. But as soon as i end the the package the database is browsable(meaning the table list,sp list…)

    is there any way i can access the db while ssis pacage is still running mode?

    Thanks,
    Madhu

  250. hi…

    This is vjayabharathi.i was downloaded Sql srver 2005 express edition after downloading i was instaleed in my personal system but i didn’t find out Query Analyzer to do Sql &T-Sql queries. before installation of Sql server 2005 express edition i was installed DOTNEt Frame work as a support file but i didn’t find out query Analyzer…but i didn’t understand what’s the problem…plz send me some queries ASAP. i need Sql server 2005 Express Edition software r different edition & what is tha support file plz send me details…at the same time plz send how to install….

    thanks & Regards
    Vijayabharathi.

  251. Hi Pinal,
    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

  252. Hi Dave!

    SQL SERVER – Clear Drop Down List of Recent Connection From SQL Server Management Studio

    … Mru.dat …

    Observation:

    This does not work (for SQL Server Express 2008)? But if you erase
    C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\100\Tools\SqlStudio.bin
    - the list is empty!

    - with regards, Predrag

  253. Dear Sir Pinal,

    Please help me to solve my problem. I can’t continue my program because of this problem which I know you can solve or can suggest the best possible solution.

    I have a project in my study doing simple accounting program. To facilitate the discussion, I will just
    show you the two tables where I want to insert records – the TransactionTable and the TransactionDetailsTable.
    I am very confused of how to create a stored procedures to insert a single row of record in TransactionDetailsTable
    and multiple rows of record in TransactionTable. Can you please help me to create the perfect one for my problem?

    Below is a sample transaction

    Transaction Reference Number: CR12345

    Transaction Date: 12/06/2008

    GLCode GL Description Debit Credit
    ———– ——————— ———– ———–
    1010010000 CASH 1,500.00
    1050020000 Sales Discount 30.00
    1050010000 SALES 1,500.00

    Particulars: To record cash payment from customers on previous sales.

    TansactionTable Rows are as follows:

    TransNum BigInt PK AutoIncrement- This will store
    transaction identity
    TransRef VarChar(16) FK NOT NULL- This will store
    transaction details as entered by the
    user such as CR12345 based on the
    above sample transaction
    GLCode Char(10) NOT NULL – This is the Code of the
    General Ledger Account stored in the
    GLTable such as 1010010000 for
    CASH, 1050020000 for SalesDiscount,
    etc based on the above sample
    transaction.
    SLCode Char(10) NULLABLE- This is where I will store the
    clients Code if there is a transaction
    related to them
    TransAmount Money- This is the amount of debit(+) or
    credit(-) transaction

    TransactionDetailsTable rows are as follows:

    TransRef VarChar(16) PK - Handler of Transaction Details
    TransDate SmallDateTime – Date of Transaction such as
    12/06/2008 based on sample transaction
    TransParticulars – to handle particulars of the transaction
    such as “To record cash payment
    from customers on previous sales” on
    sample transaction.

    I created the transactionDetails table to handle Transaction Date and Particulars of the Transaction so that
    it will not be recorded repeatedly on TransactionTable.

    I created a function under the Class as follows

    Public Sub AddTransaction(ByVal myTransTable As DataTable, ByVal iret As Integer)

    I have strConnectionString declared.

    Dim myConnection As sqlConnection = New SqlConnection(strConnectionString)
    Dim cmdAddTransEntry, cmdAddTransRef As SqlCommand
    Dim ParamTransRef, ParamRef, ParamTransDate, ParamParticulars, ParamUserID, ParamGLCode, ParamSLCode, ParamTransAmount, ParamTransCat As SqlParameter
    Dim myTrans As SqlTransaction
    Dim strAddTrans As String

    myConnection.Open()
    myTrans = myConnection.BeginTransaction

    Try
    strAddTrans = “INSERT INTO TransactionDetailsTable(TransRef, TransDate, TransParticulars)” & _
    “VALUES(@TransRef, @TransDate, @TransParticulars)”
    cmdAddTransRef = New SqlCommand(strAddTrans, myConnection)
    cmdAddTransRef.Transaction = myTrans

    ParamTransRef = cmdAddTransRef.Parameters.Add(“@TransRef”, SqlDbType.VarChar, 16)
    ParamTransDate = cmdAddTransRef.Parameters.Add(“@TransDate”, SqlDbType.SmallDateTime, 8)
    ParamParticulars = cmdAddTransRef.Parameters.Add(“@TransParticulars”, SqlDbType.VarChar, 300)

    ParamTransRef.Value = VoucherNo
    ParamTransDate.Value = dtSystemDate ‘ stSystemDate is given under Global Module
    ParamParticulars.Value = Particulars
    cmdAddTransRef.ExecuteNonQuery()

    strAddTrans = “INSERT INTO TransEntryTable(TransRef, GLCode, SLCode, TransAmount)” & _
    “VALUES(@TransRef, @GLCode, @SLCode, @TransAmount)”

    cmdAddTransEntry = New SqlCommand(strAddTrans, myConnection)
    cmdAddTransEntry.Transaction = myTrans

    Dim TAmount As Double = 0

    For i = 0 To myTransTable.Rows.Count – 1 ‘ Came from Dataset

    ‘to determine if debit or credit and if credit make it negative(-)
    If Not IsNumeric(myTransTable.Rows(i)(2)) Then
    TAmount = myTable.Rows(i)(3) * (-1)
    Else
    TAmount = myTable.Rows(i)(2)
    End If

    ParamRef = cmdAddTransEntry.Parameters.Add(“@TransRef”, SqlDbType.VarChar, 16)
    ParamGLCode = cmdAddTransEntry.Parameters.Add(“@GLCode”, SqlDbType.Char, 10)
    ParamSLCode = cmdAddTransEntry.Parameters.Add(“@SLCode”, SqlDbType.Char, 10)
    ParamTransAmount = cmdAddTransEntry.Parameters.Add(“@TransAmount”, SqlDbType.Money, 10)
    ParamTransCat = cmdAddTransEntry.Parameters.Add(“@TransCat”, SqlDbType.Char, 1)

    ParamRef.Value = VoucherNo
    ParamGLCode.Value = myTable.Rows(i)(0)
    ParamSLCode.Value = SLCode
    If ParamSLCode.Value = “” Then
    ParamSLCode.Value = String.Empty
    End If
    ParamTransAmount.Value = TAmount
    ParamTransCat.Value = PrivTransCat
    cmdAddTransEntry.ExecuteNonQuery()
    Next

    myTrans.Commit()

    Catch ex As Exception

    myTrans.Rollback()
    MsgBox(“Transaction Not Added”)

    Finally
    myConnection.Close()
    End Try

    End Sub

    executing this will return error as follows under cmdAddTransEntry.ExecuteQuery()

    The variable name ‘@TransRef’ has already been declared. Variable names must be
    unique within a query batch or stored procedure.

    Please help me solve this problem.

    Thanks in advance and more power

    Ariel

  254. 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.

  255. I have problem with this qurey, result is ok but i have seen multiple duplicate records in brows mode please tell me about my problem the query is blow there:

    select c.batch_no,a.ship_date,b.custname,c.contract,c.style,c.quantity,c.quantity/c.qty_perctn as ctn_qty,d.ctn_size;
    from ex_paper a,customer b, ex_pap1 c, p_style d into cursor cebat where A.CUST_CODE=B.CUSTCODE AND c.batch_no=a.batch_no and c.style=d.style;
    AND BETWEEN(a.ship_date,date1,date2)

    please tell me about above this query

    regards,

    Zahid Khan

  256. Dear Pinal,
    Not sure if this is the right way to contact you. I am new to SQL 2005. Read your instructions to set up a remote oracle publisher from sql 2005. I have a evaluation version of sql 2005 enterprise edition.
    For some reason I am unable to launch the new oracle publisher wizard from the replication folder in SSMS. I installed the oracle software and can connect to the remote oracle server and also created an Administrative account in the database.
    I am trying to set up replication from oracle to sql so that the tables have updated data if new rows are added or modified.
    I have set up a test environment at home and have installed oracle 10g r2.
    Is there something I am missing. Can you please help.

    Regards

    Deepak

  257. Hi Dave,

    Since everyone has the problem of converting UFT8 to latin charset.I am also having the same problem when i migrate the data from oracle to sql server.I have gone through most of the forums ans still I havent come up with a proper solution.
    Kindly provide us a solution.

    Thanks,
    Arun RS

  258. 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,

  259. Hi pinal, will you please assist me i would like to build a database that will be storing music but i am not winning even if you can give me tutorial link i will be glad.

    Thanx

  260. Hi, I thank for the reply, the music database that i would like to create has one of the table that has the following columns:
    Artist_ID
    Track_ID
    Track_Title
    Track_FileName
    Track_Sequence

    So i would the design of this table, more focus on the “Track_FileName” the data type and will need for me to have file for each album on my database.

    thank you

  261. have a table called test with 2 columns like jobid & empid.this is my table….

    jobid empid

    1 12345
    1 13421
    1 24131
    2 25243
    2 52312
    2 54572
    2 87921
    3 10001
    3 51024
    4 87610
    4 87622
    now i have to display the result as like this

    jobid count

    2 4

    How can i write the sql query for this……….

  262. @ss

    Use Group By,

    here is an example.

    create table test (Jobid int, empid int )

    insert into test values ( 1 ,12345)
    insert into test values (1, 13421)
    insert into test values (1 ,24131)
    insert into test values (2, 25243)
    insert into test values (2 ,52312)
    insert into test values (2 ,54572)
    insert into test values (2 ,87921)
    insert into test values (3 ,10001)
    insert into test values (3, 51024)
    insert into test values (4, 87610)
    insert into test values (4 ,87622)

    select Jobid, count(*) [Count] from test
    group by Jobid

    – drop table test

    Hope this helps,
    IM

  263. sir

    i need how to connection to sql server 2005 to visual basic 6.0 . than i put in sql server 2005 user is sa put password but i change my password in sql server not work in vb pl reply me sir

  264. I want to select First value and Last value for the particular date and Id

    Table Ex.

    ID DATE VALUE
    001 23:04:2009 APPLE
    001 23:04:2009 GRAPHE
    001 23:04:2009 ROSE
    001 24:04:2009 BERRY
    001 24:04:2009 TIFFANY
    001 24:04:2009 ORGANE
    001 24:04:2009 SILVER

    FOR 001 VALUE NEEDED –

    APPLE AND ROSE FOR THE DATE OF 23:04:2009
    BERRY AND SILVER FOR THE DATE OF 24:04:2009

    Result Expected:

    ID DATE VALUE
    001 23:04:2009 APPLE
    001 23:04:2009 ROSE
    001 24:04:2009 BERRY
    001 24:04:2009 SILVER

    I tried Min, max, top condition but I am not getting a proper answer

    SQL QUERY HELP urgent

    • check out the below code snippet :
      create table #temp(
      ID varchar(10), DATE datetime, [VALUE] varchar(100))
      go
      insert into #temp
      select ’001′, ’2009-04-23′, ‘APPLE’
      union all select ’001′, ’2009-04-23′, ‘GRAPHE’
      union all select ’001′, ’2009-04-23′, ‘ROSE’
      union all select ’001′, ’2009-04-24′, ‘BERRY’
      union all select ’001′, ’2009-04-24′, ‘TIFFANY’
      union all select ’001′, ’2009-04-24′, ‘ORGANE’
      union all select ’001′, ’2009-04-24′, ‘SILVER’

      – method 1
      select v1.id, v1.date, v1.[value]
      from (select id, date,[value],
      row_number() over(order by id, date) as row_num from #temp) as v1
      where v1.row_num in ( select row_num from (
      select v.id, v.date,
      min(v.row_num) as row_num from (
      select id, date, row_number() over(order by id, date) as row_num from #temp) as v
      group by v.id, v.date
      union
      select v2.id, v2.date,max(v2.row_num) from (
      select id, date, row_number() over(order by id, date) as row_num from #temp) as v2
      group by v2.id, v2.date) as v5)

      – method 2 :
      with cte(id, date,[value],row_num) as
      (select id, date,[value],row_number() over(order by id, date) as row_num from #temp
      )
      select c1.id, c1.date,c1.[value] from cte c1, (
      select min(row_num) as min_row_num, max(row_num) as max_row_num
      from cte
      group by id, date) as c2
      where c1.row_num = c2.min_row_num
      or c1.row_num = c2.max_row_num

  265. Hi i have report (SSRS 2008 ) which look like this…………..

    Business Area MTD Month Estract X Y Z

    Segment Sales Plan Sales
    Category %
    Brand
    ——————————————————————————————————————————————–
    +LL Builder 34567 678 111 888

    +Decorative 43727 457 2453 6574

    + xyz 3587 8764 1134 36572

    +pqrs 6154 7676 8699 58585

    I need to sort the column that is if some one clicks on MTD Sales %
    all the values in the column need to be sorted even the drill down
    values in +LL Builder and others also need to be sorted

    I have tried different ways using interactive sorting

    In interactive sorting

    i have selected Group By and in Group By i have selected Business_Area

    and in Sort By ………i have selected MTD Sales (=Sum (Fields! MTD
    Sales.Value)

    and in also sort group i have selected Tablix ………

    its working fine with normal values…………….and for drill down
    values its not working.

    Query:

    SELECT NON EMPTY { [Measures].[EST YTD NET SALES % of PLAN],
    [Measures].[EST YTD NET SALES % of PREV YR],
    [Measures].[MaxERAEOperTimeSales], [Measures].[YTD PLAN],
    [Measures].[TOTAL SNB GROSS], [Measures].[YTD DP@STD], [Measures].[YTD
    DP@STLF], [Measures].[CURR BACKLOG GROSS], [Measures].[TOTAL BACKLOG
    GROSS], [Measures].[EST MTD NET SALES], [Measures].[MTD NET SALES],
    [Measures].[EST YTD NET SALES], [Measures].[YTD NET SALES],
    [Measures].[EST MTD NET SALES % of PLAN], [Measures].[MONTH PLAN] } ON
    COLUMNS, NON EMPTY { ([Product].[By Business Area].[Business
    Area].ALLMEMBERS * [Product].[By Segment Category
    Brand].[Brand].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
    MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (
    STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED) ) ON COLUMNS
    FROM ( SELECT ( STRTOSET(@ByYearMonth, CONSTRAINED) ) ON COLUMNS FROM
    [SALES])) WHERE ( IIF( STRTOSET(@ByYearMonth, CONSTRAINED).Count = 1,
    STRTOSET(@ByYearMonth, CONSTRAINED), [By Year Month].currentmember ),
    IIF( STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED).Count = 1,
    STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED), [Selling
    Company].[By Selling Company].currentmember ) ) CELL PROPERTIES VALUE,
    BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
    FONT_SIZE, FONT_FLAGS

  266. Hi Pinal,

    i have a column with positive and negative numeric values in it, how will i sum positive and negative values separately using aggregate function….Can anybody guide me….Thanks in advance….

    • Shah,
      If i understand your Qstn correctly, the column is integer type. Find the below code.

      select sum(column_name) from table_name
      where column_name > = 0 — for positive
      union
      select sum(column_name) from table_name
      where column_name < 0 — for negative

  267. Hi Pinal,

    would you please assist me to get the resolution in the following issue.I wrote this in the vbscript

    Set CnnSQL=CreateObject(“ADODB.Connection”)

    CnnSQL.Open “Provider=SQLOLEDB;” & _
    “Data Source=datasourcename;” & _
    “Initial Catalog=databasename;” & _
    “User Id=user;” & _
    “Password=password”

    my probelm is i want to pass the dynamic variable to initial catalog i.e. ” i ” is the value of database name reading from the text file and have to pass the different database name value to initial catlog for each loop

    i.e Initial Catalog=i (Parameterization)

    But when i executed that vb script i m getting the error i.e can not read the ” i “value

    Can you please provide me the solution.

    Thanks,
    Veera

  268. Hi,

    when i am restoring a database in sql server 2005. In compatability level sql server 2005 is not showing, if i select sqlserver 2000 it was giving me a error that “too many backup specified for backup or restore only 64 allowed”.

    It was giving me this error even though i m using sqlserver 2005.hw can i get sqlserver 2005 in compatibility dropdown list.

    Regards,
    S.Inayat Basha.

    • Hi Inayat,

      What statement you are using to restore the database. Compatibility level is not specified at restore time. Let us know the statement and error in details.

      Regards,
      Pinal Dave

  269. Hello Inayat,

    SQL injection is inputing a sql command to violate security and steal or corrupt data or even server.
    Most common way of SQL injection is changing the statement by passing specific values in parameters.
    This can be avoided by strongly typed parameter, using sp_executeSQL with parameter instead of dynamic query, replace ‘ (single quote) with ”(two single quote) in input paramter values and type check before using in statement.

    For more details please visit: http://msdn.microsoft.com/en-us/library/ms998271.aspx

    Regards,
    Pinal Dave

  270. HI
    i Have 3 tables one to many relation

    1 . tblStudent
    studintID pk
    studentName

    2 . tblStatus
    statusID pk
    statusName

    3 . tblStudentStatus

    srNo pk
    studintID fk
    statusID fk

    How can i select latest( top ) status of All Student ?
    when i use TOP(i) it selects only i Student please help me

  271. Hi,
    When i going to debbug SP in VS 2008 its giving error “canceled by user”

    SQL Server Edition: SQL Server 2005 SP3 Enterprise Edition,
    Win server 2003 64 bit,

    Please help me.

  272. Just wanted to extend my thanks and appreciation for devoting your quality time and energy for the betterment of SQL Server and IT as a whole.

  273. Hello pinal,
    I just know about the .bak file.
    I lost my .mdf file so how it possible restore my database with .bak file.

    I created database same as previous name.
    and restore with my backup file.
    but I got error.

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

    Restore failed for Server ‘MYPCNAME’. (Microsoft.SqlServer.Express.Smo)

    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: Directory lookup for the file “D:\SQL2005DataBases\TestDB.mdf” failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Express.Smo)

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

  274. I have two tables tbl1 and tbl2.
    in tbl1 one field is there.
    FirstName
    a
    b
    c
    d……..
    in tbl2 i have Menu field
    menu
    x
    y
    z….

    Now i would like to display Like… (Menu Fields are dynamic creation i.e x,y,z)

    FirstName x y z
    a
    b
    c
    d

  275. Hi Pinal,

    I am sort of novice as a DBA. I just started learning about log shipping. I did a test on my own computer that I have created a database called ‘Test’, then I restored to a new database ‘Test2′. FYI, I used with norecovery while doing the restoring the database.

    While restoring process is on going. I create a log shipping, Database Test as primary database and Test2 as secondary database. Test2 is set to Stand By Mode.

    Then I tried to run the related jobs manually and it looks successfully done. But,there is one thing seems to be strange. I got Skipped log backup file log shipping. Secondary DB: Test2. Could not find a log backup file that could be applied to secondary database Test2.

    What’s the main problem that makes this issue raised up?

    Thanks in advance.

  276. Hi Pinal,

    how are you?. i am new to SSIS. I am doing ETL mappings by using SSIS package. while doing i am getting below errors. Could you please guide me how to resolve these errors.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “OLE DB Destination” (73) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (86). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    [OLE DB Destination [73]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
    An OLE DB record is available. Source: “Netezza Performance Server” Hresult: 0×80004005 Description: “ERROR: External Table : count of bad input rows reached maxerrors limit”.
    An OLE DB record is available. Source: “Netezza OLE DB Provider” Hresult: 0×80004005 Description: “Unable to write nzlog/bad files”.
    An OLE DB record is available. Source: “Netezza OLE DB Provider” Hresult: 0×80004005 Description: “Unable to write nzlog/bad files”.
    An OLE DB record is available. Source: “Netezza OLE DB Provider” Hresult: 0×80004005 Description: “Operation canceled”.

    [OLE DB Destination [73]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “OLE DB Destination Input” (86)” failed because error code 0xC020907B occurred, and the error row disposition on “input “OLE DB Destination Input” (86)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “OLE DB Source” (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

    [OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

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