About Pinal Dave
Pinal Dave is a common man who enjoys listening music, reading books, travelling places, watching movies and writing blogs. Pinal has over 6 years industrial experience gained through his work within the IT industry and SQL Server Community.
He is a highly respected and leading figure in Indian IT field. He is recognized through his work as founder of the highly useful SQL Server Site SQLAuthority.com. Microsoft has presented him SQL – MVP award for his extraordinary contribution as a SQL Server Expert.
Contact Pinal Dave
Email is always welcome, whether you’re interested in talking about projects, SQL Server 2008 and SQL Server 2005 technologies, .NET(C#), ColdFusion, Web Architecture, have taken an online tutorial class at SQLAuthority.com, or have an event where you believe Pinal Dave would be a great speaker or panelist.
If you want Pinal Dave to review your product, book, website or code, please do not hesitate to contact him. Or, feel free to say “hi” anytime. All comments are welcome.
Search SQLAuthority.com
If you have any questions for faster response, Search SQLAuthority.com. It is possible that your question is already answered in one of the hundreds articles.
Child Rights and You:
If this blog has been helpful to you and if you want to help me. Please stand up for the child rights. Donate money to Child Rights and You by visiting their site directly.
pinaldave “at” yahoo.com
pinal “at” SQLAuthority.com
Dear Pinal,
I used ur corsor example and it worked great thanx for that…\
I am facing a problem I have a coulmn address and it is having house no with cutyname,state and country all in address field and I want to separte all these to differnet column as addrees, city ,state and country……..
how can i do that……
I have used charindex also but it only fiends the first one
plz help……..
Its very urgent………..
Thanx in advance…………….
Hi Rupesh
How to post my doubts to Pinal?
What is your doubt?
Post in this post
Hi,
I have a doubt it is,
I have two databases say DA1 and DA2.
Both. DA1 and DA2 have table1 which is a clone.
If I alter table1 from DA1 then I wish to alter table1 FROM DA2. But before altering table1 of DA2 a notification specifying what are the changes in table structure must be given. Also data from the table1 must not be lost.
It’s very urgent please reply.
Have a look about Replication in SQL Server help file
hiii..
i am using sql server 2008 with asp…..when i want to connect master database, then it works ok…..
but when i use the same connection string to connect to mydatabase,it gives me an error……
Microsoft OLE DB Provider for SQL Server error ’80004005′
Cannot open database “mydb” requested by the login. The login failed.
the connecton string used is….
sConn= “Provider=SQLOLEDB;Integrated Security=SSPI;Trusted_Connection=yes;Initial Catalog=mydb;Connect Timeout=0;Data Source=VIVEK-PC”….
i am using windows authentication….
Hello Rupesh,
Please visit,
http://blog.sqlauthority.com/2007/02/10/function-to-convert-list-to-table/
Regards,
Pinal Dave
(http://www.sqlauthority.com)
Hi Pinal,
I am looking to migrate the SQL 2000 applications to SQL Server 2008 and the OS will be Windows 2003 Server.
the server where we run a lot of DTS’s. I understand that SQL 2008 requires some add-ons to be downloaded and installed to make DTS’s work.
Can you please let me know which type of add-ons I need to download it?
Thanks in advance,
Vamsi.
Hi Pinal,
i have one question. we have little complex report in sql server, confused about the logic, can you please help me?
the report is like,there are 3 parameters we need.
Todays Ticket Bookings, Previously Booked Tickets For Current Date, Today Booked Tickets as An Advance for upcoming events.
What can be the possible logic? and we need this report monthly basis.
With regards
Can you post some sample data with expected result?
Dear Pinaldave,
Can you please suggest some good training institutes in Bangalore for SQL server and RDBMs
sir i got a prob…
This exception occur..when i insert record in a table first 10 record are insert succesfully but after when i insert new record it wil gice me exception..
Procedure or function x has too many arguments specified. in asp.net
..
what i do?????
my sp is..
CREATE PROCEDURE [dbo].[Proc_Corporate_Info]
@Corporate_Product varchar(50),
@Corporate_Description varchar(2000),
@Corporate_Address1 varchar(200),
@Corporate_Address2 varchar(200),
@Corporate_Country varchar(50),
@Corporate_City varchar(50),
@Corporate_State varchar(50),
@Corporate_User varchar(50),
@Corporate_Company_Name varchar(100),
@Corporate_Company_Email varchar(50)
AS
declare @Corporate_Id varchar(50)
select @Corporate_Id=count(Corporate_Id) from onlineshop123.Corporate_Info_Table
if @Corporate_Id =0
begin
select @Corporate_Id=@Corporate_Id+1
end
else
begin
select @Corporate_Id=max(Corporate_Id) from onlineshop123.Corporate_Info_Table
select @Corporate_Id=@Corporate_Id+1
end
insert into onlineshop123.Corporate_Info_Table (Corporate_Id,
Corporate_Product,
Corporate_Description,
Corporate_Address1,
Corporate_Address2,
Corporate_Country,
Corporate_City,
Corporate_State,
Corporate_User,
Corporate_Company_Name,
Corporate_Company_Email)
values(@Corporate_Id,
@Corporate_Product,
@Corporate_Description,
@Corporate_Address1,
@Corporate_Address2,
@Corporate_Country,
@Corporate_City,
@Corporate_State,
@Corporate_User,
@Corporate_Company_Name,
@Corporate_Company_Email)
RETURN
————————–
please help me….
Issue could be with parameter passing. Check properly are you passing the required parameters only
hi,
how to go to managing services
Hi pinaldave,
this is Ramanjaneya Reddy,
i have one query in ssis 2005, i.e in my c:\ folder i have 10 *.bak files all bak files have same table after restore database. so i need to insert all database tables records into one table.i traied using for each loop container but this files not mdb or txt or excel files these is .bak file how can i wil do… please give some example for this query in ssis using for each loop container.. please its a urjent
example for for each loop container with *.bak extension in ssis 2005
Hi Pinal,
I Have a problem with deadlock error across 4 servers! Could you please tell me the solution how can i overcome this issue.Error logging indicated an error even though the deadlocks were properly handled. Deadlock did not occur during QA, SIT.
Thanks & Regards
Sandeep Kumar
Hi Pinal,
Do you have have the documentation for upgrading SQL Server 2000 to 2005. I am very new in SQL Server and i got a project to migrate SQL Server 2000 to 2005. Please help me with the detail process and procedures for the migration.
I look forward to hearing from you soon.
Thank you
Abi
Abi,
I recently upgraded 10 SQL Server from 2000 to 2005.
First I unattached Databases.
Second, I did is uninstall SQL SERVER 2000 installed SQL SERVER 2005. Installation of SQL SERVER 2005 is very simple. Once you install it just reattach the databases.
I will create detailed explanation tutorial soon and post on web.
Kind Regards,
Pinal Dave
(http://www.SQLAuthority.com)
Hi dave.
yr articles r just so nice, especially for students like me.
Is there anyway i can subscribe to yr blog without using the feeds?
Thank you Hussein,
At present there is only one method of feed. Very soon, I will provide email subscription of feed or post.
Regards,
Pinal Dave
(http://www.SQLAuthority.com)
Pinal,
Thank you and i look forward to receiving the upgradation docs.
I will have bunch of questions for you if i run into any issue.
I like your online articles …it is really great!
Regards
Abi
Mr. D Pinal,
How can we arrange meeting with you? If we pay your expenses would you travel to visit our branch in Los Angeles in weekdays? If it works out we would like to have contract with you as external expert. You must provide suggestions in writing at the end of review.
Hope to hear from you soon,
Margie Smith
Hello Margie,
Thank your offer.
My advise and opinion are free, if project/product interests me.
I can help as long as any request does not conflict with my current employment and approved by my employer.
Kind Regards,
Pinal Dave
(http://www.SQLAuthority.com)
hi Pinal Dave,
you site is excellent, i wanted a small help.
i have just one year exp in MSSQL and currently work as a Asst. DBA, i want increase my knowledge as a Production DBA can you please tell me the list of areas where i should focus.
Thanx with Regards,
Kishan
Really great blog link, I enjoy reading content many times.
Thanks
Deepak Kumar
Hi,
I have one table which contains certain rows.
I have to display records in to datagrid.
Now, one user is getting 10 records on one page in datagrid,
if other user accessing tht same page, he can show all the records except those 10 records.
i.e he cann’t read those 10 records.
Please, tell me how we can implement using Sql Server 2005.
Is it possible using ‘Sql Server Isolation Level’ or
by ‘Locking Hints’ on Database,table,row,page?
please reply asap.
I would like to effectively screen candidates for various DBA roles, ranging from junior to senior. Do you have the list of interview quesitons for SQL Server broken down into what one’s each level should be able to answer?
Hi Pinal,
I’ve come to bottleneck while trying Log Shipping in SQL 2005 between two data centers, one of which is the primary and the other a secondary fail over location. I wanted to create a shared folder at the primary location, from where the secondary server can access the log backups. Right now, I have a mapped drive on the secondary server to the primary server, which can be accessed providing network credentials. However, the copy job at the secondary fails citing access issues. I didn’t find a place in the Log Shipping configuration where you can set network credentials, so how can I provide that?
Thank
Jaes
Hi Pinal,
I came to know about your sql server’s intellectual through my friend and able to find you in Google.
I hope you will be helping on my sql server problem.
I have transactional replication happening from one server to another and both are having sql server 2000 enterprise edition with sp4 installed on them.
One of the table say A is getting replicated to another server which is having one column having sql_varient as a datatype.
Now the problem is the values are getting displayed fine at publisher side but we are not able to see the values in that column in subscriber.
We are just seeing the unprintable characters and junk values.
Help me if you have time as my reports are getting affected due to this.
Mail me back if you need more information,
Your reply will be highly appreciated.
Hi Pinal,
Your articles have enriched my knowledge. Can you please
suggest different ways of exporting data from excel to sql
You can use many methods using a query,import/export wizard. For queries, refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/import-export-to-excel.aspx
WOW.
One h3ll of blog. Contact our agents on our website. We can help you for anything.
Hi Pinal,
I have been a frequent visitor of your Site, and must say it is quite informative and helpful !! Hearty Thanks.
I was wondering if you could also let us know something about the “Row Overflow Data” Feature in SQL 2005.
This is related to the Max Row Size in SQL 2005.
Cheers,
Deepan S.
Hi Deepan,
Thank you very much for great suggestion. I started to work on the article about that subject and It will show up in sometime in next month as I finish it.
Good suggestion.
Pinal Dave (http://www.SQLAuthority.com)
Hi Pinal,
I have a problem with an Oracle database, may be you could help as well:
The database is running on a database server, accessed by an application server. From time to time, we have long response times for a single select on a small table (10 records), which takes about up to 40 seconds. At the same time, a deadlock on other processes is detected by Oracle and resolved. The delayed select does not have any relation to the deadlock tables, but it comes back at the same timestamp, when the deadlock is solved.
Any idea for a desperate guy?
TIA
Jochen
Hi Pinal,
I’m really impressed by the knowledge sharing by you.
Can you help me in dobut.
I need to delete records from two tables, Table1 contains the primary key and Table2 contains PK and FK of table1.
I want to delete a records from Table1 and 2 without affecting the constraint by using a single Delete Query.
Thanks,
Regards,
Nakkeeran Rengasamy
First delete a child record followed by parent record
Hello Nakkeeran,
It is impossible to delete from two tables using Single Delete Query.
Kind Regards,
Pinal Dave(http://www.SQLAuthority.com)
HI Pinal ,
There was a problem in my sql server 2008 the sql server agent automatically restarted The memory consumption at that time was 94 %
The error was : ex_handle_except encountered exception C0000005 – Server terminating
and the
* Short Stack Dump
77E4BEF7 Module(kernel32+0000BEF7)
026F8EB4 Module(sqlservr+016F8EB4)
026FC9E9 Module(sqlservr+016FC9E9)
026FC585 Module(sqlservr+016FC585)
02A12121 Module(sqlservr+01A12121)
77E761B7 Module(kernel32+000361B7)
77BC3E74 Module(msvcrt+00023E74)
77BCB547 Module(msvcrt+0002B547)
77E6482F Module(kernel32+0002482F)
Kindly help my email id is [email removed]
Hi Pinal
i have a varchar field in a table that is used to store a variety of values (including dates, text, nulls etc).
I need to find all dates (these are currently stored in the format dd/mm/yyyy) and convert them to yyyy-mm-dd format. These values need to remain in this varchar field (rather than a datetime field).
Any help would be greatly appreciated!
Aaran
update table
set col=convert(varchar(10),convert(datetime,col,103),120)
where col like ‘[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]‘
It is not good idea to store dates and other values in a varchar datatype. Read this for more information
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/29/understanding-datetime-column-part-iv.aspx
update table
set col=convert(varchar(10),convert(datetime,col,103),120)
where isdate(col) =1
You should note that isdate() is not reliable. You should also cechk the length
http://beyondrelational.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx
really terrific website to learn SQL very effectively
hi pinal,
i want to depict tempdb size every hour …
which table can have actual size of tempdb size ….
other than sp_helpdb,sp_spaceused,sysfiles….
ur given column shud say actual occuppied size, freee space…
Hi Pinal,
I am in beginner stage with the concept of stored procedure.
So I need the proper and simplest guidance to go through the matters of Stored Procedure.
Plz send me the URL to get the proper guidance from you.. ( URL from your branch )
I thank you in advance …
Regards..
Wilson Gunaniithi . J
Hi Pin (pet name),
I ve sent to you my doubts on stored procedure and some other things..
But I didnt get any reply for them.
Please consider my doubts and reply immediately..
Regards ,
Wilson Gunanithi . J
Hello Wilson,
I try my best to reply emails and comments. It may take upto 48 hours for me to answer questions, due to high volume of emails and comments I receive.
Regarding basic tutorial about stored procedures, you should http://msdn2.microsoft.com/en-us/library/ms187926(SQL.90).aspx as that is the best tutorial ever for beginner. Once you have learned basic you can go to Search Authority and search for Stored Procedure to learn advance tricks and tips.
Kind Regards,
Pinal
I want use more than 8000 characto string as input parameter in my stored procedure.It come frm the .cs file.
and Ii want to manuplate that string.And i hv use text fro that as varchar wont supprot more that 8k of charctor.But problem is that u cant assign value to text feild acccept input parameter.Canu pls suggest wat shuold i do??/
Is there any other son for that..
Pls help Thank you.
Regards,
Harshal
You need to make use of more than one variable
Also if you use version 2005 or above, you can make use of varchar(max) datatype
Hi Pinal,
How to migrate the data from sql server 2000 to sql server 2005. Please send me the details about this.
Thanks in advance.
Regards
Prakash S
One option is to make use of import/export wizard
GID GName Status
1 Compliance Pending For Approval
1 Compliance Rejected
1 Compliance Approved
2 Risk Management Pending For Approval
3 Legal Pending For Approval
I may not be able to explain the exact issue but hope you will understand the scenario.
Due to execution of certain SQL statements I receive above table in a SP.
Now the final result that I am looking for is
GID GName Status
1 Compliance Pending For Approval
2 Risk Management Pending For Approval
3 Legal Pending For Approval
If there is any multiple record for any GID I want the least priority( Status) record
Priority is
2:Approved
1:Rejected
0: Pending For Approval
If there is any 0 Priority record then it should show ‘Pending for approval
If there is no record with priority 0 then it should look for priority 1 if found ‘Rejected’ will be displayed else
Approved (Priority 2)
You should have a status table with data defined
Your original table should store statusid instead of name
Then you can use
select GID ,GName ,min(Status) as Staus from table
group by GID , GName
otherwise see how complicate it is
Hi Pin ,
How to find the number of columns in the table by query…
And how to find the total number of user tables in the database by query…
I worked on this situation in the Oracle Database.,but I have no idea about this in SQL server 2005..
Reply me with the query.. Ok ..bye
Regards.,
Wilson Gunanithi . J
number of columns in a table
select count(name)
from sys.columns
where object_id = object_id(‘table_name’)
or
select max_column_id_used from sys.tables
where name = ‘table_name’
number of tables
select count(*) from sys.tables where type = ‘U’
Hi Pin ,
I m working in Software Company as DB Developer…
By mistake I ve done a mistake in a real database.. So I ve to restore within two days …
I have a backup..But I dont know how to restore…
Send me the way to restore the backup by query or anything ..
Kind regards,
Wilson Gunanithi . J
Hello Wilson Gunanithi . J,
Refer following post for answer to your question :
SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database
If you have any additional questions visit : Search SQLAuthority.com
Regards,
Pinal Dave
Hello Wilson Gunanithi . J,
To restore your backup refer following script.
SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
Please test all the script before you deploy on production server.
Regards,
Pinal Dave (SQLAuthority.com)
Hi Pin ,
Thank you for your quick consideration about my doubt among your fasttrack life.
I tried your restore script as you gave.
But I received one error as follows:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file ‘D:\Program Files\Microsoft SQL Server\MSSQL\data\wiltest.mdf’ is not part of database ‘wiltest’. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
But I checked so many times the ‘mdf’ file name. Definitely it is correct.
Then how to avoid the error to complete my job.
Please refer this doubt quickly .. I am awaiting for your reply.
Kind regards ,
Wilson Gunanithi . J
Answer is your error message, which you have listed above.
Use RESTORE FILELISTONLY to list the logical file names.
Hi Pin,
One month ago, I was asked by the interviewer that ‘ What is the purpose of WHEN clause in SQL Server 2005 ‘?
I have been used WHERE clause in my script so many times as it is a core clause of SQL, but I never heard about the WHEN clause..
Is there WHEN clause in SQL Server ? If yes , reply me that what is the purpose of that clause ? Where we’ve to use? and also send me what are the clauses there in SQL server ?
If no , Send me what are the clauses there in SQL server ? …
I am awaiting for your wonderful reply..
Regards,
Wilson
Hi Wilson,
WHEN is used with the clause CASE.
SQL SERVER – CASE Statement/Expression Examples and Explanation
Regards,
Pinal Dave (SQLAuthority.com)
Hi Pin,
I have been come again to ask you one doubt.
Send me the script for migrating the database from lower version to higher version using SQL script.
Regards,
Wilson . J
Wilson,
You should search in my blog search first before your ask here as that is faster way to get your questions answered. I have answered the latest question in two parts already in my blog.
I enjoy helping everybody though, you should do your part by searching in my blog before asking question – this will save your lots of time.
Search for “Database Compatible” at Search SQLAuthority
Kind Regards,
Pinal
I have a table that has an Identity field and primary key and a description field of what the record contains. I also have 6 other columns that have data that can be redundant.
I want to group the data by the redundant data to get a count of the 6 fields that repeat. I can do this easily.
However, I can’t seem to figure out how to also display the key and description field for the first record found in a group by query.
Post some sample data with expected result
Hi Pinal,
I need to disable a trigger for a particular update statement in a stored procedure then i need to enable it…
Is it possible in MS SQL ?
If yes, HOw? I tried adding diable trigger before Update, but SQL shows error
Thanks
What was the error you got?
Hi Sarran,
Please go to Search SQLAuthority.com and search for “Disable Trigger”
Regards,
Pinal Dave (SQLAuthority.com)
Hi,
I went thor’ the URL.
I need to do in the Stored procedure, before and after an Update… some thing like this…
Create proc a
as
Disable trigger trigger1 on tbl1
update tbl1 set col1 = 3
enable trigger trigger1 on tbl1
is it possible…
What happens when you tried the above code?
Did you get any error?
Hi Pinal,
I just wanted to know what conflicts can happen during restoration if backup of SQL databases is happening by both veritas netback up and SQL command backup(Full backup ,diff backup and transactional log backup).
how can I schedule them.
Should I Continue with one..
I m confused.
please suggest
Hi Vaibhav,
I always use SQL Server backup methods. It is online operation and happens in background. I have been successfully able to restore that database always.
I have not used Veritas backup. I trust SQL Server backups and their restoration.
Regards,
Pinal Dave (SQLAuthority.com)
hi pinal,
I have gone through your some articles their really very helpfull to me as I’m new to SQL, I’m using sql server 2000 & sql 2005 express in a project.In our application we providing backup & restore facility, for the same I used sql query to take a back it creates “.bak” file but when I’m tring to restore same bak file it show following error :
my code is as follows
Restore Database TestDB
from Disk=’c:\pay.bak’
with move ‘payroll’ to ‘d:\program files\microsoft sql server\mssql\data\TestDB.mdf’,
move ‘payroll_log’ to ‘d:\program files\microsoft sql server\mssql\data\TestDB.ldf’
logical file ‘payroll’ is not part of database ‘TestDB’, Use RESTORE FILELISTONLY to list the logical file names.
As it says, use filelistonly to identify the mdf anf ldf files and use WITH MOVE option to move to the respective paths
Hi Pin ,
I have received the information about the backup devices in SQL Server 2005 that there are three type of devices as :
Disk / Tape / Pipe — in this, I have some idea about first two.But I dont have any idea about the ‘Pipe’. So reply for the same.
Sorry for the frequent disturbance.
I am awaiting for your reply.
Regards,
Wilson Gunanithi . J
Hi Pinal,
Please give me difference between SQL 2000 & 2005.
Please give in short .
Warm Regards,
Abhijit B
Hi,
Really you site is very nice. we guys are always make use of this … thanks for your service all the best….
Mani…
Hi pinal
My bakup job failed with below error on sql 2005
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
Pls give resolution
Thanks
Sampath,
Please go to Search SQL Authority.com and search for xp_cmdshell. First link is your solution.
I recommend if you have any problem search there, as you will be able to get faster response.
Regards,
Pinal Dave (SQLAuthority.com)
Hi Pinal
Issue was resolved
by using Surface Area Configuration tool enabled the xp_cmdshell
My job went success
Thanks
Thanks a lot Pinal for your Quik response my issue was resoled , always ur hints vl helping me a lot . Once again Thank you very much .
Great site!
I have a user DB where the sysobjects and sysindexes tables are either no longer present or recognized. CHECKTABLE can find neither.
Is there a way to have SQL Server (2000) rebuild these by inspecting the contents of the MDF?
Thank you.
How did you know that they were not availabe?
Hi Pinal,
i want to attach mssql 2000 mdf file to mssql 2005 and upgrade it , please tell me the way to do it.
i have tried it but i am facing some issues.
Krsh
What are the issues you are facing?
I have two tables say TableA and TableB. TableA has column ColumnA and TableB has column ColumnB. ColumnB is a unique column. ColumnA is receiving values from the user. I want to write a query which can update the values of ColumnB after reading values from ColumnA. Do I have to check each value in ColumnA to make sure that it does not exist in ColumnB and then issue Update Command else issue insert command. ColumnA can have duplicate values
What will be the best option?
Both tables can be accessed by multiple users. So I have to make sure that while selecting value from ColumnA by one user, the value in ColumnB is not being updated
Plz rephrase the Qstn. sounds confusing
Hi Pinal,
I am getting following two errors.
1)Repository engine V3.0 or higher is required to run meta data browser.
2)REPODBC.dll is missing
Both of these errors are on different servers.
Please suggest…
Hi Pinal,
I want to transfer tables from porodox to sqlserver , I have file with *.db format,but during Import wizard ,sql send me this error message
Error Source:Microsoft JET Database Engine
Error Description:External table is not in the expected format
Contex:Error calling open rowset on the provider
What should I do?
Please suggest
Thanks
Vaibhav
Hi Dave,
How to change the row value into the column name..?
Eg..
Col1 Col2 Col3
———————————-
Img1 10 20
Img2 20 40
I need ,
Col2 Col3 Img1 Img2
————————————————–
10 20 xxx yyy
20 40 yyy xxx
Is this possible..? If yes, Please tell the way..
I ve already searched in ” search.pinaldave.com ”
But , I didnt get anything based on this..
So , help me .. this is the task from our company client..
Use PIVOT. Refer this post for more informations and examples
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
You should create new columns and insert details in those column and remove this older columsn.
Regards,
Pinal Dave (SQLAuthority.com)
Hi Dave,
Can we move the attribute values from another attribute?
Eg..
Names—–Exam——–Mark
———————————–
Wilson—–Maths——-null
Siva——–Maths——-90
Pinal——-Maths——-null
Wilson—-Science——null
Siva——-Science——null
Pinal——Science——95
In which ‘always’ wilson won’t have mark for any exam..
So I need to move the mark from “Siva” or “Pinal” to “Wilson”.
Move means not like copy – paste.., its like cut – paste…
Is this possible? If yes, Plz tell the way by words or query..
This is also the request from our company client…
So Plz respond me immediately.. Please….
update t1
set mark=t2.mark
from table as t1 inner join
(
select Exam,max(mark) as mark from table
group by Exam
) as t2
on t1.Exam=t2.Exam
where Name=’Wilson’
Hello Wilson,
This is totally possible and very easy. If your client needs to advise, I can provide contact information of technical professionals, who should be able to help with this small matters timely.
Kind Regards,
Pinal Dave (SQLAuthority.com)
Hi Pinal,
I have recently joined your blog and have found some very intresting articles.
Well i have a problem for which i think you are the right person to be contacted.
The situation is that i have Meta DB architecture in which i have various content databases for each dealer(client) and a single MetaDB containg the meta info of all the content databases(like: Server Name, Server IP, Connection string etc.
Now, the concern over here is of maintenance. Whenever i had to make a production release and definately the release would contain various db object changes to be released. To tell you that the pace with which the content db are created in my application is quiet high. Currently my application supports 71 content db’s. now here lies the problem of releasing the db changes on 71 individual databases.
I have analysed some solutions like:
1. Create a publisher subscriber policy.
2. Created a small application or a script which will loop through the list of dealers from the Meta DB and execute my script files through “osql” command.
The publisher subscriber is not completely feasible for me since i want few customizations like: i may release my scripts on few content db’s instead of all and like other customizations which are i think quiet difficult to configure in this case.
So, currently i am using the second option.
Please, guide in case you have some better solution.
thanks,
Umesh
Hello Dave ,
I received your wonderful reply.. You ve told that I ll give some contact info of professionals to my clients..
But I need their contact …
Plz send their contact info to me….
Thanks…
Wilson,
Please send me details of project as well budget and I will forward the email to my contacts. They will get in touch with you for further project discussion.
Please make sure that you have properly defined your project proposal with need of developers.
Regards,
Pinal
Hi Pinal,
I want to transfer tables from porodox to sqlserver , I have file with *.db format,but during Import wizard ,sql send me this error message
Error Source:Microsoft JET Database Engine
Error Description:External table is not in the expected format
Contex:Error calling open rowset on the provider
Please suggest
Thanks
Vaibhav
Hi Pinal,
I got a new project in setting up Clustering: Active/ Active clustering in SQL server 2005.
I read lot of stuff and i did get some knowledge in clustering. But i am new in this but i would like to take it as challange. So if you have time could you tell me how do i start from the scratch to set up clustering in SQL server 2005 assuming that Windows Clustering has already been set up.
I know that there will be virtual server to which application will connect for accessing the database. I do have understanding about it but still if you could give me start up point. For example, when i go to the company first day how should i start from the scratch. Also let me know how to setup quorum disk.
I would appreciate if any one could give me the startup idea from the scratch.
Thank you in advance.
Abi
Hi Pinale,
I Need a SQL Query that can answer my question.
Here is my Question. For eg- there is one table called PRODUCT. This is how the data is loaded.
NAME GOODS
John Nuts
John Bolts
Peter Nuts
Peter Bolts
David Nuts
David Bolts
David Screws
Mike Nuts
Sue Bolts
Now what i want is the query that will give me the names of people who sell whatever John sells, without passing goods criteria. Query should return Peter and David. But not Mike and Sue because they only sell Nuts and Bolts respectively. Furthermore, if John starts selling Screws in the future the query should fetch only David.
check this out :
create table #temp (name varchar(100), prod varchar(100))
go
insert into #temp
select ‘john’, ‘bolts’ union all
select ‘john’, ‘nuts’ union all
select ‘David’, ‘nuts’ union all
select ‘David’, ‘screws’ union all
select ‘David’, ‘bolts’ union all
select ‘Sue’, ‘bolts’ union all
select ‘kate’, ‘screws’ union all
select ‘Mike’, ‘bolts’ union all
select ‘peter’,'nuts’ union all
select ‘peter’, ‘bolts’
go
select t1.name from #temp t1
inner join #temp t2
on t1.prod = t2.prod
where t1.name ‘john’
and t2.name = ‘john’
group by t1.name
having count(1) > = (select count(1) from #temp
where name = ‘john’)
Pinal,
Is there any knowledge out there in the ether about SQL2005, Vista, Coldfusion and IIS
I have an app I’m working on which I need to load on to a new laptop running Vista. But no ammount of fiddling with settings etc can make the three things work together.
Mr. Dave -
I was wondering if you could assist me in the answers to two questions:
First, I work with the federal government with MS SQL 2000 and 2005 at the current time. We are very tight with our funding this year and as such are very limited in our spending for good DBA database monitoring tools. Do you happen to know of any OPEN source monitoring toosl (i.e. equivelent to SQL Sentry and the like) as well as performance tuning/monitoring tools out there for these two MSSQL engines?
Second, do you have or could you point me in the direction of a Stored Procedure Organizational Chart seperating all the stored procedures contained in the Master DB grouped by some type of logical arrangement?
Thanks again!
Kevin
Hi Pinal,
I work for a company which is developing a web application in cold fusion. We are exploring the possibility of using SQL 2005 reporting solutions with Cold fusion. I know that Adobe has a reporting solution with cold fusion, but am not sure we want to use that. I also don’t like crystal reports because of the cap on their concurrent user access licenses of 5 per software license.
Have you had any experience using SQL reporting solutions with cold fusion? Any major “Gotch –ya’s “ I need to think about when doing this?
I’m also thinking to wait till SQL 2008 comes out, before I purchase SQL 2005, in anticipation that the price for 2005 will drop dramatically when 2008 comes out….
Any thoughts on the benefits of other reporting solutions?
-Philip
Pinal,
Thanks for your advise. Tom, Seth and Mya says hi as well. It was good to meet you in person and hopefully you will have time to help us in final phase of the design approval.
Please give us call.
Margie
Is there any way we can get you to work for our company?
Hello Pinal ,
I m in desperate need of help.
The Project is half way through and we r in a rollout stage.
There is a major concern of Performance as the client has SQL Server 2005 standard edition.
Please provide some help or some test that has to be perfomed on th databse to check whether the DB tuning is perfect..
Urgent Help required.
Thanks & Regards
Pritam Shetty
Hi Pinal,
I need help to resolve follwoing problem:
There are three SQL Server instances , Instance1, Instance2 and Instance3, in a single box(server). My moderator asked me to give access to only Instance1 to my co-worker.
How can i give the access to only a single Instance ( Instance1 ) in a server. How do i do this..I really need help for this, i am not sure how to do it . I look forward to hearing from you soon. Also if you get time, i am hoping to get help in clustering as well.
Thank you.
Abi
Abi,
Every SQL Server instance has its own permissions.
If you go to your server instance >> Security >> Login >> here you can create your own instance.
Kind Regards,
Pinal Dave (SQLAuthority.com)
Pinal,
I guess my question is not clear or i have limitted understanding. I need to create logins for a single instance to access by a co-worker. How do i do this. I hope this helps .
Thanks
Hello Pinal ,
I m in desperate need of help.
The Project is half way through and we r in a rollout stage.
There is a major concern of Performance as the client has SQL Server 2005 standard edition.
Please provide some help or some test that has to be perfomed on th databse to check whether the DB tuning is perfect..
Urgent Help required Please.
Thanks & Regards
Pritam Shetty
Hi Pinal,
I enjoy reading your articles
Would you please send me your articles in pdf format
Thank you very much
Hi Pinal,
Could you please help me with advice and recommendations on creating a large database? I have a table where there is stored data for the last several years (all in all, billions of records).
Should I divide this table into several tables (for example, each year will have a corresponding table)? What type of the field is it more preferable to use as a Primary Key?
Thank you.
Hi Pinal,
I need to setup clustering between to sql server, could you please provide me information for that?
Thanks,
Rupal
Hi Pinal,
Size of my database file(.mdf) is 35 GB.
I have an application for that database which has the facility to purge data of database before a specified date.
When I run that,application monitor displays the size of database less than what it was before purging.
But the size of my .mdf file remains same.
even when i shrink the database, there is no change in the
size.
Please Suggest
Thanks,
Vaibhav
Hi Pinal.
I started workign on SQL Server 2005, and I notice that lot of DML triggers are automatically created for tables. How can I disable these triggers on one go, or delete the triggers in one go.
Please help
Thank you again for your help. I am glad that you helped us out.
Looking at your skills and in depth knowledge our manager has asked if you are interested in job, we want to offer you one.
Please let me know. Also please put my name in reference, this will also good on my cv.
Raj
Hello Dave,
I have a problem with the DTS conversion (Excel to SQL)
In Excel sheet I ve data on particular cell.
But after the conversion of Excel into SQL by DTS, SQL contains NULL for that particular cell.
Eg..
In Excel ,
Reg.no——-Name———-Rank
—————————————-
10————Wilson———1
20————Saran———-20
30————Giri————–6
40————Ram————-8
After the conversion, In SQL ,
Reg.no——-Name———-Rank
—————————————-
10————Wilson———1
20————Saran———-NULL
30————Giri————–NULL
40————NULL————NULL
Why this happens? How to avoid this.? I ve searched in search.pinaldave.com… But I didnt get anything to solve my problem… So tell me the way…
You need to set the column with right datatype and it will not create the problem you have.
Hello Dave,
sorry…
I m not creating the table via SQL..
In DTS, table will be created automatically..
Then how can I set the column’s datatype?
Hello,
We would like to do an interview with you about your blog for
http://www.BlogInterviewer.com . We’d like to give you the opportunity to
give us some insight on the “person behind the blog.”
It would just take a few minutes of your time. The interview form can
be submitted online at http://bloginterviewer.com/submit-an-interview
Best regards,
Mike Thomas
I have to download a pgp encrypted file, file name will be like
20070701.txt.pgp
how can i decrypt the file in T-sql or or batch + t-SQl in sql 2005
Hi guys, I have a problem with error: 40.
I made a Web page, and it works perfect on my local machine, everything is OK, until I uloaded page to the server, it reports this error:
ON MY LOCAL MACHINE EVERYTHING WORKS AS IT SHOLULD, BUT ONLINE WONT, PLEASE HELP
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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
I dont’t Know what to do. Please HELP!
Zdena
Hi,
I want to know if its possible to copy a database in SQL Server 2005, without its data. (copy only the structure).If yes, how can we do it using the ‘copy’ wizard that SQL Server 2005 supports?
Please give me some advise as i’m new to SQL server and need this information desparately.
Thanks,
Nivetha
hi,
i am astudent of the 5th dem MCA from ahmedabad,
i have encountered the follewing problem in my project….
i need Ur help…
i want to insert the records in the SQLServer through the Visual Studio .Net 2005….
Using the stored procedure…..
the whole purpose is to tacle the concurrency issues.
hope U will help……
if U have the code for the same send it…..
plaese its very much needed and urgent….
thanks…
Hi Pinal;
Have you ever seen the physical files names as dbname_1.mdf., dbname2_mdf….and so on after restoring a large database via Enterprise manager in sql 2000? The properties states user defined file groups and logical file names as previous, but the physical file names are different.
I have restored hundreds of small databases before, but never seen this. The only logic I could figure is that perhaps the sql engine will first leave the physical filename as 1,2…mdf, then rename them later. But, when the Enterprise manage states, the restore has completed in a messagebox, I would imagine, it has already renamed the files.
Your explanation, help is greatly appreciated.
Sincerely,
srijan.
Can you please tell me what I wrong with this code?
declare @account char
DECLARE Accounts_Cursor CURSOR FOR
SELECT account_number FROM accounts
OPEN accounts_Cursor
FETCH NEXT FROM accounts_Cursor into @account
WHILE @@FETCH_STATUS = 0
BEGIN
update results
set account_number = @account
FETCH NEXT FROM accounts_Cursor into @account
END
CLOSE accounts_Cursor
DEALLOCATE accounts_Cursor
I just need to get one field from the accounts table and put it in one field in the results table
Hi Pinal,
In post number 21 Nakkeeran has asked,
“I need to delete records from two tables, Table1 contains the primary key and Table2 contains PK and FK of table1.
I want to delete a records from Table1 and 2 without affecting the constraint by using a single Delete Query.”
Please correct me if i am wrong.
In the table properties if we select “Enforce relationship for Inserts and updates” and select “cascade Delect related records”
And if we delete the records in one table the related records with the FK relationship in the second table gets deleted.
Thanks,
Sam
Hi Dave,
I have the data in .txt format. I need to bring them into SQL server.
Can we import the data from notepad to sql server?
If yes , tell the way.
If no , tell alternative way.
Regards,
Wilson
Hi Wilson,
You can use SSIS (SQL Server Integration Service) to import data from .txt file to sql server.
Regards,
Pinal Dave (SQLAuthority.com)
hi Dave
how to send email form sql server with attachment.pls post one doc
hi dave
how can i to import data frm txt file to sql server
Great site and appreciate your zeal to share the knowledge, I’m sure we can meet somewhere sometime… small world, eh!
Hi Satya,
I would like to absolutely meet you. I have read any articles, posts and help written by you from many years.
Kind Regards,
Pinal
Hello Dave,
Thank you for wonderful reply..
I ‘ve tried via SSIS to import the data from .txt to SQL., but the truncation error occured..
How can I solve this?
Please send the way ….
Regards,
Wilson
Hey Pinalkumar Dave,
I am log shipping two databases. I have no problem with one, but the other is another story.
The restore part of the process stops working after a while. I notice job messages that state that certain transactional files are too recent. It skips those files and the job fails.
Any ideas?
Hi Dave,
I have the data in .txt format. I need to bring them into SQL server.
In .txt has many no of columns.
How many columns are accepted in Sql server?.
Please answer my question…
Regards,
S.saravanan.
hi there,
I hope you have done that before, it is very simple job i am trying to get done.
I want to use dts package to convert table into text format and then want to sent to ftp site.
Ftp component only give a facility to download but not upload. Do you have any script handy.
thanks
Hello Dave,
I ve visited so many times ur website.
There is much difference between earlier stage and now…
In earlier stage, you will answer properly for all user’s question…
But,, now even you dont care about so many user’s questions…as user get increased…
Why.? Plz reply especially for this question.. Dont ignore this..
This is the ? on so many users heart…….
Regards,
Varun
Dear Varun,
Thanks for your comment. Users have definitely increased in recent time. I have been receiving more than 200 valid comments in a day. I used to hold each comment and answer them when I have time.
However, I realize that many questions are time sensitive and need answered quickly. Many smart readers visits my blog and read the questions and they help out my other reader’s questions. I am very pleased that while I answer a few questions, other smart readers and developers are helping my readers.
I see many of the questions getting answers from comments from my readers. This nice little community is growing with the help of good readers and developers. If I see any question not getting answered, I will jump right on it and answer myself or contact my other reader who are enthusiastic to answer questions.
I have created custom search http://search.SQLAuthority.com for my readers so they can find answers faster for the question already answered. I direct users to search while I focus on new questions which needs attention. I take my readers very seriously and this blog is extremely important platform for SQL community.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Pinal,
Your interview question helped me to get job. Your site has helped me to get job immediately after my company closed one morning. I never felt so bad in my whole life when I lost job. Bangalore companies are no more safe.
May God give you long life.
Sarad.
Hi Pinal,
The content on your site is very useful. Keep up the good work.
I have a query regarding Triggers.
Suppose my DML query updates 10 rows of a table, how many times trigger is fired?
Trigger type can be either “after” or “instead of” in the above case.
Kindly suggest answer for this.
Thanks and regards,
PRAShant Patil.
Prashant,
Triggers are fired on different condition (i.e. column value changed or inserted data)
Without knowing that the general assumption would be trigger will be fired 10 times for 10 rows updated.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
HI Pinal
I have gone thru the site, its very informative. I know you have successfully upgraded to 2005 without interupting any service.
Here we recently bought new server for SQL 2005 having 4 cpu. We were using sql 2000 till now.
What necessary steps we need to follow to put all databases in 2005?
Also I have to setup a standby sever. can you tell how it can be done ?
how to transfer data from standby server to the production server once it is online?
Thanks &
Regards
Mathew
Hi pinaldave,
I added your links in my comment.
Thanks for your cooperation.
Kumar.A
This materail is very useful for basic understanding
Hi Pinal,
I have a FTP server in which files are loaded everyday, these files are then pulled down, unzip and through a SQL Script the data in these files is populated in SQL Server. Now, I want to automate this process and eliminate all the manual work…….
the ftp path is as following:
ftp://ftp.example…./documents/ This path contains 6 to 8 folders. each of which contain have around 30files or more and in the Modified column there is the date and time stamp.
Now I need to write a script that will automatically connect to the ftp server above go to the document folder then go in each folder and compare the Modified date with today’s date from system. If the date mataches then go ahead and pull that file on the local machine may be in c:/data unzip it and then run the SQL SCRIPT to populate the data…….
Any help in this is really appreciatd. This is really important and urgent to me so please if you have any way out email me soon.
Thanks In Advance.
Hi
May be u can help me
I have following errors every day
High Stolen Page 28254 Threshold is 5
High Page Reads Per Second 43.1910 Threshold is 30
High Read Ahead Page Per Seond 669.0730 Threshold is 30
Current environment is SQL Server 2005 Standard Edition with SP1 running on Windows 2003. 4 Processor with 4GB Memory – dedicated SQL Server
Any help will be highly appreciated.
Many Thanks
DK
Hi,
Install SP2 as it has many fixes regarding memory management.
Regards,
Pinal Dave
HI Pinal,
I have a querry , i need a querry or script which helps in inserting all the data from one table to another table
and the both tables have same fields
Eagerly awaiting your reply
Hi Anup,
You can use
INSERT INTO TableName1 (Yourcolumns)
SELECT YourColumns FROM TableName2
This will fix your issue.
Kind Regards,
Pinal Dave
Hi Pinal,
I guess you can really help me. I posted my request yesterday # 116
Pinal
BTW… Is there a way to get the date and time stamp from the modified column through .sql script.
Hi Seema,
Answer to your FTP question.
Yes, we have been doing something similar for our import data process. We have ColdFusion script which logs in the FTP account, retrieves the data to correct place and uses BULK INSERT to insert the data in the SQL Server. We are planning to re-write that in .NET in near future.
I am not sure if we can get time stamp from the modified column using SQL Script. I might have not understood your questions.
Regards,
Pinal Dave (http://www.sqlauthority.com)
HI Dave ,
I have been going through youir site since last couple of days .Inface i m searching for some help regarding sqal server installation .
I am very new to this database side and now my immediate job responsibility is to install sql server 2005 along with sql server 2000 in same windows server( i heard sql server 2005 is capable of coexisting with sql server 2000).can u suggest me how to do that
Thanks in advance
Hi Srikanth,
you can absolutely have two SQL Server 2000 and SQL Server 2005 on one machine. However, you can not have both of them as same default instance, you will have to save one as default and another as named instance or both of them as named instances.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hi pinal,
I’m trying to run .sql script for ftp nad everytime it gives me the same error…….
***Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online. ***
I tried to change the settings in “SQL Server Surface Area Configuration” but every time I enter any other computer name which I know is on network. It gives another error:
***Computer does not exist on network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again. ***
I don’t understand this error…
Any suggestion or help…
Hi Seema,
It is difficult to provide suggestion from here in this case however, you can do following.
You may want to go login as local administrator to the computer for which you want to enable local area surface and enable it. This should have no issues like you mentioned.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hi
I have been tasked with a task of creating a timesheet That must automatically be diplay for every user per month, their monthly timesheet, setting the weekends to Off and the week days to the default hours of the individual and can also be edited.
The thing is I do not know how I am going to have this done when the user do not have to insert his time at any time during the month. I have proposed to my supervisor that it would be easier for me to have the user insert his time at the end of the day but he does not. I have no idea how I am going to have this records displayed when I do not have them stored any where. Can you please help
Hi Pinal,
How to migrate the data from sql server 2000 to sql server 2005. Please send me the details about this.
Regards
S.Saravanan.
Hi Dave,
I have the data in .txt format. I need to bring them into SQL server.
In .txt has many no of columns.
How many columns are accepted in Sql server?.
Once agin send this question.
Regards,
S.saravanan.
Hello Dave,
Thanks for your answers in all clients.
Regards,
S.Saravanan.
Hello Dave,
How r u?.
what is the use of trrigers in sql.
Please explain which situation u to use trrigers.
Please explain with Example
Regards,
S.Saravanan.
Hi Pinal,
I need help for this immediately. This looks simple but i am not sure
and not confident. Please help me. My scenario is:
I have created a database called ASPStateDB to hold the seesion of
objects in SQL server.
Now i have to create a Local service account for this database.
The local service account name will be ‘ASPUSER’.
ASP.NET is installed in the machine and have to use ‘ASPUSER’ account
to connect to the application from the database.
So how can i create local service account with the above servic account name.
account name.
Please help me immediately, i have to set this up on this monday.
Thank you
Abi
Hi All,
I will be traveling 8/4/07 and 8/5/07 for database architecture meeting with one of the leading software firm to New York. I will be not able to answer any questions for next two days.
Kind Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hi All,
I have came back from my visit of leading software firm. There are lots of information to share. I will be sharing with all of you in next day or two.
Please continue asking questions, I will answer them as fast as I can.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
HAI SIR,
HOW DO FIND LAST ROW IN ANY ONE TABLE WITHOUT KNOWN ANY COLUMNS IN THE SQL
PLEASE GIVE SOLUTION FOR ME THIS QUESTION
BY
T.SURESH
How to find out user activities in sql?
for ex:
to find which user delete the data in database.
Please Ans me immediate very urgent
Thanks,
S.Saravanakumar.
Hi,
How query processing is done internally in SQL server?
Thanks!!
Regards,
Anil Jain
Dear Sir,
How to write a query to find count of tables, SPs, triggers, primary keys, constraints, views and all objects in a database?
Output must be similar to…
Object Name Count
Procedures 27
Triggers 20
Primary Keys 10
…
…
Please give explanation also…
Thanks & Regards,
Suresh
check this out :
select type_desc, count(1) as ObjectsCount from sys.objects
group by type_desc
I would like to make a comment about compatibility issues i.e 65 and 80.
I been testing to all the store procs , jobs, dts which we want to move over to 80. From all the testing experince what i learned was that there isnt any differences if you copy all the settings of 65 to 80 and run the sql code in 80 compability. (No need to change the sql codes, just change setting of 80 as of 65)
Do you see if I am wrong?
where i will see the reply from Pinal?
Hi saq,
Answer to your question (http://blog.sqlauthority.com/2007/08/06/sql-server-fix-error-2596-the-repair-statement-was-not-processed-the-database-cannot-be-in-read-only-mode/#comment-7062) is that some settings with Server with Regards to NULL might have changed. ANSI NULL ON or something similar would have caused that issue.
Regards,
Pinal
Hi,
This is Suresh. My Question no. is 139, Where I would see that answer…………….
Thanks & Regards,
Suresh
Great paper Pinal. My name is Mike Biddle and I am the webmaster for the Detroit Area SQL Server User Group (DASSUG), http://www.sqlsig.org.
I would like, with your permission, to post a link to a downloaded SQLInterview.pdf document on our website.
Thanks.
Mike Biddle
DASSUG Web Adminsitrator
Hello Dave,
I am using your answers very effectively.Thanks..
Now I need to convert the “TIFF” images into “JPG” images using query.
I have searched in your blog.But I did not get anything…
So,Plz consider this issue and reply for the same..
I ll be waiting for your reply..
Regards,
Wilson Gunanithi.J
hello dave
Is there any tool available for sql server 2005 which loads the table of a database with bulk number of records?
Hi Dave,
Scripts are very useful …
Can you provide a good example set for implementing partitioned tables concept in SQL 2005.
Thank you,
arjun
@Rajesh,
If you are asking for any third party tools, I do not have much experience with it.
I use SQL Server BULK INSERT or bcp for the same purpose. You can also filter your records/rows using query.
@Arjun,
I will sure work on partitioned tables and post my views on this platform.
@Wilson Gunanithi.J
I would not use SQL convert images, I will let application programming language (ASP.NET, PHP, COLDFUSION etc) to take care of that situation.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hello Dave,
Thank you very much your wonderful reply of question no 145..
(Plz, dont hide this mail from users’ view)
Regards,
Wilson Gunanithi.J
Hi Pinal,
I am a newbie to SQL 2005. I have a requirement with the client where in he wants me to optmize a stored procedure.I have executed the stored proc and am looking at the execution plan.Execution plan has clustered index seek and also index seek which are the most expensive i.e cost is ranging from 50%-100%. I have no idea how to go about this. I would appreciate if you could throw some insight into it.
Regards and thanks,
Kiran
Hello Dave
i didnt get any comment for my question # 140. Please reply me with your comments.
It will be appreciated.
saq,
I have already replied you in #142.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Sir We are upgrading our SQL server 2000 to 2005 but i am faceing some problems after restoring the database.
In 2000 after restoring database from sysusers table after deleting use we can create againg that user in security but in 2005 i can’t find this table and can’t understand what is the solution your kind help will be highly appreciated.
Dear Readers,
I have been receiving constant request to answer few questions. I try my best to answer all the questions. If I do not know the answer, I request my other knowledgeable readers to answer question. Please be patient, every question has answer.
Regards,
Pinal
Hi
I am new to SQL serevr 2005 and we are usinf Express edition.
I have few questions it would be nice if you could answer.
1. I want to store total no. of rows of a table in a variable is it possible?
I came to your blog site with a query that shows rows and cols and size of all tables in a database but i could nt figure out how to get no. of rows in a temporary table in a variable.
2. I have a sql serevr 2000 database when i take backup of it and try to restore in on sql server 2005 express edition it says access denied. I could not figure out why?
Regards,
A
hi pinaldave i am new in sql-server 2005, before i worked oracle9i…. now i am shifted to sql-server2005.. so how is it… and also please send me MCDBA and sql-server 2005 study materials..
Thanking you
ashok
Hello Dave,
Actually i’m facing a big difficulty.
Can you tell me how can i query several SQL databases at the same time on the Same server. All the databases are similar in structure.
i need to run it throught VB.
Thanks
Mooneer
Hello Dave,
Thanks for your reply..
You have told that ” I ll use ASP.NET,PHP,COLDFUSION ” on that situation ( “TIFF to JPEG” ).
Ok.. please, can you send the coding in ASP.NET to this situation and the coding to convert the “COLOR Images to BLACK & WHITE Images” as your reply..
I will be waiting for your reply…
Thanks in advance..
Regards,
Wilson Gunanithi.J
Wilson Gunanithi.J,
I will be not able to comply to your request.
Good Luck,
Regards,
Pinal Dave (http://www.SQLAuthority.com)
how can i connect 2 sql servers on different machines and get the result set
1 example plz
Query plz
can u plz reply me to my mail ID
Hi..
can u explain wats the use of ‘synonyms’ in sqlserver…
Thanks
Hi Dave
How to write case statement in an insert clause ?
What is your business logic ? Or What do you want to achieve ?
1 case is an expression and not a statement
2 You need to give example code of what you want to do
Hi,
How can i find records having duplicate values in the same column of a table.
I have a column called ‘desc’ in my table. I want to find out all the records where the word ‘profile’ is repeated more than once in the column.
Appreciate any help.
Sanjay
select * from your_table
where desc = ‘profile’ — would suffice
I’m not sure when you say duplicate records and would like to find all records… sounds confusing
How to find all the constraint names in a particular database?
Explain about ddl an dml queries?
how to find the no of primary key in a database?
select count(name) from sys.objects
where type = ‘PK’
hi Pinal
Help me out
How to Convert ’22-aug-2007′ to 22/08/2007 in sql
You need to do this front end application. If there is no option use
select convert(char(10),date_col,103) from table
Hi Pinal,
I need to write a query which based on a column name for example this_Column will update data in the database. If there is no entry found with a particular field name append it to existing DB else if there exists an entry overwrite the whole record with the new one. i.e; update this_Column, make, model etc. Any help is appreciated.
Thanks.
My question is simple, which one is faster:
UPDATE Money_Master
SET MonKey = 6
WHERE MonKey 6
OR
UPDATE Money_Master
SET MonKey = 6
I dont understand your question. 1st Query- Syntax error.. 2nd Query, as there is no condition updates for all records irrespective of what MonKey contains.
Sir
I have one very urgent problems. how to transpose from rows data to columns data.
By
Kumar . T
Make use of this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Hi Pinal,
what is Basic Multilingual Plane(range U+0000 to U+FFFF).
Does SQL Server support this.
If yes how to check it???
Please suggest..
Thanks,
Vaibhav
Pinal,
would be possible to let me know if having ‘Print’ statements in stored procedure for debugging purposes affects performance and therefore should be commented out? what’s your suggestion?
Thanks
Vijay
It should be used only for internal testing purpose. In production it should be commented
Vijay,
Yes, Print statement in SP does degrade the performance because SP has to return more data then usual. However, the performance degradation is usually very negligible so I see use it once in a while. If you are using extensively or SP is called many times, I would remove it.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
@LB
Your section option is faster.
@Priya,
Checkout : http://blog.sqlauthority.com/tag/sql-datetime/
@Kumar, @sabari
http://blog.sqlauthority.com/2007/08/07/sql-server-2005-list-tables-in-database-without-primary-key/
@sanjay,
http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/
@mudit,
http://blog.sqlauthority.com/2007/07/17/sql-server-case-statement-in-order-by-clause-order-by-using-variable/
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hello Dave,
I have the backup of DB1 database(d:\DB1_Bkp.bak).
But, I need to restore this backup into DB2 database (…using query…)
I have the query to restore the backup within a same database.
But, I dont have the query to restore the backup in another database.
Plz, reply for the same with the query.
Plz, dont ignore this. very urgent.
Thanks ,
Wilson Gunanithi.J
Wilson Gunanithi.J,
Replace the name of this same database with another database.
Kind Regards,
Pinal Dave
Wilson,
You just have to replace the name of the database and it should work for another database.
Pinal,
I have request if you please control the quality of the question asked.
Thanks for your detailed reply about backup and restore in email. Your interview questions are good too.
YOU ROCK SQLAUTHORITY!!!
Kevin G.
very true Kevin,
you said what i wanna say always.
Good work Pinal. whez cn i find interview qs and ans.
S
Sivam,
http://blog.sqlauthority.com/2007/04/21/sql-server-interview-questions-and-answers-complete-list-download/
Kevin,
No question is less important or more important. As per me, I enjoy reading all the questions and like to answer as much as I can.
There are many great readers visiting this blog regularly. They have helped out many of my other readers who have questions.
This blog is created as my personal book mark and reference base. I am glad many people are finding it useful and participating in my strong and cozy community.
I have edited your comment and removed inappropriate remarks to other reader.
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Dave or Pinal,
You are doing awesome job with this blog. Do not get distracted with comments. Your articles are your money.
Nobody shares information like you do. You are gift to community.
Dave Moore
Hi Pinalkumar,
Thank you again for your help with our backup system. It is running excellent. We are willing to share your scripts if you approve. We are sent you email regarding few other things.
You should right about those script which you provided to us. They can be useful to everybody who have issues with backups.
Sanjeev Nayak.
Sr. Network Administrator
Very good very very good.
Is it possible to have you over to our corporation one day to discuss few things and go over our system? All paid trip to DC?
send me email.
Hi Pinal,
I want to use log shipping for Disaster Recovery backup of my database. But question is only that, will it effect on my application performance ? If yes, how much.?
Regards,
Hemant Nehete
Hello Pinal,
Can you please explain for my doubts,
We have one sql server,we are not configured sql backups with this server but some other backup tools taking all servers drives backups to different sever every 30 mnts,
now my database with Full recovery model, and on 10.00 am my ldf and mdf files backup done(third party backup tools taking drives backups). now my database gone crash at 10.25 am so how can we recover point to point failure data. we are not taking any sql backup for these servers.
Thanks and Regards
Jayamohanan M.R.
@ Hemant,
It all depends. I use the same setup for some of my not so much used servers. There is little bit of performance degradation but I consider it as part of this set up and architecture. Do not worry, this is not the best set up but works and have been working for me on certain servers.
Since we have moved our databases to SAN, space is no more an issue so it works great.
@Jay,
If you are not taking backup on server and it has crashed. I am very sorry but you are out of luck. There are very few things can be done in that situation. You can visit here and see if something you can apply in your situation.
http://blog.sqlauthority.com/tag/sql-backup-and-restore/
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Thank you Pinal for your detailed answer.
All the time you have answered me. How come you help so much whereas other blog authors ignore users.
Raj
Hi,
I have a situation. I have to load the data from CSV file into a SQL server database table. In the CSV file there are 16 columns, and in table there are 24 columns. I do not know how to do this as there are differences in the columns. I tried using Data Import Wizard, i was not able to include nulls in the rest of the columns of the table so please help me how to resolve it.
Thanks
Abi
Create SSIS package. and map only columns that are coming from csv. let rest of the columns take null
Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
Hi pinal I an getting following error when I am restoring database. but the same backup is restored on other server.
Source MSSQL
Event ID 3266
Type Error
The backup data at the end of “D:\Websupport\db_ncdex_Old_0708231405.bak” is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
please reply. I am in serious trouble.
Thanks,
Vaibhav
plaese tell me alternative for cursor in sql server (with example)
Use while loop
While loop is not direct equivalent of a cursor. Most of the time, it can be replaced with set based method
@Rajesh,
Thanks. Read my reply here : http://blog.sqlauthority.com/contact-me-contact-pinaldave/#comment-9344
@Vaibhav,
Please follow the suggestion in the error message. Use RESTORE HEADERONLY and then follow up it with RESTORE VERIFYONLY. Additional Information is here : http://blog.sqlauthority.com/tag/sql-backup-and-restore/
@girish,
Cursors are not good and alternative is mentioned here with examples.
http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi,
I am importing the CSV file data into SQL Server table using the Import Wizard Tool but i got this error. What does this mean and how can i be able to get rid of this. Please help me as soon as possible.
Information 0x402090dc: Data Flow Task: The processing of file “C:\Test.csv” has started.
(SQL Server Import and Export Wizard)
everyday I learn from you.
Good work. Please email me with your contact info. We learned from Manmeet that you have helped him to look over his TempDB and now it is optimized and no locks. We want that help.
Soniya (ebrahims@tm…)
Hi Pinal,
My CEO wants your help. I send you four emails and you no reply. Today I am writing to forum. Can you visit our organization?? You will have to pay for your travel and accommodation. We will pay for food only. I have promised CEO you come because you friend.
reply faster. Weekdays only. Do not ignore this message and publish it. We want you to come and fix everything. Weekdays only. Reply is must.
good site. We like it.
rajguru
@Rajguru,
I thank you for your offer to visit your firm/organization. I wanted to reply your email earlier. My schedule is little tight recently as I have been traveling in weekend. I had wonderful visit to biggest environmental firms recently. You can read about that here :
http://blog.sqlauthority.com/2007/08/13/sqlauthority-news-author-visit-database-architecture-and-implementation-discussion-new-york-new-jersey-details/
I will check my schedule and will reply to you when I will be visiting your organization. I will have to take a day off from my regular workday to visit your place, so please be patience. I will also check my budget as I will have to fly to NY.
I will sure get back to you soon. I hope this reply will satisfy your curiosity and doubts. No need to further communicate about this, I will get back to you very soon.
Kindest Regards,
Pinal Dave ( http://www.SQLAuthority.com )
PS: I have removed name of your website to protect your privacy.
@Rajguru S,
Good letter. I am surprised why Pinal did not reply to you earlier. I am glad now you have posted to this “forum” to invite Pinal to your “organization”. Just so you know Pinal loves Indian Food.
@Pinal,
LOL!
Kevin G.
PS. Do not edit this post.
Now you are true guy.
I learn so much from you. You must be awarded by microsoft for using their product.
R. Vyas
Wilson Gunanithi.J,
I have same problem as yours. Send me the work script or post here working script.
Thanks in advance.
Pinal,
Do you know how I can pass a hashed value into a SQL Server 2005 stored procedure as an input parameter?
I am using a database symmetric key on my db to encrypt sensitive data like cc number, and ssn fields.
I am storing these values as varbinary encrypted in the tables.
I am passing a SecureString (encrypted) type from my C# application to my SS stored proc.
Do you have any ideas how to do this?
Fred Z. (Atlanta)
Hello Dave,
Is this possible to increase the size of the index key >900 bytes?
If yes, reply how to do..
If no, reply the reason..
Please, reply for this question.
Regards,
Wilson Gunanithi.J
Wilson,
You must search http://search.sqlauthority.com before requesting assistance.
Follow the first link:
http://search.pinaldave.com/?domains=blog.sqlauthority.com&q=index+size&sitesearch=blog.sqlauthority.com&sa=Google+Search&client=pub-6502880871475562&forid=1&ie=ISO-8859-1&oe=ISO-8859-1&cof=GALT%3A%23008000%3BGL%3A1%3BDIV%3A%23336699%3BVLC%3A2266cc%3BAH%3Acenter%3BBGC%3AFFFFFF%3BLBGC%3A336699%3BALC%3A2266cc%3BLC%3A2266cc%3BT%3A000000%3BGFNT%3A336699%3BGIMP%3A336699%3BFORID%3A11&hl=en
Regards,
Pinal Dave
hiiii
i required a query to find last record of the table.
so plz help me out
Varun,
Run Query to find MAX(IdendityColumn) on your table.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Dave,
GREATEST Work!!!!!
You Rock!
Hi,
No one responded my post. Pinal, could you please look at my problem and how to resolve it.
Thanks
Abi,
The reason no one responded as nobody understand what is going on and how the error is happening. You may want to describe your problem differently so people can guide you better.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
hi Pinal,
very goodevening first.. I need one help in one query which will give an addition of a decimal column of successive rows. E.g.
1
4
8
10
15
Output should
1
5
12
18
25
Pls respond fast as I need solution.
Hi pinaldave,
I want to extract all the statements of Procedure. Is it possible to extract or do we have this information in any system table.
Please help me out if you have the information. Please send me a mail to my mail id…..
Thanks a lot in advance.
Hi Ravi,
I encourage you to search your questions at http://search.sqlauthority.com . It is very powerful search and will be able to report back your desired search very quick.
I searched there and I fond following post, which answers your questions.
http://blog.sqlauthority.com/2007/05/25/sql-server-stored-procedure-to-display-code-text-of-stored-procedure-trigger-view-or-object/
Regards,
Pinal Dave
Hi pinaldave,
I don’t want to display the text of the procedure. Yes. It can be displayed using sp_helptext or from syscoments. But want i want is, to extract the exact statements from the procedure as shown in the below example…
For Example
Procedure
CREATE PROC Sample_SP
@Sample_Param INT
AS
BEGIN
IF @Sample_Param = 0
BEGIN
SELECT ‘ZERO’
END
ELSE
BEGIN
SELECT ‘NON ZERO’
END
END
Statements
IF @Sample_Param = 0
SELECT ‘ZERO’
SELECT ‘NON ZERO’
Help would be very much appreciated…
Hi Pinal,
I need some advice from you. I am working on a project. Here is a small description. We have an Intra net application with a SQL Server 2000 back end. Now the new project will create a public web portal which allows users to enter data this data will be stored in a SQL Server 2005 created as part of the new project. An important requirement here is that whenever the data in the Sql Server 2005/SQL Server 2000 is changed it should update the SQL Server 2000/SQL Server 2005 database with these changes(there needs to be two way updating upon triggers on each of the databases. There will be corresponding identical tables in both these databases.
So the databases in these two stand alone applications should communicate in a bi-directional way upon data changes.
What is the best way to solve this problem?
Thank You
@Ravi,
You will have to write your custom code for that.
@JoeM,
You will have to do either logshipping or transactional replication.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Pinal,
Can you suggest when to use views and when to use stored procedure. if we generally want to fetch the data from a table so which would be the better option of fetching the records,
“Views” or “Stored Procedure”
Which one is better in terms of performance.
Thanks
Both objects are meant for different usage. Views meant to provide secured access to tables. We cant implement business logic, neither we can pass parameters not implement conditional statements.
Meanwhile, both SP and View are pre-compiled. Both offers same performance as long as the nature of SQL statements are same.
Hi Rakesh,
I do not use Views at all.
http://blog.sqlauthority.com/2007/04/28/sql-server-restrictions-of-views-t-sql-view-limitations/
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
We are having problem with Microsoft Dynamic GP , when we create new function or module in it ,or even small customization we have to install this package in all users PC (50 pc) .
This issue is very important to us as organization and its time consuming
So there are no way to run patch or script file on the server that does all of that for us .
Thank you
Hi Pinal,
I will first give a brief of the situation, My application allows customers to create service request from my company through a web portal. Now the customer fills up the request form and saves it . The data goes to the SQL Server 2005 DB . Their is a Service request ID column which is the identity column. I want the index to be generated in a particular format by the SQL Server 2005. The format should be SR(mm)(yy)xxxx. For eg: SR0907235 means this item is the 235 th request made in the month of August 2007. How can I achieve this. Please help me out.
Hello Dave,
What is sql server? why is it used? is it good to use?
If yes, reply how and why…
If no, reply the reason… why not…
Please, reply for this question.
Regards,
Wilson Gunanithi.J
Hi Pinal,
Your blog is very attaractive and giving good knowledge of sql . I Have one Q i am using ‘case’ in select statement i wanna
that if column contain NULL value i wanna show ‘Some Other Information’ but NULL is not refrenced please help
like this
case when ‘NULL’ then ‘NA’ else ‘AN’ end
Thanks in Advance.
Regards
Praveen
Praveen,
you can use ISNULL function to compare the NULL values.
http://blog.sqlauthority.com/2007/06/22/sql-server-explanation-and-comparison-of-nullif-and-isnull/
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi PinalDave,
first of all, my praises to you for this very informative website!! so awesome…
i have a small problem though on my ssis project, im sure this is just a walk in the park for you:
we have 2 separate DAtabases and each DB has a common table , lets say
Tracking Table. In DB1, Tracking has columns that has varchar datatypes, and
at DB2 it has same columns but of INT datatype.
Basically, each night , I want to extract only new records from Tracking
table at DB1 to Tracking Tbl at DB2. Then convert the other columns from
varchar to INT.
Also there are some columns at DB1 that need to be looked up at some
reference tables before it can be saved at the Tracking at DB2. But that can
come in later, what i need now is just to convert those varchars into INTs
on the DB2 server. Im talking about 10-15 millions of rows from DB1 per day.
Any ideas which tool I should use in SSIS? any links I can read to help with
my problem ? I know I should use 2 diffrent connection managers for DB1 and
DB2 on the SSIS project, and i also wanna avoid creating temp tables at
either Databases. Maybe just store the new records in the memory (after
comparing the 2 tables from 2 databases ) and then iterate through that
memory recordset then save new records to the DB2 tracking Table. And then I
should also use the For loop component? Can u show me the flow of how
my SSIS should be???
thanks in adv and more power 2 u !!
Pinaldave,
i used ISNULL but this is not working in this case
case when ISNULL( ,’NA’) then ‘NA’ else ‘AN’ end
pls. check with example and forward me. waiting for reply.
Pls. Help.
Thanks
Regards
Praveen
Hi Pinal dave,
situation :-
i have a table having 2 field namely userid,menuid
both field are foreign key
now if new menu add in menu table
i want that this menu aaded for all the user in this table
i don’t want to manually insert it , just i need to insert new record for all the user in this table
thanks
Regards
Praveen
Dear Pinal,
Fantastic website !
Please can you answer a question for me ?
I have a SQL server 2005 that does not need to be running 24/7
Is it wrong to perform backups by shutting down SQL with TSQL SHUTDOWN and then simply backing up the entire hard disks (all the MDF and LDF data files) ?
Microsoft books on line etc does not mention this method at all and I wondered if there is a problem with it or it is wrong in some way ?
kind regards
Mike
Do you have a work around for this same process for SQL Sever 2005?
http://blog.sqlauthority.com/2007/07/21/sql-server-fix-error-msg-1813-level-16-state-2-line-1-could-not-open-new-database-yourdatabasename-create-database-is-aborted/
Thanks Greg!
Hi Pinal Dave,
Good Evening.
The below article was present in our company’s community site. I want to share the same with you. The article is related to debugging of stored procedures. Especially look at the end of the article. It tells us how to enable Debug menu in case it is not enabled.
————-
Thinking whether a stored procedure can be debugged?
Yes of course Y NOT. Here are the steps.
• Open SQL query analyzer.
• Open the object browser.
• Expand the database in which the stored procedure you wish to debug is present.
• Expand the stored procedures node.
• Locate the stored procedure. Right click on the stored procedure. The last option, saying “Debug”, will allow you to debug the stored procedure.
• Once you click debug, you will be prompted to enter all the parameters.
• If you want to debug the stored procedure without being changing the values within the tables then select the option “Auto roll back” option.
• Enter all the parameter values and click on “Execute”.
• The debug point will be created on the first executable statement of the stored procedure.
• In case of character values, that need to be passed to the stored procedure as parameters; do not include them in single quotes.
• In case of datetime values, pass them as yyyy-mm-dd hh:mm:ss; do not include them in single quotes.
If the debug option is disabled then, collapse the ‘master’ database node then extended stored procedure node, locate the extended stored procedure ‘sp_sdidebug’. Right click on this extended stored procedure; select ‘Properties’. Click ‘Permissions’. Grant the ‘Exec’ permission to the ‘Public’ role. Click ‘Apply’ and then click ‘Ok’.
My coworker and I, both DBD’s have been trying to figure out a way to “load” a number of varchar row results into a single variable or field, without using a cursor.
Table1:
ID(int) FirstName(varchar(50))
1 David
2 Susan
3 Tommy
We want the variable contents to look like:
David,Susan,Tommy
We haven’t been able to find a way to do this in a single line of code, such as an Update (to a temp table for example) or a Set @TmpVar = blah
Any suggestions?
check this out :
create table #temp2 (
ID int,
FirstName varchar(50))
go
insert into #temp2
select 1, ‘David’
union select 2, ‘Susan’
union select 3, ‘Tommy’
declare @sql varchar(1000)
select @sql = coalesce(@sql + ‘, ‘, ”) + FirstName
from #temp2
select @sql
Please note that this would give csv of all records. But if ID are duplicates and u want csv for specific IDs, then put condition in where clause
You absolutely rock.
Thanks our CTO wants to say thanks. He would like to request another phone conference call with you. Your help on the spot and saved day.
Hi Pinal Dave,
This is second request. Let me know when you can visit our company? We will pay for your travel, accommodation and food of your choice. (desi food?)
Just let us know what it takes for us to have you over. A good direction in beginning is all we ask. I also asked Mr. Das to contact you with additional information.
I will personally take you to visit all the good places in NYC.
If you do not respond in three days we will have to seek another consultant. Our email address is {removed}.
Sarjeet Singh
Sarjeet,
Thanks. I have replied to you and Rohit Das.
Regards,
Pinal Dave
Pinal, is there a command in sql query analyzer to redo text unlike undo text?
I have bunch of dirty and duplicate datas across my table. I was asked to match and De-duplicate based on only 5 columns(FN,LN,Address,Zip,Buyer/Seller) out of 25 total column. Unfortunately, there is no Primary Key. I figured that Fuzzy Lookups, Groupings and Conditional Split would do the magic. But I am still struggling! How can I populate 25 column based on these 5 duplicates?
Hi Dave,
in my log getting below messege repetedly
database starting ”
unable to find answer in Search SQL Authority.com .please help me on this application team reported tat performancce issue.waiting for ur reply.
@Vijay,
No.
@Sunil,
http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/
@Sampath,
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
I hav a doubt abt using 2 cursors inside a recursive procedure..
Inside the procedure i hav open cur1 done certain calculations.After closing cur1,i opened cur2 and inside while fetch i recursively called the same procedure…
As a result,cur1 fetching is possible but occurs an error like this:
Msg 16916, Level 16, State 1, Procedure debit_credit_calculations, Line 31
A cursor with the name ‘cur2′ does not exist.
My code is like this:
use test1
alter procedure debit_credit_calculations(@grpid int)
as
begin
declare @ah_id int,@ah_name varchar(30),@amt int,@gid int
declare cur1 cursor for select tab1.accountheadid,tab1.accountheadname from accountheadmaster as tab1 where tab1.groupid=@grpid
declare cur2 cursor for select GroupId from groupmaster where ParentGroupId=@grpid
open cur1
………………….
could you plz help me out,waitin for u’r rely…
Hi pinal Bhaiya ,
If it is possible to send me a interview question by mail please send me .i m waiting for your response.
Hi rahul,
Please go to following link and download interview questions and answers yourself.
1) Go to Following link.
http://blog.sqlauthority.com/2007/04/21/sql-server-interview-questions-and-answers-complete-list-download/
2) Right click on the link and click on “save as…”
3) Save to your local system.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Pinal,
Please give us Some key points to be keep in mind when applying Log Shipping, to maintain application performance as well as good programming practise.
Regards,
Hemant Nehete
Hi Pinal,
Please see following query and tell us what’s the difference, with performance prospective.
1]
SELECT SMCOCD, SMCUCD, SMGCCD, SMCUNA, SMCTST FROM CUSMST
2]
SELECT SMCOCD as CompanyCode, SMCUCD as CustomerCode, SMGCCD as GroupCompany, SMCUNA as CustomerName, SMCTST as State FROM CUSMST
Regards,
Hemant Nehete
Both are same per performance wise. The Second Query looks more readable and is as per Best Practices. Always good to use second type
hi Pinal,
Is it possible to open one cursor inside the other and call that procedure recursively in sql server?….
Hi Pinal sir,
How ru??
See i found the below article when i am searching for shortcut keys for comment in SQL2005 Management Studio.
http://nickbarclay.blogspot.com/2005/10/ssms-comment-keyboard-shortcut.html
BUT
I want to know what is we call Ctrl+K keys actually. Why we need to pres these keys, Why not microsoft retains the previous keys (CTRL+SHIFT+C). Any reason to change this.
When we press Ctrl+K in the Management Studio then in the status bar below it shoes
“(Ctrl+K) was pressed. Waiting for second key of cord”
What this cord means????
I know that this is a very silly matter to discuss BUT if u can tell me then its great. Else no problems.
And one thing that your blog is “Simply AWESOME”.
Keep it up.
Byee and Take care
Hi Sir,
Can u please explain me the CUBE and ROLLUP statements, WHAT these stament do, and why and when to use these.
Please , eplain these and if u can give any example for these using Norhwind database, then its good. Thanks you !!!!!
Hello Pinal,
We are getting errors in our production database when we restarting the sql services
Could not find database ID 2, name ‘tempdb’. The database may be offline. Wait a few minutes and try again.
can you please tell me why this occuring
Regards
Jay
Hi,
Can you tell me something about sleep_task waittypes in sql 2005, how we can avoid this waittype?
Regards
jay
heylo Pinal,
can u just let me know how can v remove a primary key constraint from a table.
eg) create table ABC(
Emp_No int primary key,Emp_Name varchar(20))
please let me know at the earliest…waiting for ur reply
bye bye
When I try to insert a record into a table with few columns one of the values that I provide has a length greater than the column length. We get a generic error message that the “String or Binary field would be truncated …..”. Is there any way to extract from SQL Server which column had issues than manually looking at every single column to figure out the culprit?
Thanks
Srinivasan Prasanna
Hi Pinal,
First of all thanks a lot for creating such a great & helpful blog, i highly appreciate this.
Well, i am planning to make my career as SQLDBA, infact i have already started the studies also. Could you please guide me through the process / procedure which i need to follow to become a successful sqldba. I will highly appreciate if you could tell me how to plan the studies, i mean, since i am new to sqldba, what should i understand first, and then onwards…
Any help in this regards is highly appreciable.
Once again thanks a lot for creating such a wonderful blog.
Hi Pinal,
We are frequently facing this error while working on SQL.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server ‘MELAWAI-EAS’ in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Regards,
Hemant Nehete
hi there,
I have a issues updating a current proc. Currently proc is written to go back one yr from today. This needs to be changed to pull one year worth of data ending six months prior to last sales week.
Please help me out.
Thank you.
Hi Pinal,
I have bunch of records that are being pulled from DB2 databse using ETL and loaded into SQL Server Database tables. But these records have single quotes and i have to remove these quotes from the records in the SQL database tables. Thus i am looking for a script or a way to automate these quotes nightly basis because the data load from DB2 database to SQL is nightly process. So please let me know how do i be able to work around as this is a crtical issue for me.
I look forward to receiving your help.
Thank you
This code is giving me Sunday as a starting day of the week
‘Week ‘ + cast(datediff(WEEK,(date – (Day(date)-1)),date)+1 as varchar) + ‘ ‘ + cast( DATEPART(YEAR, Date) as varchar ) AS WeekDescription
could you please tell me that how to get the monday as starting day of the week
Thanks for the great site. I had found a T-SQL script that I used in my 7.0 database. It allowed me to find a piece of text in any of tables (715+) in the database.
I tried to create the stored proc in SQL 2000, to search a new 2000 DB for data. However, the proc would not load/run for some reason. I think there may be a compatibility issue.
The DB is only going to exist for a short time, and I need to send to someone else. I did not want to add full text search if that could create an issue.
Is there a way on 2000 or 2005 to find data within all tables in a database?
I can send the Stored Proc, if you think that might be useful.
Mr. Pinal – I do some creation of SQL tables and importing them from Access databases but have now been asked to put an Access database on SQL so that associates around the US can access it. Is this possible? Thanks in advance… have gotten alot out of your website and will continue to use it as a reference!
Jani
Hi Pinal :) how are you today?
i just stumbled upon your website and foud that your articles on MS SQL 2005 are great and understandable.
I have a problem need to solve, and I hope you can help me with it.
The problem.
I have 2 databases namely: DB_Old and DB_New.
In DB_Old, there is a table called tPhonebook_Old.
While in DB_New, there is a table called tPhonebook_New.
tPhonebool_Old contains 100,000 records while tPhonebook_New contains just a mere 1000 records.
So now i want to append all the old records from the tPhonebook_Old into the new table, tPhonebook_New. How to do it?
Here is my script which i tried to do but it doesn’t work, as it returns on “0 records affected”.
================================================
USE DB_Old
DECLARE @refno char(12)
SET @refno = ‘SELECT NO_RUJUKAN from tPhonebook_Old’
DECLARE @pinno char(16)
SET @pinno = ‘SELECT PIN_No from tPhonebook_Old’
GO
USE DB_New
DECLARE @refno char(12)
DECLARE @pinno char(16)
UPDATE tPhonebook_New
SET PINNO = @pinno
WHERE RefNo = @refno
GO
================================================
Do please take a look and tell me what are the problems with this script. Thank you.
Daniel – 02.10.2007
Hello
@Hemant,
I am planning to write in depth article about log shipping. Keep checking the blog.
@Nyvi,
Yes, it is totally possible. However, please be careful as usually cursor is not required and same task can be achieved using T-SQL Script.
@Prashant Pandey,
I will try to answer your questions in either email or on blog.
@Jay,
Restart the server and try it again, if this keeps on happening you want to check your tempdb logical file names.
@vineet vijayvargiya,
DROP CONSTRAINT constraintname ON tablename should do it.
@Srinivasan Prasanna,
You will have to manually test this.
@Klin,
There is no shortcut to hard work and real word experience. Keep reading good SQL Sites and Blog.
@Hemant,
You need to add link server as it says in error. I will write a post about it soon.
@Priya,
I need additional information to answer your questions.
@Abi,
I am SQL Server Expert not DB2 so I am sorry I do not have answer for you.
@Maple,
Please visit http://search.sqlauthority.com and search for datefirst and you will find the answer to your question.
@Walter,
FullText Search is very powerful and I always have used it sucessfully. No issues.
@Jani,
Create tables and use SSIS to insert database.
@Daniel,
GO statement is resetting your query.
Visit : http://blog.sqlauthority.com/2007/05/11/sql-server-explanation-sql-command-go/
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Unable to connect to server IP
Server:Msg 17,Level 16, State 1
[Microsoft] [ODBC SQL Server Driver] [DBNETLIB] SQL Server does not exist or access denied.
Whenever to connect sql server from query analysier at present to display above error. Please give the solution.
By
Jahir
Unable to connect to server \\SRVNAME:
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
i have this problem plase give me a solutin
Hi pinaldave,
I’m looking for a way to search for a certain value (number) in a databasefield (text). The problem is that the datafield contains html code that contains certain numbers. The goal is to return all rows that contain a value equal to or above a given parameter. Is this possible on a sql base?
Thanks very much,
Al
hii
i am having a small doubt.
I need to copy the data from on etable to the other in tha sam edata base. the problem is in one table the Date of birth is declared as nvarchar(50) and in other table it is declared as smalldate. so i need to convert the date in order to copy the data.
so can u help me out in the query that is required to copy the data successfully.
regards
sarath
Hi Dave
frequently i am getting error
WARNING: Failed to reserve contiguous memory of Size= 65536.
And iam unable to backup my databases running on this server all backups jobs are failing
can u please give me queck soluton to resolve the issue …
version : Sql server 2000 sp4
Hello Dave,
I have the column for “Images”, in which I have the path of images.
Eg.,
c:\ppl\images\image01.jpg
Now, I want to check the invalid images.
To check whether the image is in the path, or not.
i.e. I want to find the invalid images from database (which are not in the specified folder/path).
Is this possible by query? If yes, please tell me..
Regards,
Wilson Gunanithi.J
Hi Pinal,
Can you tell me something about sleep_task waittypes in sql 2005, how we can avoid this waittype?.
We are getting continues this waittype with store procedures.
Regards
jay
hi all,
i want to write a generic Insert & update procedure.It should work with different tables having diffrerent datatypes.suppose let say table1 may have col1 int,col2 varchar… and let say table2 may have col1 varchar,col2 char..like that..and so on..
generally till now wat iam doing is that iam writting different stored procedure for different tables..
Now i want to write One Insert & update procedure in my project…and for all the tables in the project i want to use only that procedure.
Is it Possible? If yes kindly help me ..on resolving this issue..
Thanks & regards
suman
Pinal,
Can you tell me is there load sql available for sql server 2005. Some one asked me if I can load sql for sql server? I thaught it is only available for Oracle.
Thank you for your input in advance.
hello dave
I wish to thank you for creating such a helpful blog .
I am new to the field of SQLDBA, freshly graduated and without proper training I have been assigned the job of DBA . With lots of struggle I have managed to learn quiet a bit which earlier seemed quiet tough.
Can you suggest me some books to refer?
My first assignment was to find the number of records in entire database,after 5 hours of struggle I copied your code.(sorry for that).
I’ll be looking forward for your guidance in future.
Wont copy this time though……
Thanks and regards.
chandrakant
Hi,
It is very good. Keep it up. I found this very useful site. It clarified almost all doubts.
Dave,
I have a Coldfusion Webpage that is timing out. The datasource is SQL Server 2000. I have modified the table that the sql uses for the page. Now there is only one criteria in the sql it is a range on a float(8) column
Select …..
from details
where invoice between #firstinv# and #lastinv#
The webpage pulls up without timing out, but it’s slow.
This table is growing and I’m concerned it will start timing out again. I added an index to the one column in the where statement. I created a non-clustered index on invoice float(8)
the web page timed out again. I deleted the index and the page pulls up slowly, but does not time out.
How can I create an index with sql that will not timeout?
Thanks,
Martha
Hi Pinal,
I am Raaj from India and i am now working for a project which has been using Merge Replication for the Data Synchronization within its locations across the globe. Each Site / location updated the data to the central server. For example there are two locations “A” and “B” and all the three are in diff time zone. There is a POSSIBLE problem of overwriting of data and data corruption. If Location / site “A” has the correct data and updates the central server with the local time, due to connectivity is slow it takes a bit longer for it to update. It lags a time diff with the Location / site “B” which in turn updates the same data to the central server and the same data is pushed to the subscribers “A” and “B”. Here comes the problem. The data updated by “B” is not correct. I have planned to resolve this by having the GMT in place of the local time. I want your help how to implement GMT in the merge replication (publisher, Distributor and the Subscribers)?? I want when the data is pushed to the publisher it has go with the GMT and vice versa. Kindly help me to overcome this problem and fix for the same.
Thanks and Regards
Raaj KR
Is there an advantage or disadvantage to using multiple fields for the clustered primary key, instead of just using one ID field? I am concerned with any performance issues this might cause……
If you could give me the advantages of both types that would be very helpful as I could not find any good writings on this.
Thanks!
Primary Key created for multiple fields is called Composite PrimaryKey. So, Clustered Index is created for these columns. During insertion for the primary key fields, it looks if these fields values not exist are not null. Then inserts and reorders.. Hence, primary key on multiple fields is just costlier
I loved your article in sql mag.
You need to write more like that.
hey Dave,
i need you help.I’m a new born DBA. Tell me what i have to do and what should be my first step…etc
I haven’t find any good article regarding how Table Partitioning works in SQL 2005 and how to implement the horizontal partitioning on a 200GB transaction log table. Can you write an article about that and what will be your approach if you have to solve that problem?
I’ve been trying to find your article on how to script a complete database (objects and data), but I can’t find it. I found the article on how to script the objects, but I’m needing to script the data also. Any chance you can reply with the direct link?
Thanks for all of the great articles,
Jim
I want to store more than 64000 characters in a single field in SQL 2005 DB. I read your article on the website, and tried out varchar(MAX) and text fields but all of them are truncating the data at 64000 characters. Is there any particular setting on SQL 2005 that I am missing out or is SQL not able to hold more that 64000 characters in a single field.
Thanks
Eric
hi Sir,
have a varchar field in a table that is used to store a variety of values (including dates, text, nulls etc).
I need to find all dates (these are currently stored in the format dd/mm/yyyy) and convert them to yyyy-mm-dd format. These values need to remain in this varchar field (rather than a datetime field).
Any help would be greatly appreciated!
Thanks
————
sabeel khan
sir,
I am studying Singapore .now i have Esd subject idnt hve idia.
i want some coding
1) how to create a data base?
2)create tables
can u help me
Hi:
I like to know how to use a store procedure to query sql 2005 all tables in a particular user database to show me the column property say all nulls is set to yes, or default value is zero for this column.
But do not know how to do it -
Hi Pinal,
I would like to know about SQL Reporting Services 2005. Let say I would like to deliver those reports to cold fusion using Reporting’s web service, how’s the command needed to insert into cold fusion script ? Hope able to get some help from you here and thanx a lot.
Best Regards,
Hans
Hi Pinal,
I haven’t found any best place to learn sql in such depth.just keep it up.I have a requirement which i feel necesarry while working with sql server.supoose i want all the sp which consist of particluar word i have a solution for this its
CREATE PROCEDURE Find
@StringToSearch varchar(100)
AS
SET @StringToSearch = ‘%’ + @StringToSearch + ‘%’
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = ‘P’
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name
but what if i want to search all the tables names used in the result of above StringToSearch output sp list.please provide me a solution it will be a great help to all.plz mail me solution and do post in your blog
Hi Pinal,
Is it posiible to pass a table to a user defined function?
I have
Create function Split( @text varchar(1000), @delimiter varchar(20)= ‘ ‘ )
When I call this function
SELECT * FROM Split(‘foo, bar’, ‘,’)
Instead of this I want to call a table. can I do something like this.
Select * from Split(Select Terms From dbo.temp, ‘ ‘ )
Hi,
How can i know, when the stored procedures last run ?
Is there any system table in 2000 or 2005 ?
If you have any ideas or script then advise me.
Thanks
amarish
Hi Pinal,
I wanted to ask a question about how I can use a T-SQL script to move the transaction logs for System databases to another drive and change the database and log settings to different drives letters for future database creations? Do you have a script that can achieve this?
Regards
Rakesh
Hi Pinaldave,
This site is awesome. I am a beginner and got stuck at some point.
My query is i have 2 tables (tblbuilding & tbltype) with columns as building, buildingid, type, typeid.
I have one main table tbltechen2 with columns group,seats,room,buildingid, typeid.
My question is when i submit the survey page with building and type specified, i should get the buildingid and typeid to be entered into tbltechen2 getting the buildingid and typeid from tblbuilding and tbltype respectively..
Can you tel me the query for inserting into tbltechen2.
Thanks a tonnnnn,
karthik
Hi Pinal Dave,
Actually i wnted to save .log file every day to diffrent folder how do i go for this and once it is saved the file should get refreshed and new data should be there in the .log file can you please help me i did it copy file and then but next day it get replaced but the older information will still remains
Hi Pinal,
I am having a performance issue with a data flow task is ssis. I am pumping data from a view to a table.
Could you please help with some suggestions
Thanks in Advance,
Bala
Hi Pinal,
I’m a SQL DBA and working on downloading the files from FTP site to local machine using FTP task in SSIS package.I want to download the files depending on the corresponding modified date in the FTP site (if the modified date matches with the date in SSIS package,then the file should be downloaded).
Could you please help me in this regard…
Appreciate your help..
Thanks,
Swathi.
Hi Pinal,
When i developed my stored procedure and run it on my laptop, it took 30ms. But when in test it at my server, the query took 2.30min. My server specs is so much better than my laptop. I using MSSQL 2005 standard edition for my server.
It came to me that the server is having problem. Correct me if i’m wrong. What are the test can i do to prove to my boss? Thks in advance.
Hello and Good Afternoon Mr. Pinal.
I’d like to make a question for you. I would appreciate a lot if you could help me with the next issue.
I have two Servers on different buildings and both buildings need to have the data replicated on both servers with a delay of one or two hours ( better if I don’t have a delay ). Right now the two servers aren’t communicated but in short they will be.
The have tables that are constantly changing in the order of millions. The principal problem we’re facing is the indexes on both servers cause of the independent growth.
If you could point me at some direction I would appreciate a lot
Thanks for reading this.
Alberto Hernandez
Hi Pinal
I have problem ,I have deleted 10 rows from table by mistake,
but now i have to recover these rows,I dont hav any backup &
transaction backup.
can u tell me any way to recover these rows.
Thanx
Ashutosh Gupta
Hi Pinal Dave.
I read your posts about collation problems. They were of tremendous help as we’ve been having trouble with collations.
Thanks a lot for these posts.
Hi Pinal Dave
I am using SQL Server 2005.
But i am not able to connect it with vb.Net 2005 using DSN.
can you please tell me how to connect SQL Server 2005 with the use of DSN?
Thanx .
I am trying to take data from excel to sql server and i am getting the following error please help.
SELECT * INTO Pips FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0′,
Hi man
I need help, I’ve been working on this for a weeks now. I have created a Job in SQL Server Agent. The job is is executing fine but the my SQL Statement in the Command block is not correct.
I’m using SQL Server 2005, I need to import an Access Database to SQL server automatically every morning, and Delete the old data before Inserting new data.
Can you please help me with the SQL statement to do this job.
Ndindi
Hi Pinal Dave. I remember you from your great tools developed with ColdFusion. I’m a CFer with a little bottleneck in SQL Server and XML. Look this, please:
- I have an XML file with deep structure(nodes). What’s the best approach to import this XML data into a SQL Server 2000 table? What do you suggest? I don’t have success using OPENXML with a path to that XML file. Cheers
i have installed sqlserver 2005 from microsoft.com on my server
and i have installed sql server 2005 client componets on client side.
and i was unable to connect to the server from client side.
i have run queries through command line (client side)from there i was able to connect to the server side system databases but i was unable to connect to the user databases
1. i have enabled all the protocols except named pipes in sql server 2005 network configuration
2.i have enabled all the client protocols except VIA in sqlnative client configuration and i have given default port 1037 in tcp\ip ( the similar settings i have made on sql server side.)
3. i have enabled remote connections in surface area configuration and i have restarted all the services also
and the similar error come repeats always:
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: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server, Error: -1)
Hi Pinal,
Your website has worked wonders for me so thanks for that. My question for you is:
Using cursors (or nested cursors), how can I display all the columns with a clustered index across all tables, all databases within one server?
I have the following code that gives me all databases within a server:
declare @DBname nvarchar(128)
declare @testCursorForDB cursor
set @testCursorForDB = cursor fast_forward
for
select name from sys.databases with (nolock)
where name not in (‘master’,'tempdb’,'model’,'msdb’)
open @testCursorForDB
fetch next from @testCursorForDB
into @DBname
while @@fetch_status = 0
begin
print @DBname
fetch next from @testCursorForDB
into @DBname
end
close @testCursorForDB
deallocate @testCursorForDB
I have the following code that gives me the table name and column name where the cluster index exists:
select object_name(i.id) as TableName,
i.name as IndexName
from sys.sysindexes as i
where i.indid = ’1′
However, the above code only works for one database.
Would you happen to know how I can combine those 2 tasks into 1 using some kind of nested cursor? Or perhaps a better way of doing it?
My end result would have the database name, the table name & the column with the clustered index for that table.
Thanks so much…you rock :)
hi pinal,
i have gone trough sqlauthority.com it was so interesting
and i am new to this site
1. i want to create a index for a table. can u send me with example
2. i want to create a custom control can i know the procedure how to register it
Hi Pinal,
I want to export a database from one server and import it to another. The destination server will not allow me to restore from a backup it did not create(godaddy.com), so i need to generate scripts. I read your blog on scripting the db, but how do i script out the data ?
thanks,
Art
Hi there Pinal.
I am creating a simple application to record employees information. In the database structure under the “Eployees” tbl I want to keep a unique alphanumeric string which will be self generated from the first letter of each new added employee LastName and a self increasing integer.
For example:
Smith—-> S001
Saron—->S002
Egor—->E001
Allen—->A001
and when a new record is added it will take the below shown sequence
Svenson —->S003 (it checks and finds the last record starting with S and adds +1 to generate the next integer that will follow the letter)
If you have something to propose on that I would be great full.
Best regards,
Stratos
@jahir,
Your database server name must be incorrect or database server is not running. Check services.
You can also search http://search.sqlauthority.com for solution.
@AL,
You can use CHARINDEX or LIKE function to search in text field.
@sarath,
You should use CAST or CONVERT function.
You can also search http://search.sqlauthority.com for solution.
@sampath,
You need more more space on your harddrive or filegroup.
@jayamohanan MR,
There is no specific answer to your query. You can send me details of the SP and I can give it a look.
@k.n.m.suman,
Not a good idea.
@Sonal Dave,
I have not heard of it.
@CHANDRAKANT GAURAV,
I will send you email.
@Martha McPherson,
Index is good option.
You can read my articles on Index Tuning http://blog.sqlauthority.com/category/sql-index/
@Jay,
It all depends on what you are queries are. If you queries have joins on composite key then it is advantages, vice versa for single column primary column.
@Maple,
Read this blog regularly.
@Roy,
Sure I will write on soon.
@Eric,
I will research it and reply to you.
@ajmal,
Read this blog regularly.
@henry,
Use ISNULL.
I will request some of my smart reader to answer this question.
@Hans,
Sure I will write article about it.
@Khushi,
You can do that in SQL SERVER 2008 but not in any previous versions.
@Amrish Patel,
You can check in buffer. I will write article about this soon.
@Rakesh,
Search http://search.sqlauthority.com for solution.
@Ashwini Kumar,
You should read articles here : http://wordpress.com/tag/sql-backup-and-restore/
@Bala, @Swathi,
Need more information.
@Samuel,
Server must be busy when you ran your query.
@Alberto,
Replication or mirroring can fix your problem. I will soon write articles about replication.
@Ashutosh Gupta,
Sorry you are out of luck. You need back up for sure. Read articles here : http://wordpress.com/tag/sql-backup-and-restore/
@Mayoz,
Thanks.
@Utkarsh,
Search http://search.sqlauthority.com for solution.
@Herman,
You need to install jet drivers.
@ndindi,
need more information to answer your question.
@Marco Antonio,
I am using SQL Server 2005 as it has XML as native datatype.
@arihant kothari,
Search http://search.sqlauthority.com for solution.
@Arthur Garcia,
You need to link servers and use insert into select.
@To All Readers,
Thank you very much for kind suggestions and words. I will answer everybodies question as soon as possible.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Pinal Please get in touch, need some assistance.
Thanks
Anil S
Dear Dave,
my database has 7.5 GB of the log size. How can I reduce its size by achieving the maximum efficiency ?
Prompt me ASAP.
Thanks in Advance
Sabah
hi pinal.
i have gone through ur blog.sqlauthority and i have done all the settings and even through microsoft.com
1.i have allowed remote connections
2.firewall settings are also done
3.pipes tcp etc were enabled still i am unable to connect to remote server
but still i am getting this error
can u please help me out
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)
can u tell me these instances settings
Great blog – thx.
How can I call an sp from within another sp and get its result (it returns a single value).
Eg (pseudo code)
proc Wrapper
begin
select @innerResult = exec InnerProc
select ‘hello world’, @innerResult
end
proc InnerProc
begin
select 1
end
BTW I am running on 2005 but would prefer a solution that works on both.
Hi Pinal Dave,
I am vamshi SAP Basis actually we have an issue regarding the SQL Server 2005,We want to change the transaction log file to a different disk location. Now it is on the same disk as of data files. Can We do this through Management Studio, If so Please help me regarding this issue.
Thanks in Advace
vamshi
Hi,
I am a SSIS Developer.I am using Execute SQL Task.
In this, I need to write a simple query that can retrieve each row in a table using a parameter.Please help me
Thank You
Dave, I always wondered the difference between GROUP BY and DISTINCT and thanks to your post I now know.
Ta much!
dear penal
i have recently find your blog on net , ia hve some problem i have two table in one table i have four field in which station_name , std_code,phoneno_to,phoneno_from and second table have phone_no,std_code,station_name
i want to update second table for station_name in which query will pick station_name from first table by checking std_code and phone_no between phoneno_to and phoneno_from if this condition satisfy it will update second table for station name in each row one by one but i am getting the error “single-row subquery returns more than one row.”
please solve my problem i need help urgently pls .
hallo sir ,
i want 2 changr my field .net 2 dba
plz give me suggg,
bye
Hi Pinal,
Greetings,
Thanks for continuing this great work. It would be nice, if you post an article on Common Table Expression with recursion.
Thanks & Best Regards
Chella Ganesh
Hi,
Nice to see you here with lot of database expertness.
I am facing one critical problem here, I hope you could suggest me. I am trying to optimize a very huge database transactions having some trillions of records in it. Actually, our database is clustered accross multiple physical hard drives(24 hdd) connected with optical network because of terra bytes of data in it. Here, particularly, I am trying to reduce record retrival access time and batching some DML and DDL statements together. I have used various methods of SQL, PL/SQL and Java Stored Procedure optimization techniques, but it has resulted to take more time than existing SQL approach.
Here the question is, Does PL/SQL or Java Stored Procedures takes more time than SQL statements? Is it because of the data is being clustered physically accross multiple hard drives? Please let me know if have any idea on this kind of performance related issues over distrubuted/clustered databases.
Veeresh D.
http://drveresh.googlepages.com
Hi,
I have a question regarding sql reporting services using sql 2005( VS2005).
Start End 10/20/2007 12:05:57 PM 10/20/2007 12:08:06 PM
Duration = End – start = 00:02:09.6570000
I need to display this duration as
00:02:09.65 ( i need to round of that milliseconds to 2 decimal places).
Any idea?
Thanks,
Pavan
hi
this is keyur
i want to create .mdf .ldf file from any data base without data
help me
i sure i will get positive response
i dont want to generate a script so,.
thanks
Hi Pinal,
I am in an unusual situation, I am working on SAP, and in 22 days I have a client interview on SQL Server2000.
Can you help me as to how should I go about it and where I can find material to study SQL Server 2000, any online tutorials or something.
The only knowledge I have of SQL is the part of queries and not much.
So can I get some recommendations from you as to how go about…
Archayan
hi pinal,
while connecting to sql server 2005 i am getting this error.
A connection was successfully established with the server but then an error occured during the login process (Provider :Tcp provider,error:0 -An existing connection was forcibly closed by the remote host.)(Microsoft Sql Server,Error:10054)
can u help me out of this situation
hi there,
This is question for anyone who know the answer.
i have a string
“102 101 320 102 101″
I have to remove duplicate out of that string. Take 3 char match as crieteria.
i created this inline function hope it will help others.
CREATE FUNCTION dbo.Remove_duplicate_instr
(@datalen_tocheck int,@string varchar(255))
RETURNS varchar(255)
AS
begin
declare @str varchar(255)
declare @count int
declare @start int
declare @result varchar(255)
declare @end int
set @start=1
–set @count=4
set @end=@datalen_tocheck
set @count=@datalen_tocheck
set @str = @string –’123456789123′
–SELECT REPLACE(‘abcdefghicde’,'cde’,'xxx’)
–SUBSTRING ( expression , start , length )
while (@count <=255)
–while (datalength(@str)!=0)
begin
set @result=@result+substring(@str,@start,@end)
set @str=replace(@str,substring(@str,@start,@end),”)
set @count=@count+@datalen_tocheck
–select @mystr as mystr
end
return @result
end
– how to use it select dbo.Remove_duplicate_instr(3,’123456789123456456′)
viresh,
it depends what kind of query you are writing, whether these queries are optimised or not. you can use query execution plan and check any table scan in there.
1. If query is doing a table scan then you need to create index on those column.
2. if you are using odbc connection it has different algorithm to generate the result which is takes longer than normal .net client.
3. check statistics in the table make sure then are updated.
4. try to make sure there is no blocking going on.
there are lot of other factors could be making it slower, i can’t till i see.
Clustering of database just g ive you virtualization of sql server instance it has nothing to do with sql server performance.
clustering of disk shouldn’t not be an issue check with your dba or system administrator what kind of raids has been used and what raid data has been stored.
Hi Pinal,
How many hours a week do you invest in upgrading your skills? By the way, I find your site very informative.
Thnxs
Does anyone know how make a backup of a serie of databases in Standby-ReadOnly state? The reason: I’m using Log Shipping Microsoft Solution for replicate info.
A SnapShot isn’t a possible solution.
Hello,
I have my SQL 2005 database on a remote server. I do not have access to the actual database file itself. Is there something I can run from my local machine to create a copy of the database to my local machine, possibly automatically every day?
If this is asking too much, I apologize for my ignorance, but any help would be most greatly appreciated.
Kindest regards,
Robert Tilley
Hi Pinal,
As you are a champ my question would sound little funny to you, but i have a task where in my database we have 140 datafiles in which 60 would be index data files
so my requirement is they want me to delete or remove unsed datafiles from the database, and as am very new to sql i dont know how to do this and to be honest to use google in correct way is also a challenge and a talent
And 2 question which bothers me a lot bcoz in every interview they asked me :
1) you created a query or store proc which was running good and very next day it started taking 1 to 2 hrs to execute what could be the reason
2)you are able to execute your store proc and get the result in 1 2 sec and the same store proc when executed by other user is taking 1 hr on same network why?
I hope its not that hard for you to answer.
And i apologize if am asking too much question
Thanks
sameer
Hello
How to prevent records from deleting accidently when we pass a command like delete from ‘tablename’. I want to get a message confirming to proceed or not in this condition.
Regards
Vikas
TRIGGER would help you
Hi,
Do you recommend any web site or on-line certification for Cold fusion?
Thnxs
C
Pinal,
I am currently frustrated with SQL2005 and it’s datetime capabilities. I have a couple of questions. My problem is how would I be able to determine the last day of the current or next month ( some months have 30 days and sometimes a leap year happens )? Also a very similar problem if I have a date of January 31 and I were to add 1 month to that date ( giving me a date of February 31 , an invalid date which can be recognized by the ISDATE( ) function ) how can I determine or receive a valid date of February 28 or February 29 depending on if it is a leap year.
Thanks for the help!
Robert
Sameer ,
Store procedure need a re compliation.
SP needs recompilation unless you have Dynamic SQL embed into it. Otherwise, it just compile once and run many times
The problem what sameer is facing is slowness in execution. Need to check what SP is doing .
Sorry to double post but I found out the answer to my previous post. This line should do the trick. Thanks for a great website.
SET @time = DATEADD( mm, datediff( mm, 0, dateadd( mm, 1, @time ) ), -1 )
Robert
Hi Pinal,
Can you please help me to write a recursive query?
I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.
Sample data in the table is as follows
wonum parent
7792 NULL
7793 7792
165305 7793
7794 7792
7795 7792
eg:
7792 is a workorder,which has got children and grand children
7793 is a child of 7792
165305 is a child of 7793
7794 is a child of 7792 and
7795 is a child of 7792
When I give the 7792 in the query,it should fetch all the children and grand children,etc. ie 7793,165305,7794,7795
How can we do that?
hi dave,
while writing a stored proc, i came across a scenario where ther were 2 insert statements. The first statement was a legal insert statement, whereas the second one was designed in such a way that it would throw an error as a result of which the first statement got committed and obviously the second didnt.
what i need to know is that is there any means by which i can rollback the first insert statement?
hi dave,
can i get tsql script that generates restore commands for the latest backup into a notepad, ……..so that if i run those restore command after copying backup file to another server it should be able to restore.
or
T sql script which automatically restore latest backup.
Hi,
I have a problem with scheduled backups. No log is maintained. Any backups that call xp_sqlmaint or rather anything that is been executed using xp_sqlmaint procedure hangs.
Could someone please help?
This is really crucial as backups are not happening.
thanks
Shreyas
Hi Dave,
Sir, I am working on large scale of database. When i transfer database from online to my local sql. There is lot of procedures , triggers and constraints. It is failed to transfer on my local server.
To resolve this problem I do following steps
1. I just create database.
2. Alter table with no check in implement triggers, constraints like foreign key, and primary key
then procedures
Am i doing right thing or there is some other way. I’m also try to replicate but i want to confirm that is there any problem to replicate data
Hello sir, I hope you be fine…
I have a problem. I want generate object script via stored procedure. Please guide me to find and use it.
i have to create stored procedure to a table i created, how to do this
Can I create a view which will refer some tables from local database and some tables located in different location
(database server which is located in clients machine)
Hi Pinal,
My organization is currently migrating about 35 databases from SQL 2000 to SQL 2005. Most of the databases are small with the largest being about 6gb. These databases are not very transaction intensive. We are about half way through as we started the project a year ago. We purchased new hardware for this SQL 2005 so it is a side-by-side migration and it has been going well so far. I made one oversight which I think will come back to haunt us down the line. I installed Windows Server 2003 Standard R2 Edition and of course now I am considering putting more RAM in the server. We have 4 GB of RAM in the server and of course Standard Edition will not address more 4GB of physical ram. I have not seen any memory pressure yet but as databases are migrated over the amount of physical RAM drops. My buffer hit cahce ratio and page life expectancy performance counters are fine so far. The server is a brand new HP DL 380 G5 with with tons of disk and processing power, but only 4GB of RAM. I can’t migrate to another server with more ram obviously.
1. What would you do in a situation like this?
2. I could do an in-place upgrade to Windows Server 2003 Enterprise R2. Has anyone attempted an in-place upgrade? I have no applications
on this server, just SQL 2005 Standard.
3. Would the /3gb switch be an option? I have heard mixed reviews on this one.
As far as database optimization, %75 of the databases are third party so we have little flexibility in tuning them or redesigning
tables.
Thanks,
Alex
How to get WEEKS and months listed in a query by passing the from date and to date
Hi
actually am having one problem regarding weeks and month
what i want is .if suppose am passing from date and to date as a parameter.. then i want total weeks listed
like 02-12-07 to 08-12-07 ,09-12-07 to 15 -12-07 in one column as a week same for months as well
Thanks
Hi Pinal,
I’ve created a table with 2 columns (Myfilename varchar(255) and the myPicture Varbinary(max)).
I was able to upload a file into the table using Openrowset(Bulk ‘E:\temp\myTest.jpg’,SINGLE_BLOB) AS [file]… worked like a charm.
I now, need to retrieve the file from the table and put in a folder using TSQL. I’ve been searching everywhere without any success…
Would you have any idea? It would be greatly appreciated…
Thanking you in advance.
John G.
Hi Archana,
You can get the number of days between two entered dates by using datediff function
example:
SELECT DATEDIFF(day, ’20071201 23:59:59.997′, ’20071231 00:00:00.000′)
this function will give you number of days which you can divide by seven to get number of weeks.
To get number of months you have to divide by 30 days.
However for the month of february, you need to calculate the number of days and divide by 28.
That will give you number of months.
Hope that answers your question.
Shreyas
Thank u shreyas n good morning to u
.. whatever u told that will give me only no of weeks i.e . 4 or 5
n i want those listed in list
like 02-12-07 to 08-12-07 It means 1st week
09-12-07 to 15-12-07
likewise
thanks
archana
Hi Archana,
A very good morning to you.
Actually your question wasn’t quite clear to me before and even now.
Select datepart(ww,’2007/12/12′) –(returns 50)
Select datepart(ww,’2007/1/12′) –(returns 2)
The above function return you week number if a year.
However, if you want to get week number in a month, you would probably require to write a function which returns week number of the month.
Let me know if that satisfies your question.
good luck.
Shreyas
Pinal,
Do you have a schedule of answering queries or questions or you do it randomly?
Shreyas
@Anil,
Sure I will get in touch with you.
@Sabah,
You can truncate and shrink database as well backup on backup device.
For more help : http://search.pinaldave.com
@arihant,
Try to access server using IP address.
@Nilesh,
Sure, you can use OUTPUT parameter.
@Vamshi
Please search for solution here : http://search.pinaldave.com
@Varsha,
I will sure write about SSIS soon.
@George,
Glad you liked it.
@manish,
Your subquery is returning more than one records, please look into that.
@Abhay,
Read this blog regularly and read lots of SQL.
@Chella,
I already have posted article about CTE on this blog.
Please search for solution here : http://search.pinaldave.com
@Veeresh,
Sure, I will write about that to you.
@pavan,
It can be rounded to only 3 miliseconds
@Keyur,
Create empty database.
@Archayan,
Need more information.
@viren,
I already have posted article about your issue on this blog and thank you for sending solution.
Please search for solution here : http://search.pinaldave.com
@Tina,
Everyday more tahn 4 hours
@Edgar,
I will write about this very soon.
@Robert,
Please search for solution here : http://search.pinaldave.com
@sameer,
Please search for solution here : http://search.pinaldave.com
@vikas,
I wrote about accidental deletes on this blog already.
Please search for solution here : http://search.pinaldave.com
@Carmen,
I read http://www.bennadel.com/ for coldfusion.
@Robert,
SQL Server 2008 has new DATE datatype. Very helpful.
@Rajesh,
You have to use error handeling in SQL Server.
Please search for solution here : http://search.pinaldave.com
@varun,
Sure you will have to use SQLCMD to create text file and use it.
@Shreyas,
I will send you email soon about this.
@Amit,
http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/
@mahdi,
http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/
@Senthilkumar,
Please search for solution here : http://search.pinaldave.com
@prasad,
I do not think that is possible.
@Alex,
Good explanation, I will send you email with detailed information.
@John,
You can do it using sqlcmd.
@Shreyas,
I answers questions as soon as I can. Thanks for your help and wonderful comments on this blog. Please participate more.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Sir,
I get lot of information from your site.Please can you tell me how to improve knowledge which is required for DBA.
Hi..
I have also received the Error “Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”
.
could you please tell me how to fix this error. I am using two diffeent VB Applications whis use same SQL Server 2000 database.
I have found the one process which became a victim. Could you please let me know where to set up the DEADLOCK_PRIORITY as i am new in SQL server 2000. I really appreciate your help.
Regards
Nitesha
Sir,
i need your help,
i had created a table student, studentid column with alpha numeric primary key with varchar datatype
now my problem: i want to sort the student id accroding to studentid like
STU1
STU2
STU3
STU4
STU5
but i’m getting the sorted result like this, how to overcome this problem, please guide me sir
STU1
STU10
STU100
STU1000
STU10000
STU2
STU20
STU200
STU2000
STU20000
STU20001
Sir,
I am Vinoth just i need a help from u i am trying to create a database with username and password in SQl using query.
please help me in this problem and i need query to create databse with username and password.
Regards
Vinoth
Sir,
I,need the Storedprocedure related answer.
How to write a single storedprocedure for all controls in a form.
Thanking You
G.Thangapragassam
hello sir , i want to become an sql server DBA . please suggest some books or some institutes for guidance.i’m a beginner and new to sql server
Hello sir,
I am having some confusion, related to Select and update statement in term of performance. pls help me out in this topic. I am explaining one problem.
I am using MySql 5.1. Now the problem is. related to update the record after inserting. what i have to do is inserting the record in database but one of the filed is using the Auto generated ID value. for that we have to insert the record first and then have to update that particular field.
For this we think another way i.e. fetching the id max + 1 value from the table and used it for inserting.
pls suggest us what is better and safe way in term of performance.
Thanks and Regards,
Pinkesh Maheshbhai Khatri
Hello Sir,
I am using Sqlserver 2000 and i faced a problem.
I have no latest backup of my database.The Database
is now on suspect mode and the logfile is corrupted.
How can i recover my database.
Pls tell me the procedure in detail.
Thanks.
Hi Pinal! Recently i have know about SQL Server Management Objects. I want to create table using SMO in sql sever 2005 with primary key & foreign key. can you help me in this regards?
Hi Senthil,
I understand your problem. However, if would have been better to create 2 fields for using Student Id.
Example one field as just numeric (auto increment) whereas other as alphanumeric.
So Student table can be as ID (Character), ID1 (Number), StudID (Character) Name (Character), etc.
ID can store “STU” whereas ID1 can be 1 and StudID (ID+ID1) = (STU1)
In this case, you can either select StudID as primary key or ID1 as primary key. In fact you won’t even need to create StudID as for display you can always show it as ID+ID1 as explained above.
Let me know if that suits.
Shreyas
Let me know how that
Hi Pinal,
Impressed with your knowledge.
Need help with this. I have connected to teradata as a linked server from SQL Server 2005.I have done this using OLE DB Provider . I can see the table and views and the test connection succeeds. But When I run any query it gives me. Cannot open schema rowset Msg 7311 error. Its a 32 bit windows server 2003 machine running 32 Bit Sql Server 2005 and the teradata linked server is also a 32 bit server.
I haven’t found a solution anywhere. I will greatly appreciate any help.
Thanks,
Aarthi
Hi Pinal and all users facing same problem,
I was able to solve the problem for maintenance plan backups not happening.
After lot of investigation, what was found that sqlmaint.rll was the main culprit. This file was found missing in a specific location where sql looks for.
After copying this file from another location it was all Ok.
Pinal: Thanks for your help anyways
Regards
shreyas
Hello Dave,
Need your help very urgent.
SQL Server does not work. Contact me.
Regards,
Wilson Gunanithi. J
Pinal,
I am trying to use a charindex to find either a space or a colon within a string. From there I am trying to get the string that is the shortest in length, but I keep getting an substring arguments error or null values for the colon based srting when I attempt to run my SQL. Do you have any suggestions?
Thanks,
David
SELECT fullname, CHARINDEX(‘ ‘,fullname) CountToSpace, CHARINDEX(‘:’,fullname) CountToColon,
(case when ((CHARINDEX(‘ ‘,fullname) = 0) and (CHARINDEX(‘:’,fullname) = 0)) then fullname
when CHARINDEX(‘ ‘,fullname) > 0 and CHARINDEX(‘ ‘,fullname) 0 and CHARINDEX(‘:’,fullname) < CHARINDEX(‘ ‘,fullname) then SUBSTRING(fullname,1,CHARINDEX(‘:’,fullname)-1)end)
Hello all
I am having a problem on database.
In details: I downloaded 1 database from online server and restored it on my machine . but all the tables and stored procedures created in that database is under 1 user
like select * from abc.tbl_Login
and i am logging to the sql server 2005 using usename=sa
and password=abc123
so while accessing tables or stored procedures i have to prefix it with username like abc.tbl_Login ….am not able to access it by only using select * from tbl_Login
SO is anybody has any solution for this then plz forward me
otherwise i have to change whole code by using like abc.tbl_Login
Thanks
hello sir,
i want to use MAX function in insert query, how can i do that?
insert into xyz(id,name) values(max(id),’nirav dave’)
i want to use max(id) in insert query coz i don’t want to fire special query to find max number of id,
plz. help me.
You can not directly use aggregate function without specifying the resultset/table. Your Query is syntactically wrong. What are you trying to achieve here ?
One option is use make use of identity column so that you dont need to find maximum and increment it by 1
Hi Pinal,
I am working as a software developer on the .NET 2.0 platform and SQL Server .My front end skills are good but I want to enhance my database or back end skills.
The reason is that I have done my BE in Chemical Engineering and then I joined an IT firm. I studied ASP.NET and C# of my own.
The book I refer for these topics are C#: The Complete reference and ASP.NET unleashed.
Can you suggest me some books which would help me in enhancing my T-SQL programming and SQL Sever 2005 programming and which cover the basics.
Thanks and Regards,
Prashant
Imagination is more important than knowledge –>Albert Einstein
hi pinal,
i have installed sql server 2005 successfully . but when i was logging in
i was getting the same error
A connection was successfully established with the server but then an error occured during the login process (Provider :Tcp provider,error:0 -An existing connection was forcibly closed by the remote host.)(Microsoft Sql Server,Error:10054)
next i have seen tht the services but the services has not been started.i.e
ms sqlserver
ms sql agent
when i am going to start these services it is giving me this message
“”some services stop automatically”".
and the services r back to their normal position(i.e in stop position)
so please pinal help me to solve this situation
please i need your help
not one can help me
on
generate daily shift pattern 1,1,2,2,3,3,4,5
every 30 days
tnx for your help
ilan
Dear Shri Pinal Dave,
To introduce my self, I am Rajesh Thakkar, an ex bank officer and an ex-employee of Virmati Softwares a Banking Software development company, I worked with them as Domain Expert and Business Analyst.
I belong to Ahmedabad and have migrated to Chicago(US) last year and am pursueing studies of SQL Server 2005 as per a recruiter’s advice.
I came across your site sqlauthority.com and has found it extremely useful.
I some times find some queries extremely difficult to formulate. Can you give me some web resources which I can refer to, to formulate queries or can you support me to formulate correct queries?
Thanks in hopeful anticipation.
Rajesh
Hi Pinal,
Rupesh here I am working as a DBA.
I am having one problem related to Performance…
My database size is about 20GB Testing Database In sql server logs I m finding this Error:
SQL Server has encountered 130 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\SQLDATABASE\SPSTAGE2006_Data.MDF] in database [SPSTAGE2006] (5). The OS file handle is 0x000007B8. The offset of the latest long I/O is: 0x000000ed2a8000
This seems to be a hardware issue:
and retrieving of simple select statement is also taking so much time sometimes…
Please give me some solutions ..
Regards,
Rupesh
Hi,
I would like to get a list of all queries related to a database using SQL server, instead of searching every file in that folder for a query.
How do I do that? I am new to the project, so how do I search and list all the queries that have been created on it?
Any help is appreciated.
Thanks.
Suchitra.
Hello sir,
As am arief from chennai. i have a doubt.
i want assign multiple table to single data set
i hope you will responce for my doubt
thanx
gud day sir, im a newbie in using MS Sql 2005, ive tried using the Rebuild All Indexes using the enterprise manager, by right clicking the Index folder then choosing the rebuild all option, but some tables with (non unique, unclustered index) fragmentation level does not drop to 0, what am i doing wrong? thanks.. can u email me?? thanx. God Bless U
hi pinal, i have a stored procedure that have some input as well as some output parameters. now i want to list following information about a particular stored procedure.
parameter_name, data_type, length, parameter_direction.
i have tried sp_help system stored procedure. it gives all the information except ‘parameter direction’. can u help me in this regards?
hi,
i have one problem, i am using sql server 2000.
my sql server database is on remote location. and i want to take backup of the database on my local machine by using enterprise manager, can it possible? if yes then please tell me how can i do this.
thanx
with regards
maneesh
Hi Pinal,
I am new to SSIS. I am attempting to read the system variable PackageName inside a Script Task. I get the following error message:
“The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.”
Can you please shed some light on what I am doing wrong?
Regards
Suresh
Hi Pinal / anyone who passed this stage,
I want to migrate from sql server 2000 to 2005. as far as databases are concerned, i can, but how to migrate DTS packages?
Can you guide me the best and easiest way to do it ? I know there is DTS wizard / transfer.
Regards
Shreyas
Please help me how to recover Ms Sql2000 database from suspect state?.
Dear Pinal,
I need your help on some sql time outs. I just joined this company and they are having several time outs on sql procs. I checked the procedure and did some minor updates such as set nocount on and off, etc.
I checked the error log and the error message is;
‘Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.’
Can you please post couple of option to solve this issue. Thanks
Legacy style joins (*= or =*) with SQL 2005.
I have inherited some stored procedures that were written a while back by some of our off shore resources and migrated to SQL 2005. Some of our customers, run into issues with the left/right outer join pages through our application and we found the issue to be using these joins (*= or =*). I want to know if it is true that SQL 2005 stopped supporting these legacy joins? I searched MSDN and google and couldn’t particulary find any article to reflect the same. I may have searched using wrong key words so if any one has information on this that will be highly beneficial.
Thanks much
Hi Dav!
you said don’t store images in databas. The database will go slow performance.
Please can you send me how can insert images(URL) in database and retreive into datagrid using asp.net.
Thanks….
HI, I am looking at the remote database administraton and waswandering is there is a good resource for the scripts that i need to run and report on to ensure the helath of the server or database.
thanks in advance
Hello pinal
i m designing a database for matrimonial site in which main consideration is given to search criteria(more than 15 columns are used).so i want to know should i have to design a single table for the whole information.if i normalize that table then multiple tables are used for searching n more joins are performed.so i want solution according to performance.
thanx in advance
hi pinal,
i want to change one column based on codition if the value of column is a then it shd get updated to b and if b then into a
how shd i do it
I would like to design one database (OLTP) for my company and database size is going to be 300 GB and it will grow up to 900 GB in another five years.
I need your suggestion for creating log file.Generally,I used to create single log file.Another DBA in my company likes to create multiple log file for that database.What is your suggestion for single or Multipe log file?Please advice.
Hi Manisha,
Can you give at least 5 rows with example ?
Shreyas
Hi Pinal , Great Job !!!!
Wish to upload 400 CSV Files Data to SQL Server
Can i get any help from your side ??
In Advance Thanks
Aditi
Greetings Pinal, I enjoy reading your blogs. I have a newbie question that I can’t seem to get a good answer for.
If you use a select distinct on a 10 column table with two columns used in distinct, but need the resultset to include an index so it can be used to drill down in an app, what’s the best way to accomplish that? I jave found a couple of convoluted ways to do that, but they invariably are very long and complicated and sometimes produce incorrect results. If I include the index, I get every record as expected but not wanted.
You provide clear and detailed answers, thank you!
Charles
I am looking create a DB build script for my SQl Server 2005 database, execution of this should create table, insert data, execute a set of SQL commands that fetch from cursor and insert into a table etc.
I would like to place all my create table statements in 1 create.SQL file. I would like to place all my inserts realted to table1 in insert_table1.sql etc.
In my DB Build Script, I would like to make the calls to create.SQL and execute. Similarly, I would like to make the calls to insert_table1.sql and execute it.
How can I accomplish this? In Oracle, I would use @create.SQL and @insert_table1.sql. Any help is greatly appreciated.
Hello Pinal,
I have a great confusion regarding what to use “uniqueidentifier or identity ” as primary key.
My Database contains apprx :70 tables.
Should i use uniqueidentifier or identity as primary key across my whole database.
OR
Should i split i mean in some tables uniqueidentity as primary key and in some tables as identity.
Kindly revert back on this to clear my confusion.
Regards,
Siya
Hi Pinal,
It was wonderful to meet you in person yesterday. Hopefully, you will visit our company again and teach us some more.
Milind Karthik
hey,
You rock man, absolutely. With your blog I am at my best.
Bests,
Mike
I have package on sql server 2000, when ever i execute that package it throws an error saying “Login failed for user(null). Reason: Not associated with a trusted sql server connection” Can you guys please suggest me what should i do regarding that the package runs successfully. Please helpme.
Venkat
Hi Aditi……..
Go for DTS – Data transformation service, utility in SQL Server.
Let me know how you go.
Shreyas
Hello Venkat,
This could be problem with your ODBC connection.
Make sure that user name and password is entered correctly.
Let m know if that solves the problem.
Also you could try running the package in query analyser step by step for testing purpose.
Shreyas
@Devdatta,
Read http://blog.sqlauthority.com regulalary.
@Nitesha Patel,
@Senthil,
Sure I will send you email describing your answer.
@Vinoth,
I need little more information to answer your question.
@G.Thangapragassam,
Search your question at http://search.sqlauthority.com
@raghuvaran,
Read http://blog.sqlauthority.com regulalary.
@Pinkesh Khatri,
My experties lies in SQL Server.
@Bhabani,
Sorry. There is no solution if you do not have complete backup.
@Samir Nigam,
http://blog.sqlauthority.com/category/sql-constraint-and-keys/
@Aarthi,
This is interesting problem. Let us discuss through email, once we find solution we will post it here.
@David,
I will follow up with you in email. NULL values are always interesting to play with.
@Archana,
You are facing problem with Schema. You need to give permissions to users to access schema.
@Nirav,
You will have to use GROUP BY clause.
@Prashant Sharma,
Read http://blog.sqlauthority.com regulalary. I post lots of good book review.
@Arihant Kothari,
What is the server you are using with.
@ilan,
Not sure what is your question.
@Rajesh,
Sure. I am always willing to help.
@Rupesh,
It seems like error with operating system or Hard Drive might have corrupted.
@Suchitra,
I have already written about this topic on my blog post.
Search your question at http://search.sqlauthority.com .
@Arief,
I will send you email.
@angelo conti,
You need to defragment your database.
Search your question at http://search.sqlauthority.com .
@Samir Nigam,
I will try to write about that soon on this blog.
@Maneesh,
You will have to connect to remote admin using enterprise manager.
@Shreyas,
The best way to upgrade your packages is with the Package Upgrade Wizard.
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
@Suresh,
You need to provide more information why it got into suspect stage?
@Fahima Shaffi,
Need to know more detail about query.
@Srinivasan Prasanna,
Search your question at http://search.sqlauthority.com .
@muhabas,
I will send you email.
@cube,
Search your question at http://search.sqlauthority.com .
@Rakesh,
Post your job request at http://jobs.sqlauthority.com
@Manisha,
Use CASE statement.
@Boby Khan,
Create log files on SAN.
@Aditi,
Sure, I will post about this on my blog very quickly.
@Charles,
Sure, I will post about this on my blog very quickly.
@Vashudha,
I will send you detailed answer in email.
@Siya,
I have already written about this topic on my blog post.
Search your question at http://search.sqlauthority.com .
@Milind Karthik,
It was my pleasure as well.
@Mike Dymtri,
Thanks.
@venkat,
We have moved to SQL Server 2005 so I have no way to reproduce your error.You might have entered incorrect username or password.
@Shreyas,
Thanks for helping blog readers. Appriciate your help.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
hi,
I have a database say last year database. Now
I want to create a new database , new tables, new stored procedures and new udf.
I created new database using SQLDMO.
I created new tables in new database using code
Now I want to create stored procedures and udf’s programitically using visual basic and ado code.
For this I tried following options :
1. I generated a SQL Script of my custom stored procedure
in a text file. I tried to access the text file and using exec command in a new stored procedure I picked the text file data which will create my custom stored procedures.
2. I tried to put all stored peocedures create method in
one new stored procedure and call that ‘CREATE_SP’ stored procedure which will create my custom stored peocedures.
But both the attempts were failed.
Please guide me on this.
Sankalp
Hi, Pinal !
I am working on MS SQL 2005 and have a question on using TOP issue while creating views. A view (just about 100 records) is created with TOP (100) PERCENT and order by, say LName. The output did not look ordered. I changed to TOP(214147483647) and the output came out in ordered. But I came to know the system is getting slow. Please explain of how the TOP(214147483647 worked and is this created a cache that make the system slow or what? Do you know how do I ordered the view by LName?
Your response is greatly appreciated.
Titus
Hi Pinal,
Im new at SQL and at my job I have been asked to create a SQL-query to create more than 250 databases of a single query. This databases are mostly for testing purposes.
Thanks in advance,
Mark.
hello pinal
i have done orcale 9i database administration and i m new to sql server database so i wana to know about the security through logins.means how to create then n assign different priviliges to them n wt role should be define to them for only selection of data.n how to define schema to them so that they create objects in their schema.i need it urgently.
thanx
hi
plz tel me also is it possible to copy database from one system to another.if possible wt things are also copied with it.
Hi Pinal,
I have a question about getting the path/folder name of where my backup is going. For example, when I go into the maintenance screen, I can see that I am creating a sub-directory for each database. The next line is the folder. In my example it is ‘H:\MSSQL\Backup’. I am writing a script to monitor the size of my disk and the size of my backup to make sure that I have enough space on the disk to preform the backup.
how in the world do I get the H:\MSSQL\Backup via SQL or a stored procedure.
Thank you in advance.
hi
in my comp two applications are there one is desktop application and another is web based application.both database are in sql server 2005.
i wana to know is it possible to make a common database for that so that they can communicate with each other.
its very very urgent
so please reply as soon as possibe
thanx
Hi Pinal;
I have quick question if you can reply me back soon as possible that will be great.
What is database schema, and how do you define this schema?
How do you merge to database in to one database? Do you have to define schema when you merge these to database in to one database?
How do you merge two tables in to one table? Do you have define schema when you merge two table into one?
Thank you in advance for you help.
Rahul Patel
Insert in Chunks..
Can You please give me an example in sql 2005 on how to insert data in chinks to reduce locks as well as not compromise on performance..
–insert in chunks
insert tbTest(Id,Fname,Amt,……….)
select x,…… from tbSource
Dear Pinal
I am a software engineer from chennai, India and have two years experience working in SQL 2000. My job nature is mainly handling the business loggic of our prjoect through stored procedures and triggers. Now i am planning of doing a certification course in SQL which would nehance me technically and give me better job perpectives. What certification do you suggest. Please help. Waiting for your response.
Regards
Balaji
Pinal,
I have question
We have interface which brings down few tables from web database to our local database, in this table some of the columns are bit columns, my question is if some values in this column are null then what it will bring down ‘True’ or ‘False’ or Null.
Sonal
Hi, Dave
I want information about replications I have to network LAN-WAN-LAN one LAN end keep server to update data in data server and also same server have applicaton to service from the request. I just want make replication database server to keep other end of LAN and it is syncronized data automatic when transaction update . So how i can do.
regards,
sandy
Pinal,
I want to post a review of your site in our blog (http://dbalink.wordpress.com).
I am really impressed with your site. I think it is better than the BOL in many aspects. Your site needs to be advertised in as many channels as possible. And I would love to do that. As you may already know, I have your site in my blogroll.
Please email me the info and the highlights of your site ASAP.
Regards,
Marlon Ribunal, DBAdmin/Linked DBA Group Manager
Hi Mark,
Firstly, Go through the syntax for creating database….
Now, what you could do is write a stored procedure wherein, you create database test(i)……..
Declare i as variable and loop the create database test(i) till i reaches 250, in your case.
Let me know if that works.
Shreyas
Hi Sonal,
If they are bit columns then they will not give true or false.
Bit datatype always can be 1 or 0. So while creating table if you specify datatype as bit and accept null then if you query result as
select * from table where column = ”
then it will return you all ’0′ values.
Shreyas
Hi Pinal,
do you have any info on log shipping? if you have step-by-step instruction, please send it to me.
Regards
Siva
hi,
Is it possible to set a nullable column as a Foreign key
to PrimaryKey Column of another table?
Siva.
Yes you can. Foreign key can take NULL values but not Primary Key
Hi,
Pinal!I have a problem.How to set in stored procedure parameters for from date”@FD” and to date “@TD”.10x alot
Hi Pinal,
u r great !
dheena
Hi pinal,
Now i am working in java with sql server.
but i am very intresting to work in sql server.
so i have plan to switchover from java to sql server.
what i have to do now..
suggest me.. please….
thanx
dheena
i have table has 3.5 milion record and want to do some operation on it and i alwase have problem with time and speed
wark with mssql 2005 and amake application with microsoft c# 2005
pleas i want some information to increas performance of mssql 2005 more and more times
thank you
Pinal,
Have you ever seen an error such as I see in my Appliation log on a Windows 2003 server?
Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 42000, Native Error: 18456
Error state: 1, Severity: 14
Source: Microsoft OLE DB Provider for SQL Server
Error message: Login failed for user ‘NT AUTHORITY\SYSTEM’.
The client also has errors in their application of WindowsFormsParkingWindow – Memory could not be read.
Any help would be appreciated.
Thanks, Mark
compare the data………………..
How to compare the data one table to another
hi dave
why its not possible to exist two identity columns that will be auto incremented in a single table in sql server 2005
thanks in advance
Its because, Identity is meant for identifying unique records
Hi Pinal,
Your web provides a lot information about MS SQL and it is really helpful.
Could I ask you a question about replicating a new stored procedure automatically? Is there any way we could setup to replicate the new stored procedure automatically without add it to a new articles manually?
Thank you
Ting
hi pinal deve sir
i would like to ask this question
how can i store or fetch the date except it display date with time
We are having some problems training our developers. Consequently we have tables dropped and stored procedures changed without the proper controls on the process.
We are using the new DDL trigger to track changes, which helps. But now we would like to programmatically script out all databases on all servers so that we can easily recover from the unexpected dropped objects. We have 11 servers with about 400 databases.
I appreciate any suggestions that you might have to develop a way to do this.
Hello good noon to all,
I have following query:
I want to remove all the triggers and identity property of all tables from specified database in one go
Ex. suppose my database ‘Test’ consist of 10 tables and each one is having identity property and triggers defined
n i want in one go all triggers and identity property should
removed
Is it possible?? if yes then how
Thanks in advance,
Archana
– Get the script to drop all the tables
SELECT ‘DROP TABLE’ + table_name FROM INFORMATION_SCHEMA.TABLES
– Execute the script
When you drop the table , all the triggers and identity property drop with it.
Satish Shrikhande
Hi Pinal,
We are using SQL Server 2000. Some one is deleting record from one important table.
We wrote on e trigger for getting the deleted data.
Can I able to know which machine ID the particular query is executed?
Pls advice me to proceed further?
Regards,
Suresh
Hi Pinal,
I have a database on SQL Server 2000 and recently we moved it to SQL Server 2005.
While running some queries we found that we are not getting the same results in SQL Server 2005 as in 2000 server.
We ran the same query in both the servers but they returned different outputs.
I am not sure why this is happening.
Please advice on this
Hi Pinal,
I need to use split function in sql query same as .net split function.
I am not finding any sql function for splitting particular column. So i decided to write use defined function for the split functionality. but I am not getting values in multiple columns after splitting the column
@ Satish Shrikhande
Hi
I want all tables plus data of those tables
all i need just to remove identity property and triggers on those tables as i want to create copy of those tables with data into other databse depending on some condition
Thanks for ur reply
Hello Pinal,
I am read u r articals regulraly
and it will help me lot off
thanx for that
I wanna help for one sql query
I want to select date of the current month’s
Second and fourth Saturday
Please help me
I am waiting for u r reply
plz help……..
Its very urgent………..
Thanx in advance…………….
Hello Pinal,
My database has a duplicate entries like 0123 and 123. I want to find such duplicates. How do I go about it? I tried for substring but that returns exact same entries not the once that I mentioned above.
Can you help with this?
Your articles are informative. Great effort!!
Thank you.
Regards,
Rashmi
Pinal,
Thanks a lot. Found a job because of you. Hope to meet in person one day.
Raman S.
dear pinal,
after a long time i have got this error on sql server 2005 . whenever i use to attach database or detach database always the error use to be
“Could not load the DLL xpstar90.dll”
what does it mean?
and when i have seen the services of sql server 2005 sql server agent was stopped i was starting the services but it was showing me that some services stop automatically when there is no use .
so please pinal can u help me out of this situation.
Hi,
I want to know if there is a way to script all objects and their data into one script file using SQL 2000.
- Binoj
Hi Pinal,
I am read u r articals ,it will help me lot off……….
thanx …..
I wanna help for one topic.
I want to run multiple “*.SQL ” files at a time (like Batch Process) , it’s Possible without using any third party tools ?
Please help me
I am waiting for u r reply
Thanx in advance…………….
Hi
I have a database in which one of the table is having huge data.i want to create file group for this table based on date range like for jan 1 file,feb another.. like this.I am inserting data into this table using SSIS packages.So how do i handle this situation.. LIke from packages how can i check the constraint and create a file if required .Is it possible or not.Is there any work around if it is not possible from SSIS packages to create files.
thanks in advance
moupiya
Hi
A third party system we work with has twelve identical tables. Lets name them INFO1 to INFO12.
I would like to write one (1) stored procedure which, with some kind of case statement, is able to join to either of the tables. Some kind of “JoinWithThisTable” variable is probably necessary.
select p.name, i.something
from person p
left join INFO1 i on i.id = p.id
INFO1 could be INFO7, or INFO9, or..
Is there a “native TransactSQL way” – today I build up the query in a varchar and end the store procedure with exec(@query).
Thanks in advance
JJ
Hi Pinal:
I am currently using all my data in xml files to run a GUI developed using MFC in visual studio 2005. My GUI is a stand alone application.
How much effort required to convert xml files to port to sql server 2005 and write stored procedures and make it work?
Could I still keep my GUI as stand alone application or do I need to convert to client-server architecture?
Thanks for your time.
kasi
Hi
I want to copy database from one sql server 2005 to another 2005 how can i do that …. i am not getting copy database in my management studio so i have generate scripts but it did not help me ..I have given different database name …is this problem ?
What should i do ??? please help me …
Thanks & Regards
dev
hi Dave
Please help me installing
Analysis services & MDAC in SQL Server 2005
Please provide me any docs available
thanks waiting for your reply
Hi Dave,
I want to understand the logic behind the working of the following command in retrieving the 3rd Highest Salaried Employee. I mean what happens just by using a number (2 in this case) to match with the result of a sub query.
Select Emp_Name,Emp_Salary From Employee A
Where 2 = (Select Count(*) From Employee B Where B.Emp_Salary > A.Emp_Salary)
Thanks,
Rakesh Verma
Hi! Pinal
I need to know how can I Encrypt or decrypt our password.
Hi Dave,
I have a Database in SQL 2005. having 12 tables.
and now my requirement is i wanted to keep the history of all the table modification.
I want to trace what all changes were made in
this database on a particular date an time.
A kind of report that will highlight all changes made to the Database on that date.
How do I acheive that ?
Please advice
Rajan
Hi Pinal,
I am trying to set up Database Mirroring in SQL Server 2005 for Disaster Recovery Solution.
The principal server and the DR Server are geographically separate locations. So with Asynchronous Mirroring will we have performance problems on the Principal Server during periods of high writes to the Database?
If Mirroning fails from where does it start mirrroring the next time it starts?
Also is Log Shipping preferred? I have been thinking of setting up Mirroring and also manually backing up the Tran Log and physically copying it over to the DR server. Can setting up manual copying of Tran Log affect Mirroring?
If I do the manual copying of Tran Log each hour, how can I restore using some automated script at the DR Site?
Can log shipping and Mirroring be set up simultaneously?
Hi Pinal
how can i store the result of an update query
into a variable
for example after i execute an update query i see a message “6 rows affected”
is there anyway that i can store this message into a variable
Thanx in advance
MAKAROV
Hi Pinal,
I was wondering if it is possible to use the SQL Where clause and have 2 different files in it. For example:
Where file1.Status ‘V’ AND file2.Status ‘V’. I’m not sure if the where clause is limited to 1 file or not and cant seem to locate any examples of multiple files in a where clause. If it is possible, do you have an example? I greatly appreciate your time and response, thank you!
Dan
hi! pinal .
I am new learner of sql server. can u plz tell me how it is useful for mechanical and civil engineering stream. Can u plz give one application of each stream (mech/civil) where it is used.
thanku bye.
Hi Pinal,
I want to know how many Non Clustered Indexes we can define on a particular table and also clustered Indexes?
Hi Bhupali,
You can define 1 clustered and 249 non clustered index
on a particular table.
Hi Pinal
I have a question, I am working on Microsoft SQL Server database, and only 3 of the stored procedures have a padlock against them, and the modify option is disabled, how do I enable that?
Thank You
Harp
Hi Pinal,
I m very much impressed with your blog. It is everytime very useful and knowledgeable.
i have one problem here hope you can help me.
Table1 (this is my present table)
———————————————-
col1 col2
—– —–
1 abc
2 xyz
———————————————-
Table1 (I added Col3 and want to put same data as in col2)
———————————————-
col1 col2 col3
—– —– —–
1 abc abc
2 xyz xyz
———————————————-
how is it possible to put col2 data into col3 for all records?
Many Thanks,
saroj
Hi, I made some publications from diff servers every time.But When i go to my replication monitor,it shows all other Publications made previously and which r now not in use.Can u pls tell me how can i delete all those publications.
amit
hi saroj
u can get ur result with the following query
UPDATE table_name
set Col3=col2
by this both col3,col2 contains same data3
Hi Rakesh,
Thanks for your valuable answer to me. it works!!!
many thanks!!!
Saroj
Hi Pinal,
It is regarding Best Practice Analyzer. I went through the sample.
http://blog.sqlauthority.com/2007/07/05/sql-server-2005-best-practices-analyzer-tutorial-sample-example/.
Can we give the server name in computer name field?
When we access the server we need to give the user credentials.
I give my credentials when I login to my workstation, but I use my colleauge’s credential to login to the sever(remote login). I have sysadmin role for database.
When I give the server name, I get error “An error occured while enumerating services…..”. Please help me how can I proceed with the analyzer tool. Thanks.
I am having an issue with I am thinking SQL 2005 and a third party product.
I went in and changed the sa password to a simple password before starting the install of Tririga on SQL 2005 on a Windows 2003 SP2 server. Nothing else is running on this box.
But the install fails each time. the install tries to create a user “tridata” and a database tridata and the password is again tridata which I have tried to change to complex but it still fails.
The eveent viewer shows as error 18456 Sev 8 or Sev 5 SQL server log shows login failed for user “tridata” [client:127.0.0.1] I have tried to install locally and remotely no luck either way and suggestions ( I also created the user inadavance and still the same failure)
Hi Pinal,
Is it possible to restore Sql Server 2000 Backup file in Sql Server 2005, I have a SQL 2000 backup file need to restore in SQL 2005.
thanks In advance
Sir,
I thank you to you for your help and guidance. I will never forget the advise you gave me. I wish all your reader can atleast have phone call with you.
Thank you again,
Megha
Hello pinal, i visit your blog periodically. It is highly informative. i recently a multi media content website http://www.dotnetvideos.net.i have over 100+ videos on the site right now with an additional 200+ videos in the pipeline ready to be added. Many more videos are in the pipeline. I created this website as a community give back.Every registered user of my website will receive a FREE 6-month subscritpion to asp.net PRO magazine.i would appreciate it, if you can announce the launch to the members of your blog readers or a .net user group you are associated with or any other .net developers.You may be instrumental in making your blog readers or visitors to receive a premier asp.netPRO magazine.
Please look at Scott Mitchell’s (www.4guysfromrolla.com creator) comments on his website about http://www.dotnetvideos.net at http://scottonwriting.net/sowblog/posts/13074.aspx
**I do like lot the content on your blog, If would you like to contribute any content to my website please contact me.
i would also invite you to visit my website. thanks for your time and consideration.
–ravi nangunoori
http://www.dotnetvideos.net
Hi Pinal,
I have question about sql server collations. How can I tell whether collation supports
storage of particular language. Is there a mapping table?
For Instance, I need to make sure existing columns collation will support
French, English , German and Spanish. I know that the best option is probably to use
Column types that support unicode, but is it really necessary in this case? All languages
required are use single byte character sets, also unicode will use more space, another problem is that
I would have to change existing db columns (by design), which requires significant regression testing,
Which I don’t really have a time for.
Let me know your thoughts.
Regards Igor.
Hi Pinal,
I am from Australia. I visit your website and found lot of useful articles, I was wandering if I could ask you a question, Currently I am working as a 1st and 2nd Level Helpdesk Support position and wanna move to Database side, could you please guide me to correct direction, as I was planning to do Microsoft SQL Server 2005 and apply for Database admin or Database developer job.
Hi Pinal,
I need create a database user in SQL server 2005 with speicific permissions like execute, create new tables, views, storedprocedures, add new datatypes…., but I should not use ‘dbowner’ role.
Could you please help in this..thank in advance.
Cheers,
Pruthvi
I have a query. How to select Sql Server names through sql query?
Please mail me.
Thanks in advance.
Hi Pinal,
I tried searching some article on Data modeling but I could not find one ( Correct me if I missed ). Wondering if you can share your views on data modeling on your blog. Keep up the good work.
Thanks,
Hello, I m Trying to remove some snapshot Publications for the databases that right now does not exists.some snapshot publications r removed but some r not. Is it related to some distributor problem.
Hi Pinal,
I want to upgrade from sql 2000 to sql 2005 ,and have databases with data. Do i need to bring down sql server 2000 before installing 2005 ,also will I be able to access the databases of SQL 2000 ?Please advise what steps I need to upgrade to 2005.
Pinal,
Concerning Error 18452 and the info you provided. I was had the issue arrise and followed almost all the blog and support site configs to resolve the issue. But none of them worked. What I found to be the issue was DNS and the naming context. My application servers were set to use one set of DNS Names and the SQL Server was set to use another set of names for example:
Apllication Server 1 – DNS Suffix order was – DNS1.com, DNS2.com, DNS3.com and the domain that the server belonged to was DNSDev1.com.
SQL Server1 – DNS Suffix order was – DNSDev1.com, DNS1.com and the domain that the server belonged to was DNSDev1.com.
As soon as I added the DNSDev1.com to the search order and placed it at the top of the list TCP/IP on the Application Server. Both TCP/IP and Shared Memory connections started working.
I thought you might be able to pass this along.
Hi Pinal,
I read your articles, those are very useful.
I want to write sql script to migrate data from sql server 2000 database to server 2005 database using sql script.
Please guide for same…
Thanks a lot….
Chandrashekhar Mahale
Hi Pinal,
Really a great work.
My questions are all about my career. I have not so much experience in SQL server DBA. But in general companies are going to give their databases to a less experienced person. They need minimum 2 years of exp. (General senario of India).
Currently I am working on DWH, specially BO products.
How to proceed?
Thanks in advance.
Regards,
Arup
Hello Readers,
This community is doing well and I am glad many readers are participating to answers questions of other readers.
@Sankalp Mehta,
I will write a blog article over it.
@Titus Jacob,
Create indexed view.
@Mark,
You can subscribed to My Feed.
@Rakesh,
You should search my blog http://search.sqlauthority.com
@Paul,
http://blog.sqlauthority.com/category/sql-backup-and-restore/
@Rahul Patel,
I have already answered your questions on my previous articles on my blog. Search http://search.sqlauthority.com
@Ranga,
Good suggestion.
@Balaji,
You should focus on learning new things. Certifications are just motivations.
@Sonal Dave,
I am not sure it all depends on many settings of your database.
@sandy,
Search http://search.sqlauthority.com for solution.
@Marlon Ribunal,
Appriciate new blog to help community.
@Siva,
I will send you email about all your questions.
I have already answered your questions on my previous articles on my blog. Search http://search.sqlauthority.com
@Vladimir.
I do not understand your problem well, please explain properly.
@dhenna,
Read http://blog.sqlauthority.com regulalary. I tried to update it frequently.
@Mark Karcher,
I have not faced that error before I will research that and will let you know.
@thamilasarasan,
There are tools available to do this task. I will research best in market and will write about it.
@Ting,
Thanks, yes there is a way and I will write about it.
@Rakesh Nirmal,
Please explain your problem in detail.
@Richard Mason,
DDL Trigers are great way to avoid problems like you are facing. I have previously written about it.
Search http://search.sqlauthority.com for solution.
@Archana,
Yes, it is possible. I will try to write custom solution for you.
@Satish,
Good script.
Other readers use it carefully, it will drop all your columns.
@Suresh,
If someone is modifing data DML triggers will help you.
If someone is modifing schema DDL triggers will help you.
@Vipul Agarawal,
Some syntax may act different. What is exact SQL for your problems. Send me email.
@Chetan Sharda,
Post your function to me and I will look into it.
@Amol,
Search http://search.sqlauthority.com
@Rashmi,
I will write UDF for you.
@Raman,
Congratulations.
http://jobs.sqlauthority.com
@arihant jothari,
Send me mroe detail.
@Binoj Daniel,
I am aware of same feature in SQL Server 2005.
@Koushik Chakraborty,
sqlcmd can be used in your case.
@Moupiya,
I will soon write article about your suggestion.
@Jens Johanneson,
I am not sure what is your questions.
@kasi,
Client-Server Architecture is good model for your case.
@Dev,
I have previously written about it.
Search http://search.sqlauthority.com
@sampath,
I will send you docs requested.
@Smita,
I have previously written about it.
Search http://search.sqlauthority.com
@Rajan,
Use DML triggers.
@Kal,
Yes you can do both together. However set up is very complex. I will write down action plan for you.
@MAKAROV,
I will write article about it.
@Dan,
Sure, I will write about it soon.
@shaila,
SQL is userful to everyone.
@Bhupali,
You can define 1 clustered and 249 non clustered index
on a particular table.
@Dhruv,
Thanks for your answer.
@Harpal,
I will soon write about it.
@amit saxena,
Replication problems are always interesting to solve. Provide more details to me.
@Rakesh, @saloj,
Good to see you got your answer.
@Prasant,
Good question, I will send you email with answer.
@Max,
Yes totally possible. You use restore feature of SQL Server 2005.
@Megha Raychand,
It is always pleaasure to help good people.
@ravi,
Congratulations for wonderful site.
@igor,
I have previously written about it.
Search http://search.sqlauthority.com
@vim,
Please continue reading this blog.
@Pruthvi,
I am not sure what you are asking.
@sunitha,
I have previously written about it.
Search http://search.sqlauthority.com I think it is db_name() function.
@Navdeep,
Sure I will write about it to you.
@amit,
Please provide more details and I will answer you.
@Ray,
Great Stuff.
@chandrashekhar,
I have previously written about it.
Search http://search.sqlauthority.com
@Arup,
Read http://blog.sqlauthority.com regulalary.
Regards,
Pinal Dave ( http://blog.sqlauthority.com )
Hi Pinal,
Need help for creating a SP which will retrun me the first business working day ( USA) of the Month in a year for all the months..
Thanks in advance
Dhananjay
Great site – tons of great works – gratz on what seems to be a massive following…
I have a problem – just posted primary question on the MS forums – related to email notifications and maintenence plans – not sure if you might have an answer but…
http://forums.microsoft.com/MSDN/ShowPost.aspx? PostID=2886103&SiteID=1&mode=1
Hi,
i need to combine the data of 4 different tables & place it into the 3rd table in sql2000.
how do i go for that??
thnx a lot…
Amit
Hi Dave,
I have 2 servers, main & backup,
I want to use an “Insert into table5 (Select * From tableA ) where table 5 is on the backup server and tableA is on the main server,
The connect from the main server is direct thru internal IP of 192.168.1.202 with a username and password to connect.
Can you help please ?
Richard
I recently experienced 2 users (1 mssql2005 and 1 2000) who had issues querying to the database after reattaching and or restoring an image of the db. It appears that the objects were given the users schema rather than their original dbo schema (user.table rather than just (table). The users had to run some scripts to reassign the ownership/schema. Why did this happen?
Thanks
Hi,
1.Generally what is the retention period in transactional replication .
2. what are the factors we have to consider while creating a login.
3.what is index management.
these are the questions i have faced in a interview.
could u please answer these questions
thanks,
sripathi.
Hi,
1. iam using transactional replication. i published one article but it is not replicating at the subscribers .what i have to do.
2.If we set transaction replication what are the coumns are added
thanks,
sripathi
Hi Pinal,
I am trying for foloowing problem and unable to get proper solution.
My actual task is to provide to Import/Export facility to user in application. For that I was using SqlServer 2005 Bakcup and Restore scripts, I noticed that only work if restore machine has same envoirnment as backup machine (I am not sure if this is the reason), I want to move “Complete” DB. Then I found that I should user Attach/Detach script.
The steps I am doing for Detach are as follows:
1- ALTER DATABASE MYDB SET OFFLINE
2- EXEC sp_detach_db @dbname = N’MYDB’, @keepfulltextindexfile=N’true’
3- Then programmatically I allow to copy/move application user to copy files MYDB.mdf and MYDB_LOG.ldf
This works fine, and I am happy with it. :)
Problem is DETACH, when I try to Detach it says “Cannot open database MYDB requested by the login. The login failed. Login failed for user mymachinename/mywindowsusername”
I am using windows authentication.
In my application user should be allowed to copy DB files (a way to provide back up) and should be able to install application anywhere and place the files back so he will have all the schema and data back in that machine. I think the approach I am trying is correct, if i can getrid of the above mentioned error.
Please guide me if approach is correct, and help me to resolve aboe mentioned error, or if there is better way to achieve the task i am trying to do.
Best Regards
–Junaid
P.S. I am not sure when you post a reply I will get email that reply is there and I should visit your web page to read it.??
Hi Pinal,
I have installed SQL Server with windows Authentication mode. Later I planned it to change as SQL Server Authentication Mode. I opened server properties – security – changed the mode. When I try to login in it is asking for password. I tried with blank password and my previous password, Though it is not working, pls help me……………
My previous post probably not explaining the problem exactly, here I try again.
I am trying to Detach and Attach DB, I read your one previous blog about that and I am following the same process.
In Sql Management studio it works fine and no problems at all. If I try to run the mentioned scripts in C# then DETACH works fine, copying files done properly, but ATTACH never works. The error I can see in exception message is:
Cannot open database MYDB with this login. Login Failed.
I need help to resolve this issue.
Regards
–Junaid
Hi Pinal,
I need to insert all data which is updated in a table, for this i have created a update trigger on table.
Let Suppose there is two table T1 and T2 now i have updated 10 rows by a command i want to insert all 10 rows in T2 table how can i do it?
Is update trigger is good approach?
Thanks in Advance
re: http://blog.sqlauthority.com/2007/05/15/sql-server-dbcc-commands-list-documented-and-undocumented/
Pinal –
I don’t know how to directly approach my documentation issue–perhaps you can help. Please.
I have been invited to make a presentation at UCLA in just over three weeks about DBCCs–both documented and undocumented. I have a list so far of over a 130 from the beginning of time. I’d like to suggest to the students/professionals in attendance when to use which resource. For example, DBCC ShrinkDB (to my knowledge) should be replaced by DBCC ShrinkDataBase in all jobs/scripts.
Can you point me to a resource (whether inside or external to Microsoft) that discusses ALL DBCCs? I’m looking for more than syntax to include efficacy. I have, for another example, a reference that suggests DBCC ShrinkDataBase is a “hit-or-miss affair”. Proving or debunking this kind of statement is what I’d like to do.
I’m willing to share back the results for broader dissemination.
— Dave Owen, MCT, (805) 658-6775
Hi,
I am stuck where I have requirement I have to optimize a small query ..
I have a table with 40Million rows
And that query do’s a self join on that table and do some MATHS like
Table 1 has 5 Coloumns
and my query is
Insert into Table2
select A.Column1, B.Column1, count(A.Column1) as Frequency ,sum(B.Column2 – A.Column2) as Difference
From Table1 A with (readuncommitted)
inner join Table1 with (readuncommitted)
on A.Column2 = B.column2
Am doing Minus(-) on Column2 so its like 1 row is going and looking 20Million Rows and more to get the best REsult
I tried Partioning but it didnt worked out
Any Suggestion would be help
Thanks
Sameer
Pinal I hope these should be a piece of Cake for you :))
Hi Pinal,
Post number 470and 472, after digging a lot finally I resolved the problem.
Regards
–Junaid
Hi Dave,
I am graduating soon and want to apply for a
SQL server DBA as an Entry Level. I have some experience on SQL server, but not much. I posted my resume, and a few compnies had called me already, but I afraid I am not experienced enough to take this Entry level job.
Can you please tell me specifically what Entry Levels DBA do and what their duties are? What should I focus on to prepared for this job? What topics of SQL server do I need to practice to successfully be able to handle an entry level DBA job. I am pretty sure you have managed some people, so if a company hire me as an entry level DBA, will the training be enough for duties of an entry level DBA?
Thanks Dave!
John
Hi,
I have table with date,productname and quantity columns.
I want to result in tabular format.
Like,
Date Product1 Product2 product3 ….
01/01/2008 5 7 8
02/01/2008 15 9 8
03/01/2008 25 17 18
So,please help me to write query for this type situation.
Send me solution by email.
Thanks in advance.It’s very urgent.
Good Day Pinal,
I come to you since ive been doing google all day and i just cant get to work one thing:
I have 2 servers, in both servers there is SQL Server 2000 and from server 1 i did a vinculation of servers with a particular Database, it works fine i even had some issues with collationg but nothing that could not be solved, but i made this StoreProcedure and i wanted to add it in a task job, but keeps on failing with Error 7399 failing authentication, i did the log on thing of giving access to c:\Temp and all stuff but keeps on failing…
The store Procedure has queries that uses tables from server1.database and server2.database, so i had to do when creating the Stored Procedure the
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
The SP works in the QA fine, but on the task it keeps failing, could you give me a hand?
Thanks
Sir,
I have one query for Scripting the tables.what is the use of this scripted table.I want to know the answer for
” From one server,how i can do DML commands in another server’s tables, and also copying of my tables in to that server. “
Hello Pinal,
I have updated a column in a table . is there any process by which we can get that previous column data.In simple terms, is there any undo(ctrl+z) type of command in sql server.
Hello Dave,
Having a problem deleting about 4 million records by date out of a SQL DB. My CF script keeps timing out even though I’ve added “RequestTimeout=10000000″ to my url.
delete from tablename where sdate < ’10/1/2007′;
Any suggestions would be greatly appreciated.
hi
how to insert a new record between two existing records.
response plz.
i want to install SQL server and when i start instalation i need to set sa password and i dont know where a can set it
can you help me
Hello Good noon,
I facing problem with performance in sql server ,
My application totally depends on database ,what am doing in that is selecting records from one(Previous) datbase
and inserting those records into newly creted database depending upon date selection by user
and after that am removing those selected records from previous tables.
All is working fine bt the problem is that its taking too much time
Ex. its taking more than 3 hrs for 1.58 Gb of database
so Is There any Way to minimise the timing for better performance
Plz tell me
Thanks,
Archana
hi pinal dave,
i have got this error
Could not load the DLL xpstar90.dll”
i have resolved the error .the dll was missing
instead of taking backup in the same server i want to take backup on other remote server . can u tell me how we can do it?
Hi pinal,
How to fix this issue in sql sever
“Transaction (Process ID 81) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Item cannot be found in the collection corresponding to the requested name”
How to resolve this issue as i am facing lot of problem because to this deadlock.Please help me to fix this issue.
Thanks,
AnjanA
Hi pinale,
I have a doubt, have seen your post for get first and last records.
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID)
OR
SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC)
my simplified query is on below. please suggest me my below qry is correct or not.
select * from tbl1
where col1 in (select max(col1) from tbl1)
or col1 in (select min(col1) from tbl1)
note: col1 is PRIMARY KEY
Hi Pinal,
I have one stored procedure and its taking 2 mins to execute. My stored procedure has 7 input parameters ( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 2 seconds. I am really puzzled with this.
What could be the reason why the SP is taking more than query,
Regards,
Hemant Nehete
Hello pinal,
this is my second post to you.
In sql server 2005 express when maximum concurrent connections is reached it throws a sql exception. But this exception is general
how can i know that the exception is due to the maximym concurrent connections, so that i can display appropriate message in my c# 2003 code.
thanks.
samir mackwan
software developer
Hi pinal,
I want to know the query to get primary keys of all table in my sql server 2000 database.
Check this out :
select name from sys.objects
where type = ‘PK’
Hi Pinal,
I’d like to know how can a stored procedure be executed as a different user than the current one.I tried the following command:
exec ‘name_of_stored_procedure’ as user=’impersonated_user’
I’m using MS SQL Server 2000.The error I’m getting is:
Incorrect syntax near the keyword ‘as’.
Am I doing something wrong?
Thanks!
Dave, I’m new a SQL sever 2005. I have a stored procedure with a insert statement. The table has 56 fields. Is there a way to select all fields within table without having to type each one of them.
Thanks
Hi Pinal Dave
Ur site is very useful for me … i m new to sql server
recently i have faced 1 interview ,in that they asked some questions which i dont knw
please provide me the answer for the following
1 “If one table is locked in front-end , how can we remove the lock from back-end”?
2 max how many @@ error and raise error we can use?
3 max how many .ndf files we can create at the time of creation of a database?
4 how do we know an index is properly used or not?
5 max how many .ndf,.ldf,.mdf files we can create?
major difference between DML and DDL ?
main difference between JOINS and Sub Queries?
Please help me
zakeer
Hi,
I ‘ve got a problem with using “apply” in views.
Indeed, I want to create a view by right-clicking “Views” then “New View” and Paste a request including “outer apply” statement (due to the use of function).
I always get this error message : “the outer apply sql construct or statement is not supported.”
Is it possible to use “apply” with a view ?
i have two tables T1 and T2. Both has the same schema. I am inserting a few records manually in T1 which has columns empname and empid.I need to create a stored procedure which should delete a particular record in T1 based on the parameter value in T1. Before the record is deleted, it should get copied into table T2 which has the same schema.
How to achieve this? Pls give me the full stored procedure coding.
It is quite urgent.
Thanx in advance
Regards
Hi,
I just read your blog on not posting images in a sqlserver 2005 database. This makes sense to me. However since I’m painfully new to sql and asp.net 2.0 I have no clue as to what the syntax would be to save the actual url of the photo to the database. The url is:
ImageUrl=”~/App_Data/Photos/WeddingPhotos/1.jpg”
When I put this in it shows in the database field, however it is simply text. The photo doesn’t load (in gridview) which would be my goal. I’ve spent hours/days searching for a tutorial on how to do this with a url vs a image file but have found nothing that really meets my needs or my limited knowledge.
Am I wrong in my assumption that storing the url to the photo will result in the photo showing when the gridview opens? If I am wrong could you point me in the right direction. If I am not wrong, then I need to now the syntax for inserting the url in the database so the image populates.
Thanks,
Leesha
Hi Pinal,
I’ve hard time finding reliable information about number of the bytes each data types uses in SQL server 2005.
I am trying to create a design document which shows amount of space require for each table and collectively for the whole database.
appreciate your help
Kia
Hi Pinal,
I need your help regarding transaction log file. I am totally confused about this. I need exact information i.e what is the main purpose of transaction log file.
Plz help me out.
Thanks
Madhu
Hi Pinal
I am having difficulty in something very simple. I am quite new to SQL.
I have table A, where records are inserted with duplication.
I need to create a trigger FOR INSERT on TABLE A. Copy the new records to Table B. First check if the Name which is Primary key exists in Table B, if exists update the row with the new data.
If it does not exist create a new row in Table B
Plz Help
thanks
Zinadeen
Hi,
this is sai krishna plz answer my query.
What is the diff. between select statement and self join?
how to run a command on command prompt that will sure me all the computer that runs sql server over a certain domain.
Thanks.
hi
i am working on sqlserver 2000.
right now i have problem that
i have to retrive the column data into row
if u have any solution please reply to me
waiting for response shortly
thank you
Hi Dave.
Thanks for helping Database community, great work.
I have one question for you.
I have more than 1 non-cluster index in a table, how can i enforce to use particular index in my query?
Thanks
Jaya
ANY BODY CAN TELL ME HOW TO HIDE A DATABASE IN SQL SERVER
Hi Dave,
I want to congratulate you on your 500th post. Your blog has a wealth of knowledge for a novice as myself. Thank you, for your time and effort.
Regards,
how to restart the distribution agent in transactional replication using pull subscription at the publisher
Hi Pinal,
In sql Server 2005 when I leave query window ideal for some times it is disconnected autometically showing in its header as not connected .so where is the connection properties to change its status. I searched tru google and in sql server also I couldnt find any solutions. plz help.
I have some serious issues with paging thru large result sets.
Coding for Paging, Sorting, Filtering in a signle page is very difficult in SQL Server 2000 days. I think this same with SQL
SErver 2005 also (even with ROW_NUMBER()).
Why Microsoft is failed to comeup with a neat solution like LIMIT clause in MySql. Its so much easy to use, even in commandText.
And, I wouldlike to know comparision between ROW_NUMBER() and LIMIT clause in mysql. Which is the best interms of performance?.
Looking forward for good reply…
Regards,
Pradeep
hello Pinal Dave
please tell me about cursor in sql server with some examples.
with Regards’
Manish
Anybody Tell me how to auto generate primary key which is begin with emp.With Regards
With Regards
Manish
Hi
This is UmaMahesh. iam .NET Developer. i need ur help in SQL server.
i have one table say table name is Table1.
which contains 3 columns called ‘year1′, ‘year2′, ‘year3′ and
also alwasy the table contains only one row. The values will be like this
Year1 Year2 Year3
1 0 0
i need the column value, whichever column contains the value ’1′
means in the above scenario i need year1 value only
plz help me
it is urgent for me
Regards
UmaMahesh.Ch
Hi sir,
I wnat to become a DBA.How can i become a DBA?Tell me the best institute in india providing courses in the field of database.Reply me soon.Waiting for ur reply.
Thanks & Regards
Vinay Pandey
Hi,
I have used START WITH …. CONNECT BY in my sql to get the output in a hierarchical structure(tree structure).
Since sql server does not have the connect by hierarchical option, could you please let me know if there is any similar option for SQL Server and DB2 databases too?
Also, could you please tell me the need of using WITH
ex:
WITH n(empid, name) AS
(SELECT empid, name
FROM emp
WHERE name = ‘Goyal’
UNION ALL
SELECT nplus1.empid, nplus1.name
FROM emp as nplus1, emp AS n
WHERE n.empid = nplus1.mgrid)
SELECT name FROM n;
Thanks in Advance
Hi Ankur,
Ankur will you please tell me that how can we insert a value of one database table to another database table through query?
Regards
Jani vishal
hai dave ,
i want to dump an excel file with large amount of data to a table .
what are the different methods ,please tell me?
send me reply as early as possible. i am great full to u if u suggest in this regard.
Hi Pinal,
I have one master table , in a table having password field as varchar(50). Now i want to convert to varbinary using sql script.
Please give me a solutions.
Hi Pinal
I am using SQL Server 2005 and would like to get all the rows that exist in
(Table1 – Table2) without the answer being distinct, what do i do?
Example:
Table1:
Products: A, B, C, D, D, D, E
Table2:
Products: A, C
select * from Table1
except
select * from table2
will give me: B, D, E
But i want it to give me: B, D, D, D, E
Any way that would work will be helpful. Thanks in advance.
select t1.Products from table1 t1
where not exists(select 1 from table2 t2
where t1.Products = t2.Products )
Hi Pinal,
I’ve a subquery which returns more than one row (user_group), and i need to convert those rows into a comma delimited list. i tried with various possibilities but unable to find the solution. can you pls.help.
here is my query below.
SELECT
user_ID,
user_Firstname,
user_Lastname,
user_Email,
Username = (Case When user_Username = ‘ ‘ or user_username is Null then ‘None’ else User_Username end),
user_creationdate,
Active = (Case When user_Active = 1 then ‘Yes’ else ‘No’ end),
User_Group = (Select group_name from usergroup a, usergroup_combo b where a.group_id = b.ug_groupID and b.ug_userid = user_id),
TheatreCode = (Case When User_TheaterCode = ‘ ‘ or User_TheaterCode is Null then ‘N/A’ else User_TheaterCode end),
JobTitle = (Case When user_JobTitle = ‘ ‘ or User_Jobtitle is Null then ‘N/A’ else User_Jobtitle end)
FROM [user]
thanks
Sreedhar
One more suggestion,
why don’t you make a Forum of SQL GROUP Pinal..that way it’ll be easier for everybody to post and easier to read
my 2 cents.. :)
thanks
Sreedhar
Hi Sreedhar,
Create a User Defined function which will take the UserID as IN parameter and return you the groups in comma delimited.
Try out, if not let me know. I don’t have SQL, but can give you psuedocode for you.
Regards
Saroop
hi Pinal,
im developing an accounting software db design.. But im not familier wit accounting operations, itz some awat complex for me… if u ve any idea or any reference plz tell me… if u know any website to learn system based accounting operations plz tell me….
thank u,
wit regards
vasan
How can you copy a database and rename it so it can be used within the same instance. E.G. copy sales DB to sales_2007 Tead End DB
Regards
Jon
Hi Pinal,
Could you please give me a link or document where I can get step by step upgradation/migration process from SQL 2000 standard to SQL 2005 standard…
Regards,
Nihar
can u help me in this?
=================
Suppose if i want insert data which has same name but has Unique ID by using range number.
_______________________________________
example if i want insert ‘ItemName’ SIX times |
______________________________________ |
code here is set Auto.
———————-
Insert into Items values
(
‘ItemName’,null,null,null,null
)
thanks and best regards
NOT just SIMPLY i ASK you, but with appreciation of ur kindness.
Hi Pinal,
I would like to know whether there is a way to know
the exact timestamp ( without using the timestamp field)
of a row updation or insertion in a MSSQL table?
Is there any undocumented feature for this?
Thanks
Dileep.
HI Pinal,
I am a starter in database. What would be the best source
start learning SQL Server 2005.
Dearest Pinal,
I am trying to update a field in table A by linking table A and B.
When the date from Table A, falls in between the two date fields on tableB, I want to populate a field in tableA with a value from a field in tableB.
For example the date 9/30/01 exists on tableA. TableB has a date range of 7/30/01 to 11/30/01 and a value of XYZ corresponding to that date range. I want to populate tableA with value XYZ since 9/30/01 from tableA is between those dates on tableB.
Thank You,
Sunita
Hi
I have one Flat File. It contains around 300 rows.
Issue is,
The records are getting merged with the same line.Like i have column in excel sheets look
a b c d
– — — —
1 aa bb cc
2 ww cc yy
3 oo mm ii
…
…
Am getting the results of ,
a b c d
– — — —
1 aa bb cc 2
3 oo mm ii 5
6 ff jj ee
Please give me the solution for the above. why the flat file is not terminated with the single record.
Thanks in advance
while inserting the records from flat file to database thru ssis package.
Hi Pinal,
My question is related to a database. I work as a BA and working on a project related to Oracle(9i) database. Let me give me some brief description about the project.
There is a requiremet to create a new oracle database on production enviorment for XYZ departement of the company. The end user wants database backup on daily basis. Also, there is a requirement that database should have the ability to be restored to a prior version.
First of all, What is difference between database backup and database version ( what do they mean by prior version? oracle version or what?). If they are different, what are the recommended steps/considerations to get both backup and version?
Please help me to find this out.
Regards.
Hi Pinal,
Can I in some way find out a column or a value is hard coded in the sp’s or views.
That is for suppose on in a table there ar3e two columns
x and Y
where x = id (its a primary key)
y = name
now I want to find stored procedure or view where the following thigs are hard coded:
1. Column names (x, Y)
2 .id is hard coded
3. Name is hard coded
Can you pls help me out in this
thanks in advance
praveena
Hi Pinal
It is really nice to read all your articles,from past 8-9 months i am a regular reader of this blog thanks to providing such useful stuff to us.
i have a query to you
i) is there any method to take backup of a perticular table from a database.
Thanks in advance
Jayasimha
@Dhananjay,
Sure I will get that for you.
@Richard
Thank you.
@T.Amit,
You should write SELECT statements using Joins after that insert using SELECT insert.
@Richard Guyon,
Please provide more information.
@Ken,
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
@sripathi,
search your answers here : http://search.pinaldave.com
@Junaid,
I send email most of the time. Many times if question is quick I post it here.
@Venkat,
I will send you email with solution.
@Amit kumar jain,
search your answers here : http://search.pinaldave.com
@Dave Owen,
Good Note.
@sAMEER,
search your answers here : http://search.pinaldave.com
@john Tran,
I will write blog post about this.
@Kamal,
http://blog.sqlauthority.com/2007/05/06/sql-server-udf-function-to-convert-list-to-table/
@johand,
Sure send me email with little more details.
@Thomas,
search your answers here : http://search.pinaldave.com
@amit,
No. you will have to retrieve from logs.
@Travis,
You need to increase the time out value.
@shaik,
You need to add order by column and insert value in order you want it ordered.
@marfi,
When you are installing it will ask you in separate propt.
http://blog.sqlauthority.com/2007/12/29/sql-server-change-password-of-sa-login-using-management-studio/
@Archana,
Implement Index optimization. You can search here : http://search.pinaldave.com
@arihant kothari,
I am not sure if I understood your problem.
@Anjan,
You need to find out which processes are long running and optimize it.
@sabarish,
Your query is correct if your query is primary key.
@hemant,
Network traffic can be issue.
@samir,
I need more information to answer your question.
@kash,
I have previously written about this : You can search here : http://search.pinaldave.com
@Paul,
I support SQL Server 2005 and higher versions.
@Ron,
SELECT * FROM TABLENAME
@zakeer,
I have answered all this answers in this blog. You can search here : http://search.pinaldave.com
@sedor,
View does not support outer apply.
@CRPRAJAN,
You should use trigger.
@Leesha,
I suggest that we should use filesystem to store the images and keep the path to files in database.
@Kia,
Thank you for your words.
@madhu,
Transaction log files stores the information of all the transaction.
@Zinadeen,
You need to create for insert trigger as you said.
@sai krishna,
select statement and self join can not be compared. They are apples and oranges.
@chuene,
You should write shell script for it.
@ajay,
Use Pivot table.
@jaya,
SELECT * FROM TableName T (INDEX = idx1) WHERE Field1 = ‘A’
@saravanan.p,
You can not hide database in SQL Server.
@Oscar,
Thank you very very much.
@ravi,
Search for replication at http://search.pinaldave.com
@Rupesh,
Right click on database and see property.
@pradeep,
You can use TOP(number) for the same purpose.
@Manish,
http://blog.sqlauthority.com/2008/03/05/sql-server-simple-example-of-cursor-sample-cursor-part-2/
Use IDENTITY
@UmaMahesh,
I do not understand your quesiton.
@vinay pandey,
Read this blog regulalary.
@Raja,
SQL Server 2008 supports hierachical options.
@Vego,
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
@Mohamed Hanif,
Use keyword CONVERT.
@Elad,
Use UNION
@sreedhar,
I will look into the forum option.
@Saroop Chand,
thanks for helping readers.
@vasan,
I have writen about accounting issues previously http://search.pinaldave.com
@jon,
You will have to restore the database.
@Nihar,
This blog has talked about this many times.
@Zafar,
Please explain you question little more.
@Dileep,
I do not think it is possible.
@imran,
http://search.pinaldave.com search your questions.
http://job.sqlauthority.com find your job.
http://blog.sqlauthority.com read it regulalary.
@Sunita and @Ashok and @Praveena,
I will send you email with answer to your query.
@Himdeo,
Backup is copy of your data.
Version keeps track of your changes.
@Jayasimha,
Thank you for your words. You can not take single table backup.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi pinal dave,
I have gone thru ur articles and its intersting. i was searching for “how to give parameter for DTS”. i didnt find it in ur posting. If u hve any idea can u please post.
Thanks in advance..
ashim
hi i am very new in sol field so plz help me
Hello Pinal Dave,
I am new with mysql 5.0.16 database.
I have to create the backup of database. But in dump process it give me the error of trigger. So, i want to delete all trigger.
I don’t know any trigger name. So, please guide me.
I want list of all triggers in database. So, which command is used to get list of all triggers.
I am working with CentOS(Linux).
Hello Pinal Dave
Can you give me a Live project to do a practive in SQL DBA.
Thnks
Dev
Hi,
I came across your blog today and found it very interesting.. Recently I have been hired as a DBA and report writer to work with SQL 2005 and Crystal Reports XI. I also have a data warehousing project coming up.. Could you please suggest some good books that’ll help me learn data warehousing concepts or point to it in your website.. if you have some topics that covers reporting as well, that would help me too..
Thanks,
Lakshmi
Could you also give me the code to import an XML file into SQL 2005 or the steps to reach it?
Thanks,
Lakshmi
Hi,
Could u please suggest me the name of the book from where i can practice sql query?
Thanks
Pratik
Hi,
Could you please help me out to built this query.
I am having a table which has the following column:
1. EmpId
2. EmpName
3. Salary
4. DeptName
Q: I want to list all the employee name , deptname & salary and at the end of each dept i want to display the sum of salary of all the employees according to the dept.
Q: I want to display the highest salary of each dept & the empname who is having the highest salary in respective dept.
Thanks
what is the maximum numbers of foreign keys on a table?
what is the maximum numbers of columns can have foreign keys in a table ?
what is the difference between sysusers and syslogins ?
how i can catch the return value from a procedure?
how we can explicitly call triggers?
how do i know from query which sql server service pack is installed on the machine ?
what is the maximum number of joins in a query?
what is the maximum number of inner queries in a query?
I’m look for a script that will find a specific column heading from all tables in my sql 2005 db. I will need to find the ‘acctper’ and ‘acctyr’ This would be helpful in knowing how many tables and columns that I will need to update with the current year and accounting period.
Hello pinal
I have requirement in which i database backup has to take place automatically to a backup server. My backup server is located in a different place and my main server is in some other place. I am aware of backing up data in the same machine by doing creating a maintainance plan for the backup purpose. But how i can do this when my backup server is in a remote place.
Please help me.
Thanks and Regards,
Anand
Dear Pinal,
I need the sql programming using cursor for the following issue.
I have to insert the values of 4 fields.
Sno(Primary key), StudentName, College, SortId.
If i enter the collegename(VKK College) the sortId should be sorted and displayed as 1.
If i enter the same collegname(VKK College) the sortId should be displyed as 2.
Pls send I need this using cursor.
Thanks
vikkas.
Why do you need cursor ? Also why cant you set SortID as Identity column ?
Please elaborate more on your requirement
Hi Pinal,
This is Thakarshi if you remember me then( We worked 2gether on autobiz)..Now I am in london.
I need your help..
I want to generate unique random card nubers at time 50,000 and store in table. So when i need to issue card then i will use this unique numbers. n remove row from this table,
So pls help to how to generate unique random numbers which may between 6 to 20 digits.
Cheers,
Thakarshi
Hi Dave
I recently discovered your site and find it to be very resourceful for someone like me who is new to SQL server. Thanks for the good work.
I would like to use an ENDPOINT as an interface between SQL server database and a client web page. I would also like the web client to access a Procedure in SQL server through the endpoint and pull data that is displayed on the client web page. I would like to do this without having to code objects in programming language like C# or visual basic ….etc. I have created the endpoint and a procedure for this purpose.
Sincerely,
Biggy
CREATE ENDPOINT sql_getEmployee
STATE = STARTED
AS HTTP(
PATH = ‘/sql/AdventureWorks’, AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR) )
FOR SOAP (
WEBMETHOD ‘pr_getEmployee’
(name=’AdventureWorks.dbo.pr_getEmployee’,
schema=STANDARD),
WEBMETHOD ‘GetProductsNoSchema’
(name= ‘AdventureWorks.dbo.pr_getEmployee’,
schema=NONE),
WSDL = DEFAULT,
BATCHES = ENABLED,
DATABASE = ‘AdventureWorks’,
NAMESPACE = ‘http://www.www.mywebsitename/’)
Create Proc pr_getEmployee
As
Select H.EmployeeID, H.Title, YEAR(H.HireDate) as HireYear, H.Gender
From HumanResources.Employee H
Where H.EmployeeID <5
Return
Go
tel me how to retrive row no like 1,2,3,…..
in query itself.
thks in advance
select row_number() over(order by column_name) as row_no from table_name
hai dave
how can i over come database suspect
i found one database instance is showing suspect
when i can not access it.
is there any chance to restore it .
can u please give solution please
thank u
vego reddy
Hello, I am facing this issue:
I have 2 severs one of them is 32 bits with sqlserver 2005 the other one is 64 bits with sqlserver 2005
I made a backup in the fisrt one server (32 bits). I restored the backup in the second one server (64 bits).
The nonprintables characters look diferents.
So, when I run a report made with reporting services and that report query the datas in 64 bits server and the report has parameters which query datas with nonprintables characters the following message is showed.
There is an error in XML document (1, 17960).
hexadecimal value 0x1A, is an invalid character. Line 1, position 17960.
thanks a lot,
Hello Pinal Dave,
Thank you for your web SQLAuthority.com and postings. That’s helped me in great deal…
after following your instruciton of “SQLSer 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects”, the SP, table names, Functions, Triggers are created, but i still have a trouble to load all rows into the tables. I tried the SSIS, import/export, copy database that none of are working. Can you make a suggestion for me to get this work done?
Thank You!
Denise
Hi,
What would correct an error like this!
Thanks
This was a from saved backup that I tried to restore back into my database. They are also all program generated, I did no coding.
MySQL said:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-3.29.2008_15-31-03_thetalke/’ at line 1
,,,,,,,,,,,,,,,,,,,,,,,,,,,
Hi Pinal,
I have a question plz reply, In sql server how could we get the name of columns which are produced by a select statement,Actually i wanna make a utility that generates code for producing temporary table by supplying sp name only. I have seen that sql server query execution plan has property outputlist but how to use that through query.
Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQLServer database into a table in a Oracle Database?
master mind
Hello Sir,
Lets say i have 2 tables or 10 tables.
In all these tables , there is one column same; say orderNo.
Now if the value of the column is changed in any of the tables , how can we populate the changes of that column in all tables?
Regards
Hi Pinal / SSIS users,
In SQL Server 2005, in SSIS, how do I get datestamp on file name while creating/copying/moving file.
Example: I’m able to generate file name but I want to generate it with date timestamp on it.
Thank you in advance.
Shreyas
Dear Pinal Dave,
i have one query,
how to display the table content in column,
For Example,
Emp Table having 3 rows and 2 Column,
select * from Emp
_________________
| Emp-id | Emp-Name |
—————————-
| 1 | John |
—————————-
| 2 | Dave |
—————————-
| 3 | Max |
—————————-
the query result will be in normal, but i want the result to in Column views..
_________________________________________
|Emp-id | 1 | 2 | 3 |
——————————————————————
|Emp-Nme | John | Dave | Max |
——————————————————————
how can we acheive this type result using Sql Query.
Thanks in Advance.
windows_mss
Paul,
use
SELECT * FROM Information_Schema.Columns
Shreyas
Dear Pinal,
I receive below error when firing any DML operation on a specific database.
——————————————————————————–
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.
——————————————————————————-
When I enable it, Update runs but I am getting NULL as a ouput
output
—————————————————————————————————————————————————————————————————————————————————————
NULL
(1 row(s) affected)
This causing application to get extra row??
Please suggest me what to do….
Thnx in advance
Rakesh
Hi Pinal Dave,
i’m developing a project and i use SQL server to store my data.
i made a table which contain at least 70,000 record and this
number may be increased to 200,000 or even half million.
if i want to search for specific record, can i use the primary key of the table to search for this record, you know that
the primary key has the indexer cabability which leads to faster search .?
or i should partition this table first using horizontal or vertical partition ? or what ???
hi pinal i am reading a csv file and i want to bulk insert the data to sql server 2005 table i tried your bulk insert code but it is giving me error of syntax can you please help me..
Can you post the code you ued?
Hai Pinal Dave,
This is srinivas to day i saw ur web site it was very nice and i saw the answers given by you.I had no of queries in sql server can u please guide me for better results.
Actually i am a .Net developer (fresher) write now i am working in web applications.If possible can u give me ur mailid to me
Thank you,
Srini
Greetings Pinal
Wow what a rich source of information you have! I would like to ask the following question
Which one is best practice, more efficient and speedy?
SELECT BinLoc,
InventoryTag
FROM MyTable AS Mt
WHERE (LTRIM(RTRIM(Mt.BINLOC)), 2) NOT IN(‘MC’)
-or-
SELECT BinLoc,
InventoryTag, (LTRIM(RTRIM(Mt.BINLOC)), 2) NOT IN(‘MC’) As FormattedField,
FROM MyTable AS Mt
WHERE FormattedField NOT IN (‘MC’)
Is formatting best done in WHERE clause, SELECT clause or none of the above. What do you suggest.
Thank you!!
Yosias,
NOT IN doesn’t work in Select statement. Also not sure what are you trying to compare ? Please elaborate further
Use second method. It should actually be
SELECT BinLoc,
InventoryTag, (LTRIM(RTRIM(Mt.BINLOC)), 2) As FormattedField,
FROM MyTable AS Mt
WHERE FormattedField NOT IN (‘MC’)
so that the index will be used (if available on that column)
Hello jitendra v patil
Would u elaborate more like what kind of syntax error u are getting
Hi Pinal,
I have inserted around 3 lakhs of records in a table in SQL server2005 from a dat file.
I have to update and insert that table with data from new dat file with increased number of records records, in very less time.
Which is the best method.
Thanks,
Vijay.
Hi Pinal, I am having trouble & thought that you might be the perfect person to ask for input.
I am creating a dynamic new members page & would like to display them acconding to the ‘datecreated’ field in table A.
I have created a view that pulls the info I need from the 3 different tables. I have been able to display them all here http://www.tennisindustry.org/Membership/NewMembers.cfm.
My issue is that I would like to be able to display them with a Month heading for each of the 12 months and cfif out the months that aren’t here yet or that had no members join in that month. I have a static page here, http://www.tennisindustry.org/Membership/NewMembersStaticWeb.cfm as an example of what I want to do which can be found at the bottom of our home page http://www.TennisIndustry.org.
Any thought or direction that you might offer would be greatly appreciated.
Thank you very much…
Jitendra,
Do it using SSIS
Shreyas
Yosias,
good question; but logically thinking formatting in where clause will be faster, if that makes sense reason being for where clause it will compare it just one time but in select clause it has to format it (n-row) times.
However, Pinal can confirm or correct me if I’m wrong.
Shreyas
Develop,
I dont know if there is one statement which will force update on all tables. But I guess updating all tables will have the desired output.
Shreyas
Talker,
I believe that you haven’t coded for restore and assume that you are using sql server manual restore utility for restoration.
Having said that, have you tried restoring using query analyser and restore command? Do you still get same errors?
Shreyas
Hello Pinal,
I have been working with PL/SQL development for last 4 years. I need a small advice. My client is having production SQL 2005 db’s of approximately 2GB’s (back up file size) in 9 sites / 4 different contries. We need to do a data warehousing in the head office for consilidated reports for the Group.
The possibilities for me are
1. Shipping of back up files every day from each sites to head office using an ftp transfer or so. – This is heavy load and will take too long time
2. Shipping of incrimental back up from each sites to head office every day – An Incremental back up will take the logs from last full back up. So in sites there is the possibility of taking the back up of the db any time for certian purpose, so the integrity of teh incremental back up is not reliable.
3. Shipping of refined data (a seperate db contains refined data) from sites every day to head office after ETL process locally – This is what I planned to do but size is getting big each day.
I just want to know, is there any method of tranfering the logs every day automatically through internet to a partcular mailid or ftp address and we can update that in the head office?
Thanks and regards
BineeshThomas
Hi Pinal Dave,
i’m developing a project and i use SQL server to store my data.
i made a table which contain at least 70,000 record and this
number may be increased to 200,000 or even half million.
if i want to search for specific record, can i use the primary key of the table to search for this record, you know that
the primary key has the indexer cabability which leads to faster search .?
or i should partition this table first using horizontal or vertical partition ? or what ???
Hello Shreyas
I didnt get wat u said in thread 571.
Hi Develop,
lets say you have 4 tables: a, b, c and d
All have got orderno. table a is master table whereas b,c and d are detail tables having foreign key to table a(orderno). In this case you update all the detail tables viz. b, c an d with new order no and then update table a with order no
hope that makes sense
Shreyas
New Question.
I’m looking through some code at my new job and I ran across this:
*The code is functional, so just look at the general design
INSERT rdi_Class
SELECT DISTINCT
@Var_A, Var_B, Var_B, NULL, Var_C, NULL, Var_D, NULL,
Var_E, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0, 1, NULL
FROM Table_B
WHERE LTRIM(RTRIM(Var_C))+LTRIM(RTRIM(Var_E))+LTRIM(RTRIM(Var_B))+LTRIM(RTRIM(Var_D)) IN
(SELECT DISTINCT LTRIM(RTRIM(Var_C))+LTRIM(RTRIM(Var_E))+LTRIM(RTRIM(Var_B))+LTRIM(RTRIM(Var_D))
FROM Table_C)
I re-wrote it as:
INSERT Table_A (Var_A, Var_B, Var_B, var_C, Var_D, Var_E, another_var, another_var, another_var, another_var)
SELECT DISTINCT @Var_A, c.Var_B, c.Var_B, c.Var_C, c.Var_D, c.Var_E, 0, 0, 1, NULL
FROM Table_B c
INNER JOIN Table_C cr ON c.Var_C = cr.Var_C
AND c.Var_E = cr.Var_E
AND c.Var_D = cr.Var_D
AND c.Var_B = cr.Var_B
The previous code runs in under 1 second, my new code takes ~1:57… minutes. I put indexes on all of the coloumns I need to make a comparison and it stil ltakes ~50 seconds. All columns are varchars because we’re importing ‘untrusted’ raw data. The tables are ‘generic’ tables created while making a DTS project and each column is varchar(255).
it seems to me that ltrim/rtrim and concatinate your variables runs faster than doing inner joins in this case.
Table_B is about 6k rows and Table_C is about 50k
Query Estimation for the first example spends 17% on hash inner join
My code does 40% on inner join
P.S. being that these tables are truncated and repopulated during our nightly import, we have no indexes. Having a 2min run time for a nightly process is no issue, but if the previous query runs as fast as I can hit F5, then there’s no reason to change it. Even if it doesn’t look ‘propper’
Thanks
Shreyas thanks for your comment. I agree with you that it will be a bigger performance hit if I do the formatting on the SELECT statement rather than the WHERE clause.
Another question for you guys. I need to populate a listview with a result set from a stored proc. I am having trouble with the grouping issue. I have work order table. On each row of the work order table there is a work order number, a part number and part size. I want to group all the same part number + sizes. But the catch is I also want the work order numbers somehow. Can I do that if so how? Could I comma delimited and dump it into another fields? Or do I need to a two step stored procedure for this kind os beast.
Thanks a lot
Hi Dave,
I want to use silent install and was wondering if there was a way to collect the settings of a currently installed server in order to write the INI file you would need with the [options] section. This is for SQL Express, if that matters.
thanks!
Regarding replication, can I have multiple Publishers on the same SQL Server instance? Can I have multiple Distributors on the same SQL server instance? Finally, can I have multiple Subscribers on the same SQL Server instance?
Hello Dave, we have run into an issue and we are at our wits end trying to figure it out. Whenever we rebuild indexes, our system is very sluggish after completion of the rebuild. In order for us to our performance back, we have to stop and start the sql service. Is this normally a thing that is required to do after indexes are rebuilt? I don’t recall us having to do this with sql 2000.
Our system is a 4 dual core processor, 24 GB of ram, running sql server 2005.
Any comments would be appreciated
Dear Pinal,
Is there a way out to implement Merge replication(SQL) by setting the server clocks to GMT where there are more than one server to sync and each server fall in diff time zones?? if so, will there be any conflicts & there be a data loss?? kindly help to over come this issue. how to implement GMT in Data Sync
Kindly help!!
Rangarajan K
Dear Pinal,
is there any way to zip 10 BD database backup file.since i want to move to another server
regards,
ravi
Greetings
I have a stored procedure where I want to use a good boolean algebra based on a certain parameter. My parameter might come in NULL or varchar(50) of concatenated values. How do I got about doing this in the WHERE clause?
IF @parm IS NULL
Table.WorkOrderNum = COALESCE(@parm, Table.WorkOrderNum )
ELSE
Table.WorkOrderNum IN (@parm) meaning
Table.WorkOrderNum IN (’1′,’2′,’3′)
Is this stretching or do i have a good case for optimization using a good boolean algebra
Thank you
@Ravi
Microsoft suggest not to compress backup’s not eveyr time but some time it may be curropted then it will be hard to get it back and Microsoft doesn’t provide any solution for it.
You can use litespeed to compress your back up when you actual take backup.
I know this is not the answer you are lookingat may be this will help.
how to solve this problem?
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 paul
5 aish
6 raj
7 mani
8 madu
9 cotter
now i deleted the 4′th row
4 paul
ok
now how can i display the remaining names as given below
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 aish
5 raj
6 mani
7 madu
8 cotter
can u help me sir?
saranpselvam@gmail.com
Saravanan,
When you say, ID is an identity column, you cant adjust 4th record to takeup5th and 5th to 6th and so on… but can be done in other way – a length process…
1. delete from table_name where ID = 4
go
2. select name into #temp from table_name
go
3. truncate table table_name
go
4. insert into table_name(name)
select name from #temp
He just needs a serila no for the record which can be done using
select row_number() over (order by id) as sno,name from table where id4
Dear Pinal,
As well as they are possible to be backup and to restore DBs in SQL, can also be made with the DTSs and the JOBSs?
regards,
Rangel
Pinal,
I would like to identify tables in databases which are rarely used.
After looking at some sys tables, I found that sysobjects table has a column as refdate.
But No information found about it.
Do you have solution for this. I would liek to write a script which lists the user tables which are and their last reference date or last query date?
Thanks,
Shruti
respected sir ,
i delete auto generate column data but after inserting new data thats count next auto generate val .please tell me
how we start auto generation from privious value.
Hi Pinal,
i am working on a banking project for a financial bank.it deals with end of day and start of day…i have created some indexes in it. i m getting some error while end of day of any branch.
Error messages:
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. shud i try to defragment it, how can i defragment it.plz give me a long term solution bcoz branches are going to increase like anything… do u have any contact no. its urgent
Hello,
i need to make a sql script thru vb which can automatically do the mob sync on its own. no need to do for particular branches.
Hi pinal,
Through stored Procedure can i get Job History in sql server of all the failed jobs and the successfull jobs including the job steps.
By running the sp’s i should all the informations.
I tried by using system stored procedures but could’nt get the exact output.
Pls help me by giving the sp to find the job details as soon as possible.
Waiting for ur kind reply.
Regards,
Anjan
hi pinal
what is main diffrence between sql server 2000-2005
can you give me good store procedure examples
Dear Pinal Dave,
You are really amazing!
Thank you for such a wonderful site
Sangeeth
how to solve this problem?
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 paul
5 aish
6 raj
7 mani
8 madu
9 cotter
now i deleted the 4′th row
4 paul
ok
now how can i display the remaining names as given below
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 aish
5 raj
6 mani
7 madu
8 cotter
can u help me sir?
saranpselvam@gmail.com
this site is really wonderful. can you give me the architectural difference of sql 2000 and sql 2005
Hi pinal,
I want to create a batch file to delete the old backup files based on their date.
I want to delete old backup files whose date is greater than 2 days.
Pls if any body know help me,its urgent.
Regards,
Anjan
Hi,
I got error while creating job in sql server 2005 sp2.
Unable to cast object of type ‘Microsoft.SqlServer.Management.Smo.SimpleObjectKey’ to type ‘Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey’. (Microsoft.SqlServer.Smo)
Any suggetions & solutions.
Thanks
JOHN
Hi,
When i execute this procedure I am getting this error often.
When I drop procedure and recreate it gone.
But later it came.
The error is
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Please help me to rectify it and explain me why is occurs.
CREATE PROCEDURE [dbo].[GetAssociates]
— Add the parameters for the stored procedure here
(
@CompanyID INT
)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
SELECT CM.*, CRM.* FROM CompanyRegistrationMaster CRM
JOIN CountryMst CM ON CM.Country = CRM.Country
WHERE Master = @CompanyID
END
exec [GetAssociates] 50
Thanks.
Hi Pinal,
How can i extract a particular row (for eg. the 4th row) from the table having 50 rows?
Pls. help.
Thanks and regards
Kedar
how to solve this problem?
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 paul
5 aish
6 raj
7 mani
8 madu
9 cotter
now i deleted the 4′th row
4 paul
ok
now how can i display the remaining names as given below
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 aish
5 raj
6 mani
7 madu
8 cotter
any body can u help me sir?
saranpselvam@gmail.com
Hi Pinal,
Great site, you are very informative.
I have a question, is it possible to update part of a record? For example, many of our employees just transfered to a different site, and we need to update the extension on their phone numbers.
The last 4 digits are staying the same.
For example:
I need to update 200 records from 123-456-XXXX to
321-654-XXXX. Can I do this as a mass update?
Thanks,
Shannon
HI Pinal,
Great posts..
Suppose i write some update statement on some table and forgot to write WHERE Statement so what happen it will affect to all records. Can i rollback it??? i am using SQL SERVER 2005 and i am doing this testing using management studio… means nothing in transaction… you have any idea except database backup :)
Regards,
Kiran
hi dave,
how can i use sql_variant data type
i want my variable for zipcode like 000 000
having 6 digits with space
can i define my variable like above using sql_variant datatype
please send me reply
thank you
vego reddy
hi
i have some doubts on i want to check two tables
Hello Pinal
Thanks for all the efforts for putting out a awesome blog. I know really requires big efforts.
Pinal, I was looking for the difference between INNER JOIN and Where Clause. Eg. If I have the following query
SELECT
A.C1, A.C2, B.C4, B.C2
FROM
A, B
WHERE
A.X = B.Y
and
SELECT
A.C1, A.C2, B.C4, B.C2
FROM
A INNER JOIN B ON A.X = B.Y
Can u pls let me know if there is any performance issue with these queries.
I understand that Format 2 is more professional. But I was really looking the efficiency and performance with many tables participating.
Thanks
Hi Pinal,
In our project we need to insert data from a file to a table and also extract data from table to a file. I do not have sys admin permission . Could you help me with an example of BCP utility to both insert the file in the sql server table and to extract the data also
Thanks,
Prasanna
Hey i m using SQL Server 2005, i want to insert multiple rows using single insert statement, but it’s not working. If it is possible, just send the syntax to me.
Hi dear,
I have a table from which i m gona delete the record using delete from tablename where id=xyz.
It works fine for me. Suppose the row which em gona delete has child in its child table then sql server will throw an exception. I m catching this exception and showing a msg to the user that its child record exists so you are not allowed to delete this record.
Now instead of actual deleting the record, ive put another column in the DB which is “is_active”. When I delete the record i Set is_active = 0 (is_active is a boolean column).
In this case I want a generic solution that before deleting (which is now basically an Update statement) how could I know that the row which em gona delete has child (in any table) as I want this in several tables..
Thanx in advance
Hi Pinal,
How to take backup of only tables and restore it in other server? Both the server is not connected.
Seeking Support!
Ashish
Hey, I wanted to find an expert to help me with this…it doesn’t seem out of the ordinary, but it doesn’t work like I need it to. I have SQL server 8.0.194
I have a SP that may or may not return a GUID Session ID via an output variable. so it reurns NULL or a nvarchar string
I want to take that string into another SP to update the row it is in.
So in SP1
I have
:
:
DECLARE @SessionID nvarchar(50)
Exec GetUsersSession @UserName, @IPAddress, @SessionID OUTPUT
EXEC EndSession @SessionID
:
:
EndSession has code to check for NULL or not and doesn’t process if it is null……but even when @SessionID does have a value the endsession still thinks it is null.
I think my problem is that this is dynamic and I am not handling it corectly…but this should work.
let me know if you need more to guide me in the right direction.
Thanks
Ron
Hi pinal,
i hv two tables. both the tables contains common colunmn named month. i want to copy columns of one table into another where months of both tables should match.
t1 month jan feb march april
col2 1 1 1
col3
t2 month jan feb march
col 2 2 2
result should be
t1 month jan feb march april
col2 1 1 1 1
col3 2 2 2
Hi Pinal,
Hi all,
I get this error while i run my SQL job .
spExtractTable Failed @ Line 1,
The OLE DB provider “MSDASQL” for linked server
“QOS” supplied inconsistent metadata for a column.
The column “InteractionID” (compile-time ordinal 1)
of object “”xxxx”.”dbo”.”vw_Scorecard”"
was reported to have a “DBCOLUMNFLAGS_ISNULLABLE”
of 0 at compile time and 32 at run time.
spExtractTable — this extracts data from the source view and drop the destination table and recreates a replica of the view in the destination database(which is my repository).Source database is sql server 2000. and destination server is sql server 2005.
I had no problem doing this job run for past 1 month.Infact till last friday it ran fine. i had created a linked server using OLE DB.
But now when i run the job its breaks with this error.
Extra Inputs: I tried my linked server connection test. it says connection successful.
InteractionId is PK on destination database.
Could You please guide my thoughts to fix this?
My work is getting affected big time because of this.
Any kind of help will be greatly appreciated.
Thanks in advance
Leah
Hi
this batch code is not working for me to delete old files.
forfiles /P E:\test\ /S /D -5 /C “cmd /C del /q @path”
Wat to do pls help me.
its urgent
I tried to load a very large txt file(144GB) to sql 2000 database.
I received this error after first 2 hrs of load.
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider Step Error Description:Error creating file mapping view: Not enough storage is available to process this command.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
We have HP EVA SAN.
Is this because I/O issues? How do we fixed this problem?
Thanks,
Kevin
on April 29, 2008 at 10:39 am3 Kevin
Your comment is awaiting moderation.
By the way, It’s sql server 2000.
Hi
I’m having a table Employee. Fields are Emp_Id, Emp_Name, Place. In this Emp_Id is Primary and Identity Field. Here i want to remove only Identity Property for Emp_Id thorugh Sql Query not using of Sql Server Management Studio.
Regards
Sachin
Hi pinal i am new to sql packages 2000
will u plz suggest me some help in this..
hi pinal for what purpose we are using GO at the end of
an stored porcedure and T-SQL
Hi Pinal,
I read your articles, those are very useful.
I want to write sql script to migrate data from sql server 2000 database to server 2005 database using sql script.
How to take backup of only tables and restore it in other server? Both the server is not connected.
and information about stored procedures and joins and some examples.
Please guide for same…
Thanks a lot…..
Pankaj S.Deshpande
Hi Pankaj,
For migration use migration update advisor.
For more info. http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
, http://msdn2.microsoft.com/en-us/library/ms144267.aspx
Still question arise in your mind after understanding the migration process…, please elaborate your requirement “why and how you would like to do” I will try to help.
Backup Table only I don’t understand but if you mean to say you don’t want to copy whole database only few tables. The source And destination server are not interconnected;
Try this:
Create a new database on the source server run INSERT INTO to copy all your tables to that database and simply backup the database and restore to destination.
Also read file and file group backup for your future reference.
BR
Praveen Barath
Hi Prajin,
Sol 1: SQL 2000 Packages: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx
Sol 2:
“GO” use to marks the end of a batch in Query &
signals the end of a stored procedure definition in that batch
Praveen Barath
hi pinal
I want to select the records from a table which are updated or added in the last one month (table doesn’t have any datetime field and I can’t change the structure). Is it possible to write a sql query for that?
Hi Pinal,
I am a regular follower of your article. As iam very new to this field, iam designing a project. Just want to have some suggestions on how to go about designing a stored procedure.
I want a stored procedure which takes date as a parameter.
from that date i want to read the day…eg monday then it will execute the monday query, if they enter 30/04/2008 since its falls on friday then it sud execute the friday script.
scriot will be somethg like this
CASE mon
select mona,monb,c……
CASE
select tuea,tueb,…………
just let me know it this possible…
reason behind that database field is named that wat datz y….and i cant waste my time redesigning the db.
sugesstions please……..
Hi
Remove a default from a column using sp_unbindefault.
EXEC sp_unbindefault ‘Emp_Salary.DA’
while running the above query i’m getting error
Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 83
Cannot unbind from ‘Emp_Salary.DA’. Use ALTER TABLE DROP CONSTRAINT.
why i’m getting like this
Hi Pinal, I am new to SQL and would like to find out how to automate and/or schedule the DBCC CHECKDB to run. Or, is this the same statement that runs when you set up a DB maintenance plan?
Any reply is greatly appreciated.
Regards.
Hi Pinal,
When I try exec sp_change_users_login ‘Report’;
I can see list of orphaned users. then I try
EXEC sp_change_users_login ‘Auto_Fix’, ‘nameofuser’, NULL, NULL
I get an Error saying,
An invalid parameter or option was specified for procedure ’sys.sp_change_users_login’.
I am trying to run this 2 fix my users on sql2005 instance. This db is currently migrated from another 2k5 instance.
But interesting thing is when i try this,
exec master..sp_change_users_login ‘Report’;
it doesnt return any results.
Please help me out….
Tnx in Adv
Hi hitesh,
Yes, It is same as into your Maintenance plan else you can also schedule a job for that.
BR
Praveen Barath
Hi Pinal, ur blog is of gr8 help for us … Thanks a lot 4 it!
Is there a single query by which i could replace all null values with my own string ….now … null includes (is NULL, ‘NULL’, even blanks) … which is for a single table !
That would be of gr8 help!
Good afternoon,
I was hoping you could be of some help. I am looking for a series of questions and answers to use in screening a SQL Server DBA on their experience and skills in SSRS. I am a technical recruiter with unfortunately limited technical skills in this area and have recently had a consultant greatly overstate their skills in SSRS, resulting in a very bad interview and an extremely unhappy client.
The job is a SQL Server DBA 2000/2005 with expert level SSRS skills.
Any insight would be greatly appreciated.
Dear Sir ,
I have one problem .I have one table named Employee with ename,dob,gender fields.
The valeus male and female are saved in the coloum gender
now i wants to write a single query which will alter the value of field such that all value having gender as female become male and vice versa
plz help me as i have to write a lot of code in visual basic
Thanks and regards
Yogesh Tyagi
Hi
Can you point me in the right direction.
I need to create a daily proceedure that imports a .csv file from a html link and inserts it in to a mssql table. Since I have never coded a script like this your help would be greatly appreciated.
Thank you
Hello Pinal,
I tried
sp_CONFIGURE ‘nested_triggers’,0
GO
RECONFIGURE
GO
and it helped me, but I am a bit confused. I have two versions of the database. Both show SELECT TRIGGER_NESTLEVEL() as = to 0 (on development before and after the sp_configure).
After I run the above on Development the Config value and run_value are both = 0. Production has those values as = 1.
The funny thing is in production I do not get the error “Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).” Which I do get in development when the configuration is the same, both values set to 1.
Any ideas on this?
Thank you,
Mikey
sir,
I developed a .net windows application.And i created a set up for this application.I want to install this application in client pc.I have a doubt HOW TO ADD DATABSE TO THIS SETUP FILE? AND HOW TO RESTORE THIS DATABASE IN CLIENT SIDE PC?
sir i need a n immedite reply.
Thanks in advance
Hello All,
I m trying to update a table whose col name will be read from another table.
For e.g. Table1 gives the result:
‘emp1′, 1, ‘John’
‘emp2′, 2, ‘Mike’
Now in the second table, i need to update the table with Col name = ‘Emp1′ and then from the second row (above), I need to update Col name= ‘Emp2′
I need to write one Update Statement which will handle all the cases. I tried
Update Table2 set @VariableName = …….
but didnt work…
How can i do that ?
Hi Pinal. I want list of all the tables used in a stored procedure. can u help me in this regards?
Hi Pinal:
I am having some problem with sql server job agent.
my problem is very similar to the one listed below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3090812&SiteID=1
I have scheduled a whole bunch of reporting services jobs using the sql server 2005 job agent and it’s worked fine that way for about 1 year now, but lately, things started to act up.
The job schedule on job activiy monitor doesn’t match with what’s on the sql server jobs agent.
so jobs that are scheduled to run quarterly by the sql server agent schedule either won’t run when it’s supposed to or it runs when it’s not supposed to.
Have you experienced anything like this before?
Thanks-
Iman
hey pin i like reading your articles even if its above my head , ive looked everywhere i can , was wondering if you could help me with this, Hypothetical situation. I need a database with 10 users. The back-end will be some version of SQL Server. How much will it cost for me to have legitimate and legal versions, licenses, calls or whatever to run 1 SQL backend that gets called from 10 Access Front end databases.
This can conceivably be accomplished with SQL server express which is either free or cheaper. Otherwise i need the most basic of the SQL Server programs with a certain number of licenses or calls
Hi Pinal:
I have a doubt regarding the identity column
is it possible to reset the identity column with out truncating the table
please help…
Hi Pinal,
We are having only one row in a file when we do bulk insert ,
the row was not inserted into the table. But when i add one more row it is getting inserted. I tested by pressing an enter at the end of the line(having only one line in file). It worked fine.
Could you tell us How can we solve this?
Thanks,
Prasanna
Hi Pinal,
Really awesome work man..
I have a lil query concerning the Book “Learning SQL on SQL Server 2005″…O’reilly
I’m unable to figure out a solution for Chapter 5, Ex 8.
Am still tryin it…. do let me know if you’ve figured out how it is to be done. Would enrich my knowledge greatly…..
Thanks,
Kunal.
hi Pinal
I have a doubt regarding Trigger ..Do the trigger fire when we update the database in the enterprise manager..
Thanks
Prajin
How to create a “BEFORE” UPDATE Trigger in SQL Server 2000
hi guys,
I want to write a stored procedure to track schema changes and reflect the changes to same db in other servers.
so wat to do for this.Pls help.
Using SQL 2000 OLAP processing have been a true and interesting delima. Have you found that the size of the transaction log affect the processing time of cubes/dimesions?
HI pinaldave,
I have requirment to insert two laks records at one time. how can i do this in sqlserver 2k. can u help me in this issue?. for the table which i am going to inserting bulk records, that table does not contain any primary key. so how can i insert the bulk records?. it’s an urgent issue.
thanks in advance.
nagesh
how to solve this problem?
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 paul
5 aish
6 raj
7 mani
8 madu
9 cotter
now i deleted the 4′th row
4 paul
ok
now how can i display the remaining names as given below
ID(identity column) name
——————————————
1 david
2 john
3 ram
4 aish
5 raj
6 mani
7 madu
8 cotter
how the identity column automatically comes to above method any body can u help me sir?
saranpselvam@gmail.com
Hi Pinal,
I have ERD diagram
Is there any simple way to verify database design is perfect using ERD diagram ?
thanks
Ranjana
hi sir
i am new to sql.i want put identity property to a column bt that column datatype is varchar.and tht column must starts with P001.hw i ll do pls help me sir
thank u sir
Can you please tell me how to obtain a dump of all the roles that are created from SQL Server 2000
Hi Pinal,
First of all, love your articles; helped me a lot in many cases. Anyways, I ran into this problem which is described below. Hope you can provide some insight in this regard.
Scenario:
I have an item table (tbl_item) in my database (db_kam). The table only holds current items. That is the items that have been removed are no longer stored in this table. The database is automatically backed-up once in every 24 hours, and the transaction log every 6 hours.
I understand I should have kept an archive table of some sort to keep the removed items. Unfortunately, I didn’t and now I need to see the history of all items.
Is there any script or tool that can read from database .BAK or .TRN files, and provide me all distinct data for tbl_item table?
Look forward to your answer, would really appreciate your assistance.
hi ,
I want to drop the Table if it is exists in the database.
i am using the
‘drop table if exists [1_ResultData]‘ Query but it is not working.
It gives error
‘Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘if’.
Thanks
Pragnesh
Hi Pinal,
Recently, I’ve a problem on create and run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server. The solution belows works perfect for us, I think it is good to share.
http://support.microsoft.com/kb/906954
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
Thanks,
Roy
Hi, Pinal!
I’d appreciate if you canm help me here. I have a table that contains these columns: CustName, Col2, Col3, OrderDate.
All but OrderDate are duplicates because OrderDate is unique, i.e. CustName placed say 5 order within 5 months. I’m interested only record with this CustName with latest OrderDate. How can I accomplish this? It’s SQL Serevr 2000.
Thanks,
Albert.
Hi
I’m having a table Employee. Fields are Emp_Id, Emp_Name, Place. In this Emp_Id is Primary and Identity Field. Here i want to remove only Identity Property for Emp_Id through T-Sql not using of Sql Server Management Studio.
Regards
Sachin
Pinal,
Really great blog link, I enjoy reading content many times.
I am currently working on conversion of product from SQL 2000 to SQL 2005 64 bit version.
We have many scripts where excel or access files are dumped into table.
As I understand SQL 2005 (64 bit version) does not come with these Jet OLEDB drivers.
I found following solutions to get this done
1. SSIS -Import
2. BCP utility
3. Third Party tools
4. Install SQL Express (32 bit) and create a SP to read data from excel and load into table. Then create linke dservers and get the data moved to SQL 2005.
I would like to know what is the bset way to handle it and does option 4 is reliable?
Thanks,
Shruti
Pinal,
I have aske dthis question few days back, I understand you can not answer every question. But please see if you can help.
I would like to identify tables in databases which are rarely used.
After looking at some sys tables, I found that sysobjects table has a column as refdate.But No information found about it.
Do you have solution for this? I would like to write a script which lists the user tables and their last reference date or last query date? This result will be very useful in our environment.
Thanks,
Shruti
Pinal,
Can you let me know the list of sys tables and the stored procedures I need to keep track of to maintain SQL Server 2005?
I basically want this in an interview perspective..!!!
Thanks a ton..!!!
Mr. Pinal,
I read your articals and happy with your words. I am working as a senior software engineer in India. We have an requirement to insert/update records on different Database on different SQL Server 2000 using triggers. We did insert/update on different database on the same SQL Server 2000 using Triggers.
But we tried on different server by using
IF( NOT EXISTS ( SELECT [SRVID] FROM sysservers WHERE [srvName] = ‘ServerName’ ) )
BEGIN
EXEC master.dbo.sp_addlinkedserver ‘Servername’
END
after execute the select query for example
select * from Servername.lms.dbo.Trainees
We got the error message
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user ‘sa’. Reason: Not associated with a trusted SQL Server connection.
Could you please guide us to solve this issue. Thanks a lot.
Thanks,
Vivekanandan
Hi Pinal,
I’ve an SP. In that i’m building an SQL query with lot of criteria and based on lot of parameters. So here the query is very big. I used a variable say @SQLString nvarchar(max). Actually 2gb we can store thru this. But if i used only one variable like this to build a query, its not building the exact whole query. Some where its cutting and remaining query conditions are binding into the variable. But we can use number of variable to store sql query with lot pieces and finally join together and use it. its working. but i think this is not a correct method.
I would like to know why its not storing more lengthy query in a variable with max type. whats the issue and how to solve this one ?.
Thanks
Abhijeet
I needed a script that could help convert the following table infor
FieldName: FieldA, FieldB, FieldC
Value1: 65, ABC, NBN
Value2: 66, CDE, BBB
From the above data, I want to produce a spreasheet that should look like
Col 1 Col2
65 , ABC
65,NBN
66,CDE
66,BBB
thanks for helping
Hi Pinal,
Transaction (Process ID 108) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am getting this error how to over come this.
I have run the SQL profiler also. Application is already in the production please help
thanks in advance
Arun
My group recently migrated to SQL 2005 so we are learning as we go. I’m currently learning Coldfusion and have to provide a CF interface for a SSIS Package. No one in my area knows how to do it. The way I want it to work is the suer would click a link on the menu that would take them to the page that causes the package to run behind the scenes they would only see a message telling them the package is running and if possible that it’s complete.
Any help would be appreciated.
Thanks
J.T.
Hi
I have the following query with a single parameter. But some times I need to ignore the where clause and bring the all the organization list if the @clientidis not supplied or null. May I know how to implement this in a query.
select * from tblorganization where id = @clientid
Hi,
I am working on a big SQL database which is now 106 GB. I have trouble to extract the data from this database because its performance is too bad.
Please advise.
Regards,
Imran
Hi. I am trying to set up Replication between database(i.e. Pubs) in MS SQL 2000 personal edition on Windows 2000 server w/Active Directory built and database(i.e. Create new database called Pubs_copy) in MS SQL 2000 personal edition on Windows XP pro.
Publisher/Distributor is DCH\DHSERVER and subscriber is SERVER1.
I am testing locally with local static IP given to each computer. When I set up in Windows 2000 server computer as publisher/distributor and Windows XP pro computer as subscriber, I keep getting errors(Login failed for user ‘DCH\DHSERVER$’. and The process could not connect to Subscriber ‘SERVER1′.) so it’s not replicating to subscriber(Windows XP Pro computer) from Publisher/Distribuor(Windows 2000 server computer). What can I do run this setting right?
When I replicate within Windows 2000 server computer(Publisher/Distributor/Subscriber), it works fine(from Pubs to Pubs_copy within the same MS SQL 2000 database on Windows 2000 server computer)
Thank you.
Dear Pinal,
I have one one XLS File i need to import data in 2 diffrent SQL SERVER 2005. For e.g One Master Table X and Child Table Y. X Table columns mastertabid, col1,col2,col3, etc etc..master table columns are fix.
and Child table columns childtabid,mastertabid,childcol1,childcol2,childcol3,childcol4, etc. etc.. Child table columns are not fix it may be 5,10,15 or 20…
Now, at the time of importing XLS file i need to insert data in both table. most importing thing is i need to add data in child table with reference of mastertabid..i have to add this column value.
from my business logic idea i can do it but it makes my page slow…and i dont want to do that…
how can i do that, plz. guide me….
Thanks
Chirag
Hi Pinal,
I have an interesting query request using Case When in sql server 2000,
I am converting a field values with tokens like following
first row 123|xyz|24-08-2008|
second row 234|abc|08-03-2008|
to another table with fields rowId , colId, value)
with values
1 1 123
1 2 xyz
1 3 24-08-2008
2 1 234
2 2 abc
2 3 08-03-2008
now i want to convert the rows into columns
like
123 xyz 24-08-2008
234 abc 08-03-2008
for this i am trying to use the following query
select rowId, case colId when 1 then Max(token) end , case colId when 2 then max(token) end,
case colId when 3 then max(token) end
from test_token group by rowId
but its asking to have an agg. function for colId field also,
This is where i am struct , could you help me with his
Thank you
Subbu
Hi,
I am not able to truncate the log file in sql server 2005 by using the following steps.
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
I am getting the error
“The log was not rebuilt because there is more than one log file”,
Manually by running dbcc shrinkfile has not affect.
Pls help me to truncate the log in sql server 2005 as it is very urgent.
Hi Pinal Dave,
I love the way you have built your website.
I would like also to create my own blog.
Could you let me know which technologies did you use?
Thanks.
Nicolas,
Hi Pinal,
Your website is very good. I found many solution from you website.I have one problem in MS SQL 2005.I would like to add Job in MS SQL dynamically using SP.
Regards
shailesh Kavathiya
hi pinal,
My sql statement for MS Access is as Below.
SELECT IIf([Water Depth (M)]<500,”=500″) AS DEPTH, Count([All Platform with Min dist city].[Water Depth (M)]) AS [CountOfWater Depth (M)], AS [CountOfField Name], Count([All Platform with Min dist city].[Platform Name]) AS [CountOfPlatform Name]
FROM [All Platform with Min dist city]
GROUP BY IIf([Water Depth (M)]<500,”=500″);
which i am using to get the grouped result.
How to use the more Condition(i.e. If else if…) in sql.
Because this function is not working in SQL Server 2005.
Any idea how to implement the this thing in SQL server?
Regards,
Pragnesh
How many concurrent connections to a SQL Server 2005 running on a dual processor machine are too much?
Is 100 concurrent transactions too much?
The following is a sql statement generated by an MS Access query which is connected to a SQL Server 2005 view. In Access, it generates a statistical report. It sums on a field, based or whether the field value is ‘D’ or ‘E’. I have changed the obvious differences from Access to SQL, such as replacing UCase$ with UPPER and IIf with IF, but it still doesn’t work. Do you have any suggestions as o how I can make this work?
SELECT dbo_vwDisplayUserList.DEPT_DESC, Sum(IIf(UCase$([Essential_Code])=”D”,1,0)) AS Department_Essential, Sum(IIf(UCase$([Essential_Code])=”E”,1,0)) AS EOC_Essential, Int([Department_Essential]/[Total_Employees]*100) AS [%Department_Essential], 100-Int([Department_Essential]/[Total_Employees]*100) AS [%EOC_Essential]
FROM dbo_vwDisplayUserList
GROUP BY dbo_vwDisplayUserList.DEPT_DESC
ORDER BY dbo_vwDisplayUserList.DEPT_DESC
Hi Pinal Dave,
One of my friend has an issue with 64 bit server, actually what happened was they have upgraded the server from 32 bit to 64 bit and when ever they runs the scrits on 64 bit server data was not refresing unless they manully do it and which is not happening on 32 bit server……..
I tried to investigate the issue but no clue :(………
what is the reason for this??
also whats the script to refresh the database with the t sql command.
Thanks in advance,
siva.
Transaction log is taking more space after truncating also.
The database is in Full recovery mode as we need both data and log backup for security purpose.
If i change to simple recovery mode i can get only data backup not the log backup in case of any database corrupt or deletion and we can’t do point in time restore in case of db corrupt.
So pls tell me how to solve this transaction log growth issue.
this is causing space issue a lot.
Pls any one help me.
Hi Pinal ,
I want to store a Log for data modified in each column of every table by old data , new data ,user and change date .I am planning to write Triggers in each table for Insert , Delete and Update and get all the columns of a table in to a cursor and check “If Update(@colname)” then insert the OLD value and New value into a Table.But here @colname variable can not be used in UPDATE() function.Is there any way to use variables in UPDATE() and SELECT statement?
thanks in advance
Nisanth
Love your blog.
I inherited a database that has tables that I suspect aren’t being used. Is there something on the server side I can do to verify this? My last resort is to change the tables names and see who yells!
Thanks
Hi Pinal,
Thank you for all the great articles. I had a probelm in SQL Server, which I found a solution to in one of your articles:
SQL SERVER – Cannot resolve collation conflict for equal to operation
I followed this recommendationL:
To resolve the collation conflict add following keywords around “=” operator.
SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT
and it worked.
BUT … I was thinking if there is a way to change the collation of the Database since it is taking the default of the server, which is not correct.
The Database was duplicated from Production to Development Server, and on Development, we are unable to perform updates on a table in this Database by using Sub-Query over a Linked Server from MS Access Database, due the Collation Conflict Problem. On the production server, it works becuase the default server collation is correct.
I tried to change the Collation using the Database/Properties/Options, but got many errors due to some dependent objects which must be deleted, and I am now lost.
I searched the net, and most solutions are asking us to rebuild the Master Database, or re-install the SQL Server using the correct default collation.
I really do not want to re-install or do huge changes !! I am looking for a simple solution.
Please help me ? What I can do ?
Tarek.
Hello,
I need to compare performance of 2 SQL server machines at OS/SQL level. Are there some simple example SQL scripts to do it?
Thanks for any suggestion
Petr
Hi Pinal,
your comments are really helpful. I have one questions about SQL Permission for following scenario:
Login A is a member of one NT group. That NT group login has some permissions on diff. databases. Login A is also exist independently in logins. and it has some permissions. Now as login A has permission through group and through its own login. In this case which permission will be applied ? How SQL Server decides that and in which order it will be applied ?
hi, Pinal
i have one query in SSRS (Sql Server Reporting Services) how can i post my question on your site?
Hi Dav,
Your site is so helpfull. i appreciate you for such work.
I am a fesher. would you plz mail me the interview question for the fresher since a couple of interview are there in the next week. thanks so much.
Hi Pinal,
I am facing a typical issue from last week in SQL Server 2000. During an insertion the error “Invalid length parameter passed to the substring function.” appears for a particular combination of two fields.
Though this message surfaces up yet insertion is done in the
table. The combination of values for which the warning pops up is ‘EDI’ , 137. For other similar and permitted values like
‘WEB’,137 / ‘FTP’,137 no problems or warning msg appears.
Moreover for all combinations like ‘EDI’,136/ ‘EDI’ , 299′ etc
insertion is completed without any msg.
Only ‘EDI’ with 137 produces this msg still data is inserted.
Isn’t it something like not explainable.
I have checked the code statement that is responsible for it and is given here:
substring(MF_FILE_NAM,1,len(MF_FILE_NAM)-5) = @MF_FILE_NAM) , the col MF_FILE_NAM and the variable
@MF_FILE_NAM both are of varchar(50).
Please provide any way to get rid of this problem.
Thank You Pinal,
Shankar
Hi Pinal,
I am a novice with SQL, and just started learning.
I want to retrieve unique records for Vendornames, city and state, however no two unique vendornames must be in the same city and state.
How can I do this? I used distinct and self join as they are in the same table but I am still getting duplicate city and state records.
Thanks,
Sheela
Hi Pinal,
I am a complete novice with SQL Server but I find it fascinating. I find it so interesting that in fact i have chosen to do a dissertation based around it :)
For my dissertation I aim to compare the effectiveness of a range of database comparison tools that are available on the net.
In order for me to compare these tools effectively I need to have at very least two databases (ideally there will be many more databases)
My idea is to use the AdventureWorks database that came with SQL Server 2005 for one database and make a slight modification to it to make another database.
My problem begins in the ‘copying’ of the database. I can copy the database and tables etc but none of the data.
I need help with:
1) Is it possible to simply copy a database and it’s data onto the same server? (I have really really struggled with this for a few weeks)
2) Can you suggest a better, more effective way for me to get multiple similar databases onto the same server?
Keep in mind, that while I am learning I still feel a complete novice with SQL Server. I am using 2005 version by the way.
Hi Pinal
Do u have some Atrticles on SSIS. I want to know how i can Pass a All Text file in a Folder in SSIS.
Thanks in Adv.
Regards
Tarun
Hi Pinal
Got some good stuffs from your forum. Thanks !
I have one question, how to upload collection of .csv files into SQL server database using the stored procedure. I am able to upload one file.
But i couldn’t able to upload group of files. Like how to retrieve the file count from folder and how to upload all one by one into SQL server database.
Thanks in advance
Hi Pinal,
I am Abdul Malik Klari Indian nationality working in Qatar Doha
Your Forum is good and helps me more
Here I have a problem regarding the collation.
I am using SQL 2000.
I have a database with collation Arabic_CI_AS and my front end is developed in VB6 and the data will be in English & Arabic letters. In this database Arabic data are stored normally and properly.
Recently I created another datbase in the same server with collation Arabic_CI_AS. But arabic letters are not stored correclty in this database, it become only question marks (???????????) and english letters are OK. The data comes to this database from the same fron-end program for the first database.
How can I fix this problem. I hope you may help in this
abdul malik klari
Doha – Qatar
I much appreciate the information provided by this Blog.
We have SQL2000 running on our production boxes, We are planning to migrate to SQL2005 soon.
What are the issue invovled with installing SQL2000 on SQL2005 default instance. Are there any known issues that we would run into?. Waht is the best path of Migration with minimal downtime ?.
thanks in Advance
We want to use parts of your Coding Standards as part of our documentation.
Can we just use then and insert a line acknowledging that?
Is that all is needed?
Thanks
After restoring database, i like to know how to restore all the security login/users/jobs on this database. Many thanks in advance
HI ALL,
Just wonder if anybody can help me on this, as right now i am facing a prblem using INSERT-EXEC statement, the issue is relate to the performance, like when I am inserting the record in my temp table using below statemnet
Insert into #TempTable
Execute Stored Procedure (parameter…)
it is taking almost 3 minutes for 2870 recods return from stored procedure.
while if i am running only stored procedure (actual without insert) the actual run time of the stored procedure is 10 sec.
so want to know if anybody knows what is wrong here or is there any better solution for this!
Regards,
Syed S Taqvi
Hi Pinal,
Is there a article on capacity planning? I appreciate.
Hi,
can any one help me Please. i have a date column “DateTime” populated with 2008-05-01 11:45:58.380 with thousands of dates , when i run report /Excute i would like to get result ” DataTime” as 2008-05-01 only, not 2008-05-01 11:45:58.380. Is there any way to truncate the date field??
Below is the command i am using
SELECT dbo.Users.LoginID, dbo.Users.UserName AS [User], dbo.AuditTrail.ATTime AS DateTime, dbo.UserTypes.Description AS [User Type],
dbo.UserGroups.Name AS [Default Group], dbo.Users.Description, dbo.UserContact.AdvisorTitle
FROM dbo.Users INNER JOIN
dbo.AuditTrail ON dbo.Users.ID = dbo.AuditTrail.UserID INNER JOIN
dbo.UserTypes ON dbo.Users.UserTypeID = dbo.UserTypes.ID INNER JOIN
dbo.UserGroups ON dbo.Users.DefaultGroupID = dbo.UserGroups.ID INNER JOIN
dbo.UserContact ON dbo.Users.ID = dbo.UserContact.UserID
WHERE (dbo.AuditTrail.ATTime >= CONVERT(DATETIME, ’2008/05/01 00:00:00′, 102)) AND (dbo.AuditTrail.ATAction = ‘Login’) AND (dbo.Users.IsActive = 1)
ORDER BY dbo.Users.LoginID, dbo.AuditTrail.ATTime
select ATTime from AuditTrail
Hi,
can any one help me Please. i have a date column “DateTime” populated with 2008-05-01 11:45:58.380 with thousands of dates , when i run report /Excute i would like to get result ” DataTime” as 2008-05-01 only, not 2008-05-01 11:45:58.380. Is there any way to truncate the date field??
hi pinal
i want to know that oops concepts are followed in sql server
2005 or not if yes then pls give me some example with
description.
Hi Pinal,
Could you prescribe me a good book on SQL server 2005? I am new in this technology and working as an application DBA (PT, BIW services, Reporting etc.)
I am from India.
Thanks in advance.
Hi Pinal,
I am a novice with SQL, and just started learning.
what is the use of identity constraint ,how to add the constaint to existing table.
regards,
venkat.
Hi Pinal,
Your query to eliminate duplicate rows was really very good.
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicatevalueColumn1, DuplicateValueColumn2,
DuplicateValueColumn2)
When I executed this query on a small table, it worked, but my table consists of 70 lakh rows. When I am trying to create the identity column in this table, my system is not responding. Please help me out with this.
Thanks,
Amrita
Hi Pinal,
i need to retreive a data from more table with multiple condition, which lead to me having 20 sub queries and 15 joins
the sub queries used in select columns
it also contain the tables used in the select statement
it take more than 40 seconds to retrive the data,
please suggest me some idea to speed up it.
hi pinal,
thanks for posting new thing regarding sql server on sites.
pinal, what shuold be most difficult situation most sql
developer facing on thier project ?
can i have list of this problem and how to solve that ?
thnaks..
Hi Pinal
I find this site very helpful.I want some guidance from you.
I want to learn server 2005.Can you please advise me some books to learn microsoft SQL Server 2005 with some good practical example.I have some basic knowledge about SQl 2000.
Thanks
MANISH
Hi Pinal,
I have a table containing nearly a million records
I have around 100 duplicate records in it. So dropping the table and copying its contents would consume time.
Without dropping the table, is it possible to remove duplicates from the table??
Thanks
Abdul
Hi Dave,
In oracle sqlplus i m trying delete some records that is around 12 crore previously it was deleting immediately that is around 1 Lakh in and hour but now it takes more than two hours to delete 1000 rows. Kindly give us solution.
Thanks in Advance,
Mohsin javed
hi sir,
actually i am importing carores of data in mt database in sql server 2005. becuse of low disk space on my c drive i changed the location of tempdb .mdf and .ldf file to some drive e which have lots of free space around 200gb. now i had run the query for insert into a table for inserting data . but its taking lot of time and now wht i saw is that neither the size of tempdb ldfand mdf is increasing i am not able to undestand why this is happning
thanks
hi,
i encounter one problem. well i have created a databse sfimport and its all .ldf and .mdf files are created on f drive and i have made the mode “simple” .There are approximately 10crores records in this db.
and now i am trying to update these records.
but the problem is its increasing the ldf file of tempdb database which is syatem database becuse of which my space on c drive is geeting very less and the query for update are geeting stop by giving the below error
“sg 1105, Level 17, State 2, Line 1
Could not allocate space for object ‘dbo.SORT temporary run storage: 152711031947264′ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database ‘tempdb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
”
but i dont know why , i am not doing anything with tempdb all my things are done on sfimport database which is on f drive . pls tell me the solution of this problem i am not able to understand this
pls reply me as soon as possible
Hi Pinal,
You have a link to download interview question and sql coding standards. How come you don’t have a link for we your readers to download a template on Disaster recovery plan.
O r you can give us a hint on putting some documentation on disaster recovery plan(DRP) documentation.
Cheers
Emeka
Hi Pinal,
I highly appreciate great efforts and work you are doing.
I would like to ask you a query in regards to SQL server 2000
- is it possible to alter the datatypes of more than 1 column in a single query
I’ve tried
ALTER TABLE table1
MODIFY(col1 varchar(50),
col2 varchar(100));
but getting “incorrect syntax error ‘(‘ ” error.
Thank you
Nilesh
Hi,
I went through your website and i found it really helpful.
I have few queries in sql server 2005 and they are:
1) how to debug a stored procedure?
2) where is the sql query analyzer in sql server 2005( i’m using MS sql server Management studio express)
3) how does “EXECUTE sp_executesql” work?
Awaiting your response.
Thanks,
Deepti
@Kabir
This might help
declare @datetime datetime
declare @date varchar(10)
declare @year varchar(10)
declare @month varchar(10)
declare @day varchar(10)
select @datetime =getdate()
select @year= Year(@datetime)
select @month = MONTH(@datetime)
select @day = DAY(@datetime)
select @date = @year + ‘-’ + @month +’-’ + @day
select @date
Pinal,
I have a problem and I was hoping you could help. I am tring to do multiple case statements within one select where the results of the first one are used as the criteria of the second, I don’t know if its possible hence the email. The following is roughly what I am trying to do.
Select rate, time_hours,
K = Case When rate 40
Then Null
Else
CASE When time_hours = ”
THEN Null
ELSE 1
L = CASE When K = ”
Then 2
Else Null
End
When it gets to the L it fails as it does not know what K is. Please advise. Thank you
Pinal,
Really great resource!
I would like to know the advantages and or disadvantages of deleting rows in a table or updating them via a active field (bit 1 or 0).
At present if a user removes a web page, for example, that row in the database is assigned a 0 in the active field. We then filter all queries with WHERE active = 1. We use this in case users wish to restore their web pages.
The table is approaching 4 million rows and probably 25% are inactive rows.
If we switched to deleting these rows instead how would indexing be affected? Our queries would obviously speed up without the Where clause, so my question is with a large DB what is the most effect way of dealing with this?
Thankx Ameka,
I tried the below sql which wored for me: -
SELECT dbo.AuditTrail.ATTime,
CONVERT(char(12), dbo.AuditTrail.ATTime, 3) ,
CONVERT(char(12), dbo.AuditTrail.ATTime, 8) ,
CONVERT(char(12), dbo.AuditTrail.ATTime, 10) FROM dbo.AuditTrail WHERE CONVERT(char(12), dbo.AuditTrail.ATTime, 3) = ’01/05/08′
Thankx again
@Kabir
This will help your query.
select convert(varchar(10),getdate(),120)
Hi
I need to find out the version,build,edition of sql server for 60 servers for an inhouse project .
I understand that the function serverproperty gives all the details on my local system but if i need these details for my remote sql server how do i get it .Please adivce or please provide me some script to do this .
Thanks
Aparna
Hey – love your site, it’s a wonderful resource.
I have like 100 small databases on my MSSQL 2005 server, all were created in FULL recovery mode. I want to change them all to SIMPLE recovery mode… is there a simple way to do this all at once?
Thanks, Mike!
–This only list sql servers that are broadcasting their existence on the Network
–use this on the command line:
SQLCMD -L
OR
exec master..xp_cmdshell ‘SQLCMD -L’
Hi Pinal,
Your site is so helpfull. It is very good.
Thanks,
Sandeep
hi there,
I am in really good situation if you guys can help me out.
i have database test.
i has been backed up regularly. some one did imports and between each transactional backup but by mistake some one deleted some of the files the problem was noticed till system already had taken the three consecutive backups after that incident. lot of data has been changed since then. i am trying to recover all the data but can’t go further after point of time recovery.
Pinal is there any way to restore the data “AFTER” “the point of time reovery”
hi,
I have been in your site for many times and its great work.
I have one question to ask
How can we print the output of query results in a text file.
for eg. Select * from customers
results: 1 Mark
2 David
etc.
I just want the results in a .txt files by just executing the query in sql server.
Could you or anyone visiting this blog help please.
Dear Pinal,
How are you,
I have a problem which needs ur help.
I have a table T1 like below
Item_Id vendor_id price
1 1 12.35
1 2 11.00
2 1 10.00
2 2 11.00
2 3 12.00
2 4 12.50
3 1 10.00
I want output like below.
Item_Id VendorPrice1 Vendorprice2 vendorprice3 vendorprice4
1 1:12.35 2:11.00
2 1:10.00 2:11.00 3:12.00 4:12.5
3 1:10
hope you got it.Pls help me Mr.Sql Expert.
Hi Pinal,
I am facing a problem.
I am executing a stored procedure in sql server 2005.
That stored procedure called several other stored procedure.
I am getting the execution time for the main stored proc.
How can I get the execution time for all the other stored proc which are being called from the main proc.
Please reply on my mail too.
Hi pinal,
i want to know the use of that 4 system databases. How can handle that system databases for our databases. i am working inventory related database projects.
could u pls explain me.
Hi Pinal,
maybe you can help me in this question:
i need to log every query statement started in my instance of SQL Server 2000 in a range of time, tipically a couple of hours.
Do you know if is it possible?
Thx in advance.
Hi Sir,
I am working on SQL server Reporting Services 2005. I am trying to exectute one CASE Statement in Reporting Services 2005, it’s giving me an error. “Unable to Parse Query”
But when I am executing the Same statement in TOAD for Oracle 9.5, it gives me proper Result.
I have Oracle Server as backend for Reporting Service 2005.
Plz guide on the same. Waiting for your Reply.
Query is as below for your ready reference.
SELECT MLISTNODE.LN_CODE AS UNIT, MLISTNODES.LN_ID, MLISTNODES.LN_CODE AS AREA, MLN.LN_CODE, MLN.LN_ID AS LINE_ID,
MLN.LN_CODE AS EXPR1, MLP.QUANTITY, MLP.OPTION_CODE AS OPTIONCODE, MLP.SHORT_CODE AS SHORTCODE, MLP.TAG_NUMBER,
MLP.INPUT_1 AS SIZE1, CASE WHEN MLP.INPUT_2 LIKE ‘S%’ THEN ‘ ‘ELSE MLP.INPUT_2 END “SIZE2″,
CASE WHEN MLP.INPUT_2 LIKE ‘S%’ THEN MLP.INPUT_2 ELSE MLP.INPUT_3 END “SCHEDULE1″, MLP.INPUT_4 AS SCHEDULE2,
MLP.PROJ_ID,
M_COMMODITY_CODES.COMMODITY_CODE, M_IDENTS.IDENT_CODE, M_STATUS.STATUS, M_COMMODITY_CODE_NLS.SHORT_DESC
FROM M_LIST_POS MLP INNER JOIN
M_LIST_NODES MLN ON MLP.LN_ID = MLN.LN_ID INNER JOIN
M_COMMODITY_CODES ON MLP.COMMODITY_ID = M_COMMODITY_CODES.COMMODITY_ID INNER JOIN
M_IDENTS ON MLP.IDENT = M_IDENTS.IDENT AND M_COMMODITY_CODES.COMMODITY_ID = M_IDENTS.COMMODITY_ID INNER JOIN
M_STATUS ON MLP.STAT_ID = M_STATUS.STAT_ID INNER JOIN
M_COMMODITY_CODE_NLS ON MLP.COMMODITY_ID = M_COMMODITY_CODE_NLS.COMMODITY_ID INNER JOIN
M_LIST_NODES MLISTNODES ON MLISTNODES.LN_ID = MLN.PARENT_LN_ID INNER JOIN
M_LIST_NODES MLISTNODE ON MLISTNODE.LN_ID = MLISTNODES.PARENT_LN_ID
WHERE (MLP.PROJ_ID = :PROJ_ID) AND (MLN.PARENT_LN_ID IN (:LN_ID))
Thanks & Regards,
Sushil Bansode
I have a table t1 as
id ||mon-year||valu
————————————-
1||Jun-2008||AABBCCDDEEFFGGHHIIJJKKLLMMNNOO
2||Jun-2008||ABBCCCDDXXYYXXYYCCDDEEFFBBDDXX
3||Jun-2008||XXXYYYZZXXDDCCDEEREWWWEEREXXER
t2 as
id||date|| mon_year val
——————————————
1||10-Jun-2008||Jun-2008||X
1||12-Jun-2008||Jun-2008||Y
2||15-Jun-2008||Jun-2008||A
2||16-Jun-2008||Jun-2008||C
2||10-Jun-2008||Jun-2008||B
3||01-jun-2008||Jun-2008||B
3||02-Jun-2008||Jun-2008||C
3||03-Jun-2008||Jun-2008||D
using a single query I need the result in t1 as below based on t2 val
id||mon-year||valu
——————————————————
1||Jun-2008||AABBCCDDEXFYGGHHIIJJKKLLMMNNOO
2||Jun-2008||ABBCCCDDXCYYXXACCCDDEEFFBBDDXX
3||Jun-2008||BCDYYYZZXXDDCCDEEREWWWEEREXXER
Is it possible to do it with a update and select statement. Can u pl. help me in this regard
Hi Pinal,
I just want to check how many tables altered in last 15 days and what were the alter statements(in SQL Server).
Please help me.
Thanks & regards
Pradeep
amazing and brilliant…
Hi Pinal,
I am a ASP.NET Developer and not real strong in database management. I have searched for the best way to manage transaction log growth, but have been led in many directions. I wonder if you could set the record straight on the best practices for managing transaction logs.
Thanks
There are lot’s a great hints here can you help me with my problem
I need to make a copy of a row of data before update statement is executed.
Table TEST has a row with, say, ID 5, and FLAG active. When an update is performed on this row I need to make a copy of the original data into the same table with a new ID and FLAG set to inactive.
My initial though was to use “before update trigger” but SQL2005 does not have that functionality???
what can I do ?
Thanks
Hi Pinal,
a couple of months ago I set myself the task of learning SQL Server 2005, both admin and SQL. Naturally, I’ve been visiting your web site quite frequently during this time – lots of useful stuff! Well, now I’m at the stage where I need to assess how well I’ve done. I have AdventureWorks, and I thought that since it’s such a large (comparatively) and detailed DB, wouldn’t it be nice if I could find some exercises/tests that targeted it – particularly exercises in SQL. For instance: design a query that provides the names of all employees whose sales in 2003 were greater than those of John Smith. Can you suggest anything? I’d be especially interested in some resource on the internet.
Regards, and keep up the good work -
Ron
Hi Pinal,
I m working with reporting services, it return me a large amount of data (thousand of pages). Why paging under Reporting Service get all data and don’ t retrieve part by part ?
thank you for your helping
scarabee
hi pinal,
i have a one query
i am giving the table structure
bid cid aftervalue before value after value
the above is hte table structure and in the table bid is identity column and we are having aftervalue and cid values
the condition is
before value=after value
after value=next after value
and for same cid value the above condition must satisfy
so please help me in this case
Hi all,
Making use of parameters, how do I pass parameter to choose the columns/fields of a table.
I know parameters are used in WHERE clauses to filter data but can parameters be used to choose the Column of a table ie. in a SELECT clause
(The sql statement below does not work, as I have found out)
SELECT @Column
FROM For_Sale
Thanks
hi ,
yours blogs are excellent. its nice learning from urs blogs.
Actually i want to import data from excel file i.e. i want to import excel file in sql server 2005 but i am not able to import. its giving error.
pls let me know the procedure for importing excel file .
thanks
amit sharma
Dear Sir,
I am a Mainframes Tester and i wanted to shift my career into SQLServer2005DBA.
Can you please suggest me, whether it is possible or not and also suggest me the Good Text book for becoming?
Thanks
Vihshal
hi,
This is the first time i am asking a q in a blog.
I have an windows applicatin with MS access (.db) as front end and SQL2000 as backend.Its connected using Linking tables. Its running perfectly but I have 2 SP in the sql server which accept 2 parameters from front end and generate the report.Its not running. So is it possible to call SP having parameters in (.mdb) project in access ?
Hope u would reply.
hi,
i have small doubts in sql server . I had stock 10 item .user will buy my stock.he will select the 20 item. in that i ahe to display 10 item instock,10 remaining items will be 1 week tme this two will display.how i procedure
I have a query Please give some suggestion.
I have Order Master and Order detail table.
I want to display ALL Orders with all itemid correesponding to a single order.
Ex
Order Id Order Value
I have a query, I need your suggestion.
I have a Order table and Order detail table (Master-Child)
I want to display all orders with item details.
Ex
OID OValue ItemId
1 123.23 I1,I2,I3
2 456.12 I2,I3
Like this.ie Oll the items in a single order in a row.
Waiting for your valuable replay.
mail id – k_arunpatro@rediffmail.com
Hi Webmaster,
I am writing a document on SQL Sever Coding Standards and planning to post it on our Knowledge Management intranet, so that other employees in my company can also benefit from reading it. I came across an article, http://blog.sqlauthority.com/2007/06/04/sql-server-database-coding-standards-and-guidelines-part-1/, written by you on the same subject. I would like to include parts of this in my document. I request you to kindly grant me permission to do so. I assure you that I would give appropriate credits and references (in-line citations) to your article in my document.
Thanks & Regards,
Karuna
hi all,
Is there any solution in sql query for the doubt explained below.
In table_1 there is a field called “typesOfProduct”
contents of the field is A,B,C,D,E,F (A,B,C,D,E,F are different product stored in one cell)
when i copy from table_1 to table_2 i need one product Type to be stored in each Row.
like
A
B
C
D
E
F
Thanks
How to make the initial letter of a word to capital letter
reply me soon
Thanks,
anand
Hello Sir,
I , Amit Saxena had a chat few weeks back. I am working on a merge pull Replication Project holding 8 branches in Rural areas supported by VSAT.In the latest release of the application and coding, we have 3 new tables to be added,columns to be added on tables already in replication,column to be added in already existing sps and triggers and a index to be created .Can u tell me how we shud proceed.Based on links from internet , we tested some things on UAT like adding new column in already existing table but it throwed an error on doing sync for the subscribers.Plz revert to me with some suggestion if u find some time.
Thanx
Regards,
Amit
Hi Sir,
I am facing a problem while exportin excel to sqlserver.
I am having two excel sheets with different dates(Created dates) while exporting excel to sql server these sheets data is stored in one table and then compare each rows for duplicates and we have to eliminate duplicate rows from that table(if there is any two rows are equal we have to eliminate second row only) and after eliminating duplicate rows the data will be imported to actual excel format.
Please help me..
With Regards
Bhanu Prakash.
Hello Dave,
I really appreciate your work and more important the way you work.
I have one suggestion which might be considered or can be rejected.
I do agree that this blog is created to print all the articles written by you to help others. I see most of the times members ask many questions which are urgent and need immediate reply and attention. But when they post their question it takes one day to display that question on the web page and when we reply to it our post is not visible until next day, ( until moderator approves it).
This is my request to remove or take out this constraint of getting approval from moderator to ask any question to reply to all those questions.
I see that this blog is maintained by Indians ( from India). And most of SQL Server DBA/Developers who are in US or different countries have to wait till next day to get approval, because of different timings in different countries.
Please think about this. Thanks for taking out your precious time for reading this.
Thanks,
Imran.
Imran Mohammed,
I appreciate your comments and feedback. I will sure rethink about moderator approval of comments and question.
Your participation on this blog is very very helpful.
The reason moderation is there to avoid any personal attack, flames, or spams. You will not believe how many spams are still going in valid queue, which have to be manually deleted.
Again, please continue participating on this blog. I will see if I can do something about moderation.
Kind Regards,
Pinal
@ Lakshmish,
You did not write complete question, please be sure to write complete quires. Thanks.
This is what i did, since I did not understand your question properly because of lack of information you provided, I have to write the code two times,
1. All data is with out spaces (which you mentioned) in your post, which is grammatically incorrect like this, : A,B,C,D….
2. All data does have spaces in between them ( which I assume should be the case). Which is grammatically correct like : A, B, C, D…..
And then in this example I took some extra data to show you that code still works if you have large names instead of A, B, C, D … I took new data as Onion, tomato, chilli, pepper ( sorry could not think of something else ;) )
Code worked fine for both, initially I wrote a code for ABCD it worked fine but later when I said, wait what if the names size increases and then I changed the code, new code works for both.. ABCD as well as onions…. ( ;) )
— code starts here
Before you execute the code you need to create tables and enter sample data.
CREATE TABLE TABLE_1 (TYPESOFPRODUCT VARCHAR(MAX))
CREATE TABLE TABLE_2 ( TYPESOFPRODUCT VARCHAR(MAX))
INSERT INTO TABLE_1 (TYPESOFPRODUCT)
SELECT (‘ONION,TOMATO,CHILLI,PEPPER’) UNION ALL
SELECT (‘A,B,C,D,E,F’)
– I created two sample tables, like you mentioned table_1 and table_2 and then I entered sample data ( with no space after comma (,) ).
After you create tables and enter sample data … execute this code. Before executing please check which line you have to comment , no space in between words or have space between words.
DECLARE @VAR INT
DECLARE @PLACE INT
DECLARE @CMD VARCHAR(100)
DECLARE @VAR1 VARCHAR (100)
DECLARE @GETNAMES CURSOR
SET @GETNAMES= CURSOR FOR
SELECT TYPESOFPRODUCT
FROM TABLE_1
OPEN @GETNAMES
FETCH NEXT
FROM @GETNAMES INTO @VAR1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @VAR =1
WHILE @VAR < = (SELECT LEN( @VAR1))
BEGIN
IF ( SELECT CHARINDEX(‘,’, @VAR1, @VAR)) 0 AND @VAR 1
BEGIN
SELECT @PLACE = CHARINDEX(‘,’, @VAR1, @VAR)
–SELECT @CMD = SUBSTRING (@VAR1 , @VAR+1 , @PLACE – @VAR-1)– IF HAVE SPACE
SELECT @CMD = SUBSTRING (@VAR1 , @VAR , @PLACE – @VAR) — IF NO SPACE
SET @VAR = @PLACE +1
END
ELSE IF @VAR = 1
BEGIN
SELECT @PLACE = CHARINDEX(‘,’, @VAR1, @VAR)
SELECT @CMD = SUBSTRING (@VAR1 , @VAR , @PLACE – @VAR)
SET @VAR = @PLACE +1
END
ELSE
BEGIN
–SELECT @CMD = SUBSTRING (@VAR1 , @VAR+1 , LEN(@VAR1)- @VAR) — IF HAVE SPACE
SELECT @CMD = SUBSTRING (@VAR1 , @VAR , LEN(@VAR1)- @VAR+1)— IF NO SPACE
SET @VAR = LEN(@VAR1)+1
END
INSERT INTO TABLE_2 SELECT @CMD
END
FETCH NEXT
FROM @GETNAMES INTO @VAR1
END
CLOSE @GETNAMES
DEALLOCATE @GETNAMES
GO
– After you execute the code, check if you got your desired output –
SELECT * FROM TABLE_2
Also check the length of each item is it same or size has been increased of decreased.
SELECT LEN (TYPESOFPRODUCTS) FROM TABLE_2
Your input to tables : ‘A,B,C,D,E,F
Your output from new table :
A
B
C
D
E
F
Hope this Helps
Thanks
Imran.
@ DAVE.
Thanks DAVE.
@ Jarrod ( Post no 734 )
CREATE TABLE EXAMPLE1 ( COLA INT , COLB INT )
INSERT INTO EXAMPLE1 VALUES ( 2, 3)
DECLARE @CMD1 VARCHAR(MAX)
DECLARE @CMD2 VARCHAR(MAX)
SET @CMD1 = ‘COLA’
SET @CMD2 = ‘SELECT ‘ + @CMD1+ ‘ FROM EXAMPLE1′
EXEC (@CMD2)
Hope this helps,
Thanks,
Hi Pinal
Like your blog.
I am hoping you can help me with something. I have been studying the Adventure Works OLTP database on SQL Server 2005 and it seems the Purchasing data seems incomplete.
See Following – using Item with ProductID = 320
1st the info regarding item 320 from Product vendor:
SELECT *
FROM Purchasing.ProductVendor
WHERE (ProductID = 320)
From here we can see the ‘standard’ Vendor Unit of Measure = ‘CAN’ – canister.
2nd the Info on where this product is used in production from the BOM
SELECT *
FROM Production.BillOfMaterials
WHERE (ComponentID = 320)
Here the Unit of Measure is ‘EA’ – Each
3rd in Inventory:
SELECT *
FROM Production.ProductInventory
WHERE (ProductID = 320)
From the results this must be in ‘EA’ too.
So the BOM and inventory file has this Item being used in the UoM of EA. The Purchasing files and transaction history files have this item in ‘CAN’
I must be missing something in the way the whole database is set up – I just cannot figure out an answer to the following question:
Where in the data base do we find how much of this product 320, in the unit of measure ‘EA’, is found in a Canister?
dear mr.pinal,
it is really a great blog.it helps me a lot and it suggests all the tricks in sql.
but I am looking for importing data frm excel worksheet to sql,
it is not in the blog.
can u explain it & help in this.
Thank U.
Dear Sir,
I see your articles
it is very useful to me
i need one stored procedure that is
insert multiple records using stored procedure
hi
i am raj acharya right now working as a network and admin excutive . also performing dba activities i want your advice for choosing my field .. i think you will help me out from my this problem can i forward you my resume….
actually i am in so much confusion about in which field i should go .NET developer, Network or system admin or Database admin , first you will think that you should go whatever field in which you have interest or in which you have experience… i have more experience as software support and system admin also supporting sql server and handling. but you know i am like jack of all but master of none….please help me
Dear Pinal
I got the “Database.Bak” file from my CUSTOMER
I am Unable to Restore the same
Plz help me out to solve the Problem
I am Getting the ERROR When iam i am try to follow the steps that u Suggested
The Error MSG is “Msg 3241, Level 16, State 7, Line 1
The media family on device ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Hills.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1″
Plz Help Me
@Sumanth,
SQL Server is very intelligent, when you take backup, it stores the information from where it was taken, in case your customer took backup from Edrive path “E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”
When you try to restore database inside SQL Server, SQL Server will look for the same location, E drive path “E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
And I assume you dont have this path available in your computer.
Solution, when you try to restore the database, try to change the location and then you will be able to restore the database.
1. generally this happens when there is one file already exists at that location,
2. when the path does not exists.
In your case, path does not exists, so go ahead and change the path location while restoring the database.
Hope this helps,
Thanks,
Imran.
Sir,
I’m a nontechnical user who has been able to work from home by connecting to my office computer.
All of a sudden I got this error and can no longer work from home:
Only part of a ReadProcessMemory or Write Process Memory request was completed.
I’ve spent hours searching the Web and came across your site. Might you have a suggestion or two.
Many thanks.
Dear Imran Mohammed
I Tried the BUT same As per the location in my system BUT
I am Getting the same ERROR
I Used “restore database Hills
from disk = ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Hills.bak’
with recovery,
move ‘Hills’ to ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Hills_Data.mdf’,
move ‘Hills_log’ to ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Hills_Data_log.ldf’
go
I am Getting the Error as
“Msg 3241, Level 16, State 7, Line 1
The media family on device ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Hills.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.”
Plz Help me to sole the issue
Dear Pinal,
I am facing one error in my replication process.
Replication type is Transaction replication.
We have a setup of 2 servers
Server A———-à Publisher Database resides.
Server B ——–à Subscriber and Distributor database resides
Problem is that after the articles got replicated to subscriber databases, the distributor agent cleaning job is failing to run.
I am getting the below error message
Executed as user: DOMAIN\username. Error converting data type
nvarchar to
int. [SQLSTATE 42000] (Error 8114) Could not remove directory
‘\\Server\DISTRIBUTOR\unc\dbname\20080710012702\’. Check the security
context of xp_cmdshell and close other processes that may be accessing
the
directory. [SQLSTATE 42000] (Error 20015) [Email address protected]
parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only
check rowcou: agent [Email address protected] parameter must be the
value
0,1, or 2. 0=7.0 compatible checksum. 1=only scheduled for retry. Could
not
clean up the distribution transaction tables. [SQLSTATE 01000] (Message
14152). The step failed.
Please help me on this and revert me if you need more information
Hi,
I wanted to know impact of using UNION and UNION All in SQL queries on database and application performance. I’m using SQL Server 2000 but not sure which build it is. I know there was a performance bottleneck with SQL Server 2000, the issue was SQL server was generating inefficient execution plan when Union operator is used in query. But this has been fixed later on but Microsoft, so assuming this may not be the issue.
Basically I’m not SQL Server professional and also new to the same.
One more question, is the MS SQL operator and function works in same maner as of Oracle?
Your reply on above will be much more apprecited.
Thanks,
Hi Pinal,
Is thr any way to use coldfusion in .net(asp.net),if it is possible can you please send me some sample code for that.
Thanks
Hi, hope you can help me.
Is it possible to get SQL 2005 when you purchase 2008?
The reason is that we are busy upgrading clients to 2005, but seeing that 2008 is being released shortly, I was wondering if there is an option to get 2008 and 2005 at the sametime. They would then have the 2008 version for when we upgrade them in a few years time.
Or is there a downgrade rights option as there was with SQL2005?
Thanks
Pinal,
Can you tell me how do i convert data from excel sheet into a web page by using ASP.net, I need solid solution for this please help me yaar
through java connect to the databse using sqlserver 2005 in this insert the values into databse here i am getting incorrect syntax near the keyword by how cal i modify this exception give me suggestion ASAP.
Thanks&Regards
mahi
Hi Pinal,
I have a requirement where there are 6 similar databases which is configured using DCR’s(Data Change Requests) and now I have to synchronize all the similar Db’s and find out the delta’s and finally make a single db which I can syc with other 5 similar db’s.
Please suggest me how to approach this requirement and also it’s very urgent.
Waiting for u r reply….
Regards,
Mahesh Ch.
Hi,
Can you tell me about “Dynamic SQL”, When to use Dynamic SQL ,List the advantages of Dynamic SQL,Some samples related to Dynamic SQL. Comparing Stored Procedure and DynamicSQL which one is the best on performance.
Please let me know the details.
Awaiting for your reply,
Thanks,
Anand
hello pinal sir,
i am really thankful of yours as whenever SQL server have problem with me your posts work as a guide for me.
But since last 2 week i am continuously fighting with the SSIS package.
As i have made SSIS Package for Traansfering data from .CSV file to table in my database.
While creating these SSIS PAckages I have tried with all the Package Protection Level,but still while executing these SSIS packages i m not getting my password for the User in connection string.every time i have to update it manually.
Bcoz of this i m also not able to make SP pr JOb for automation of Package Execution.
So can YOu plzplzplz help me in this.
i will be relally a grate thank full of urs.
as i m trying since two week i not successed.
any kind of help from any one should be appriciated.
Thanks in advance.
Regards,
Jigar
Hi Pinal,
can you please let me know the diffrent methods to import XML files into Sql server 2005 tables.
1) Do we need to have the table created before importing the xml file?
2) Will sql server read the schema from source XML file and create the sql table and imports the data , as with excel and text file.
I tried with import/export wizard and was struck at creating xml oledb…wat i need to do to create it.
Thanks
Hi Pinal,
I need your help in solving null values in calculation.
I have to perform calculation as follows:
Actual time = ColumnA – CoulmnB – ColumnC – ColumnD.
But, some of these columns have null values and so when I subtract it is producing null output.
forexample:
10 – null – 1 – null = null
I tried to the below, but it still does not work. Please help resolve this issue.
If(columnA is null) then (0) else (columnA) and then brought this in the above calculation, but still it does not work.
I tried nvl, Zeroifnull, Coalasce functions, but it not successful.
Please advice how to resolve this.
Can you please help me.
Thanks in advance for the help!
Toushila
@Taushila
This might help,
Create Table Example ( cola int, colb int , colc int, cold int , cole as (isnull (cola,0) -isnull (colb,0) – isnull (colc ,0)-isnull (cold ,0)))
Insert into Example ( cola , colc) values ( 10, 1)
For this record cole = value -null-value-null ( just like your example)
Select * from Example
Result : 10 NULL 1 NULL 9
Hope this helps,
Imran.
Hi Pinal,
Can help me on this?
Basically, what I want to do is take values from one table in database 1, and insert the values as different values (that part I know how to do) into a DIFFERENT database (as well as different table name) on the same mysql server. But i don’t know how?
Pls…
Thanks a lot
Dear Pinal,
Sub : MSSQL DBA vs. MYSQL DBA
First of all I would like to thanks & appreciate bottom of my heart for your Blog.
Currently I am working as System Administrator For windows
I am very interest & passion to work as a DBA
As some of my colleague’s are advised to go for MYSQL
MSSQL DBA vs. MYSQL DBA, who will have sound brighter feature & carrier
No I am in confusion to choose .
plz help……..
Thanks in advance…………
Hi Pinal,
Thank you very much for an awesome website. It has certainly helped me out of many a jam.
I am trying to get this stored procedure to execute but am having some difficulty.
I call the stored procedure from an ASP page using the ADODB.COMMAND and passing two parameters to the sproc.
The code works fine but the sp does not execute.
Here is the sp:
******************************
USE [xxxxxx]
GO
/****** Object: StoredProcedure [dbo].[xxxxxxx] Script Date: 08/08/2008 07:55:25 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[xxxxxx]
@inputfile VARCHAR (500),
@outputfile VARCHAR (500)
AS
DECLARE @ifile VARCHAR(500)
DECLARE @ofile VARCHAR(500)
DECLARE @sqlcmd VARCHAR(8000)
SET @ifile = @inputfile
SET @ofile = @outputfile
SET @sqlcmd = ‘SQLCMD -Sxxxxx -d”xxxx” -Usa -P”xxxxx” -i”‘ + @ifile + ‘” -o”‘ + @ofile + ‘” -h 2000000 -k1 -W -s”,”‘
EXEC master..xp_cmdshell @sqlcmd
**************************
Any comments will be greatly appreciated
hi pinal , how r u
ur solutions r very helpful to us. This is shaik . i’ve small Query .
how to rename / replace/change a column name in SQL SERVER 20005 . please kindly help me.
immediate response plz.
Thakx alot.
shaik
Hi i have a question , i have a table with …… UserID , QueueID , EventDate , etc…. i have made a PK on the first tree column as mentioned PK: ( EventDate ASC , UserID ASC , QueueID ASC) and i have some indexes on the same column (they are not the same). My question is why is it that when i run sys.index_Columns the order of the PK is UserID , QueueIS and EventDate
to by pass this problem i have to change the order of the column inside the table.
Hello Shahriar,
When you create a composite Primary key, you specify in which order you want to create the primary key.
I assume when primary key was created, it was created in this order, ( UserID , QueueIS and EventDate), that is why you see this order in sys.index_columns.
When you create a primary key by default a clustered index will be created on table, unless you enforce it to create a non clustered index.
You dont have to change the table structure, you can do this,
1. To check what order was used in the primary key when it was created, do this,
Right click table and and script the table to create, make sure you UNCHECK, drop existing option
and the new script will open in a new window. now check for primary key, in which order it was created. Clustered index will be created in the same order.
2. You can drop existing primary key and then recreate a new one and this time mention the proper order, when you drop primary key, clustered index for that table will also be dropped, and when you create primary key, clustered index will be recreated again, since clustered index physically stores the data, your table might not be available for users for that duration, In 2005 you can have online reindexing which is an exception to this.
Hope this helps,
Imran,
I linked a server to another server. I need to set up job to run with the following code:
Truncate table tbl_packages_package_lists
INSERT INTO [CSWReportDatabase].[dbo].[tbl_packages_package_lists]
([ShipDate]
,[service_friendlyname]
,[Cost]
,[Packages])
SELECT
ShipDate,
service_friendlyname,
Cost,
Packages
FROM
qry_packages_package_lists
The name of my linkserver is CSWReportDatabase and the name of the database CSWReportDatabase.Please help me. Thank you
Hi Pinal
Thanks for a wonderful site
my query is
I have a table with redundent data in it, would like to find the duplicate rows
Like I have three same row
Kamal, Mehta, 12/05/2008
Lalit , Gupta, 02/06/1999
Kamal, Mehta, 12/05/2008
Kamal, Mehta, 12/05/2008
Now i wish to do is Keep a single entry of Kamal,12/05/08 in original table and would like to move the two duplicate rows to another temprory table, so that in future if need to verify and deleted something wrong
Please note their is no primary key
Hope you understood the problem,
Please try to help me asp
Hello,
In SQL Serer 2000, we can get the remote server name accessing a particular SP with the help of @@REMOTESERVER (If we are using Remote Servers).
But if i have a Server A, Clients B and C connects to Server A using linked servers and say the user “u” (Windows account for example) is admin on both Clients B and C.
Now my question is, Is there any way that i can give access to “u” on Client B and not to same “u” on Client C. I want to grant access to a view.
Regards,
-Chandra.
Dear Sir
iam a constant visitor to your site as iam struggling hard to get settled in career as SQL server database analyst . I am familiar with My SQL and T- SQL at my college days but never had hands on experience working in a company. iam deeply deppressed in a fear of not being able to fullfill my will . Can u please direct me how and what to do to get a fresher job to become database analyst. What books to prepare .. what certifications required and how can they be achieved.
Your response would be great help to me
Please find time and reply me sir
thanks in advance
Sincerely
Mrs.Bhargavi Nadella.
Dear Pinal,
Hi, I am convert Dbf(Dbase IV) Into Sql Table And Then Insert Into My DataBase.
For Expale,
In Dbf Table , Name Like BillMast has Field Like Serial,invchar,invnum,PartyName,HasteName,AgentName,Grossval,NetVal,Piece,Meters Transfer Into Sql Table Name Like BillMast And Insert Into Invoicemst Table ,
How can Insert perticular Filed Of one table(BillMAst) To Other Table(InVoiceMst) Perticular Filed Like
For Example
Invoicemst ——- BillMast
Srno-> Serial
InvNo-InvNum
InvChr->InvChar
Pcs->Piece
Mtr-> Meters
Give Response As Soon AS Possible
Hello All,
This thread is closed for comments. If you have any question or need my help. Please go to following link and post comment.
http://blog.sqlauthority.com/contact-me-contact-pinaldave/
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Dear Pinal,
I have to copy a db to another db within the same server. For that I generate the script and run it. It works well. But I have to do this at run time i.e., from .net 2.0 by passing the destination db as a parameter. Please help me to do this.
Thanks.
Hi
Can any one help with the below question which is related
to SQL Server 2000.
How to list all the tables in a particular databse with the
row count of each table in descending order?
Thanks.
@ Sreekanth.
PLease use other contact me page to ask your quires as suggested by Pinal Dave.
Anyways here is script for your question. ( works in SQL Server 2000)
select A.name Table_Name ,B.rowcnt No_of_Rows from sysobjects A , sysindexes B
where A.type = ‘u’ and A.name ‘dtproperties’ and
B.id = object_id(A.name)and B.indid < 2
order by rowcnt desc
Hope this helps.
Imran.
Microsoft OLE DB Provider for ODBC Drivers error ’80004005′
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
This keeps coming up when I try to host my ASP pages on Godaddy.com I used dreamweaver to create them and I uploaded the db to the host . Can you please help me
Dear Dave,
I have impressed in your site.Now I wanna know ho to distinguish stored procedure and triggers.Pls reply ASAP
Thank You For Positive Movement
Dear Pinal
i want know is there any system procedure or function
to create script for table.
instead of right click on table
and select script table as
create.
if i give the table name and say create in (function or procedure)
it will generate the scripts
Hi dave
I do have server problem when i make a dbcc checkdb allow data lose script. the log of the database was increasing currently 114gig it started with I think 5MB log, then it has
server error message “could not continue scan with no lock due to data movement”
server message 601.
Now its been 3days running, Im running out of space..your help would very much appreciated..thanks
Regards,
Vincent
Dave,
I need this batch file ep_member_run.bat’ to enable a copy and name with date and I cannot see why results in an error when using xp_cmdshell, can you help at all?
rem ##################################################################################
rem ## Name : ep_member_run.bat #
rem ## Description : member.txt for Library to Archive #
rem ## Date : /10/2008 #
rem ## Author : Terry Mayell #
rem ##################################################################################
@echo off
@call EP_member_extract.cmd
EP_member_extract.cmd contents are;
@echo off
rem Make Directory With Name Date and Time
cd c:\
for /f “tokens=1-3 delims=/- ” %%a in (‘date /t’) do set XDate=%%c%%b%%a
copy ep_member_import.csv c:\archive_member\ep_member_import_%XDate%.csv
I run
EXEC master..xp_CMDShell ‘c:ep_member_run.bat’
I get this message
‘c:ep_member_run.bat’ is not recognized as an internal or external command
Hi Dear Pinal,
How are you?
I want to congratulate on your all success in life.
after success most people forget forever who helped them .
when i saw your site i found u exaclty opposite of it. wants to congrats you on this.Please be this way only
I m a new visitor to your site and very eagar to get a Job of sql dba currently working as a soft developer.
i have no source where i can get a good knowlege of sql and was so scared if i can accomplish my goad of being dba
ur site is as a personal trainer to me from today.
i just wish hard that i reach my goal with your help. i have given myself this november and december month for this.
hope that you wud help me get it
lots of wishes
swati.
Hi Dear Pinal,
How are you?
I want to congratulate on your all success in life.
after success most people forget forever who helped them
I am facing One problem In Pl/sql procedure there are 04 tables in the database like A,B,C,D and I willassign one column Company_Id of all the 04 tables and I will insert the data. If the data is Null of any tables that time it should retreive Null otherwiseit should shows data How to create the procedure and next what I should do.
Very Very Urgent
Regards,
Mahesh HAL
hello sir,
how to take mcp examination
HI sir,
I am kumara swamy and here by i have one big problem
that is,
i have just intsalled server 2005 and i ave restored my Database. but it is showing
Make Sure Database exist in the path Mentioned and it is not Readonly.
Hi Pinal,
This is Vivek from Bangalore. As i knew you are spending your time for child welfare. I was very happy. Its really most appreciable.
Thanks again.
I am very new to sql server. Can you please help me in oder to get knowledge of sql server from bottom to zenith. I am really very confused how to start and where to go?
Please help me.
Sincerely,
Vivek
Hi Pinal,
I need to combine multiple tables into one master table and all have the same fields could you please advise how to do this in a one step method with one statement…your help would be appreciated.
Thanks
Hi,
I need to verify whether the entered date is correct format or not.
My Req Like below
I have one text filed has fifteen characters like below
XXX200812120001
In above string 4th char to 11 char containing the Date.
I can capture the date from above string like below
l_fourth_to_eleven_char := SUBSTR(l_cdr_code, 4, 11);
My question here is how to validate whether the captured date is correct format or not.
Can you pls help me.
Thanks
Srini
Hello
I would like to write the sql certification Exam. please suggest me which books are the books are the best books for Exam. and any on line test pages .
Thanks ..
Hi All,
This thread is closed for comments. If you have any question or need my help. Please go to following link and post comment.
http://blog.sqlauthority.com/contact-me-contact-pinaldave/
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hello pinal sir,
I am your frequent reader of your SQL tips & tricks.
Sir i want to do some certification in Microsoft SQL Server. i need some more tips abt exams related to SQL. Kindly direct me sir.
Thank you sir..
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,
We are facing some problems in our company.
we have a very huge dataset ~100M and we have used partitioning for the same.
All the data was supposed to be residing in the partitions created according to the statecodes but the size of primary mdf file has increased to a bulk.
Can you suggest a solution or reason for.
Regards
Ajay
Hi Pinal,
I need SQL Server 2005 Interview Questions.
You are helping a lot of people by your experience
and extreme knowledge.
Thanks
Shilpa.
Hi Pinal,
I have installed sql server 2005 on my local pc. When I connect to the database using .net windows application, everything is ok. but if I try to connect to the database using asp.net web project I will receive this error message:
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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
Please help me
Mohsen
hi,
pinal i have one doubt regarding the debugging store
Procedure in 2005……..
i know the concept of debugging with visual studio,but
i need any solution without visual studio ……
how to debug store Procedure in sql server 2005
plse help me
thank you
hi,
pinal
how to debug the store Procedure in Sql server 2005…….?
Hi Pinal,
How are you ? I want to take one help/guideline from you is that :
we have two sql-2005(std) database server on different different location. out of them one is on remote place.
we have windows-2003(prem) server at main(central) branch.
we have windows-2003(std) server on remote place.
we have broadband connection at both the sides. At central server we have 2 mbps connection with static IP.
Now i want to connect both the server. My remote server should be connect to the central server.
Kindly give your suggetions/help in the above mentioned point.
Thanks
Mukesh Bhatt
Hi Dave,
Happy New Year!
This is Rajendra vege, I just want to know the information to grant the permission to one of the user to the data base from server in SQL server 2005 , so that he will able to access the server data base using SQL service manager.
Thank you.
Vege Rajendra prasad
Hello Pinal,
I have to create one UDF in which I required to convert special characters like “ü” to its most logical simple charater like “u”.
Is that be possible using SQL server collation functionality? Can you please provide me at least a starting point so that I can search further?
Thanks in advance.
Regards,
Rashmi
Hello Pinal,
I want to know, if there is any query to do the folowing :
Given a TableName I want to know to which Database does the table belong to ..
If there are “n” database with the same tablename then i want all the “n” database names.
Can you help me ?
Hi,
I am running SQL server 2005 standard Edition on a win server 2003 R2(server is called Computer2). I am trying to setup a log shipping between two different instances of the SQL server on the same machine. The backup portion of the Log shipping takes place flawlessly, but the restore deosn’t take place. I checked the log and saw this error message:
“The log shipping secondary database Computer2\SQLINTANCE1. AdventrureWorks has restore threshold of 45 minutes and is out of syc. No restore was performed for 92 minutes. Restored latency is 0 minutes.Check agent log an dlosgshipping monitor information.”
Any idea?
Thank you for your time.
Hi,
I want to store more then 10000 character data in a single field in single table how it do that. which datatype support
Dear Pinal;
I am new to SSIS. I have developed a package and it works fine on my machine (WIN 2003 server sp2, VS 2005 SP1) when i try to run the same package on another machine it gives me the error “Only part of a ReadProcessMemory or WriteProcessMemory request was completed” i tried to search on the net why this message shows up but there is no clue. please your quick response is highly appreciated
Hi Pinal,
How to move a table from one file group to another file group in SQL Server 2008. Is there any any UI o do this?
With regards
Priyaa Saxena
Sir,
I am developing an application where I going to insert data from temporary database in to new(main) database, and at the same time
I want to delete the record from temp database.
$query=”INSERT INTO new SELECT * FROM temp WHERE id=”;
$query=”DELETE FROM temp WHERE id=”;
Please I need your guidance.
Regards
Shakti D.Magar
Hi Pinal Dave,
I am Srikanth and i am a big fan of your SQL Authority Website. I really appreciate for all the help you have been providing for every one. Now i got a situation where i may need your help and i hope you might be helping me regarding thisssssssssss…….
1st thing..i am not so good as a SQL Server DBA…but i wish to be good…i have a question here and would be great if you can give me some brief details about this….
I have a SQL Server 2000 SP3 in which i have few databases. I would like to upgrade this server to SP4. This is easy, but the problem is this server has just 100MB of C:drive with which we are unable to upgrade to SP4 and i cannot delete any of the data in this Drive…so we planned to move this sql 2000 directly to 2005. Can you give me some details regarding this whether this is possible and if YES what do we need to to do inorder to achieve this GOAL.
Thanks in Advance
Srikanth