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
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?
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.
Good and great work Pinal……
Keep going……
Thanks a lot
please give me the network commands in sql
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
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
@Ram
Just calculate the COLUMN. That is, instead of referring to Qty, refer to Qty – Wt.
Thankx a lot sir….
@Ray
Are you passing the same parameters each time?
What is the SP doing.
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
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
@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.
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
@ 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.
@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
How can i insert records from one table to another table in a batch of 1000 records in SQL Server 2005.
Mostly like you should use
insert into target_table(col_list)
select top 1000 col_list from source_table as s
where not exists(select * from target_table where keycol=s.keycol)
while @@rowcount>0
Begin
insert into target_table(col_list)
select top 1000 col_list from source_table as s
where not exists(select * from target_table where keycol=s.keycol)
end
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 ?
@Qutub
The simplest solution would be to: INSERT INTO tab2 SELECT TOP(1000) FROM tab1;
But maybe there are more details?
@Qutub
What do you want to achieve by doing that ? What is the objective ?
~ IM.
@ Imran Mohammed
I’ll try. Thanks a lot.
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.
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
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
It doesn’t matter the ordinal position of the column as long as you put it in the proper place in the SELECT statement
If you still need it, do it via Management studio
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.
Hello Penal Dave,
I want to store data in two languages(English, Hindi) simultaneously. How can i do it. Please help.
Allice
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.
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 .
use nchar instead od char datatype
Sir,
How to import and export the data through DTS in SQL Server.
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
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
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
Hey Pianl Dave –
Your picture on every page is ridiculous and distracting.
Can you take it off.
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
@AlexB
Neither. Use NOT EXISTS:
SELECT Product.ProductID
FROM Product
WHERE NOT EXISTS(SELECT * FROM WorkOrder WHERE Product.ProductID = WorkOrder.ProductID)
@Manish
LoyaltyRept must also be mentioned in the FROM clause.
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
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
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
@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)
Thank you Brian Tkatch
But the solution is not giving the exact result as before
Can someone provide the exact solution
Regards,
Hari
Dear Pinal,
Is there a way to concisely describe how EDI relates to SQL Server or visa-verse?
Thanks,
Alan
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
@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?
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
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
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?
SELECT name from sydatabases
where name not in (‘msdb’,'tempdb’,'master’,'model’,'Cooool’)
What is this ‘Cooool’? It must be user created one
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.
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.
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
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.
@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.
@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.
@ Fazal,
Very frequent question in SQL. Please read this:
http://www.sqlyoga.com/2009/02/sql-server-get-comma-separated-list.html
Thanks,
Tejas
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
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
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
@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;
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
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
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,
@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.
@Rishi Kabra
How’s this: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en
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
@Prasanth
See CONVERT() in the help file. It has a number of formats it can output it as.
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
Where do you want to show data?
If you use front end application and date variable, by default it will display the format based on the system settings
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
@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
@prasanth
I do not know. I thought that was the default.
@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;
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.
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.
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.
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
Start with this
select
dateadd(year,@year-1900,dateadd(month,@month-1,0),0),
dateadd(year,@year-1900,dateadd(month,@month,0),-1)
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.
@Vikas
Except for the third parameter, CAST and CONVERT do the same thing. The help file explains them both in the same entry.
@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.
Where did you read that CAST wont be supported from version 2008 onwards? Note that it is ANSI standard and convert is not. I dont think MS will drop CAST
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.?
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
@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.
The proper way of incrementing the year part is not using replace function but dateadd
update #example
set date = dateadd(year,2,date)
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.
@ 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
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
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
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
You need ti make use of PIVOT operator
Refer this post for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
@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.
@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.
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
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
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
@Imran
Thanx for getting back on that. I was a little nervous. :)
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)
@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…
@chirag
If it gives the appropriate results within an acceptable amount of time, use it. :)
Thanks Imran,
I will dig in the details of the data base and will come back to you.
Manuel
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.
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
Subscribe with a post with old email id
Confirm it with wordpress which will take you to all old subscription which you can delete them
Subscribe with the new email id again
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..
select * from your_table
where cast(left(data,1) as varbinary(100)) cast(upper(left(data,1))+lower(substring(data,2,charindex(‘ ‘,data))) as varbinary(100))
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
@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’
That wont work for case insensitive columns
@Steve
Do you mean CR or CRLF? On Windows, CRLF is what is usually wanted, so CHAR(13) + CHAR(10)
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
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
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
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.
It should be
alter procedure ins @ename varchar(25),@key int output
as
insert into dept(empno,ename,ddate) select empno,@ename,getdate()from emp
SET @key= (select max(empno) from emp)
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
You may need to get help from this upgrade advisor
http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en
Also read about behavioral changes
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?
How the get the list of database views present in a database
select * from sysobjects
where type='v'
Request for AdventureWorks Database – Samples
As mentioned tried
First, go to Microsoft Download Center and download SQL2000SampleDb.msi.
Gives Following Error
AccessDeniedAccess Denied
2955D539CFB9C2D6
−
ZnCjz7bcuibM41qzigspuLrcPmGh0YwhIBju8oVVvAewZibnSAMXikH79agKKcJY
Please Help
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
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!…
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.
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.
You should group it by
dateadd(month,datediff(month,0,leave_start_date ),0)
We have a SQL Server cluster that we want to break into two standalone servers. What’s the best way to do this?
@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
@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;
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.
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.
@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
@Chirag
OK, i understand.
The answer? I don’t know.
I use the CTE because it is usually looks cleaner.
@kasi
Are there always exactly 4 statuses?
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.
@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;
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?
Freek,
Check the below code snippet and I believe this is what you are looking for.
create table #temp(
time_stamp datetime,
variable_name varchar(10),
variable_value varchar(10))
go
insert into #temp
select ’01-01-2009 12:00′, ‘a’, ’1.0′
union all select ’01-01-2009 12:02′, ‘b’, ’2.0′
union all select ’01-01-2009 12:05′, ‘a’, ’1.2′
union all select ’01-01-2009 12:10′, ‘b’, ’2.5′
go
select t1.*
from #temp t1
inner join (select variable_name, max(time_stamp) as time_stamp
from #temp
group by variable_name)as t2
on t1.variable_name = t2.variable_name
and t1.time_stamp = t2.time_stamp
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.
If you use version 2005 or above
select t1.sno,t2.sno from
(
select row_number() over (order by sno) as rownum,sno from table1
) as t1
inner join
(
select row_number() over (order by sno) as rownum,sno from table2
) as t2
on t1.rownum=t2.rownum
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
Both server different purposes
You need to follow one of the steps suggested here
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/24/select-columns-from-exec-procedure-name-is-this-possible.aspx
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.
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
You need to make use of PIVOT operator
If there are unknown number of FLD2, you need to use dynamic sql
For version 2000, use
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
For version 2005 or above,use
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
@Freek Wiekmeijer
Take a look at using something like
MAX(variable_value) OVER(PARTITION BY variable_name ORDER BY timestamp DESC)
@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;
I would use row_number() function to connect two tables
See my previous reply for the same OP
@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;
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
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
@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.
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
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.
@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.
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
It should be
“select * INTO ” + tablename + ” from
(select col1,col2,…. from OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=” + dsn + “‘, ‘SELECT * FROM [Sheet1$]‘) ) as t”
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
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
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
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…
You need to use only two columns in the INSERT statement as you supply values for only two columns
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,
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 !!
You need to double the singles quote when it is part of data
select id from sample where name=’xyz’’s home’;
Also refer this to know how to use single quotes
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
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.
@Perm
select * from [sample]
where name = ‘xyz”s home’
~ IM
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
Ashutosh
1. you can not have same column_name and table_name
2.
select sum(case when amount > 0 then amount end) as sum_positive,
sum(case when amount < 0 then amount end) as sum_negative
from
@Irene
Which COLUMNs are you trying to UPDATE?
@Ashutosh Katariya
select sum(case when amount > 0 then amount else 0 end) as amt1 sum(case when amount 0
I hope you meant this
select
sum(case when amount > 0 then amount else 0 end) as amt1,
sum(case when amount <0 then amount else 0 end) as amt2
from your_table
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.?????????????
use
replcae(col,”,’ ‘)
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”
@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.
@ Imran,
thanks dear It is working,and I use like replace(telephonetype,’<>,”)
and it is also working.
thanx again.
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.
If you want date in specific format, format that cell in EXCEL
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 ..
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.
Post the code that caused the problem
Also read about ALTER table in SQL Server help file
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 …
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.
Take a look this query
select * from msdb..restorehistory
@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.
@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.
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
I dont think that is possible in SQL Server
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
@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′)
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.
i replaced union all to union and used distinct but the duplicate rows are still there
What did you mean by duplicate?
Note that you have used 0 for some of the columns in the second part of the union all that cuases this
Post some sample data with expected result
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.
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.
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
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?
Either follow what you used now or make use of Linked Server so that you can query ORACLE data within a connection to SQL Server
Read about sp_addLinkedServer in SQL Server help file
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
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.
1 It is used in versions prior to 2005
2 It is used from version 2005 onwards
So there are some new columns added
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
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
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.
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
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.
Read about Stored Procedure in SQL Server help file
It has examples
Also refer http://www.asp.net to know how to refer that in .NET
Hi Pinal,
Can i use Truncate Event in DDL trigger ?
Is there any way to restrict a table from being truncated?
One option is to create a view with computed column and use that view
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
@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;
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
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
@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;
How we can check which application or process is updating our database.
One option is to run a profiler
thanks brian , kudos
it worked for me.
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 ?
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
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
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
DECLARE @T AS TABLE(
Here the keyword AS wont work in version 2000
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.
Hi Rakesh,
You can do this simple, sort & sweet.
update emp set status = ((status-1) * (-1))
or
update emp set status = status-1
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)
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.
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
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
@Nitin
If the user who owns the stored PROCEDURE has access, the SP can include the option:
WITH EXECUTE AS OWNER
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
Hi, Panil. Could you please tell me how to search one query string from specified fields of specified tables from database.
See if this post helps. It searches for a particular string in all character columns from all the tables of the database
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
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!
@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.
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.
Hi Pinal Dave,
I just wanted to express my gratitude for your site. I read tons of other sites but yours stands out for straight forward solutions. So don’t be too jealous of Steve Jones! He is great but so are you!
Take care my friend and congratulations on your cute baby!
Howie
Thank you howie,
I appreciate your kind words. Steve Jones is my personal friend and you can see I have several of his photo graphs on my site. We are together as friend and appreciate each other. In fact, I do put on SQLServerCentral.com T-Shirt quite often.
Thank you again for you visiting my site and observing the details. Very few people leave comments so those who leave comments, I thank them.
Regards,
Pinal
Hello to all,
This is a very good webside for information related to sql. I m persuing MCA ,please tell me how can i get quaries related to sql.
@ alkesh,
Try this.
select email, name into tableA
Where email in (select email from tempTable where siteid=5);
reply me the answer.
Can you pls help asap?????
when i migrate tables from oracle to sql server using import/export wizard , i choose option delete rows in destion table in wizard but at the end it showing u can’t truncate table it is referenced by other table.but i am 100% sure that i disabled all the foreign keys? Can you please help me its urgent??????????????????
Hi Aleks,
You can write your query as:
DECLARE @Table TABLE(Seq INT)
INSERT INTO Table(Column1, Column2)
OUTPUT inserted. INTO @Table
SELECT C1, C2
This populate your table variable with inserted identity values.
That you can use it to next queries for the session.
Thanks,
Tejas
Thank you guys,
I forgot to say that the Sql server version I’m using is 2000 which doesn’t have the OUTPUT clause; and the other approaches didn’t work at all.
So far I have solved the issue using this query, but it’s not 100% secure…
SELECT subscriberId INTO #TempTable2 FROM
(
SELECT TOP 1 WITH TIES id FROM tableA
ORDER BY Date DESC
) as t;
which takes all records that were inserted on the same date time as the top 1 and puts them into tempTable2, but if someone else is doing and insert on the same moment then I guess those records will be selected too.
Hi Pinal and others,
I am using one SQL Server (2005 Entp.) Database which is connected with a specific application. I want to create a Mirror Database from this SQL server Database which will work with the Mirror of this application. And of course, this mirror database will synchronize from the original database from time-to-time as usual.
Now, as far as I know there are quite a few procedure to make a mirror from the database, such as Database Log shipping, Database mirroring, Database Snapshoot and so on. But the thing is, these all procedures create a ”Read-Only” DB, which synchronizes with the main Database. I am trying to create the mirror database with ”read and write” access to other application separately.
My queries are:
1. Is it possible to create a mirror database with ”read and write” operation?
2. If yes, what is the procedure to do that?
Hi Koushik,
It is not possible to configure Mirroring with “Read and Write”.
You can not access or query to mirror database.
If you want to access same copy of DB then you have to implement replication. this will solve your purpose.
Thanks,
Tejas
Hi
I read your blog it very interesting.
Hi Tejas,
you are correct .During mirroring ,it’s not possible db with read and write.
Actually i mean to say mirror means duplicate copy of Primary database with proper synchronization.
Also replication of database its not possible because during replication db state is read only or recovery mode.
Thanks
Koushik
I need to find the recent date should be displayed from the results
The table looks like as follows
ID Startdate endDate
1 10/10/2009 10/30/2009
2 10/15/2009 11/20 2009
3 11/10/2009 12/31/2009
4 09/01/2009 10/25/2009
I should be able to get the 3rd record only
By recent date do you mean the maximum date value ? Also, on which column you are looking for recent date ?
Hello pinal,
I have a very strange SQL 2005 installation problem which I have put in lot of forums including Microsoft’s one without any positive result.
Whenever I try to install SQL 2005, I get this error
“Failure setting security rights on DTS user account $ComputerName”.
After this error, my installation is stopped abruptly.
Please can you tell me how to solve this error.
You are my last hope, Trust me i have put this in almost all sql forums with no positive result. please help.
Hi,
We are using master.dbo.xp_smtp_sendmail .
EXEC master.dbo.xp_smtp_sendmail
@from = N’spiProjectqmail@spi.com’,
@TO = @Recipients_Id,
@message = @strMessage,
@type = N’text/html’,
@subject = @strSubject,
@server = N ‘IP address’
TestSP contains above staements and also its working fine in SQL Server 2008 Query Editor but same TestSP we created Job and run that job its giving error.
Message
Executed as user: dbo. The EXECUTE permission was denied on the object ‘xp_smtp_sendmail’, database ‘master’, schema ‘dbo’. [SQLSTATE 42000] (Error 229). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
Any Configuration Required for xp_smtp_sendmail to run from SQL Agent.
Can you please help me, this week end production migration.
Hi,
i want a query that add an existed user login to specific databases with a specified role (db_datareader) and not using management studio to add a user to a role for every database. Is there a way to do that?
Thank you in advance.
@Anil
Is this what you want?
SELECT TOP(1) ID, Startdate, endDate FROM mytab ORDER BY endDate DESC;
@Frida,
Can you provide more details, why you do not want to use Management Studio.
Do you want a Stored Procedure, which you can call from (Front End) application to add a user ……
If you want a Stored Procedure, then yes, it could be done.
~ IM.
@Krishna Mohan
To answer your question quickly, You said, you put that script in a job and execute job and it fails,
Solution: Give the owner of the job execute permission to stored procedure : xp_smtp_sendmail in master database. Below is a sample script.
use master
go
Grant Execute on xp_smtp_sendmail to [Job_Owner]
go
Question:
Dude, did you said, you are on SQL Server 2008, why are you still using xp_smtp_sendmail stored procedure, this is not a native SQL Server stored procedure, this is a third party stored procedure.
Instead of this you can use, SQL Server native stored procedure with same functionality as xp_smtp_sendmail, this procedure is available from SQL Server 2005 and higher version.
Stored Procedure Name: msdb.dbo.sp_send_dbmail
Let us know, if you need more help with your migration.
~ IM.
@Anil
create table #example1 ( ID int, StartDate datetime, EndDate datetime)
insert into #example1 values ( 1, ’10/10/2009′, ’10/30/2009′)
insert into #example1 values ( 2, ’10/15/2009′, ’11/20/2009′)
insert into #example1 values ( 3, ’11/10/2009′, ’12/31/2009′)
insert into #example1 values ( 4, ’09/01/2009′, ’10/25/2009′)
select ID, StartDate,EndDate
From #example1
Where EndDate = (select Max(EndDate)from #example1)
go
drop table #Example1
~ IM.
Hi Imran Mohammed,
Thanks for yours reply.
exec PRJ_SP_Project_PMT_Notify
Note : first give spiProject login account permission for mater database.
Use Mater
grant execute on xp_smtp_sendmail to spiProject
use master
go
Grant Execute on xp_smtp_sendmail to [Job_Owner]
go
I done all the settings , no luck and also insted on xp_smtp_sendmail i used msdb.dbo.sp_send_dbmail.
I done all the Configuration for sp_send_dbmail. Through SP mails going same thing i execute from job same error coming.
Using Database Mail with SQL Server Agent
http://msdn.microsoft.com/en-us/library/ms190606.aspx
above settings also i done. Why emails are not triggering from jobs through storeprocedures its triggering mails.
Hi Pinal,
I have created 2 schemas in one database and trying to run the script in order to create tables under schemas. but problem is that script has another schema inside it and when i run that script its giving me error like:
”
User does not have permission to perform this action.
Msg 2759, Level 16, State 0, Line 2
CREATE SCHEMA failed due to previous errors.”
E.g
I have 2 schemas say Tarun1,Tarun2 under abc database. now I want to add some table under Tarun1, Tarun2 schema. (something like nested schemas… )
Here is script:
– Create schema for common tables
create schema OLS_TABLES
;
– Create common tables
create table OLS_TABLES.Table1(
ID nvarchar(20) not null,
TYPE nvarchar(50) not null,
SCRIPT varbinary(max),
primary key(ID, TYPE)
)
;
Can we add OLS_TABLES schema under Tarun1 schema? If yes then please suggest me how we can do that? what Kind of permissions we need?
Waiting for your reply!
Thanks in advance
Tarun
Hi,
Give me few most promising reason when we are left with no option and have to use cursor only instead of while loop.
Thanks
Anil
Hi Pinal,
I am working on a process flow application and within a group of records, I need to create subgroups based upon certain actions.
As the process moves from one person to the next, the current user can reject the process, causing it to move back to the beginning again. The first time the process goes through would be subgroup 1. If it gets rejected, the next group will be subgroup 2. Going forward, this would be pretty easy if I could change the table structure and add a new column. Unfortunately, that is not possible. This will have to be a dynamic process.
I know I could do this with a cursor or other looping structure but would rather use CTE.
Can you give me any thoughts on how to accomplish this?
Thanks!
Clay
Hi Pinal, I have a question for ms sql 2000 that you maybe can help me.
I’m not a dba, I was just needing to do a “what I thought was a sinple task” and suddenly I meet hell.
What I need to do is as simple as to duplicate a database with a different name and I have no found a procedure to complete this task successfully.
I have seen this http://blog.sqlauthority.com/2007/07/13/sql-server-2005-use-alter-database-modify-name-instead-of-sp_renamedb-to-rename/ and this http://www.mssqltips.com/tip.asp?tip=1122 with no results. always the data file keeps having same name and I cannot mount both databases at the same time in same server.
What I need is… simply a database called PRODUCTION copy this same entire database to same ms sql server 2000 with other name like TESTDB.
If you have a way of doing this please contact me!
Thank you very much.
Regards,
Pablo Alonso
We have one production DB. We are copying data from production ,say X….and archive DB , say Y, to one more DB , say Z….which is specially used for reporting.
Our reports will always point to reporting DB Y….and every day there is reconsolidation of data for each process like orders, lens etc into respective tables in reporting DB.
Reconsolidation scripts will use the ARCHIVE DB Y.
Now archive DB Y. size is very large so customer decided to split the DB into year wise DB’s…
Like
• Y.__ARCHIVE_2009
• Y.__ARCHIVE_2008
• Y.__ARCHIVE_2007
If we do this change,we have to run consolidation scripts on every above DB’s…now its 2009 but in future 2020, we have to run scripts in 10 to 20 Archive DB’s..
My question: Is it right way to handle this kind of satuation? If not, what is the best approch?
Thanks in advance brother..
hi pinal,
i was looking for Reporting Services at your blog but no luck.
Please can you suggest some good reference to SQL Reporting services? or
Can you post something related to reporting services?
Thanks
Hi Friends,
Could any body help me to find out SQL serevr BI tutorials in Mumbai?
Thanks,
Hi Pinalkumar,
I have a concern regarding Stored Procedure & Execution Plan Cache.
I’ve heard that it’s not good when the execution logic depends too much on the input parameters, wrong execution plan maybe chosen and causes bad performance. However I have a scenarios like this: I have 3 task to do: Update, Insert, Select. And I have 2 way to create SPs
1. Create 1 SP only, with an input parameter to tell which action need doing, something like:
if ‘U’ then update data
if ‘I’ then insert data
…….
2. Create 3 separate SPs for those 3 actions.
For not too complicated SPs, I like option 2, to reduce the number of SPs. However, which is better regarding performance on large databases?
Thank you very much,
hello Pinal,
Im a web site developer, I have a question for you. currently my team is busy doing database optimization task. I was asked to sort out the SPs and it full statics. I was able to do the thing except the condition, which is very challenging.
We r using sql server 2005(Without sp2).
My Query is that i have to sort out all the SPs which was never used, at this moment im able to get list with the help of sys.dm_exec_cached_plans but its giving the list which was executed at least once, furthermore there is no relation between the rows returned by sys.dm_exec_cached_plans and sys.object so that I can get SP name and its execution_count in a single query.
Please suggest help…
Thanks in advance.
Hi,
Need some help regarding SQL server 2005 .
I have set up replication on SQL server 2005 with tables and Clustered and non clustered indexes being replicated.
What i want to know here is what are the important parameters to be monitored and how to set up Notification alerts for the monitoring of these parameters or failures .
Thanks
Pravin
Okay, I did this but I was able to inject (both delete and drop table statement worked.). Can you please a solution on sp_executesql. Yes, I did LOTS OF GOOGLE FOR PAST 8 HOURS WITH NO LUCK… THANKS
CREATE PROCEDURE uspGetEmployee
@LastName VARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(300)
SELECT @sql = ‘ SELECT * ‘ + ‘ FROM EMPLOYEES
WHERE 1=1 ‘
IF @LastName IS NOT NULL
SELECT @sql = @sql + ‘ AND [LASTNAME] = @LastName’
EXEC sp_executesql @sql, N’@lastname VARCHAR(50)’, @LastName
end
GO
Here is how I deleted and drop the table…
DECLARE @RC int
DECLARE @LastName varchar(50)
EXECUTE @RC = [Northwind].[dbo].[uspGetEmployee]
delete from employees where lastname=’king’
hi pinal,
please give same way to tuning a SP.
Can you pls help asap?????
I an new in sql Server, i need to become SQL server DBA, So plz tell me the best books for beginners. So that i can learn from A to Z of SQL server 2005
Hi,
I have just started learning TSQL and I have a basic question.
? If there are 100 million rows with 20 indexes, I want to delete 90 million rows from it.
what I have to do for this?
@Kumar
Based on what criteria are you doing the DELETE?
Hello Pinal sir,
can u plz help me with this
we r implementing some standards so we need to know
can we monitor the sql error log configure settings which is greater than 0. I know sql server allows to store between 6 – 99 logs…I want to know where it store this information in system tables or registry some where etc..if we configure the log setting to 20 or 34 where does sql server store this information and is it possible to monitor these settings
THANKS IN ADVANCE
Hi Kumar,
In your case, it is better to insert remain 10 million records to dummy table and then truncate that table
and Re-insert it again. This will be much faster.
Tejas
SQLYoga.com
Hi,
my client now want to migrate their Legacy Oracle database into SQL Server 2005 database uisng SSIS.
can anybody suggest me some good URL or reading material for Oracle to SQL Server migration ?
what to consideration and how to
thanks.
Hi
Sir, Can i access the resultset returned by sql statement, stored proc in sql server 2005 or 2008
Please reply
Hi
I am executing query as follows
select top(1) city, state from city_state_finder where
freetext((city,state),’vancouver’) and freetext((city,state),’on’)
While executing this query response will not come as it’s delaying the result without giving any errors.
But if used same query without top(1) then result is coming up in 3 seconds.
select city, state from city_state_finder where
freetext((city,state),’vancouver’) and freetext((city,state),’on’)
Please guide me why this is happening & how can i over come this situation.
Thanks
Shiv
Hello Sir,
Is that possible to recover truncate table in Sql Server 2000 without any available backup ?
Please give me tips or idea to recover truncate table.
Thanks
Bhargav
Hi all,
I have a problem in Sql 2005. I have design a databse in Sql 2005 with applicable triggers, procedures , function etc. my database is handled properly from front end. Now, what i want that i have to create a new database which has same features as my current database. how can i generate ? pls. help me.
thanks in advance.
Nayan
Dear,
I wonder if the XP_CMDSHELL feature is enabled. Does anyone know how to do this?
I will need your help,
I have one table TAB1 with atributes name, surname
I need to copy all data from tab1 into tab2 using cursor…
tab2 has the same atributes…name, surname
Can you help me with it?
@Nirav
Pinal has a linked whitepaper for migrations: http://blog.sqlauthority.com/2009/08/23/sqlauthority-news-sql-server-2008-migration-white-papers/
@Kapil
Perhaps you can store it in a temp TABLE first? Pinal has an article here: http://blog.sqlauthority.com/2009/09/23/sql-server-insert-values-of-stored-procedure-in-table-use-table-valued-function/
@Shiv
This probably has to do with the optimizer plan. Usually, the optimizer is very good at picking the right plan, but sometimes it isn’t.
To see if this is the issue, go to the menu “Query” then “Include Actual Execution Plan”
@Bhargav
I don’t know. Pinal has a related article, however, that talks about DELETE, TRUNCATE, and ROLLBACK:
http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/
@Nayan
A database can be duplicated with scripts: http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
@Adalton Jr.
Use sp_configure: http://blog.sqlauthority.com/2007/05/24/sql-server-2005-retrieve-configuration-of-server/
It also can enables xp_cmdshell:http://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/
@Nidecker3
Why use a CURSOR?
INSERT INTO tab2(name, surname) SELECT name, surname FROM tab1;
Thank you Brian Tkatch,
I truly appreciate your contribution to community!
Kind Regards,
Pinal
@Pinal, it’s easy when you already have answers for everything. :)
@Brian Tkatch,
Thanks for the reply.
The link you gave me does not refer to the proper use, but I understand.
Just use:
sp_configure ‘xp_cmdshell’
Thank you.
Hi Pinal,
I have to find / kill qureies by userid’s which runs more than 10 min ….
Please help.
Thanks,
I have to find / kill qureies by userid’s which runs more than 10 min ….
Please help.
Thanks,
Hey Dave,
I am trying to figure out a way to find relations of tables in a database without constraints using “parsing the create statement of procedures”. Could you give any hint? Thanks…
@Ugur
Are there FOREIGN KEYs? INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS lists FKs, so tyou ought to be able to look there to find relationships.
Hey Brian. There are no keys whatsoever. It is an un-relational relational sql server db. I did parse the procedure definitions for cases like table1.column1 = table2.column2
If there are no FKs, how do you define “relations”?
I am defining logically “related tables” by discovering the logic embedded in stored procedures and examining data.at leas that is what I am hoping to do
@Ugur
That sounds complicated, and probably best done by hand.
But i am confused. If the relations are defined by their usage in SPs, how do you expect to find their relation without parsing those very SPs?
I guess, technically, you could watch executed SQL statements, but even if that worked, is that really any different than parsing the SPs themselves?
Perhaps the COLUMNs are similarly named?
Thats what I said actually. I am parsing SPs for the join cases on table column couples…Table1.column1 = table2.column2. Cases tools like PD can also help you related similar named columns but its nonsense so what I do is to find the cases and check if the tables are any of the dependent tables of the SP. u can find that using the query analyser or the INFORMATION_SCHEMA views…I will post the script when I am done
@Ugur
Hmm.. If all the SQL statements use semicolons, parsing the FROM until the semicolon should find all TABLEs and the relations.
Just exlcude any time where a literal is used, or only inlcude when a COLUMN is used on both sides, then ORDER BY the clauses (possibly duplicating every clause so it shows up backwards and forwards).
It’s take a few minutes to write such a query, but shouldn’t actually be that bad.
Hi all,
I want to know that, if we apply trucate statement,
will it effect on indexes, both cluster and non cluster?
additionally, should ‘trucate’ affected any sql objects like
trigger,sp,function etc…
pls. reply
Thanks
Chirag
Hi Chirag,
TRUNCATE will only delete all data from table.
There is no any other effect of this.
Tejas
Ha Panel – you seem to always help me an you dont even know – thanks to your blogs. Got a question for you. I am trying to create a full text indexing using the wizard in 2005 and I am getting this error message:
“could not load type ‘microsoft.sqlserver.management.smo.agent.jobbasecollection’from assembly ‘microsoft.sql.server.smo, version=9.0.242.0,…”
If you need more info please email me and i can send a screen capture.
Thanks – Patrick
Hi sir ,
i have one doubt About SqlServer Convertion
my code is below
In SQl stored procedure
Create Procedure OnlineExam_QidsFromQtype
@QtypeID as int,
@QIDS as nvarchar(100)
as
begin
select * From Question where QtypeID=@QtypeID and QuestionID in(@QIDS)
end
In Table QuestionID Coloumn is int datatype
what can i do sir,pls reply quick
Hi sir ,
i have one doubt About SqlServer Convertion
my code is below
In SQl stored procedure
Create Procedure OnlineExam_QidsFromQtype
@QtypeID as int,
@QIDS as nvarchar(100)
as
begin
select * From Question where QtypeID=@QtypeID and QuestionID in(@QIDS)
end
In Table QuestionID Coloumn is int datatype
what can i do sir,pls reply quick
Advance Thanks
Hi Pinal,
Is it possible to send SMS from SQL Server 2005.. ? If yes, Can you please guide me..
Thanks,
Mani V S
Hi Pinal,
I have a question about the patches..how does the patches involve in sql server??
Hi Pinal,
I have a desktop application. While installing my client machine, i am running my SQL Script through the installation. I need to add security to the database. I need to use SQL Server authentication, but i want to block all the other users to access my database and they should not able to change through any system stored procedure like sp_changeobjectowner, sp_change owner.
Please reply me.
Thanks – S.Devarajan.
Hello Devarajan,
Even Windows Authentication is more secure than Mixed mode Authentication. But If you want to use only the SQL Server authentication then delete all windows login including BUILTIN\Administrators.
For more details on how to control windows logins please check the below article
http://support.microsoft.com/kb/932881
This way you will allow only to the users that have a valid SQL login and password. Now you will have to control authorization of each sql login. To a login allow only those permission that are needed.
If you want to check what permission are granted on each object to all logins then use the below script:
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
Please let me know if you have any more query/doubts.
Kind Regards,
Pinal Dave
Hi Pinal,
I am very happy to see your reply.
I gone thru the technique what you forward me. But it mainly focused on creating service and agent. I can’t able do from script code those operation. Bcz i am running my SQL script from my setup file.
another way and your suggesition,
But now i have blocked my Sps, UDFs and Views. Using your blog (http://blog.sqlauthority.com/2007/07/01/sql-server-explanation-of-with-encryption-clause-for-stored-procedure-and-user-defined-functions/)
still pending is table structure. I don’t want, my client to open my table structure. Is this possible i can encrypt the table structure(not data), i can write a script for that?
Thanks and Helpful your pervious reply.
S.Devarajan.
Hi need your help in SQL Server 2005 Stored Procedures can you please help me
Hi pinal,
I have query where I need to display Enddate which is 18 yrs more than of Start date in my Reports.
I have 3 columns say QEDate,QEStartDate & QEEndDate.
first two column display from my form and I want to set the condition for Enddate which display 18 yrs more than of start date.
please help me here
Hello Sarang,
To add 18 years in a datetime column QEStartDate, use the DATEADD function as below:
SELECT DATEADD (year, 18, QEStartDate ) FROM
Kind Regards,
Pinal Dave
Hi Pinal,
I am working with SQL Server 2005. I want to get the Interview Questions & Answers.
Please Help me.
Hi Sanjoy,
Get the Interview Q&A from below link:
http://blog.sqlauthority.com/sql-server-interview-questions-and-answers/
Kind Regards,
Pinal Dave
Hi Pinal,
I found the following paragraph in your interview Q&A and I thought you should correct the typo in the line “HAVING can be used only with the SELECT statement”. I am guessing you meant “WHERE can be used only with the SELECT statement”. Over all Good work. Thanks
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. (Read More Here)
Hello Dani,
Thank you for your nice feedback on Interview Q&A. But there is no typo in the line “HAVING can be used only with the SELECT statement”.
HAVING clause is used to apply criteria on aggregated values. We can not use aggregate fuctions or grouping in DELETE and UPDATE statements. Thats why HAVING clause can not be used in DELETE and UPDATE statements.
Please let me know if you need more details.
Kind Regards,
Pinal Dave
Hi Pinal.
I am new in ur useful web site.
I’m trying to download SQL Server Cheat Sheet,but it has an error.colud u pls email me.???
thank you.
Dear Pinal,
Name of Table : poa_detail
rows : 4405
Reserved : 49640 KB
Data : 7120 KB
Index Size : 320 KB
Unused : 42200 KB
This is output of Sp_spaceused poa_detail
I am working for performance tunning work on the table.After rebuilding,index defrag and update stat.The unused space for the table is not reducing.I want to reduce the unused space in the server.I am thinking that due to this reasonperformance slow? .its true.Please tell me how to reduce the unused space.Please tell me the tech.
Please clear me.
If this table has clustered index then defrage the clustered index. If not then you can remove the high unused space by creating one temporarily.
Unused space also include the free space specified by fillfactor and that can not be removed by defragmenting the table or index.
Kind Regards,
Pinal Dave
Hi Pinal,
When we creating a cluster index and drop that index significantly reduced the unused space .After doing this similar to all tables performance will increase or decrease.(Mostly In our system use of only Non cluster index ).
Please suggest me,
Greetings Pinal.
I have been reading this blog for a long time and it has really been very helpful to me.
I am having an issue with the rsconfig utility.
I am trying to execute the following command,
rsconfig -c -s -i -d -a SQL -u sa -p sage
But really I am not very sure about what needs to be there in . I put my machine name, since SQL Server is in my machine.
It says “The Command Completed Successfully”., but throws an error saying,
The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help
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)
I have gone through the article regarding the error in your blog and found everything configured in my machine.
I doubt there is something wrong with the .
Please provide me some help on this.
Regards,
Kumarjit
My rsconfig command did not show up correctly. It is like
rsconfig -c -s [server name] -i [instance name] -d [database name] -a SQL -u sa -p sage
Hi,
I have a conflict problem in my stored procedure Union operation.
While i ran the stored procedure i got an error
“Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the UNION operation”
help me to sortout this problem
Anuthuvan,
Collation conflict occures when we try to compare, join or union two column of different collation.
For example
SELECT col1, col2
FROM Table1.col1 = Table2.col4
UNION
SELECT col5, col6
FROM Table3
Here make sure that collation fo following column should match
col1 = col5
col1 = col4
col2 = col6
Kinid Regards,
Pinal Dave
Hi Friends
Here i have a doubt regarding the comparison of DATETIME fields in SQL Queries. Suppose i need to fetch certain list of data from a table by inputting a datetime parameter. Then i need to write a condition in the where clause.
For ex
SELECT [UserID] FROM [Users] WHERE [DateOfBirth] > @prmDate
Here it seems simple but i felt some issues and im just trying to achieve a good query which can resolve all my queries or rather a better approach.
If the inputting date is in “DD/MM/YYYY” format and the date is in Database is in “MM/DD/YYYY” or vice versa
If the input date is having time part also then i will not get a complete result out. i.e. my input date is 01/01/2009 10.20.00 and the date in my Database is 01/01/2009 00.00.00 or something i will not get the result. But i should get the result out.
Then i tried the code CONVERT(VARCHAR, ‘dd/MM/yyyy’,101) approach again i felt this is having some issue.
For example
Compare CONVERT(VARCHAR,’02/01/2009′,101) > CONVERT(VARCHAR,’01/01/2010′,101) , here i should get a false instead im getting true as output.
Can you guys please advise me regarding this.
Thank You
101 does not have a time element. 130 might be a better option.
Hello Sujith,
These two values are compared as string. To get the right result, first convert them in datatime datatype and then compare as below:
(convert(datetime,CONVERT(VARCHAR(10),’02/01/2009′,101)) >
convert(datetime,CONVERT(VARCHAR(10),’01/01/2010′,101)))
Kind Regards,
Pinal Dave
Hi pinal
please waiting for ur response.
Hi Sir.
I want to be a DBA .But i am not Getting proper way.Kindly help me.
Hi Pinal,
1)I want remove the fragmentation in table.
Not in index. Tell me the idea for removing or defragmenting the table.
2) I want to know Table fragmentation will decrease the performance of the system.
Dont suggest DBCC INDEXDEFRG,DBCC DBREINDEX DBCC UPDATEUSAGE .Beause all i tried it is vain
please give me the brief description above
Thank You
Hello Asin,
If the table has clustered index then defragmentation of clustered index itself is defragmentation of table otherwise shrinkfile or shrinkdb degrag the table.
Obviously defragmentation decrease the performance because it results in more numberof data page load in memory and more page switching while retreiving the required data.
Kind Regards,
Pinal Dave
Pinal,
We’re curious if you may be able to help us with an issue we’re seeing with one of our customer’s SQL/Access Db.
We are an IT company specializing in Network Support, Help Desk etc. We have a customer who has built a SQL database with Access and they are having OBDC time out errors between the DB and the front end application. This application is a custom built solution similar to Master Builder. The customer elected to build their own database and application due to their belief that Master builder could not do what they needed to adequately support their business.
They earlier believed this was related to network configuration which would have pointed back in our direction, however over the past few days have come to realize they have problems between the front and back end of their solution. They have agreed to have our team have an outside pair of eyes on the situation to see if there is anything glaringly obvious that is being overlooked. The db resides on one server and the application resides on another server. The two servers are within two feet of each other and we are experiencing no significant network delays that would lead to slowness or time outs.
We would like to forward the database and have you analyze it for any glaring issues. We’re not programmers but I know in my past experience if you try to cram too many if statements and application deliverables into one solution, a lot of times the db and application react in the manner the customer is observing today.
We would like to know your rates and your availability to work this over the next couple of weeks so that we can provide our customer with unbiased expertise on what we believe to be the main cause of their issue. Thank you for your time and we look forward to hearing from you in the near future.
Hello,
How can i get collumns name of unique key of table ?
Hello Bindi,
To get the all unique key columns of a table, use the below query:
select c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME =
and CONSTRAINT_TYPE = ‘UNIQUE’
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
Kind Regards,
Pinal Dave
This should help.
SELECT
Con.Constraint_Name,
Con.Table_Name,
Col.Column_Name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Con,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE Col
WHERE
Con.Constraint_Type = ‘UNIQUE’
AND Col.Table_Name = Con.Table_Name
AND Col.Constraint_Name = Col.COnstraint_Name;
Hi Glinn,
The ODBC timeout while connecting from MS Access to SQL Server causes to suspects various aspects. Some of them are as followings:
ODBC driver you are using to connect to SQL Server.
Connection timeout setting in Access db.
U.ing Linked tables or pass-through queries
Execution time and amount of data retrieved by query in SQL Server.
Data type of columns in source table in SQL.
Quering to SQL tables or views
These all aspects need analysis and only after that we can findout any resolution.
Kind Regards,
Pinal Dave
Hi Dave,
I have some basic idea on Sql Server 2005.
Can you suggest me some books or tutorials where I can learn more especially on stored procedures in sql server 2005
Thanks
Meghana
Hi,
I had to disable and enable an step of a DTS package in SQL Server 2005 several times. I did it 5 or 6 times in order to execute the DTS and verify the notification is working.
The task was a “rebuild index task” that some tables were selected to be reindexed.
The other night the job failed and I discovered there is no table selection in the Rebuild index task while it was already there.
Do you think by disabling/enabling a task, its content could be lost?
Thanks!
Hi All,
This is archive page. Posting your question here may not get quicker answer.
Please contact me here : http://blog.sqlauthority.com/contact-me-contact-pinaldave
Find solution to your questions : http://search.sqlauthority.com
Kind Regards,
Pinal
Hi All,
This is archive page. Posting your question here may not get quicker answer.
Please contact me here : http://blog.sqlauthority.com/contact-me-contact-pinaldave/
Find solution to your questions : http://search.sqlauthority.com
Kind Regards,
Pinal
Hi Pinal,
When we creating a cluster index and drop that index significantly reduced the unused space in tables .After doing this similar to all tables, database performance will increase or decrease ?.
Defragmantation and reduced unused space of tables/index always improve performance because of less I/O and extent switches.
Regards,
Pinal Dave
Thank You
Regards
AAAA
Hello Sir,
I have question regarding what strategy should be followed for auditing.We are working on windows Application. We have various screen mode like update,insert, delete etc.. We have two approaches:
1) Our DBA prefers that auditing should be done based on screen mode. He prefers that all the information should be captured in audit tables present on screen once we update or delete any thing on screen.
2) My fellow developers on other hand prefer that only that particular information should be send to audit table which is added or deleted.For example (If a row in a grid is deleted and another row is updated then separate audit_ids going for both rows a with respective delete and update operations)
The first approach is simple but tracking is difficult, Second is complex but more accurate and easier to track changes
Kindly please reply.
Thanks & Regards
Ranjoyt
The usual solution is to track it row by row in the TABLE itself.
What is it the audit is trying to accomplish? The different goals can have different approaches.
Thank you very much Mr Brian.
In our case there is a audit table corresponding to every table. for example: If there for say ABC table we have audit_ABC table
The goal of audit table is that every record that is modified or deleted should be inserted in the audit table to keep track or history of all the data.
I guess now you should be able to suggest.
Thanks & Regards,
Ranjoyt Singh
Hi,
Would these help:
http://technet.microsoft.com/en-us/library/dd277388.aspx
http://msdn.microsoft.com/en-us/library/cc280386.aspx
Other than SQL Server built-in Auditing that Marko pointed out, to track ever change means to track every record. Like option 1, but better in the TABLE itself. Having a separate TABLE wastes space, unless the COLUMNs need to be left out of the main TABLE. Retrieving the data is easy. If it is hard, write a VIEW that does it for you.
The second option seems to me like it might miss details. That is just a feeling though.
In either case, tracking DELETEs is harder. If all the data need to be kept as a history, a status COLUMN (with a lookup TABLE) could be added to each TABLE and instead of DELETEing a record, the status can be changed to “Inactive” or the like. If only a record of the DELETEion is required, but not the actual data, an audit TABLE like the one the DBA suggested seems best.
These comments are without knowing the details of you DB. The details can always change what is best. :)
Dear dave, I hope you can help me with this error, please…
I’m getting an error message on SQL Server 2008:
Error al recuperar datos para esta solicitud. (Microsoft.SqlServer.Management.Sdk.Sfc)
Excepción al ejecutar una instrucción o un proceso por lotes Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)
No se puede abrir la base de datos ‘msdb’. Tiene la marca SUSPECT para recuperación. Consulte el registro de errores de SQL Server si desea más información. (Microsoft SQL Server, Error: 926)
Many Thanks
Hi Pinal
I need to execute a procedure on multiple servers. Could you please suggest me how to execute a stored procedure on multiple servers simultanously. How to create batch file for that.
Regards
Vaibhav
If you have added other servers as linked servers, it is possible
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID
HI Pinale sir,
One of my DBA sir asked a qsn but ican not find anywer…plz help me on this..
Please refer below error while running the DTS package as a job . Please note that Other DTS jobs are running successfully.
Please provide your soultion …. I have got couple of workaround but not yet applied on the server.
Message
Executed as user: CORP\ftrcom_sqladmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:00:00 PM Error: 2011-09-12 21:00:03.19 Code: 0xC0012024 Source: Delete Archive Files Description: The task “Delete Archive Files” cannot run on this edition of Integration Services. It requires a higher level edition. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:00:00 PM Finished: 9:00:03 PM Elapsed: 2.468 seconds. The package execution failed. The step failed.
hii , i just need to know the procedure for removing primary
filegroup
Hi,
I have a table that has a field called ‘group_quartile’ which uses the sql ntile() function to calculate which quartile does each customer lie in on the basis of their activity scores. However using this ntile(0 function i find there are some customers which have same activity scores but are in different quartiles. I need to modify the ‘group-quartile’ column to make all customers with the same activity scores lie in the same group_quartile.
A view of the table values :(apologies: the column headers are not aligned with the data rows)
Customer_id Product Activity_Score Group_Quartile
CH002 T 2328 1
CR001 T 268 1
CN001 T 178 1
MS006 T 45 2
ST001 T 21 2
CH001 T 0 2
CX001 T 0 3
KH001 T 0 3
MH002 T 0 4
SJ003 T 0 4
CN001 S 439 1
AC002 S 177 1
SC001 S 91 2
PV001 S 69 3
TS001 S 0 4
I used CTE expression but it didnot work.
My query only updates(from the above example) :
CX001 T 0 3 to
CX001 T 0 2
So only the first repeating activity score is checked and that row’s group_quartile is updated to 2.
I need to update all the below rows as well.
CX001 T 0 3
KH001 T 0 3
MH002 T 0 4
SJ003 T 0 4
Can anyone help with what should be the t-sql statemnt?
Cheers!