Contact Me « Journey to SQL Authority with Pinal Dave

Contact Me

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

This page has the following sub pages.

Contact Me - Archive 1

266 Responses to “Contact Me”

on July 19, 2008 at 1:51 pm pinaldave

Dear Readers,

I have received many comments on this page.

I do my best to answer almost all the queries and questions.

If possible I answer them right here or if necessary I send email as well.

Due to length of current page, I have moved previous comments on archive page here : Contact Me - Archive 1.

Fee free to ask your doubts here.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on July 20, 2008 at 8:00 pm sri

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
on July 22, 2008 at 10:15 pm Craig Douglas

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
on July 22, 2008 at 10:31 pm Pete Ocasio

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.
on July 23, 2008 at 10:13 am Zee

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
on July 23, 2008 at 11:59 am Naresh

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.
on July 23, 2008 at 2:33 pm Ashfaq

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
on July 23, 2008 at 2:50 pm Divz

Hi Pinal,

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

Thanks.
on July 23, 2008 at 3:42 pm vijay

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
on July 23, 2008 at 10:31 pm ANIL

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
on July 24, 2008 at 2:44 am Imran Mohammed

@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.
on July 24, 2008 at 1:15 pm Raj Patel

how set null row value when they more than one time in row ?
on July 24, 2008 at 1:44 pm Anoooo

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..
on July 24, 2008 at 6:50 pm Adrian Galende

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
on July 25, 2008 at 12:19 am Divz

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
on July 25, 2008 at 1:42 am Hardeep Singh

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.
on July 26, 2008 at 12:26 am Vijay

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: 0×4004300C 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
on July 26, 2008 at 12:16 pm Priyank Dave

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
on July 26, 2008 at 5:18 pm Kinjal

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
on July 27, 2008 at 11:48 pm KK

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
on July 28, 2008 at 10:05 am vallabh

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
on July 28, 2008 at 6:55 pm Will Turcios

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
on July 28, 2008 at 7:09 pm Titus

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
on July 29, 2008 at 12:31 am Mike Weltman

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 !!!
on July 29, 2008 at 3:10 am amit saxena

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
on July 29, 2008 at 7:06 am pinaldave

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
on July 29, 2008 at 7:37 am Benediktus

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-
on July 29, 2008 at 3:03 pm Zany D.S.

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!
on July 29, 2008 at 4:02 pm Mohamed Idhris

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 ?
on July 30, 2008 at 6:51 am Cenk Taylan Düz

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)
on July 30, 2008 at 7:30 am Anthony

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!
on July 30, 2008 at 9:59 am mike

Just curious , why this name SQL authority?

Is this some organization or company?
on July 30, 2008 at 7:48 pm mahi

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
on July 31, 2008 at 7:32 am Daniel Ashton

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

Thanks in Advance
Daniel
on July 31, 2008 at 10:44 am Thannan

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
on July 31, 2008 at 3:47 pm DBAtor

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
on July 31, 2008 at 8:15 pm Joseph Agai

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
on July 31, 2008 at 8:16 pm Mayank

I want to start your fan site.

Luve u boss.
on August 1, 2008 at 1:04 pm uğur oral

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.
on August 1, 2008 at 2:35 pm kalu

Hi Adrian Galende

See following link for select 1 vs select *

http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/

If exists (select from where )
statement1
else
statement2

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

Above syntax not clear.
on August 1, 2008 at 6:09 pm Duncan

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
on August 1, 2008 at 7:04 pm Mohan

hi all,
how can i get field name and type etc. in MS-SQL server 2005. is there any query available???
on August 2, 2008 at 2:01 am Imran Mohammed

@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.
on August 2, 2008 at 2:16 am Imran Mohammed

@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.
on August 4, 2008 at 8:05 am Maya Thanki

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)
on August 4, 2008 at 3:19 pm Saraboji Jayaraman

Dear Pinal Dave,

My Hearty Greeting to you!

I would like to learn some sql server cmd in DOS prompt which book is good?

Please advice me the same.

Thanks,
JSaraboji.
on August 4, 2008 at 10:02 pm Maya Thanki

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)
on August 5, 2008 at 7:01 am SQL SERVER - 2005 - Get Field Name and Type of Database Table Journey to SQL Authority with Pinal Dave

[...] 5, 2008 by pinaldave In today’s article we will see question of one of reader Mohan and answer from expert Imran Mohammed. Imran thank you for answering question of [...]
on August 5, 2008 at 9:42 am Steve Harlington

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:)
on August 5, 2008 at 9:18 pm sam

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
on August 6, 2008 at 3:18 am Vishal

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
on August 6, 2008 at 5:04 pm Ashutosh

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??
on August 6, 2008 at 5:29 pm Chetan

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
on August 7, 2008 at 11:24 am Giribabu

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
on August 7, 2008 at 1:30 pm Ashutosh

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??
on August 7, 2008 at 1:39 pm Jigar

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
on August 7, 2008 at 4:13 pm Giribabu

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
on August 7, 2008 at 5:26 pm Giribabu

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)
on August 8, 2008 at 12:30 am MAMU99

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
on August 8, 2008 at 11:35 am GreggB

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
on August 8, 2008 at 5:35 pm Faisal

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
on August 10, 2008 at 1:30 pm Tito

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
on August 11, 2008 at 11:03 am Gabriel

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,
on August 11, 2008 at 2:43 pm Prajin

Hi Pinal,

Could you please give some notes on SQL profiler..
as i am new to this..
Thanks
Prajin
on August 11, 2008 at 4:24 pm Susanta

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
on August 11, 2008 at 9:09 pm Vaibhav

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
on August 11, 2008 at 11:03 pm sushma

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
on August 12, 2008 at 12:49 am Chris

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.
on August 12, 2008 at 1:33 am Imran Mohammed

@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.
on August 12, 2008 at 7:15 am California

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
on August 12, 2008 at 1:15 pm SJ

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
on August 12, 2008 at 1:20 pm SJ

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
on August 13, 2008 at 12:59 am Imran Mohammed

@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.
on August 13, 2008 at 10:31 am Raji

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.
on August 13, 2008 at 4:41 pm Venkat919

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
on August 13, 2008 at 9:03 pm Anu

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.
on August 14, 2008 at 12:12 am Namita

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
on August 14, 2008 at 12:41 am Kalyan

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.
on August 14, 2008 at 1:48 pm Aks

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
on August 14, 2008 at 5:06 pm Roger Ben Silva

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
on August 14, 2008 at 7:33 pm Anshu

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
on August 15, 2008 at 1:51 am scott

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
on August 15, 2008 at 7:15 pm Sharjeel

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
on August 17, 2008 at 6:58 pm subin

hai sir
i want to know Sub queries in detail
on August 18, 2008 at 9:47 am Emeka

@Scott

select date from mytable
union all
select ‘ALL’

hope this helps
on August 18, 2008 at 9:49 am Emeka

@Daniel Ashton

select top 10 salary from mytable
order by salary desc.

Hope this helps
on August 18, 2008 at 8:19 pm SQL_Junkie

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.
on August 19, 2008 at 1:14 pm Nandha

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
on August 19, 2008 at 2:50 pm Zahid

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.
on August 19, 2008 at 4:41 pm Kamlakar

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
on August 19, 2008 at 7:32 pm dat

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
on August 20, 2008 at 10:36 am Corina Macia

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
on August 20, 2008 at 1:54 pm siju

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
on August 20, 2008 at 10:52 pm Imran Mohammed

@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.
on August 22, 2008 at 9:49 am Zin

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
on August 22, 2008 at 5:20 pm Nitin Kumar

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
on August 23, 2008 at 11:20 pm tibya

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
on August 24, 2008 at 11:51 am Imran Mohammed

@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.
on August 24, 2008 at 1:56 pm issam

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
on August 25, 2008 at 12:15 pm Emeka

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.
on August 27, 2008 at 1:53 pm Ruan

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
on August 27, 2008 at 11:09 pm Hitesh

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.
on August 28, 2008 at 8:18 am Imran Mohammed

@ 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.
on August 28, 2008 at 9:25 pm srini

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
on August 28, 2008 at 11:37 pm K. Baldwin

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.
on August 29, 2008 at 4:44 am ARABIA

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.
on August 29, 2008 at 11:00 am Malik

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
on August 29, 2008 at 8:42 pm Imran Mohammed

@ 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.
on August 30, 2008 at 1:22 am Natarajan

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
on August 30, 2008 at 11:39 am tibya

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.
on August 30, 2008 at 2:59 pm Imran Mohammed

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.
on August 30, 2008 at 3:05 pm Narendra Jani

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.
on August 30, 2008 at 3:37 pm Imran Mohammed

@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.
on August 30, 2008 at 6:19 pm Amanda

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
on August 30, 2008 at 10:21 pm Imran Mohammed

@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.
on September 1, 2008 at 12:25 pm Emeka

@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
on September 2, 2008 at 6:54 pm Moretlwa

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
on September 3, 2008 at 11:36 am Mohan

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
on September 4, 2008 at 12:42 am tibya

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
on September 4, 2008 at 3:07 pm Aruna Namal

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
on September 4, 2008 at 4:31 pm Arvinder

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
on September 4, 2008 at 9:55 pm Imran Mohammed

@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.
on September 4, 2008 at 10:00 pm Imran Mohammed

@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.
on September 5, 2008 at 3:13 am Vijeya Shobana

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
on September 5, 2008 at 9:40 am Imran Mohammed

@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.
on September 5, 2008 at 11:47 am Emeka

@Arvinder
@121

USE Database;
GO
DBCC CHECKIDENT (’MyTable’);
GO

Hope this helps
on September 5, 2008 at 8:53 pm

Journey to SQL Authority with Pinal Dave

Notes of a SQL Server MVP and Database Administrator