Contact Me « Journey to SQL Authority with Pinal Dave

Contact Me

Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1100 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.

I am proficient in Corporate Training. I have designed and implemented complex database architecture, and have also implemented strategies for database high availability and scalability. Furthermore, my core expertise lies in query tuning and performance optimization.

If you want to seek my expertise then drop me a line and tell me about your requirements by using the form below or send me email pinal “at” sqlauthority.com. I value development community and will be happy to help you at any stage of project development, from design to deployment.

Copyright violation and Reproduction of blog:

SQLAuthority.com is trademark of Pinal Dave. Exact work “SQLAuthority” or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave.

If any article published on this blog violates copyright please contact me, I will remove it right away.

Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, which ever is maximum in event of linking to blog (Link is must).

Search SQLAuthority.com

If you have any questions for faster response, Search SQLAuthority.com. It is possible that your question is already answered in one of the hundreds articles.

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

This page has the following sub pages.

197 Comments »

197 Responses

on July 18, 2009 at 8:12 am | Reply Babu

Dear Pinal,

Greetings…

Is there any working professional cum tutor/teacher to teach SQL 2008 in Chennai(Tamilnadu,India) apart from NIIT, STG and Digiterati. Thease institutes are giving only theoritical knowledge. But they unable to answer production based problems.
Can you help in this regard?
- on November 1, 2009 at 7:18 am Allen McGuire
  
  What questions might you have Babu? Pinal is likely a very busy man, but I would be more than happy to answer any questions you may have regarding SQL 2008. I’ve been administering it since it’s inception.
on July 18, 2009 at 1:44 pm | Reply suresh

please give me the network commands in sql
on July 20, 2009 at 1:23 pm | Reply Ram Yadav

Dear sir,

I m ram yadav from mumbai(india).
i read u r blog always and i find it’s too much intresting.
I have a query that :-

I hava a table A and columns are SrNo,Qty,Wt
suppose there are 3 srno like

1 100 50
2 200 50
3 50 25

now i have use srno 1 with 50 qty and now my balance qty is 50.i will use next time remaning 50 qty.
so how i can use these process in my sql server please help…….

i hope that u will reply…..
and Please reply on my E-Mail Id Please…..Pls…..

Thanks,

Regards,
Ram Niwas Yadav
on July 20, 2009 at 3:27 pm | Reply Ray

Dear Pinal,
I solved many problems by reading your blog. It is excellent!

But I still have 1 problem that cannot be fixed. I have a SP which can be completed in the SQL Managemet Studio in 1 seconds. But if I use the vb.net to execute it, it took very long time to complete (more than 1 min). I use the profiler and activity monitor to troubleshoot, the SP just keeps running. If I change the same vb.net code to another SP name, it works fine.

May I ask what the problem is? Thanks again.

Best regards,

Ray
on July 20, 2009 at 5:44 pm | Reply Brian Tkatch

@Ram

Just calculate the COLUMN. That is, instead of referring to Qty, refer to Qty – Wt.
on July 20, 2009 at 5:45 pm | Reply Brian Tkatch

@Ray

Are you passing the same parameters each time?

What is the SP doing.
on July 20, 2009 at 5:54 pm | Reply Kulbir

Hi pinal
I’m using sql server 2008.i get the following error when i try using sqlcmd
HResult 0×2,Level 16,state 1
Named pipes providers:could not open a connection to sql server[2]
sqqlcmd:Error:microsoft sql server native client 10.0:An error has occured while establishing a connection to the server.
sqlcmd:Error:microsoft sql server native client 10.0:login timeout expired.

I have tired all solution suggested on different blogs but enable to solve the problem.plz answer as soon as possible
on July 21, 2009 at 8:34 am | Reply Ravi

I’ve a question. If you know any solution let me know

Scenario:

I need to write one batch file where it has to read text file data, first it has to check the data in the database whether record exist or not, if not then it has to insert that data into local database (Oracle).We’ve to use SQL Loader for this process.Do you know how do we create this one with SQL Loader?

Thanks&Regards,

Ravikiran.T
on July 21, 2009 at 4:59 pm | Reply Brian Tkatch

@Ravi

Which part are you asking about? Getting the data from SQL Server, or putting it into Oracle?

SQL Server has DTS/SSIS packages that can handle the entire process.

As for SQL*Loader, it has options not to overwrite. Check the Oracle utilities documentation.
on July 22, 2009 at 2:14 pm | Reply kannadasan

I have doubt..
i have one table
———————————–
1 kannadasan
1 ram
1 bala
1 raman
1 Babu

this table convert to
1
kannadasan
ram
bala
raman
babu

give solution in ASp.net
on July 23, 2009 at 8:57 am | Reply Ray

@ Brian Tkatch

Thanks for your reply.
The parameter is the user ID. The SP will do this:

select count(recid) from myView where userID = @userID

Some people said we should turn Arithabort ON in the vb.net code. And some said the SP should include the with recompile option. Please advise. Thanks.
on July 23, 2009 at 10:12 am | Reply Imran Mohammed

@Ray,

I Would suggest you use Exec Sp_ExecuteSQL to execute any dynamic SQL.

Something like this.

Exec Sp_ExecuteSQL N’select count(recid) from myView where userID = @userID’, N’userID int’

~IM
on July 23, 2009 at 1:20 pm | Reply Qutub

How can i insert records from one table to another table in a batch of 1000 records in SQL Server 2005.
on July 23, 2009 at 1:22 pm | Reply Qutub

How can i insert records from one table to another table in a batch of 1000 records without using a cursor in SQL Server 2005 ?
on July 23, 2009 at 6:00 pm | Reply Brian Tkatch

@Qutub

The simplest solution would be to: INSERT INTO tab2 SELECT TOP(1000) FROM tab1;

But maybe there are more details?
on July 24, 2009 at 3:28 am | Reply Imran Mohammed

@Qutub

What do you want to achieve by doing that ? What is the objective ?

~ IM.
on July 24, 2009 at 8:44 am | Reply Ray

@ Imran Mohammed

I’ll try. Thanks a lot.
on July 24, 2009 at 10:32 am | Reply Qutub

The above question was an interview question which was asked to me.
Moreover, if there are around 2,00,000 records in the table which are to be copied, TOP(1000) would pick the first 1000 records. But what about the succeeding batches of 1000 records? Thanx.
on July 24, 2009 at 8:26 pm | Reply Colleen

Hello Penal Dave,

I am trying to install SQL Server Express Edition 2005. I cannot download the Adventureworks. I starts to download and then opens word processor. After that an error message show that this file is currently being used or is corrupt. Do you have any suggestions?

Colleen
on July 26, 2009 at 12:37 am | Reply subramaniam

sir i have one doubt in sql server 2005
how can i insert a new column into table
that column should insert between two columns

for example
i have Eid and Ename in my table i have add a column Esalay between Eid and Ename.

how?
tel me the query

thank u
on July 26, 2009 at 10:39 pm | Reply Syed Ahmed

I’m unable to install MS Sql Server 2000 on WinXP SP2. I need MS Sql Server because it comes with Query Analyzer, which is missing in the latest versions.
When I start the setup, it does something for 2-3 seconds and nothing happens. When I start again it shows that setup already running. But it does nothing.
Please respond.

thanks.
on July 27, 2009 at 11:24 am | Reply Allice

Hello Penal Dave,

I want to store data in two languages(English, Hindi) simultaneously. How can i do it. Please help.

Allice
on July 27, 2009 at 11:36 am | Reply Jigna

Hi,

I am getting an error message :
My dabase could not be open. When try to open it in SQL Server MAnagement studio, it gave me following error message:

“DATABASE cannot be opened. It has been marked suspect by recovery. See the SQL Server Error Log for more information. ( Microsoft SQL Server Error 926)”

I am using SQL Server 2005.

Pls help how can i Solve this problem.
on July 27, 2009 at 10:35 pm | Reply Narendra

Hi .

I am getting problem while inserting data from a file using BULK INSERT utility server .
The charecter to be inserted is of German charecer like ‘Jörg’ and its getting loaded as ‘J+Ârg .’

Can you please tell me the solution on this .I am using Datafile type as ‘char’ in this case . If i use it as native or widenative then file is not at all getting inserted .

Please help on this .
on July 28, 2009 at 10:54 am | Reply krishna

Sir,

How to import and export the data through DTS in SQL Server.
on July 29, 2009 at 6:31 pm | Reply kalyan

In my organization, we are developing lot of SSIS package and deploy it. My team is group of 7, Each time, when SSIS package is deployed, we have Excel file in shared directory, whoever deploying package to production, needs to update Excel with source and target (meta information).

Is there a way to develop SSIS package/ .Net application to run frequently to get meta data information of SSIS package on all production m/c. Our practice is , deploy into MSDB database, from there we are scheduling.

If you have any logic to extract or any sample work , that will be great for us to avoid confusions other issues.

Thanks
Kalyan
on July 30, 2009 at 2:58 pm | Reply AlexB

Hi

I have read your article: http://blog.sqlauthority.com/2007/05/22/sql-server-2005-comparison-except-operator-vs-not-in/

And i have another question: What is the better way to use left join or except syntax?

left join syntax should be:

SELECT Product.ProductID
FROM Product
LEFT JOIN WorkOrder on Product.ProductID = WorkOrder.ProductID
WHERE WorkOrder.ProductID IS NULL
on July 30, 2009 at 8:54 pm | Reply Abhi

Hello,

if i add one more ndf or ldf file in logshipping primary server. is it affect my logshipping configuration or not if no how come
on July 31, 2009 at 7:28 am | Reply Abe

Hey Pianl Dave –
Your picture on every page is ridiculous and distracting.
Can you take it off.
on July 31, 2009 at 10:32 am | Reply Manish

Hi Pinal,

I am trying to update column using self- join as follows :
UPDATE LoyaltyRept
SET LoyaltyRept.CustomerChange = ((CONVERT(NUMERIC, LoyaltyRept.TotalOrderingCustomers)/CONVERT(NUMERIC,A.TotalOrderingCustomers)) -1)
FROM LoyaltyRept A
WHERE A.Month = @MIN_Month
AND A.site =LoyaltyRept.site
AND LoyaltyRept.Month = @Updt_Month

But giving following error :

Msg 4104, Level 16, State 1, Line 20
The multi-part identifier “public_New.LoyaltyRept_OrderingCustomersRecency” could not be bound.

Please help me.

Thanks
Manish
on July 31, 2009 at 5:57 pm | Reply Brian Tkatch

@AlexB

Neither. Use NOT EXISTS:

SELECT Product.ProductID
FROM Product
WHERE NOT EXISTS(SELECT * FROM WorkOrder WHERE Product.ProductID = WorkOrder.ProductID)
on July 31, 2009 at 6:01 pm | Reply Brian Tkatch

@Manish

LoyaltyRept must also be mentioned in the FROM clause.
on August 4, 2009 at 1:47 am | Reply rm

exec master.dbo.xp_startmail;
EXEC master.dbo.xp_sendmail @recipients = ”,
@subject = ‘RM testing’,
@message = ‘RKKKKK tetsing .’
exec master.dbo.xp_stopmail;

couldn’t get subject line when I got mail.

Please help me how can I get subject line.

Thanks

RK
on August 4, 2009 at 5:53 pm | Reply Harinadh

Hi Pinal,

I have to replace *= ,=* by LEFT/RIGHT JOIN.Can you please give the solution for the below query

FROM Famille_Tarifaire As FT,
Prix_Base As PB,
Member As Proprietaire,
Member As Client
WHERE
PB.ID_Membre_Client =* Client.MemberId AND Proprietaire.MemberId = FT.Id_Membre_Fournisseur AND FT.Id_Famille_Tarifaire *= PB.Id_Famille_Tarifaire

Thanks in advance!!

Regards,
Hari
on August 5, 2009 at 12:45 am | Reply prashanth

hi
this is prashanth,

I want to deploy a web application with a batch file containing scripts which create database and tables and sp associated with it.

I can create and deploy a web application on iis but i cannot attach a batch file containing scripts which has db file

i cannot even create a batchfile containing these scripts.

plz help thanks in advance
on August 5, 2009 at 1:20 am | Reply Brian Tkatch

@Harinadh

I’m not sure i got this right, but here’s a shot:

FROM
Famille_Tarifaire As FT
LEFT JOIN
Prix_Base As PB
ON
(FT.Id_Famille_Tarifaire = PB.Id_Famille_Tarifaire)
RIGHT JOIN
Member As Client
ON
(PB.ID_Membre_Client = Client.MemberId)
INNER JOIN
Member As Proprietaire
ON
(Proprietaire.MemberId = FT.Id_Membre_Fournisseur)
on August 5, 2009 at 5:38 pm | Reply Harinadh

Thank you Brian Tkatch

But the solution is not giving the exact result as before

Can someone provide the exact solution

Regards,
Hari
on August 5, 2009 at 11:28 pm | Reply Alan Arico

Dear Pinal,

Is there a way to concisely describe how EDI relates to SQL Server or visa-verse?

Thanks,
Alan
on August 6, 2009 at 10:16 am | Reply prashanth

but i now want to know how this batch file is attached with a web application and when we create a deployment package and install it in other computer this .bat file should execute and a data base should be created…

if some one know plz..

thnks in advance

-pth
on August 6, 2009 at 5:47 pm | Reply Brian Tkatch

@Harinadh

Sorry about that. I am not very experienced in ANSI syntax.

Does not “exact” mean not the same result set, or not the same order?
on August 6, 2009 at 7:45 pm | Reply sampath

Hi

I am using sql2000 , and server got crashed ,
my database ldf file showing issue and have renamed the log file and tried to attach using sp_attach_single_file_db
while attaching the database got below error

Server: Msg 823, Level 24, State 6, Line 1
I/O error 38(Reached the end of the file.) detected during read at offset 0000000000000000 in file ‘F:\MSSqlSvr\Logs\CusActv_Log.LDF’.

Connection Broken

Please help me urgent , it is production database.

thanks
on August 6, 2009 at 7:54 pm | Reply sampath

hi
i am using sqlserver2000, when i am attaching the database got ldf file issue , and when i tried to attach single db file

got below error :

Server: Msg 823, Level 24, State 6, Line 1
I/O error 38(Reached the end of the file.) detected during read at offset 0000000000000000 in file ‘F:\MSSqlSvr\Logs\CusActv_Log.LDF’.

Connection Broken

Please help me attach the databse , ASAP .. waiting for your reply sir thankyou
on August 7, 2009 at 2:30 am | Reply Pallavi

Pinal,

For any sql server issues, I usually refer to your website. Thanks for updating the site frequently and posting interesting articles.

I could not find a quick solution for a problem? Is there a way to find out what databases are user created ones?

I have one giant list of databases (executing sp_databases). But how do I find out which one is user created in sql server?
on August 7, 2009 at 10:19 am | Reply chirag

pinal,

i m not clear (by defination) about data types using by sql.
Pl. explain

–> char(n)-varchar(n)
–> binary-varbinary-image
–> nchar-nvarchar
–> sql_variant-table

pl. explain all descibed terms with example and tell where is it used and/or how far it will be usefull in terms of processing or any other concept.
on August 8, 2009 at 1:42 am | Reply Kyle

Pinal,

I am new to SQL Server. I’m using SQL Express 2005 running SDE. My Database was created using ArcCatalog by ESRI.
I’m trying to find out where in the SQL Server Manager Studio, do I write code that will calculate a record when the user creates a new record.
on August 10, 2009 at 11:48 am | Reply Harinadh

Hello Brian Tkatch,

after replacing with your suggested changes i got no result set values but before it is returning result with some records.

Regards,
Hari
on August 10, 2009 at 12:07 pm | Reply Fazal

Dear Pinal ,

Hi , I Want to concate two or More rows in one rows.My data is

Code Name
10541 abcd
10541 xyz

I want to display following data inresult.

Code Name
10541 abcd,xyz

Please Give me a solution.
on August 10, 2009 at 7:07 pm | Reply Brian Tkatch

@Harinadh

I do not know then. Obviously, i still have a lot to learn. Hopefully, someone else will answer the question. Note, you can also ask at the MSDN forums.
on August 11, 2009 at 8:38 am | Reply Imran Mohammed

@Harinadh,

Looks like you are trying to convert a PL-SQL script to SQL Server Script. I had done this assignment in the past. I did not spent too much time figuring out what is what.

May I suggest you one tool that will convert PL-SQL to T-SQL.

You can download trail version, and convert all your scripts converted into T-SQL. Review scripts carefully before you actually use them in your procedures.

Here is the free download link (trail version) of SwisSQL (Oracle to SQL Server)

http://www.swissql.com/products/oracle-to-sqlserver/oracle-to-sql-server.html

Enjoy.
~ IM.
on August 11, 2009 at 12:22 pm | Reply Tejas Shah

@ Fazal,

Very frequent question in SQL. Please read this:

http://www.sqlyoga.com/2009/02/sql-server-get-comma-separated-list.html

Thanks,

Tejas
on August 11, 2009 at 1:11 pm | Reply Harinadh

Thank you Brian Tkatch

Hello Imran,

It is not conversion from PL-SQL to SQL server.

I need to modify the SQL procedures with replacing the *=,=* by LEFT JOIN ,RIGHT JOIN .

Can some one provide solution.

Thanks in advance!!

Regards,
Harinadh
on August 11, 2009 at 9:37 pm | Reply Rupesh Behera

Hi Pinal,
I just face some problem on my database. It is self blocking.
I am running sql server 2000(sp4). Can a select query be blocked and why self blocking occurrs on the server frequently.

Thanks,
Rupesh
on August 13, 2009 at 5:33 am | Reply Sam

Hi Pinal,

I’m trying to get the difference of first task’s StartDateTime and Second task’s StartDateTime and so as for 2nd and 3rd task to calculate Total Actual Time for an Order. I’ll only use the EndDateTime to get the final actual time because that is the end task of the Order. How can I get differnce of the first task start time and second task start time and so on. Can we do it without using cursor, may be using different temporary tables.

TaskNumber Order StartDateTime EndDateTime
5072161 3559013 2009-04-27 16:31:15.000 2009-04-27 16:32:11.000
5072189 3559013 2009-04-27 16:32:12.000 2009-04-27 16:33:29.000
5072305 3559013 2009-04-27 16:33:30.000
2009-04-27 16:34:04.000
on August 14, 2009 at 12:12 am | Reply Brian Tkatch

@Sam

How does this look?

WITH
Data(TaskNumber, [Order], StartDateTime, EndDateTime)
AS
(
SELECT 5072161, 3559013, ‘2009-04-27 16:31:15.000′, ‘2009-04-27 16:32:11.000′ UNION ALL
SELECT 5072189, 3559013, ‘2009-04-27 16:32:12.000′, ‘2009-04-27 16:33:29.000′ UNION ALL
SELECT 5072305, 3559013, ‘2009-04-27 16:33:30.000′, ‘2009-04-27 16:34:04.000′
),
Data_RN
AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY [Order] ORDER BY TaskNumber) RN,
TaskNumber,
[Order],
StartDateTime,
EndDateTime
FROM
Data
)

SELECT
DR1.TaskNumber,
DR1.[Order],
DATEDIFF(s, DR1.StartDateTime, DR2.StartDateTime)
FROM
Data_RN DR1
LEFT JOIN
Data_RN DR2
ON
DR2.[Order] = DR1.[Order]
AND DR2.RN = DR1.RN + 1;
on August 14, 2009 at 12:11 pm | Reply Harinadh

Hello Pinal,

Please help me in modifying the below query by replacing *= with LEFT JOIN.I am unable to do because of the complex join condition.

select * from
Commercial_Response as cr,
Lieu_Livraison_BOC as ll_boc,
Member as m_Fournisseur,
Member as m_selag,
Product as p_offert,
Product as p_commande,
Commercial_Offer as co
where
cr.LocationId *= ll_boc.Id_Lieu_Livraison
AND m_fournisseur.Code_BackOffice *= ll_boc.Code_BackOffice
AND cr.Id_agence_Responsable *= m_selag.MemberID

Thanks in advance!

Regards,
Hari
on August 16, 2009 at 5:06 pm | Reply Sam

Thank you Brian,

Very good example. It works in Sql Server 2005, but not in 2000. I think, 2000 does not support WITH clause, right? And, apparently my development version is 2000. Any suggestion?

Regards,

Sam
on August 16, 2009 at 5:52 pm | Reply Rishi Kabra

I am looking for smaple database AdventureWork DW for
SQL 2005.
Plz let me know the specific link to download this database.

Or can mail me script to configure.

With regards,
on August 17, 2009 at 6:16 pm | Reply Brian Tkatch

@Sam

The first CTE is just data, so that does not matter. The second CTE could be oput in the FROM clause.

The real issue is, that ROW_NUMBER() is not available in 2000.

If you could add a row number (or have a similar umber in there already), which would obviate the need for ROW_NUMBER(), it becomes possible.
on August 17, 2009 at 6:17 pm | Reply Brian Tkatch

@Rishi Kabra

How’s this: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en
on August 17, 2009 at 8:46 pm | Reply prasanth

Hi,
I have column InputDate which is Datetime datatype. If this InputDate column i need to display in the date format like if i use this query in US, US format should be displayed, if in UK that format and Singapore it should display in that date format.( may be by sql server installation date format it should read and display as output format.) Plz help me out.

Rgds,
Prasanth
on August 18, 2009 at 1:32 am | Reply Brian Tkatch

@Prasanth

See CONVERT() in the help file. It has a number of formats it can output it as.
on August 18, 2009 at 12:01 pm | Reply prasanth

thanks Brian, but convert(), each and every date format we need to change for every country. is there any solution like sql server installtion date format we could get and according date could be displayed ? As my requirement is InputDate column should be displayed in Date format used by that respective country.

Thanks in Adv.,
Prasanth
on August 18, 2009 at 1:48 pm | Reply chirag

Hi,
I have problem with query. i have following table.

scheme nav_rate nav_date
—————————————————–
s0001 11.2354 27-apr-2009
s0002 9.6547 28-apr-2009
s0003 46.254 29-apr-2009
s0001 13.2413 12-may-2009
s0002 11.6547 24-may-2009
s0003 49.3712 20-may-2009
s0001 9.2354 27-mar-2009
s0002 8.6547 28-mar-2009
s0003 44.2413 29-mar-2009
…

i want to summarise output ln this format

scheme max(nav_rate) date min(nav_rate) date
—————————————————————————
s0001 13.2413 12-may-2009 9.2354 27-mar-2009
….

Can some one provide solution.

Thanks in advance!!

Regards,
Chirag
on August 18, 2009 at 4:24 pm | Reply Tejas Shah

@Chirag,

You can get expected output as:

DECLARE @test TABLE(Scheme VARCHAR(12), nav_rate NUMERIC(16,4), nav_Date DATETIME)

INSERT INTO @test
SELECT ’s0001′, 11.2354 ,’27-apr-2009′
UNION ALL
SELECT ’s0002′, 9.6547 ,’28-apr-2009′
UNION ALL
SELECT ’s0003′, 46.254 ,’29-apr-2009′
UNION ALL
SELECT ’s0001′, 13.2413 ,’12-may-2009′
UNION ALL
SELECT ’s0002′, 11.6547 ,’24-may-2009′
UNION ALL
SELECT ’s0003′, 49.3712 ,’20-may-2009′
UNION ALL
SELECT ’s0001′, 9.2354 ,’27-mar-2009′
UNION ALL
SELECT ’s0002′, 8.6547 ,’28-mar-2009′
UNION ALL
SELECT ’s0003′, 44.2413 ,’29-mar-2009′

SELECT t1.Scheme, t2.MAXnav_rate, t1.nav_date, t2.Minnav_Date
FROM @test t1
INNER JOIN (
SELECT Scheme,
MAX(nav_rate) AS MAXnav_rate,
min(nav_Date) AS Minnav_Date
FROm @test t1
GROUP BY Scheme
) t2 ON t1.Scheme = t2.Scheme
AND t1.nav_rate = MAXnav_rate

Let me know if it helps you.

Thanks,

Tejas
SQLYoga.com
on August 18, 2009 at 6:00 pm | Reply Brian Tkatch

@prasanth

I do not know. I thought that was the default.
on August 18, 2009 at 6:14 pm | Reply Brian Tkatch

@chirag

I’m not sure this is the best way to do it, but it should return the correct results.

Note: The first CTE is just your data. The second CTE assigned a row number which is the basis of the query.

WITH
Data([scheme], nav_rate, nav_date)
AS
(
SELECT ’s0001′, 11.2354, ‘27-apr-2009′ UNION ALL
SELECT ’s0002′, 9.6547, ‘28-apr-2009′ UNION ALL
SELECT ’s0003′, 46.254, ‘29-apr-2009′ UNION ALL
SELECT ’s0001′, 13.2413, ‘12-may-2009′ UNION ALL
SELECT ’s0002′, 11.6547, ‘24-may-2009′ UNION ALL
SELECT ’s0003′, 49.3712, ‘20-may-2009′ UNION ALL
SELECT ’s0001′, 9.2354, ‘27-mar-2009′ UNION ALL
SELECT ’s0002′, 8.6547, ‘28-mar-2009′ UNION ALL
SELECT ’s0003′, 44.2413, ‘29-mar-2009′
),
Data_RN
AS
(
SELECT
[scheme],
nav_rate,
nav_date,
ROW_NUMBER() OVER(PARTITION BY [scheme] ORDER BY nav_rate) RN_Min,
ROW_NUMBER() OVER(PARTITION BY [scheme] ORDER BY nav_rate DESC) RN_Max
FROM
Data
)
SELECT
Data_RN_Max.[scheme],
Data_RN_Max.nav_rate,
Data_RN_Max.nav_date,
Data_RN_Min.nav_rate,
Data_RN_Min.nav_date
FROM
Data_RN Data_RN_Max,
Data_RN Data_RN_Min
WHERE
Data_RN_Max.RN_Max = 1
AND Data_RN_Min.[scheme] = Data_RN_Max.[scheme]
AND Data_RN_Min.RN_Min = 1;
on August 18, 2009 at 11:47 pm | Reply Gerardo Trejo

Hi Pinal I’ve been reading your blog and it’s very interesting. I have a serious problem with my SQL Server 2005 installed in my Windows Server 2008 machine.

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

My dedicated admin connection port is 51780
My default port is 49161

The things I did:

1. I installed SQL Service Pack 3, but it’s important to say that my SQL Server has never worked.
2. I enabled TCP/IP connections in the Surface Area Configuration.
3. In the Registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SuperSocketNetLib\Tcp\IPX I changed the TcpPort to my default port (49161)
4. The same as step 3 a changed to my default port the port number in the Configuration Manager of my instance.
5. I switched off my firewall (hehe).
6. I created an alias and it doesn’t work.

Other details are:
Can not connect using localhost, I just can connect using ServerName\InstanceName and the alias I created.
In the network databases I don’t see all of them and the ones I see I can’t connect to them.

Don’t know if it’s a security issue and/or compability issue?

I’m really desperate, please help guys.
on August 19, 2009 at 1:57 am | Reply ashish ghai

dear sir,

i am a student and i am working on a VB.net application
which is used to store accounts of a mobile store

i need a database which could be deployed in the application as well as is capable to be adding the columns using alter table command

i tested three databases
1)sql server 2005 (it supports the alter table-working on buttont click event-”executenonquery method”)but it cant be deployed with app.
2)sqlserver database file(similar to CE)

3)sqlserver CE 3.5 (it could be deployed but the alter table command dosent works on it while i run the query at button click event)

how can i overcome the problem to create such dynamic database.
on August 19, 2009 at 8:02 am | Reply ghan

hi pinaldave,

I hope u can help me in this matter. I have 2 servers. One run on sqlserver 2005 express edition and the other one run on sql server 7.0. My application is connected to sql server 7.0 . I need to transfer data from 3 tables when user click a button in my application to sql server 2005 express. table structure for both servers is the same.

my questions are :
1. how to do a script to transfer/update data 3 tables from sql server 7.0 to sql server 2005 edition?

2. How to automate the process like DTS/job in sql server 7.0 . any ideas?

Hope to get solution asap.
Thank in advance.
on August 19, 2009 at 1:07 pm | Reply vijay sharma

i sir
i have a problam.
i need a all date in given month nad year peramerter

like

march -2009 all date
1/3/2009
2/3/2009
———–
———–
31/3/2009
on August 19, 2009 at 7:07 pm | Reply Vikas Anand.

Hello Pinal Dave,

Could you please tell me the exact difference between , Convert() and Cast() functions in Ms-SQL . Except the extra formatting paramter in convert().

I searched lot but,didn’t find satisfactory response.

Please help me to know this.

Thanks in advance.
Vikas Anand.
on August 19, 2009 at 10:47 pm | Reply Brian Tkatch

@Vikas

Except for the third parameter, CAST and CONVERT do the same thing. The help file explains them both in the same entry.
on August 20, 2009 at 9:20 am | Reply Imran Mohammed

@Vikas,

Just to add to what Brian said, CAST is older version of CONVERT. CAST will not be available in new version starting from SQL SERVER 2008 and higher, so we better stay away from them.

Both does the same job. Because Convert is a newly introduced T-SQL reserved Key word, it has some additional functionality, like changing dates to specific format, we call it as styles.

Like Brian suggested, please go through Books Online Once, search for word, Convert. You will find Books Online information to be very helpful.

~ IM.
on August 21, 2009 at 4:00 pm | Reply chirag

THANKS BRIAN, TEJAS

for supports.

but i have laks of rows in a table.

is thare any other way(/query) to dispaly/ get desired result.?
on August 22, 2009 at 1:32 am | Reply Manuel

Hi,

I am new in SQL and have been asked to increment by 2 all year dates in a SQL DB.

That is, the DB has dates ranging from 2005 to 2007. I need to leave everything identical except for the years that should be incremented in 2, to read 2007 to 2009.

There are multiple year fields in the DB, so the idea would be to have something like the Excel Find-Replace that could be applied to the whole SQL DB

Any suggestion is welcomed,

Thanks, Manuel
on August 23, 2009 at 9:04 am | Reply Imran Mohammed

@Manuel.

Your question is not clear.

You have date fields in your database.
Question 1 : Does all date fields have same column name in all tables in a database.

You want to increment dates ranging from what to what ? And what dates should be left untouched.

This can be done. All we need to do is write a dynamic SQL.

Step 1:
Get a list of all date data type fields in a temporary table. In that table you will store, table name and column name that has date field.

Step2:
Using Dynamic SQL you will prepare SQL that will replace a date in the field to a new date,

step3 : You will execute the statement

consider this example below…

create table #example ( eid int, date datetime)

insert into #example values (1, ‘06/30/2008′)

select * from #example

update #example
set date = replace ( date , ‘2008′ , ‘2009′)

select * from #example

Let us know, if you need more help with this.

~ IM.
on August 24, 2009 at 5:06 am | Reply Ryan Pedersen

Is there a place for a “SQL Job” in the Visual Studio for Database Professionals product?

I have a new project up and running GDR R2 and having great success with it but I can’t seem to find a place to position a scheduled job.

Thank you for your assistance.
on August 24, 2009 at 2:27 pm | Reply ashishgilhotra

@ Chirag

Borrow populate script from Tejas and here i think a simple way to get ur results…

DECLARE @test TABLE(Scheme VARCHAR(12), nav_rate NUMERIC(16,4), nav_Date DATETIME)

INSERT INTO @test
SELECT ’s0001′, 11.2354 ,’27-apr-2009′
UNION ALL
SELECT ’s0002′, 9.6547 ,’28-apr-2009′
UNION ALL
SELECT ’s0003′, 46.254 ,’29-apr-2009′
UNION ALL
SELECT ’s0001′, 13.2413 ,’12-may-2009′
UNION ALL
SELECT ’s0002′, 11.6547 ,’24-may-2009′
UNION ALL
SELECT ’s0003′, 49.3712 ,’20-may-2009′
UNION ALL
SELECT ’s0001′, 9.2354 ,’27-mar-2009′
UNION ALL
SELECT ’s0002′, 8.6547 ,’28-mar-2009′
UNION ALL
SELECT ’s0003′, 44.2413 ,’29-mar-2009′

select Scheme,max(nav_rate),max(nav_Date),min(nav_Date) from @test
group by Scheme
on August 25, 2009 at 1:12 am | Reply Krishna

Hi Pinal,

Do you have any article on extracting the schema for partitioned table in SQL Server 2008 Using SSMS 2008. When we extract the schema for partitioned table, it didn’t extract the on the file group (ie. partition scheme )? Do you have any idea on it? In SQL Server 2005, SSMS 2005 extract exactly the way it was lay out on the partition scheme.

Thanks in advance

-Krishna
on August 25, 2009 at 11:38 am | Reply mahtab

hi pinal
would you help me about finding user in sqlserver2005 that connect dba and does some query.
how can i find user and commend that it does.
ofcourse use sp_who but i want find this user what did it do and find its ipaddress.
thanks in advance
on August 25, 2009 at 4:09 pm | Reply Girish

I have some, I need to create 1 records Data, are as follows if required group by also to be added location/customer wise
Data
Location Customer Trn_dt Amount
1 A001 01/01/2009 100
1 A001 01/01/2009 100
1 A001 01/02/2009 100
1 A001 01/03/2009 100
1 A001 01/04/2009 100
1 A001 01/05/2009 100
1 A001 01/06/2009 100
1 A002 01/07/2009 100
1 A002 01/08/2009 100
1 A002 01/09/2009 100
1 A002 01/10/2009 100
1 A001 01/11/2009 100
1 A001 01/12/2009 100

Output Records
Locn Cust jan feb mar apr may jun jul aug sep oct nov dec 1 A001 200 100 100 100 100 100 100 100
1 A002 100 100 100

Please help
on August 25, 2009 at 7:44 pm | Reply Brian Tkatch

@Imran

“CAST will not be available in new version starting from SQL SERVER 2008 and higher, so we better stay away from them.”

Really?

I looked at the documentation for 2008: http://msdn.microsoft.com/en-us/library/ms187928.aspx and i do not see a mentioned of CAST being deprecated. If it is being deprecated i want to change my code to not use it.
on August 25, 2009 at 10:38 pm | Reply Imran Mohammed

@Brian TKatch

You are correct, I don’t know where I got that information from. But today, When I searched online, I could not found any information related to CAST being depreciate in future version.

I apologize for any confusion.

Thanks for correcting me.
Imran.
on August 26, 2009 at 6:08 pm | Reply Gokula Krsihan

Hai Sir.

I have a doubt on transaction.

Is there any way to achive the nested transaction in Sql Server 2005.

Regards,
Gokula Krsihnan R
on August 26, 2009 at 9:22 pm | Reply Deeptiprasad Nayak

Hi Pinal,
I heard about you a lot.
I am interested to learn sql service integration service 2005.
Tell me some nice site names where i can learn it ……
Or some video tutorials or their sites….

Can you please help me?

regards
deepti
on August 27, 2009 at 4:43 am | Reply Rabbu

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
898

+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

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
on August 27, 2009 at 6:10 pm | Reply Brian Tkatch

@Imran

Thanx for getting back on that. I was a little nervous. :)
on August 28, 2009 at 12:05 pm | Reply ashok

hi pinal, i am using sql server 2005

i want to know more about SEC ROLES

and how to find differene between 2 Databases (suppose the 2 database is same, i deleted i colum in one data base i want the deleted column like that)
on August 28, 2009 at 3:46 pm | Reply chirag

@Tejas, Brian

I got the solution for said query …

query should like …

select t.schemecode, max(nav_rate) as MaxNav, min(nav_Rate) as MinNav,
(select nav_date from test where nav_rate =(select max(nav_rate) from test t1 where t.schemecode=t1.schemecode)) as MaxDate ,
(select nav_date from test where nav_rate =(select min(nav_rate) from test t2 where t.schemecode=t2.schemecode)) as MinDate
from test t
group by schemecode

comments are welcome…
on August 28, 2009 at 5:42 pm | Reply Brian Tkatch

@chirag

If it gives the appropriate results within an acceptable amount of time, use it. :)
on August 28, 2009 at 6:18 pm | Reply Manuel

Thanks Imran,

I will dig in the details of the data base and will come back to you.

Manuel
on September 1, 2009 at 4:53 pm | Reply Jeet Thaker

Hello,
CONGRATULATIONS!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Our heartiest blessings to the SQLAuthority baby and all of you…Wishing all of you a very very happy and prosperous life ahead……..

Regards,
Thaker Family.
on September 3, 2009 at 4:45 pm | Reply Srivignesh Jaganathan

Hi,

I already subscribed to your blog, but I want to change my email id which I subscribed. How can I change the email subscription id?

Please advice me.

Thanks & Regards,
J. Srivignesh
on September 3, 2009 at 6:44 pm | Reply Loganathan

hi pinal,

Am using MSSQL Server 2000.I have a record in a table like this :

NAME
——————–
GANESH
Raja Sekar
VIJAY
Vijay Shankar

I want to retrive data from the table only first letter starts with capital letter.. i.e Raja Sekar and Vijay Shankar.. kindly help me..
on September 3, 2009 at 10:00 pm | Reply Steve

Dave,

Wrote a query to pull row data. Want to combine multiple column fields into one field and stack the data like using a carriage return. Not sure how to do this.

Here is the example I have tried with no sucess.

SELECT
ITEMS.QUANTITY AS Quantity,
PACKAGES.PACKAGE1 AS Type,
ITEMS.HM,
ITEMS.ITEMDESC1 + char(13) + ITEMS.ITEMDESC2 + char(13) + ITEMS.ITEMDESC3 + char(13) + ITEMS.ITEMDESC4 + char(13) + ITEMS.ITEMDESC5 AS ITEM,
ITEMS.ITEMGROSS AS Gross,
ITEMS.ITEMTARE AS Tare,
ITEMS.ITEMNET AS Net,
ITEMS.WGT_LABEL,
ITEMS.ACTIVE, ITEMS.SN_ID
FROM ITEMS INNER JOIN
PACKAGES ON ITEMS.PACKAGE_ID = PACKAGES.PACKAGE_ID
WHERE (ITEMS.ACTIVE = ‘True’) AND (ITEMS.SN_ID = ‘32590′)

Thanks
on September 4, 2009 at 12:39 am | Reply Brian Tkatch

@Loganathan

Depending on what you want, this might work:

WHERE
LEFT(col, 1) BETWEEN ‘A’ AND ‘Z’
AND SUBSTRING(col, 2, 1) BETWEEN ‘a’ AND ‘z’
on September 4, 2009 at 12:42 am | Reply Brian Tkatch

@Steve

Do you mean CR or CRLF? On Windows, CRLF is what is usually wanted, so CHAR(13) + CHAR(10)
on September 4, 2009 at 1:24 am | Reply ali

hello <<
I want to ask about how we can do pharmacy(warehouse) bills if the products can be sold with parts , some with
units and some have life date ..
and we should consider that product can be updated so in the future it can't be sold with units and bills can be update…
Thanks
on September 6, 2009 at 1:14 am | Reply sathish

Hi

I have a query like in BI,How to optimize a reports in
ssrs 2005

i need some perfect clarification pls.

Rgds,
sathish.N
on September 6, 2009 at 2:21 pm | Reply Ali

Hi
Mr Pinal Dave
I Have problem in SQL Server 2005
I Have 3 tables for Example tblA , tblB And tblUser
tblA have Relation with tblB and he is perent for tblB
and tblUser have relation with 2 table tblA and tblB
and he is parent for tblA and tblB
when I want to create Relation SQL Server 2005 trow
error with this comment :

Introducing FOREIGN KEY constraint ‘FK_tblB_tblUser’ on table ‘tblB’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

if i Set ‘Cascade Rule’ to ‘NoAction’ and Set ‘Enforce Foriegn Constraint’ to ‘No’ then for this reason i have 2 solve
1- use transaction to update
2- use Trigger to Update

please tel me wich one is better
tblA and tblB grow 1 million record per year

Thank U
plz send your idea to my mail
on September 7, 2009 at 12:59 pm | Reply Jey ganesh

alter procedure ins @ename varchar(25),@key int output
as
insert into dept(empno,ename,ddate) select @key= max(empno),@ename,getdate()from emp

i have following error

Msg 141, Level 15, State 1, Procedure ins, Line 0
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
on September 9, 2009 at 9:53 am | Reply janak

Hi,
Mr. Pinal Dave

I Want to Migrate from Sql Server 2000 to Sql Server 2005 for beter Performace,
But I dont know what steps are required to migrate on Sql Server level and Database level. and what should be take care while migrating .

Please giude me , so I can Get maximum performace.

Regards
Janak
on September 11, 2009 at 9:37 pm | Reply Sharan

Hello,

I have Microsoft SQL 2005 server standard edition in that i’ve one TEST databse,whenever i tried to add huge amount of data in that databse i get a error like,

“Could not allocate space for object “db.TEST” in Table1 because the primary file group is full. Create disc space by deleting unreed files, dropping files in filegroup, adding additional files to the filegroup, or setting autogrouth on for existing file in the filegroup.”

even though i have 40 GB of enough space in my drive.
i think when i exeed 4GB it is giving me an error.
Is there any restriction on database size?
on September 15, 2009 at 1:48 pm | Reply srinivas

How the get the list of database views present in a database
on September 15, 2009 at 5:12 pm | Reply Girish

Request for AdventureWorks Database – Samples
As mentioned tried
First, go to Microsoft Download Center and download SQL2000SampleDb.msi.
Gives Following Error

AccessDenied
Access Denied
2955D539CFB9C2D6
−

ZnCjz7bcuibM41qzigspuLrcPmGh0YwhIBju8oVVvAewZibnSAMXikH79agKKcJY

Please Help
on September 17, 2009 at 9:09 am | Reply pankaj

Hello ,

i would like to have suggestion from you , actually in my current database i am having a rows more than 2 crore but now we want to split this table into multiple table using a field parameter

for example table structure :

Name,POS, CID, Value – data on this is 2 crore

CID will have data 1,2,3,4,5,6,7,8

so i want to split this table into multiple with

tablename_1 = which will contain data for CID = 1

similar way looping through all distinct CID value which is already stored in another table i will extract data , i am able to do this using a SSIS package , but i want to split them either using a technique from my vb.net application , so kindly suggest how i can achive maximum performance and what script should i use.

with best regards
on September 17, 2009 at 9:15 am | Reply Jose

Hi Daniel, How are you?…I hope good, Men, My question is some rarously, ok…
I cant see the Storage folder on SQL 2005 Express…Or this option is enabled on pay versions?…I think no. or i need a SP or Hotfix or Script for view this folder…Thanks for answer my question and regards!…
on September 18, 2009 at 7:39 am | Reply Kalyana

Hi Pinal,
I am replication database in sql server 2005 from oracle using publisher and subscriptions. I had to delete publisher and subscription and recreate as requirements changed. when I try to create the publisher i get following error. I don’t know what to do .

The Oracle server [XXX] is already defined as the Publisher [XXX_2] on the Distributor [XYXYX].[distribution]. Drop the Publisher or drop the public synonym [MSSQLSERVERDISTRIBUTOR].

I appreciate your help in this regard.
on September 18, 2009 at 1:02 pm | Reply Ankit gandhi

Hi i am ankit gandhi.
Below is my proble.
I have one table for calculation of employee leave.
Table Structure
Employee_id
leave_Start_date
leave_end_date

Now I want to calculate total day difference of leave by month.
Mins in January how many leave taken by employee.
But my problem is when leave_start_date =”01/29/2009” and leave_end_date=”02/15/2009” so how to calculate 3 days in January and 16 days in February. And so on as per date changes.
Please give me example to solve my problem.
on September 18, 2009 at 4:26 pm | Reply Satendra

We have a SQL Server cluster that we want to break into two standalone servers. What’s the best way to do this?
on September 23, 2009 at 1:13 pm | Reply Chirag

@Brian Tkatch

regd. your answer to my test query, ‘If it gives the appropriate results within an acceptable amount of time, use it. :)’

but regarding reffence book Sql Server 2005 Bible (by Paul Nielsen) wrote
” So far my testing, I’ve not seen any performance difference between simple subqueries and CTEs; both are compiled to the same query execution plan.”

what is fact regd. CTEs? pls. Explain. if possible, set with example.

thanks

Chirag
on September 23, 2009 at 6:43 pm | Reply Brian Tkatch

@Chirag

Are you asking what a CTE is? A CTE is a Common Table Expression, which is the WITH at the top of a query.

CTE example: WITH example_CTE AS (SELECT ‘chirag’ poster) SELECT poster FROM example_CTE;

subquery example: SELECT poster FROM (SELECT ‘chirag’ poster) example_sub_query;
on September 23, 2009 at 10:22 pm | Reply Ketan

I have one table
inventory that contain number of column.

i want to track column value updation of inventory table in log table that contain following column
Updatefieldname,beforevalue,aftervalue,Updateusername,date,time.

i create group edit for inventory in which updation criteria is selected through wizard.

my problem is maintain log table for this group updation.

i have one problem. i can get all values for log table in trigger.only one problem is that i can not pass updateusername as parameter to trigger.

i also want to ask it is possible to create dynamic cursor or not.
on September 24, 2009 at 5:34 am | Reply kasi

Hi Pinal,

I have 2 tables and i am joining them baesd on CID. For CID 123 i have 4 types of status, these 4 status i have to load into 4 columns as shown below in output.
Table 1:
———-
CID STARTTIME
123 8/19/09 01:11:32 AM
124 8/19/09 01:13:13 AM
125 8/19/09 01:14:32 AM
126 8/19/09 01:15:36 AM

Table 2:
———–

CID status
123 abc
123 abcd
123 abcde
123 abcdef
124 efg
124 defgh
125 ghij
125 ghijk
125 ghijkl
126 jklm

output
——-
CID STARTTIME status1 status2 status3 status4
123 8/19/09 01:11:32 AM abc abcd abcde abcdef
124 8/19/09 01:13:13 AM defg defgh – -
125 8/19/09 01:14:32 AM ghij ghijk ghijkl -
126 8/19/09 01:15:36 AM jklm – - –

Can ypu plz look into this and help me.
Thanks in advance.
on September 24, 2009 at 11:07 am | Reply Chirag

@Brian Tkatch

regd. your answer to my test query, ‘If it gives the appropriate results within an acceptable amount of time, use it. :)’

but regarding reffence book Sql Server 2005 Bible (by Paul Nielsen) wrote
” So far my testing, I’ve not seen any performance difference between simple subqueries and CTEs; both are compiled to the same query execution plan.”

means which will execute faster? with CTE or normal subquery? and which option preffer for query execution?

my question is not regd. with CTE syntax. but regarding comments with your statements and statements given by author (paul nielsen).

pls. clarify

thanks

Chirag
on September 24, 2009 at 6:44 pm | Reply Brian Tkatch

@Chirag

OK, i understand.

The answer? I don’t know.

I use the CTE because it is usually looks cleaner.
on September 24, 2009 at 6:46 pm | Reply Brian Tkatch

@kasi

Are there always exactly 4 statuses?
on September 24, 2009 at 10:17 pm | Reply kasi

Hi I have total 8 statuses just for an example i have mentioned 4 only, if i get more than 8 i have to ignore,
no need to load if we get more than 8 statuses.

please help me on this.
on September 25, 2009 at 6:28 pm | Reply Brian Tkatch

@kasi

This should start you off. The simplest way is with sub-queries. Though, if performance is bad an outer join can be used as well.

This example shows 4, you can expand it to 8 as required. ROW_NUMBER() is ORDERing BY status itself. You might want to change that to a date COLUMN or the like so the ORDER makes sense.

WITH
[Table 1](CID, STARTTIME)
AS
(
SELECT 123, ‘8/19/09 01:11:32 AM’ UNION ALL
SELECT 124, ‘8/19/09 01:13:13 AM’ UNION ALL
SELECT 125, ‘8/19/09 01:14:32 AM’ UNION ALL
SELECT 126, ‘8/19/09 01:15:36 AM’
),
[Table 2] (CID, [status])
AS
(
SELECT 123, ‘abc’ UNION ALL
SELECT 123, ‘abcd’ UNION ALL
SELECT 123, ‘abcde’ UNION ALL
SELECT 123, ‘abcdef’ UNION ALL
SELECT 124, ‘efg’ UNION ALL
SELECT 124, ‘defgh’ UNION ALL
SELECT 125, ‘ghij’ UNION ALL
SELECT 125, ‘ghijk’ UNION ALL
SELECT 125, ‘ghijkl’ UNION ALL
SELECT 126, ‘jklm’
),
[Table 2 With RN](CID, [status], RN)
AS
(
SELECT
CID,
[status],
ROW_NUMBER() OVER(PARTITION BY CID ORDER BY [status])
FROM
[Table 2]
)
SELECT
T1.CID,
T1.STARTTIME,
(SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 1) status1,
(SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 2) status2,
(SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 3) status3,
(SELECT [status] FROM [Table 2 With RN] T2 WHERE T2.CID = T1.CID AND T2.RN = 4) status4
FROM
[Table 1] T1;
on September 26, 2009 at 3:50 pm | Reply Freek Wiekmeijer

Hello Pinal,

First, congratulations on this website. It seems to be one of very few websites dedicated to real problem solving, beyond just the standard SQL language reference you find in so many places.

Yesterday I encountered an SQL programming problem of my own.

Imagine the following table columns:
- timestamp (datetime)
- variable_name (varchar)
- variable_value (varchar)

The table contains data records on multiple variables, and multiple records per variable. For example:

01-01-2009 12:00, “a” , “1.0″
01-01-2009 12:02, “b”, “2.0″
01-01-2009 12:05, “a” , “1.2″
01-01-2009 12:10, “b”, “2.5″

The challenge: I would like to select the most recent value for each variable in the table. In this example, the 12:05 record on variable “a” and the 12:10 record on “b”.

Obviously I could do an individual select on each variable

( SELECT TOP(1) timestamp, variable_name, variable_value
FROM my_table
WHERE variable_name = “a”
ORDER BY timestamp DESC )
+ the same query for variable b.

The problem with this approach is that the table contains data on more than 50 different variables. There has to be a way to combine this in a single “SELECT” statement.

Any suggestions?
on September 27, 2009 at 1:37 am | Reply satish

Hii Pinal,

I have gone through your blog many times and got my doubts cleared instantly, thanks for your support, I having an issues at my client site.

table 1

sno name comy
1 aa xx
2 bb yy
3 cc zz

table 1

sno name comy
11 aa xx
12 dd yy
33 cc zz

no relation between two tables

lookup table
tab1sno tab2sno
1 11
2 12
3 33

Need to jon the 2 tables through lookup table , and need the details from the both, used the full outer join.
on September 28, 2009 at 2:58 pm | Reply B Ravi

I did not get the following statment ….

If you ask any one what is difference between udf and sp
they are saying below statment..

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

Stored procedures also return values and having out put parmeters so why we can not sp in select statment

if is it possible how can we achive .. Please let me know
on September 28, 2009 at 7:19 pm | Reply satish

How to fix this issue?

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
on September 30, 2009 at 12:59 am | Reply Ajay

Hi
I want to Count and display the Same Field Mutiple Times With Different Criteria from one table.

For E.g
I have a data base Anpdata file as Below

FLD1 FLD2
—— ——
X 11X
X 11X
X 12Y
X 13Z
X 13Z
S 11X
S 12Y
S 11X
S 13Z
V 12Y
V 12Y
V 11X
V 11X
V 13Z
V 13Z

Resultant must be as below

FLD1 11X 12Y 13Z
X 2 1 2
S 2 1 1
V 2 2 2

Is it possible via sql?
Here is my qry for 1 criteria

SELECT FLD1, Count(*) as 11X from ANPDATA where X = ‘11X’ and S = ‘ ‘ and V = ‘ ‘ Group by ANPDATA

I need a query to display ALL 3 Criteria in one qry.
Please help.

Thanks
Ajay
on September 30, 2009 at 6:46 pm | Reply Brian Tkatch

@Freek Wiekmeijer

Take a look at using something like

MAX(variable_value) OVER(PARTITION BY variable_name ORDER BY timestamp DESC)
on September 30, 2009 at 6:53 pm | Reply Brian Tkatch

@satish

Is this what you want?

WITH
[table 1 a](sno, [name], comy)
AS
(
SELECT 1, ‘aa’, ‘xx’ UNION ALL
SELECT 2, ‘bb’, ‘yy’ UNION ALL
SELECT 3, ‘cc’, ‘zz’
),
[table 1 b](sno, [name], comy)
AS
(
SELECT 11, ‘aa’, ‘xx’ UNION ALL
SELECT 12, ‘dd’, ‘yy’ UNION ALL
SELECT 33, ‘cc’, ‘zz’
),
[lookup table](tab1sno, tab2sno)
AS
(
SELECT 1, 11 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 3, 33
)
SELECT
[table 1 a].sno,
[table 1 a].[name],
[table 1 a].comy,
[table 1 b].sno,
[table 1 b].[name],
[table 1 b].comy
FROM
[table 1 a]
FULL OUTER JOIN
[lookup table]
ON
[table 1 a].sno = [lookup table].tab1sno
FULL OUTER JOIN
[table 1 b]
ON
[table 1 b].sno = [lookup table].tab2sno;
on September 30, 2009 at 7:01 pm | Reply Brian Tkatch

@Ajay

How’s this?

WITH
Anpdata(FLD1, FLD2)
AS
(
SELECT ‘X’, ‘11X’ UNION ALL
SELECT ‘X’, ‘11X’ UNION ALL
SELECT ‘X’, ‘12Y’ UNION ALL
SELECT ‘X’, ‘13Z’ UNION ALL
SELECT ‘X’, ‘13Z’ UNION ALL
SELECT ‘S’, ‘11X’ UNION ALL
SELECT ‘S’, ‘12Y’ UNION ALL
SELECT ‘S’, ‘11X’ UNION ALL
SELECT ‘S’, ‘13Z’ UNION ALL
SELECT ‘V’, ‘12Y’ UNION ALL
SELECT ‘V’, ‘12Y’ UNION ALL
SELECT ‘V’, ‘11X’ UNION ALL
SELECT ‘V’, ‘11X’ UNION ALL
SELECT ‘V’, ‘13Z’ UNION ALL
SELECT ‘V’, ‘13Z’
)
SELECT
FLD1,
COUNT(CASE FLD2 WHEN ‘11X’ THEN 1 END) [11X],
COUNT(CASE FLD2 WHEN ‘12Y’ THEN 1 END) [12Y],
COUNT(CASE FLD2 WHEN ‘13Z’ THEN 1 END) [13Z]
FROM
Anpdata
GROUP BY
FLD1;
on September 30, 2009 at 7:27 pm | Reply Kaz

Hi Dave,
Whenever i got stuck in any thing of SQL server. I tried it to find out in your blog.. This is first time i am writing to you. So, hopfully i ll get reply as well.
I have date of birth and date of death in my talbes. i try to find exact age (like day, month and years) of my client. I used this sql but even it is giving wrong years.
DATEDIFF(dd, dob, dod) / 365 as “Age”
Can you please guide me. how can i get exact age of my client
Regard
Kaz
on October 1, 2009 at 12:37 am | Reply Ajay

Brian Tkatch
I am confused

Here is my Query for similar example

SELECT ANENVI, count(*) FROM anpccdtl WHERE ANCCS = ‘D’ and ANCCZ = ‘11x’ and ANCCS1 = ‘ ‘ and ANCCZ1 = ‘ ‘ and ANCCS2 = ‘ ‘ and ANCCZ2 = ‘ ‘ and ANCCS3 = ‘ ‘ and ANCCZ3 = ‘ ‘ and ANCCS4 = ‘ ‘ and ANCCZ4 = ‘ ‘ GROUP BY ANENVI
UINON
SELECT ANENVI, count(*) FROM anpccdtl WHERE ANCCS = ‘D’ and ANCCZ = ‘17Y’ and ANCCS1 = ‘ ‘ and ANCCZ1 = ‘ ‘ and ANCCS2 = ‘ ‘ and ANCCZ2 = ‘ ‘ and ANCCS3 = ‘ ‘ and ANCCZ3 = ‘ ‘ and ANCCS4 = ‘ ‘ and ANCCZ4 = ‘ ‘

The above Query dispays one below the other in same row.
But the problem is, I need to display the field ANCCZ in colums
as sown .i,e ANCZZ is one filed Have differnt records combinations,My Question is can Udisplay all three rcds in columns

ENVI ANCCZ ANCCZ ANCCZ
11x 17Y 14x
——————————————–
X 2 1 0
V 0 2 1
S 2 2 0

My data
———–
ANENVI ANCCS ANCCZ ……………1 2 3 4
——————————–
X D 11x
X D 11x
V D 17Y
V D 17Y
S D 11 x
S D 11 x
S D 17Y
S D 17Y
X D 17Y
V D 14X

Thanks You
Ajay
on October 1, 2009 at 6:24 pm | Reply Brian Tkatch

@Ajay

What do you not understand about my solution?

All i have done is use COUNT() with a CASE statement. If the code matches, we count it. Otherwise, the CASE statement turns it into a NULL, and COUNT() ignores it.
on October 1, 2009 at 8:20 pm | Reply ben

Hello Pinal Dave,

I am running a sql2008 server with IIS and a sharepoint site. The admin changed the IP address and some of my apps stopped working. Once I tracked down the IP address references I was good to go.

The problem is with a sharepoint site I have now. I cant find anywhere that makes a reference to an IP. Should I delete the sp site and reinstall? ( I know if the server name is changed you have to do that)

If you can think of somewhere that the IP is referenced that would be great!!

thanks
ben
on October 2, 2009 at 1:58 am | Reply Nick

I have a table like as below:
Table-A
———-
Bill_Number Char(5)
Bill_Seq Char(3)
Line_Status Char(15)
Bill_Status Char(15)

The sample records would be like as below:
Bill_Number Bill_Seq Line_Status Bill_Status
11111 001 New
11111 002 Ready

22222 001 Rejected
22222 002 Processing

33333 001 Rejected
33333 002 Close

Now the above should be updated as below:
Bill_Number Bill_Seq Line_Status Bill_Status
11111 001 New Ready
11111 002 Ready Ready

22222 001 Rejected Processing
22222 002 Processing Processing

33333 001 Rejected Close
33333 002 Close Close

The condition is:
1. If the line items for the bill has New and Ready change the Bill_Status to Ready.
2. If the line items for the bill has Rejected and Processing change the Bill_Status to Processing.
3. If the line items for the bill has Rejected and Close change the Bill_Status to Close.

How can this be achieved using sql query. Please advice.
on October 2, 2009 at 9:21 am | Reply Imran Mohammed

@Nick,

I Initially tried doing this with a select statement, but I could not figure it out, so I chose while loop to loop through all records and update our main table, I am sure, there must another (simple) way of doing this, but this is what I have came up with…

Check if this could help

– Script starts here.

CREATE Table A
(Bill_Number Char(5)
,Bill_Seq Char(3)
,Line_Status Char(15)
,Bill_Status Char(15)
)

Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 11111 ,001 ,’New’)
Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 11111 ,002 ,’Ready’)

Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 22222 ,001 ,’Rejected’)
Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 22222 ,002 ,’Processing’)

Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 33333 ,001, ‘Rejected’)
Insert into A (Bill_Number, Bill_Seq, Line_Status) Values ( 33333 ,002 ,’Close’)

SET NOCOUNT ON
Declare @table1 table ( Bill_Number varchar(5), Line_Status varchar(15))
Declare @table2 table ( Ident int Identity , Bill_Number varchar(5))

insert into @table1
select Bill_Number
, Line_Status
from A
order by Bill_Number ASC

insert into @table2 (Bill_Number)
select distinct Bill_Number
from A

declare @status1 varchar(15)
declare @status2 varchar(15)

declare @count int
declare @string varchar (32)
DECLARE @Bill_Number INT

Set @count =1
while @count < = ( select Count (*) from @table2)
begin

Set @status1 = NULL
Set @status2 = NULL
SELECT @Bill_Number = Bill_Number
FROM @TABLE2
WHERE Ident = @Count

set @string = ''
select @string = @string + case when @string = '' then '' else ',' end +LTRIM(RTRIM(Line_Status))
FROM
( select
Line_Status
from @table2 T1
join @table1 T2 on T1.Bill_Number = T2.Bill_Number
where T2.Bill_Number = @Bill_Number
) X
group by Line_Status

SELECT @status1 = SUBSTRING ( @string , 1, CHARINDEX( ',', @string)-1)
,@status2 = SUBSTRING ( @string , CHARINDEX( ',', @string)+1 , LEN (@STRING) – (CHARINDEX( ',', @string)))

IF (@status1 = 'New' AND @status2 = 'Ready') OR (@status2 = 'New' AND @status1 = 'Ready')
UPDATE A
Set Bill_Status = 'Ready'
WHERE Bill_Number = @Bill_Number
and Line_Status in ('New', 'Ready')

IF (@status1 = 'Rejected' AND @status2 = 'Processing') OR (@status2 = 'Rejected' AND @status1 = 'Processing')
UPDATE A
Set Bill_Status = 'Processing'
WHERE Bill_Number = @Bill_Number
and Line_Status in ('Rejected', 'Processing')

IF (@status1 = 'Rejected' AND @status2 = 'Close') OR (@status2 = 'Rejected' AND @status1 = 'Close')
UPDATE A
Set Bill_Status = 'Close'
WHERE Bill_Number = @Bill_Number
and Line_Status in ('Rejected', 'Close')

Set @Count = @Count+1
End

SET NOCOUNT OFF

GO

select * from A

If any one else has a better solution, please post it, it would be helpful for every one including me.

~ IM.
on October 2, 2009 at 6:23 pm | Reply Amit

Hello Sir,
It’s first time that i am writing a message to you.
i have some problem related with database queries actually
i hav one excel sheet with me and i hav to import that sheet in sqlserever2000 The query i write is
“select * INTO ” + tablename + ” from OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=” + dsn + “‘, ‘SELECT * FROM [Sheet1$]‘) ” ok
its working correctly but the problem is my requirement is to import only some fields not all fields of sheet1 and i dont knw where to write the specific fields name.so can you please solve my problem.

It will be great help of urs.Thanks
Regards Amit Shrivas
on October 4, 2009 at 11:32 am | Reply Jean Baptiste K.

Dear Pinal,

I am looking where I can dowload AdventureWorks Report Samples for SQL 2005 Express. I went to the site.
But there is no way I can download report samples for sql server 2005. It’s always giving only download to SQL 2008 server reporting sample. Please could you indicate alternate location where I can dowload AdventureWorks Report Samples for SQL 2005 Express.

Best regards, Jean Baptiste
on October 4, 2009 at 2:49 pm | Reply amee kalaria

plz help me i seriously need your help
i have to develop an ’sql query cost based optimizer’ as my BE(IT) project and i am confused how to go about with it plz help me.it takes into consideration 1.sql query transformation 2.execution plan selection 3.cost estimation
on October 5, 2009 at 4:39 pm | Reply Hemal Bhatt

Respected Sir,

I have completed PGDCA & MScIT and I am having a good expose for Microsoft technology i am also having experience about 2 years in this Developing field.

but now i want to earn certain certificate like MCP or other Microsoft certification so i need your advice about

1. which certificate is good for experienced developer and 2. Is there any special vacancy that need only person with these Microsoft certification … ?

3. I enjoyed with ASP.Net Custom Server controls developing so is there any special requirement like
“ASP.Net Server control developer ”

so my main confusion is to how can i defined goal for specialization.

My contact No is :

Thanks With Regards,
Hemal Bhatt
Elitech Systems Pvt. Ltd. – Ahmedabad
on October 6, 2009 at 4:27 am | Reply Irene

Please i am trying to populate a single colume that is within a table of 18 coulmns. I have been trying to foloow your steps but it appears im not getting it right.

Here is my query

INSERT INTO parceltable (gid, objectid, parcelid, year, allocation, parcelowne, sex__m_f_, parceltype, parcelarea, parcelar_1, parcelperi, x_cord, y_cord, id, clases, hectares, crop_type, geom)
VALUES (”,1),
(‘ ‘,2),
(‘ ‘,3),
(‘ ‘,4),
(‘ ‘,5),
(‘Irene’,6),
(‘ ‘,7),
(‘ ‘,8),
(‘ ‘,9),
(‘ ‘,10),
(‘ ‘,11),
(‘ ‘,12),
(‘ ‘,13),
(‘ ‘,14),
(‘ ‘,15),
(‘ ‘,16),
(‘ ‘,17),
(‘ ‘,18)

This is the error i am getting.
ERROR: INSERT has more target columns than expressions
LINE 1: INSERT INTO parceltable (gid, objectid, parcelid, year, allo…
on October 8, 2009 at 1:07 pm | Reply Zakie

Hi!
I’ve downloaded installation files for:-

1. SQL Management Studio_x64_ENU when i try to intall i got error msg says application is not support Win 32 and

2. SQL server 2008 when am trying to install i got error says application is not support type of processer so what gonna do?

Plz advice or give me another option, i need help

Regards,
on October 9, 2009 at 11:56 am | Reply Prem

hello sir ,
I am facing one problem in Mysql . it’s a simple where condition but thought i am not able sort it out …so i need your help.

I have one table suppose sample(id,Name);

id Name
1 abc’s home
2 xyz’s home

n i want to fire query like this

select id from sample where name=’xyz’s home’;

but after running it giving error…

any solution for this.

thanks in advance !!
on October 10, 2009 at 5:40 pm | Reply DS

Hi Pinal,

I am using Soundex keyword in my sql query which is used for free text search. So I can get the related words also like if I search for butterfly then I will also get results of butterflies.

But now I get one strange issue. When i search for ‘footwear’ word it also gives results of ‘fedora’ word. I have checked soundex code for both words and it is same.

Can you please help me to sort it out? I just want to search related words like google do.
on October 11, 2009 at 10:14 am | Reply Imran Mohammed

@Perm

select * from [sample]
where name = ‘xyz’’s home’

~ IM
on October 12, 2009 at 2:22 pm | Reply Ashutosh Katariya

hi pinal

my query such like that :

table name : amount
field name : id and amount

i run this query :

select sum(amount) as amt1 from amount where amount>0
union all
select sum(amount) as amt2 from amount where amount<0

Result :
amt
-21000
31200

but i want this type of result :
amt1 amt2
-21000 31200

i hope this is enough to getting idea, what i want.
please help me.

thanks in advance
on October 13, 2009 at 2:35 am | Reply Brian Tkatch

@Irene

Which COLUMNs are you trying to UPDATE?
on October 13, 2009 at 2:36 am | Reply Brian Tkatch

@Ashutosh Katariya

select sum(case when amount > 0 then amount else 0 end) as amt1 sum(case when amount 0
on October 13, 2009 at 7:32 pm | Reply sohil

hi 2 all,
I have a problem with <>.
<> this value is in the database name xyz, table customer, column telephone type.
when I fetch this table into the .xls file It will automaticaly convert into combobox in xls file.I want to replace this value with ” “(space). Note: I have a 3 tables. I wrote join query with this field. …………………Please help me.?????????????
on October 14, 2009 at 2:59 am | Reply robin

how to get all database name from a sql server without run sql script, but use some command (run on DOS) or utility like :
“osql -L > .\server.list”
on October 14, 2009 at 11:07 am | Reply Imran Mohammed

@sohil.

Make you use of case function

Select case when [telephone type] = ” then ‘ ‘ else [telephone type] End [telephone type]
from xyz..customer

Try this, Should work.

~ IM.
on October 14, 2009 at 12:28 pm | Reply sohil

@ Imran,

thanks dear It is working,and I use like replace(telephonetype,’<>,”)
and it is also working.
thanx again.
on October 14, 2009 at 12:37 pm | Reply sohil

hi 2 all,
I have a new problem, i have a dateadded field which is getdate() type like 2009-09-09 00:00:00.000. but when I fetch that in my .xls file it will convert like 09-sep-2009 in my .xls file.
on October 14, 2009 at 3:00 pm | Reply Sunil

Hi sir,

Now days i m putting more interesting in the database professional. But mean while i m working in the both the platform as Asp.NET and sql server.

How i can make the turn my carrier to change into the sql server platform completely ..
on October 14, 2009 at 4:05 pm | Reply chirag

Hi,

in sql, i want to add constraint ( foreign key ) in existing table with existing column. but with alter table… add constraint.. syntax give error. Pls. help in this matter.
thanx in advance.
on October 14, 2009 at 8:16 pm | Reply Like to Like SSRS Charts

Hi, I need HELP from you..

The One kind of Following Scenario is :

Table in Sql server Database

Fiscal year Sales
———— ——-
2006-2007 100

2007-2008 50

2008-2009 200

My Need is SSRS Charts from CUBES :

I want column Chart for like to like sales..

How to achieve this one

Plz help me

Thanks in advance …
on October 15, 2009 at 1:20 am | Reply Umakant

Hi All,

I need a help in following scenario.
I have to read data from a SQL Server database (for ex. XYZ_DB). XYZ_DB is getting restored from some other backup every night. There is no fix time of XYZ_DB restore.
So my question is how do I know XYZ_DB restore got completed and I can start pulling data from it.

I don’t have any control on the system which is restoring the backup file on XYZ_DB. So I cannot expect any kind of notification from that system, so that I can start my SQL job on XYZ_DB to read data. Whatever I have to do, I have to do it from my end.

Pls let me know if any one have any idea on this.
on October 15, 2009 at 6:05 am | Reply Imran Mohammed

@Umakant,

You can schedule a job, that will check for the database in online status.

Schedule this job to run every minute, and check the status of that database, by querying sysdatabases view look for status online.

if there exists a record in this view with this database name and status online, you can then start your main job which will read data out of your newly restored database.

In between the two jobs, you could perform multiple steps by which you don’t repeat duplicate steps once database is detected to be online.

If you need more help, please let us know.

~ IM.
on October 15, 2009 at 6:10 am | Reply Imran Mohammed

@Chirag

Check Books online, there are plenty of examples for almost all cases of ALTER TABLE, follow one of the example.

Also, next time you ask question, please post your script and error message, it will be helpful for us to identify issue.

~ IM.
on October 15, 2009 at 5:17 pm | Reply Akshay

Does SQL Server 2005 has any facility like %ROWTYPE and %TYPE as in Oracle. I am fetching number of fields in a cursor and I need all those fields to be used in an INSERT query; how do I work with this without declaring variables as the number of fields in the Cursor query.

Please help
on October 16, 2009 at 4:41 pm | Reply feroz

i want to select following records using this query

select collect.File_No as FileNo,collect.Recept_No as ReceptNo, collect.Coll As Amount from collect where (col_date=’10/16/2009′)and (collect.file_no=’P0816′ or collect.file_no=’C7756′ or collect.file_no=’P0839′)union all select hirer.File_No as FileNo,0 as ReceptNo, 0 As Amount from hirer where(hirer.file_no=’P0816′ or hirer.file_no=’C7756′ or hirer.file_no=’P0839′)

but it also generates duplicate record which must not b there
on October 16, 2009 at 8:09 pm | Reply Brian Tkatch

@feroz

Add DISTINCT to the query (or just change UNION ALL to UNION)

BTW, (collect.file_no=’P0816′ or collect.file_no=’C7756′ or collect.file_no=’P0839′) can be a simple: collect.file_no IN (’P0816′, ’C7756′, ’P0839′)
on October 18, 2009 at 5:03 pm | Reply shine

Hi,
What is the difference between two string queries.

1. ‘Create Table #Temp’+@RandomNumber+’(fields)’

2.Declare @TableName Varchar(30)
SET @TableName =’#Temp’+@RandomNumber
‘Create Table ‘+@TableName+’(fields)’

Replacing all the references to the table name with this variable .

And first one taking too much time to execute.

Thanks.
on October 19, 2009 at 11:55 am | Reply feroz

i replaced union all to union and used distinct but the duplicate rows are still there
on October 20, 2009 at 4:18 am | Reply Laurie Clemens

HI There, I have a question for you? I have an individual who is wanting to go into the sql 2005 cert. track but he also wants to learn scripting languages and VB. Where would those fall into the track? If he was starting with database and access, the going into Microsoft IT, would he take the scripting language before of after the SQL?/

He is a programmer from years back and knows mainframe language but hasn’t had much hands on with the pc. so his skillset is outdated, but he knows programming. He has been going to university here, but is thinking of switching to my school which offer certification. Any advice you would have is greatly appreciated.
on October 20, 2009 at 10:07 pm | Reply Pratik Kantharia

Hi Pinal,

I am facing some problem in LINQ.

There are three tables, let’s say ‘Profile’, ‘Test1′ & ‘Test2′. (There are other tables, too).

Profile – Id, Username, Email, Password

Test1 – Id, Title, Description

Test2 – ProfileId, Test1Id, Percentage

Test2.ProfileId & Test2.Test1Id are definned as a composite primary key and foreign key reference to relevant tables.

Test2 table has more than 9000000(9 million) records.

Now I want to implement paging, so I am using .count() method of IQuerable object, but it is giving me following error.

System.Data.SqlClient.SqlException

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

You can look at code in following post.
http://forums.asp.net/t/1483028.aspx

I’m not getting idea what to do further.

Please help me out of this.

Thanks in advance.
on October 21, 2009 at 5:11 am | Reply robin

Hi all
Can someone help please ????????????

how to get all database name from a sql server without run sql script, but use some command or utility like
“osql -L > .\server.list” run on DOS will list all SQLserver on your network
on October 22, 2009 at 8:55 am | Reply Radha

Hi,
I am working on Dataconversion project. I want to validate the source data comes from Sqlserver 2005 and destination data from Oracle. I used to work on VB6 to .net on sql server conversion project. When I was validating the source and destination data I have written as one query for source and destination tables and gave the different server names on joins. My question is Can we write in query the Sql server data and oracle data? or Do we need to different queries for comparing the source and destination?
on October 22, 2009 at 12:08 pm | Reply Avani

Hi all,
I have written one SP, which takes “ItemName” as input and returns “ItemGroup” of that Item.THese both fields are in “Item_master” table.

when I execute this SP from SQL Server, I want to execute it for each n every “itemname” for which there is entry in table called “Sales_detail”.

so for executing this SP, I hv written code like below, but it goes in infinite loop, why is it so?

declare @itemgroup varchar(100)
declare @itemnm varchar(100)
while (exists(select itemname from sales_detail))
begin
select @itemnm=itemname from sales_detail
if @itemnm is null
break
else
continue
exec dbo.getgroup @itemnm,@itemgroup out
select @itemgroup
end
on October 22, 2009 at 2:20 pm | Reply Ashutosh Katariya

hi,

what is the difference between sysobjects and sys.objects

E.x
1. select * from sysobjects
2. select * from sys.objects

it will return same affected rows. but some column id different.

can u pls explain with technical reson.

Thanks in advance.
on October 22, 2009 at 3:46 pm | Reply GiRi

Hi Pinal,
I am working as software engineer in Microsoft technologies working in asp.net.
I want to become SQL Server DBA for this specify me a good institute where can i learn thoroughly in ahmedabad or in gandhinagar.

thanks & Regards,
Giridhar
on October 24, 2009 at 1:56 pm | Reply Abhishek

Hi Pinal can you please help me on below issue, I want to implement Full text search with join query but faced below problem

i want to use full text search in sql server 2005.so i am facing the problems which is as below.

1) i am using 6 tables.

i)Product (ProductID primary key,Name,Description,Summary,ImageFileNameOverride)
ii)Maufacturer(ManufacturerID,Name,Description)
iii)Category(CategoryID,Name,Description)
iv)ProductCategory(ProductID,CategoryID)
v)ProductManufacturer(ProductID,ManufacturerID)
vi)ProductVariant(ProductID,VariantID,SalePrice)

i want this result
ProductID,ProductName,ManufacturerName,CategoryName,ProductSummary,SalePrice,ImageFileNameOverride

2) one product have multiple category.
3) while i create a view the index in not possible because there was an error
“The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.ViewSearch’ and the index name ‘IX_ViewSearch’. The duplicate key value is (3).”

4)i have tried also with temporary table but the it gave me an error that #temp is not available.
5)when i change the datatype in view because index is not perform on ntext,image,text and xml type so i have converted
to varchar(max) but still it gave me an error that there may be a usage of image,ntext,text or xml value.
6)and when perform the below query
SELECT product.ProductID,product.Name,product.Description,SalePrice,Product.ImageFilenameOverride FROM product,ProductVariant,Category WHERE FreeText((Category.Name,product.Name,product.description,product.summary,product.SETitle,product.SEDescription),’”Apple”‘) AND Product.ProductID=ProductVariant.ProductID
it gave me an error that Fulltext predicate references columns from two different tables or indexed views ‘Category’ and ‘product’ which is not allowed.

so please give me a solution as soon as possible

thanking you
on October 26, 2009 at 9:55 am | Reply june

Hi pinal,
wondering if you could post something regarding credit card encryption and decryption like — ways to implement encryp/decrpttion,how they work and how to secure them.

thanx in advance.
on October 26, 2009 at 10:29 am | Reply Dev...

hi pinal….

I used the property called ‘WITH ENCRYPTION’ (while creating a procedure sp_testProc ) in order to make sure that my juniors who use that procedure will not able to edit it & modify it but unfortunately i am unable to get back the script of the same procedure sp_testProc…….. can I get the script or else I need to write it again

If u have any other option of encryption also U can suggest me please
on October 26, 2009 at 11:51 am | Reply Avani

Hi Pinal,
I want to learn how to create simple SP and executing them directly from SQL Server as well as from VB.NET code, if u can show me some example or else if u hv written some E-books regarding this please tell me.

Thank you.
on October 27, 2009 at 5:13 pm | Reply Rishikesh Tiwari

Hi Pinal,
Can i use Truncate Event in DDL trigger ?
Is there any way to restrict a table from being truncated?
on October 29, 2009 at 4:10 am | Reply Nick

Table 1
——-
LANG_VALUE
LANG_DESC

Table 2
——-
EmpId
LANG1
LANG2
LANG3
LANG4
LANG5
LANG6
It looks like as below:

Table 1
——-

LANGVALUE LANGDESC
——————-
SPAN SPANISH
GERM GERMAN

Table 2
——-
EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6
—————————————–
1 SPAN NULL NULL NULL NULL NULL
2 GERM SPAN NULL NULL NULL NULL
3 GERM NULL NULL NULL NULL NULL
Expected Result:

EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6
—————————————–
1 SPANISH NULL …
2 GERMAN SPANISH NULL…
3 GERMAN NULL…
How to do this in sql query
on October 29, 2009 at 7:16 pm | Reply Brian Tkatch

@Nick, this is a standard join:

SELECT
[Table 2].EmpId,
T1.LANG_DESC,
T2.LANG_DESC,
T3.LANG_DESC,
T4.LANG_DESC,
T5.LANG_DESC,
T6.LANG_DESC
FROM
[Table 2],
[Table 1] T1,
[Table 1] T2,
[Table 1] T3,
[Table 1] T4,
[Table 1] T5,
[Table 1] T6
WHERE
T1.LANG_VALUE = [Table 2].LANG1
AND T2.LANG_VALUE = [Table 2].LANG2
AND T3.LANG_VALUE = [Table 2].LANG3
AND T4.LANG_VALUE = [Table 2].LANG4
AND T5.LANG_VALUE = [Table 2].LANG5
AND T6.LANG_VALUE = [Table 2].LANG6;
on October 29, 2009 at 9:03 pm | Reply Satheesh

hi pinal

I got a scenario like this

Say there is

1)students table (tbl_students)

with fields
1)student_name
2)student_id

2)their marks table (tbl_marks)
with fields
1)student_id
2)subject_id
3)marks (out of 100)

I need to get all students with marks 80 and 90. I mean
not in (80,90) but those who got both 80 and 90 no matter what the subject is

And once i got this result next i want is to get all students with marks 80 and 90 but without 70 for a subject X.

i mean those who got both 80 and 90 but havnt scored 70 yet for the subject X .Can u please help this.

thanks
on October 29, 2009 at 9:07 pm | Reply Satheesh

hi pinal in the above scenario what will be the case when its an institution where there are thousands of students and thier marks .Could you please help me and reply at my email id.

thanks
on October 29, 2009 at 10:39 pm | Reply Brian Tkatch

@Satheesh

For both 80 and 90:

SELECT
student_id
FROM
tbl_marks
WHERE
marks IN (80, 90)
GROUP BY
student_id
HAVING
COUNT(DISTINCT marks) >= 2;

For both 80 and 90 but not 70:

SELECT
student_id
FROM
tbl_marks
WHERE
marks IN (70, 80, 90)
GROUP BY
student_id
HAVING
COUNT(DISTINCT NULLIF(marks, 70)) >= 2
AND COUNT(CASE WHEN marks = 70 THEN 1 END) = 0;
on October 30, 2009 at 4:52 am | Reply vikas

How we can check which application or process is updating our database.
on October 30, 2009 at 12:23 pm | Reply Satheesh

thanks brian , kudos
it worked for me.
on October 31, 2009 at 11:38 am | Reply Rajesh

Dear Pinal,

I am developing FAS (Financial Accounting System) with .NET and Sql 2005. I have plan for a single database for multiple year (Is it worth ?). What should be the best method to calculate Balance of each account ? There should be heavy amount of data in table.

The scenario like….I have a table with all the debit credit transactions which has any number of rows. So when i retrieve balance of a single party then i have to scan entire table. so what is the best method to do that ?

Should i make another table which records partycode,financialyear and debit credit balance ? (Single row for single party). so that i can retrieve from that table.

If i make different database for different financial year (after year ending), how can i affect balance in current financial year if i change any voucher or transaction in previous year ?
on October 31, 2009 at 1:16 pm | Reply feroz

Dear Pinal,

i want to select 3 columns but on three different conditions

how can i query that as my database structure is

create table HIRER(
DIST_CODE Char(9),
BR_CODE Char(9),
EMP_CODE Char(9),
FILE_NO Char(9) primary key,
HIRER_NAME Char(51),
DISB_TYPE Char(15),
STARTING Datetime,
INSTALMENT Numeric,
MODE Char(1),
CLOSING Datetime,
OVERDUE Numeric))
go
create table COLLECT
( FILE_NO Char(9),
RECEPT_NO VARCHAR(60),
COLL NUMERIC,
col_date datetime,
coll_type varchar(10),
fin_year varchar(9)
constraint uk_collect unique (recept_no,fin_year))

and my query is

select branch.br_name as Branch,hirer.br_code,sum(R.s1+A.s2) as Actual,s.coll
from
(select br_code,sum(hirer.instalment) as S1 from hirer inner join collect on hirer.file_no=collect.file_no where hirer.closing>’5/27/09′group by br_code) R,
(select br_code,sum(hirer.overdue) as S2 from hirer inner join collect on hirer.file_no=collect.file_no where hirer.closing<'5/27/09' group by br_code) A,
(select br_code,sum(collect.coll) as Coll from hirer inner join collect on hirer.file_no=collect.file_no where collect.col_date between '1/1/2009' and '10/31/2009' group by br_code ) S,
branch inner join hirer on branch.br_code=hirer.br_code inner join collect on hirer.file_no=collect.file_no
where collect.col_date between '1/1/2009' and '10/31/2009'group by br_name,hirer.br_code,s.coll

actually i want to generate a report to find branch wise collection and actual amount we have to collect from customers

please solve my problem
on October 31, 2009 at 2:14 pm | Reply rakesh

Hello ALL,

I have a question related to update statment.

we have a table name EMP in this table there is a collumn name Status. The Status have 1 and 0 in this callumn we need to update all 0 in 1 and all 1 in 0.
But in a single statment.

If any one knows this answer please send it to me at rakesh.cdac@yahoo.com

Thanks
Rakesh Kumar
on October 31, 2009 at 5:48 pm | Reply Tejas Shah

Hi Rakesh,

You can do this by update statement.

SQL will only update values after statement execution, so you can write it in update statament,.

Let see example:

DECLARE @T AS TABLE(
ID INT IDENTITY,
A BIT,
B BIT
)

INSERT INTO @T(A,B) VALUES(0,1)
INSERT INTO @T(A,B) VALUES(0,1)
INSERT INTO @T(A,B) VALUES(1,0)
INSERT INTO @T(A,B) VALUES(1,0)

SELECT * FROM @T

UPDATE @t
SET A = B,
B = A

SELECT * FROM @T

Thanks,
Tejas
on November 1, 2009 at 12:49 am | Reply victor

SQL SRVR 2000

Using DTS – Import delimited file into temp table. I run a validation script against temp table to check for valid data in a column.

For a column that should contain data – an IS NULL test.
For a column that should NOT contain data – and IS NOT NULL test,

However, for a column that is expected to be NULL, IF there is a space, it returns a false positive for an IS NOT NULL Test.

I have looked everywhere to find a way to use SQL to tell me that what is being reported is a space – not data.

Please help.
on November 2, 2009 at 8:58 am | Reply Chirag

Hi Rakesh,

You can do this simple, sort & sweet.

update emp set status = ((status-1) * (-1))
on November 2, 2009 at 9:03 am | Reply Chirag

Hi Rakesh,

littel change, if req.

update emp set status = ((status-1) * (-1))

where status in (0,1)

(if status have different value then 0,1)
on November 3, 2009 at 2:43 am | Reply Tim Carlisle

I have a production SQL Server 2000 instance that I back up (databases) daily and restore to a failover SQL server instance. Both SQL servers have inherited their default (host server)names. I can backup my production databases (SQL Server 2000) and restore them to the failover server (SQL Server 2005) and all databases work fine, and the Dot Net applications that connect to each of them run fine.

When I attempt to back up a database from the failover instance, I receive the following error:

Backup failed for Server ‘Sandbox’ (Microsoft.SqlServer.Smo)

Additional information:

System.Data.SqlClient.SqlError: THe backup of the filegroup “sysft_Name” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo).

I managed to find some article a while back that indicated that this was catalog related (system files thinking that the server instance is still the production server) and that deleteing the catalog and recreating it would solve the problem, but I don’t know how to do that. Has anyone in this forum run into this before/ It is especially pressing, because I want to upgrade my production SQL server, and need to know that I can backup a restored database from a server that was named differently.

The master database backs up ok, it is just the other database that throws the error.
on November 3, 2009 at 3:25 am | Reply Preet Patel

Hi Pinal,

I wanted to fetch data from DB2 for reporting. I could not decide which tools is better. SSRS or Crystal Reports. Your help will be appreciated.

Thanks,
Preet Patel
on November 3, 2009 at 4:03 pm | Reply Nitin Bhojwani

Hi Pinal,

I like your website and your answers to questions of others.

I was wondering, if you can help me on a small issue.

I am trying to create a stored procedure to populate a table in my database with data from tables in another database. So I created synonyms of those tables in my database and added them in the stored procedure.

Now I want every user, whom I give execute permission to be able to access the underlying tables in another database, without specifically giving them SELECT access to those tables.

Do you think there is a way I can do this.

Thanks,

Nitin
on November 3, 2009 at 8:41 pm | Reply Brian Tkatch

@Nitin

If the user who owns the stored PROCEDURE has access, the SP can include the option:

WITH EXECUTE AS OWNER
on November 4, 2009 at 5:49 pm | Reply DHANAJEYAN

Hi Dave,
Could you please tel me as how could i retrieve all the schema names from a database by passing the database name as a parameter using a stored procedure
on November 5, 2009 at 8:06 pm | Reply Shafqat

Hi, Panil. Could you please tell me how to search one query string from specified fields of specified tables from database.
on November 5, 2009 at 9:40 pm | Reply Aleks

Hi All,

Is this possible?

I have this query which inserts records on table A (which has an increment identity column) from a temp table; when executed let’s say it has inserted 1000 records…

Insert Into tableA (Email,Name)
select email,name FROM tempTable
WHERE email in (select email from tempTable where siteid=5);

What I want to do is to get all those 1000 identities generated on the insert command and put them on a temp table. Is there a way of doing this?

Thanks in advance!
on November 5, 2009 at 11:53 pm | Reply Brian Tkatch

@Aleks

A few ways:

1) In the INSERT use OUTPUT INSERT.Id, INSERTED.email INTO @temp_tab

Then use @temp_tab to UPDATE tempTale

2) UPDATE tempTable SET id = (SELECT Id FROM tableA WHERE tableA.email = tempTable.email);

3) same UPDATE, but use a join.
on November 6, 2009 at 5:57 pm | Reply Najam Khan

SELECT D.Branch_Name,D.branch_id,D.Branch_Cd,(D.Add1+’ ‘+D.Add2+’ ‘+D.City)AS Address ,A.NAME,A.SMC_CD,A.Enroll_Dt,B.DATA, (select Name From Employee_master where Parent_id in (Contact_id))
FROM EMPLOYEE_MASTER A
INNER JOIN MASTER_DATA B ON B.DATA_ID=A.DSGN_ID
INNER JOIN branch_master D On A.branch_id=D.branch_id

I have table which column are mentioned in query.Contact_Id is PK and Parent_id reporting head ID.IF I am Manager My Contact ID is 1001 after some time I hired an assist manager they will report me so assist manager id will be 1002 and parent ID will be 1001.So I write above query and want to get reporting head ID with his name.So I write above query and it raise an error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
”
So give me solution

Warm Regards
Najam Khan
Software Engg.