About Pinal Dave
Pinal Dave is a common man who enjoys listening music, reading books, travelling places, watching movies and writing blogs. Pinal has over 6 years industrial experience gained through his work within the IT industry and SQL Server Community.
He is a highly respected and leading figure in Indian IT field. He is recognized through his work as founder of the highly useful SQL Server Site SQLAuthority.com. Microsoft has presented him SQL – MVP award for his extraordinary contribution as a SQL Server Expert.
Contact Pinal Dave
Email is always welcome, whether you’re interested in talking about projects, SQL Server 2008 and SQL Server 2005 technologies, NET(C#), ColdFusion, Web Architecture, have taken an online tutorial class at SQLAuthority.com, or have an event where you believe Pinal Dave would be a great speaker or panelist.
If you want Pinal Dave to review your product, book, website or code, please do not hesitate to contact him. Or, feel free to say “hi” anytime. All comments are welcome.
Search SQLAuthority.com
If you have any questions for faster response, Search SQLAuthority.com. It is possible that your question is already answered in one of the hundreds articles.
pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com
Hello pinal sir,
I am your frequent reader of your SQL tips & tricks.
sir i have completed my BTech IT degree in 2008..Sir i want to do some certification in Microsoft SQL Server. i need some more tips abt exams related to SQL.i want to become as like your successful career.Kindly direct me sir.
Thanks n thanks sir..
Hi Pinal,
Help needed
I was asked this in an interview, I have only 1 column in a table and i need to break it into two columns..
Plz give your ideas in this
Thanks in advance
Jayanth
Hello sir,
I have MCA degree and now i want to make my career in Sql server administration ( although i have a very good knowledge in C# and asp.net). Please tell me from where to do the related certification. I am sure you must be knowing some good institute/company from where to take these courses…
Hi,
iam in a fix pls help me;((
i have 2 tables- user_table(id,name) and name_master(name).
i want to transfer the data from name_master to user_table.
Now id is the primary key in user_table and name_master table doesnt ahve an id field.
I want to enter name from name_master table and id will be any integer number that keeps incrementing at run time.
how to insert both values (id, name from name_master)at the same time to user_table
Hi Jeena,
1. Set ID as Identity property through SSMS (SQL Server Management Studio) as Yes and Seed and Increment values as 1,1.
2. Below is work around as an example.
create table #user_table(id int,name varchar(100))
go
create table #name_master(name varchar(100))
go
insert into #name_master select ‘sa’ union select ‘b’
go
declare @id int
select @id = isnull(max(id), 0) from #user_table
insert into #user_table (id, name)
select @id + row_number() over(order by name), name
from #name_master
select * from #user_table
Replace the temporary tables with actual names
Hi,
I am developing a education portal. We are taking tests of the student whoever logs in for the first time and then save the details of the student and the related test in a table. This tests are useful in analysis for giving practice sheets for his learning purpose. So according to his sore we would give him practice sheets would be given which will also be stored in the DB. So should I store the practice sheets in the same DB or create new DBs or new table for each distinct student. Please send your contact no on my mail ID for my further reference.
Thanking You.
Hi Pinal
I have a table Album Details
There are some fields like UserId, AlbumId, DATE
A user can uplaod many Album
I want to select Top 100 Album. But If a user posted more than one album them his last posted Album should come.
How it is possibe?
select * from Album_details as t
where date=(select max(date) from Album_detail where userid=t.userid)
one one latest album should come not all album
of a single user
Hi Pinal,
I am very impressed with your blog and knowledge.
I am Software devloper and living at gandhinagar Sector 3-C.
I would like to meet you personally if you desire.
Thanks,
Amit Makwana
Hi Pinal,
This is Eswari.S from chennai\india….Many times i crossed your articles….Now i want to learn sqlserver2005….can you please help me out, that will very useful for my career future.
I am Waiting for your reply.
Thanks and Regards,
Eswari.S
pseswari@yahoo.co.in
Dear Pinal ,
I have one problem regarding trigger it is new for me
i have two data base with same table on both side only name will be different
1)Head
2)SITE
when i insert one record in Heads–> Item table then that record
is also inserted into SITE –>Item Table
and how should i pass the parameters to insert statement
which iam writin inbetween BEGIN ans END section of trigger
OR if any article is there regarding triggers (May be i dont got it ) for reference
Thanks
Hi Dave,
Its Swati from mumbai
I M in a need of job in urgent basis.
I am a software developer working a company
if you have any openinging for sql dba pls reply soooon
thanks a ton
swati
Hello Pinal,
This is a slightly strange question might be.
But One of my friend has written storedprocedure with a
couple of Update and Insert statements.It seems to return a
value of 0.This seems quite natural but is there a way to
suppress this behaviour or alter it in any manner.Please help.
Since he doesnot want any return value.
Sirish,
We can make out the actual reason unless we know what business logic / stored proc is doing ? Paste the proc if possible for further investigation
hi…
i am a programmer from indonesia
your infomation very very very very good………………
thank a lot
;-) pokoknya keren banget deh…
Hi
I am maintaining Simens Hospital Information system for a leading Medical college hospital in India. They are using lot of temporary tables for reporting the output after the insertion to the table . I would like to know wheather it will affect the peformance. Another thing they are using different date format in different tables like number in one table, date format in another and string. expects your reply.
Regards
Pradeep
Hi pinaldeve,
Just now I see ur sqlauthorith.com very useful for me. Recently I am learning sql server 2005 (DBA). Can u tell d/w sql server 2000 and 2005 and features of 2005.
Am waiting for ur reply.
Thank You,
R.Sivagurunadhan
Hi pinal,
Thought you might be able to help me out.
Is there a way to copy results of a union of three queries into temp table.
It doesn’t work when I try. This is what I tried.
select * into #temptable from
( query 1
union
query2
union
query3)
I get a syntax error saying – Incorrect syntax near ‘)’.
any other suggestions welcome -:)
cheers,
RK
Hi RK
You are missing alias of subquery.
select * into #temptable from
( query 1
union
query2
union
query3) T
Should fix it.
Regards,
Pinal Dave
hi pinal sir
i have a problem in Sql server 2005 plz solve my problem
as soon as Thanks
Problem :
How to store an images in sql server data base table( directly like insert into ..) using DML Command
Read about OPENROWSET function in SQL Server help file
It has example
Hi Pinal,
I have a problem. I have a 80thousands records table and I have to export it to an XML file. The problem is that the data must be in a defined structure, I’m using FOR XML EXPLICIT but how can I export the data to an XML file?
Regards
Hi Pinal,
I have installed SQL Server 2008 Express version on my machine and just baffled not to see “SQL Server Agent” in Management Studio. I didn’t stopped investigating though…., I checked services.msc and found the SQL Server Agent Service in stopped state and was end up with error while starting…
One new thing I have noticed was the “Policy Management” that has been newly added in katmai, is there any link between PM & SS Agent
Thanks
Hello Pinal
I am working on the project for the HR team.. U can say, We are going to build the Resume bank for them.. And i have the responsibilty to handle complete Database… our main objective is to make the search more faster and efficient according the keyword (eg. java AND .net)..
Sir I would like to know about .. What is the best way to store the complete Resume of 1 person in the SQL Server Feild.. Should i go for nchar,nvarchar,image or something else.. please suggest me
what will be the best way to store the resume so that search will be faster and performance will not be affected.
Thanks
Hi Pinal,
since last few months i am reading your blog. I am interested in it. I have 1 question…
There are two tables.. Customer with columns custid,custname,custadd and another table Product with columns prodid,prodname,prodsupp.
Now i want to know from sales table which is Sales with
custid,orderid,prodid,totalqty,totalamount.
I want to know know how many customers have ordered 1 product how many have ordered 2 products and how many have ordered 3 products and so on……
Please let me kow ASAP.
Thanks in advance.
Hey, Dave. I am going to starting blogging my TSQL tricks and snippets of codes and opened a blog on WordPress, but I am trying to find the best way to display my code.
How do you get your SQL to not lost its color coding?
@Someone I dont remember
Yesterday I was reading at article in SQLAuthority , person asked script to find out jobs that are scheduled today.
here is the script.
use msdb
select A.name Job_Name, ‘Will be running today at ‘+substring( convert(varchar(10), case when substring (convert(varchar(10),next_run_time) , 1 ,2) > 12 then substring (convert(varchar(10),next_run_time) , 1 ,2) -12 else substring (convert(varchar(10),next_run_time) , 1 ,2) end),1,2)+’:'+substring (convert(varchar(10), next_run_time ),3,2)+’:'+substring (convert(varchar(10), next_run_time ),5,2) ‘Scheduled At’
from sysjobs A ,sysjobschedules B
where A.job_id = B.job_id
and substring(convert(varchar(10),next_run_date) , 5,2) +’/'+
substring(convert(varchar(10),next_run_date) , 7,2) +’/'+
substring(convert(varchar(10),next_run_date),1,4) = convert(varchar(10),getdate(),101)
– and ( substring( convert(varchar(10), case when substring (convert(varchar(10),next_run_time) , 1 ,2) > 12 then substring (convert(varchar(10),next_run_time) , 1 ,2) -12 else substring (convert(varchar(10),next_run_time) , 1 ,2) end),1,2)+’:'+substring (convert(varchar(10), next_run_time ),3,2)+’:'+substring (convert(varchar(10), next_run_time ),5,2)) > substring (convert( varchar(30) , getdate(),9),13,7)
Thanks and sorry for confusion.
Regards
IM.
Imran this is good comment.I have modified your script a bit as it was not giving accurate time for schedule job if the time was less than 12 hours.
Again, Good Script.
Regards,
Pinal Dave
Hi,Pinal
I am a Jr. DBA Working in SQL SERVER 2000.Please Give me a All Information of MCDBA Exams.
Thanks in Advance,
Regards,
Fazal Vahora,
Sorry Sir but a link in your web site at ur home page is placed incorrectly………i give u the location and the link given in ur site,,may be i m wrong but take a look at it?
SQL SERVER – Interesting Interview Questions – Revisited
December 17, 2008 by pinaldave
SQL SERVER – Interesting Interview Questions At that place the link goes onn,,,,
http://http//blog.sqlauthority.com/2008/12/07/sql-server-interesting-interview-questions/
sorry if i said some thing wrong,,,
thanks///
Hi Pinal,
i need to use statement inside a stored procedure in sql 2005.Is there any way to accomplish this task?
Or can you advise me on how to create a global stored procedure which will be commonly accessed by multiple databases on same server?
Thanks ,
Andy…………….
@anurudha deshpande.
I dont know what you mean by first statement.
By Answer to your second question,
Yes, it is possible to have a stored procedure that can be used in multiple databases.
Create that stored procedure in master database. Name of this stored procedure should start with sp_ …..
When you call this stored procedure from any database it will execute.
Based on the actions the stored procedure performs, you might want to script that way. Like if database name is needed in that stored procedure, then you have to pass database name as parameter.
If you need any help, please leave a comment I will show you an example.
Regards
IM.
Hi Dave
How do you do. well can u help me in my problem that how can i find tables related to each other using primary key & foreign keys as their are no of other tables in the database
i am new to sql Database’s as for my first chance to the sql as a database developer and i need to combine all the tables as for my requirements as i need to update users for their reports for which i need to check the table name as per requirements via command
select * from information_schema.tables
where table_name like ‘%employee%’
please help me with my query to find all the tables that are interrelated to each other
Thanks & Regards
[...] receive good question on blog, however, I do not always receive good answer for the questions. Imran Mohammed is one SQL Expert who spend his time helping SQL Community on this blog. I always read his answers [...]
how to import particular columns from .xl (or) .CSV (or) .txt file to SQL server table using bcp xp_cmdshell .
Use BULK INSERT
Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
Hi
i have a Task of sending mails from Sql Server…;) which i had done using SMTP. but want to Send E-mail from SQL Server using Exchange Server there i dont have SMTP.some where while googling i have read that you dont have SMTP on Exchange server as it is more advance then this SMTP. So is it possible to send mail using Exchange Server,
If yes then please let me know if any artical is there or not and please give me some Configuration regarding this.
and Do let me know that How to do it..!!?
Sir, please help me out with this.
Thanks in Advance
Parth Rawal
Dear Dave,
How do you partition a large table with parents and child refrential integrity with 4 other tables. In some cases the table in quetion is child and in some cases it is the parent of 4 other tables. How can I move the table into partitions?
Thanks,
hi pinal,
how to read sql server password.
the scenorio is our client changing the sqlserver password monthly once. now we hardcoded the username and password in project for connection.(VB using). each time we have to change the coding and release exe. so avoid this from where i can read sqlserver password and pass as parameter to project
please help me
Hariharan
Hi
Pinal
In My database I have some 40000 rows now i need to chage the descreption for some products
I want to update the data on where condition
Plz help how to update the data
Basing on Where condition
Ex
If ProdcatID =1 then I want chage the descreption
Hi.. Is there any way to insert a bulk data (say 1 billion rows) from 1 table to another table without involving the Transaction log. The issue here is we dont have enough space for the log files and log always slows down the process too. The operation we are carrying out is not of that high risk, hence we can forego the transaction logs. Please comment.
Hi,
I Implemented fulltext indexing on seven columns in a table.I used contains keyword.Initially it worked fine but when records in the table started increasing search becomes slow.
can you please suggest me how to improve the search performance. I used sql server 2005. please help me
Hello Pinal,
I have a stored procedure which is backing up all databases but for some unknown reason its creating two backup sets and appending the second set to first one so for database of 5GB I am getting a backup file of 10GB I am completely lost on this n need quick help. Thanks
THis is extract from the Backup Log
Starting Full Database Backup of Database: WSS_Content @ 12/19/2008 21:00:51 [SQLSTATE 01000]
10 percent processed. [SQLSTATE 01000]
20 percent processed. [SQLSTATE 01000]
30 percent processed. [SQLSTATE 01000]
40 percent processed. [SQLSTATE 01000]
50 percent processed. [SQLSTATE 01000]
60 percent processed. [SQLSTATE 01000]
70 percent processed. [SQLSTATE 01000]
80 percent processed. [SQLSTATE 01000]
90 percent processed. [SQLSTATE 01000]
Processed 673440 pages for database ‘WSS_Content’, file ‘WSS_Content’ on file 1. [SQLSTATE 01000]
100 percent processed. [SQLSTATE 01000]
Processed 7 pages for database ‘WSS_Content’, file ‘WSS_Content_log’ on file 1. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 673447 pages in 103.021 seconds (53.550 MB/sec). [SQLSTATE 01000]
Processed 673440 pages for database ‘WSS_Content’, file ‘WSS_Content’ on file 2. [SQLSTATE 01000]
Processed 1 pages for database ‘WSS_Content’, file ‘WSS_Content_log’ on file 2. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 673441 pages in 95.846 seconds (57.559 MB/sec). [SQLSTATE 01000]
Verifying Full Database Backup of Database WSS_Content [SQLSTATE 01000]
The backup set on file 2 is valid. [SQLSTATE 01000]
Deleting backups older than 1 days from the “E:\MSSQL.1\MSSQL\BACKUP\WSS_Content” Folder [SQLSTATE 01000]
Completed Full Database Backup of Database: WSS_Content @ 12/19/2008 21:00:51 [SQLSTATE 01000]
thks a lot sir..
ur website gives more information in easy manner..
Hello,
I’m planning to write a schedule a script for Tlob backup in SQL server 2005. i have to do following 4 steps in the script
1. Dynamically detect FULL and Bulk-logged recovery modes databases in the instance and backup the database transaction log for those databases only.
2. If a FULL or Bulk-logged recovery mode database is added it will automatically be included the next time the job runs.
3.If a database is changed from Full to Simple recovery mode it will automatically be excluded the next time the job runs.
4.The job should skip databases with the following states: loading, pre-recovery, recovering, not recovered, offline, read only, emergency mode
5.As a last step the job should delete transaction log backups older than 3 days on disk
please help me if you have any
Hello,
I’m planning to write a schedule a script for Tlob backup in SQL server 2005. i have to do following 4 steps in the script
1. Dynamically detect FULL and Bulk-logged recovery modes databases in the instance and backup the database transaction log for those databases only.
2. If a FULL or Bulk-logged recovery mode database is added it will automatically be included the next time the job runs.
3.If a database is changed from Full to Simple recovery mode it will automatically be excluded the next time the job runs.
4.The job should skip databases with the following states: loading, pre-recovery, recovering, not recovered, offline, read only, emergency mode
5.As a last step the job should delete transaction log backups older than 3 days on disk
please suggest
Hi Pinal,
I had for 2 days this problem and I tried almost everything.
I have SQL 2005 with the following info:
Microsoft SQL Server Management Studio 9.00.4028.00
Microsoft Analysis Services Client Tools 2005.090.4028.00
Microsoft Data Access Components (MDAC 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
After installing MySQL which uses the port 3306 (and is working fine), now my MS SQL 2005 is not connecting and I can not Start the service SQL Server.
While trying to connect to my SQL server by running the Sever Management Studio it is giving me:
….SQL Server does not allow remote connections …Named Pipes Provider, error: 40 – Could not connect; Microsoft SQL Server Error: 2
I tried all posibilities, runing Local connections only, and/or local and remote. I use this server only for my own use, not shared and not used by remote.
Please advise,
Abe
Hi
i want to import data from excel to sql server 2005.I am dong it by using DTSWizard, butthe problem with DTSWizard is that i am lossing the data in transferring.
What is the best way to copy data(without going into the detail of data type) across?
I am strugling to od so and my application is stuck.Can anybody help?
How did you know the data were lost?
Did you get any errors?
Hi Pinal,
I am using sql 2005.
I want to search particular word in a column. e.g
Suppose below table
Product_Id Product_name
101 New bath soap 200g
102 Old bath soap 100g
103 Bath soap 125g new
104 Bath soap 125g old
105 New bath soap 200g gold
106 New bath soap 125g Rose
Now i want to search with keyword like “bath” ,”200g” ,”125g”, “soap” etc.
Please advice me.
Thanks in advance
Please use the wild character(%).
Select c1,c2 from t1 where product_name like ‘%bath%’
Hello Sir,
I got the solution.
Hi,
i cannot past into the microsofot office 2003 .
i can only past into an text .txt and all other applications, like word excel .ect.
only i cannot when i want to past in the microsoft office outlook .
thx if you have any resolve for this problem ..ca
roger.
m using vb.net and sql server 2000 with windows authentication mod on XP. m getting error while connection from client “Cannot open database request in login ‘accounts’. Login fails. Login failed for user ‘Server\Guest’.”
plz suggest me for vb.net and sql server 2000 which authentication i shd use. how i can resolve the problem. my connection string is as under.
cn.ConnectionString = “data Source=server;database=accounts;Integrated Security=true;user=sa;password=;Connect Timeout=30;”
Hello Pinal,
My DB server (HP prolaintG5)faced harddisk problem. OS got corrupted. database was in suspect mode and server rebooted. Cannot access database.
By any chance i can recover this database?
Please reply.
Thanks in advance.
Kinnari.
Sorry Pinal,
I forgot to mention error in my previous mail that i occur when i do anything to recover the db
follwoing error:
I/O error (bad page ID) detected during read at offset 0×00000000030000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\IT_Data.MDF’.
[db server faced HDD problem. OS got corrupted. server rebooted when db was in suspect mode]
Thanks.
Kinnari.
Pinal,
I have a query.How to fetch last two years from a current year. for instance,current year is 2008 i need to fetch 2007 and 2006.
Looking forward to hear from you.
Regards
Ram
where
date_col>=dateadd(year,datediff(year,0,getdate())-2,0)
and
date_col<dateadd(year,datediff(year,0,getdate()),0)
hi i downloaded your file Data Synchronization Stored Procedures and views – Part II.sql. I search your blog to read more about how to implement this but it seems like you removed it. I would like to learn how to synchronize two database without having to buy sql server. I mean, just use the express edition to synchronize two database. can i do that?
Hello
Mr . Pinal Dave first of let me Congratulate u for getting married ‘ wish u happy married life ‘. i’ll Come to point now
Mr pinal , some times you are not taking care about some comments/Queries who eagerly wait for ur solutions . you are avoiding them , what is the reason for it tell me right away ..what is the mistake they ‘ve done .
some time before Ritesh , Paul and shaik they asks the question , but u didn’t give the answer on the blog. plz let me know.
Thank you very much.
Dear John,
Thank you very much for your wishes.
I always take time to answer all the question asked on this blog. There are plenty of the comments I receive every day. More than 300 comments every day. I publish all of them, as it creates good community between readers. Many time smart readers like Imran and others right away answers other readers and some time good communication is on blog comments itself.
I reply most of the answers either by email or commenting on blog itself. I do get many many calls every day and I take time to answer them as well.
I have visited many readers work place to solve their problem as well.
Let me know if you want me to answer any question and I will reply in 48 hours.
Kind Regards,
Pinal Dave ( http://blog.sqlauthority.com )
Hi Ram,
You can use this query to get the years as you want:
SELECT YEAR(GetDate()) As CurrentYear,
YEAR(DATEADD(yy,-1,GetDate())) As LastYear,
YEAR(DATEADD(yy,-2,GetDate())) As LastOfLastYear
Thanks,
Tejas
Hi pinal,
I have to create a Stored proc which selects data from Two tables which lies on two different server.
I need to do comparision sort of work..
how this can be possible.,.??
Or either ,
can i manage this from my code behind side..while working in Asp.net ?/
You need to setup a linked Server or use OPENDATASOURCE
Read them in SQL Server help file
i installed sql server 2005 in my leptop (DELL 1525)
but while installing on my local machin i dont know what to write in domain
so i let it remain blank.But know i getting error microsoft
sql 18456 while connecting to the batabase engin
im loging through window authentication
please help me
bcz im installing first time
ill wating for u r reply
thanks
hello,
i am woking on such projects which needs to know how many times table altered and which field has been changed /modify. I tried using following queries
select top 10 *
from ::fn_dblog(null, null)
where Operation = ‘LOP_MODIFY_COLUMNS’
but it does not retrive any thing which can be useful to get such information.
I can not add any other table like audit manager which tracks on such operation. And if I add such table then also my previous changes i could not get.
Plz help me thanks in advance.
:-)
Hello pinal,
I just wanted to know one resolution from your side. I have tried to change the SQL-Server Name by below commands
@@Servername — to get the server name
sp_dropserver ‘oldservername’- delete the old server name
sp_addserver ‘newservername’ ,local – add a new server
net stop mssqlserver — stop the sql serever
net start mssqlserver — start the sql server
net start sqlserveragent – start the sql agent
When I check in query analyser by “Select @@Servername” command it already changed the server name with new name but it does not change the SQL Server Management Studio Object Explorer with the new name. How do I change the name here???
Here it is displaying the old name of sql server?? Please let me know how do i Change??
Thanks
Himanshu
Hi Pinal,
Is it possible to update procedures or functions on sqlserver database using a batch file. for example, i have 100 procedures in a folder (proc1.sql, proc2.sql ….. proc100.sql) and i need to update all these procedures in test database using only a single batch file. Is this possible?
–Irfon
Hi Pinal,
I got the solution for what i had asked earlier. thanks anyway
set source=C:\procedures\
for %%a in (“%source%/*.sql”) do (
echo %%a
sqlcmd -S compname -U username -P password -i “%%a”
)
pause
Hi sir,
How can I connect to MS SQL Server database using SHOCKS host?
Mai Thanh Hai
Hello Sir,
I am a beginner in SQL 2005 and would like you to help me out in the following issue.
I have two columns both of datatype ‘datetime’ as start time and end time. I want the time difference from the two columns and then add the hours or time what I have got.
I have already got the time difference from below query
select convert(varchar(5),schetm-schstm,114) from psschtbl
here, schetm -> End time
schstm -> Start time
psschtbl -> Table name
Now how do i get the add the time i have got from the above query.
Please Help.
How to select a record which has the row number 5 in the database?
I have a database named db_ERP . In that database number of tables and SPs are there.In that database i modified field names of some table columns.But i have a lot of SPs using those table fields.I need a query for getting the Sps which are now incorrect.
EXEC sp_depends 'your_table'
Whats is the main purpose of the SQL Server 2005 backward compatibility ?
Himanshu
Hello pinal,
I just wanted to know one resolution from your side. I have tried to change the SQL-Server Name by below commands
@@Servername — to get the server name
sp_dropserver ‘oldservername’- delete the old server name
sp_addserver ‘newservername’ ,local – add a new server
net stop mssqlserver — stop the sql serever
net start mssqlserver — start the sql server
net start sqlserveragent – start the sql agent
When I check in query analyser by “Select @@Servername” command it already changed the server name with new name but it does not change the SQL Server Management Studio Object Explorer with the new name. How do I change the name here???
Here it is displaying the old name of sql server?? Please let me know how do i Change??
Thanks
Himanshu
Dear sir
I saw your web site and exicited to get some solution so sending mail.
For the last few weeks I have been suffering with the problems of SQL Host_ID().
This query (SELECT Host_ID()) is generating Unique host id on each Exe on Windows Application
Where as it’s not generating from Asp.Net C# on each user Login. Every time it return same Host_Id on different browser. I also Configured in IIS Pooling recycle worker process but it returns different Host_ID on each request which is also not good.
I want Onces Request should create new host_ID with in that browser and until and unless the browser is closed the same host_ID should be remain at last and If different browser is opens, it’s calculated next request and again New Host_ID should be created.
Onces I found that This all happening due to the Pooling method. So If you have any solutions then plz forward me..
Thank you
Rupendra nath Shrestha
Software Programmer
Hi sir,
It’s Nicework.
I’m a strating learner. what way i can learn.
Whats is the main purpose of the SQL Server 2005 backward compatibility ?
plz send to me, all the interview questions.
ThankQ Sir.
Hello sir
im begineers of sql server 2005
i know basic sql but i do know how to write stored procedures ? can u help m eplz
Read about Stored Procedures in SQL Server help file
It has examples too
Hello Pinal.
I have a schema.csv file. Is there a way to create a blank database using that file?
I am just starting off with 2005 and am still in the process of learning.
Thanks and Regards,
Vijay.
hi Pinal
Can you please explain why indexes having avg_fragmentation_in_percent > 98 is not changing the value even after reindexing or rebuilding.
Came to read that small tables will not affected by rebulding or reindexing
So I am just mentioning some more details like page_counts below.
name avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count
xxxx1 98.8538681948424 346 7.5606936416185 2616
xxxx2 98.6381322957199 511 6.5812133072407 3363
xxxx3 84.3016069221261 684 9.25292397660819 6329
All the advice and tips will be greatly appreciated.
Hai, Pinal
Nice to see your great articles.
I’m new in SQL Server, so Iwould ask how to get value from 2 column to be 1 column.
I used “select Column A + ‘,’ + space(1) + column 2″
the problem is when there is no value in colum2.
How to split/delete string ‘,’ if the value of column 2 is null
select Column A + coalesce(space(1)+','+column 2) from your_table
How can i delete the MDF and LDF file from c: Drive.It shows a error messge “Cannot Delete tempdb2:It is being used by another person or program “….
Sir plz help me out to resolve this problem.
Thanks
Abhishek
Why do you want to delete?
You can make use of DROP database command
Hi Pinal,
I have to pull the records from one server’s table to another server’s table.
I am using SSIS package to do this.
But the problem is one of the column is of Datatype “SQL_Variant”.
Because of that I am getting this below warnings..
. Warning 1 Validation warning. DFT-ROW_OpenAccessor: {8872BE20-F843-4D47-A2A1-D6364E528FA8}: The input “OLE DB Destination Input” (29) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead. OpenAgreementRefresh.dtsx 0 0
..how should i avoid them!!
Hello,
I need to do a data backup (complete backup means records with table structure) of ONE PARTICULAR Table in SQL Server 2005.
Is it possible? if yes,please explain the method.
Thanks & Regards,
Ravinder
1 Scrip the table using Generate Script method
2 Use bcp to copy the data to a text file
select col, right(col,charindex('_',reverse(col))-1) from your_table
How to get the last part of a stirng after the last occurence of the underscore
eg: End of Specific Month_9_LatestDate
I need to get just the LatestDate from the above string. I have several other different strings of same type and I need to get the last part of the string after the last underscore.
Any help is appreciated. Thanks in Advance.
Try this code
select col, right(col,charindex('_',reverse(col))-1) from your_table
Hi Pinal,
I’m uploading data from a flat file of 2 lakhs records to a table also the table contains more fields than in the flat file..
Can you please suggest the best way to do this …
Thanks ,
Bhanu Prasad M
You can use BULK INSERT
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
Hello Pinal,
I am working on an application and learned something about SQL Server handling the default constraint.
If you have a date column in a table which has a default of getdate() specified, I would expect that whenever a value is not given the default constraint will become effective and the current date is filled in.
However when I tested, I learned that the value specified in the default value is only used when the column is omitted from the SQL statement. If the column is specified with a NULL then the default value is not used.
example : insert into T1 (c1, c2) (c2 is a date column)
values (1, null)
insert into t1 (c1) values (2).
only the 2nd row c2 has the default value, not the first row.
Your thoughts?
Regards,
That is the expected behavior
You can give any valid value to a column that has default value. However default value will be considered only if you dont input value to it
I was asked in an interview and want to have some feedback to see if better method exists to do this.
We have 2 tables.
table 1 – mgr (id, name)
table 2 – emp(id, mgrID, name)
where mgrID is foreign key from mgr table.
Now ,
how to find the name of manager who has maximum number of employees.
My solution :
select m.* from mgr m
join (select top 1 m.id, count(m.id) as cnt
From mgr m
join emp e
on m.id = e.mgrID
group by m.id
order by 2 desc) x
on m.id = x.id
is there a better solution ?
Thanks
Nanku
please check if this helps :
table 1 – mgr (id, name)
table 2 – emp(id, mgrID, name)
select m.*, e.emp_count
from mgr m
inner join (select top 1 mgrid, count(id) as emp_count
from emp
group by mgrid
order by 2 desc) as e
on m.id = e.mgrid
In my product there in one procedure in which we are calling three different procedures from three different databases used to fetch client’s ledger balance and printing data by using XML. If I give duration of six months in front end its give server time out error. ( Not scrip time out) If I run same procedure from back end get output within 30 seconds and also in range of 15 days report is working properly.
Hi Pinal,
Thought you might be able to help me out.
Is there any possibility to get the execution plan Subtree cost of the Stored procedure by using any sql script or query.Actually i need to use this from front end and to display the subtree cost in my windows application.
Hi,
I always used to go through all your blogs, and its really helpfull.
Can you please provide me some document or presentation on sql server 2005.
Thanks
Anil
I was wondering if you have script ready to generate a script of all stored procs changed in 2 days. Since we have the names now, thanks to sql server2005.
Can you help me with that..
Hi Pinal
I am using MS SQl Express 2005 Database where I have written own DTS Program to Transfer data from flat file to MS SQL 2005 Express. some reason , there are some tables not empty from DTS in MS SQL table. those tables have more than 300,000 records each.
do you know the reason.
Please help me
Regards
Sutha
Hi pinal,
I want to install the SQL Server 2005 free edition on my home PC. can you please tell me from where i will download my free copy of SQL Server 2005.
Regards
Saurav
Go google/bing search on “sql server 2005 free download”
I want to grant permissions to the function GetFirstNames to the user “domain\xyz”
GRANT EXECUTE ON [dbo].[GetFirstNames] TO [domain\xyz]
How do i do it by passing as parameter
Some thing like this
GRANT EXECUTE ON [dbo].[GetFirstNames] TO [@user]
declare @user varchar(100), @sql varchar(1000)
set @user='user name'
set @sql='GRANT EXECUTE ON [dbo].[GetFirstNames] TO ['+@user+']'
exec(@sql)
Hi Pinal,
I have just installed Sql Server 2008 server. When I am trying to create Maintenance plan from management studio, when I click on save butoon getting error catastropic failure message.
How I can resolve this error, I am able to too every singal function like backup running query everything working fine.
I am not DBA beginner only.
Thanks,
Hi pinalbhai,
Congratulations ,
This is maitry , deval’s cousin . Best wishes from me, Atul uncle & aunty for successful married life. I was searching some sql query on net to findout day starting from sunday from the hiredate while doing this i found ur site.I had done mca and i m dba beginner. U r knowledge in sql is tremendeous , i just come to know this site about ur educational success. Very few ppl from india reaches upto this stage. Now onwards whenever i will stuck up i will send u my queries .
Cheers,
Maitry.
DECLARE @iExchangeID INT
SET @iExchangeID = 1
SELECT CASE WHEN @iExchangeId != 5 THEN CAST(1200.0001 AS DECIMAL(19, 2))
ELSE CAST(1200.0001 AS DECIMAL(19, 4))
END
hello Pinal sir,
Plz help
here i am expecting an output as 12.00
but i am getting 12.0000
i dont want last 2 extra zeros
now if i modify the query as follows then i am getting the desired output but i will have to change the second cast/ convert statement and it dosent solve my pupose…
DECLARE @iExchangeID INT
SET @iExchangeID = 1
SELECT CASE WHEN @iExchangeId != 5 THEN CAST(1200.0001 AS DECIMAL(19, 2))
ELSE CAST(1200.0001 AS DECIMAL(19, 2))
END
i want output as 12.00 when iExchangeID!=5
and 12.0001 when iExchangeID=5
what is the solution??????????????????
Hi Aditya..
I have modied your script a bit..
Hope it helps you..
DECLARE @iExchangeID INT
DECLARE @myval decimal (10, 4)
SET @iExchangeID = 1
SET @myval = 1200.0001
IF @iExchangeId != 5
SELECT CAST(CAST(@myval AS varbinary(20)) AS DECIMAL(10, 2))
ELSE SELECT CAST(CAST(@myval AS varbinary(20)) AS DECIMAL(19, 4))
Shree
Hi ,
I am an ASP.net Developer , i am working on a Software that has a SaaS Module. In our software there are lots of instance where i need to insert multiple records into database. While inserting data into database i wanted to know, how can I increase the performace/ integrity of my appplication when then are multiple insert into database.
1. I am Processing all the data Initially and then using transaction to insert data.
2. I can pass all the data to 1 store procedure and in the store procedure i can have the transaction that will loop all the data and inset it into database.
Please help me which is the better option or is there any option better then this.
awaiting reply.
@Dasharath Yadav,
I will go for the second option : I can pass all the data to 1 store procedure and in the store procedure i can have the transaction that will loop all the data and inset it into database.
Performance of this will not be optimal, however, it will give you better control over error handling.
If you are confident about handling error in processing all the data, I will suggest to go for option 1.
Overall, as I am not sure how things will work, I will go Row by Row. This will prevent me for having any accidents.
One more advantage it has is it will not lock complete table and put lock at row level, so your database table will be available when data is inserted.
Regards,
Pinal Dave ( http://blog.SQLAuthority.com )
Hi
I work with VisioalStudio6 and sql2005
the connect with the sql i do with ADO
I am excute ,a transactions that contain severl store-pocedures,from vb6 aplication.
and i want to catch the error if done in transaction. if one procedure is succeeded and the other is fail i did not get response aboute the failer on.
or if i excute sp from my vb6 application,if the sp
contain 2 commands “isert” and “delet”.
the “insert ” success and the “delet ” fail ,
the fail MSG did not over to the vb6 .
how can i catch the Messages
Thank you in advance
yacov
HI Pinal,
I wanted to create a directory and share this directory to users and also give permission to this directory.
I was able to create the directory using
EXEC master.dbo.xp_cmdshell ‘MD E:\Praveena’
But now am not able to share this directory and also give permission to this directory using a sql server script.
Can you please help me out in doing so
thanks in advance,
Praveena
HI Pinal,
I wanted to create a directory and share this directory to users and also give permission to this directory.
I was able to create the directory using
EXEC master.dbo.xp_cmdshell ‘MD E:\Praveena’
But now am not able to share this directory and also give permission to this directory using a sql server script.
Can you please help me out in doing so?
thanks in advance,
Praveena
@shree
what was the problem with my scrpit ??????????????
hai
my name is pavan.i am trying job in sql dba
can u tell guid me regarding that
and i need some interview questions in sql dba
regrads
pavan
Hi
Thanks for the great site.
I am trying to install SQLServer2005 on my Windows Vista Business(32Bit) OS system
Is vista supports this or not? Can you please assist me in this ?
thanks..
venkat
@Venkat,
From what I believe and what I have read online, I can tell you , any Edition of SQL Server 2005 can be installed on any Edition of Vista.
1. It will prompt you an error saying some components are missing, what you have to do, once you install SQL Server 2005, you have to apply SP2.
2. If you are trying to install SQL Server reporting services then You should have IIS 7.0
Look on internet, there are plenty of forums.
Which Edition of SQL Server 2005 you are trying to install, like : Enterprise Edition, Standard Edition, Developer Edition, Express Edition, Evaluation Copy ?
On which Edition of Vista you are trying to install , like Vista Ultima, Vista basic Home, Vista Home premium, Vista business ?
Please provide information above information, if you are getting error while installing, please post that error here.
Regards,
IM.
hai pinal ,
I have a problem in Sql server 2005 ,
iam using Linked server For Data transaction through internet
I Set the MSDTC In Both Server and Node System..
Below i mension Stroe Proc
I am using Linked server For transferring data using MSDTC
Alter Proc [dbo].usp_Select_TransferingDatasFromServerCheckingforExample]
@RserverName varchar(100), —– Server Name
@RUserid Varchar(100), —– server user id @RPass Varchar(100), —– Server Password @DbName varchar(100) —– Server database
As
Set nocount on Set Xact_abort on
Declare @user varchar(100) Declare @userID varchar(100) Declare @Db Varchar(100) Declare @Lserver varchar(100)
Select @Lserver = @@servername Select @userID = suser_name() select @User=user
Exec(‘if exists(Select 1 From [Master].[' + @user + '].[sysservers] where srvname = ”’ + @RserverName + ”’) begin Exec sp_droplinkedsrvlogin ”’ + @RserverName + ”’,”’ + @userID + ”’ exec sp_dropserver ”’ + @RserverName + ”’ end ‘)
declare @ColumnList varchar(max)
BEGIN TRY BEGIN DISTRIBUTED TRANSACTION
set @ColumnList = null
Select @ColumnList = case when @ColumnList is not null then @ColumnList + ‘,’ + quotename(name) else quotename(name) end From syscolumns where Id = object_id(‘Crnot’) order by colid
Set identity_Insert Crnot On exec (‘Insert Into ['+ @RserverName + '].’+ @DbName + ‘.’+ @user +’.Crnot (‘+ @ColumnList +’) Select ‘+ @ColumnList +’ from Crnot ‘) Set identity_Insert Crnot Off
COMMIT TRANSACTION Select 1 End try Begin catch if (@@ERROR 0) Begin
if @@trancount >0 Begin Select 0
Rollback transaction END End End Catch
Exec sp_droplinkedsrvlogin @RserverName,@userID Exec sp_dropserver @RserverName
I am Getting An eroor “No Tansaction active”..
Plz Help Me . it is very Important to Me
Then Only i can Complete My Project …
I Just Create An Data Base Mail Account, In SQL 2005 all are Set I configure This Account on My Local Computer all goes well well i when see these process
SELECT *
FROM sysmail_log
GO
then there is a messeage
message could not sent to the recepient,,,,,
This is because the connection is refused by the targeted machine (I.P.)…so
i cant figure it out wat is this,,,
Hi Mr. Pinal,
Thanks for providing this opportunity for all. Espacially
this site is very useful for me.
How can I track client machine IP address from Server(using SQL Server 2005). Automatic IP should insert on particular table.
I tried on this but I am getting server IP not Client machine IP.
Hi Pinal
I am Sunil from Chandigarh.
tell me any website which provide me good knowledge of SQL-Server from beginner level to advance level.
Thanks and Regards
Sunil
We are looking to migrate a mySQL db to SQLServer 2008. This db is about 100 gb in size and has several large (330+ million row) tables.
What hardware setup do you recommend to optimize performance. What kind of disk setup (raid 1, raid 0, raid x, etc), recommended processors and count, how where to place the os vs the data files vs the tempdb files vs the tlog files.
Thanks so much!
David Fordham
Hi,
i have some questions.
i want to move a database from one server to another databse in sql 2008
and to create a script to import data into the database and able to run automatically to update the database.
thanks
Hi,
i have another question,
i have a query which is displayng results
such as value 2, 3,6,8 ,15, etc. and for each client the value is different.
Now , i want to display the difference in the same query as well
then the output will be
value dff
2 0
3 1
6 3
8 2
15 7
Thanks
Search for Running total in Google
Pinal:
I get following error while installing SQL Client tools. Please help me!!!
TITLE: Microsoft SQL Server 2005 Setup
——————————
There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=50000&EvtType=packageengine%5cinstallpackageaction.cpp%40InstallToolsAction.10%40sqls%3a%3aInstallPackageAction%3a%3aperform%400×643
——————————
BUTTONS:
OK
——————————
Thanks for putting up a truly wonderful reference site for SQL Server. As part of my new job responsibilities, I have started on the DBA path and your site has been very valuable.
Great Job!
- Marco
Pinal:
I have following table
complain(autoid int ,ComplaintDate dateTime
ComplaintType (Foreign ,Product and Service) varchar(15))
by this table i have to build the following out put using PIVOT
Foreign
—————
MonthName ThisyrTotal LastYearTotal Change
January 10 10 0
February 8 12 -4
March 11 9 3
April 5 8 -3
May 8 14 -6
June 12 15 -3
July 6 11 -5
August 10 4 6
September 13 8 5
October 9 9 0
November 7 4 3
December 12 4 8
—————————————————————–
Product
—————
MonthName ThisyrTotal LastYearTotal Change
January 50 48 2
February - - -
March - - -
April – - -
May - - -
June - – -
July - - -
August - - -
September - - -
October - – -
November - – -
December – - -
So, can u give any idea or any sql query for this.
Please help me.
Thanks.
hi pinal
i have a doubt .
i am stopping and starting the sql server serices.
does is there any system table or any view to find out the details.
thanks & regards.
Hi Dave,
InSQL Server 2005 there is a table with primary key that contains two columns. I need programmatically add third column to this primary key. How can I do this?
Thank You for advance,
Igor
Hi Pinal Dave
In the sql i have table its name temp with 2 colomns
id (samllint) and VALUE_NAME(varchar(5)).
–
i have procedure with the name spInsTemp
-
create proc spInsTemp
as
delete from temp where id=1
insert into temp(id,value_name) values (1,’dfdfdfdfgdfgdfgdfgdfgdfgdfg’)
-
when i execute the procedure – exec spInsTemp
i get the mesege
(1 row(s) affected)
Msg 8152, Level 16, State 14, Procedure spInsTemp, Line 4
String or binary data would be truncated.
The statement has been terminated.
–
and its ok
when i execute the procedure from vb6 with ADO
i cant get the error msg . iget only the first msg.
in this case i dont have controle on the procedure i dont know if the procedure succeed or not
please advice ,how can i get evrey msg from the sql
in transactions or Storeprocedures
Thank you verey much inadvance
yacov
Hi,
I know primary level knowledge of transactional log….I want to know more about transaction log …can i see transaction log in server like we see table ……..how it is ?
@ Aditya
Just a small change in loop….
Was it helpfull??
Shree
I need some advice and after reading your blog, thought you might have some insights.
A new server that we’re installing for Navision 2009 will have 2 73 gig drives and 6 146 gig drives. Since I want to run 2-tier, it seems like I have the following choices for setup:
1. Mirror the 73 gig drives and use them for op system (server 2008)
Mirror two of the 146 gig drives and use them for Navision
Raid 10 the remaining 4 drives and use them for SQL Server (2008)
2. Mirror the 73 gig drives and use them for BOTH the operating system and Navision
Raid 10 the remaining 6 drives and use them for SQL Server
3. Mirror the 73 gig drives and use them for op system (server 2008)
Raid 10 the remaining 6 drives and use them for BOTH Navison and SQL Server (2008)
I’d really like to find out if any of these is significantly better than any of the other. I guess left to my own devices, I would go with option 1, but I really don’t have enough experience with Raid (and Nav2009) to know for sure.
Thanks.
Hi Dave,
One column in my SQL has an integer data, something like 123456789. How to display it as $123,456,789 ?
i.e add ‘$’ symbol infront of the value and separate data with commas(,) as shown above.
Thanks
This is the formation issue which you should do in the front end application
@yacov.
You have a table temp ( id samllint , VALUE_NAME varchar(5) ).
Here column Value_Name can take max of 5 characters only. It cannot take values more than 5 characters, when you give more than 5 characters, sql server will trim the data and it will remove the extra characters from that value.
In your example, you are inserting ,
insert into temp(id,value_name) values (1,’dfdfdfdfgdfgdfgdfgdfgdfgdfg’)
here you are trying to insert a value which has more than 5 characters,
In your stored Procedure try this statement,
insert into temp(id,value_name) values (1,’dfdfd’)
This should work, because the value which we are passing into Value_name column is 5 characters.
Hope this helps,
IM.
We ran BPA scan on our new server. We got the following message. Can you please advice.
Thank you
Message from BPA scan
This message indicates that SQL Server has issued a read or write request from disk,
and that the request has taken longer than 15 seconds to return.
This error is reported by SQL Server and indicates a problem with the disk I/O subsystem.
Delays this long can severely damage the performance of your SQL Server environment.
@Tejal,
I personally never came across this error.
you can do one of following two things,
1. Google Error. copy full error message and google it as it is.
2. From what I remember when I last used BPA. Solution to error message will be included in the error message only. Error message will be in a form of link, if you click that link, it will direct you to solution page, If you are using BPA (SQL Server 2000) then try using Help File. I am sure you will find a solution.
Once solved, please post solution so that it is helpful for every one of us.
Hope this helps,
Imran.
Hye mr pinal dave,
i need some guidance on sql command. i accidently update all data in 1 table to be as one data only. may i know how i want to revert back to the previous data. or at least what is the sql command to use to make the data in that table updated according to their own data by referring to another table. fyi, the another table has one column which match another column in that affected table and the data affected is around 85000 data. should i use ‘insert’ command to update back the table. and what is the fastest way to update back the table to the original one. Thank you in advanced for ur help….
Hello Sir,
I had done 1st semester in NIIT and completed my SQL Server 7.0 & JAVA, HTML, Front Page.
SO i want to make a career in SQL DBA, So please tell me what can I do,
or how to kick my career in this field.
Best regards,
Manoj Pal
@Charry
Use this function to get the required format.
create function currencyFormat (@value int)
returns varchar(100)
as
begin
declare @index int
declare @str varchar(100)
set @str = convert(varchar(100),@value)
if(len(@str) > 3)
begin
set @str = left(@value,len(@value)-3) + ‘,’ + right(@value,3)
while(charindex(‘,’,@str)>4)
begin
set @index = charindex(‘,’,@str)
set @str = left(@str,@index-4) + ‘,’ + right(@str,(len(@str)-@index)+4)
end
end
set @str = ‘$’ + @str
return (@str)
end
Best Regards,
Muppidi.
This is as simple as
declare @n decimal(12,2)
set @n='123456789'
select '$'+convert(varchar(20),cast(@n as money),1)
But as I told earlier, this is the formation isses that should be done in front end application
how to solve the orphaned user problem please explain
@VijayaKumar,
exec sp_change_users_login ‘report’
will give you a list of orphaned users ( only for sql server logins)
in order to fix these orphaned users, run this script.
step1: First create the sql server login if it does not exists, if it does exists already then go to step2
step2 : exec sp_change_users_login ‘user_name’ , ‘login_name’
for more details look at sp_change_users_login in books online.
Hope this helps.
IM
Hi Pinal,
I need you help in writing a query. I have to return TOP N result. If the user pass me 0 (zero) then I have to return all the results. Along with this I have to dynamically sort the data as well. I have written a query but the problem is that TOP with ORDER By clause is not returning me the correct data. Here is my query. Please help me if you have any suggestions:
IF @MaxCount = 0
BEGIN
SELECT
FloatValue AS DataValue
, @EstimatedRecordCount AS EstimateRecordCount
FROM
@DataItemList DataItemList
INNER JOIN
tbDSNET_DIM_DataItem_IndexValue_Float IndexValueFloat
ON
DataItemList.[PK_idDataItem]= IndexValueFloat.[FK_idDataItem]
ORDER BY
CASE @SortDecending WHEN 0 THEN
FloatValue
ELSE NULL
END ASC,
CASE @SortDecending WHEN 1 THEN
FloatValue
ELSE NULL
END DESC
END
ELSE
BEGIN
SELECT
TOP (@MaxCount) FloatValue AS DataValue
, @EstimatedRecordCount AS EstimateRecordCount
FROM
@DataItemList DataItemList
INNER JOIN
tbDSNET_DIM_DataItem_IndexValue_Float IndexValueFloat
ON
DataItemList.[PK_idDataItem]= IndexValueFloat.[FK_idDataItem]
ORDER BY
CASE @SortDecending WHEN 0 THEN
FloatValue
ELSE NULL
END ASC,
CASE @SortDecending WHEN 1 THEN
FloatValue
ELSE NULL
END DESC
Hi venkat ..
yes vista supports sql server 2005.. install the destop engine edition..
it will work
Hi Pinal,
I need your help to develop my SQL skils.I 2007 passed out and got placed in an MNC.I am NON-IT student.I got training in unix.But in my firm they appointed as an SQL database admin.I dont have much knowledge about SQL.Please help me to develop my SQL Skills.
sir,
i installed SQL Server 2005, and in server name local server name e.g “pcname\sqlexpress” is missing, my sql connects with main server and works proper, but for local server how can i add this in the list?
Hi Pinal,
I have 2 table, table1 have 4 records with same id but diff. fields and table2 have 1 record with same id to table1 and diff fields, now when i use inner join on id then all columns from table 1 comes fine but columns for table2 have repeating values.
please suggest any query.
Hi Pinal,
I have a database named db_ERP . In that database a number of tables and SPs are there.In that database i modified field some table column names.I do the same for some other table columns also.But i have a lot of SPs using those table fields.
The columns changed not for a single table,but it done for many tables. How i can get the list of SPs which are now incorrect by modifiying the table’s columns ? .
please suggest any query as early as possible.
EXEC sp_depends 'table_name'
hello,
How to use variable content as fieldname in any sql query?
e.g.
Insetead of using select query as
SELECT OrderNo, Orderdt FROM tblOrders;
I want to provide column name dynamically like:
declare @colname as varchar(10);
Set @colname=’OrderNo’;
SELECT @colname, Orderdt FROM tblOrders;
but this query returns string “OrderNo” for all rows.
How to solve this problem? Pls suggest any solution.
This takes call for Dynamic SQL. Well, why do you want column name to be dynamic when you can easily write actual name in SELECT.
declare @colname as varchar(10);
declare @sql nvarchar(1000)
Set @colname=’OrderNo’;
set @sql = ‘SELECT ‘ + @colname + ‘, Orderdt FROM tblOrders’
exec sp_executesql @sql
But please note that, if you embed dynamic sql in a proc, this leads to a serious SQL Injection attack
Hi Sir
Its an honour to write to you. I installed SQL SERVER 2005 Enterprise on Windows server 2008 machine. After that installed Service Pack 3 directly without installing prior SPs ie 1 and 2. To connect graphically i just installed Express Management Studio things worked fine as i normally connect to the Server with my local system where i upgraded from Express to Standard. On server i dont have the SQL server management studio thus i dont have many facilities like back scheduling, SQL profiler etc. Could you please help me how to make things right at the Server end.
Thanks in advance
Emad
Hello Sir,
Am a regular reader of all the blogs discussed here. It is very useful to me and to all our friends..
Hello Sir,
I am very new for Windows and SQL. I’m working as SAP Basis Admin and have to update the SP of SQL (2005) in my current landscape.
Can you please help me in this. I searched the same in SQL 2005 BOL and net but got nothing on this. I need a step-by-step process list for the SQL 2005 Support Pack upgrade.
Thank you Sir …
Anil
Imran Mohammed
we used sql 2005. I copy past the message in google , didnt come across any soultions or any message that can guide me or point me to this BPA message.
on January 30, 2009 at 2:59 am Tejal
We ran BPA scan on our new server. We got the following message. Can you please advice.
Thank you
Message from BPA scan
This message indicates that SQL Server has issued a read or write request from disk,
and that the request has taken longer than 15 seconds to return.
This error is reported by SQL Server and indicates a problem with the disk I/O subsystem.
Delays this long can severely damage the performance of your SQL Server environment.
Anyway thank you for your suggestion.
Tejal
We currently upgraded our database from 2000 to sql 2005. We have notice some sql procedure run take 3 hours to run. We contact tech.support they requested to do maintenance on index and stattics.
We rebuild all the indexes. It still didnt help.
Another soultion they offered is
Enable RCSI. (probably the most important from a performance standpoint)
You must now activate the READ COMMITTED SNAPSHOT ISOLATION level for the upgraded database. To enable
ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON
To verify if the database is using RCSI you may run the following SQL command:
select is_read_committed_snapshot_on from sys.databases where database_id = db_id (DATABASENAME)
The output must be the number 1 (one), which means, RCSI is enabled.
we are running above sql script in single user mode. It’s been 48 hours it’s still running.
Any help appercaited to improve the performance.
Thank you
Tejal
– Hi, I have some many times benefited from the code snippets from your blog so I thought to share one mine with you
– what the … I have been doing lately on sql server ( 2005 or 2008) ?!
use db
go
select o.name ,
(SELECT [definition] AS [text()] FROM sys.all_sql_modules where sys.all_sql_modules.object_id=a.object_id FOR XML PATH(”), TYPE) AS Statement_Text
, a.object_id, o.modify_date from sys.all_sql_modules a left join sys.objects o on a.object_id=o.object_id order by 4 desc
Br,
Yordan Georgiev
P.S. Keep going with the best sql server dedicated blog !
Hello Mr. Dave,
Greetings!
I am quite new to web development and coding.
I want to try a simple function – to save data from a web from into an ms sql database called WebData (table called Test)
The web form has 4 fields Name, email, job title and company.
I want to use html and php scripting. Can you please help me with a sample code for the same.
Kind Regards,
Rishab Varma
Hi Pinal,
I am a regular visitor to your site and I got many benefits from your articles. I have a query could you please help me
I’ve a db on postgresql and i want to export the db to sql server 2005 I could export all the tables but I am unable to export Triggers,Views, Indices and stored proc’s.
Your kind help is greatly appreciated
With regars,
Raveens
I am a programmer from Libya
your infomation very very very good.
Kind Regards,
Ashour ALtaeeb
hi pinal.
u have created the most amazing, helpful,knowledgeable, excellent , conviniently understandable database material.
this could helo masses in cracking their interview and making their dreams tru.
May GOD fulfil all your wishes.
Keep up the good work.
Thanks for sharing such a great information ……
;-) Cheers to Pinal Dave
Regards
HSingh
I want to autogenrate data in a table column like ‘MMYY001′ in this format
ex:- ’0209001′
if it is march 1st then it should be ’0209001′
an what datatype can it be
Please reply asap
Why do you need this?
How are you going to use this?
Thanks in advance for your assistance.
I am defining an openquery to a linked server with a @SqlStmnt VARCHAR(8000) declare statement. I am getting a
Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string ‘
error message which I believe is caused by my query being over the 8000 limit. I have tried varchar(max), but get the same error. My select statement has about 100 lines & I’d say the avg # of characters on each line is 50, so maybe 5,000 characters…although I am also using a looping routine to use AcctNbrs in my where criteria – WHERE Account IN (‘ + RTRIM(@AcctString)+ ‘).
I believe there are 300 accounts in each batch of records in my looping routine. Not sure if this is increasing the number of character interpreted by Sql in for the @sqlstmt variable.
When I run the code and only select 10 fields it works, but now I need all 100 or so fields from the table I am querying against.
I cannot select * from the table as I have to convert some date values so I have to specify each field.
Not sure where to go from here…thanks.
If you use version 2005, make use of varchar(max) datatype
Can’t find much documentation for this out there for this. Why or Why don’t DBAs use WHERE clauses that contain case statements? Is their a performance hit or is it just something that DBAs generally look down on? It would seem logical to me to pass a parameter and depending on its value use a CASE statement to supply the correct WHERE clause. Don’t see much of people doing this out there however. I would love to hear points for and against this scenario. I am guessing it is a read ability issue, but if not what is the best practices for scenarios like this? Example below.
select *
from YourTable
where SomeColumn = case
when Condition1 then Value1
when Condition2 then Value2
else Value3
end
Dear All,
Could you please provide me SQL SERVER 2005 Interview question of DBA ( production ) not for DBA (Developer )..
thanks in advance
Dear Pinal,
I am working in SQL SERVER 2005 ,
i have found out difference between DBMS and RDBMS …
DBMS Support Single user and RDBMS support Multiuser..
can you please explain this answer with realtime example?
why DBMS not allow for Multiuser?
Hi Pinal Sir,
I am frequent visitor of SQLAuthority.com.You do a graet job.
I have found answers to most of my issues at work thru your site.
Now I have a issue that I cant find a solution.
I have a RunSQL.bat file that calls the .sql files and creates stored procedures in the Database.But when I try to execute the SP,I get error that I don’t have access.(I can grant permission manually,but this is going to be implemented by another person,so I have to script everything).
So I tried to create another .sql file that will grant execute permission for the 3 stored proceudres that will be be created when I run the .bat file.Also this grant permission sql file will also be executed as part of the RunSQL.bat.
The bat file will generate a .out file where it will have errors if any.
The grantexec.sql file:
USE databasename
Go
Grant exec on [dbo].sp_PersonAddresses] to public
go
I get the following error in the grant permission sql.out file,but when I run the same command thru SQL QUERY analyzer,the command is successful.
‘You can only GRANT or REVOKE permissions on objects in the current database’.
Any suggestions to grant permissions to the SP while I create.
Your suggestions and tips would be much appreciated.
Thanks,
Hi Pinal
Great site, I was wondering , why i could not discover you few years back? I have found many useful articles from your site , and will mention it at US East coast sql forums.
Would you be interested to do a little favor? I have searched your entire site ( and web too) but could not figure out the best way to do it.
assume following three tables
Vendor=>
VendorID (int)
Name (varchar)
State (varchar)
AddedOn (varchar)
__________________________________
VenDorDescription=>
VendorID (int)
VendorDescription (Text)
__________________________________
VendorItems=>
VendorID (int)
ItemID (int)
ItemName (varchar)
ItemSize (int)
Now assume following query…
Query is something this , find all vendors added in last three months (Addedon) in state (State) , who have some licenses (Text column)
and they supply ItemA with at least size 4 or itemB with at least size 6
For first part a inner join and contains will do the trick, for later part (find parents where detail have matching criteria) is getting tricky.
What do you suggest? UDF , CTE or something else
‘I have experimented with temp tables and UDF (single value) and execution time is horrific.
Hi Pinal
I am working on a project where the column and table have the same name. That is say the table name is Employee and there is a column in the table called Employee. I was under the impression this is not a good practice because it is confusing but would like to know if there is any performance constraints because of this. I spoke with the DBA whose thoughts were other than (maybe) confusing this is an OK practice – do you have any insights that you might be able to share on this?
hi, pinal.
i would like to see my self as database administrator.
can you guide me basic steps which i need to take up.
it can be your online course or other..
regards
vijay
i have a website Server and a data base server. I connect the database server from the website server using DSN.
This is how our system has been running for sometime.
I keep observing the error log file quite frequently.
These days i come across a very umfamilar error which says as follows:
<![CDATA[ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
i have a website Server and a data base server. I connect the database server from the website server using DSN.
This is how our system has been running for sometime.
I keep observing the error log file quite frequently.
These days i come across a very umfamilar error which says as follows:
<![CDATA[ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
ERROR errormsg – A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
I want to perform backup to a network share. Does the network share need to be on the server or client side? I have mapped a drive on the sql server, however when performing the backup the drive is not listed. Also, I don’t see the client side network share either. The only drives I see listed in backups is the physical drives located on the server. Do to space limitations I would rather not use these.
Contact your network admistrator to know why shared folder is not visible
Hi Dave
Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
I need help,,,,, I follow yours steps but my sql show an error. Exist another source for make this Import CSV File Into SQL???
ok thanks and Congratulations for you god job!!!
What was the error you got?
Without seeing it it is hard to help you
Hi,
I am having a scheduled job running DTS packages on my production environment.
My production server server is having Window 2003 Sp2 installed on it.
Before rebuilding the server I was getting more details error messages in case job failed
the error description is having more detailed error message.
for example:
Step ‘DTSStep_DTSExecuteSQLTask_197′ failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Microsoft OLE DB Provider for SQL Server (80004005): Connection failure.
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Now I am getting error message like below:
Step ‘DTSStep_DTSExecuteSQLTask_17′ failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. ( (80004005): )
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
The same error message is there in Job History and DTS log.The error codes I am getting in the error messages are the generic ones which can
contain several errors.
I have checked everything and could not figure out any differences in the configuration before and after building the server.
Please can someone help me to find the out the reason why now I am not getting the detailed error description
and how to rectify this as without the actual errors it wil be difficult to investigate the reason for failure and
to convince the client.
I have a problem with a database I inherited. It is for a web app that is subscription based. Each account has its own set of tables. Table1_Username, Table2_Username, …. ect. So the problem is that the procedure cache is pretty large since I have to use dynamic sql in the stored procedures to reference a specific users table. I wanted to parameterize these so I don’t add each instance to the cache but I can’t do so with a table name. Do you have any suggestions other than redesigning the whole db.
why stored procedure encrypted is very weakly.
it would decrypted very easy with decrypted Software .
is there way that robust?
why stored procedure encrypted is very weakly.
it would decrypted very easy with decrypted Software!
is there way that robust?
Hi Pinal,
I was wondering if you can refer me anything related to how images can be handled in SQL 2005. I want to store image in a field so that when its called from the application the image shows up.
Thanks for your help in advance.
The best approch is to store image file path in a table and store images in the server’s directory. If you want to store it in a column, use varbinary(max) datatype and openrowset function to convert it to binary
Hi sir,
Can you help me with importing a log file into mSSQl . The log has following format :-
[INFO ] [2009-02-20 11:16:52,356] [http-12080-1] [categorization.CategorizationUtil] – [CMR String:: -1,0,1,1,65,66,433,450][-1,0,1,1,66,67,443,457][-1,0,1,1,150,151,992,1008][-1,0,1,1,283,471,1907,3283][-1,0,1,1,471,472,3274,3290][-1,0,1,1,472,474,3284,3304][-1,0,1,1,474,475,3295,3311])(7[-1,0,1,1,5,5,30,39])}]]
[ERROR] [2009-02-20 11:16:52,356] [http-12080-1] [categorizer.StagedCategorizerUtils] – [Stream is Corrupted]
java.io.IOException: Stream is Corrupted
[ERROR] [2009-02-20 11:16:52,356] [http-12080-1] [categorizer.StagedCategorizerUtils] – [Stream is Corrupted]
java.io.IOException: Stream is Corrupted
Thanks
Ajay
[...] script written by Imran will show how to convert the first char of sentence or word group to be upper case in any [...]
Hi Pinal – I have a problem that baffles me. I had a sql server 2008 install that was bad. I uninstalled, reinstalled. When I go into Management studio, it lists two entries for my server and two entries for the MSSMLBiz. I think the extra one is from the bad install. Know anything to fix this?
i need a help pliz,
to create SQL scripts whereby the shipmentdate should be updated to two days after the transaction date after the transaction is successfully completed
update table
shipment_date=dateadd(day,2,transaction_date)
where
@Kathy
Issue:
Mru.dat file tracks to all Server names that you connected previously, just like our Internet browser, it records all the websites we visited. SQL Doesn’t checks if that server is available or been unstalled. The information you see is purely historical information saying you once connected to that server.
Solution: To get rid of this list, delete mru.dat file:
Location of mru.dat file
C:\Documents and Settings\user_name\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat
Nothing will show up in drop down list when you try to connect SQL Server through Management tools after you delete mru.dat file.
In the above location path change the following
1. Replace user_name with your windows account name.
2. You might not be able to see Application Data folder, to see that folder,do the following.
a) double click MyComputer.
b) Click Tools on the top bar of the window
c) Click Folder Options
d) Click View
e) In the list, click Show Hidden files and folder
f) Click OK
After you perform step 2(a) to 2(f) you should be able to find this file and then delete this file.
Hope this helps.
Imran.
HI
I’m looking for a solution to deal with Data import from flat File(s) in to sql server 2008 DB, Please help…
There are various flat files that have to be imported into the database (I can use SSIS for import); the real problem is with the data in the flat files.
Each row in file is identified by different row types and each row type’s columns are defined in a separate document.
In detail:
First Row data:
“A00″, 399,”COY”, 20090113,093605,330
Second row data:
“V01″,”008″, 15.0, 1265129.89
Please observe the first column(s) A00 and V01 in both the rows
There is a separate document in which the column names for A00 and V01 are defined.
What I want to achieve…
I want a suggestion from the members to know what can be the possible destination table architecture for this scenario and what procedure can I follow to import all Files in to SQl Server tables through SSIS or through any other possibilities.
I cannot create a table for each row type as there are few files with over 60 row types.
Please also note that there will be multiple records under each row type
Ex:
First Row set:
“A00″, 399,”COY”, 20090113,093605,330
“A00″, 400,”COY1″, 30090113,193605,430
Second row set:
“V01″,”008″, 15.0, 1265129.89
“V01″,”002″, 13.0, 1244.34
“V01″,”003″, 16.0, 177126.09
“V01″,”009″, 19.0, 1565129.89
Etc,
Please excuse me if im not clear in my description, Please post your suggestions.
Thank you.
JG Kiran
Hi ,
I was trying deny query on SQL Server 2005 Standard edition.
deny select on test..testtable to A1
eventhough it showed command was successfully executed, the command was not effective ie user can still successfully run select query.
But when I run the same queries for SQL Server 2005 Enterprise edition it worked perfectly.
Please advise me.
I used management studion too for denying a user fomr accessing a table as weel as column from denying through securables. But it is not working for Standard edition but working for Enterprise edition.
Please advice me on this.
Thankyou
A
when one user is running the DTS package. How the other user know that the DTS package is running.
Hi
i am using sql server 2005,
i have created two table . i have created primary key constraint on tableone and on tabletwo i have created foreign key constraint that refernces to tableone . now i am inserting into table two with null value it allows me .it violates the parent-child relationship. is there any solution?
Thank you
@Kaushik
create primary key constraint on foriegn key column of tabletwo
or
create not null constraint on foriegn key column of tabletwo
Regards,
IM
I have a department store database with the following tables:
tbl_branch —> branch_id, name, location
tbl_category —> category_id, name
tbl_items —> item_id, name, price, branch_id, category_id
ISSUE:
1. I have backup files of more than 2 years of my database. (around 6000 .BAK files)
2. I need to retrieve data of the mentioned tables.
3. Note that tbl_category & tbl_branch data have been modified several times by the administrator over the period.
4. I want to save those tables in a separate database from the backup files, so that i can run queries for statistics
purpose.
Database engine used: MS SQL 2000 Standard
What process should i follow to automate the task? Are there any tools that can help me achieve this?
Hi ,
I am using SQL Server 2005.My report server is not working.When I am starting my reporting services it is throwing some error like Error 1053:The service didnot respond to the start or control request in a timely fashion.
Could you please help me out.
Thanks imran
but why that thnig is happen once i define the relationship. can you please explain me?
Thank you
@Kaushik.
I Understand what you must be thing, you cannot insert NULL value in Primary key Column, so your question is how can you insert NULL in column that has Foriegn Key constraint because foriegn key column should have only those records that exists in Primary key and Primary key will not allow NULLS.
According to what I understand.
Following are important Features of Primary Key constraint.
1. One Primary key per table.
2. Data inside that column ( or combination of columns) must be unique.
3. Data must not contain NULL values.
4. Duplicates cannot exists.
.
.
.
.
Foreign Key constraint.
1. Will contain all values that exists in primary key column to which it is refering.
2. Duplicates can exists
3. Can have NULL values.
We also have to understand that NULL itself is not a value. 0 (zero) is a value, but NULL is not a value. And I have not read any where saying Foriegn Key constraint should not have NULL, because primary key column cannot have NULL values.
Remember to have no NULLS in column is imposed by Primary Key constraint, Foriegn Key constraint does not impose any constraint like that, Similarly Unique key constraint will allow only one NULL value in that column and NOT NULL Constraint will now allow any Nulls in that specific column.
There is alot of material available on internet, I suggest you please read it.
Regards,
IM.
@ shruti , please follow this ,
From online forums, I saw this solution for your problem,
Click Start, click Run, type regedit, and then click OK.
2.
Locate and then click the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
3.
In the right pane, locate the ServicesPipeTimeout entry.
Note If the ServicesPipeTimeout entry does not exist, you must create it. To do this, follow these steps:
a.
On the Edit menu, point to New, and then click DWORD Value.
b.
Type ServicesPipeTimeout, and then press ENTER.
4.
Right-click ServicesPipeTimeout, and then click Modify.
5.
Click Decimal, type 60000, and then click OK.
This value represents the time in milliseconds before a service times out.
6.
Restart the computer.
source :
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/b57ee42d-42ef-44a4-9670-be9088dbf9d4/
Hello Pinal,
I have some problem with sql server performance.
We have a 300 million records in our db table.
How do I write select * from this table.
I am working designing paging concept in our framework development.
We are using .Net 3.5 and SQL server 2008.
Any suggestions for paging concept at SQl server side are welcome.
Thanks & Regards
Hasmukh
You can use a pagination technique so that you dont need to push all data to .NET. Google/bing for Pagination+sql server
Hi Pinal,
I need help about email alert for Failed backup.
How we can do that ?
I refered ur document for database mail and it worked fine but how to send mail when backup failed.
Thanks and regards,
Atil
Hi Pinal Dave,
Could you please tell me, is there any query that will convert the data in a Row of a table to XML file format.
Example: Table name : Employee
EmpNo Emp2 Emp3
1 Kumar DBA
I want the above column convereted as follows:
Please give me a suggestion
Thanks in Advance
Prashanth
hi,
i would like to see my self as database administrator.
can you guide me basic steps which i need to take up.
it can be your online course or other..
regards
obu
Hello,
First of all, thank for maintaining such and interesting and completed blog Pinal, it is great to see the dedication you put into SQL.
I am calling a Object execution statistic report from the SQL studio management and all the statistics returns correctly on my databases except on one, where it says currently no executable object has consumed CPU time.
I have updated all the stats already and auto update is set to ON.
Have you faced this ever before ?
All the best !
Hi – did you ever resolve this, I’m faced with same issue.
Hi,
I have raw data(sheet1) in an excel sheet in one tab. In the same excel sheet on a different tab(sheet2) i a using a ms-query to pull records from the rawdata(sheet1) in and build graphs on them. This works fine on my system….however if i email this file to another person they are unable to refresh it . they get an error message stating ” The Microsoft jet database engine cannot open the file ‘…../…./….’ .Its already open exclusively by another user or you need permissions to view the dat”
There is no password for this file.
Please advise.
regards,
conrad
Dear Sir,
I am working on a billing Module and just cannot get the recurring billing dates right, the end dates always mess ups. I use the following code, which has a bug.
‘For Next Start (i is the counter to repeat number of times)
Format$(DateAdd(“M”, i, @SDate), “dd/mm/yyyy”)
‘For End Date
Format$(DateAdd(“M”, i, DateAdd(“M”, i, @SDate) – 1), “dd/mm/yyyy”)
Please Help. thanks
Iqbal Zariwala
I have a production server.I want to get the data from application server for updation and reviweing. When I tried establsih remote database access with my computer to the application computer, I coudn’t connect it. It shows the following problem :
“A network-related or instance-specific error occurred while establishing a
connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured
to allow remote connections. (provider: Named Pipes Provider,
error: 40 – Could not open a connection to SQL Server)
(.Net SqlClient Data Provider) ”
I made the changes in both the computer to enable remote access. But it is not working. Please provide a solution
Thank you for all your efforts!!! You have made my life easier on so many occasions! I can thank you enough – please keep up the outstanding posts.
IF you have time (some other green sql people may want to know this as well) I have run the DBCC CHECKTABLE and it prints out stuff in the message area, it does not say there is any errors, but I would just like to know what does it all mean?
Here is what I recieved…
USE DATABASENAME DBCC CHECKTABLE ([TableNAme])
DBCC results for ‘TableNAme’.
There are 48127 rows in 165 pages for object “TableNAme”.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Any insight to this would be great, I am just looking to understand a little more.
Thanks again
Bill
Hello Anand
Check whether your Sql Server Service is started or not. Start –> Control Panel –> Administrative Tools –> Services –> Sql Server Service
Regards
Vijay
Hi Pinal,
I found your website through search engine in January and from then till today i have been visiting your website everyday. I have learned alot from articles, scripts… So thanks for good work, keep it up.
I need ur advice on the following:
1. ARCHIVING – is very imp – what thing needs to be considered and discussed with client… as the database would have million of records in couple of days.
2. SYNCHRONIZING – We got 50 developers working on REMOTE location. We used Redgate in the past to keep everything in sync (dev, staging and production) but we faced lot of issues with it.
What would you suggest on this – so that we keep everything in sync….
Hi Dave,
How can we check whether a table (upto row level) has been locked for insertion , updation or deletion?
Thanks in advance.
Jacob
Hello,
I am a .net profession working in chandigarh. I want to switch into database. I would like to devote maximum of my time into database work. What would you recommend me to do in order to be a database professional. Shall i do SQL DBA training or SQL Database development training. As i already have over 3 yr. of sql experience what peace of suggestion you would recommend me.
Thanks
Anil
Hi Pinal:
I need an advice from you. I’m having a DB which is updated daily. Some time I face an issue. While updating, the size of log file increases horizontally & this stops the application from working. Until no one truncate or shrink the log file, it will not start work. What I want, I want to develop a USP which will monitor the size of log file & if it increases beyond a limit or increases sharply, either I should shrink the file during updation or logging of log should stop.
I can get the size of log file & check whether it is increasing sharply or not, but problem arises when I want to shrink the file. I can’t shrink the file while updation is going on as the transaction is not committed yet.
Could you pls provide me your input to come out from this showstopper. I will be thankful to your for the same.
Thnxs
Mint
Hi doctor,
I would like some help for transact replication in sql server 2005. I’m mcts and mcdba. This error is very complicated. About conflits in replication monitor. I used verboselevel 0.
I woulk like to send a txt, can I?
thanks.
I have two database (same structure with same name in different location.) But different data. Now my problem is I need to merge through some script or stored procedure.
so please help me.
eg
database name:testdb
table name: testtbl
data inside table: Data are different in two testtbl (totally different )
HI guru
I want to drop multiple stored procs and tables with similar names.
How can I do this? Can i use wild cards?
Similar names with deifferent schema?
Give us more informations
hi pinal
actually i search so much for this problem but can’t find solution for that…so writing to you..
actually i have make one trigger on table for after insert…this trigger is simple when any row inserted into table
i m doing some calculation and put data into another table during trigger….
but when i m going to insert data into triggered table using import and export wizard that time don’t trigger not fired…means it’s completely bypass trigger can you tell me what’s reason behind that pls tell me i have provided my mail id …so if you can reply here or there both are very helpful to me…
thanx in advance…
Hi Pinal,
How can I get the full schema of the Tables for a Database, in the same way you can get the Stored Procedure Schema as shown in your example.
http://blog.sqlauthority.com/2008/11/22/sql-server-simple-use-of-cursor-to-print-all-stored-procedures-of-database-including-schema/
Thanx in advance.
select * from 8information_schema.tables
Sir I have a requirement that I have 2 tables and I want to update and insert from the another table .
I have table A and table B
Table A have 3 colms and table B is same as table A
But I want to replace the data of table A which is in table B and at the same time I want to insert the data which is not in table A but in Table B
Hi Pinal,
I have a set of tables
–create table Acct(Accont varchar(2),Parent varchar(2))
–create table Fact(Account varchar(2),Value numeric)
–create table Agg(Account varchar(2),Value numeric)
The Account table will have the data for parent as well as child accounts.
The Fact will have values for only child account codes.
But the Agg will have Values for both child account codes and also their parents.
Can you please let me know how this can be done.
Thanks,
Priyadarshan Mishra
Hi Pinal:
I am looking for a way to audit all logins in all production servers and sql servers, make a list , for each login, their respective server roles and database permissions. What about scanning through a whole group of servers? I have about 1500 SQL Servers to check through.
How can i achieve this using script. I have to automate also so that i will monitor all servers.
Please help me in this.
Thanks
Hi Pinal
I have a question about the message returned in this update:
update s set s.bill_amount = 1, s.balance = 1
from subscriptions s, name n
where s.balance = 0 and s.bill_amount = 0
and s.id = n.id
and n.member_type in (‘MU’,'MRU’) and n.status = ‘S’
and s.product_code = ‘USDUES’
Result:
(1086 row(s) affected)
(1476 row(s) affected)
When I executed a select query with same conditions before the update it returned 1,476 rows
What is it 1,086 records affected
Thank you
Hi Pinal,
I’m a big fan of your work and for a novice developer like me, your work and examples have been a ton of help.
Recently I came across a problem with SQL 2005 that how do you compare two rows within the same table?
What my problem is, how do i get the date and the values it changed by between 2 rows within a same table.
I figured running a distinct will give me the unique results but when trying to get the date, data chaged, it’ll destroy the usage of distinct function.
(Note: this table contains transactions everyday)
I know it’s a bit strange question, but i can’t think of a way of accomplishing this?
Thank you
Damiththa
hi Pinal,
do you have by chance steps for sql 2005 mirroring.
I need the step by step .I greatly appreciated you help in advance
BACKUP LOG db-name WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (db-name, TRUNCATEONLY)
Can I use this, in order to reduce log.ldf file?
please send me any suggestion to me email
best regards
@Damittha,
If you know the where clause for the two rows even if it is a same table, you can still make the same table as two tables. and then using join you can see what values have been changed.
Example:
create table example1 ( ID int, Value1 int, Date int, Changed_By varchar(40))
insert into example1 values ( 1, ’3000′ , ’12/31/2008′ , ‘Imran’)
insert into example1 values ( 2, ’4000′ , ’12/31/2008′ , ‘Imran’)
insert into example1 values ( 3, ’3000′ , ’1/3/2009′ , ‘Taher’)
insert into example1 values ( 4, ’5000′ , ’1/3/2009′ , ‘Imran’)
Now lets consider, for example above, lets says you came up with a where clause something like this,
where ID = 1 and Value1 = 3000 , this will be your first table.
Where ID = 2 and Value1 = 3000, this will be your second table.
your select statement can be something like this,
select A.Date Previous_Date,
B.Date New_Date,
A.Changed_By,
B.Changed_By
From (select * from example1 where ID = 1 and Value1 = 3000 ) A join (select * from example1 Where ID = 2 and Value1 = 3000 ) B on A.Value1 = B.Value1
So, all you need to find out is how will you be able to build your where statement.
~ IM.
@ IM (Imran Mohammed)
Thank you for the quick response. Seems like your solution did solve the problem.
Let me give it a shot and will let you know whether I am able to tweak it a little for my purpose.
Thanks again
damiththa
Hi Pinal !
I have a database which has long-text data and I am using full-text search on it.
I need to remove duplicate strings from it.
When i use direct string comparison it takes long time to retrieve distinct records for removal, I am using CTE for removal the duplicates.
How can I speed it up ?
Please help me.
Waiting for your kind response.
Regards,
Musab
Database Administrator
Hi Pinal,
I learnt about piecemeal restore recently. Can you explain this feature more details and what is the benefit of using it? Also, I want to know if it works in the subsciber databases if I’ve replication setup.
Thanks,
Roy
Hi Pinal,
i have a three tables of same structure(alls having same
fields).And all three tables have data.
Now i want to merge all three tables into one.
These three table may have some common records thats i
have to put in final one.
I m using sql server 2005.
Please tell me how do i go about it ?
Thanx,
Lalit
Hi Lalit,
I think you need to find common values among the three tables. You can use Row_Number() of SQL 2005 to identify the common row. you can find more details at : http://tejasnshah.wordpress.com/2008/12/31/find-duplicate-rows-with-row_number-sql-server-2005/
let me give you one example by dummy data:
DECLARE @a TABLE(ID INT, Value VARCHAR(50))
DECLARE @b TABLE(ID INT, Value VARCHAR(50))
DECLARE @c TABLE(ID INT, Value VARCHAR(50))
INSERT INTO @a
SELECT 1, ‘AA’
UNION ALL
SELECT 2, ‘AB’
UNION ALL
SELECT 3, ‘AC’
UNION ALL
SELECT 4, ‘AD’
INSERT INTO @b
SELECT 1, ‘BA’
UNION ALL
SELECT 2, ‘BB’
UNION ALL
SELECT 3, ‘BC’
UNION ALL
SELECT 4, ‘BD’
INSERT INTO @c
SELECT 1, ‘CA’
UNION ALL
SELECT 2, ‘CB’
UNION ALL
SELECT 3, ‘CC’
UNION ALL
SELECT 4, ‘CD’
;with cte AS
(
select ROW_NUMBER() OVER(PARTITION BY A.Id, A.value, b.Id, b.Value, c.ID, c.Value ORDER BY a.ID) AS RowID,
A.ID as AID,
A.Value as AValue,
b.ID as BID,
b.Value as BValue,
c.ID as CID,
c.Value as CValue
from @a a
inner join @b b ON b.ID = a.ID
inner join @c c ON c.ID = a.ID
)
select *
from cte
Let me know if it helps you in any way.
Thanks,
Tejas
hi !
i need to a stored procedure for Create and Drop Trigger
please send this script too my mail
tanks you!
This is not advisable.
Why do you want to do this?
Dear Pinal Sir,
I am swati Bhoyarkar from mumbai.I am a soft Developer. keen to be the most successful DBA .I just want to know if you conduct and seminor or class for the needy job seekers like me.I just need a guidance on how to move successfully wihtout leaving anything behind.
Thank you regards
swati
hi sir,
I am getting this error while taking connection string from udl file..
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied..
actually i installed software on client side and i configure udl file client format their system now when i configure the udl file the same error i got while configuring udl file.. my application take connection string from the udl file…… i am using sql server 2000……
plz help me
thanks
nitin sharma
hi sir,
I am getting this error while taking connection string from udl file..
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied..
actually i installed software on client side and i configure udl file client format their system now when i configure the udl file the same error i got while configuring udl file.. my application take connection string from the udl file…… i am using sql server 2000……
plz help me
thanks
nitin sharma
Hi sir,
I want to use osql in sql server 2000 for my job i m able get output. As i m giving input from one file using -i command and saving tat bunch of query in one file i.e -o command but the pblm is tat i hav lots of query so i m not to able to knw whch query output is this and another is i want some command which can be used for formating my output. plz help and rply to my email id.
plz help me
thanks sir
Amit Shah
Hi pinal
I have tried your help related to view all the tables of all databases of sql server 2005 but it bring results of only master data base’s table while i need to view and access all the tables of all databases how is it possibel please help
Hi Pinal,
I am new to SQL Server, just started using it since last 2 months… I have few queries, hope you don’t mind to spend your time to look into it. Thanx in advance for your reply..
1 – How could I get the Index, given on a table using query? Like in MySQL we are using “SHOW INDEX FROM route”
2 – How could I get Full Text Catalogs name for a database, through query?
3 – How could I get, name of the Tables on which Full Text Index is applied, along with column name on which Full Text Index is given, through query?
4 – We have a Central Data Base Server, we all are using it through our PC, with same login name and password. There are few procedure and user defined function in this database. Now is there any way get this procedure or functions last modified time, with the IP address of machine through which it is modified?
Thanx,
Ekta…
Need help how to get previous row value for caluclutions in next row
Example
Int varchar int int int int
ID Month Pend_Start Additions Completions Pend_End
1 JAN08 11 12 13 14
2 FEB08 NULL 12 13 NULL
3 MAR08 NULL 12 13 NULL
I need to find cummulative calculation like
(Pens_Start+Additions)-Completion = Pend_End
Pend_End for JAN 08 I need to use it as Pend_Start for FEB 08……then so on.
Please help.
Krunal
hi there
i want to import some table from access file(mdb) info my SQL Server 2005 DB.because some of my biz Requrements, i can`t use, import/export wizard in SQL Server, and must use SQL Script.
please help me or sent me some page that show how i can do it
best regard
bye
Read about OPENROWSET in SQL Server help file
Hi Krunal,
I hope following could hepl you out…
——————————————————-
;WITH TMP(ID, Month, Pend_Start, Additions, Completions, Pend_End) AS
(
SELECT ID, Month, Pend_Start, Additions, Completions, Pend_End
FROM tmp1
WHERE ID = 1
UNION All
SELECT t.ID, t.Month, d.Pend_End, t.Additions, t.Completions, d.Pend_End + t.Additions + t.Completions
FROM tmp1 t, TMP d
WHERE t.ID = d.ID+1
)
SELECT ID, Month, Pend_Start, Additions, Completions, Pend_End FROM TMP OPTION (MAXRECURSION 0);
;
———————————————————-
The only problem with this approch is your ID should not break in between, and I am considering it starts with 1 and goes like 2,3,4,5,6….
Hi Krunal,
You can use Self Join to get rows with another row like:
SELECT A.*, B.*
FROM TableName A
LEFT JOIN TableName B ON A.ID = b.ID-1
This way you will get the result as you want.
Let me know it it helps you in any way.
Thanks,
Tejas
Hi Pi
I need your help in locating some one who could build me a very good db on sql for an e-commerce business.
Any advice?
Is there any function in MS SQL server which can replace the regular expression with required text? For e.g. if the text contains \n [enter] in between, then how can I search and replace \n[enter] with ‘.’?
Please help?
Hi Manish,
you can use:
DECLARE @string AS VARCHAR(100)
SELECT @string = ‘Tejas
Shah
‘
SELECT REPLACE(@string, CHAR(13), ‘”New Line”‘)
Thanks,
Tejas
Our SQL Server 2003 configuration is 12GB RAM Intel Xeon CPU E5310 running at 1.60GHz. OS is Microsoft Windows server 2003, Standard x64 edition with SP2. It’s a dedicated SQL server and no other software is installed on it.
We have third party software , Idera to monitor sql servers.
The problem is Idera always shows the memory utilization on this server to be critical, i.e. more than 90%. I guess this is due to Windows server tendency to reserve all OS memory for SQL server (if it is installed on that server) and dynamically allocate the required memory for other applications on demand basis. So in this case, that server will always show that high memory utilization on it.
Can we find out the actual memory used by the SQL Server?
i.e. is there any way, we can conclude that present memory on SQL server is not adequate and need to upgrade.
Dear Pinal,
I am facing a problem with connecting it with the Sql server 2005. Actually the problem is that i have newly installed the Sql Server 2005 and already have installed Sql Server 2000 on my pc. Now when i trying to connect with Sql Server 2005 it get connected me with Sql Server 2000 database. Any help greatly appriciated.
Regards,
Rupesh Maheshwari
I have a tabe articles :
CREATE TABLE [dbo].[articles](
[articleid] [bigint] IDENTITY(1,1) NOT NULL,
[name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sdesc] [varchar](250) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[filepath] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sortorder] [int] NULL,
[endeffdt] [datetime] NULL,
CONSTRAINT [PK_articleid] PRIMARY KEY CLUSTERED
(
[articleid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Now when I execute following query :
“select name + sdesc from articles”
It gives me following error,
“Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.”
Is there any way to solve this error without changing collation of columns?
Hi,
Myself Shrikant v Sakpal.
Working with one of the leading MNC as an IT-Asssociate(DATABASE). I want to improve my SQL server 2005 or 2008 developers as well as admin skills.I can also opt for certification program.
Kindly suggest where will i get the excellent Training and certification Assistance in the Central region of Mumbai(NEAR THANE).
Regards,
Shrikant S
Please feel free to mail me your suggestions….
Hi Pinal,
I am trying to disable table locks in sql server 2005 by enabling trace flag 1211, but still I see table locks when I query syslockinfo table while executing a SP.
Please help me in disabling table locks at DB level(as oppose to query level).
Thanks,
Anjali.
Is there any way to change collation of a indexed column, without droping the index?
Hi
I want to lock the table in a database using Microsoft SQL Server Management, where the client should not be able to view the table structure or any the contents of the table (ie) the client should not be able to view the table structure.
Please help me
Regards
Prity.R
Dear Pinal Sir,
Can you please explain how sql server stores data in pages?
thank you
regards
swati
Hi Pinal,
I am a newbie to Sql Server.
The query I am looking to create is with sql server 2005
Below is a sample TestData table:
Batch Color Value
8440 Blue 99
8440 Red 89
8440 Green 79
8441 Black 48
8442 Blue 98
8442 Red 78
8443 Purple 65
8443 White 91
I have the following successful sql statement:
Select Batch + ‘,’ + Color + ‘,’ + convert(varchar, Value) from TestData
which produces
8440,Blue,99
8440,Red,89
8440, Green, 79
8441,Black, 48
8442,Blue,98
8442, Red, 78
8443,Purple, 65
8443, White,91
How do I construct an sql statement to generate following results? (same as above but in one row for each batch)
8440,Blue,99,Red,89,Green, 79
8441,Black, 48,
8442,Blue,98,Red, 78
8443,Purple, 65,White,91
Thank you in advance,
Elle
Hi Elle,
Please find the solution:
DECLARE @t AS TABLE(
Batch INT,
Color VARCHAR(50),
Value INT
)
INSERT INTO @t
SELECT 8440, ‘Blue’, 99
UNION ALL
SELECT 8440, ‘Red’, 89
UNION ALL
SELECT 8440, ‘Green’, 79
UNION ALL
SELECT 8441, ‘Black’, 48
UNION ALL
SELECT 8442, ‘Blue’, 98
UNION ALL
SELECT 8442, ‘Red’, 78
UNION ALL
SELECT 8443, ‘Purple’, 65
UNION ALL
SELECT 8443, ‘White’, 91
;with a AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Batch ORDER BY Batch) AS RowId,
*
FROM @t
)
SELECT SUBSTRING((select (‘,’ + CAST(b.Batch AS VARCHAR)+ ‘,’ + b.Color + ‘,’ + CAST(b.Value AS VARCHAR))
from a b
WHERE b.Batch = a.Batch
FOR XML PATH(”)
) ,2,1000)
FROM a
WHERE RowID = 1
Let me know if it helps you in any way.
Thanks,
Tejas
Thanks Tejas Shah,
it really worked…
thanx a lot…
Hi Pinal… Hi have a problem with two sqlserver instance (2000 and 2005) I have a function on first istance(2000), I try to call it from the second instance… but I’ve got this error “The multi-part identifier “sqlserver.DBtest.fn_test could not be bound.”
How can I solve this?
Thanks!
Gus
Did you setup it as a linked Server?
Also you are missing objectowner
sqlserver.DBtest.fn_test
should be
sqlserver.DBtest.objectowner.fn_test
hi pinal,
i want to develop messaging system like Facebook messaging system for my application ..
it should be in Clild parent form and it also display all history for that message ..
i created this table for my messaging system..
i.e
Message_id int no 4 10 0 no (n/a) (n/a) NULL
Message_Subject varchar no 500 yes no no SQL_Latin1_General_CP1_CI_AS
Message_Body text no 16 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Message_from int no 4 10 0 yes (n/a) (n/a) NULL
Message_to int no 4 10 0 yes (n/a) (n/a) NULL
Message_Isread_From bit no 1 yes (n/a) (n/a) NULL
Message_Isread_to bit no 1 yes (n/a) (n/a) NULL
Message_Isdelete_from bit no 1 yes (n/a) (n/a) NULL
Message_Isdelete_to bit no 1 yes (n/a) (n/a) NULL
Message_from_vdate datetime no 8 yes (n/a) (n/a) NULL
Message_to_vdate datetime no 8 yes (n/a) (n/a) NULL
Message_from_ddate datetime no 8 yes (n/a) (n/a) NULL
Message_to_ddate datetime no 8 yes (n/a) (n/a) NULL
Message_Created datetime no 8 yes (n/a) (n/a) NULL
Message_Parent int no 4 10 0 yes (n/a) (n/a) NULL
but i have one issue that how to maintain parent child relational
hello Pinal dave
i would like to know how do you elimate lookup operator?
Thanks & Regards
Sudhir
Can you give us more informations on what you want to do?
@ergus
Use four Part Name.
Correct four Part Name
Format : Server_Name.Database_Name.Object_Owner_Name, Object_name
So four part name in your query will be changed to this,
sqlserver.DBtest.object_owner_name.fn_test
@Jayesh L Lolariya
Parent Child Relation is created by creating foriegn Key on child Table.
Create a primary key or unique key on the key column in Parent table and then create a forign key on key column of child table referencing primary key column of parent table.
~ IM
I have a query like this
both tables are indexed on ani and the timestamp column
SELECT *
INTO CWC1
FROM CIB A
LEFT JOIN CVT B
ON A.ANI = B.ANI
AND A.TIMESTAMPCREATION BETWEEN B.TIMESTAMPCREATION AND DATEADD(S, 120, B.TIMESTAMPCREATION )
It runs for too long – over 5-6 hours.
I noticed one thing that if I split the CVT table into two parts – of about equal records and do the above query.
One part runs for 30-40 min and the other runs for 4-5 hours.
is there a way to figure out why this other parts runs for too long??
Hi Pinal,
I am searching for Datawarehouse specific sql operators.
Can you please tell me which are sql operators specifically used for Datawarehouse operations.
Regards,
Girish
Hello Sir,
Could you please explain the operation of Distributed partition Views or where i can find the source for this.
Regards.
Sanjay
Hi
Col1=’Test’
Col2=’Value’
Select Col1+ ‘ ‘+Col2
Output will be Test Value
But i want out one below the other
like
Test
Value
How to do it at the query level ?
Please can u help me out.
Regards
Mohammed Irfan
@Irfan,
SELECT Col1
UNION ALL
SELECT Col2
Regards,
Pinal
Hi
Thanks for the quick reply..
Actually i dont wont to do it like this the query is very huge i cant use the union all for the query.
Ex: i have string ‘Hello world’ in one column only
i want output like:
Hello World ..
is there any new line chrecter in SQL 2005 using which i can split it into two line ?
Hi
Thanks for the quick reply..
Actually i dont wont to do it like this the query is very huge i cant use the union all for the query.
Ex: i have string ‘Hello world’ in one column only
i want output like:
Hello
World
is there any new line chrecter in SQL 2005 using which i can split it into two line ?
what is the difference between two
print getdate()
select getdate()
i want to select date in yy/mm/dd format and assign to a datetime varibale but the output is like Apr 11 2009 3:16PM
plz reply ASAP
@ Irfan
select substring ( ‘Hello World’, 1, charindex (‘ ‘ , ‘Hello World’) -1) Output_Column
Union
select substring ( ‘Hello World’, charindex (‘ ‘ , ‘Hello World’ )+1 , (len(‘Hello World’) – charindex (‘ ‘ , ‘Hello World’)) ) Output_Column
You can replace ‘Hello World’ with column Name and add from Table Name at the end of script. It will work fine.
@Rahul
Print – prints message in message window. Where as
Select – displays output in result tab.
Select can be used to assign a value to variable.
if you want assign a datetime value to variable then do this,
declare @DateVar varchar (10)
select @DateVar = convert ( varchar(10), getdate (), 101)
print ‘Print – Prints this Message in Message Tab’
print @DateVar
select ‘Select – Select is Displayed in Results Tab ‘
select @DateVar Ouput_Column
~ IM.
Hi there,
Can you please tell me what will be the best way to search for the string in the where clause without using LIKE clause.
eg. select * from test where ____ (compare characters or something) simple search without using like.
Thanks
Sam
@ Sam,
If you have a full text Index build on that column for that table, then answer is yes,
You can use Key word contain.
This is much powerful than Like.
You can see a simple example here.
http://www.databasejournal.com/features/mssql/article.php/3441981/Full-Text-Search-on-SQL-2000-Part-1.htm
If you do not have a full text search index or do not want to use full text index search then try using charindex. This will work if you are searching for only one character.
select column_name from table_name
where column_name in ( select column_name from table_name where charindex (‘mention_character_you_want_to_search_for’, column_name ) > 0)
Remember Full text index can be used in SQL Server 2000 and SQL Server 2005 and SQL Server 2008.
~ IM
Hi Pinal,
Hope u r doing well. I have one question related sql server 2005, I uploading my site on godady.com. i have authorized.net(payment gateway) code in my web service. i want to call that web service from sql server 2005. because i want to fire that web service once in a day. for that i can create sql job. is thare any way to call web service from sql server 2005. or can we directly execute authorized.net code from sql server procedure.
Plz reply asap on my id.
Thanks & Best Regards,
Jiten Chudasama
hi
i am trying to insert data from one table to another table with same field name but the datatype for one field is different that is for TravelDate field in the 1st table it is Varchar(15) & in the 2nd table the Datatype of TravelDate is Datetime so when im trying to insert data from 1st table to 2nd table im getting error as
“Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.”
when im trying to insert from this query
insert into linkinvoicesector(InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag)
select InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag
from linkinvoicesectors
i have tried even by use convert while inserting but no use still same error.
can i have any solution regarding this problem. I would be very thankfull to u if my problem is solved
thankq
Hi Vani,
I think field “TravelDate” contains the data which is not proper for DateTime column.
Please find it with ISDate(“TravelDate”) and if you get 0 in any column, update that data to proper datetime format.
Thanks,
Tejas
Hi Teja
Thank u for giving me reply.I have tried the ISDate(”TravelDate”) but the time is entering as 1900-01-01 00:00:00.000 which is not correct.
Yes the date formats in the 1st table is not in correct format. for some of them its like eg:- 20NOV, 03Jan08, 25December2006, 19/05/08
So now i have to convert the dates while inserting the data from 1st table to 2nd table.if the date is not in correct format then it can enter as null there.Is there anyway to convert the dates of different formats to datetime format while inserting.
Thanks
Vani
Hello Imran
my problem is that i am getting month,year saparately in
S.P as parameter and conacat it like
select @DateVar = convert ( varchar(10),@ForYear + ‘/’ + @ForMonth + ‘/’ + 1, 101)
Error is:Conversion failed when converting the varchar value ‘/’ to data type int.
plz help me
@ForMonth should be cast( @ForMonth as varchar(2))
hi,
I have a probelem in SQL server 2005.
I have made a Sql server Agent JOB, but when it fails it desnot rollback the changes made in the database.
For Example: It has 10 steps but if it fails on 5th step then database remains in that state only.
Is it possible if it fail on step 5 it rollback the changes made by it in the database.i.e., Can it bring the database in state before the start of step 1.
Better you create a procedure with transaction that handles error handling and rollback and use that procedure in the job
My “Web Server” is in UK and “Database Server” is in US. The problem I am facing is, using GETDATE() function the the date and time I recived is of US. I need it to be UK time.
Is there any way, I can change timezone setting for DB? Or any other alternative for this?
Thanks
Ekta
Hi
I want to know about database designing process .What points should keep in mind when we are going to design a new data base.
Hi,
I have a very simple query and it seems valid to me but it is not working and giving a syntax error. I looked on different sites but I couldn’t find anything. And finally have decided to ask you.
SELECT ISNULL((Select Id from Student Where StudentId = 1), (EXEC(’SELECT Id From Student Where StudentId = 0′)))
If we run only EXEC(’SELECT Id From Student Where StudentId = 0′) then it works. But whole query togethere doesn;t work.
Please let me know what should I use?
Thanks,
Hitesh Savalia, MCAD
Hi Hitesh,
Try following
SELECT ISNULL((Select Id from Student Where StudentId = 1), (SELECT Id From Student Where StudentId = 0))
I have a table, with field
id, pid <-refers to existing id, name, type,order.
This is a table used to create the main menu for the application.
how do we write a sql query to display the output in a tree manner.
ex:
id; pid; name; type; order
10; 0; mnu100; 1; 1
20; 0; mnu200; 1; 2
30; 0; mnu300; 1; 3
11; 10; mnu110; 2; 1
12; 10; mnu120; 1; 2
13; 12; mnu121; 2; 1
14; 20; mnu210; 2; 1
15; 30; mnu310; 2; 1
16; 12; mnu122; 1; 2
17; 16; mnu123; 2; 1
should be displayed as a tree,
Hi Pinal
Hope you are doin well..I am daily reader of your blog.I just want to know how to join your SQL Server Ahmedabd User group..I want to be member of your group..Let me know
bye
Hi gpshiburaj (Is this your name?),
Hope following could help you, just change table and field name as per your requirement. And let me know if it work?
———————————————————
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[FN_CategoryTree]()
RETURNS
@categorytree TABLE
(
– Add the column definitions for the TABLE variable here
categoryid bigint,
parentid bigint,
name varchar(250)
)
AS
BEGIN
DECLARE @categoryid bigint
DECLARE parentid_cursor CURSOR FOR
SELECT categoryid
FROM category
WHERE parentid = 0
OPEN parentid_cursor
FETCH NEXT FROM parentid_cursor INTO @categoryid
WHILE @@FETCH_STATUS = 0
BEGIN
;WITH cattree(categoryid, parentid, name) AS
(
SELECT categoryid, parentid, name
FROM category
WHERE categoryid = @categoryid
UNION ALL
SELECT c.categoryid, c.parentid, c.name
FROM category c
INNER JOIN cattree d
ON c.parentid = d.categoryid
)
INSERT INTO @categorytree
SELECT categoryid, parentid, name FROM cattree;
FETCH NEXT FROM parentid_cursor INTO @categoryid
END
CLOSE parentid_cursor
DEALLOCATE parentid_cursor
RETURN
END
———————————————————
Hi Pinal Dave,
What is the installation order for Visual Studio and SQL Server?
Thanks
Anil
Thanks Ekta,
I am able to achieve exactly what I require with the function. I am not clear with the “with” (CTE) that you have used, but that is not a problem, I will read the BOL to get more information on that. Thanks for giving me a good lead.
And my name is Shiburaj G Purushothaman.
Hello Pinal Dave,
I am a frequently used your blog for my solutions.
I have a doubt regarding the timeout for connection in sqlserver 2005.
Actually, what is the Default Timeout for connections string
if i am not mentioned,
And when i give 40 in ConnectionTimeOut is it taken as Seconds or Minutes.. I am very confuse in this scenario..
Can u tell me regarding this with full Explanation.
Thanks & Regards
Ravikumar
Sir
Hope u r fine. Am a beginner in SQL and interested in becoming a fulltime DBA. Though I know something in SQL (both query & enterprise manager) they are very basics only. So please advice me what should I do and from where should I start. Also for any of my doubts now am using your tips and site for refference and find it as ONE STOP SHOP for sql related doubts.
Can you please tell me the ways to contact you and attend any course if you are conducting. Also please advice me whether you have any books written by you.
Hopin for your earnest reply.
Thank you in advance
Chakravarthy.
Hello Sir,
I have to develop keyword search..Requirement for this search is:
As Example Keyword is : test of travel
Combination of Keyword is :
1)test of travel
2)test
3)travel
Now result should display in order like “test of travel” match case display first & then after other keyword match cash list.
This Search applies on multiple tables and fields.
Can you please guide me what kind of query will work in this keyword search?
Thanks in advance,
Hi Pinal,
I am very impressed with your work. I’d like to be more familiar with SQL Server via this blog.
I have this scenario in my web app.
In my application the database is year dependent. i.e. the database is meant to be used for current financial year only. When the financial year ends, the database needs to be recreated with new name (e.g. if current year database name is DB2009 then for next year it should be DB2010)
Another situation is, based on certain conditions I need to carry forward some data to new database (like dues of certain customers etc.)
How do I do it?
We are developing a multi language website. The problem I am facing is, if I am directly adding japanish character in a table it is being displayed properly on the website.
But if I am adding the record using the insert statement like following, the characters are converted to question mark.
INSERT INTO product (code, name, sname) VALUES(‘aaaa’, ‘ようこそゴールデン・ツアーズへ！’, ‘ようこそゴールデン・ツアーズへ！’)
If any one has any idea, please help me…
Thanx…
Hello Sir,
I am working on transactional replication of SQL 2008 over the internet. But I am getting error when try to add a subscription over the internet when I give the login details with server name\ instance name then it is not connecting when I give only instance name then it iwll ask me for the server name and instance name.
With the same login information when I started the management studio and try to connect to the server over internet then it is connecting properly.
Please help me in this scenario.
One more question when I change the server and try with another server and add a subscription then while starting the log reader agent at publisher it display the error
“The process could not execute ‘sp_replcmds’ ”
I am using SQL 2008 server 64 bit enterprise edition and Windows 2008 Server 64bit as OS.
Thanks.
Regards,
Vinod Chakote
Well I got the solution to above problem. This is just to help if any need it…
What I have to do is, have to mention N before inserting and retrieving column values. So now the above query will look like,
INSERT INTO product (code, name, sname) VALUES(’aaaa’, N‘ようこそゴールデン・ツアーズへ！’, N‘ようこそゴールデン・ツアーズへ！’)
And while retriving the data it should be like,
SELECT * FROM product WHERE name = N’ようこそゴールデン・ツアーズへ！’
But I have one more question, using following function I am not getting proper result.
Note : Full text Index is applied on code, name, sname fields.
CREATE FUNCTION [dbo].[FN_SearchProduct] (@searchstr nvarchar(50))
RETURNS TABLE
AS
RETURN (
SELECT productid
FROM product p
WHERE freetext(*, @searchstr)
)
I can not use N infront of @searchstr, it gives error. Please let me know if any one has any idea.
Thanx…
Ekta
Try converting “@searchstr” to “convert(nvarchar(50),@searchstr)”
SELECT productid FROM product p WHERE freetext(*, @searchstr)
to
SELECT productid FROM product p WHERE freetext(*, convert(nvarchar(50),@searchstr))
Convert it explicitly
hope this will solve the issue
Hi Ravikumar
Default connection Timeout is 60sec.
ConnectTimeout property specifies the connection time-out period in seconds only.
Thanks
hi Ekta
Try converting “@searchstr” to “convert(nvarchar(50),@searchstr)”
SELECT productid FROM product p WHERE freetext(*, @searchstr)
to
SELECT productid FROM product p WHERE freetext(*, convert(nvarchar(50),@searchstr))
Convert it explicitly
hope this will solve the issue
I have table employee.
Eid
18241
18245
18246
18247
18248
18249
18250
18261
18262
18263
18264
18265
18266
I want output as follows (in range)
18241 1841
18245 18250
18261 18266
@Amit
WHERE Eid BETWEEN 18245 AND 18250
Assuming the ids are in another TABLE, just use a join.
HI AmitMakwana
Could you be more specific on your requirement.
thanks
Hi Sanjay…
Thanx for your reply. But it’s not working here @searchstr variable is already of nvarchar type and the value I am storing in it is also of nvarchar. The thing that I need is to apend N infront of the value of @searchstr.
Right now the
@searchstr = ‘エアコン付きの快適な大型バス使用’
Actually which should be like
@searchstr = N’エアコン付きの快適な大型バス使用’
But I am not getting any way to convert it like this….
Well using convert function within Freetext is giving an error , I also tried it in following way…
EXEC(‘SELECT productid, code, name, sname
FROM product p
WHERE freetext(*, ‘ + convert(nvarchar(50),@searchstr) + ‘)’ )
Any ways, thanx again for your reply…
I have table employee.i have one table only
Eid
18241
18245
18246
18247
18248
18249
18250
18261
18262
18263
18264
18265
18266
I want output as follows (in range) table format like below
Start End Total unit
18241 18241 1
18245 18250 6
18261 18266 6
@Amit
Is this what you wanted?
WITH
Employee(Eid)
AS
(
SELECT 18241 UNION ALL
SELECT 18245 UNION ALL
SELECT 18246 UNION ALL
SELECT 18247 UNION ALL
SELECT 18248 UNION ALL
SELECT 18249 UNION ALL
SELECT 18250 UNION ALL
SELECT 18261 UNION ALL
SELECT 18262 UNION ALL
SELECT 18263 UNION ALL
SELECT 18264 UNION ALL
SELECT 18265 UNION ALL
SELECT 18266
),
Grouper(RN, Eid)
AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY Eid),
Eid
FROM
Employee
WHERE
NOT EXISTS
(
SELECT
*
FROM
Employee E2
WHERE
E2.Eid = Employee.Eid – 1
)
UNION ALL
SELECT
RN,
Employee.Eid
FROM
Grouper,
Employee
WHERE
Employee.Eid = Grouper.Eid + 1
)
SELECT
MIN(Eid) Start,
MAX(Eid) “End”,
COUNT(*) “Total Unit”
FROM
Grouper
GROUP BY
RN
Hi Pinal,
I am trying to connect SSRS by SSMS with windows authentication on remote server but I am getting following error
The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)
I am able to connect remotely using basic authetication through SSMS. Is there any place to look why am I unable to connect with windows authentication?
Thanks
Sanjeev
Thanks Brian Tkatch for solution.
But this does not solve my problem.
You have taken static data but my data is dynamic not fixed
like
18241
18245
18246
18247
18248
18249
18250
etc
so i want solution in SP in MSSQL 2000
Hi Pinal,
we are a team of developers working in my team. Being a small organistaion we dont have a DBA. so everybody has access to the procedures in the servers.
we do maintain a copy of the procedures where is is modified and commented for reference. But some developers directly modify the procedures on the server using the sp_helptext command.
I want to restrict the use of sp_helptext by every body except the administrators.
How can I accomplish this?
Thanks Brian Tkatch for solution.
But this solution does not solve my problem.
I want this solution in MSSQL2000.
Also data is dynamic not static as you have used
SELECT 18241 UNION ALL
SELECT 18245 UNION ALL
SELECT 18246 UNION ALL
SELECT 18247 UNION ALL
SELECT 18248 UNION ALL
SELECT 18249 UNION ALL
etc…..
Please provide updated solution
Thanks in advance.
Regards,
AmitMakwana
hi ekta
I think there is no way to prefix ‘N’ to a sql variable inside T-SQL Script
Can i know from which instance your passing the japanish data ( asp.net?), If so, no need to prefix ‘N’, .net framework by itself handles passing of data by Prefixing N automatically.
Meanwhile I’ll try to find the solution.
Hi AmitMakwana,
Please create the following procedure.
/***************************Start Procedure**********************/
CREATE procedure usp_findrange
as
declare @ItemNumber int
declare @total int
declare @temp int
declare @initval int
declare @temp1 int
set @total = 0
set @temp = 0
if exists (select * from sysobjects where id = object_id(N’[range_tbl]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [range_tbl]
create table range_tbl (fromval int, toval int, total int)
DECLARE c1 CURSOR local FOR
/* You can change table name and set field on which you want to perform */
select Eid from employee
OPEN c1
FETCH NEXT FROM c1 INTO @ItemNumber
WHILE @@FETCH_STATUS = 0
BEGIN
if @total = 0
begin
set @initval=@ItemNumber
end
if @temp 0
begin
set @temp1=@temp+1
if @temp1=@ItemNumber
begin
set @temp=@ItemNumber
set @total=@total+1
end
else
begin
insert into range_tbl(fromval,toval,total) values(@initval,@temp,@total)
set @temp=@ItemNumber
set @total=1
set @initval=@ItemNumber
end
end
else
begin
set @temp=@ItemNumber
set @total=@total+1
end
FETCH NEXT FROM c1 INTO @ItemNumber
END
insert into range_tbl(fromval,toval,total) values(@initval,@temp,@total)
CLOSE c1
DEALLOCATE c1
select * from range_tbl
GO
/***************************End procedure**********************/
With Best Regards,
RupeshPrajapati
@Amit
WITH
Employee(Eid)
just provided sample data. Remove that CTE and change the FROM in the other CTE (Grouper) to refer to the actual TABLE.
Hi,
I know there are tools that squeeze SQL backup on the air. But I am looking for a way to force SQL server to produce smaller SQL backups or compressed backups? Is there a coding approach?
Thanks,
Farhad
Hello,
how is timestamp in mysql implement in ms sql server….
ms sql server timestamp puts binary data… i hope you can give me enlightenment here…
Hi Pinal,
I am working as analyst and currently working on SQL. Its new for me. I want to load data from excel into SQL Server 2008. Openrowset/Opendatasource commands has been turned off by the admin for security reasons. is there any other way of doing it? i tried SSIS but the server i want to load onto is a remote one. please help me with this
Hi Pinal,
I am posting it here again:
I got error 15401 when I tried to manually change the Window group name after the physical server got renamed.
The Microsoft Article ID: 324321 (http://support.microsoft.com/kb/324321) seems not help with my problem, I would appreciate your help :), thank you!
After renaming the server, I use sp_dropserver and sp_addserver to bring SQL in sync with the new server name. Then I restart SQL Server. Everything seems to work fine. However, under Security -> Logins there are 3 groups that are prefixed by the old server name and also contain the old server name within their name (for example: oldservername\SQLServer2005SQLAgentUser$oldservername$MSSQLSERVER)
Can you please advise how to fix it?
I found out lots of useful information from your website.
Thank you very much for your help!
Alice
Hi Pinal,
Should I change them through Computer Management -> System Tools -> Local Users and Groups -> Groups ?
I have local admin right.
I see all of the SQL Serevr 2005 related group names are still with the old servername embedded.
Many thanks!
Alice
Pinal,
Thanks for all the help.
I am having a problem restoring sql server 2005 database backup. the file name is myProdDB.dat.
In SQL Server 2008 Management Studio, I created a database, myProdDB using the wizard. On database right clicked and Tasks and Restore from File group and selected the above mentioned .dat file.
I selected the replace in options, selected the database as you mentioned in one of your post.
I still am getting the 3154 error, that says the backup file contains more than 1 database blah blah.
I did search in the internet and I am not able to find a good solution for this.
I would really appreciate your help..
Thanks
Srini
HI Pinal,
i am trying to truncate the log file in sql server 2000….
i m getting the message as [SQLSTATE 01000] (Message 0) Cannot shrink log file 2 (PER_LOG)
because total number of logical log files cannot be fewer than 2. [SQLSTATE 01000] (Message 9006)
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
please help to correct this
Bhasakar,
Please post your code to shrink the log.
Best Regards,
Pinal
HI Pinal,
the following is the Script that is setup has a Job in SQL Server to clear all the log of all the Application DB’s
DECLARE @DBNAME VARCHAR(100),
@Query NVARCHAR(4000),
@SqlParams Nvarchar(4000),
@LogFileName VARCHAR(100)
DECLARE DBLog CURSOR FOR
SELECT LTRIM(RTRIM(convert(varchar,NAME))) FROM MASTER..SYSDATABASES WHERE DBID > 5 OR name = ‘tempdb’
Open DBLog
FETCH NEXT FROM DBLog INTO @DBNAME WHILE @@FETCH_STATUS = 0
BEGIn SET @Query = ‘BACKUP LOG ‘ + ltrim(rtrim(@DBNAME)) + ‘ WITH TRUNCATE_ONLY’
PRINT @Query EXEC (@Query)
select @SqlParams =’@LogFileName varchar(100) out ‘
select @Query = N’select @LogFileName = ltrim(rtrim(name)) from ‘ + @DBNAME + ‘..sysfiles where fileid = 2′
–PRINT @Query
exec sp_executesql @Query,@SqlParams, @LogFileName OUTPUT
–PRINT @LogFileName
SET @Query = ‘USE ‘ + @DBNAME + ‘ ‘ + char(10)+”+char(10) + ‘ DBCC SHRINKFILE (”’ + @LogFileName+ ”’,1)’
– PRINT @Query
EXEC (@Query)
FETCH NEXT FROM DBLog INTO @DBNAME END close DBLog deallocate DBLog
Hi,
i have couple of questions in SSIS,
1. How to get the current configuration file name and path from the Script task component
i am running the package thru DTEXEC /config . Is there any way to get this info inside the package. Also i need to get the package path.
2. How to get the current package tasks list(in execution order) in the script task…
i am able to get it for other package by creating the object instance.. but that too gives the tasks in alphabetical order.. not in the execution order.
would appreciate if you could help me on this.
Thanks.
Eid Ename Parentid
1 Amit 0
2 Jayesh 1
3 Mitesh 1
6 Rupesh 2
10 Raj 6
12 Ritesh 1
I have one table with child and parent relationship
eid is autoid and parent id is parent id.
i want output as follows(tree) in MSSQL 2000
Amit
-Jayesh
-Rupesh-Raj
-Mitesh
-Ritesh
Hi Pinal!
I am new to this and i need to create a table with 1,000,000 entries wherein in the first column (CardNo) it starts at 000000001 and so on. It has to be sequential. The second column (Pin) must be composed of 3 random numbers from 000-999.
Does any of you have an idea on how i can achieve this?
I am really hoping you can give me a solution to my problem.
Thanks a lot in advance. :)
*I already posted this in another page but i think i posted it in the wrong topic.
I Tried using the code below but it doesn’t work.
I don’t know what code to use so i put together some of the codes i found in the internet. The first insert statements works when i separate it from the pin.
The random number generator works when it is not inside the insert statement.
Can somebody tell me what i am doing wrong here?
DECLARE @Number As int
DECLARE @NumberPin As int
SET @Number = 000000001
WHILE @Number < 000000003
BEGIN
INSERT INTO CardTemp(CardNumbers)
VALUES (@Number)
INSERT INTO CardTemp(Pin)
VALUES (@NumberPin)
SET @NumberPin = (SELECT FLOOR(999*RAND()))
SET @Number = @Number + 1
END
– Mirei
You can add leading zero’s into VARCHAR but not INT column. the alternate ways is to have like this.
create table CardTemp
(
id int,
CardNumbers as (right(’00000000′ + CONVERT(varchar(10),id),10)) PERSISTED NOT NULL,
Pin int
)
DECLARE @Number As int
SET @Number = 000000001
WHILE @Number < 000000003
BEGIN
INSERT INTO CardTemp(id,Pin)
select @Number,FLOOR(999*RAND())
SET @Number = @Number + 1
end
Results will be
id CardNumbers Pin
———– ———– ———–
1 000000001 99
2 000000002 170
Hope this helps ur problem
Hi,
I would like to know, is there a way to use DB files without installing MS SQL Server ?
Thank you.
@praveen kv
To write a stored procedure,following syntax will help:
CREATE OR REPLACE PROCEDURE[Schema.]
( {IN,OUT,IN OUT} ,…) {IS, AS}
declarations;
declarations;
BEGIN
;
EXCEPTION
;
END;
Jeet.
To write a stored procedure,following syntax will help:
CREATE OR REPLACE PROCEDURE[Schema.] ProcedureName
( Argument {IN,OUT,IN OUT} Data Type,…) {IS, AS}
variable declarations;
constant declarations;
BEGIN
PL/SQL subprogram body;
EXCEPTION
Exception PL/SQL block;
END;
Jeet.
Note that this site is for MS SQL Server and not for Oracle
Hi
I have created a stored procedure that monitors a table with “posttime” column.
this procedure takes as input “date” from user and it should return all records on that date .
post time uses getdate function to enter date value in it.
Problem is that i only want user to enter date part of getdate and procedure shd return all values with time atamps on that date ??
can you help ?
My sample script is
create procedure check_DDL
@date datetime
as
select * from ddl_log
where postTime = @date
Go
It should be
create procedure check_DDL
@date datetime
as
select * from ddl_log
where
postTime >= dateadd(day,datediff(day,0,@date),0)
and
postTime < dateadd(day,datediff(day,0,@date)+1,0)
Go
@Zubaria,
u can use the Convert function in ur procedure to remove the time from the date
create procedure check_DDL
@date datetime
as
select * from ddl_log
where cast(convert(varchar(10), postTime,101)as datetime)= cast(convert(varchar(10),@date,101)as datetime)
Go
Hope this will help ur query
select * from v$session where upper(program) like ‘%APP%’;
The above statement is oracle database. Does anyone have an idea how do i use in sql ?
Thank you
@Tejal.
SQL Server by default is not case sensitive, so you need not use Upper function, You can use same script with little modification.
Select *
From V$session
where Program like ‘%APP%’
This should work.
~IM.
Dear Pinal,
I regullary visit your blog and I find it very useful. I am living in Serbia, so it would be very difficult to visit yours Gandhinagar SQL Server User Group Meetings. It would be very useful if your meetings are recorded with camera and then published here or somewhere else, or if you could attach all presentations materials from meetings. Just keep up with your excellent work !!!
Sincerely
Aleksandar Joksimovic
@ganesh,
to select a record which has the row number 5 in the database:
It is not possible without making reference to some sequence field.Unless you specifically design a “row number” or other sequence field into the table, the rows have no inherent sequence.So if there is a field that you can sequence the records on, you can ask for record number 5.
Jeet.
Hi pinal
1.
I have SQL server 2005. In my network anothere system SQL server 2008 is installed. Can I connect to that system?
2.
If first one not possible, Can I take backup of 2008 server and dump in 2005 server?
Please replay.
@avinash,
Yes, you can connect just fine.
hi
i have two tables
first tabe
Productfamilyid productfamilyname
1 fam1
2 fam2
3 fam3
4 fam4
5 other
secon table
ID Productfamilyid Productname
1 1 a
2 1 b
3 2 c
4 2 a
..
…
…
10 5 x
11 5 y
12 5 z
i want output like this
productfamilyid productfamilyname
1 fam1
2 fam2
3 fam3
4 fam4
5 x
5 y
5 z
note:-instead of others i want to display the productname based on that others id
could u please help me
thanks
Prasad Gopathi
Hi pinal
I am not able to connect to sql server 2008 from sql server 2005. I am getting follwoing error:
TITLE: Connect to Server
——————————
Cannot connect to PC5\SQLEXPRESS.
——————————
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————
Any idea!!! please.
@Prasad
Here’s one method:
WITH
first_tabe(Productfamilyid, productfamilyname)
AS
(
SELECT 1, ‘fam1′ UNION ALL
SELECT 2, ‘fam2′ UNION ALL
SELECT 3, ‘fam3′ UNION ALL
SELECT 4, ‘fam4′ UNION ALL
SELECT 5, ‘other’
),
secon_table(ID, Productfamilyid, Productname)
AS
(
SELECT 1, 1, ‘a’ UNION ALL
SELECT 2, 1, ‘b’ UNION ALL
SELECT 3, 2, ‘c’ UNION ALL
SELECT 4, 2, ‘a’ UNION ALL
SELECT 10, 5, ‘x’ UNION ALL
SELECT 11, 5, ‘y’ UNION ALL
SELECT 12, 5, ‘z’
)
SELECT
Productfamilyid,
productfamilyname
FROM
first_tabe
WHERE
productfamilyname ‘other’
UNION ALL
SELECT
Productfamilyid,
productname
FROM
secon_table
WHERE
EXISTS
(
SELECT
*
FROM
first_tabe
WHERE
first_tabe.Productfamilyid = secon_table.Productfamilyid
AND first_tabe.productfamilyname = ‘other’
);
1. have two tables
First table
Productfamilyid productfamilyname
1 fam1
2 fam2
3 fam3
4 fam4
5 other
second table
ID Productfamilyid Productname
1 1 a
2 1 b
3 2 c
4 2 a
..
…
…
10 5 x
11 5 y
12 5 z
I want output like this
productfamilyid productfamilyname
1 fam1
2 fam2
3 fam3
4 fam4
5 x
5 y
5 z
Note:-instead of others i want to display the product name based on that others id
@Farhad
Please visit : SQL SERVER – 2008 – Introduction to New Feature of Backup Compression
http://blog.sqlauthority.com/2008/07/03/sql-server-2008-introduction-to-new-feature-of-backup-compression/
Thank you IM. I run the following statement against master database in sql 2005.
Select *
From V$session
where Program like ‘%APP%’
it gave me following message
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘V$session’.
is there any sql equalvilent table for V$session ? or this table need special permission to view in sql 2005. ?
hi
I have 2 tables
1)
FamID FamName
1 fam1
2 fam2
3 fam3
4 Others
2)
prodid FamID Prodname
1 1 A
2 1 B
3 2 M
4 2 N
5 4 x
6 4 y
7 4 z
i want out put like this
FamID FamName
1 Fam1
2 Fam2
3 Fam3
4 x
5 y
6 x
note:-i wnat distinct famalies of first table
and based on that others ID i want to display product name in one query
like above o/p
instead of OTHERS i want to show secong table product names
could you please help me .
Thanks & Regards,
Prasad Gopathi
is there any sql table or procedure to monitor web server ?
From command line i get result whether web server/services is up or down.
Monitoring Weblogic Web Server
java weblogic.Admin –url $HOST:$PORT -username
USER –password PASS GETSTATE
any other help grealty apperciated.
Hi,
I have an issue related to report paramters
i have a report which group by data based on 2 paramters and also from and to dates The Issue is that whenever during the report execution I do a “Select all” from the second parameter List–and when I click view reports it does not display the report. it throws an error
Default value or value provided for the report parameter ‘ProcessNm’ is not a valid value.
But this error is not thrown always. It is weird that sometimes it works perfectly with “Select all” the parameters.
I assume that the above error could be occurring since it is trying to pass a comma separated parameter string into the dataset query which accepts this parameter.
If we may think that this is a data parsing issue due to the fact that staging database has different data than development, but then it should never execute it from SSRS at all. The issue is that it execute same query with all the parameters selected sometimes.
The interesting part is that The error mentioned above does not appear always and instead it just clears out the parameter
Another thing observed was that if the refresh button (not view report button) continuously it displays the report correctly once and next time it throws error then again displays correctly.
The SSRS environment on staging and development are SQL 2005, but on staging SSRS is 2005 but database is SQL2008.
The SSRS logs shows the error which appear randomly appear while calling GetReportParamters internal function
The same query was captured from tracer when it worked on staging and was executed and works fine from backend.
Also there are no null values in the parameter lookup tables
Anyone faced this situation in your report with muliple paramter “select all”
Appreciate any help or clues towards the resolution.
@Prasad,
Firstly, Please post your question once.
Secondly, There is no consistency in your question which I do not understand, was that a mistake or your requirements was wrong, anyways here is the script.
Lets First create table and insert some sample data which you provided.
create table Table1 ( FamID int , FamName varchar(20))
insert into Table1 values ( 1, ‘fam1′)
insert into Table1 values ( 2, ‘fam2′)
insert into Table1 values ( 3, ‘fam3′)
insert into Table1 values ( 4, ‘Others’)
create table Table2 ( Prodid int, FamID int, Prodname varchar(20))
insert into Table2 values ( 1, 1, ‘A’)
insert into Table2 values ( 2, 1, ‘B’)
insert into Table2 values ( 3, 2, ‘M’)
insert into Table2 values ( 4, 2, ‘N’)
insert into Table2 values ( 5, 4, ‘x’)
insert into Table2 values ( 6, 4, ‘y’)
insert into Table2 values ( 7, 4, ‘z’)
– This is response to your first Question
select distinct A.FamID
, A.FamName
from Table1 A
where A.FamName ‘others’
union
select A.Famid
,B.ProdName
from Table1 A
join Table2 B on A.Famid = B.FamId and A.FamName = ‘Others’
– This is response to your last Question
select identity ( int , 1,1) FamID
,FamName
into #DisplayResults
from (
select A.FamName
from Table1 A
where A.FamName ‘others’
union
select B.ProdName
from Table1 A
join Table2 B on A.Famid = B.FamId and A.FamName = ‘Others’
) X
select * from #DisplayResults
~ IM.
@Tejal,
Syntax that I provided is right. But the table name you used in that query might not exists in sql server database on which you are executing that query.
You can get details of what sql quiries are currently executing in sql server from DMV, sys.dm_exec_requests , you can refer below link to get complete script.
http://www.sqlservercentral.com/articles/DMV/64425/
~ IM.
@ MP.
I dont have an answer for you at this time.
You said in your description, you ran a trace when report was running fine from SSRS. When the same query executed on back end, it executed fine with no issues.
Sir, why are you tracing when everything is working fine. Trace when report does not run. When it gives you error, Trace at that time. And then run the same script on database back end.
Also, test internal function GetReportParamters with different input values.
it would be easy for us, if you share sql of that query.
~ IM.
@Imran Mohammed
Thank you for helping out readers of this community. You are one asset!
Regards,
Pinal
Hi ,
I have Three Tables
1 Table:-
ProductID ProductName
1 Prod1
2 Prod2
3 Prod3
4 Prod4
5 Prod5
6 prod6
2 Table:-
ProdFamID ProdFamName
1 Fam1
2 Fam2
3 Fam3
4 Fam4
5 Other
3 Table:-
ProdTransID ProdFamID ProductID
1 1 1
2 1 2
3 2 1
4 3 4
5 3 5
6 5 4
7 5 5
8 5 6
I WANT OUTPUT LIKE THIS:-
ProdFamiID ProdFamName
1 Fam1
2 Fam2
3 Fam3
4 Fam4
5 Prod4
5 Prod5
5 prod6
NOTE:-
i want to select distinct of ProdFamname in second table
at the same time i want to select instead of OTHER
i want to select productname based on 3rd table
that means in third table Other contain three ProductID’s
based on that other id’s i want to select Productname at the same time i want to select distinct of ProdFamname in second table
as per above output
pla help any one it’s urgent in my application
Hi,
I am posting this again.
I want a database, like Access ie it can work with out client-server architecture and which has some features like indexing, views, SP.
Where can i look upto for such DB ?
Thanks.
Hello Pinal.
I am a frequent reader of your blog.
I have question for you:
Can we upgrade a production server which is on sql server 200o to sql server 2008?.
If we upgrade, do the DTS packages work without any compatibility problems in sql server 2008 SSIS ?
And what are the major precautions to be taken for the upgrade from 2000 to 2008?
Thank You
Tom
Thank you IM for your help. I’m using following sql statement to monitor my application services for ERP system.
/* Monitor Application server services.*/
DECLARE @count INT
SET @count =(select count(*) FROM sys.dm_exec_sessions WHERE host_name = ‘myserver’)
/* The application services running are > 8 */
if @count < 8
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = myservermailprofile,
@recipients = ‘test.yahoo.com’,
@subject = ‘Application services on my server (Application Server) is Down’,
@body =’Application services on myserver (Application Server ) is down according to the established “resource monitoring rules”.’,
@importance = ‘High’
END
How do i find out What time of day has the most active users in our application through sql statement.
Thank you
Tejal
@Ritest,
Why dont you make use of Northwind, Pubs or AdventureWorks database. These are sample database. You will find plenty of tables, views stored procedure to work with.
Look at this link to get more details :
http://blog.sqlauthority.com/2008/08/10/sql-server-2008-download-and-install-samples-database-adventureworks-2005-detail-tutorial/
~ IM.
Hi PinalDave
I have seen the below mentioned in a coding standards document.
“Ensure that large text and binary columns are listed at the end of the columns list in the SELECT statement.”
Why we have to use like that
Thanks in advance
Robin
Hi Pinal,
Good to see this type of informative site which is to the point.
I would like you to publish a artical related to SQL script versioning.
Today I saw a Pool at http://www.SQLServerCentral.com and question is :-
Which Version Control System do you currently use to store you database scripts?
I am a single software developer (without any team), using VB.Net 2008 and SQLServer 2008 Express
I hope this kind of artical will help Script programmer to maintain the version history.
:)
Hi Pinal.
I need the procdure of how to do replicate tables from Oracle Database to SQL Server 2008. (Transactional Replication)
What is required for the replication to take place.
Can you list me the procedures to do for establishing the Replication.
Have A Nice One
Thank You
Tom
Hi Pinal, I like you solution for the “Challenge 4″, actually all good information on the blog, but you solution actually fails when A,B or C have alphanumerics on it except when in the first byte. Try ’1AB-2C-3DEF’.
-Cleber.
Hi Cleber,
Did not test with that, good catch.
Best Regards,
Pinal
@ ParameJeet
Very Good Question.
I been looking for this information too, this is what I have concluded, I did not do any research but I been hearing from my colleagues.
1. Third Party Client tools for SQL Server like TOAD for SQL Server (I believe they provide version control for scripts) I am not sure, I heard some one saying this for Toad – Oracle.
2. We use IBM Rational Clear Case for version control in my company.
SQL Joke : How about storing scripts in ExecuteSQLTask in DTS package. DTS package is the only place in Sql Server which has version control feature. (Just joking).
~ IM.
I’ve read that SQL Server 2005 supports statement-level recompile. Instead of having all queries in the stored procedure recompiled, SQL Server can now recompile individual statements.
When I create following procedure
SET NOCOUNT ON;
USE Northwind;
IF OBJECT_ID(‘dbo.useKeepPlan’, ‘P’) IS NOT NULL
DROP PROC dbo.useKeepPlan;
GO
create procedure useKeepPlan as
create table #t (a int, b char(3))
– Make greater than 6 changes to #t
insert #t values (1, ‘abc’)
insert #t values (2, ‘abc’)
insert #t values (3, ‘abc’)
insert #t values (4, ‘abc’)
insert #t values (5, ‘abc’)
insert #t values (6, ‘abc’)
insert #t values (7, ‘abc’)
– Now reference #t
select count(*) from #t
go
and run it for first time I get
SP:Recompile
SQL:StmtRecomplie events
when select count(*) from #t statement is reached.
Should there only be SQL:StmtRecomplie event withouth entire procedure recompilation?
i have to combine records from two table,how can i do it,for ex:table 1 gives username,address and table 2 gives passportno then i want the results in a format username,address,passportno..plz give the reply ASAP.
Thanks Imran Mohammed for your reply.
What I meant was, I need a database engine like SQLce (compact edition) which I can deploy with my application and I don’t need to install SQL server to use the DB.
And which has features like indexing, views.
Hope I am not asking too much. :)
Thanks again.
Hi Pinal,
Kindly let me know the detail concept of SQl Execution plan.
Thanks and Regards,
Jyoti
Dear Sir
I am create a database then create more tables in same field in more tables. How To Delete Particular Record in a database. How to use Sql Server Query Pl Reply
me.
i have create unique field in more table. how to check unique fields how to use sql server 2005 query reply me.
Sir,
I wanted to know that developing the Database. why we should create store procedure on table what is the purpose it is can you show you me this
hi
i have some probleme in that
i want to put search engine in my site that enable users to search for jobs by write the job name and determine the job fields and cities and if this job found in my site it will be displayed and if not the message will be displayed to the user to inform them that job is not found
pls help ,ineed the answer in these days because ihave xsame in this issue
thanx alot
Hello, i would like to ask you about Bidirectional Transactional Replication how difficult could be, if it is recommended, the version of sql server and what about conflicts?
Thank you.
Hi Pinal,
I have small query regarding SQL 2000. We used to send mail through xp_sendmail stored procedure on Windows server 2000. But past many month job is failing:
xp_sendmail @recipients=’abc@xyz.com’,
@subject=’test’
I got a fatal exception error. I tried it again straight away and it worked. Some times we need to forcefully restart mail session by xp_stopmail and xp_startmail.
Can you please guide me for the same.
Thanks
Gaurang
Hi Sir.
can i use a column name in freetext predicate ?
like here :
select cat_name,count(*) tot from tbcat_res c,tbanswer a where freetext((a.ans_Ques,a.ans_quesdesc),’xyz’) and freetext(c.cat_key,a.ans_cat) group by c.cat_name
please help.
Hay Man,
I am a big fun of your posts. Thanks for all the great job. You are a big help to the community.
I have question and that is:
How can I set up a schedule task to run a SP every 24 hours? Now, I can use a .bat file and use task scheduler of Windows or use SQL servers built in functionality if there is any.
Many thanks
@Shuaib
SQL Server Agent service in SQL Server executes scheduled jobs.
You can create a new job under sql server Agent. In steps for the job, you can include execute stored procedure script.
And then schedule that job, SQL Server Agent will run that job
IM.
@Anil
I installed the Developer Edition of SQL Server instead of the SQL Server Express edition that comes with VS2008.
I found that the install works better if you install SQL Server first, that way there is no need to uninstall the Express edition before installing SQL Server Developer edition. I don’t recommend having both editions installed. I had no end of problems until I figured out that having two versions installed was the issue. Uninstalling VS and SQL Server Express may be necessary, but I believe it was enough just to uninstall SQL Server Express and (re)install SQL Server Developer edition.
Jeet.
@Munaf
Stored procedures are very similar to user-defined functions, but there are subtle differences.
Stored procedures can be used by multiple users and client programs.Utilizing them properly in our code,we can decrease the time taken in development cycle.
Also,users can be granted permission to execute a stored procedure independently of underlying table permissions.
For example,consider the query:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = ‘FL’
We need to execute this query every time we need to select the Product,Quantity frm the ‘Inventory’ table without using a Stored Procedure,but when we use the stored procedure as follows,we get the same result with less execution time:
CREATE PROCEDURE GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
We can execute it as given here:
EXECUTE GetInventory ‘FL’
Jeet.
Hi,
I have a doubt on performance of a query.
Will the performance DETER or vary a lot if we use WITH function instead of table variable @Table in stored procedure.
Note : I know WITH function is used for recursive queries.But In the above example there is no recursive action at all.
So which one is better if no recursive action is there and just needed for temporarily storing the data ?
Any help is appreciated.
Thanks
Sudha
Mr. Dave,
Excellent site with great information — thank you very much, and keep up the great work!
Hey can you put your email address on the contact page so ican send you a email?
Great site!
Thanks for sharing your knowledge.
Hi Pinal,
I am your frequent reader of your SQL tips & tricks.
you have very good knowledge sharing scripts.
Pinal is it possible to have more DBA Related Knowledge on different Link or different tab.
Dear Sir,
I am developing one application which gets data from another application.In this i am retrieving data from one database (say db1) and inserting into another database (say db2).When i again insert into the db2 from db1 that time data repetition happens which creates problem.
And also i dont want to delete data from db2 as I am modifying it.
Kindly suggest any solution to retrieve data from db1 to db2 without repeating it.
Hello Sir,
I am a regular reader of your articles.
I want to write a select query to select data from an xml file.
Please tell me……
Thanks & Regards
Brijendra
Mr. Pinal,
I have gone through your one of the good article for sql that is SQL Server Cheat Sheet. thats good for me as a developer reference. i want to give one suggestion that you just give overview description of each. and if possible then for functions,constrains,TSQL statements and object operation with example.
Rgds,
Bhavank
Hi Pinal,
Which Index takes much disk space in SQL Server DB. Please explain the reason if applicable?
This was the question which my client for which i am not too sure about the answer.
Please clarify at the earliest!
Regards,
Varun.C
Hi Pinal,
I’m doing a project in c# and the back end is sql server2000 and my question is.. in my project im saving a whole bunch of text from richtextbox control in the db..in that the end user will be formatting the text like bold,italic,and underline and changing the font size and my problem is i wasnt able retrieve the format text..its coming without the formating made by the end user…can i know wat is the issue here..im saving the data in table with text as datatype
thank u
@Brijendra,
You can read XML values from xml file as:
declare @doc XML
set @doc=
‘
‘
SELECT x.v.value(‘@id[1]‘,’INT’) AS OrderID,
x.v.value(‘@ostatus[1]‘,’INT’) AS OrderStatus
FROM @doc.nodes(‘ord/order’) x(v)
Thanks,
Tejas
Hello Pinal ,
Personal request that you add a ‘subscribe via email’ option not only RSS as in office RSS feed reading is blocked for me ..
Regards
Siddanth
MCTS-70-431
hi
How To Return String value in sql Server 2005 as like this
Create Procedure vLogin
@UsrNa varchar(20),
@Pwd varchar(20),
@Result varchar(20) Output
as
if exists(Select UserName,Password from login Where UserName=@UsrNa and Password=@Pwd)
print ‘Valid UserName’
else
print ‘Invalid UserName’
Sql server Fine work but how to retrieve asp.net pl reply me
Sir
i’m software Trainee.I’m developing application with sql server as a back end.I want to ask , do i have to install complete sql server on clients machine so that he can easily work with my application or i can customize it.And also my client should be able to take back of data and zip the back files so that i can work on them in my office.
@kumar
User an OUPUT variable.
I have a table like follow
————————————————
DECLARE @t AS TABLE(
ROWID int, tmpdate datetime, discount float,
discountlevel char(1), discounttype char(1)
)
INSERT INTO @t
SELECT 1, ’2009-04-01′, 20, ‘C’, ‘T’
UNION ALL
SELECT 2, ’2009-04-02′, 20, ‘C’, ‘T’
UNION ALL
SELECT 3, ’2009-04-03′, 20, ‘C’, ‘T’
UNION ALL
SELECT 4, ’2009-04-04′, 20, ‘P’, ‘T’
UNION ALL
SELECT 5, ’2009-04-05′, 20, ‘P’, ‘T’
UNION ALL
SELECT 6, ’2009-04-06′, 20, ‘C’, ‘T’
UNION ALL
SELECT 7, ’2009-04-07′, 20, ‘C’, ‘T’
UNION ALL
SELECT 8, ’2009-04-08′, 20, ‘C’, ‘T’
UNION ALL
SELECT 9, ’2009-04-09′, 25, ‘C’, ‘B’
UNION ALL
SELECT 10, ’2009-04-10′, 25, ‘C’, ‘B’
UNION ALL
SELECT 11, ’2009-04-11′, 20, ‘C’, ‘T’
————————————————
And the required output is like
————————————————
FromDate ToDate Discount DiscoutLevel DiscountType
’2009-04-01′ ’2009-04-03′ 20 ‘C’ ‘T’
’2009-04-04′ ’2009-05-03′ 20 ‘P’ ‘T’
’2009-04-06′ ’2009-04-08′ 20 ‘C’ ‘T’
’2009-04-09′ ’2009-04-10′ 25 ‘C’ ‘B’
’2009-04-11′ ’2009-04-11′ 20 ‘C’ ‘T’
————————————————
In short I need to club the rows, from where information other then date is changing.
What could be the best way to achive this?
Many thanks…
sir,
I am your frequent reader of your SQL tips and its very helpful .
Iam an non degree holder but very much interested in database aplications with sql . As iam working on sql from past 5 yrs and have a very good knowledg relating to sql but being an non degree holder(10+2 fail) .Sir pz guide me
to do some certification in Microsoft SQL Server relating that i can get a good job(as iam working in a domestic company).
I need some tips related to SQL DBA . i want to become as like your successful career.Kindly guide me sir.
Thanks sir
Hello magz,
just try this following code into your application for inserting RTF formatted text into SQL database.
Just follow this code n Try it out !!!
Dim conn As New SqlClient.SqlConnection(“Server=myserver;Database=mydb;Trusted_Connection=yes;”)
Dim command As New SqlClient.SqlCommand(“insert into my_table(controlnumber,id_1, cde, de2, wa,stmp, eml) values ( ‘” & controlnumber.Text & “‘,’” & j1.Text & “‘,’” & bl.Text & “‘, ‘” & rm.Text & “‘, ‘” & wd.Text & “‘, ‘” & time100.Text & “‘,’” & (@RTFData) & “‘)”, conn)
Command.Parameters.Add(“@RTFData”, SqlDbType.Text).Value = richtextbox1.Rtf
conn.Open()
Command.ExecuteNonQuery()
conn.Close()
conn.Dispose()
Rgds,
Bhavank
Hi dave,
I would like to consult your professional SQL Server skill on this matter. Have you ever encountered this type of problem in SQL Server 2008 Ent Ed; Just recently we added another DB Instance with RS(Sharepoint Integrated) then for some reason we are having this weird log entry on our windows application keeps repeating every 5 secsonds which says:
Event ID 5084: “Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$TempDB” and
Event ID 5084: “Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$”
take note that these dbs are indeed set to 100 for compatibility level. Googled for answers but could not found any.. only same people having same issues..
Thanks for reading my post and hope to hear from you soon.
Hello Pinal,
UsreID UserName
640 Ram
641 bingo
742 cinu
743 zuli
744 kavi
The above result is based on order by UserID
But I want to get result like belwo
UsreID UserName
743 zuli
744 kavi
640 Ram
641 bingo
742 cinu
how can I write query for this
please advice me.
My requirement is
Iam getting 743 and 744 these two id’s are from previous page
So I want to show that two records (743, 744) on top and remaining recodrs below (743, 744) records.
Thanks,
Venkat
@Venkat
How do you know what page it is on? Is that a COLUMN?
ORDER BY Page, UserID
Hi Pinal,
I am getting error messgaes like below when executing script,how to suppresss these error meesges in sql 2000,if we cannot what is the work around
error
Msg 61110, Level 19, State 1, Line 0
The backup file is encrypted, and you have not supplied the correct decryption key for this file.
Note: The decryption key is the same as the one used to create the backup file.
script
– is backup file encrypted…?
declare @filename varchar(2000)
declare @verifystmt nvarchar(2000)
declare @physical_device_name varchar(2000)
select @physical_device_name = bh1.physical_device_name
from dbautils..backupHistoryRS_vw bh1, master..sysdatabases sd
,dbautils..backupHistoryRSlast_vw bhL
where sd.name not in (‘master’, ‘model’, ‘tempdb’) and
bh1.database_name = sd.name and — used to skip deleted db’
bh1.database_name = ‘dbocap’ and
bh1.type = ‘D’ and
bh1.database_name = bhL.database_name and — gets the last backup date to determine RECOVERY OR NORECOVERY
bh1.backup_start_date >= (select max(backup_start_date)
from dbautils..BackupHistoryRS_VW bh2
where type = ‘D’
and bh2.database_name = bh1.database_name
)
order by bh1.database_name, backup_finish_date
select @verifystmt = ‘xp_restore_verifyonly
@filename = ‘+””+@physical_device_name+””
exec(@verifystmt)
@varun
The above script is trying to verify the backup by restoring a backup file, backup file name and device name are passed as input parameters to this stored procedure xp_restore_verifyonly (User written stored procedure).
I believe when taking backup, some one has encrypted the backup, meaning password was provided at the time of taking backup, and when you are trying to restore that backup using above script, sql server needs that password to unlock backup file.
You have to find out what password was used while taking backup in order to decrypt that backup file
Also check script of stored procedure, xp_restore_verifyonly , may be password is hard coded in this stored procedure.
Let us know if this doesn’t solve your problem.
~ IM.
I have written the query .
ALTER FUNCTION [dbo].[fn_ComplexItemsTree03](@int_JobNo Int, @int_xBaseItemId int)
RETURNS
@tree_ComplexItems TABLE
(
int_Level int,
int_pBaseItemId int,
int_BaseItemId int,
chr_BaseItemId varchar(100),
chr_BaseItemDesc varchar(250),
dbl_Selected_Qty numeric(18,3),
dbl_Amount numeric(18,3),
dbl_Labour numeric(18,3),
dbl_Plant numeric(18,3),
dbl_pMaterial numeric(18,3),
dbl_tMaterial numeric(18,3),
dbl_SubContract numeric(18,3),
dbl_Staff numeric(18,3),
dbl_Gen_Expense numeric(18,3),
dbl_Prov_Sums numeric(18,3),
dbl_onCost numeric(18,3)
)
AS
BEGIN
DECLARE @int_pBaseItemId bigint
DECLARE @int_BaseItemId bigint
DECLARE @int_Count bigint
;WITH cattree(int_Level, int_pBaseItemId,
int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
dbl_Selected_Qty, dbl_Amount, dbl_Labour,
dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost) AS
(
SELECT
int_Level, int_pBaseItemId,
int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
dbl_Selected_Qty, dbl_Amount, dbl_Labour,
dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost
FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) A
WHERE int_pBaseItemId = 0
UNION ALL
SELECT c.int_Level, c.int_pBaseItemId,
c.int_BaseItemId, c.chr_BaseItemId, c.chr_BaseItemDesc,
c.dbl_Selected_Qty, c.dbl_Amount, c.dbl_Labour,
c.dbl_Plant, c.dbl_pMaterial, c.dbl_tMaterial, c.dbl_SubContract,
c.dbl_Staff, c.dbl_Gen_Expense, c.dbl_Prov_Sums, c.dbl_onCost
FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) c
INNER JOIN cattree d
ON c.int_pBaseItemId = d.int_BaseItemId
)
INSERT INTO @tree_ComplexItems
SELECT int_Level, int_pBaseItemId,
int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
dbl_Selected_Qty, dbl_Amount, dbl_Labour,
dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost FROM cattree;
RETURN
END
The output I get is like
int_Level int_pBaseItemId int_BaseItemId dbl_Amount 1 0 1 100
2 1 2 50
2 1 3 50
3 2 4 25
3 2 5 25
3 3 6 20
3 3 7 20
3 3 8 10
What I want is
int_Level int_pBaseItemId int_BaseItemId dbl_Amount 1 0 1 100
2 1 2 50
3 2 4 25
3 2 5 25
2 1 3 50
3 3 6 20
3 3 7 20
3 3 8 10
The problem is with the order of the out put.
Please help me identify, where I am going wrong in the function?
how to solve this problem below, and check what are the FK pointing to this PK.
Msg 3725, Level 16, State 0, Line 1
The constraint ‘RHPLCA_EIXO_P’ is being referenced by table ‘RHCOMP_PERFIL’, foreign key constraint ‘RHCOMP_PERFIL_F11′.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
thank you very much.
celio. sao paulo – brazil.
The output I get is like
int_Level – int_pBaseItemId – int_BaseItemId – dbl_Amount
1 – 0 – 1 – 100
2 – 1 – 2 – 50
2 – 1 – 3 – 50
3 – 2 – 4 – 25
3 – 2 – 5 – 25
3 – 3 – 6 – 20
3 – 3 – 7 – 20
3 – 3 – 8 – 10
What I want is
int_Level – int_pBaseItemId – int_BaseItemId – dbl_Amount
1 – 0 – 1 – 100
2 – 1 – 2 – 50
3 – 2 – 4 – 25
3 – 2 – 5 – 25
2 – 1 – 3 – 50
3 – 3 – 6 – 20
3 – 3 – 7 – 20
3 – 3 – 8 – 10
I was not sure how to sorting the output of the function,
The out put I got was sorted as
order by int_Level, int_pBaseItemId, int_BaseItemId
What I have done now is to add a field that will help me sort the records.
now my funtion is,
ALTER FUNCTION [dbo].[fn_ComplexItemsTree03](@int_JobNo Int, @int_xBaseItemId int)
RETURNS
@tree_ComplexItems TABLE
(
int_Level int,
int_pBaseItemId int,
int_BaseItemId int,
chr_BaseItemId varchar(100),
chr_BaseItemDesc varchar(250),
dbl_Selected_Qty numeric(18,3),
dbl_Amount numeric(18,3),
dbl_Labour numeric(18,3),
dbl_Plant numeric(18,3),
dbl_pMaterial numeric(18,3),
dbl_tMaterial numeric(18,3),
dbl_SubContract numeric(18,3),
dbl_Staff numeric(18,3),
dbl_Gen_Expense numeric(18,3),
dbl_Prov_Sums numeric(18,3),
dbl_onCost numeric(18,3),
chr_Tree varchar(100)
)
AS
BEGIN
DECLARE @int_pBaseItemId bigint
DECLARE @int_BaseItemId bigint
DECLARE @int_Count bigint
DECLARE @int_MaxLevel bigint
SELECT @int_MaxLevel = MAX(int_LEVEL) FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId)
;WITH cattree(int_Level, int_pBaseItemId,
int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
dbl_Selected_Qty, dbl_Amount, dbl_Labour,
dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost, chr_Tree) AS
(
SELECT
int_Level, int_pBaseItemId,
int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
dbl_Selected_Qty, dbl_Amount, dbl_Labour,
dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost,
convert(varchar(100),cast(ABS(int_pBaseItemId) as varchar(10)) + ‘.’ + cast(ABS(int_BaseItemId) as varchar(10))) chr_Tree
FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) A
WHERE int_pBaseItemId = 0
UNION ALL
SELECT c.int_Level, c.int_pBaseItemId,
c.int_BaseItemId, c.chr_BaseItemId, c.chr_BaseItemDesc,
c.dbl_Selected_Qty, c.dbl_Amount, c.dbl_Labour,
c.dbl_Plant, c.dbl_pMaterial, c.dbl_tMaterial, c.dbl_SubContract,
c.dbl_Staff, c.dbl_Gen_Expense, c.dbl_Prov_Sums, c.dbl_onCost,
case when DataLength(d.chr_Tree) > 0
then convert(varchar(100),d.chr_Tree + ‘.’ + cast(ABS(c.int_BaseItemId) as varchar(10)))
else convert(varchar(100),cast(ABS(c.int_pBaseItemId) as varchar(10)) + ‘.’ + cast(ABS(c.int_BaseItemId) as varchar(10)))
end as chr_Tree
FROM [dbo].[fn_ComplexItemsTree02] (@int_JobNo, @int_xBaseItemId) c
INNER JOIN cattree d
ON c.int_pBaseItemId = d.int_BaseItemId
)
INSERT INTO @tree_ComplexItems
SELECT int_Level, int_pBaseItemId,
int_BaseItemId, chr_BaseItemId, chr_BaseItemDesc,
dbl_Selected_Qty, dbl_Amount, dbl_Labour,
dbl_Plant, dbl_pMaterial, dbl_tMaterial, dbl_SubContract,
dbl_Staff, dbl_Gen_Expense, dbl_Prov_Sums, dbl_onCost,
chr_Tree + REPLICATE(‘.0′,@int_MaxLevel-dbo.fn_CountChar(chr_Tree,’.')) chr_Tree
FROM cattree;
RETURN
END
now if i use the query
select * from [dbo].[fn_ComplexItemsTree03] (7106, 1)
order by chr_Tree
I will get my desired output. I expected the CTE to do the sorting on its own. I guess, for that I should have used a cursor and a recursive call.
Thanks
@Celio.
Error Message you posted : The constraint ‘RHPLCA_EIXO_P’ is being referenced by table ‘RHCOMP_PERFIL’, foreign key constraint ‘RHCOMP_PERFIL_F11′.
It clearly says, Constraint Name : RHPLCA_EIXO_P is referenced by a table RHCOMP_PERFI, with foreign Key constraint , Constraint Name, RHCOMP_PERFIL_F11
So If you want to drop RHPLCA_EIXO_P constraint, then you first have to go to RHCOMP_PERFI Table and drop Foriegn key constraint RHCOMP_PERFIL_F11.
You can use below script to drop this foreign constraint on that table,
ALTER RHCOMP_PERFI
DROP CONSTRAINT RHCOMP_PERFIL_F11
Then you can drop your first check constraint RHPLCA_EIXO_P.
Warning: By executing above script, you are actually dropping constraint on a table, this will lead to data inconsistency, Foreign key constraints are created to maintain Data consistency. Make sure you know what you are doing, dont be in a situation in which you try to make one thing and you break 3 other processes.
Check with senior DBAs, ask for assistance if needed.
~ IM,
@gpshiburaj
Looking at the sample data you provided as output, Looks like there is no specific order .
No Specific fashion is being repeated.
I have no clue, I looked at it, but again to me it looks like there is no repeating fashion in the data,
if you have more info, please provide. I will try to look into this again.
Is there any other column which makes order of the out put like this ??
~ IM.
ok. obrigado pela ajuda.
hi pinal
I am a sailent reader of your site .I had seen your site one month back only.
it is having a lot of very good and essential information which is very much useful to everyone.
thanks for your information
this site is very good.
thanks
rajeev
Hi,
Thanx for your informative site – I really appreciate the time and effort you have put in to help the community.
I have a question about cursors and stored procs that I’m hoping you can assist me with.
I would like to write T-SQL script that I can schedule to run after my database backups are run, to force the log files to be shrunk as small as phsyically possible.
My database backup process is a FULL backup – so I’m not worried about the log files after the backups are done, and in one case, the log file grew to over 300 GB.
I basically want to select all the user database into a cursor, and then loop through them, shrinking the log file for each database.
This is what I have so far, but the part that is missing is getting the log file name from sys.database_files into a variable that I can then use in the EXEC statement to execute DBCC SHRINKFILE (@logFile, 1):
– declare some local variables to use
DECLARE @useDb varchar(60) – use statement
DECLARE @db varchar(50) – database name
DECLARE @sql varchar(2000) — sql queries to be executed
DECLARE @logFile varchar(100) — name of log file
– declare the cursor
DECLARE dbs CURSOR FOR
SELECT Name
FROM sys.databases
WHERE Name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’)
ORDER BY Name
– open the cursor
OPEN dbs
– get the next record into local variables
FETCH NEXT FROM dbs INTO @db
– while there are more records to process
WHILE @@FETCH_STATUS = 0
BEGIN
print @db
SET @useDb = ‘USE ‘ + @db + ‘; ‘
SET @sql = @useDb + ‘CheckPoint’
print @sql
exec (@sql)
SET @sql = @useDb + ‘BACKUP LOG ‘ + @db + ‘ WITH TRUNCATE_ONLY’
print @sql
exec (@sql)
SET @sql = ‘SELECT @logFile = Name FROM ‘ + @db + ‘.sys.database_files WHERE TYPE = 1′
print @sql
–exec(@sql) — need to get this result into @logfile in order to proceed as required
print ‘———————’
– get the next record to be processed
FETCH NEXT FROM dbs INTO @db
END
– close the cursor
CLOSE dbs
– and cleanup
DEALLOCATE dbs
@Alon,
Execute below statement after you take log backup with truncate only option. Put these statements in cursor.
Should work fine,
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @SQLString NVARCHAR(500)
DECLARE @LogFileName varchar(128)
DECLARE @Db varchar(128)
DECLARE @Sqlcmd2 NVARCHAR(500)
– set @Db = ‘AdventureWorks’
SELECT @SQLString = N’select @LogFileName = ”[''+Name+'']” from ‘+@Db+’..sysfiles where substring (filename , len(filename)-2 , 3) = ”ldf”’
SET @ParmDefinition = N’@LogFileName varchar(128) OUTPUT’
EXEC sp_executesql @SQLString,@ParmDefinition,@LogFileName=@LogFileName OUTPUT
SELECT @Sqlcmd2 = N’DBCC SHRINKFILE (‘+@LogFileName+’ , 1) ‘
EXEC Sp_executesql @Sqlcmd2
Let us know, if you need more help with this.
~ IM.
@Alon,
Forgot to mention one important note, the above script that I posted will work fine if you have only one log file per database.
If you have multiple log files, then you need to store results (Log File Names) in temporary table instead of variable, and then write another cursor or while loop and execute dbcc shrinkfile command on each log file.
~ IM.
Hi Dear,
Its very nice site to seee anything about SQL Server.
Thanks for made such type of Site.
Regards,
Anil Chauhan
Hi there,
I am new in SQL server world. I have 1 problem, maybe someone can help me.
I have around 20 jobs in SQL Server Agent of 2005. I want to take backup of all those jobs.
I can take the backup of databases, but don’t know how to take the backup of jobs.
Thanks..
somia
@Somia,
Job information is saved in msdb database.
If you take backup of msdb, it is same as you took backup of all jobs scheduled through SQL Server Agent.
~ IM.
Hi Pinal –
Thanks for responding to my query posted in MSDN. I really appreciate that.
I followed all the instructions exactly as specified in the article (http://blog.sqlauthority.com/2008/12/02/sql-server-2008-install-sql-server-2008-how-to-upgrade-to-sql-server-2008-installation-tutorial/ ). I’m still getting the error.
PrepInstance() failed for .
The following features are missing: Full Text Search
Fix the problem and re-run the setup
I don’t understand why the error is coming with “.” insted of the instance that I have selected during installation “MSSQLSERVER” .
Please let me know if you want to know more details .. My OS and other specs.
Thanking you in advance
Best Regards
Hi Dave,
I have followed your website and I really like the way you are able to break down complex information into simple step by steps instructions.
I am trying to implement a trigger that would populate another table with the additional information once the item number is selected. But I am getting a some errors can you tell me what I am doing wrong here?
CREATE TRIGGER trg_FoodJournal
ON FoodJournal
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
Insert into FoodJournal (Foodtype,Food, Calories, FatGrams, ProtienGrams,CarbGrams, pFat, pProtien, pCarbs)
Select Foodtype, Food, Calories, FatGrams, ProtienGrams,CarbGrams, pFat, pProtien, pCarbs
FROM FoodData
Where FoodID.FoodData = FoodID.FoodJournal
END
GO
Hi Eric,
What table do you want to populate when the item number is selected, FoodData or FoodJournal?
The trigger that you’ve written is wrong because you’ve created a trigger on FoodJournal. This trigger will get fired when any row is inserted into FoodJournal. But the trigger inturn is re-inserting back into FoodJournal by selecting some row from FoodData…
Moreover, the query is also wrong as you are selecting from FoodData but in the where clause you are using an alias “FoodID” ??
Should the from clause be “FROM FoodData FoodID” ?
And i think you need to create your trigger on FoodData table. Can you give me details of the tables and what you want to insert and where please..
Hi Ryan,
Thanks for the reply!
So basically, when a user enters
The user enters the food and the serving size of what he or she has eaten into the FoodJournal.
Since most users dont know the nutritional value of what they have eaten in detail.
I want to populate the remaining nutritional information that is in the FoodData into theFoodJournal.
The FoodID is the Unique Identifier for foods entered in both tables. So the trigger is supposed to use the FoodID to identify the entry that needs the info and copy the remaining data from the FoodData into the FoodJournal.
I hope this helps.
Thanks
E.
Hi Eric,
If i undertand correctly, you insert just the Food & ServingSize into FoodJournal.. The rest of the data you want to pull from the FoodData table right? I have modified your trigger to do that, see if you understand it.
Just a word of caution, I wouldn’t advice you to use a trigger for this purpose, if you are inserting Food & ServingSize into FoodJournal via a Stored Procedure, then i would advice you to modify the stored proc and do an inner join with the FoodData and get all the information you need and then insert into the FoodJournal table in one go. In this case you don’t need any trigger to update the remaining additional columns.
CREATE TRIGGER trg_FoodJournal
ON FoodJournal
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
–
– Since the row is already inserted,
– we have to update this row with additional info from the food table
–
Update FJ
Set FJ.Foodtype = FD.Foodtype,
FJ.Food = FD.Food,
FJ.Calories = FD.Calories,
FJ.FatGrams = FD.FatGrams,
FJ.ProtienGrams = FD.ProtienGrams,
FJ.CarbGrams = FD.CarbGrams,
FJ.pFat = FD.pFat,
FJ.pProtien = FD.pProtien,
FJ.pCarbs = FD.pCarbs
From FoodJournal FJ – The Food Journal Table that has the new entry
Inner Join Inserted NewRow – This magic table ontains the newly added row(s) into the food journal table
On ( FJ.FoodID = NewRow.FoodID )
Inner Join FoodData FD – This table has the additional info for food data
On ( FD.FoodID = FJ.FoodID )
END
GO
Hope this helps..
– Ryan Thompson
Ahhh! Got It! Thanks Ryan For All Your Help! I will give this a try and let you know how it goes!
Thanks Again!
E.
Dear Mr.Pinal,
I am using Asp and ms sql server 2005 for one of my project. In the project iam saving date from one form to multiple table where i am referring One perticular column id in all other tables . there are many people using the system so some times the id gets duplicated . How to solve the issue Pls help me out. Following is the save query iam using.
rsDwrNO.open “select max(Dwr_Id)+1 from Dwr_Master”,conn,adOpenForwardOnly
DwrCode=rsDwrNO(0)
sqlDwM=”Insert into Dwr_master(Dwr_Id,Mtp_Id,Hq_Cd,Hq_Name,Prmn_id)Values(“&trim(DwrCode)&”, “&trim(Sroute)&”,”&trim(request.form(“Rhqcode”))&”,’”&trim(request.form(“HqName”))&”‘,”&trim(session(“Emp_ID”))&”"
Insert into Dwr_Doc_Product(Dwr_id,Dwr_Doc_id,Dwr_Doc_Name)Values(“&trim(DwrCode)&”, “&trim(request.form(“DDcd”&i))&”,’”&trim(request.form(“DDnam”&i))&”‘)”
Hello Sir,
You r great hope,
I was trying to write a stored procedure as under
I have a database named family, having table itemlist
I wanted to generate Crystal report lables data with blank lables at top
USE [Family]
GO
/****** Object: StoredProcedure [dbo].[sp_ItemLables] Script Date: 06/05/2009 10:41:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ItemLables]
(
@repeatlables numeric( 3,0),
@blanklables int
)
as
declare @i integer
set @i = 0
delete from Family.dbo.Repeater
while @i < @repeatlables
BEGIN
insert into [Family].[dbo].[Repeater] (how_many) values (1)
SET @i = @i + 1
END
/*
@itmeui varchar(5000)
declare @pos int
set @pos = 0
CREATE TABLE #TempItemLables
(
ui uniqindentifier
)
*/
IF OBJECT_ID (N'#TempLables',N'U') IS NOT NULL
DROP TABLE #TempLables ;
select line1, line2, line3, line4, line5, line6, line7, line8, itemlist.ui
INTO #TempLables from [Family].[dbo].labels itemlist, [Family].[dbo].[Repeater]
/* insert top( @blanklables) into #TempLables( line1)
SET NOCOUNT ON
set @i = 0
while @i < = @blanklables
BEGIN
insert into #TempLables ( line1 ) value ( '')
END
*/
RETURN
can u please correct this procedure
HI, Imran Mohammed
I knew the backupfile is encrypted, I am actually writing a script like this.
ex:
if ( backupfile is encrypted )
print ‘encrypted’
else
HI, Imran Mohammed
I knew the backupfile is encrypted, I am actually writing a script like this.
ex:
if ( backupfile is encrypted )
print ‘encrypted’
else
print ‘not encrypted’
but actually in output I am getting.
output:
encrypted
Msg 61110, Level 19, State 1, Line 0
The backup file is encrypted, and you have not supplied the correct decryption key for this file.
Note: The decryption key is the same as the one used to create the backup file
I want output as only as below to be printed and supress the error meesage.
output :
encypted
hello,
sir i have a problem in my query and did’t found the result.
i want to sum the column of a table with respect to the type
1- purchase
2- payment
i write this query..
select supplierId,sum(Amount) as ‘Amount’
,(select sum(Amount) from tbl_supplierLedger where Type=’Payments’ group by SupplierID ) as ‘Payment’
from tbl_supplierLedger
where Type=’Purchase’ group by SupplierID
but the problem is that the subquery that i am using “select sum(Amount) from tbl_supplierLedger where Type=’Payments’ group by SupplierID” return 2 records and 1st query return 4 records thats y i am facing an error…
please replay me as soon as possible
Hi Pinal,
I have a heavy table on sql server 2005 with enable full text search on it, I want to get top N newest data by id or insert date with specific full text.
some query result with “Containstable” return over 500,000 record and take over 1 min, that is not good, also top n by rank not good for me because maybe return old data
Table info: Auto increment field, Id bigint as primary key Info Field type is NVARCHAR(MAX) to using full text Insert Date Field and desc index on it
* More than 28 million record now
* Insert More than 600,000 record per day
Hardware:
* 8 CPU 2.2 GHz (really 2 quad core cpu)
* 16 GB RAM
Software:
* Windows 2003 x64
* SQL Server 2005 Standard
Have any idea
Or can we modify (override) full text search rank method?
Thanks
@Salman,
Please post error message,
Mean while you can try this,
Replace your script with this script.
select supplierId
,SUM(Amount) as [Amount]
,( select sum(Amount) [Internal_Amount]
from tbl_supplierLedger
where [Type] = ‘Payments’
group by [SupplierID]
) Payment
from Tbl_supplierLedger
where Type = ‘Purchase’
group by SupplierID
– Try this example, I am doing the same
create table example1 ( eid int, ename varchar(10), Month_Name varchar(20), salary money )
insert into example1 values ( 1, ‘Imran’ , ‘Jan’ , 20000)
insert into example1 values ( 1, ‘Imran’ , ‘Feb’ , 20000)
insert into example1 values ( 1, ‘Imran’ , ‘Mar’ , 20000)
insert into example1 values ( 1, ‘Imran’ , ‘Apr’ , 20000)
insert into example1 values ( 2, ‘Taher’ , ‘Jan’ , 30000)
insert into example1 values ( 2, ‘Taher’ , ‘Feb’ , 30000)
insert into example1 values ( 2, ‘Taher’ , ‘Mar’ , 30000)
insert into example1 values ( 2, ‘Taher’ , ‘Apr’ , 30000)
GO
create table example2 ( eid int, ename varchar(10), Month_Name varchar(20), salary money )
GO
insert into example2 values ( 1, ‘Imran’ , ‘Jan’ , 20000)
insert into example2 values ( 1, ‘Imran’ , ‘Feb’ , 20000)
insert into example2 values ( 1, ‘Imran’ , ‘Mar’ , 20000)
insert into example2 values ( 1, ‘Imran’ , ‘Apr’ , 20000)
GO
select Eid
,SUM(salary) as [Salary]
,( select sum(Salary) [Internal_salary]
from example2
group by [Eid]
) Payment
from Example1
group by Eid
~ IM
in
Design table
(Design_nm,Design_Desc)
Work table
(Work_title,Design_nm,Client_nm)
i want
“Design_nm,No.of.Client.No.of.work”
notes:
in above result Display all the row of Design_table
if any design_name(design table) match with Design_nm(work table) than display total no of work and client else display only Design_nm(design table).
example:
(Design TABLE)
Design_nm,Design_Desc
(logo design,—-)
(website design,—)
(video design,—-)
(WORK TABLE)
Work_title,Design_nm,Client_nm
abc1,logo design,jayesh
xyz1,logo design,sapan
abc2,logo design.jayesh
zzz1,website design,jayesh
i want
Design_nm,No.of.Client.No.of.work
logo design,2,3
website design,1,1
video design, 0,0
Hello pinal,
I have a question,
I need to replicate few tables from production server to different server(RPTS Server). Can you tell me the easiest way to do it ? without usage of SSIS
regards,
Sri.
Hi,
Can you please tell me what is difference between index intersection and index join.
Thanks,
Kamlesh.
Hello Pinal,
I have been using database (e.g. ABC) for my web application. Web application is accessed by customers as well as internal users. I need to have a different database (XYZ) on the same server having some of the tables of database ABC. Database XYZ will be accessed by customers. I want to sync these databases in such way that data inserted into ABC will also be added to XYZ immediately and vice versa. So that data added/ updated by internal users and customers will be available to each other right away. I can’t have triggers on both sides because SQL server does not allow to do so.
Please suggest a solution.
Thanks a lot !
Prashant
Here is situation which you can come up with a solution
I have about 4000 customers out which there are 160 customers in a Group A and 200 in Group B which I can extract into Excel.
Now my issue is that I wish to change these customers ” terms of payment” let’s say from “net 30″ to “prepayment”.
How can I write a query and can pull the data from excel column in my where clause?
e.g.
update rm00101 set tor = “prepayment” where custid = “????’ and “?????’ and so on until the end of line.
Hi every one!
I can not create full-text catalog. I have read http://www.codeproject.com/KB/database/SQLServer2K8FullTextSearh.aspx in wich we use Storage node in the Object Explorer, but on my sql server 2008 I have not this node. I have google all web but I can not find the answer. Why I don not have Storage node?
Hi Pinal.
h r u ?
I am a dot net developer and also working in database (Sql Server 2005). Now I m thinking join a course in database (Sql Server) where I can improve my database (sql server) skills so that I can design my database in well manner and in a efficient way. So that my database can handle the millions of data in efficient way.
In short I want gud hand in database and to improve my skills.
So can u provide me some professionals who provide the facility in Delhi.
Regards,
Kailash Paliwal
how to backup remote system
regards
ganesh
Hi Pinal,
Can you explain what is the best way of fetching the orders with their total as last row with single sql statement. I did it using union, but it takes two queries to accomplish that.
What is the better way of doing it?
e.g.
select OrderNumber,OrderTotal as Total from tb_Order
union
select 9999,sum(OrderTotal) as Total from tb_Order
Dear Pinal,
I hope you are reading this message in best of your health,
Your Site is very informative and most of the solutions found here.
I hav 2 issue, would you be very kind to provide me some tips:
1) Protect SQL 2005 SP2 Database Access for BuiltIn Admins.
2) Create New SQL user Same as Existing SQL user with all previlleges and Accesses on Tables, SP’s, Functions and Views.
Looking forward for your reply
Thanks and Regards
Murtaza Rana
Dear Sir,
I want to know what is database commitment control in SQL2005. How can I use it for my database?
Prashant
Denish Mumbaiwala
Hi
just put count(*)
like this
select count(*),OrderNumber,OrderTotal as Total from tb_Order
@@Ganesh
Hi you cal do it by just set up linked server and ya you have to have access to that remote server. you can write a query
then you can write the query
@@sri
hi if you want to replicate once the ues bcp command or select * into statement
but if you also want to send changes then you have to set up replication
Hi Pinal,
I am a LAMP/ WAMP developer since past 3+ years. Right now I am working on SQL server 2005 to export data and import it to MySQL server (version-5).
I have to get one tables data only in which first I get the all fields name from SQL server. Same when I fired a query to get data of those fields in order of fields name which I get, it will return me an error that ” MSSQL query failed ”
If I removed few fileds from the query and run it again then I will get all the data successfully. And if I add again those removed fields in to query then again it will return same error.
Please reply me ASAP I am hanged here.
Thanks in advance
Hi Pinal,
I Have to capture events of a particular table using SSAS. Events are specified as conditions.
I have been asked to use Sequence clustering algorithm in SSAS . Can u please help me how to go about doing this task. The information available on the net is insufficient. Thanks
Hi,
Is there any way to Edit SSIS package?
Sir,
I am a begginner to SQL Server, when i execute my stored procedure i got the error like this,
Location: tmpilb.cpp:2530
Expression: fFalse
SPID: 159
Process ID: 1664
Description: Attempt to access expired blob handle (3)
Msg 3624, Level 20, State 1, Procedure SP400_SEARCHAPPLICANT, Line 787
A system assertion check has failed. Check the SQL Server error log for details
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I am using temporary table in this stored procedure when i searched in google i am not getting the way to handle it, i thought it is better to ask you.
Please help me to solve this because this is a big procedure for having many search criterias.
Thanking You
ShameerKhan.S
Hi Pinal,
Im planning to take certification in Microsoft Sql Server DBA. I want the latest dumps to prepare for the exam. Can you please help me in this.
Thanks
kumar
Hi
How to get first and last dates of current Financial year?
Please replay….
Regards
avinash
Means: First date of April and Last date of March
I tried to connect to Reporting Services 2005 server (windows 2003 server 32bit) from MS SQL SMS (windows 2008 server 64bit). I got the error:
Cannot connect to the SSRSServerName.
The request failed with HTTP status 401:Unauthorized. (Microsoft.sqlserver.management.UI.RSClient)
I m not sure that I missed something here.
Thanks
Thank you for your very timely post!
This post of MSDN saved me!
http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/9e01e870-0bff-4669-a385-2d9349b1a4f0
My buddy googled an error number and youir post came up!
The error generated as the party stated on an attempt to right click APP_DATA in VWD and create a new Data Base.
Error: 15372 on adding new sql database in VWD
Is there an equivalent in VWD in XP? Or is it indenpendent of Windows version?
Thanks!
–John
@Kumar,
My Personal Suggestion don’t ever study from Dumps to do certification , You will not learn anything, Try reading books. There are many books for specific exams, try reading those books.
~ IM.
Hi
Pinal
I have posted one doubt on “on June 18, 2009 at 1:23 pm” about the blob handle error i got while running a stored procedure.
Please help me how to solve this error.
Thanks
Shameer Khan
@somia
yes you can edit the ssis packege. just open and make changes .if it is saved in msdb database then dont forget to deploy again package. if it is saved in file system you dont have deploy it again.
Thank you
hey please help me out from this problem… please
I have some knowledge on sql server 2005 in my masters and now i graduated and I am in a confusion about what to choose for training… sql dba + MSBI or .net + sharepoint…
which one has more jobs and future??? please help me.. i am in a great confusion and i need help ASAP.
Thanks,
kiran.
Hi Pinal,
Could you please let me know how can i change language locale settings after the installation of the SQL Server 2000?
Thanks & regards,
Suresh
i have a data entry system for patient records. in my system there are several forms through which data is entered in different tables. and there is a form which takes the present condition of the patient (i call it PresentAssessment). now i what i want is when the user enters a new record i want to compare the values of the attributes (database table fields in the same table) of the newly entered patient and from all the matching records i want to calculate and return the chance(in terms of %age) of particular disease(s) (stored in PresentAssessment) to newly added patient. e.g. if i have two tables i.e. PatientDrugHistory and PresentAssessment. and say each of them already have 10 records with 10 columns each. now when entering the 11th record in PatientDrugHistory i want to calculte and store how much chances (in terms of %age) exist that this 11th patient is suffereing from disease1 (a column in PresentAssessment). please if anybody can tell me what query to write for this purpose.
Hi,
I need some help. I have about 10 databases for which I
created database backup maintenance plan and run it once a day on principal server.
We have a mirror server and a witness server for failover, when fail over happened I would like to create a script to automatically determine which server is Principal.
I can use master.sys.database_mirroring to determine life server, but what should I do next?
Sir plz let me know ur email id as i have some question to clearify..plz sir its very urgent
Transfer data from sql express 2005 to sql server 2005.
What is the best way to transfer data from sql express 2005 to sql server 2005.
We have a set up where we have “N” site office, each site office has an instance of sql express installed, and the site has only the information that it requires. In the main server at the head office, the data has to be updated every week.
What would be the best way to design this replication of data.
Hi Pinal,
Is there any advantage of writing 70-431 and 70-443, if its useful let me know what is the next one i need to take.
Thanks in advance,
Gupta
Excellent web site
hi,
this is my sqlquery i want sum(average) for 3 but it gives one value
select date,(select sum(average)/3 from CCAnalysis_Internal_USR where date =ca.date and msc_name=ca.msc_name)as total from CCAnalysis_Internal_USR ca where date between ’09/11/2009′ and ’09/15/2009′ and msc_name in (‘mscbas1′,’gcsdiw2′,’mscbag1′) group by date,msc_name
Hi Pinal,
I’m a daily visitor to this blog and its excellent.
I need advice from you.
I have to migrate an oracle 10g database to sql server 2005. Do I need to use SSMA or SSIS? Please suggest.
Regards,
Vani
i want to know about certification in Microsoft Sql Server DBA.
so give some idea and I want the latest dumps to prepare for the exam
Thanks
Ashok
Hi Pinal, thanks for everything
@Ashok
What result are you seeing, and what result do you expect?
The query itself looks fine.
Hi Pinal, I just wanted to thank you for all the wonderful articles you’ve written: they’ve helped me immesely over the last year after I took over my first big SQL database. They are concise and clear, and I really appreciate you taking the time to write and publish them for the DBA community at large. Keep up the good work!
-Russell
Hi pinal,
I have a ssis package and it contains a for each loop container and it loops through each and every csv file and loads the csv file data into tables. Now I want this package to be transactional based.
Without transactional based option it is running fine and when I add the transaction option to the package and also to individual tasks then it is giving error.
The transactionoption for my package is set to required and the order of my package items are as follows:
1. For each loop container: I defined two variables the csvfilepath and csvfilespec which defines the folder path and type of files it need to extract. Inside the for each loop container I have 2,3,4,5 items. The transactionoption is set to supported.
2. Execute sql task1: Which inserts the the record with job details like jobno , filename into job table . This filename is coming from the foreachloop container as I defined a vairable which captures the filename it is processing and this execute sql task has ole db connection. The transactionoption is set to supported.
3. Execute sql task2: This task helps in retrieving the max jobno from job table and stores in a variable. The transactionoption is set to supported.
4. Data flow task: This task has one flat file source, derived column transformation(to create a new column and max job no will be placed in this one and then the data is sent to ole db destination) and ole db destination. The target table is sql server and I used ole db connection. The transactionoption is set to supported.
5. Executte sql task3: This task has selectstatment as ‘select 1/0′ The transactionoption is set to supported.
Now I want to have transactional system for my package. As I know that my last sql task going to fail I want the whole package to get rollback.
But I am getting the following error:
[Execute SQL Task] Error: Failed to acquire connection “xxxx”. Connection may not be configured correctly or you may not have the right permissions on this connection.
Task Execute SQL Task1 failed
[Connection manager “xxx” Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 “The partner transaction manager has disabled its support for remote/network transactions.”.
Please help me in resolving this issue and it is very urgent to complete this task. I request moderators to please help me urgently.
Your help is appreciated.
Thanks
Hi sir ,
Please help me to solve this…
prod_cd prod_type prod_nom prod_base Prod_diam
101 L 1.00 5 70
101 L 1.00 5 65
101 L 1.00 5 75
101 L 1.00 5 80
101 L 2.00 5 60
101 L 2.00 5 70
Result should be
101 L 1.00 5 70 65 75 80
101 L 2.00 5 60 70 Null null
can you please give me the sql server 2000 query.
Hi Pinal,
Good day, i will like to know if the Logshiping and DB partitionning can work together in MSSQL server 2005?
if yes what would be the sequence? should do first the partitionning then the logshipping?
Thanx
Patrick
hello sir,
i want to know about d2k
can u plz provide me information regarded it
can u give me links from where i can read and aquire knowledge about it
Hi Pinal,
I am a Techincal Lead and Sr. Dot Net Developer (Using database SQL Server for the last 10+ years) working with one of the biggest bank in US. For the last 1 year (Approx.) I am reading your articles and I found very impressive n technically very sound. You are doing a very good job for the guys who are in Microsoft SQL.
Hope you always guide and give your tips n ticks to the guys in the same…..!!!
Thanks so much…..
–Mohit
Pinal,
I develop some applications for personal usage and with no intention of selling or distributing them. Until now, I use Access as a database store. But lately, I got convinced that SQL server is a better choice: more flexibility, more powerful SQL, etc… (Your excellent blog was part of my conversion). Especially the Enterprise Edition offers some features I want to explore. But there is an obvious snag: its price! I simply cannot afford it.
I know there is a “developer edition” which could solve that problem (cfr: http://blog.sqlauthority.com/2008/12/23/sql-server-2008-download-copy-of-developer-edition-for-free-is-myth/)
In principle that edition can only be used for development only: (cfr:http://www.microsoft.com/sqlserver/2008/en/us/developer.aspx). I only read that one license can be used on several systems, so I can use a physical backup system.
But – and now comes the 2 cent question – what is the difference between development and production? As a single (home) user, I’m continually developing. At the same time some queries/reports are useful to me (and can be considered as my “production”).
Can you help and clarify the issue.
I also can add that Microsoft was rather “annoyed” and even unhelpful to supply to answer.
Mr. Pinal? I have used an evaluation edition of SQL 2005 from the textbook that I loaded on my desktop at home so I would not have to go into a training center that I go to learn it. The time period for it ran out and I am in week 5 of a 6 week course. Tried to reload it and end up getting errors with one that is prominent is the following “..Failed to get ModuleID_RS_Server when getting the ASP Temp directory..” Seeing the ASP error I thought it was a problem with Framework so I deleted and redownloaded .Net Framework 2.0 x86. Still got errors and now the Framework 2.0 is labled 2.0 (2). Renaming that to get rif of the “(2)” was no help. Doing a search on that original error gives 17 different posts on it on the internet that are 2 to 3 years old but no solutions. I have checked in the registry for anything and have not found any anamolies. IS there anything that can be done to correct this?
Thank you,
Tom
Mr. Pinal, now after getting backfrom the building I went to to get MCSE certified and donwloading the Express version, I get “Error 1603 installing Microsoft SQL Server 2005 Setup Support Files,” and in the log there is “..Failed to query registry value (SOFTWARE\Microsoft\Microsoft SQL Server\90\Bootstrap\MSIRefCount\Uninstall)
Error: The system cannot find the file specified..” Basically because it is not there. Now searching on this for the most part I am getting messages that it is in the registry. IS it possible to repair this or is it becase it was a trial version and something was done to make it impossible to reload it. I just want to complete this course.
Thank you,
Tom
@Tom
Did you try checking Operating System Compatibility with SQL Server 2005 Evaluation Edition.
~ IM.
ramu
hi pinal,
\12353-435764.tif.tif in one column but iwant same thing in another which is existing column \12353-435764.pdf.
i want only .pdf extension only that to only one time
if i tis like this i done like
update tbl
set col2=replace(col1,’.tif’,’.pdf’)
\12353-435764.tif
but i dont know wen it is in \12353-435764.tif.tif
how to convert into
\12353-435764.tif.tif–>\12353-435764.pdf
i amnt able to do like this by using patterns
Hi Pinal,
my requirement is solution for the following problem.
my problem is,
I have a store procedure say Proc_Main, Proc_Main calls to many stored procedure inside. the stored procedure i have written in sql server 2005.
For example my proc_main looks like
create proc proc_main (@userid varchar(10), @ipxml xml)
as
begin try
exec proc1
exec proc2
begin try
declare @ip xml(xsdex)
set @ip = @ipxml
end try
begin catch
insert into errormaster
select error_number(), error_severity(), error_state(), error_procedure(), error_line(), error_message()
end catch
end try
begin catch
select error_number(), error_severity(), error_state(), error_procedure(), error_line(), error_message()
end catch
the procedure created successfully.
now for testing i will execute the procedure by the following commands
BEGIN TRAN
BEGIN TRAN
EXEC Proc_Main ‘Prakash’,@inputxml
when i execute the procedure i’m getting following error message “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.”
i dont know what is the exact problem. thing is if i execute the same procedure without begin transaction infront then its executing without any problem. but i need to execute with begin transaction only. please give me answer what i have do to and what is the problem.
I’m in production environment, already my dead line got finished please provide me the solution
Thanks & Regards,
Prakash
Hi pinal,
Thought you might be able to help me out.
Is there a way to use a .bak file that was recovered from a formated hard drive?. The file seems to be complete because of his size but when a try to restore it the following message appears:
Mens. 3201, Nivel 16, Estado 2, Línea 1
Cannot open backup device ‘backup_200906271300.bak’. Operating system error 2(error not found).
Mens. 3013, Nivel 16, Estado 1, Línea 1
RESTORE DATABASE is terminating abnormally.
Thanks in advanced.
Dear Sir,
I have read many articles from your blog. It is very helpful for fresheres as well as experience developer.
Thanks a ton.
Regards,
Yugantar
I am a SQL developer who has an intreguing problem with temp tables (I have read a lot on your site about these). Put most simply it is this:
1. Copy Pubs table to Table in TEMPDB
2. Copy this to real local TEMP table
3. SELECT from this real local TEMP table – fails!
SET QUOTED_IDENTIFIER OFF
GO
USE TEMPDB
declare @somename varchar(6)
set @somename=’james1′
print @somename
Declare @TSQL as Varchar(400)
SET @TSQL = “SELECT top 1 * INTO ” + @somename + ” FROM pubs.dbo.authors ”
print @TSQL
EXEC (@TSQL)
select * From james1 – static version – works
SET @TSQL = ” SELECT * from ” + @somename – dynamic version – works
print @TSQL
EXEC (@TSQL)
select * INTO #localTemp1 From james1 – static version – works
SELECT * FROM #localTemp1 – Works
SET @TSQL = ” SELECT * INTO #localTemp2 from ” + @somename — dynamic version – seems to work
print @TSQL
EXEC (@TSQL)
SELECT * FROM #localTemp2 – FAILS!!!
/* Server: Msg 208, Level 16, State 1, Line 24
Invalid object name ‘#localTemp2′.
*/
drop table james1
drop table #localTemp1
drop table #localTemp2
This is condensed version that is stopping multiuser stored procs from woking on a large account (IBM)
Thanks just for looking.
This is a response to John Goodstadt’s post above:
This will probably work if you create the entire procedure within the @TSQL variable:
DECLARE @somename varchar(30)
SET @somename = ‘james’
DECLARE @TSQL varchar(400)
SET @TSQL = ‘ SELECT * INTO #localTemp2 from ‘ + @somename + ‘; SELECT * from #localTemp2; DROP TABLE #localTemp2′
EXEC(@TSQL)
I got this to work just as selecting it would have.
Hope this is what you’re looking for.
Pinal Dave,
I’m wondering if there may be some way to increment ID numbers on subsets without using ROW_NUMBER function. I have data in a similar structure as that below:
Subset1 | Subset2 | Identity
————————————–
Jan | 2008 | 1
Jan | 2008 | 2
Jan | 2008 | 3
Jan | 2009 | 1
Jan | 2009 | 2
Jan | 2009 | 3
Feb | 2009 | 1
Feb | 2009 | 2
Mar | 2009 | 1
Mar | 2009 | 2
In the case of this project, each of these must remain unique as a combination key within the same table. Is there a way to automatically calculate these rows in the case that some records are at some point removed? Incrementing must continue from the highest ID submitted within that group. The project must not renumber or else… dire consequences!
@John
Your Script :
Set @TSQL = ‘ Select * into #LocalTemp2 from ‘+ @Someone
Print @TSQL
Exec (@TSQL)
SELECT * FROM #localTemp2 — Will fail
Replace above script with below script.
Set @TSQL = ‘ Select * into #LocalTemp2 from ‘+ @Someone
Print @TSQL
Exec Sp_ExecuteSQL @TSQL
– Remember @TSQL Should be nvarchar data type, in order to use in Sp_ExecuteSQL.
SELECT * FROM #localTemp2 — Will work.
Exec (@DynamicSQL) : is one seperate session. This Creates #LocalTemp2 temporary table and then drops it as soon as this dynamic sql executes successfully. But Sp_ExecuteSQL Preserves this temporary table, even after Dynamic sql is executed.
Sp_ExecuteSQL is also used, when you want to return an output from a dynamic sql script.
Exec(@DynamicSQL) could be risky at times, this could become one of the reason for SQL Injection. So better stay away from Exec(@DynamicSQL), unless there is a need.
Since Exec(@DynamicSQL) Creates temporary tables and then drops it, you can try doing this, just to satisfy yourself.
Set @TSQL = ‘ Select * into #LocalTemp2 from ‘+ @Someone + ‘ SELECT * FROM #localTemp2′
Print @TSQL
Exec (@TSQL)
This should give you result.
But, If a temporary table is already created, you can use that temporary table in your dynamic script, that works…
For Ex:
Select * into #LocalTemp2 from james1
Declare @TSQL varchar(1000)
Set @TSQL = ‘Select * from #LocalTemp2′ — Should work.
~ IM.
Dear pranil,
i need a logic for image file compression and encryption storage in sql server 2005.we can store the image file in the
“image” datatype but i need to compress and retrive that
later in same resolution ,i try that with “image thumb nail” option but there is a problem in the image resolution ,so
i request you to send a reply for this query.
Thanks in advance :)
Hi Pinal,
I need the interview questions related to SQL server 200 DTS.
It will be great help if you could send it to my id : behera.prasanta@gmail.com
Thanks for your help.
Prasanta
Hi Pinal,
Its really good to have u on board for all SQL help.
I happen to play around with SELECT …… INTO statement and I did not understand that NULL will get the datatype of int whereas rest of them are not… To put into simple terms here I leave the statement
SELECT ‘Pinal’ AS ‘VARCHAR data type’,
100.50 AS ‘NUMERIC data type’,
25 AS ‘INT data type,
NULL AS ‘DON’T KNOW data type’
INTO
DUMMY_TABLE_WITH_DATATYPES
when I select the table name (DUMMY_TABLE_WITH_DATATYPES) and press ALT+F1 key I could see that for NULL the datatype is INT. Could you please justify on this.
Regards
Vinod Andani
Becuase it has no specific datatype, by default it is assigned int. If you want ti different datatype, explicitely cast it
cast(null as varchar(10))
Sir,
We are creating SSIS packages which will drop and create a dbf table and then copy the data from SQL server to dbf table. The dbf files are located in the same server where my packgaes are created. For DBase i am using OLEDB Jet provider. When i execute the packages, i am getting the following error:
[Execute SQL Task] Error: Failed to acquire connection “DBConnection”. Connection may not be configured correctly or you may not have the right permissions on this connection.
I checked all my permission and all is fine.
Could you please let us know the solution got this?
Avinash,
Chek out the below code snippet ….
select ’2009/04/01′ as ‘Current Financial Year Started on’,
datepart(dd,’2009/04/01′) as ‘First Date of April’,
datename(dw,’2009/04/01′) as ‘Day_Of_First_Date’,
’2010/03/31′ as ‘Current Financial Year Ends on’,
datepart(dd,’2010/03/31′) as ‘First Date of April’,
datename(dw,’2010/03/31′) as ‘Day_Of_Last_Date’
Regards
Vinod Andani
I am a regular visitor of your blog and have been able to resolve a lot of SQL related problems. I am currently facing a problem in one of our application which involves processing of around 1-2 lakh records. This application is developed in VB6 and DB used is SQL Server 2005. I am executing a stored procedure in the form, the Stored Proc contains the code of data manipulation. Earlier I was using CURSOR’s for record-by-record processing, which later on started giving “TIMEOUT EXPIRED” error as the no. of records increased.
I then rewrote the code to optimize by using bulk data manipulation. I now fetch the resultset and perform mass insert/update in one go. This method has reduced the time drastically from hours to seconds.
I have tried it and it works excellently in the Query Analyser. I tried to execute the same from the VB application which executes the stored procedure. I again faced the same “TIMEOUT EXPIRED” error, even though the actual execution time of the Stored Proc in Query analyser is just 45 sec. I even tried increasing the “CONNECTION TIMEOUT” of the connection object, that still did not help us.
I have also checked with the relationship between the tables used to create the resultset. I have also put extra indexes for faster scanning.
I hope you can help me out in this problem. Can you please guide me in the methods to be used where data manipulation takes place on large resultsets. What kind of method is being used in the industry, e.g. banking domain?
I have a question for a project that I am working on for my class a I hope you can help me with.
Here is the scenario we have.
The user would be able to send a message via email or text using four key words and some corresponding codes for each
keyword to get specific data from the table. Each key word relates to a specific table in the database, and each corresponding
code relates to specific information in that table.
For example: “Keyword”, “Corresponding Code” is received by SQL via a table called “MessageIn”.
MS SQL will then return specific information to that user via the “MessageOut” table based on the Keyword and corresponding code.
MessageIn —- Route to appropriate table based on keyword and Corresponding Code.
Keyword 1 -> Table 1 in database
code1 = Get data1 from Table 1
Code2 = Get data2 from Table 1
Code3 = Get data3 from Table 1
Code4 = Get data4 from Table 1
Keyword 2 -> Table 2 in database
code1 = Get data1 from Table 2
Code2 = Get data2 from Table 2
Code3 = Get data3 from Table 2
Code4 = Get data4 from Table 2
Keyword 3 -> Table 3 in database
code1 = Get data1 from Table 3
Code2 = Get data2 from Table 3
Code3 = Get data3 from Table 3
Code4 = Get data4 from Table 3
Keyword 4 -> Table 4 in database
code1 = Get data1 from Table 4
Code2 = Get data2 from Table 4
Code3 = Get data3 from Table 4
Code4 = Get data4 from Table 4
MessageOut —- Send appropriate information to user based on keyword and Corresponding Code.
Can this be done using SQL and if so can you provide some guidance as to how?
Hi
When I run below command it shows me particular table but when I try to delete that table it says
Cannot drop the table ‘?????’, because it does not exist or you do not have permission
SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’
Thanks,
Sunil
There is a change it is under different schema where you dont have permission to drop it
Pinal hope you cover some news about the New features of SQL Server 2010.
@Sunil.
Please give complete name of table.
For Ex:
Drop table Product.ProductDetail
Here Product is schema (SQL Server 2005) , or owner of object (SQL Server 2000).
If you execute this command
Drop table ProductDetail
It will give you the same error message, if your default schema is not product.
Either change your default schema from dbo to schema ( object Owner) or mention complete table name while dropping.
Dont use,
SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’
Use
SELECT * FROM information_schema.tables WHERE table_type = ‘base table’
In this you will get the owner name as well as the table name.
Hope this helps.
~ IM.
Hi Pinal,
Hi. I’ve a table Project(ProjectId, ParentProjectId, ProjectName)
the sample data are -
ProjectId ParentProject ProjectName
1 0 X
2 1 Y
3 2 Z
4 3 W
now for a given project id, say 4, i want to get projectid & projectname whose projectid is 0.
e.g. i want 1,X for projectid = 4
i want to SQL query for this. plz help me.
Read about CTE in SQL Server help file
Hi Pinal.
First of all thank you very much for all of your articles.
I am planning to develop an MLM software but I am very much confused in designing a database for the same.
Could you please give me some informational links about MLM (Multi level Marketing ) database design.
Thank you
Sandesh Daddi
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/contact-me-contact-pinaldave-archive-1/
Find solution to your questions : http://search.sqlauthority.com
Kind Regards,
Pinal
Hi,
My OS is corrupted.How do I take a back up of my databases from MS SQL server 2005?
hi pinal sir ,
iam new in sqlserver DBA ,
previous i worked in Biztalkserver and .net
in the recession i forget my job
iam converted to sqldba
iam searching for job.
please help me sir
regrading some in interview questions .
i attened some interview that people asking in
1) how to find out uncommitted transcations in Sqlserver using DBCC command.
2) How to find out which table is updated how to find out .
3) SLA tickets senario asking please tell me some Example s
Thank you sir
HARISHKUMAR.M
1 dbcc opentran
2 One option is to run a profiler
hi pinal,
1) how to find out in my table or database in indexes through using DBCC command.
2) i have ten lacks records in my table that rows move to warehouse without using import&export
i want spilt my records to 1 to 100000
2)100000 to 200000
3)200000 to 300000
………….
…………
n)900000 to 100000
with using hints ,i don’t barden to my production server .
use any commands …………………………
please tell me
thank you
harishkumar.M
@ harish,
I did not understand your first question.
Second Question:
Refer this article, written by a gem (Pinal)
http://blog.sqlauthority.com/2007/04/30/sql-server-set-rowcount-retrieving-or-limiting-the-first-n-records-from-a-sql-query/
~IM.
Hi Imran Mohammed,
Thank you for Giving Response .
1) Question is:
How to see in Data base level indexes?
Any DBCC command There please Tell me
Thanks&Regrads
Harish.M
Did you mean indexes defined on the table?
use
EXEC sp_helpindex 'table_name'
Dear Sir,
On windows 2003 Standard edition SP2, I have installed SQL 2005 & restore the database but while installing .net framework3.5 it gives following error.
Re-connecting to server (1 OF 5) & finally it gives message cant install.
.net framework 2.0 is installed , .net framework 2.0 SP1 is installed but .netframework 3.5 not installed.
Sir please provide me solution ASAP.
Regards,
Pavan
HI SIR PLS GIVE ME ANY TIPS AND IDEAR FOR MY SOL ONLINE EXAMA PLS SIR I HAVE A PROBLEM PLS SEND ME IMP QUESTION WITH ANS WHICH IS HELP FUL MY SQL EXAMS I HOPE PLS
I create a Database in MS SQL 2005 and used the database for the developement. But some of the memeber in my office change the table,Procedure in the database. How to find the database changes and Who change the datase. And find who Open the database.
Hi,
I am Suresh. I installed sqlserver 2008 in windows7 and also windows server2008.
After installed into my system, i typed “http://localhost/reports” in browser then it is running fine.
but, it giving the following error while i am accessing report server (http://localhost/reportserver)
Error: The permissions granted to user ‘SURESH\Suresh P’ are insufficient for performing this operation. (rsAccessDenied)
Why it is giving this error. Please give me the solution as early as possible.
Thankyou
Hi,
This might be the basic thing but not working for me…
I m dealing with the tables having column names like “a1, a2, a3 ” and trying to fetch result in the following way, but when i “execute” it give me error “Must declare the scalar variable “@a1″….
Is there any way to iterate through the columns like this…
Thanks
Khurram
Dublin, IRL
declare @i int
declare @test varchar(200)
declare @a1 int
declare @a2 int
declare @a3 int
set @a1=4
set @a2=5
set @a3=6
set @i=1
while @i<=3
begin
set @test = ' select @a'+ convert(char(1),@i) + ' a'
exec (@test)
–print @test –'' Print works
set @i=@i+1
end
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,
I need a single procedure for adding records that wil be used by 7 forms,it has to identify the form, depending on the form insertion has to be done in that corresponding form..
Can any one Please help me out for this,
Thanks in Advance,
Anisha
Hi,
I need a single stored procedure that should perform addition, for 7 different forms,So it has to find out which from has been performed depending on that its corresponding table has to inserted
Please help me n solving this
Thanks in Advance,
Anisha
Use if statement
Have a common parameter @form_name that identifies the form and based on that insert into different tables
But I think it is better you create seperate stored procedures for each form so that it is easy to maintain
hi, I have a problem whe I used a stored procedure, I stay use aplicacion functions with sql2005 , but when the program execute the consult show me a error “Fatal error in the current command. The results, if any, should be ruled” , please help me
Can you post the code that caused the error?
Hi,
i have been using Mssql2000,
My mdf file length greater than 20Gb.
Usually Log file(.Ldf) have Huge Length.
Here My data file length over than 20gb.
I have Shrink my Log file with Dump Transaction.
But i dont know,
How can i shrink My Data File length less than 10Gb?
Regards
Nazeer.
I want to synchronize two database in which one is local and another one is global. If I am doing any operation like Insert,Update and Delete, that affects to another database.
The trigger is working when I am using two local servers which is connected by LAN. But when one is global than it gives me an error.
An error like
“The OLE DB provider “MSDASQL” for linked server “(null)” does not contain the table “”Database_Name”.”dbo”.”Table_Name”". The table either does not exist or the current user does not have permissions on that table.
”
My Trigger is :
Create TRIGGER
[dbo].[Fromlocaltoserverupdate]
ON
[dbo].[Table_Name]
AFTER UPDATE
AS
BEGIN
DECLARE @ID Int,@INAME Varchar(50),@PDESCRIPTION Varchar(150),@Price Int
SET @ID = (Select ID From Inserted)
SET @INAME = (Select INAME From Inserted)
SET @PDESCRIPTION = (Select PDESCRIPTION From Inserted)
SET @Price = (Select Price From Inserted)
–PRINT(@ID)
DECLARE @sSQL VARCHAR(MAX)
DECLARE @server_name VARCHAR(100)
DECLARE @user_name varchar(50)
DECLARE @password VARCHAR(50)
DECLARE @db_name VARCHAR(100)
DECLARE @table_name VARCHAR(100)
SET @server_name=’Server_Name’
SET @user_name=’sa’
SET @password=’Password’
SET @db_name=’Database_Name’
SET @table_name=’Table_Name’
set @sSQL=’UPDATE OPENROWSET(”MSDASQL”,”DRIVER={SQL Server};SERVER=’+@server_name+’;UID=’+@user_name+’;PWD=’+@password+”’,
‘+@db_name+’.dbo.’+@table_name+’) SET INAME=”’+@INAME+”’,PDESCRIPTION=”’+@PDESCRIPTION+”’
,Price=”’+ Cast(@Price as Varchar(100))+”’ WHERE ID=’+Cast(@ID as Varchar(50))
print @sSQL
exec(@sSQL)
END
Go
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID
Hi Sir,
I am getting problem with SQL Host_ID() for web version,
SELECT Host_ID() is generating different ID number from different workstation and that is good in Standalone Apps.
But not getting same result in web version. It is generating same ID number from different workstation in Web Apps.
Please help me out?
Thank you.
Probably the server from which the application is running is same and it returns same number
Sir,
yes it only returns server identification number everytime. so is there any other way to gerenerate the different host process from client PC executing host_id() query.
You need to track this via a function available in the front end application
Can you tell me the latest version of SQL. IF you know.
It is SQL Server 2012
Kurdi
hello >>>>>>>>>>>>how i can get and display data from sql server 2005 on 2 computer to 1 computer (using vb6)
……….Thank you……………..
Hi Pinal,
I am having a strange error…. I am trying to execute a DB2 stored proc on a SQL Server.. Here is SQL Stored proc
USE [tx_MS_Prod]
GO
/****** Object: StoredProcedure [dbo].[NKN952P] Script Date: 05/15/2012 08:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: XXXXXla
– Create date: 05/09/2012
– Description:
– =============================================
ALTER PROCEDURE [dbo].[NKN952P]
@PI_PROD_ASM_LINE_NO CHAR(1),
@PI_ROUTE_NO CHAR(6),
@PI_ROUTE_SEQ DECIMAL(11),
@PO_RETURN_CODE CHAR(6) OUTPUT,
@PO_RETURN_MSG CHAR(80) OUTPUT,
@PO_RECORD_COUNT INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
declare @var1 char(6)
declare @var2 char(80)
declare @var3 int
EXEC(‘CALL SYSPROC.NKN952P(?,?,?,?,?,?)’,@PI_PROD_ASM_LINE_NO ,@PI_ROUTE_NO ,@PI_ROUTE_SEQ ,@var1 output,@var2 output, @var3 output) AT HAMDB2
set @PO_RETURN_CODE = @var1
set @PO_RETURN_MSG = @var2
set @PO_RECORD_COUNT = @var3
select @PO_RETURN_CODE
select @PO_RETURN_MSG
select @PO_RECORD_COUNT
END
I got the following error::
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Here is the input data
DECLARE @RC int
DECLARE @PI_PROD_ASM_LINE_NO char(1)
DECLARE @PI_ROUTE_NO char(6)
DECLARE @PI_ROUTE_SEQ decimal(11,0)
DECLARE @PO_RETURN_CODE char(6)
DECLARE @PO_RETURN_MSG char(80)
DECLARE @PO_RECORD_COUNT int
SET @PI_PROD_ASM_LINE_NO = ’2′
SET @PI_ROUTE_NO = ’21′
SET @PI_ROUTE_SEQ = ’20120514001′
EXECUTE @RC = [tn_MS_Prod].[dbo].[NKN952P]
@PI_PROD_ASM_LINE_NO
,@PI_ROUTE_NO
,@PI_ROUTE_SEQ
,@PO_RETURN_CODE OUTPUT
,@PO_RETURN_MSG OUTPUT
,@PO_RECORD_COUNT OUTPUT
Please help me in this regards…
Respected sir,
Sir How can I AutoIncrement the varchar…like i just want output like this
Empno Empname
————————————-
Emp001 aaaa
Emp002 bbb
———–
———-
———
———–
like this sir.
plz reply me sir.
Hai Sir,
i have an error “Conversion failed when converting the varchar value 6d6 to data type int” in sql server 2005 & im working in c#.net 2.0 ….
can you please help me to fix this error…..
Dear sir,
I installed in windows 732-bit operating system. After installing mssqlserver2005 in my system, if i connect that server by using server management studio i am getting this error message: TITLE: Connect to Server
——————————
Cannot connect to SHRUTHI-PC.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Shruthi-PC\Shruthi’. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476.
Please help me sir how to connect with server.
Hey, I think your blog might be having browser compatibility issues.
When I look at your website in Opera, it looks fine but when opening in
Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up!
Other then that, excellent blog!