Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1300 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.
I am proficient in Corporate Training. I have designed and implemented complex database architecture, and have also implemented strategies for database high availability and scalability. Furthermore, my core expertise lies in query tuning and performance optimization.
If you want to seek my expertise then drop me a line and tell me about your requirements by using the form below or send me email pinal “at” sqlauthority.com. I value development community and will be happy to help you at any stage of project development, from design to deployment.
Copyright violation and Reproduction of blog:
SQLAuthority.com is trademark of Pinal Dave. Exact work “SQLAuthority” or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave. If any article published on this blog violates copyright please contact me, I will remove it right away. Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, which ever is maximum in event of linking to blog (Link is must).
Search SQLAuthority.com
If you have any questions for faster response, Search SQLAuthority.com. It is possible that your question is already answered in one of the hundreds articles.
Community Rules
- Do not post obscene, indecent, hateful, offensive, defamatory, abusive, harassing or profane material. They will removed.
- Do not post junk mail, pyramid schemes, chain letters or advertisements.
- Do not engage in personal attacks. We have zero tolerance for such incidents.
- Vulgar and insulting nicknames will be removed. Nicknames attempting to impersonate other users will be removed.
- Do not post anyone’s telephone number, street address, email address. or any other personal information.
- Do not post copyrighted material.
- Lively debate and opposing opinions are welcome, but please behave courteously.
- All comments are moderated because of heavy spam activities.
pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com
Hi pinal
i have gone through all articles which u hav pblished and am very much impresse with ironical work n here i hav a problem,can u help out
i have to extract data from AR invoice and then i had to populate the data into two targeted tables, i have some validations to be done to the receipts before inserting into tables.. so please can you provide me a package that can do the above work
Thanks & Regards
P Manish Rao
Script for revoking all database roles and Granting Read only access???? for all users
Thanks in Advance
Hi Pinal
We have a scheduled process each night which drops and recreates index’s on one of our main Databases. This in turn causes the transaction logs to flux. As the database gets bigger then this causes the transaction log max filesize to get bigger etc.
I was in the middle of doing some study for exam 70-433 for sql 2008 and noticed an Entry advising that its commen practise to change the recovery model of a database to Bulk logged while certain operations are done which are not intended to be logged.
Just thought to ask your thoughts on this as I suppose it would be something like
a) Doing a full backup prior to Index recreation
b) flick the recovery model to Bulk Logged
c) Drop and recreate the Indexes
Any general thoughts/advise would be apreciated.
Kind Regards,
Matt
How to convert unix time into a regular date?
Thanks.
select dateadd(second,unix_time,’19700101′)
I am trying to SQL 2005 database “backup permissions and indexes” as told to me by our application software company. We are planning to upgrade our database in May to a new version of the application software, and I was told by the software company experts to not only do a full backup of our SQL 2005 database, but to “backup permissions and indexes. Could you tell me how to do that? They suggested using the “ALL TASKS–>GENERATE SCRIPTS” option but the guy wasn’t for sure how to do it because he does not work that much with SQL. Could you help me please? We are going to do the conversion on May 3rd and I need a good backup of our SQL 2005 database including the permissions and indexes, etc.
Thank you very much.
hi Pinal,
I have installed SQL server 2008. Can you pls guide me with the steps to configure Reporting Server 2008.
thanks
-jaya
Hi
I am a DBA and I like to register with your website to keep myself up to date
Thanks Pinal
Hi Pinal,
first of all saying that your blog is really excellent and you’re doing a great job. But I would to expose you a
scenario and how you would avoid the use of nested views (i read out there they are evil but I think the are god). I tell you:
Imagine you have the following tables: BasicSubOperations, Operations, WorkUnitsLevel1, WorkUnitsLevel2, WorkUnitsLevel3. Imagine you have a view for each of the previous tables. A WorkUnitLevel3 can contain several WorkUnitLevel2 and each WorkUnitatLevel2 can contain several WorkUnitsLevel1. Each WorkUnitLevel1 performs an operation and each operation can be compound of serveral BasicOperations. BasicOperations can have a lot of information but image they have a bit field called ProcessInmediatelly. This field has to be propagated from BasicOperations to WorkUnitsLeve3 in the manner that if one BasicOperation has this field to 1 then this field is 1 otherwise 0. And now the question, are the nestedviews justifieds in this scenario for propagating this field ? if no, which are the alternatives (I no triggers but i thing this had to be the last option,no?)
Thanks in advance.
Dear pinaldev,
Here we are using sql 2008 in that ..we are having 2 databases,
and test1 and test2 ,
1. in this my database size is 4 gb and log file size is growing up to 12 gb …but so if it comes near 10 gb my database is going to recovery mode…
2.getting share memmory error and sql disconnecting while working..if restart the service its working for and hour or 2 …if users use more it comes every 30 min or 40 min..
kindly advice me what the reason..
Thanks
Prabhu con
hi Pinalkumar Dave,
Here we r using sql server 2000, i need to lock the my database, can u pls guide me regarding lockin specific database. iam waiting for u r valuable suggestion
Regards,
T.Rajesh Kumar
You need to give us more informations
Do you want only single user to access the database?
Hi
Can we get AdevntureworksDw database in sql server 2005 from any site
I need this databse for SSAS
I have a table where the information is
empId, FName, SName, TName, LName
1,ABC, DEF, GHI, JKL
2,MNO,,,PQR
……..
Another table has the user updated information of the same
empId, FName, SName, TName, LName
1,ABC1, DEF, GHI, JKL4
2,MNO1,,,PQR4
The final output that I require is (when filtered for empno =1)
NewName, Old Data, New Data, updated — (4 new Col Names)
FName, ABC, ABC1,yes
SName, DEF, DEF,no
TName, GHI, GHI, no
LName, JKL, JKL1, yes
all suggestions are welcome.
my plan of action now is
1, create a new table with the fields
in this case a table with 4 fields.
2, fill the table with the information
that will require a field to be retrived at a time, that will be time consuming as the actual requirement has over 50 fields to be displayed.
thanks in advance
Shibu P
Actually it is pivot col to row (problem).
I have a table where the information is
empId, FName, SName, TName, LName
1,ABC, DEF, GHI, JKL
2,MNO,,,PQR
……..
Another table has the user updated information of the same
empId, FName, SName, TName, LName
1,ABC1, DEF, GHI, JKL4
2,MNO1,,,PQR4
……..
The final output that I require is (when filtered for empno =1)
NewName, Old Data, New Data, updated — (4 new Col Names)
FName, ABC, ABC1,yes
SName, DEF, DEF,no
TName, GHI, GHI, no
LName, JKL, JKL1, yes
all suggestions are welcome. I am looking for a better solution than the one I worked out.
my plan of action now is
select ‘FName’ as newName, a.fname as oldData, b.fname as newData
from table1 as a left join table2 as b
on a.empid = b.empid
UNION ALL
select ‘SName’ as newName, a.sname as oldData, b.sname as newData
from table1 as a left join table2 as b
on a.empid = b.empid
….
thanks in advance.
best regards
Shibu P
How many rows are there? How many COLUMN are to be populated?
Hi pinal,
I have sum doubts in writing scripts ,
i have a table like this :
Sl.no module rate date
1 mod1 4 01/04/2009
2 mod1 5 01/05/2009
but what i need is :
sl.no module rate date
1 mod 1 4 01/04/2009
2 mod 1 9 01/05/2009
i want as sum of before one regarding the month and year
because i am finding year to date value.
Kindly help.
Regards,
Haripriya.R.
This is known as “Running Total”. One method is to use quirky update as described here
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx
Thank you madhi sir… if i have any doubts in writing scripts i want to ask u means in which area i have to post my queries…
Kindly advice……
Hello Mr. Pinal Dave
I’m in Vietnam, I’m really like this blog! And I have a quession want to ask you.
I have Parent table with a primary key is PK,
Now, I want delete a record in Parent table, but I want check, if PK is Exists in some Children table then can’t delete that record.
I really set relationship cascade for those talbes.
Have any query to check that issue?
This can be done with normal FOREIGN KEY construct. Child table references Parent table and this prevents any row in Parent table, that has references in Child table, to be deleted. For example:
CREATE TABLE Parent (ID INT PRIMARY KEY)
GO
CREATE TABLE Child(ID INT PRIMARY KEY, ParentID INT REFERENCES Parent(ID))
GO
INSERT INTO Parent SELECT 1
GO
INSERT INTO Child SELECT 1, 1
GO
–This throws an error
DELETE FROM PARENT WHERE ID = 1
GO
Hi Marko Parkkola!
Thanks for reply my question.
But my problem is:
I was set cascate for my tables. So , if I delete from Parent key, then All records in Children (not child) tables will be delete. I want to do this:
IF (!Condition)
/*Delete a record from Parent Table and All record from Children table*/
Else
/*Don’t Delete From Parent TAble, But delete recoreds From Some Children tables in All Children tables of Parent*/
In that: Condition is a expression to check : Whether PK Exists in Children Table
Ah, Okay. Now I got it. There is (at least) two ways to do this.
1) Create “instead of delete trigger” to Parent table. There’s a restriction though that you can’t define instead of delete trigger to table that has FK defined with DELETE action. In the trigger you check for condition and delete rows if needed. Here’s the trigger:
CREATE TRIGGER TRG_Parent_Cascade ON Parent
INSTEAD OF DELETE
AS
BEGIN
DECLARE d CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT ID FROM deleted
OPEN d
DECLARE @id INT
FETCH NEXT FROM d INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
IF (!Condition)
BEGIN
– Delete all children
DELETE FROM Child WHERE ParentID = @id
– Delete parent
DELETE FROM Parent WHERE ID = @id
END
FETCH NEXT FROM d INTO @id
END
END
2) Create procedure to do all this.
CREATE PROCEDURE CascadeDelete
@ParentID INT
AS
BEGIN
IF (!Condition)
BEGIN
– Delete child
DELETE FROM Child WHERE ParentID = @ParentID
– Delete parent
DELETE FROM Parent WHERE ID = @ParentID
END
END
Thanks Marko Parkkola!
hello ,
how many instance use in sqlserver 2005?
Regards
kalyan
1)FOR 32BIT SYSTEM
50 instances on a stand-alone server for all SQL Server editions.
SQL Server supports 25 instances on a failover cluster.
2)FOR 64BIT SYSTEM
50 instances on a stand-alone server.
25 instances on a failover cluster.
I am using Access pass through queries to run stored procedures and return tables to Access. The queries use system DSNs, SQL Server authentication, the ‘sa’ user ID and associated password.
After fifty or so runs, the DSNs appear to not connect any longer and the Access pass through queries (set not to timeout) just hang indefinitely.
The SQL database tables involved are relatively small, and work fine for limited periods of time.
If I use manually reset the ‘ODBC Connect Str’ in each of the pass through query properties and restart my computer, the queries seem to work again for – again – perhaps fifty or so runs.
Are you aware of any similar situations or do you have any guesses as to what the issue might be?
I’ve read the post below and had hoped that it was the issue, but it doesn’t appear to have been the issue.
http://blog.sqlauthority.com/2009/04/23/sql-server-fix-error-18486-login-failed-for-user-sa-because-the-account-is-currently-locked-out-the-system-administrator-can-unlock-it-unlock-sa-login/
Just wanted to throw a post up here and ask for anyones experience with Idera’s SQL Safe. We are in the process of possibly purchasing a SQL compression and encryption software and its between Idera and Red Gate. Any information or insite would be appreciated
Thanks
Steve
Following is my stored proc :-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE[dbo].[mob_cfas_final]
as
BEGIN
DECLARE @mobility CHAR
DECLARE @cfas CHAR
DECLARE @cursor_company_site CURSOR
Print ‘tblsite conversion begins’
SET @cursor_company_site = CURSOR FOR
SELECT companycode FROM CinguLINC..tblSite
OPEN @cursor_company_site
FETCH NEXT
FROM @cursor_company_site INTO @mobility
WHILE @@FETCH_STATUS = 0
Begin
SET @cfas = (SELECT cfas_companycode FROM tblMobCfasCompanyCodeMapping WHERE mob_companycode = @mobility )
select @cfas
if (@mobility is not null and @cfas is not null )
update CinguLINC..tblSite set companycode = @cfas where companycode = @mobility
FETCH NEXT
FROM @cursor_company_site INTO @mobility
END
CLOSE @cursor_company_site
DEALLOCATE @cursor_company_site
Print ‘tblsite conversion ends’
DECLARE @cursor_pymnttypelkup CURSOR
DECLARE @mobility1 Float
DECLARE @mobility2 CHAR
DECLARE @cfas1 CHAR
DECLARE @cfas2 CHAR
declare @a char
declare @b char
Print ‘tblpaymenttypelkup conversion begins’
SET @cursor_pymnttypelkup = CURSOR FOR
SELECT account,costcenter FROM tblPaymenttypeLkUp
OPEN @cursor_pymnttypelkup
FETCH NEXT
FROM @cursor_pymnttypelkup INTO @mobility1, @mobility2
WHILE @@FETCH_STATUS = 0
Begin
set @cfas1= (SELECT cfas_account FROM tblMobCfasAccountMapping WHERE mob_account = @mobility1 )
select @cfas1
SET @cfas2 = (SELECT cfas_costcenter FROM tblMobCfasCostcenterMapping WHERE mob_costcenter = @mobility2 )
select @cfas2
if (@mobility1 is not null and @cfas1 is not null )
update tblPaymenttypeLkUp set account = @cfas1 where account = @mobility1
if (@mobility2 is not null and @cfas2 is not null )
update tblPaymenttypeLkUp set costcenter = @cfas2 where costcenter= @mobility2
FETCH NEXT
FROM @cursor_pymnttypelkup INTO @mobility1, @mobility2
END
CLOSE @cursor_pymnttypelkup
DEALLOCATE @cursor_pymnttypelkup
Print ‘tblpaymenttypelkup conversion ends’
DECLARE @cursor_vouchertemplate CURSOR
DECLARE @mobility3 CHAR
DECLARE @cfas3 CHAR
Print ‘tblvouchertemplate conversion begins’
SET @cursor_vouchertemplate = CURSOR FOR
SELECT account,company FROM tblVoucherTemplate
OPEN @cursor_vouchertemplate
FETCH NEXT
FROM @cursor_vouchertemplate INTO @mobility1, @mobility2
WHILE @@FETCH_STATUS = 0
Begin
SET @cfas1 = (SELECT cfas_account FROM tblMobCfasAccountMapping WHERE mob_account = @mobility1 )
select @cfas1
SET @cfas2 = (SELECT cfas_companycode FROM tblMobCfasCompanyCodeMapping WHERE mob_companycode = @mobility2 )
select @cfas2
if (@mobility1 is not null and @cfas1 is not null )
update tblVoucherTemplate set account = @cfas1 where account = @mobility1
if (@mobility2 is not null and @cfas2 is not null )
update tblVoucherTemplate set company = @cfas2 where company = @mobility2
FETCH NEXT
FROM @cursor_vouchertemplate INTO @mobility1, @mobility2
END
CLOSE @cursor_vouchertemplate
DEALLOCATE @cursor_vouchertemplate
Print ‘tblvouchertemplate conversion ends’
DECLARE @cursor_summaryvouchertemplate CURSOR
Print ‘tblsummaryvouchertemplate conversion begins’
SET @cursor_summaryvouchertemplate = CURSOR FOR
SELECT account,company,costcenter FROM tblSummaryVoucherTemplate
OPEN @cursor_summaryvouchertemplate
FETCH NEXT
FROM @cursor_summaryvouchertemplate INTO @mobility1, @mobility2, @mobility3
WHILE @@FETCH_STATUS = 0
Begin
SET @cfas1 = (SELECT cfas_account FROM tblMobCfasAccountMapping WHERE mob_account = @mobility1 )
select @cfas1
SET @cfas2 = (SELECT cfas_companycode FROM tblMobCfasCompanyCodeMapping WHERE mob_companycode = @mobility2 )
select @cfas2
SET @cfas3 =(SELECT cfas_costcenter FROM tblMobCfasCostcenterMapping WHERE mob_costcenter = @mobility3 )
select @cfas3
if (@mobility1 is not null and @cfas1 is not null )
update tblSummaryVoucherTemplate set account = @cfas1 where account = @mobility1
if (@mobility2 is not null and @cfas2 is not null )
update tblSummaryVoucherTemplate set company = @cfas2 where company = @mobility2
if (@mobility3 is not null and @cfas3 is not null )
update tblSummaryVoucherTemplate set costcenter = @cfas3 where costcenter= @mobility3
FETCH NEXT
FROM @cursor_summaryvouchertemplate INTO @mobility1, @mobility2, @mobility3
END
CLOSE @cursor_summaryvouchertemplate
DEALLOCATE @cursor_summaryvouchertemplate
Print ‘tblsummaryvouchertemplate conversion ends’
END
It gives me an error that -
Msg 512, Level 16, State 1, Procedure mob_cfas_final, Line 88
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
Please let me know what is syntax for cursor returning multiple vlaues
Hi,
Mr. Pinal Dave
I am searching DB Email and sp_send_dbmail and i got ur block. It’s really a nice blog..
I faced a problem when i m solving one of my issue.
Scenario: Everyday i need to update Trail User password and send it to admin. So I create a SP (Update password & send email using sp_send_dbmail) and a Job which run the job.
But the problem is, if the DBemail is down or problem with Internet, the password is updated but no email send.
So, Is there any way to know the status of email and restart the job if the email is not sent.
Regards
Animesh Chandra Dey
See if you can find it in the table msdb..sysjobhistory
Hi,
I have tables which says names employees and department.
Employees have (empid, ename, deptid, mgrid)
eg: filling table like this
Row –>(12, Paul, 01, 34)
Row –>(34, Mike, 02, 56)
Row –>(56, Pinal, 01, 78)
Department have (deptid, deptname)
eg:
Row–>(01, Development)
Row –>(02, Testing)
Now please help me to retrieve information of the particular employee say empid 12 in a manner like this
[12(empid), PAUL(ename), Development(deptname), mike(ename)(is direct mgr to paul), mgr's deptname, mgr's mgrname, mgr's mgr deptname..... ]
Can it be possible to get it in one query.
I tried using self Joins and joins but i could not able to retrieve mgr’s mgr information and mgr’s mgr’s mgr information. Please do favour to me.
Thank you in adv.
Yours truly,
Confused student.
@Paul
You cannot return an undetermined amount of COLUMNs. Even PIVOT() requires a known list. Though, if was really needed, you could determine how many COLUMNs are required and build the statement dynamically. Or you could have dummy placeholders for possible levels of management. Neither of which is usually considered good practice.
Ideally, each would be returned on it’s own row. Is there a reason you need them all in the same record?
Why Ole Automation Procedures are in disable mode Defaultly.
Does it causes any issues if i enable them on Production environment.
Hi Brain Tkatch,
I appreciate your answer dear. There is nothing reason. I just wanted to confirm about it. Cause I was asked (sorry forced) to do it in my application at office, i said it would be difficult but still they asked so i was confused. Thank you.
@Paul
Glad i could be of service.
It’s nice to hear more of the story. :)
You may need to read about Common Table expression in SQL Server help file for more examples
Hello sir,
How can i ensure sql server agent service can access the entire log server .
from which of these can i do it
remote Service account.
remote System account.
Local Service account.
Domain account.
pls explain for the specific answer as m new to SQL Server
thnx
Hello,
This is Niikunj Panchal. I am installing SQL Server 2008 on Windows 7 64 bit home premium. but it gives me the error: ”
Microsoft .NET Framework 3.5 installtion has failed. Sql server 2008 requires .NET framework 3.5 to be installed.”
When I tried to install Microsoft .Net Framework, it gives setup error. i found some information on net this shows that Microsoft .NET framework is already installed with Windows & 64 bit home premium.
Can you please give me the exact idea, it’s very important for me to install SQL server 2007 on Windows 7.
I am waiting for your reply.
Thank you.
Nikunj Panchal.
Hi,
I can feel your pain. I’m trying to install VS2010 Express to Windows XP and keep hitting errors every two minutes.
But I googled around a bit and found this KB article about Sql Server 2008 installation issues. I hope it helps.
http://support.microsoft.com/kb/955725/EN-US
I think you could try to install .NET 3.5 Service Pack first, then Sql Server 2008.
when I take backup of any database on sql serve 2008, i got error msg like,
“Property BackupDirectory is not available for Settings ‘Microsoft.SqlServer.Management.Smo.Settings’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)”.
Did you try using backup database command in Query analyser?
Hi Pinal,
Actually i want to generate a excel file with all tables from database my sql server 2005 with table headers but not the table data.
i.e only table description of each and every table.
if you could help me in this it would be greatful.
thanks
rita
Select * from information_schema.columns
Make use of the query and export the result to excel
Hi Madhivanan,
thanks. this really worked for me.one more thing is if also want get all the procedures like this waht i have do ? Hope you help me in this regard.
once again thanks alot for your help.
thanks
rita
Use this code
Select * from information_schema.routines
I hate to bother you with what should be something simple for me to do. I decided today that I would like to download the AdventureWorksDB database and install it on my matchine. I’m running a SQL Server 2005 Express Edition on my Windows Vista 64-Bit Edition.
For one reason or the other I am not able to attach the database due to an (Access Denied) Error. This seems very odd to me since I am an ADMIN user on the machine and the folder where the database is located is SHARED as well.
The main thing I notice is that my Data folder is located in a Program Files (x86) folder instead of the normal Program Files folder. I can’t image that would cause a problem, but I’m not able to get past it. I’ve never had a problem such as this, so that’s probably why I’m not getting anywhere right now.
I get the following error after attempting to Attach the files.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\DB\Server\Samples\AdventureWorks_Data.mdf”. Operating system error 5: “5(Access is denied.)”.
My T-SQL Script is as follows:
exec sp_attach_db @dbname = N’AdventureWorks’,
@filename1 = N’C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf’,
@filename2 = N’C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf’
Would you mind pointing me in the right direction so I can resolve this issue?
By the way, I like the information you provide on your web-site. Your information and the site is well done. Good job.
Thanks, Greg
SQL Server 2008 server named
ITEXAMWORLD-DB1. There are also SQL Server Agent jobs on the server. Furthermore the
database is backed up at a daily
basis. One morning, the master database on ITEXAMWORLD-DB2 went down. You then restore and
rebuild the database. The Itexamworld.com managers want ITEXAMWORLD-DB1 to work as it was
in
the past.
What should you do?
A. You should consider rebuilding the entire SQL Server Web Service for the database.
B. You should consider reserving the model and master databases for the database.
C. You should consider reserving the Log Transaction for the database.
D. You should consider rebuilding the entire table schema for the database.
pls let me know the answer as m new to SQL SERVER
Can anybody link me to free MCTS 70-432 Study material too
Hi,
One interviewer asked me if any new requirement forces us to make a child table with primary and Foreign key relation, we can create one child table. But if we keep on going for creating new child tables like that whenever new requirement comes that is after 20 years or something,
What is the best way to create or design a database for considering future requirements also.
How can we make database design useful for future requirements also.How we can make database flexible. He also said there is a straightforward answer for this question. If i answer this question my job is going to confirm.
Please let me know as early as possible the answer for this questions.
Sincerely
Murali Krishna.
You need to read about Normalisation for better understanding
Search them in Google/Bing
Hi Pinal,
your articles about SQL are very interesting.
Congratulations for your knowledge.
Bye
Marcus Linares – Brazil
Hi Pinal,
I installed AdventureWorks 2008, but I could not use it becuase I could not find the backup file. Also, I tried to attach it, but I failed .
Ali
Best Wishes
Triggers fire once per statement execution, not once per row.
i have wrote a trigger that should fire on each row insert.
but it’s fire only once when i import data from a csv file or bluk insert statement.
how can i fire trigger on each row insert.
thanks
shahid
You need to include the option Fire_triggers when you use BULK INSERT command
oo sorryi forget to mention that i am using mssql server 2000.
I wnt the window group user can connect to the sql server instance at specific time only ie from 10 to 11 pm and not at other time
let me know hw can i do it ,with reason .
AS m new to sql server
Dave,
I just wanted to take the time to thank you for all the help I have received from you via your website.
I am fairly new to the database environment. I switched jobs from a Network Administrator to a DBA. Our shop is Oracle, but I was given the job of bringing Sql Server into our environment. Your knowledge and suggestions have helped me imensely.
Thanks SO much for all your help !!!
Leroy Larson
DBA
State of Minnesota, Dept of Transportation
Dear Pinal,
I need you expert help for my problem in SLQ Server.Below is the text for the problem
hey i have my data in the following format in .txt file. There are more than 1000 line, but here i have pasted just 3 for demonstration of porblem.
Line 1:
000000004998152225|6783|1000|E|1110006042|000010|2006|03| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060217
Line 2:
F01U002255 |6783|1000|E|1110003940|000010|2006|03| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060217
Line 3
000000004998151567|6784|1000|E|1110006225|000090|2006|02| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060216
I wrote following script to copy this data from .txt file to table in the server
[db_scpm-sql].[testing].[mska3] in “Z:\40_SCPM\80_SQL-server\80_Implementation\90_testdata\MSKA.txt” -c -t”|” -T -S FE0DBP97
but here it give me the following error. I tried with the format file option also, but it gives the same error.
Starting copy…
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca
st specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca
st specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca
st specification
It would be so kind of you, if you would spend your some valuable time and help me.
Any kind of help will be highly appreciated.
Thanks a lot.
Regards,
Vishal Patel
Hi,
Can anyone please let me know if there are any discounts for Microsoft certifications. I am planning to take MS 70-432. Also, if you can let me know the materials I should refer to study for this exam.
Thanks
Hets
Hi Pinal..
I have serious problem while installation of Sqlserver 2008 in windows 7 (x64) home Premium..
First I have installed VS2008(Ent-US) and updated VS2008 SP1..
Then i started installing Sqlserve 2008 (Ent edition).. it is asking “Sqlserver 2008 required .Net Framework 3.5 SP1 required” I have download from microsoft site..
I started intalling the “dotnetfx35setup.exe”. the exe is extracting and after there is updates…
Second problem..
I tried with sqlserver 2005 developer edition..installation is going fine.. even i enabled the IIS (windows 7).. due to this reporting services are not enabled while installation…
thanks for your help in advance….
Thanks
umamahesh
Hi Dave,
Good morning. I read many of your articles. It is very much useful. I have a doubt. How can I implement set identity insert on option in all tables in a database using “sp_msforechtable” sp.
The reason:
I need to refresh table from SQL 2000 to SQL 2008. For that I tried this option before doing DTS Import from SQL 2000 to SQL 2008. But the option is not working. When I check in the DTS Wizard, the option is not selected. I manually select the option in all tables.
Thanks & Regards,
Balavenkatesh
Hi Shahid,
Trigger fired per statement. You don’t need to fire trigger after each row insert/update.
You just need to use inserted table, which you can access in Trigger.
This table gives you each row that you want to process.
Tejas
SQLYoga.com
Hi Pinal ,
I read your articles regularly, these are really informative, keep it up.
I never read about Load Testing of SQL SERVER Database
How can we Load Test a SQL SERVER Database ?
Regards,
M.Zafar Iqbal
Hi
I have two databases DB1 (used by my application) and DB2 (third party) under same SQL Server instance
I get the following error
SqlException ERROR: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
connecting to my database (DB1)
To fix this i used below
To assign a TCP/IP port number to the SQL Server Database Engine
In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for , and then double-click TCP/IP.
In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.
If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.”
Once I deleted those zeros and put in 1433 for TCP ports on each IP address DB1 works fine, but cannot access DB2 (third party)
So basically what is the default settings 0 or 1433??
how do i get my DB work with default TCP/IP settings
Thanks
Gauls
Hi Pinal,
I need your kind help.
I have two big tables in MS SQL database, i need to change column from int to bigint. Both tables have PK and it is not allowing me to alter column directly.
Is there any method i can use to change cloumn type? please help. Thanks in advance!
Regards,
Ratna Kumar.
Hi Ratna,
If table is too large it creates a problem. You need find alternate. You cam do this by:
1. Add new Column Called: Temp
2. Update new column “Temp” with your column
3. Rename yourcolumn (e.g. “TempDelete”)
4. Rename “Temp” column to your actual columnname.
This way it will allow you to do so.
Tejas,
SQLYoga.com
Hi Pinal
1.Sql server upgradation before or after how to find out Repication data .
2.In my log shiping stand by server Log file Corrupted
to resolve the problem
please tell …sir
Thanks&Regrads
harishkumar.M
dear,
i am trying to install SQL server but it is giving me the following error…
the sql server system configuration checker cannot be executed due to WMI configuration on the machine …. Error:2147749907 (0*80041013)
Pinal,
Is there any way to put multiple Else block in Case statement.
CASE expression
WHEN expression1 THEN do something
WHEN expression2 THEN do something
ELSE
some code
ELSE
some code
END
You need to use mor WHEN expression option
Good Site for DBAs.
Hi Pinal
1.Sql server upgradation before or after how to find out Repication data .
2.In my log shiping (Secondary )stand by server Log file Corrupted,how to to resolve the problem
3.in logshipping secondary server transcation log file Corruted and how to Restore the secondary server.
please tell …sir
Thanks&Regrads
harishkumar.M
I have been regular visitor to your blog and it has helped me lot in difficult situation.I keep my sql server knowledge updated by viewing your blog.
I took a question about clustered Index which had choice between two given answers
A>Rows in a table are PHYSICALLY stored in the clustered index order
B>Rows in a table are LOGICALLY stored in the clustered index order.
I checked the first answer i.e A but the correct answer is B.Can I know how answer B is true?Since we all know that the data in a clustered index is stored in physical order.
hii pinal
i need your help…
this is my table named tbl_subcode
subject subjectcode
————————————————– ———–
english 1
maths 3
science 4
i am having another table named tbl_markdetails
addno subjectcode marks
——— —————– ———
12340 1 52
12340 3 100
12341 1 90
12341 3 99
i need the result as
addno english maths
————- ———- ———–
12340 52 100
12341 90 99
is it possible to get the result like this?
if yes answer as soon as possible…
thanks in advance….
Hi
Can you try this query,
Select AddNo,[English],[Maths],[Science]
From
(Select A.AddNo,B.Subject,A.Marks
From tbl_MarkDetails AS A, tbl_SubCode AS B
Where A.SubjectCode=B.SubjectCode)PS
PIVOT
(
Max(Marks)
FOR Subject IN
([English],[Maths],[Science])
)AS PT
Thanks
For unknown number of subjects, you need to use dynamic pivot. Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Hi Madhivanan,
Thanks for informations
Hello Sir
I have aws amazon server. When i want to take backup of my database on (d,e,f) drives then it shows me following error.
Backup failed for server ‘****’. (Microsoft.SqlServer.SmoExtended)
System.Data.Sql.Client.SqlError: Cannot opem backup device ‘E:\foldername\filename.bak’ Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)
But on c drive it successfully worked.
hi pinal..
i want to send data from one table to another in a asp.net project what will be the query.?
kindly reply
insert into target_table(column_list)
select column_list from source_table
hello Admin
I have littel problem in creating trigger
i create table Employees which include four filed
EmpName Sex Age Remark
Amylee Female 45 Over Age Emp
Jack Male 19 Young Age Emp
i create trigger which automaticlly give Remark
create trigger emp on employees
for insert
as
if (select age from inserted)>20
begin
update employees
set status=Over Age Emp’
where age >=50
end
Else
if (select age from inserted)<20
begin
update employees
set status='Young Age Emp'
where age <=50
end
This trigger not Work it update all data filed it give Remark
"Over age Emp" for all emp which have under age 20 and over 20 it give same remark
You dont need a trigger
You can use it in a select statement
select columns,case when age>50 then ‘Over Age Emp’ else
‘Young Age emp’ end as status from your_table
For my ASP.net Application on a hosted MSSQL server, i have had to re-ceate using the scripts generated from my local machine using sql tools with SSMSE. This worked beautifully and all appears to work. My ASP.net application users can create their accounts and login normally. Unfortunately, my create user wizard does not automatically generate roles for the clients. So, i am using a routine that includes the following
Protected Sub CreateWizard1_OnUserCrated(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
Dim userInfo As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)
userInfo.IsApproved = False
Membership.UpdateUser(userInfo)
Roles.AddUserToRole(CreateUserWizard1.UserName, “Friends”)
End Sub
which does not work because it is looking for a AddUserToRole stored procedure. This is confusing because it this SP IS on my local version and works fine. So i attemtped to create a new SP called AddUserToRole on the server but get an error message
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 49
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (actually it occurs 5 times in the routine).
Thanking u in anticipation for reply
Hi in my case the exact error is
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 45
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 52
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 76
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 83
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 90
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
I have littel problem in creating trigger
i create table Employees which include four filed
EmpName Sex Age Remark
Amylee Female 45 Over Age Emp
Jack Male 19 Young Age Emp
i create trigger which automaticlly give Remark
create trigger emp on employees
for insert
as
if (select age from inserted)>20
begin
update employees
set status=Over Age Emp’
where age >=20
end
Else
if (select age from inserted)<20
begin
update employees
set status='Young Age Emp'
where age <=20
end
This trigger not Work it update all data filed it give Remark
"Over age Emp" for all emp which have under age 20 and over 20 it give same remark
You have already asked this
See my previous reply
hi i want to convert table data in to XML format after that i want to insert that xml format data into specific table column in sql server 2000
so it is possible i want to make it for dynamic query ——————sharad
hi pinal i want to write a script to import tables in one database into another database, can you please me in this task…………….
Use Import/Export wizard from Management studio
hi thanks fo your help but iam unable to see the import/export wizard in my sql server 2005 can u suggest me in this n i need to write a script for:
iam having a db ERPDB datbase, now i need to write a script for the tables in the databse. so that if i run that script in another databse i should get all the tables that are in ERPDB databse.
Right click on your database
–>tasks–>Export data
and follow the instructions
hi thanks for ur reply but iam unable to find export data in my sql server
please help me out in this issue thanks
Do you want strcurures only or with data?
i need data including structures, earlier only i have asked you regarding writing script for a database(which is in sql server 2005), so if i excute that script in another database i should get the tables that are in the earlier database.
You need to take a backup and restore it in another db
Hi Pinal,
I am extracting data from DB2 into SQL Server 2005 using IBM OLEDB provider for DB2. Source table have only 2 columns both are decimal in type. It has only one row for testing. While extracting data using SSIS I am getting following message
SSIS package “Package1.dtsx” starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0×40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0×40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
SSIS package “Package1.dtsx” finished: Canceled.
The program ‘[3796] Package1.dtsx: DTS’ has exited with code 0 (0×0).
hi
I have this concern. The mdf of my database grows fast. From 500 mb to 18 gb in short period of time. Hope you can remedy my concern.
Thank you
Because data are added. If you have old data that you dont want, take a backup and delete them
in just a week it grows faster..from 500mb to 18gb
Hi Pinal,
Few days back i was doing some crazy stuff in my SQL server Management studio, I wanted to create a SP which will delete itselft.. I did something like,
CREATE Procedure usp_self_delete
AS
BEGIN
DELETE usp_self_delete
END
I thought SQL server will throw error upon executing the above created SP. But to my surprise.. it didnt.. It actually deleted itself. So i was wondering how it can happen?
Can you please give some light on it.
Regards
Taher
You can’t delete a procedure
What are you trying to do?
You have created only the procedure
Run it using
EXEC usp_self_delete
you will get the error
hi taher i have gone through the code u have written, i have executed it but it havnt deleted.
regards,
sridevi
I have successfully executed the code… in SQL server 2008.. and it is actually deleting itselft.. I am able to run the SP only for the first time, next time when i run it.. it shows object does not exits error.
Hi Pinal ,
I have to call a UDF from serv1 (SQL Server) to Serv2 (SQL Server). I set up a linked server on Serv1 and trying the following command on serv2. The link server is properly tested and the user is sysadmin, so no permission issues.
UPDATE TABLE1
SET COL1 = EXEC SERV1.DBNAME.DBO.UDF(@TEST)
Please advise. thanks in advance.
You dont need to use EXEC command
Remove it from the query
Hi Pinal,,
I have the following Question:
If you were integrating a feed of end of day stock price information (open, high, low, and closing price) for 5,000 companies, how would you do it? You are responsible for the development, rollout and ongoing monitoring and maintenance of the feed. Describe the different methods you considered and why you would recommend your approach. The feed would be delivered once per trading day in a comma-separated format via an FTP site. The feed will be used by 1000 daily users in a web application.
Hi Pinal:
Need small help of yours
I need to duplicate a row having [name] column. Duplicate row should have like [existingname]Copy1
I need to increment variable value in procedure, i know that can be done by using SET @i = @i+1
But i need to keep checking continously if this name already exists in that table. Below example wil make things bit clear
declare @i int
set @i=1
if EXISTS(select [name]+’Copy’+cast(@i AS varchar(50) from tablename where userid=@UserID)
begin
set @i=@i+1
end
this will yield in ‘nameCopy2′ as result but how i can continously check in loop if next incremented value already exist in table.
any help will be greatly appreciated
Please advice, Thanks
You dont need to use a loop
Try this code
select name, 'copy'+cast(sno as varchar(10)) from
(
select row_number() over (order by name) as sno,name from table_name
where userid=@userid
) as t
Thanks Madhivanan,
Sorry i saw your post little later as got my solution earlier.
But It is good way of catching the row_number, i’ll keep note of this as well..
Thanks
Dear Pinal Sir,
I want to clear SQL SERVER Certification.
Pls guide …..
Dear sir,
I am using Sql Server 2005.
I have 2 databases
one is “olbDatabase” which contains live data and this database has not any relation ship.
The second database is “newDatabase” which has relation ship
I want to import data from oldDatabase to newDatabase without dropping constraints but, I get readonly column error.
I can not import live data in to newDatabase…
Please help me its urgent!!!
Thanks…
Nitesh Parmar from Gandhinagar (Gujarat)
Hi
sir is there any way to get specific row from a result set.My result set have 20 row then can i get 2 nd row only as result from that query
thanks in advance
Anoop
There is no concept of first row, second row, etc in relational database. You need to order it by a column and get it
select * from
(
select row_number() over (order by col ) as sno, * from table
) as t
where sno=2
Hi pinal,
I have gone through some of your articles which u hav published and am very much impresse with ironical work n here i hav a problem,can u help out
How to run an SCRIPT IN SQL SERVER 2005? Please can you explain step wise. After executing the script, in next step what i should do. This script we are going to execute in prod environment.
please help me out ASAP.
Which script are you talking about?
Give us more details to help you
Hello mr. Pinal Dave
I have this Script:
CREATE TABLE tblTempl
(
name CHAR(12)
)
GO
INSERT tblTempl VALUES
(‘abc’),
(‘asc’),
(‘dhs’),
(‘wegweg’)
GO
//First Query
SELECT *
FROM tblTempl
WHERE (name like ‘%’ + ” + ‘%’) (1)
GO
//Second Query
DECLARE @e CHAR(12) = NULL;
SELECT *
FROM tblTempl
WHERE (name like ‘%’ + ISNULL(@e,”) + ‘%’) (2)
GO
DROP TABLE tblTempl
I don’t understand why (1) and (2) not match!, Please help me.
Becuase by default isnull returns the datatype exactly what the parameter has. So you get char(12) ie 12 spaces
Use coaesce in place of isnull
Dear Pinal
Amazed at your work.
Niraj Bhatt was my trainer at Ardent collaborations in Bangalore. I am a kenyan and I am sure he remembers me.
I do some projects in. NET Framework 2.0. However there are areas I would like to make my skillset very strong such as
XML, Java scripting and AJAX. Programming platform is VB.NET and NOT C#. Coming from VB programming.
A trip to your organization is what I am looking forward to..
Kindly advice.
Regards
which is the best option while bulk inserting large amount of data(more than 10 million records)? Disable and enable the Indexes (or) Drop and recreate the Indexes
Srivivel,
The best option would be to Drop and recreate the indexes. I think you cannot enable the cluster index after disabling it, infact you will have to recreate a cluster index.
Thanks
Vishal
Hello mr. Pinal Dave,
I’m migrating an Access database to SQL Server.
I need to migrate some columns (but not all off them) from the Access file. I connected the Access file as a linked server. When I start querying it gives me the result that I expect. No problems so far but …Why is following query not working:
SELECT * — need some columns here only
— so I have to replace the *
INTO #MyTempTable
FROM EXEC(sp_columns_ex,
@table_server = N’ACCESS_MIGRATION’,
@table_name = N’ElsAangifte’)
I used the stored procedure “sp_columns_ex” because this one gives me the results I need.
Thanks for your reply in advance and I hope you give a solution.
Kind regards
Edward
You need to make use of the method 2 that uses OPENROWSET for this
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/24/select-columns-from-exec-procedure-name-is-this-possible.aspx
I made a Stored Procedure as Process with Table index when i execute it on my local PC it takes 50 seconds for 27000 records but while execute on Database Server it takes 18 minute . Why ?
Note: same Structure is working fine another PC except Database Server.
Can anyone help me what shoud i do ?
Thanks
Hi Pinal Dave,
Firstly thank you very much for sharing your knowledge on SQL on your site/blog
My question is: i have to compare a datetime and a date field, for this i have 2 options, i would like to know which one is lest expensive. the table has millions of rows and unfortunately the datetime field is not indexed
for example:
declare @date date
set @date = getdate()
– Option 1 (using cast or convert)
select * from sysobjects where cast(crdate as date) = @date
– Option 2 (using datediff)
select * from sysobjects where datediff(dy,crdate, @date) = 0
i would assume datediff will use more resources, please advise (PS: i’ts a sybase db)
This may be more effecient
Compare the performance with
select * from sysobjects
where
crdate>=dateadd(day,datediff(day,0,@date),0) and
crdate<dateadd(day,datediff(day,0,@date)+1,0)
is’nt running functions for every tuple, more expensive?
if i evaluate the cost, it goes as follows:
crdate>=dateadd(day,datediff(day,0,@date),0) and
crdate<dateadd(day,datediff(day,0,@date)+1,0)
– 4 function executions & 3 comparisions per row
datediff(dy,crdate, @date) = 0
– 1 function execution & 1 comparision per row
cast(crdate as date) = @date
– 1 typecasting & 1 comparision per row
i have millions of rows to query on, and currently do not have any performance meassurement tools, so i would like to make some logical decisions
You wont see performance difference if there is no index on crdate
If there is index on crdate, your methods use function over it thus causing index scan than seek
My method applies function over the date variable thus result to index seek
unfortunately the field is not indexed so it will have to scan,
but i will keep in mind if i have to query an indexed field in the future
wanted to know if any optimization could be done, as its a very heavy overnight batch
thanks Madhivanan
Try the method I posted
It must be slightly faster than your approach though there is no index on the column
Hi Pinal,
I am planning to create a view and retrieve data from the view instead of a table. My main goal is to eliminate locking onthe table. It is a simple SELECT 4 columns with (NOLOCK) query that issues Sch-S (schema stability) lock on the table. My goal is to eliminate Sch-S lock on the table. If I create a view (select 4 columns)on the table and retrieve data from the view, will it eliminate the lock from the table. I read that a view only adds performance hit since every time I run a query that references a view, db engine will have to cosntruct a virtual table (view) from the base table, meaning query the base table anyway.
Can you please confirm if this is true?
Thank you very much in advance,
NS
Hi Pinal
I got and query. I believe you got the solution :)
Here is the problem
————————–
I have a database in sql 2005 with 10 Lac tuples
My front-end application is designed in .net c# (aspx)
when no application is running the sqlservr.exe show 20MB of memory usage in task manager.. but eventually when we start using application the memory usage of sqlservr.exe goes upto 1.8 GBBB .. what’s the issue here.
after this Con.open() command fails to connect the application to sqlserver 2005
Pls send me a solution.
Regards
Gurpreet Singh
Hi Pinal,
First of all Congratulations on your excellent knowledge in SQL Server.
I have a question after reading some of the posts on indexes (Covering Indexes). All the posts say that if a covering index does not exist on the Select Columns a look up is done. So now my questions is if I do a select * then will a bookmark look up definitely appear in the query execution plan?
Please comment
Thanks,
Vishal
Hello Sir
I am a student of MCA, read notes from your website. thank you for the notes these are usefull to me. But i have a confution i. e. I tried to execute the query “Select rows from sysindexes where id=OBJECT_ID(table1) and indid<2" I use table name "emp" at the place of "table1" but when I am execution this query its showing error "invalide column name emp" so can you tell me where is the problem and how can i use this query? Please guid me??
And can you tell me how can i get Rowid from a table as we use Mysql and Oracle.
Thank you Sir
Use the table name within single quote
Hi pinal
Please help me with the following issue I am getting:
The server principal “content_ingestion” is not able to access the database “CMSUSERLOGS” under the current security context.
waiting for your reply urgent
rajeev
Dear Pinal,
my name is Ramakrishna and I am new to MS SQL technology. my aim is to do certifications for MS SQL technology(ADMIN, development after that BI). I am looking for some resources to to learn and I found CBT Nuggests videos. they are quite good as a stating point but I am also looking for some resource for practicing the exams questions before I take final exam.
can you please provide me some resources, preferebly opensource resources that willl help me to do MS SQL 2008 certification exams. I would be very happy if you can suggest me that.
with regards,
Ramakrishna Paruchuri
From Germany
hi pinal sir,
i am working as a sql dba. so i need ur guidence which book i should prefer and how i can increase my knowlede.
beacause i never get seniors to learn from them.
thanks & regards
Hi Pinal,How tempdb gets its intial size .I am sure that it wont get it from model database.But where this intial size will be stored when server restrats.
How can we find intial size using the T-SQL.
Thanks,
DK
Hi Pinal
I need to send an email from a stored procedure in sql server 2008 . I came across the option of database mail in one of your articles(http://www.codeproject.com/KB/database/SQLServer2008DatabaseMail.aspx)
Are there any disadvantages for this method?.From what I have read and undestood its better compared to sqlmail.Kindly suggest whether database mail is a good option.
One of my friends suggested to call an SSIS package from the stored procedure. This SSIS package will send the mail…But this idea seems a bit lengthy..i would like to know ur views as to which option is good…and in particular whether database mail has any disadvantages?
how to add values from textbox in database table in sql server 2005 at run time
i have written foll code for the same bt its not working
if (con.State == ConnectionState.Closed)
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “insert into table1(personnel_no,password) values(@1,@2)”;
cmd.Parameters.AddWithValue(“@1″, int.Parse(TextBox1.Text));
cmd.Parameters.AddWithValue(“@2″, TextBox2.Text);
cmd.ExecuteNonQuery();
bindgrid();
con.Close();
how to add values from textbox in database table in sql server 2005 at run time
i have written foll code for the same bt its not working
if (con.State == ConnectionState.Closed)
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “insert into table1(personnel_no,password) values(@1,@2)”;
cmd.Parameters.AddWithValue(“@1″, int.Parse(TextBox1.Text));
cmd.Parameters.AddWithValue(“@2″, TextBox2.Text);
cmd.ExecuteNonQuery();
bindgrid();
con.Close();
Hello,
adding a target server into a master I do the following steps:
1) On Target server:
Right click on agent -> Multi Server Administration -> Make this a Target…
2) Next
3) Pick Server
4) Type in the master server and put in [B]SQL Server Authentication[/B] (sa, which even has same password on both servers)
5) Next
6) And here I get the error:
[B][I]Login failed for user “DOMAIN\MyDomainAccount”[/I]![/B]
The login from the remote (Target) server to the master server and with sa works through sqlcmd or even on Mgmnt Studio by e.g. registering the master server.
And vis-versa it works to login with “sa” and SQL Server Authentication through the master into the target for enlisting the target then.
The error seems to lye really in the Studio Management application and definitely this wizard steps.
Can you try it out and give feedback please if same error or just working.
Thx!
Dear Sir:
I would really appreciate for you help by provide me any script that generate an automate report via store procedure in SQL 2005 and email an attachment of the report to many users via email address listed in data table for specific of time.
Thank you very much for your help.
Best regards,
hello
i got an error while i as trying to make snapshot, dnt know why?
my query was this
CREATE DATABASE SnapshotDB ON
(Name =’RegularDB’,
FileName=’C:\SSDB.ssl’)
AS SNAPSHOT OF RegularDB;
i got error like below
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\SSDB.ssl’.
thanks in advance
Just I want to know in clear view, How much Exam/s i have to give to B a MCTS DBA, and which exam code?
Hi Pinal,
I have a serious problem here.In my project we are developingInterface between our dataware house and Duck Creek(Third party tool which provides services to insurance domain clients),we are decided to use ssis for this.i would appriciate suggetions.
Thanks
Suni
Hey,
Can you please share the information about the tool which is used to Covert the Query from T-SQL(SqlServer) to Pl/Sql(Oracle) and Vise versa.
Thanks
Dear Mr. Pinalkumar Dave
how to store the arbaic character in sql server 2005 with using power builder 10 and reteriving the data also.
Kindly mail to us
From
Mohamed Bijili
Hello Pinal,
I have been looking all around the web for an answer to this question:
I have an application using SQL Server 2005. and i need to intrecept the queries before they are sent to SQL Server to do some changes.
triggers and view are not applicable in this case. I was wondering if i can intercept the communication over port 1433 and monitor/change the information needed.
i was looking for a proxy tool that sets between the application and the SQL Server. and i can write the required listener to intercept the queries and do the required modifications
thanks alot,
ramadan
Dear SIr,
please tell me how i can migrate database server from 2000 to 2005 and 2008.
Rakesh shrivastava.
[personal phone number removed]
Hello Sir.
I want to call a Web-Service function throught SQL Service Broker feature. Is that possible ?
Hello,
I have question regarding adding an instance of sql server.
I have two machines on the same network.
One of them has a SQL Server 2005 version running on it with a default instance and a named instance.
The second machine has a SQL Server 2008 version running on it with a default instance only.
All the SQL Server Instances are using local windows authentification for login credential.
On machine one,, i added a new port 1434 for the named instance and i can used both database engines successfully.
On the second machine, i just maintain one default instance of sql server 2008 database engine.
I am trying to connect to the sql server 2008 default instance on machine 2 from the sql server 2005 default instance on machine 1.
Actually, i am trying to be able to use the sql server 2008 default instance from both of my sql server 2005 instances and also be able to use both of sql server 2005 instance in the sql server 2008 default instance.
I tried the add linked server technique and also adding port techniques but there is something for sure that i am missing here.
I would really appreciate if someone could tip me.
Thanks
John
Hi ,
I am currently working on Ticket Management Application in Asp.net. I want use Email triggering From SQL server in that.
Scenario is :
To trigger an E-mail to Admin if the TAT[ turn around time] is exceeded for a ticket raised. The TAT is calculated in such a way that, only working hours are included and Holidays are omitted.
Eg. Working Hr is 9Am to 6PM,
Saturady & sunday Holiday.
If TAT of a ticket is 5hrs and ticket is raised at 5PM on Friday.Then an email has to be triggered on Monday 1PM (i.e after 5 working hours: [Friday 5pm to 6 PM] 1Hr+[Monday 9AM to1 PM] 4Hr =5 Hr ) if status of that ticket is not closed. Please suggest a method to implement this. Thank You.
Hi Penal,
I am new in SSRS. i need to design a report with drill down for a column. in a matrix like view. could you please help me for the same. I would also know the different report patterns can be made by using SSRS. Need help. thanks in advance.
Hello Sir
Thank you for replying its worked.
Sir i want to ask two more questions
1. how to retrieve ROW Id in sql server of records?
2. what is difference between insert and insert into
1
There is no rowid until you define it
Refer this for more informations on how to use row_number() function
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
2
Both are same. Into is optional
Hello sir
Myself Nilam Shinde. Sir one query runs very fine in database but while fetching data from query crystal report slows down.Would you like to give me solution?
How many rows are transferred to Crystal Reports?
Approximately 500 to 600 rows are transfered for the month APR-2009.The view runs fine in database and takes only 10 sec to run but while fetching records through crystal report 8.5 it takes 4 to 5 minutes. So Pls give solution.
It may be due to network issue
May be ,but all other reports are working fine.
And when i take the query from crystal report’s show sql query and run it in sql analyzer then it also runs slow because of it contains order by clause.Order by clause can be a problem? pls reply
Yes. Remove Order by from the query and do the ordering at the report
I got the solution.In crystal report, in report options I removed tick mark on use indexing or server for speed.
dear sir
i have two table as shown below
fieldmaster
—————————
fieldid—– field_name
1———– Sector Number
2———– Plot no
datavalue
—————————
Id—-datavalue—— fieldid
1—–28——————1
2—–30p—————–2
3—–28p—————–2
4—–52——————-1
5—–12p—————–2
6—–11p—————–2
7—–7p——————-2
and i want the result as:
Sector Number——-Plot no
28———————-30p
28———————-28p
52———————-12p
52———————-11p
52———————-7p
Plz help to resolve it
and also check all special character and ‘NA’
As far I see there is no relation ship between sectornumber and plot.
Is it always true that after every sector the next row data would be plot till it hit another sector?
If the answer is yes then you have do using a while loop or a crusor based on your requirement.
Hello Pinal.
I’ve a doubt about size and datafiles…
Yesterday we had an space issue problem for one of our servers. I release space from the current databases executing shrink commands over them.
But, there are a database that it has 31 GB for allocated space, but only 19 GB are currently allocated with data, the rest are free space. My problem was (as far as I know), there is not posible to reduce the datafile due the Error 21335: [SQL-DMO]The new DBFile size must be large than the current size.
Now, I don’t know what the vendor did, but he did it, he reduce the primary datafile to 19 GB.
How can be done?
Many thanks for your time.
Regards
Hi,
Sir
i am not graduate but i have experiance in hardaware wng. 6yrs and i have certificate DHE & DCA Private Firm.
and i want Teach Sql Server
so pls Best Line Give me
DBA Or MCDBA, MCITP, MCSE,
I’ve come across a weird problem in Sharepint 2007 Database. I’m also checking with support at MSFT but it is new to them.
I’ve applied the WSS 3.0 SP2 on Sharepoint and after rebooting , the sharepoint site was inaccessible to all the users. After doing deep analysis we have noticesd that in the dbo.UserInfo there are three entries for the same user and that is the user who had setup the sharepoint which is not anymore. His AD account was deelte almost a year back.
And one more interesting thing is that in the dbo.UserInfo under Index there are only two entries while when we setup a new database there is seven entries. so no idea what went wrong.
we have tried settingup the proper permission to Admistrator account, reset and re-applied the permission but it is same.
any clue?
1. There is table name “MUSTER” with 2 columns ename and attendence
ename – attendence
a ———– y
a ———– y
a ———– n
a ———– n
update the table such that all y becomes n and n becomes y
but condition is that u have to write single update statement only
????????????????????????
reference
——————————————————————————-
create table newtest
(ename varchar(500),
attendence varchar(500))
insert into newtest values (‘a’,'y’)
insert into newtest values (‘a’,'y’)
insert into newtest values (‘a’,'n’)
insert into newtest values (‘a’,'n’)
select * from newtest
***********************************************
2.create table candidate
(cid int identity(1,1) primary key,
cname varchar(50) ,
)
insert into candidate values(‘hasmukh’)
insert into candidate values(‘potu’)
insert into candidate values(‘nizam’)
insert into candidate values(‘sandeep’)
create table skill
(sid int identity(1,1) primary key,
skillname varchar(50) ,
)
insert into skill values(‘testing’)
insert into skill values(‘asp.net’)
insert into skill values(‘c#’)
insert into skill values(‘c++’)
insert into skill values(‘vb’)
create table cand_skill
(id int identity(1,1) primary key,
cid int foreign key references candidate(cid),
sid int foreign key references skill(sid)
)
insert into cand_skill values(4,5)
insert into cand_skill values(4,3)
insert into cand_skill values(1,2)
insert into cand_skill values(1,1)
insert into cand_skill values(1,3)
insert into cand_skill values(3,2)
insert into cand_skill values(2,5)
select * from skill
select * from candidate
select * from cand_skill
–QUESTION 1: RETREIVE ALL THE EMPLOYEES WITH THERE SKILLNAME
–Note: candidate can have multiple skills
Sir please can u please answer me this question..
Please help me to solve this questions..
1
update newtest
set attendence=case when attendence=’y’ then ‘n’ else ‘y’ end
2
What is the relation between the two tables?
I have a variable this varible i want insert in database with
same cloumn
string =”"62308718,07678892,53963600,82535854″”
Can you be more specific?
Do you want to update this value to a column of a table in the database?
i have one doubt
what is the use ‘national’ keyword in sqlserver
pls explain with example
thank u
In need a good and fast way to copy all records from sql table to access table via vb6 classic.
Note:
sql table have approx 4.500.000 records and on this sql table have only a reading permission.
Tks to the all.
Read about OPENROWSET in SQL Server help file
It will help you to copy all rows at a single execution
Hi All,
How to merge two databases in SQL Server?
If possible to tell me ur suggestions…
Thanks to All
If two databases are independent of each other, you can make use of import/export wizard to copy objects and data from one database to another
hi,
i need insert more than 1000 records from one table to another ,
if only 500 records are inserted then it sould get rollback
anyone can help
Thanks
Your Code
If @@ERROR0
ROLLBACK
There’s an easy way. Start transaction. Calculate how many rows are in the source table. If there less than 500 rows, rollback the transaction. Otherwise copy rows and commit the transaction. You may even want to write procedure for this.
@Raj
Perhaps this will work:
BEGIN TRANSACTION
INSERT INTO … SELECT … FROM
IF @@ROWCOUNT <= 500 ROLLBACK
COMMIT
Hello Pinal,
Please let me know how to solve this issue.
After running Micrsoft Upgrade Advisor, I have got this issue
When to Fix : Anytime
Description
Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default
ERRORS
SQL Server provides a way to allow instance-level registration of new word breakers and filters.
Affected Objects
Component: .asx Component: .css Component: .hta Component: .htt Component: .idq Component: .pps Component: .xps Component: .xsl
Kinldy let me know how to fix this and is there any impact of this on production if not fixed.
Thanks a lot.
Naina
Hello Pinal,
Im getting this issue, while i run the upgrade advisor
When to Fix : before Issue : full text search has changed in sql server 2008
please let me knw how to resolve this.
Thanks
Naina
Hi!
I have read your posts about enabling/disabling indexes and dropping primary keys but I have a situation where I want to disable the clustered index or primary key ready for a bulk data import.
I am happy with disabling/rebuilding the non primary key indexes but was wondering how you would get around this issue?
Thanks
Jon
How to Generate Recurring Events in the Database.
I would like to create a .net application that allows us to add events to a overall application, anyone then can go search that event by a specific date and if that event falls under that recurrence day you will see it. This would be similar to Outlook where you can say it recurs every Monday, Tuesday, Wednesday,… or every third Monday, …Specifc day of the month, etc.
Can you help me.
I have a situation where an application running on sql2000 doesn’t release any space in transaction log, with auto-grow on it just grows and grows. with auto-growth turned off we get errors after a week or two because it runs out of space. it can be using only 100kb, yet grow to 60gb. Is this a sql2000 problem or badly written code?
The log file for database ‘hsmprod’ is full. Back up the transaction log for the database to free up some log space
Thank you. Your articles are always very helpful
Hello Pinal,
I am Madhab, a reader of your Blog. I have an issue with MS SQL Server, hope you could solve that.
Description:
Let consider I have a Table Student with 4 columns. Those are: Roll Primary_key, s_name Not null, Phone, city.
I would like to write an insert statement that will insert values to the table, but specific columns. As Roll, s_name are notnull, I need to enter them mandatorily.
But I want the the other two field should enter on users choice.
EX: Insert into mydb.dbo.Student (Roll, s-name,,) values (100, ‘Madhab’)
or something like that…
Can you please help me on that…
Thank you
Maddy
EX: Insert into mydb.dbo.Student (Roll, s-name,Phone, city) values (100, ‘Madhab’, @phone,@city)
Hi
I am facing problem in SQL server 2005 taking long time to insertion, while same application is running on another server work just perfect;
Server A:
Edition Enterprise Edition (64-bit):
Product Level SP2
Version 9.00.3042.00
@@Version Microsoft SQL Server 2005 – 9.00.3042.00
(X64) Feb 10 2007 00:59:02 Copyright
(c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT
6.0 (Build 6001: Service Pack 1)
Server B:
Edition Enterprise Edition (64-bit)
Product Level SP3
Version 9.00.4035.00
@@Version Microsoft SQL Server 2005 – 9.00.4035.00
(X64) Nov 24 2008 16:17:31 Copyright
(c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT
5.2 (Build 3790: Service Pack 2)
Server A is working fine but Server B is in trouble,
Your advice to fix the problem will be highly appreciated.
Thanks and Regards
sani
Hi
I am not able to understand flow of your website, like for new post, did not find and link, where I can go for new post with new subject; which is not already posted
Thanks and Regards
sani
You can find new posts at the Home page
Visit this page
http://blog.sqlauthority.com
Hi all,
How to get start date and end date from the following duration as ‘Jan 10 – Jun 10′?
can you suggestion me as soon as,
Thanks
What about year part?
Hi all,
i have find this way to get start and end date….
Declare @m INT, @y INT, @dt SMALLDATETIME,
@Stdate DATETIME, @EndDate DATETIME,
SELECT @m = 01, @y = 10, @dt = RTRIM(@y) + ’0101′;
SET @Stdate=DATEADD(DAY, 0, DATEADD(MONTH, @m-1, @dt))
SET @Enddate=DATEADD(DAY, -1, DATEADD(MONTH, @m+0, @dt))
SELECT @stdate AS StartDate, @EndDate AS EndDate
If any way to get start and end date the following period as ‘jan 10 – jun 10′, tell ur suggestions as soon as….
Thanks to all
Refer this code
declare @from varchar(10), @to varchar(10)
select @from=’jan 10′, @to=’Jun 10′
select cast(’01 ‘+@from as datetime), dateadd(month,1,cast(’01 ‘+@to as datetime))
select columns from table
where
date_col>=cast(’01 ‘+@from as datetime) and
date_col<dateadd(month,1,cast('01 '+@to as datetime))
Hi madhivan,
Thank a lot ur suggestion….
Sir,i had a problem when i m inserting data into my database named AddressBook from another database PAFDataAtoL,when i run the below query
USE AddressBook
INSERT INTO Addr_BK_Address(PZC, Town_ID,ThoroughfareKey,ThoroughfareDescriptorKey,DependentThoroughfareKey,DependentThoroughfareDescriptorKey,BuildingNumber,BuildingNameKey,SubBuildingNameKey,CMP_ID)
SELECT Postcode,LocalityKey,ThoroughfareKey,ThoroughfareDescriptorKey,DependentThoroughfareKey,DependentThoroughfareDescriptorKey,BuildingNumber,BuildingNameKey,SubBuildingNameKey,OrganisationKey
FROM PAFDataMtoZ.dbo.Address
i get the following error
Msg 9002, Level 17, State 4, Line 1
The transaction log for database ‘AddressBook’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Plz sir what can i do now?
Lailla Khan
Hello,
If you are interested in SQL Architect/Developer positions in Monrovia or Marina del Rey, please let me know. Thank you for your time!
Hi,
I have a problem, that cannot resolve.
How can create one table of two tables that contains different registers.
for example:
The first table contain the fields next
DATE_END
PERCENT
the second table contain
ID_T
DATE
PERCENT
So the rows first table are less that second table, I want one table that contain the rows the second table always that this rows are lower
for example first table contain
FFIN DATE
————- —————————————
2010-06-07 25.00
2010-06-15 50.00
2010-06-19 60.00
2010-06-22 70.00
2010-06-26 85.00
2010-06-27 100.00
Second Table
ID_T DATE PERCENT
————— ———————- ————–
1 2010-06-01 5.00
2 2010-06-02 11.60
3 2010-06-03 25.00
4 2010-06-04 32.90
The result that I need is
NEWDATE PERCENT
———————- ————–
2010-06-01 5.00
2010-06-02 11.60
2010-06-03 25.00
2010-06-04 32.90
2010-06-07 25.00
2010-06-15 50.00
2010-06-19 60.00
2010-06-22 70.00
2010-06-26 85.00
2010-06-27 100.00
As I can obtain this result?
thanks for your help.
@Adolfo
I am sorry, I am having trouble understanding your question.
I am not sure if I understood your question correctly. You want to display your data in Ascending order of date.
Select * from (
Select datecolumn, date percent
From Table1
UNION ALL
Select datecolumn, date percent
From Table2) A
Order by datecolumn ASC
Note: Use UNION, to exclude duplicates. When using UNION ALL or UNION, make sure the data types for columns in table1 and table2 are same, otherwise you cannot use UNION or UNION ALL.
If you always want to display rows of first table below rows of second table. Then add an alias column in select statement and order by that column.
Select * from (
Select datecolumn, date percent, 2 AS Orderby_ID
From Table1
UNION ALL
Select datecolumn, date percent, 1 AS Orderby_ID
From Table2) A
Order by Orderby_ID ASC
Does that helps ?
~ IM.
Hi Madhivanan,
Table A:(GroupID, GroupName)
1 Family
2 Friends
3 Business
4 Clients
5 Others
Table B: (ConID,ConName,City,GroupID)
1 Ramesh NULL 3
2 Jegan NULL 1
3 Arun NULL 2
4 Mathan NULL 1
I want result as
GroupName NoofContacts
Family 2
Friends 1
Business 1
Clients 0
Others 0
can you suggestion me about this as soon as
Thanks
Hi
i have use this query,,
SELECT A.GroupName,Count(B.ContactID)AS NoofContacts
FROM CON_TblGroup AS A
LEFT JOIN CON_TblContactMaster AS B
ON A.GroupID = B.GroupID
GROUP BY GroupName
ORDER BY GroupName
but suggestion me any other way to get result..
Thanks
Hi.
I have a query result
which i will have to put it in a excel sheet which is in a predefined format..
The first 10 or 12 rows contains headings with some other information…
Now i have a query which i can run using Oledb source and the result of this query must sit from 13th cell onwards..
I tried mentioning Sheet$1A15:D250 this in openrowset option..but it is not working..
i can easily fetch the range i want and put it a destination table
but not viceversa..
Can anybody please help me out on this problem..
That would be of a great help..
Thanks in advance…
Bruno..
@Bruno,
I am not sure if I understood your question correctly, You have an excel sheet in which top 15 rows you want to reserve for header information, you want to export data out of table to this excel and you wantSQL Server to write from 15th row and not from the first row.
I had the similar requirement, this is how I did it.
Method 1: Either You prepopulate those 14 rows in your excel template, so that when you start writing to excel, SQL Server will start writing from 15th row. You dont have to mention row numbers in openquery statement, it will start from 15th Row.
Method2: If you are not aware of what those 14 rows will be in your header, you can do like this. Go to your excel, in top 14 rows type something in every cell and press delete button, do this one cell at a time. By doing this you are reserving space for that cell, When SQL Server will try to write to this excel, SQL Server will see that there is some data already in this cell even though the cell is empty it has already been used, So SQL Server will start from 15th Row. When I say delete, dont delete by doing Right Click, Delete entire row. I meant, type something in the cell and then press delete button from your key board so that the value you typed in the cell disappears.
This is the way I have done at my work and it works with no issues.
~ IM.
sir,
i am already sql 2005 familiar
i want to doing sql dba course so that i get degination SQl dba/ developer .
pl give me right direction
rg
amit
hi there,
how can i insert values from my vb6 textboxes(6 of them,like text1,text2….text6) on a form into sql7 which is my back end
thanks
Use paramterised queries and pass values to them from the text boxes
Hi,
I now get a blank warning modal message box, warning icon, with no text whatsoever and an “OK” button every time I start SQL Server Management Studio.
can you tell the suggestion as soon as, whats that problem, and how to fix that problem….
Thanks to all
Have you tried reinstalling the software?
Hi,
I now get a blank warning modal message box, warning icon, with no text whatsoever and an “OK” button every time I start SQL Server Management Studio.
can you tell ur suggestion about that issue, and how to fix that issue….
Thanks,
Hi
I have fix that issues in the following way…
1. In Control Panel, open Add or Remove Programs.
2. Click Microsoft .NET Framework 2.0.
3. Click Change/Remove.
4. Click Repair, and then click Next.
5. When the repair is completed, restart the computer
Thanks
Sql server 2008
We have option to edit top 200 rows
but if i want to edit rows from 300 to 500 how i can do that
You need a pagination. Refer this post to know how to do it using row_number() function. Refer point 4http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
Hi Pinal ,Imran and All,
I need a help from you regarding Replication. I have two Sql Server 2008 A And B on separate Machine.
“A” is a Live Server on which i created Trans Log Publisher and Subscriber is “B” Server.
I have created setup successfully .But It is not updating data on B server.When i checked in Replication Monitor i got the error “The process could not execute ‘sp_replcmds’ on ‘ARUN\SQL2008′ ” The log reader agent is failed.
Can u pls help me what could be the issue.I tried hard on google and other forum sites but unable to get the correct reason.
Thnx,
Arun
Is it possible to release & acquire the UPD lock with C#?
also set the timeout from code…!
Dear Pinal,
I have one doubt regarding with one query.
I have two table and need to write select query by putting table value as column name {value(table_name.coloumn) as coloumn name}
employee schema table with data as below
Pid Coloumn Display Name
1 Name
2 Age
3 Sex
Item Table data as below
Pid Schema table Xid Item
1 1 Pinal
2 2 27
3 3 Male
4 1 Abdul Manzoor
5 2 27
6 3 Male
Result should be like this
Name Age Sex
Pinal 27 Male
Abdul manzoor 27 Male
Is it possible…..
Thanks
Abdul
@Manzoor
The first TABLE seems not to be too helpful. The second TABLE doesn’t have anything to show which records go together. So, i am guessing it is just three at a time.
Here is an example of what could work:
WITH
Item(Pid, Xid, Item)
AS
(
SELECT 1, 1, ‘Pinal’ UNION ALL
SELECT 2, 2, ’27′ UNION ALL
SELECT 3, 3, ‘Male’ UNION ALL
SELECT 4, 1, ‘Abdul Manzoor’ UNION ALL
SELECT 5, 2, ’27′ UNION ALL
SELECT 6, 3, ‘Male’
)
SELECT
MAX(CASE pid % 3 WHEN 1 THEN Item END) Name,
MAX(CASE pid % 3 WHEN 2 THEN Item END) Age,
MAX(CASE pid % 3 WHEN 0 THEN Item END) Sex
FROM
Item
GROUP BY
(Pid -1) / 3;
Also refer this post which will work for any number columns and not specific to fixed number of columns
http://beyondrelational.com/blogs/madhivanan/archive/2009/05/05/import-from-text-file-single-column-to-multiple-columns.aspx
Hi Pinal,
I need a help from you regarding :
How to create a database in Sql server authentication mode so that i can connect it with my web application?
Thanks
Apurva
You need to create a new user and assign that user to access your database
URGENT PLZZZZ
I have a big code (inside after insert trigger) which causes problem (timeout) on the next insert from another user, i think thats because of locking the table.
Is there a way to commit the insert then continue running the sql code after that (so the table will be unlocked). If not what do you recommend me to play around this issue !!
Thank you,
What are doing inside the trigger?
Post the code
I have followed your suggestion to identify the heaviest-used stored procedure in my sql server 2005 database and collected these stats over time. I restarted sql server each night to get a fresh set. Can you tell me why the physical reads would vary so widely with the same approximate number of calls to the procedure?
StoredProcedure Date execution_count total_IO total_physical_reads
USP_GET_AUTHORIZATION 4/26/2010 172,221 29,357,867,028 7,409,485
USP_GET_AUTHORIZATION 4/28/2010 209,830 3,654,080,547 1,846
USP_GET_AUTHORIZATION 4/30/2010 59,614 14,468,926,585 3,597,000
USP_GET_AUTHORIZATION 5/4/2010 227,852 3,999,076,244 951
USP_GET_AUTHORIZATION 5/5/2010 191,958 42,126,062,731 10,496,595
USP_GET_AUTHORIZATION 5/6/2010 217,545 51,097,826,346 12,630,635
USP_GET_AUTHORIZATION 5/7/2010 119,129 28,107,707,537 6,982,525
USP_GET_AUTHORIZATION 5/10/2010 227,118 4,016,253,953 1,275
USP_GET_AUTHORIZATION 5/11/2010 206,457 44,431,030,980 11,128,297
USP_GET_AUTHORIZATION 5/12/2010 221,866 48,635,832,683 12,146,244
USP_GET_AUTHORIZATION 5/13/2010 186,085 3,298,051,128 2,137
USP_GET_AUTHORIZATION 5/14/2010 110,064 23,564,004,802 5,876,825
USE [CompuSwiftData]
GO
/****** Object: StoredProcedure [dbo].[selectOrderDetailsMulit] Script Date: 06/08/2010 23:42:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author:
– Create date:
– Description:
– =============================================
ALTER PROCEDURE [dbo].[selectOrderDetailsMulit]
– Add the parameters for the stored procedure here
@txtOption1 varchar(25),
@txtOption2 varchar(25),
@txtQuantity1 varchar(25),
@txtQuantity2 varchar(25)
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 [newQuantity] = @txtQuantity1,
[newQuantity] = @txtQuantity2,
[03_CS_Number],
[08_Description],
[10_Period_Text],
[17_Rand_Currency_Indicator],
CONVERT(DECIMAL(16,2), SUM([25_Total_Cost]* @txtQuantity1)) As Cost
FROM Product_List_easyCGI
WHERE ([03_CS_Number] = @txtOption1 OR [03_CS_Number] = @txtOption2)
GROUP BY [03_CS_Number],
[08_Description],
[10_Period_Text],
[17_Rand_Currency_Indicator],
[24_Sub_Total_LB]
END
The New quantity column is not in the table, but the query works however it does not return to me the result I want
newQuantity newQuantity [03_CS_Number] [08_Description] [10_Period_Text] [17_Rand_Currency_Indicator] Cost
5 10 Item1 Desc1 Bi-Annually R 100
5 10 Item2 Desc2 Bi-Annually R 250
when I want this
newQuantity [03_CS_Number] [08_Description] [10_Period_Text] [17_Rand_Currency_Indicator] Cost
5 Item1 Desc1 Bi-Annually R 100
10 Item2 Desc2 Bi-Annually R 250
Also The website page that calls this SP passes through the variables listed at the top. These however could be “null” depending on the clients selection on the page. the results then get sent back to the page and displayed in a gridview.
Hi Mr Pinal,
Recently we used Bcp to export our tables to txt fille
I have written a procedure to execute it but unfortunately am getting error
Error = [Microsoft][SQL Native Client]Unable to resolve column level collations.
Here is what the SP i have used to execute
Alter procedure sp_AOStextgeneration
as
begin
set nocount on
declare @sql as varchar(8000)
declare @docid as varchar(max)
select @docid=newid()
declare @count as int
declare @lastupdate datetime
select @lastupdate=isnull(max(Dateofdelivery),CONVERT(datetime,’1900-01-01′)) from ProductCart.dbo.tbl_sapsettings where active=1
set @count= (select COUNT(*) from ProductCart.dbo.[v_AOS-SAP] where datediff(mi,ProductCart.dbo.[v_AOS-SAP].InvoiceDate,@lastupdate)0
begin
select @sql = ‘bcp “exec ProductCart.dbo.X_sp_generateAOStoSAP_h_data ”’ + @docid + ”’” queryout E:\Interfaces\AOS-SAP\’ + @docid + ‘_d.txt -c -t, -T -S ‘+ @@servername
exec master..xp_cmdshell @sql
–print @sql
–select @sql = ‘bcp “exec pc_bak.dbo.X_sp_generateAOStoSAP_h_data ”’ + @docid + ”’” queryout E:\Interfaces\AOS-SAP\’ + @docid + ‘_d.txt -c -t, -T -S ‘ + @@servername
–exec pc_bak..xp_cmdshell @sql
—-print @sql
if @@ERROR =0
begin
select @sql=’Insert into ProductCart.dbo.tbl_sapsettings(Dateofdelivery, FileName,Active) values (getdate(),”’ + @docid + ‘.dat”,1)’
exec(@sql)
end
end
else
begin
print ‘Zero Rows Copied’
end
end
Can you pls tell where exactly whats the cause behind this issue.
Thanks
Regards
N.Balaji
Hi,
As per my knoweledge DDL never gets rollback then
how truncate get rollback. Even i have tested that create table also get rollback.
Please Can you tell me how this is possible …?
Where did you read DDL can’t be rollbacked?
As you tested, it can be rollbacked if used inside a transaction
Hi Pinal,
Can you help me in providing the basic fundamentals of the Graphical Execution plan of a Query, Stored Procedure, UDFs.
I want to know how to improve the performance of a Query/Stroed Performance/UDFs and what are the rules we need to obey while solving a Graphical Execution plan.
Thanks in Advance,
Sumit
Hello friend
I have request in SQL Server
I have tables including data (like countries) and I want to create script take all data from this table and make script including this data and can take this script and install it on another server without any error
So what I need way to get table structure and data and put it in script, so when I need it in the future, just click on this script and it will create this table with data
I hope get the answer
1 Generate script of a table
2 Export data to text file using a bcp
Whenever you want to copy the table, you can run the script and import data from the text file
Hi pinal,
I am having trouble when replicating 2 databases on 2 sql 2005 servers, restored from backups of sql 2000.
the following message i get:
Message: Data conversion failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
I looked around the google and could not find the answer, so if you could help me i will realy preciate this
Hi Pinal, could you help with my replication error??
hi,
how many connection active from Server SQL Machine? and we are using dotnet for data transfer from sql server and my programe, in this case if execute some query, still waiting sql server? if waiting, how many min. wait?
1 Run
exec sp_who
2 It depends on the timeout set in the Server
Hi Pinal,
I am using sql server 2000.
I saw some interesting stuff in sql server 2005 is row_number() function.
Using this function we can retrieve the records between the row number.
Is there any function quivalent to this in sql server 2000.
waiting for reply.
Thank you.
Best Regards,
Praveen
You can simulate it
What do you want to do?
Hi Pinal
I wanted to help me.
I have Desktop Application that I use SQL Server Express 2008.
and I use Full Text Search for my search
and it work fine !
But I had a questions
1- I know that i can Encrypt One or More Columns of my table in Sql Express 2008 Whether this is true????
2- I know too that when I search and use Full text Search Sql Server search in Separate File (Index), so When I search over one Column (that Encrypt) , my search speed not reduce and not have any Disadvantage Whether this is true????
Hi,
I want to improve my sql skills.That includes primarily analytical skills.Please recommend me how to do it.
thanks.
Hi,
I want to take the record in between two date values ,
but that date values are come NULL or EMPTY , that time how do use ISNULL and Is Empty functions in the date field.
Thanks
SELECT ISNULL(date,’other_date’)
Hi Pinal,
I have a question,
In oracle, I can define variable’s datatype using another table’s column datatype in procedure or function.. (see the example) can you help me find it out similar things in sql server 2005? Thanks in an advance.
Example:
eno in emp.empno%type,
name out emp.ename%type,
job out emp.job%type,
salary out emp.sal%type,
location out dept.loc%type
create or replace procedure display (
eno in emp.empno%type,
name out emp.ename%type,
job out emp.job%type,
salary out emp.sal%type,
location out dept.loc%type
)
is
begin
select ename,job,sal,loc into name,job,salary,location from emp e,dept d
where e.deptno=d.deptno AND empno=eno;
end;
There is no direct alternate in SQL Server 2005
hellow mr pinal. i have three table
1)cust_child
colum name
,cust_master,product_master.
hellow mr pinal. i have three table
1)cust_child
colum list ->cust_ID,Product_ID,version etc…
2)cust_master
colum list ->cust_ID,Cust_name,etc…
3)product_master.
colume list->product_id,Name,etc….
i wan list customers name with there product ..
can you reply me fast ..
select m.cust_name,p.product_name from cust_master as m
left join cust_child as c on m.cust_id=c.cust_id
left join product_master as p on c.product_id=p.product_id
hi
i have four table and I also has output can you help me how to get it that.
create table person
(PID int,
Name Varchar(20))
insert into person values (1,’x')
select * from person
create table maritalStatus
(PID int,
Status varchar(20),
Date Date)
insert into maritalStatus values(1,’s',’06/01/2010′)
insert into maritalStatus values(1,’m',’06/20/2010′)
create table JobStatus
(PID int,
Status varchar(20),
Date Date)
insert into JobStatus values(1,’U',’06/01/2010′),(2,’E',’06/10/2010′),(3,’U',’07/01/2010′)
create table Fstatus
(PID int,
Status varchar(20),
Date Date)
insert into Fstatus values (1,’F1′,’06/01/2010′),(1,’F2′,’06/10/2010′),(1,’F3′,’06/22/2010′)
output is
PID Date MaterialStatus Jobstatus Fstatus
1 06/01/2010 S U F1
1 06/10/2010 S E F2
1 06/20/2010 M E F2
1 06/22/2010 M E F3
1 07/01/2010 M U F3
Hi Pinal,
i need to know,
how to restore database in sql server 2008 from a network drive?
i have tried it by giving the network path “\\MyHomeServer\share\test.bak”, but this gives me an error, that the login account does have the access to the drive, or there no shuch path.
(Msg 3201, Level 16, State 2, Line 1
Cannot open backup device ‘\\MyHomeServer\share\test.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.)
Please help me short it out.
Make sure that is the shared path and your account has proper access to that
Hi, I want to know details about how we can connect two remote sql servers????
Will u please give me some exaple of how i can use sp_addlinkedserver
I have used it but got error
login failed for user ‘sa’
You may need to map login informations
Read about sp_addlinkedsrvlogin in SQL Server help file
performance of storing large data objects in sql server 2008
Friends,
I have this issue, where I am able to export the data to a text file in the server, but my requirement is to put the export file in the local machine, the data should be available to the user calling for it.
The command I am using is
EXEC xp_cmdshell ‘bcp “select chr_TextLines from mydatabase.dbo.myTable” queryout “C:\bcptest2.txt” -Uxyz -Pabcd -Smyserver -c -t,’, no_output;
Please advice.
This is the first time I am using the BCP utility to write back data to text file, I have only used BCP to take in values to tables.
Thanks in advance
You can make use of UNC path like
‘\\system_name\drive_name\file_path’
Hi,
i want to display the query results with column headers, and now appears all column values in single cell, how display the column values in separate cells in excel file…
Thanks
Which version of EXCEL are you using?
In 2007 onwards you can use “Text to columns” feature
Hi,
I have use MS Excel 2003.
You can format the data using the tool bar
Goto Data from Toolbar and select text to columns
Friends,
I have this issue, When i retrieve the text Data of more then 65000 characters from a table its taking around 2 min time causes time out exception in our application. same whn i retrieve around 25000 characters, it retrievies within a sec.
Could you please advice
Hi Pinal,
Please help me in following queries;
1)How to overcome SQL Server 2005 maximumm row limit i.e. 8060 bytes.
2)How to run SQL Server 2005 and Sql Server 2008 Reporting Services simultaneously on single machine.If possible then is any changes required apart from instance name.
3)My server configuration is SQL Server 2005 64 bit edition with SP2 running on windows 2003 server and it consumes 7.2 GB of RAM out of 8 GB.I run SQL Profiler check Perfmon and run dbcc memorystatus.Results are given below.Please check and suggest me some tips to reduce memory usage.Even transactions log for 1 hour is not exceeded 500 KB.
Buffer Cache Hit Ratio 99.98
Page Life Expentancy:24120
Stolen Pages:188776
Target Pages:736588
Total Pages:671077
Lock Blocks:6
Lock Memory :9720
Sql Cache Memory:20056
Target Server Memory :5893520
Total Server Memory: 5369080
AWE Memory Allocated:5471072
VM Reserved 8497400
VM Committed 87820
AWE Allocated 5471512
Reserved Memory 1024
Reserved Memory In Use 0
Could you please tell me how can i create a database, which is a copy of an existing database by using T-SQL? please dont tell backup and restore method, since i want to create database in third party server and they will not provide lack of permissions.
Which version of SQL Server are you using?
If you use versions from 2008 onwards, you can generate the script along with data and run that script on the target server
Is there any way to change MODEL database structure, and which will only affect only to current user?
Is there a keyboard shortcut to set a field to the current datetime from the grid view in the same way you can use ctrl+0 to set a field = null?
No. There is no such shortcut. You need to use update statement from the Query analyser
hi pinal
select pwdencrypt(‘AAAA’) can be used to decrypt the password
but how can we decprypt to retrive the original password
Thanks
Prajin
why you have changed so many jobs , did you liked working in ahmedabad?
Hi
I had an issue with our SQl Job Agent yesterday.
Jobs were showing as executing, but had not started their first step???
They also didn’t show as failed, they just skipped a day forward, making it hard to identify the ones effected.
Below are the logs, some hint at memory resource but checking system logs they don’t indicate that, could it be a network dns/permissions glitch?:
[382] Logon to server ‘SBE01\SBE01′ failed (ConnUpdateStartExecutionDate)
[165] ODBC Error: 0, Memory allocation failure [SQLSTATE HY001]
[165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_DBC failed [SQLSTATE IM005]
[165] ODBC Error: 0, Memory allocation failure [SQLSTATE HY001]
I’d appreciate any hints or help with this, google is coming up with nothing and its nice to understand why these things happen.
P.S Great SQL site, i always recommend it…keep up the good work.
How to do indexing or web crawling
for fast searching in database in SQL server
I m not getting right info in right format as u explain on web
hi ,
I have partitioned a table which is having a Non clustered index . After Partitioning,the query optimizer not choosing the non clustered index key for scan. It does only table scan. I followed the following sequence to partition existing table.
1. Remove all non clustered indexes
2. Add identity column
3. Created Non clustered index
4. Created index on clustered index on Identity column
5. Create partition function and partition scheme
6. Drop the clustered index with move option and applied Partition scheme
after applying partition the optimizer is not using the Non clustered index key to fetch rows…
It does only table Scan. anything wrong in that sequence?
hi ,
I have partitioned a table which is having a Non clustered index . After Partitioning,the query optimizer not choosing the non clustered index key for scan. It does only table scan. I followed the following sequence to partition existing table.
1. Remove all non clustered indexes
2. Add identity column
3. Created Non clustered index
4. Created index on clustered index on Identity column
5. Create partition function and partition scheme
6. Drop the clustered index with move option and applied Partition scheme
after applying partition the optimizer is not using the Non clustered index key to fetch rows… It does only table Scan. anything wrong in that sequence?
Hi,,
I have one doubt, how do work oracle database in sql server 2005?
If possible, please suggest me as soon as…
Thanks
You can make use linked server
Read about sp_addLinkedserver in SQL Server help file
Hi,
I have one doubt in sql server 2005,
How to access / use oracle database from sql server 2005?
If possible, please suggest me….
Thanks to all.
Make use of Linked Server. Read about sp_addLinkedServer in SQL Server help file
Hi Pinal,
Your website has bailed me out on many an occasion, but this particular problem I’m having really has me stumped. I’ve set up Database Mail in SQL server 2008 R2 to connect to a remote smtp server and have set it up using basic authentication. I have also opened up Port 1025 (which is what the remote SMTP server uses) on my computer’s Windows Firewall. I have also stopped/started SQL Server Agent.
However, the remote SMTP server rejects the logon saying “Authentication required’. What could the issue be? I am able to use the same settings on Microsoft Outlook and successfully send to the same SMTP server.
Thanks,
- Radhika
HI Pinal,
Thank you very much for your help. I have an issue with linked server.
I have two SQLServer2000 instances connected with linked server.
I have one table(ReaderInfo) in remote instance with a datetime data type column (generated_time).
I got an error “error converting dbtype_dbtimestamp to datetime” when I executed a query like below.
select * from [linkedserver].[readersdb].[readersuser].ReaderInfo
where generated_time > getdate().
Finally, I found 150 rows with invalid date(the dates were before 1753) in ReaderInfo at generated_time column.
I updated those 150 rows with current date and executed the above query again. But still I am getting this error.
There are no other invalid dates. Finally I removed the primarykey constraint in ReaderInfo table and recreated again. Surprisingly it was working.
What is the reason? is there any problem with indexes which were exist in buffer so the query’s ‘where’ still referring?.
is recreating a primary key is correct solution?
Please suggest me and help me
Thanks
Krishna
I am surprised to read this
The column generated_time is of datetime datatype
How is it possible to store dates prior to 1753?
I think it should have been a varchar datatype
Dear Pinal,
According to your experience what we should set the size of following fields while creating a new database.
MDF Initial Size = ?
LDF Initial Size = ?
MDF
—-
Enable Autogrowth = ?
File Growth = ?
Maximum File Size = ?
LDF
—-
Enable Autogrowth = ?
File Growth = ?
Maximum File Size = ?
Thanks,
Adnan
Database Portal_WSSContent [SQLSTATE 01000]
Msg 7729, Sev 16, State 1, Line 1 : Cannot specify partition number in the alter index statement as the index ‘Roles_PK’ is not partitioned. [SQLSTATE 42000]
The above is the error getting on production server,can you please help me step by step
Hi Pinal,
I am jr DBA ,
i got this error , when i take a backup from one system and Restore it to another system ,Plz help me why this error is coming
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device ‘c:arcdb\school\schoollatest.bak’. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The file path should be
c:\arcdb\school\schoollatest.bak
Also make sure the path is in the Server’s directory
Thanks ,
I tried what u have said , again same error I took a backup from Server and restore it to another computer
this problem is coming , is there any permissions do i need to change ,
This problem is occured to one Database only
The error while restoring from SSMS is
Read on “:\arcdb\school\schoollatest.bak” failed (Reached the end of the file (Microsoft Sql Error ,3203)
Please suggest me
Hi Pinal ,
I have doubt that which operating system support sql server 2008- windows 7 home basic or windows 7 home premium or windows 7 professional for sql server 2008 standard edition(for complete ssis,ssas,ssrs).
Plz give the solution.
Regards,
Ashok
Hi Pinal,
I am getting the following error on any event in my sql server management studio:
System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.CreateCWWindow()
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.OnHandleCreated(EventArgs a)
at System.Windows.Forms.Control.WmCreate(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextEditorControl.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
I am connected with SQL Server through LAN. Other users are not getting this error. So, only thing I can understand that the problem is something in the settings of my machine.
Please help. It’s urgent.
Thanks,
Vinod
Dear Dave,
Currently my web site is working in sql 2000. I am planning to move sql 2008.
Will it work without changes of query?
Thanks
G.premkumar.
There might be some problems
Read about Behavioral changes in SQL Server help file
Hello,
Kindly help me
while am creating a duplicate table without records v r giving query as
…..select into from where 1 1 ….
i want to know the meaning of y we r giving 11 near where condition..
help me..thanks
It should be
select into new_table from old_table where 1=0
It means create new_table based on the old_table
But the condition 1=0 falis so new_table will be created without any data
Hi below is one of my stored procedure, The purpose is to fill a table with all the sql statments that will be executed inside the stored procedure based on input parameter value.
————————————————————————-
ALTER PROCEDURE [dbo].[up_hd_utility_add_note]
@relatedId uniqueidentifier,
@note ntext,
@noteType nvarchar(50),
@userName nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @noteTypeId uniqueidentifier,
@query nvarchar(4000)
SELECT @noteTypeId = (SELECT note_type_id FROM dbo.hd_note_types WHERE item_alias = @noteType)
INSERT
hd_notes
(
related_id,
note,
note_type_id,
created_by,
created_date
)
VALUES
(
@relatedId,
@note,
@noteTypeId,
@userName,
getdate()
)
/* This is what i am using to insert the query into different table */
SET @query=’INSERT
hd_notes
(
related_id,note,
note_type_id,
created_by,
created_date)
VALUES
(‘+convert(varchar(38),@relatedId)+
‘,’+convert(varchar(4000),@note)+’,’
+convert(varchar(38),@noteTypeId)+’,’
+@userName+’,’
+convert(varchar(50),getdate())+’)’
INSERT zzz_ae_transations (sql_statment) VALUES (@query)
END
———————————————————————-
But there are around 250 stored procedures and if i follow this path it will be very hard maintaining them later on.
IS there any table which stores the last executed statement with the values of variables substituted in it..
( i tried
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
DBCC INPUTBUFFER ()
)
but they all give the query with variable name but not the actual values passed into the procedure.
Any pointers will be greatly appreciated.
Thanks.
hi
i have sql script contains table creation, view creation, function creation, insert query and procedures
it is like
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[TBLNARRATIONMST_01]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[TBLNARRATIONMST_01](
[ID] [int] NOT NULL,
[DESCRIPTION] [varchar](800) NOT NULL,
[MODIFIEDBY] [varchar](50) NOT NULL,
[MODIFIEDON] [datetime] NOT NULL,
CONSTRAINT [NARRATION_PK_01] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [NARRATIONDESC_UK_01] UNIQUE NONCLUSTERED
(
[DESCRIPTION] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END………..
…………….
……….
ans so on
i fetch this script in one procedure as nvarchar(max) variable.
how can i execute that script in procedure.
I divide my script in substring with less than 4000 characters.
but
————–
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
————-
gives error
why does GO give error execute in procedure?
script executes successfully in query window, but i want to execute in procedure.
my script is stored in one table and i fetch that in procedure.
please give me suggestion…..
thanks
Hi Pinal,
I am working on a project that uses SQL Server. I am having a problem to figure out how to split string from a certain field of datarows and then return the array of the string. I gonna use this for tag cloud.
Let say I have a table named Content_tbl, this table sa 3 fields (Title, Body, Tags). When storing data to this table I would like the Tags field to hold a comma separated words.
If Content_tbl has 2 records and on the first record my Tags field conatains “Tag A, Tag B” and on the second record my Tags field contains “Tag B, Tag C”. If I select all records I want the SQL to get the content of “Tags” field and return as a list like as follows:
Tag A
Tag B
Tag B
Tag C
and if possible to make it this way also:
Tag A 1
Tag B 2
Tag C 1
I hope you can help me with this problem. I’ll appreciate it very much.
Thank you,
Gary
You should store data in the csv format
Read about Normalisation
@Gary
You can start here: http://blog.sqlauthority.com/2008/08/17/sql-server-xml-split-a-delimited-string-generate-a-delimited-string/
It should be noted that in general, storing a CSV breaks the rules of normalization and is not considered good practice.
Hi Pinal,
In my machine i have sql server 2008. when i was trying to send mail from sql server 2008,i was not able to that.Bcoz in my machine sql server log tab there is no Database Mail tab.what i want do now,pls help me.Am very eager to send mail from sql server 2008.I have seen ur article in codeproject,same method am following.or anyother way is there…?
HI,
i want to know how can i get the list of users or no of users connected to my database remotely through any site.
thanx.
hi.
pls let me know how can i get the no of users connected to database and the load on the database.
thanx
One option is to use
EXEC sp_who
Hi Pinal,
I am a junior DBA. I was doing a Transactional log shipping of a database into a secondary/standby server and I got the error as :
Cannot open backup device
‘\\DS2\Demo_Database\Demo_Database_Mirroring.bak’. Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Please suggest me how to proceed further and fix this error and do the Transactional Log Shipping successfully.
Thanks in Advance
Make sure the file exists or the server has proper permission to access the file
Yes, the Full Control permission is given to the Backup folders and Destination Folders./ Problem is that for the First time the transaction log shipping was done. But when I deleted the transaction log and again set it. It throws me this error.
Please suggest.
Thanks,
Sumit
Hi Pinal,
I had SQL Server 2005 intalled on my pc and also installed 2008. I got the issue to start SQL Server Agent. I can’t see the instance of SQL server 2008 into Management studio connect to server screen in server name.
I need help. kindly reply its big pain for me. I have search for blogs and binging also. but I did not find solution.
Thanking you.
–
Regard,
Haresh
What is difference between Union and Intersect in SQL?
These details are availabe at SQL Server help file
Make use of them. If you still have doubt, post here
@Amarnath K
UNION appends the results of what comes after it to the results on the query before it.
INTERSECT returns only those records that appear in both queries.
Hi,
The Performance of a procedure after one year is slow in SSIS then what is the reason ?
Regards,
Ashok
It is not the performance of the procedure but may be the data volumn of the table
Hello,
I would like to stop receiving notigications for the new posts of your blog to my email.
How can I do that?
I think I found the way through the e-mails. I have deleted the subscription. So the last comment is not valid.
Thank you
Hello,
On my servers I always face a problem of SQL taking very large memory. My services are continuosly ineracting with MS SQL server, after some time task manager shows large memory usage by SQL. If I restart my services it starts working fine. But, again after sometime server gets slow because memory usage of SQL. Can you please guide how I can control this.
hi,
Problem wiht BCP.
1. declare @fileName varchar(200)
2. declare @bcpCommand varchar(300)
3. SET @fileName = ‘C:\Test\testbcp.txt’
4. SET @bcpCommand = ‘bcp “select chr_TextLines from pay_TextFile” queryout “‘
5.SET @bcpCommand = @bcpCommand + @fileName + ‘” -c -t, -Uxx -Pxx -S’ + @@servername
6. –print @bcpCommand
7. EXEC xp_cmdshell @bcpCommand
Please help
The Error is
1. SQLState = 42S02, NativeError = 208
2. Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name ‘pay_TextFile’.
3. SQLState = 42000, NativeError = 8180
4. Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
5. NULL
Hi,
4. SET @bcpCommand = ‘bcp “select chr_TextLines from mydb..pay_TextFile” queryout
The problem I guess was that I did not use the 3 part naming convention, and sql was looking for pay_TextFile (my table) in the master database.
Thanks,
Hello Sir
I am using MS SQL Server 2005, all services are working well but when i tried to use sql server reporting services then it shows following error
Cannot connect to Amit-PC
Additional Information
The Reporting Services Instance could not be found(Microsoft.SqlServer.Managment.UI.RSClient)
Please tell me how can i fix this problem.. n how to configure reporting services on my pc
Please Help me.. please tell me how can i do so..
hello dear,
i am a sytem administrator of an organization. the organization wonts me to connect sql server 2008 witch are in differt domain and forestes. i had hints on connecting sql server in the same forest but no hint at all in different forest. so sir could you pleas help me what to do(what steps to follow to do this )
Thanks in advance
note
i can ping to the commputers of different domains and i can access users of active directory in the other server witch is in different forest.
We have following scenario of maintenance schedule of database backup
Daily full Backup 12:00am
Deferential Backup Every 5 hours
Transaction log Backup Every 20 minute
1) Suppose our database crash at 23:55, so what will be database restoring scenario.
If full backup maintenance plan is already started at 12:00am, it will take around 2 hours to complete, deferential backup maintenance will run at 1:00am. So will scenario:
i) Deferential backup also start at 1:00am schedule time or wait for full backup to complete.
If both backup will start at their schedule time,
What will restore scenario, as on following situation?
ii) If both backup running , database crash at 01:05am
iii) If Full backup is completed and differential backup running and database crash 01:15am
iv) If deferential backup is competed and full backup is running.
Hi Pinal,
I’m impressed with your knowledge sharing here. My task includes writing a lot of scripts to upload raw data into MSSQL DB. Hence, I use many various ways, depending on the source file. Here is my question:
Assuming I have this two options to choose only, which method is faster to do bulk insert ?
(i) I notice from your blog, you use this method.
INSERT INTO JoinedTable (ID2,Col2)
SELECT 1,’First’
UNION ALL
SELECT 2,’Second’
UNION ALL
SELECT 3,’Third’
UNION ALL
SELECT 4,’Fourth’
(ii) I’m currently using this method, and I know is not very efficient.
INSERT INTO JoinedTable (ID2,Col2) VALUES 1,’First’
INSERT INTO JoinedTable (ID2,Col2) VALUES 2,’Second’
INSERT INTO JoinedTable (ID2,Col2) VALUES 3,’Third’
INSERT INTO JoinedTable (ID2,Col2) VALUES 4,’Fourth’
There can be up to >100k records for each upload. I’m using method (ii) by using vi text editor to format the raw data, as the data can come in various format, not conforming to bcp or MSSQL or delimited, etc.
Note: I welcome replies on other faster methods, but my primary question here is which one is THE faster method to insert ? (i) or (ii)
Thanks
Use method2 becuase I doublt UNION ALL will support upto 100K rows. Also it is better to keep the data in the text and use bulk insert to import them into a table
Pinal, Madhivanan,
I did read from Madhivanan blog earlier and may try out this method to load into staging-temp-tables first:
“BULK INSERT to table with specific columns”
Then proceed to massage the data into actual tables.
It will help, as bcp takes <1 minute but the manual insert may take 3-5 minutes for 100k. Imagine if I need to do it for some initial full upload of 10 million records.
More feedback are welcomed. Thanks
As I said, have data in text file and use bulk insert
Hi Pinal
We have following scenario of maintenance schedule of database backup
Daily full Backup 12:00am
Deferential Backup Every 5 hours
Transaction log Backup Every 20 minute
1) Suppose our database crash at 23:55, so what will be database restoring scenario.
If full backup maintenance plan is already started at 12:00am, it will take around 2 hours to complete, deferential backup maintenance will run at 1:00am. So will scenario:
i) Deferential backup also start at 1:00am schedule time or wait for full backup to complete.
If both backup will start at their schedule time,
What will restore scenario, as on following situation?
ii) If both backup running , database crash at 01:05am
iii) If Full backup is completed and differential backup running and database crash 01:15am
iv) If deferential backup is competed and full backup is running.
Hi,
I have learn the queries in oracle, and doubts the following conditions…
product table with data as below
Pro_Id Pro_Name Order_Date
P1 Prod A 28-JUN-10
P2 Prod B 27-JUN-10
P3 Prod C 28-JUN-10
I want query in oracle to get the following conditions result…
1) Get 28-JUN-10 ordered prod_name
2) get prod_name in between ’28-JUN-10′ to ’28-JUN-10′
Thanks
select Pro_Name from product
where Order_Date>=’28-JUN-10′ and Order_Date<'29-JUN-10'
I have use the following query, but it not retrieve the ’28-JUN-10′ result.
select Pro_name from product where order_date>=’28-JUN-10′ and Order_Date<='28-JUN-10'
bez i want to given the same dates in from and to date criteria…
If the Order_Date has time part too , it wont work
You need to add one day to the from date and use the method I posted
Hi Dave,i am venkat presently working as sqlservr data base and BI devloper , i am intrested to certification in the same,can u pls give me some guide lines to do certification and also suggest me preparing material
Hi Dave,
I am kinda new abut here is what I am trying to do. I have SQL Table that is laid out as follows:
i.e data is arranged as binary tree fashion Each parent node can atmost have 2 childs (left node L & Right Node R)
Aim: my obejective is to retrive sub tree for any node
ID Name Supervisor NodeType
1 Mike 0 0
2 Dave 1 L
3 Gary 1 R
4 Sally 2 L
5 Mary 2 R
6 Susan 3 L
Hi Pinal,
I have created a package to import excel path. But I am facing one problem, there is a column in the excel which has values like “$0.031380″ , “$0.035020″. It seems that the column has been formatted, and the ‘$” sign has been added. When I import the file with the SSIS package developed, the values gets rounded off. e.g. “$0.031380″ is imported as 0.0314. How can I overcome this? Do I need to write any script? Can you please help me.
Thanks in advance,
Rupesh
What is the datatype of the target column?
Make sure you used decimal(18,6)
Hi Madhivanan,
I have tried that also, but it did not work. However I’ll give it another try.
I want to know, is it possible to write some script and remove the formatting of the columns in the excel (this script I want to use before reading the excel)?
Thanks ,
Rupesh
No. You can not format EXCEL using a sql script
You need to do it before executing a query
Rupesh,
We use NUMERIC(28,8), although the users used up to 6 decimal points only.
Also, to remove the $, we will normally load into temp table. Then use the REPLACE command before upload into actual table.
The other method is to write VB/C#.net or Excel macros to remove the $ from the column, then only use MSSQL to load in.
“we will normally load into temp table”
- to clarify, the temp table is varchar(50) column, in order to accept it as string value with $ or other currency.
- then REPLACE
- then upload into actual table with numeric(28,8)
Hi Pinal.
Can you help me whit this …?
I have a Transactional Replication, this week the replication is marked inactive and need to be reinitialized.
I manually copy de info that i need on the suscriber and all the data is the same on both sides.
What a i have to do to mark de replication active again without do re Snapshot in the reinitialization process ???
Thanks in advance …
Saludos desde Mexico !!
Hi i have a qucik and imp question,
in our DB we have 6 users and Every one logs in with the same login, some one has dropped a really Imp table so we are trying to figure out who it is and at what time the table is dropped,
can you please give me a query or info to check this
really appreciate your Help
Thanks in Advance
Which version of SQL Server do you have?
If the version is later than 2000, you could have written a DDL trigger for this. Otherwise you should have run the profiler
Hi Pinal,
Can you please write somethig about the techiques of doing a Query optimization (by Graphical Execution Plan). All the tricks that should be measured for optimizing a query or stored procedure.
As I am a new member of this blog, I am unaware of if this had already been posted before. I have a keen interest of knowing the tricks/techiniques of Optimizing an inline query/stored procedure.
As I am a Jr. DBA, sometimes I ofetn faces situation of poor performing queries and I don’t have any idea how to optimize them.
I would be very thankful if you consider my request. I guess the other members will also gain some ideas from it.
I will be waiting for your reply and the techniques for learning the query optimization.
With Best Regards,
Sumit
hi i wanted to subscribe to ur site
I have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C).
For Example I have two tables Table_A and Table_B as below:
Table_A
———————-
RecordID StudentID Dept BookID
1 123 CS 456
2 123 CS 345
3 223 TE 190
Table_B
———————-
RecordID StudentID Dept BookID
1 123 CS 456
2 223 TE 345
3 223 TE 190
and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:
Table_C
———————-
Sim_RecordID Sim_StudentID Sim_Dept SimBookID
1 1 1 1
1 0 0 1
1 1 1 1
Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. I hope i have clearly defined my probelm. any help would be appreciated.
Hi Madhivanan,
Can you give me suggestions or query or simple stored procedure to the above issues….
Thanks
Hi madhivanan,
can you suggest for me above tasks….
Thanks
@ samy / @kamaran,
you can use the below query to get the Result as shown in table_c
Select
CASE WHEN Table_A.RecordID=Table_B.RecordID THEN 1 ELSE 0 END,
CASE WHEN Table_A.StudentID=Table_B.StudentID THEN 1 ELSE 0 END,
CASE WHEN Table_A.Dept=Table_B.Dept THEN 1 ELSE 0 END,
CASE WHEN Table_A.BookID=Table_B.BookID THEN 1 ELSE 0 END
From
Table_A
INNER JOIN Table_B
ON(Table_A.RecordID=Table_B.RecordID AND Table_A.BookID = Table_B.BookID)
Hi Bhaskar,
Thanks a lot for your valuable reply….
I m going to start my new company for software development in .net with db as Sql 2008.So in simple lines can u tell me that which edition of .net and sql 2008 shoul I purchase.Please
Hello Pinal,
I am just learning SQL2005, and was wondering if there was a way to automate my current task. I have over 450 database, wherein each database contains the same table structure. Instead of fishing though 450 database looking at 1 specific table for a value greater then 2 (which I create a query). I was wondering if there is a way to search across all databases that contain this table name, where said table has a colume with a value that is greater then 2
Thank you for your time in addressing my question.
Hi Pinal,
If a table have 1 lac record, by procedure dump to other table for reporting purpose. Next day that table have 3 lac record then will we again port all the record? is there any process to identify incremental record.& if some previous records are updated then how to identify the update previous record? & these changes how to reflect in dump table?
Regards,
Ashok
Can anyone help me in explaining the script given below:
I want to know how CASE Statement is working in the script. Its Urgent
DECLARE @Text NVARCHAR(2000)
DECLARE @StringDelimiter CHAR(1)
SELECT @Text = ‘This T-sql will split senteneces into rows.’+ ‘How many rows will be returned?.’+ ‘M.a.y.b.e..n.n.o.e.?’, @StringDelimiter = ‘.’;
With Tally (Number)
AS
( SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number UNION ALL SELECT Number + 1 AS Number FROM Tally WHERE Number 0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) – 1
ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ”
AND CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
THEN LEFT(@Text,Number -1) ELSE RIGHT(LEFT(@Text,Number – 1),
CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0 THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
END AS SPLIT FROM TALLY WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter OR Number – 1 = LEN(@Text))
Can somebody explain me How this script actually is working ?
especially Case Statement
Can somebody explain me How the script given below is working ?
especially Case Statement
DECLARE @Text NVARCHAR(2000)
DECLARE @StringDelimiter CHAR(1)
SELECT @Text = ‘This T-sql will split senteneces into rows.’+
‘How many rows will be returned?.’+
‘M.a.y.b.e..n.n.o.e.?’,
@StringDelimiter = ‘.’;
With Tally (Number)
AS
(
SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number
UNION ALL
SELECT Number + 1 AS Number
FROM Tally WHERE Number 0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) – 1
ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ”
AND
CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
THEN LEFT(@Text,Number -1)
ELSE RIGHT(LEFT(@Text,Number – 1),
CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
END AS SPLIT FROM TALLY
WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter
OR Number – 1 = LEN(@Text))
OPTION (MAXRECURSION 32767)
This is the right script ……………..
DECLARE @Text NVARCHAR(2000)
DECLARE @StringDelimiter CHAR(1)
SELECT @Text = ‘This T-sql will split senteneces into rows.’+
‘How many rows will be returned?.’+
‘M.a.y.b.e..n.n.o.e.?’,
@StringDelimiter = ‘.’;
With Tally (Number)
AS
(
SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number
UNION ALL
SELECT Number + 1 AS Number
FROM Tally WHERE
Number 0THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) – 1 ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ”
AND CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
THEN LEFT(@Text,Number -1)
ELSE RIGHT(LEFT(@Text,Number – 1),
CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0
THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
END AS SPLIT FROM TALLY
WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter
OR Number – 1 = LEN(@Text))
OPTION (MAXRECURSION 32767)
Post the workable code. It trows the error
Hello PinalKumar
I teach SQL at the University here in the USA. Every year you become a celebrity among my students without me even telling them about you. It has been a fun experience each year to see how soon until everyone is using your site for their research. It usually happens when we get to advanced queries that have Ranking functions or CTEs. Your examples are vivid, clear, and explain it in a way where the student feels confident about what they have learned. You deserve great success for what you are doing.
I am also the Author of the Joes 2 Pros SQL book series and would like to pay tribute to you in some upcoming books and training DVDs. Perhaps pointing people to your web site and maybe giving them a snipped from an article for you to choose. All of them are good. Would love to let all my readers know about you. Contact me directly when you want more materials.
-Rick A. Morelan (Joes2Pros)
Sir,
I am honored with your kind word. Many many thanks.
I am very much glad and happy with your comment.
I am sending separate email to you.
Kind Regards,
hi
I want to create a view which will give the output
…..
…..
Value from the table
…..
…..
–This is the problem area, I want to display “Others” as the last item. I dont mind using any symbol in front of “others” to make it the last item. In fact I tried “{” and “|” as they come after “z” in the ASCII table.
I can not use the ID to sort as I want my Discipline to be sorted.
————
SELECT DISCIPLIN AS Discipline, DISCIP_ID
FROM dbo.mDISCIPLIN
UNION
SELECT ” AS Expr1, 0 AS Expr2
UNION
SELECT ‘|OTHERS|’ AS Expr1, 999 AS Expr2
ORDER BY DISCIPLINE
———–
Please help.
try using Union All instead of union as union filters the duplicate rows and sorts the resultset
hi Pinal,
I am not able to copy my production diff backup using the cmdshell command .i want to tell you i am not a domain user on the server becouse server start under lacal service.
so any other way so i can copy using that command
exec xp_cmdshell ‘copy \\165.145.34.289\G$\Database_backup\Diff_backup_smswwil\latest_backup..bak \\165.145.34.289g$\diff_backup\latest_backup.bak’
Regards,
Pawan Singh
Dear sir,
Can u give me a documentation about the new features in sql server 2008…Actually i have joined a new office and i have been assigned a task to give a presentation on New Features in Sql Server 2008 to almost 50 people. I have a good knowledge of Asp.net and sql server 2005
Thanks & Regards
Sumit Thapar
Dear Friends,
Would anybody please answer this Question..
Q) CPUs can dynamically be added to a running system, by using ____________ feature
1)Cold Add CPU
2)Hot Add CPU
3)Dynamic add CPU
4)Passive add CPU
Thanks and Regards,
Riyaz
Dear dave,
me working in server 2008, i got i month gap in that time i forgot my sa passwd but in that i have a lot of dbs more sps and almost my all my work through sa only they work.
now can i change password. if i changes any my processes effect is there any way to get all should be work properly.
waiting
regards
prasad
Hello sir
I am facing one problem in sql 2008.when I give a filter of date in view it becomes slow otherwise it runs fast.I can not understand why this happen and what is the solution.Please reply.
Hello sir
I am facing date filter problem in sql 2008 since sql 2008 is installed.When i give date filter in query it becomes slow and without date filter it runs fine.Can you give me solution for this?
Why is that after I installed SQL Server 2008 and selected Default Instance, the Server name in the Login screen says (local) but not MSSQLSERVER? This is the Full version of the SQL Server and not Express.
We have migrated from sql 2000 to 2008.But after installing sql 2008 i face the problem in selecting dates.If i select 1year period in query or view it runs fine but if i select period of less than 1 year it takes time to run.For ex. If i select period from 1-apr-2009 to 31-mar-2010 then query or view runs very fine and takes 1 min to run but if i select period from 1-jul-2010 to 11-jul-2010 then it takes 5 min to run.Please give the solution.
Sir,
Good Morning.
I have doubt regarding SQL Database. I am using SQL Server 2008 for 2005 database.
I want to know the login name , ip address, query or procedure executed at the back end i.e. in database.
Because I want to catch the person who is executing the scripts at the back end.
Thank You.
With Regards,
Karthik
Hi Pinal
I’m documenting a database, all it’s tables,views, procedures etc and their dependencies. Which is fine for all objects in the database, but if I have a view or procedure that depends on a Linked Server e,g
SELECT DRKY AS [Asset Status Code], DRDL01 AS [Asset Status Code Description]
FROM MIF.JDE_PRODUCTION.PRODCTL.F0005 AS F0005
None of the standard ways in SQL Server can return the table F0005. Do you have suggestions
Regards
Robert
Hi pinal
I am daily reading your blog and i get lots of knowledge about sql server from you so thanks for that.
I found one script about to know more about how to work indentity column. but i have confusion about that.
below are that script.
*** 2010-07-07
[18:52:32] *** rahul is Online
[18:52:28] CREATE TABLE #Temp1
(
Temp1ID int NOT NULL IDENTITY (-2147483648,1),
Temp1Value char(1) NOT NULL
) ;
CREATE TABLE #Temp2
(
Temp2ID int NOT NULL IDENTITY (-1,-1),
Temp2Value char(1) NOT NULL
) ;
INSERT INTO #Temp1 (Temp1Value) VALUES (’1′) ;
INSERT INTO #Temp1 (Temp1Value) VALUES (’2′) ;
SET IDENTITY_INSERT #Temp1 ON ;
INSERT INTO #Temp1 (Temp1ID,Temp1Value) VALUES (100,’3′) ;
SET IDENTITY_INSERT #Temp1 OFF ;
INSERT INTO #Temp1 (Temp1Value) VALUES (’4′) ;
INSERT INTO #Temp2 (Temp2Value) VALUES (’1′) ;
INSERT INTO #Temp2 (Temp2Value) VALUES (’2′) ;
SET IDENTITY_INSERT #Temp2 ON ;
INSERT INTO #Temp2 (Temp2ID,Temp2Value) VALUES (100,’3′) ;
SET IDENTITY_INSERT #Temp2 OFF ;
INSERT INTO #Temp2 (Temp2Value) VALUES (’4′) ;
select * from #Temp1
select * from #Temp2
DROP TABLE #Temp1
DROP TABLE #Temp2
Thanks
Mori Ajay
Hi,
Please Redirect this issue to concern person if i m hitting in between the wrong thread…
Q: I want to create some hypothetical indexes in order to fire some issues..i googled a lot but couldn’t figure out how to create and retain a hypothetical index..while we run index tuning wizard it creates the hypothetical indexes but as soon as it finishes it deletes all the hypothetical indexes automatically.
Please let me know the solution to the above query.
Thanks & Regards
Vijay Gupta
Hi Pinal,
i am regullar reader of your blog, your postings are very much helpful for me. i am working as a Microsoft Report / ETL developer. give me some examples of Gauge in SSRS 2008.
Thanks in Advance
-Raj
Hi Pinal,
Can you please tell me how to change of location for Reporting server http logs?
I tried to change with jbelow instructions but it’s not working at all and error message come out when i edited the web config under reporting server folder.
· c:\Program Files\Microsoft SQL Server\Reporting Services\ReportManager\web.config
· c:\Program Files\Microsoft SQL Server\Reporting Services\ReportServer\web.config
· c:\Program Files\Microsoft SQL Server\Reporting Services\ReportServer\Bin\ReportingServicesService.exe.config
Add the following tag under RSTrace in all the above files:
Be sure that you apply the same permissions to the new path folder that is on the LogFiles folder.
Restart the ReportServer Windows Service
your help will be appreciate.
Thanks .
Naing
Hi,
We installed some applicaions and SQL server express 2008 and after that we renamed the user account Administrator .Once that was done ,all admintrator dependable services LOGON AS was changed to the new user’s name and password.But i can see one of my JOB failing in the SQL.When we run that JOB it fails
07/08/2010 13:27:00, SyncUserPrivilegesTables,Error,0,WINDOWS-B7L8D0H, SyncUserPrivilegesTables,(Job outcome),,The job failed. Unable to determine if the owner (WINDOWS-B7L8D0H\Administrator) of job SyncUserPrivilegesTables has server access (reason: Could not obtain information about Windows NT group/user ‘WINDOWS-B7L8D0H\Administrator’ error code 0×534. [SQLSTATE 42000] (Error 15404)).,00:00:00,0,0,,,,0
Can u give Sql server 2005 interview Question And Answer for 6 Months Exp….
Hi Pinal,
We have scheduled a job to pull data from our client’s database (sql server 2000) setup as a linked server into our SQL server 2005 DB. The job selects records and updates a flag in the source (client) DB to indicate that the record has been pulled.
The select statement executes within a second, however the update statement is taking on average 12 mins to complete; and at times gives the error ‘Cannot get the data of the row from the OLE DB provider “SQLNCLI” for linked server’. in case where the error is received, on checking the flag status, we have found that the flag has been updated.
The update statement only has the primary key in the ‘where’ clause.
The client has confirmed that there are no triggers on the table in question and there’s no activity on the database when the job is scheduled to run.
There is another table in the client’s DB which we tested for update statements and the update completes within no time.
Any ideas on what else we might be missing here that’s causing the delays and needs to be looked into??
Thanks.
SELECT TOP 1 ApplicationNumber
FROM (SELECT TOP 5 ApplicationNumber FROM Outbound_Data od )a ORDER BY a.ApplicationNumber DESC
SELECT TOP 1 ApplicationNumber
FROM (SELECT TOP 5 ApplicationNumber FROM Outbound_Data od ORDER BY 1)a ORDER BY a.ApplicationNumber desc
what is diff between this 2 Query? why its give diff Result?
Hi Pinal ,
this is Shital kasliwal.
I have a one prob.
CREATE TABLE temp
(
ID numeric PRIMARY KEY IDENTITY(1,1),
name varchar(25)
)
insert into temp
select ‘RAM’
union all
select ‘Sham’
union all
select ‘vijay’
union all
select ‘RAM’
union all
select ‘Sham’
union all
select ‘vijay’
union all
select ‘RAM’
union all
select ‘Sham’
union all
select ‘vijay’
union all
select ‘RAM’
union all
select ‘Sham’
union all
select ‘vijay’
union all
select ‘RAM’
union all
select ‘Sham’
union all
select ‘vijay’
union all
select ‘RAM’
union all
select ‘Sham’
union all
select ‘vijay’
SELECT TOP 1 id
FROM (SELECT TOP 5 id FROM temp ) a ORDER BY a.id desc
SELECT TOP 1 id
FROM (SELECT TOP 5 id FROM temp od ORDER BY 1)a ORDER BY a.id DESC
both Query Given Diff result Why?
How is it possible?
Hi Shital,
In the first query since there is no order by clause , the top 5 results can be retrived from #temp can be in any order
In the second query , the top 5 will be (1,2,3,4,5) since u have given the “order by 1″ it selects only the top 5 order by ID ASC
Hi Pinal,
I want to get SQL Server’s cpu usage and memory usage (which gives like 250 mb or %x of cpu – %y of ram)
And i want to get current session on sql server, how many session is on the sql server, it should be live, can you help me please ?
Hi Pinal Dave,
I need query for this tables.
Category
————————-
ID
CategoryName
SubCategory
—————————
SID
C_ID
Value
Query:
————————
ID, Category Name1, Category Name2….etc
1 Values of SID Values of SID
2 Values of SID Values of SID
Please let me know how to write a query for this solution
Hi,
I have try to use the following query, but its appear error message …., please give me the correct query or tell that error message reasons…
EXEC dynamic_pivot
‘Select C.ID,C.CategoryName,S.CateValue From TblCategory AS C INNER JOIN TblSubCategory AS S
ON C.ID = S.C_ID’,
‘CategoryName’,
‘CateValue’
(3 row(s) affected)
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘for’.
Thanks to all
Hi Pinal Dave,
You Can make a Link between MSSQL And MSAccess by using
linked Server
using this Commands
EXEC sp_addlinkedserver
@server = N”,
@provider = N’Microsoft.Jet.OLEDB.4.0′,
@srvproduct = N’OLE DB Provider for Jet’,
@datasrc = N”
GO
– Set up login mapping using current user’s security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N”,
@useself = N’TRUE’,
@locallogin = NULL,
@rmtuser = N”,
@rmtpassword =
GO
But in the msaccess has a password no one can make this link
can you help me in this ?????
hello sir,
i have a query regarding switching the data from one table to another.
When i try with following query then all the records copied into another table but if i want to copy only few columns then its not working.
insert into singhtable2 select * from singhtable1;
Sachin Singh
@Sachin.
You need to specify column names if you want to restrict columns in insert statement.
Sample Script:
Insert into Destination_Table ( Cola, Colb, Colc)
Select Cola, Colb, Colc
From Source_Table
Explanation: What we are doing in above statement is, we are getting data for three columns from source table and inserting into destination table in the three columns, and the three columns are given in brackets.
Check out books online, or google on topic, Insert statement in SQL Server, to find more examples.
Let us know if you need help with this topic.
~Peace.
Hi,
Can you use the following query….
SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons
Thanks,
Dear Samy,
Make your query something like this
Insert Into Persons_Backup(Column,AnotherColumn)
SELECT LastName,FirstName From Persons
Hope it will help
regards,
Adnan
editing my own reply
Insert Into Persons_Backup(LastName,FirstName)
SELECT LastName,FirstName FROM Persons
Hi,
Can tell the difference between these queries.
Option (1)
SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons
Option (2)
Insert Into Persons_Backup(LastName,FirstName)
SELECT LastName,FirstName FROM Persons
Thanks
Option 1 creates a new table from the table named Persons
Option 2 inserts data to already created table from Persons
Hi Pinal,
I have been a follower of your blog and I have a couple of questions for you. Do you have any tutorials or any link for tutorials on SSIS and SSRS other than the one available from MSDN?
Thanks for your help in advance.
@Sandeep,
Check this out. This is from Microsot Learning website, You will find some paid videos but lot of them are for free.
There are tons of free Videos on BI.
Here is the link:
http://learning.microsoft.com/Manager/BrowseResults.aspx?browseval=tt&pid=150&cid=150&nav=trainingtype%3aE-Learning&nav=productandtechnology%3aProducts+and+Technologies%2fServer+Technologies&nav=productandtechnology%3aProducts+and+Technologies%2fServer+Technologies%2fMicrosoft+SQL+Server&qry=&navclicked=1
I got this link from one of my friend. Pass it on to other who might need it.
~ Peace.
Dear Pinal sir,
Can u plz guide me how to loop through rows of table and fetch values one by one without using cursors..
Thanks & Regards
Sumit Thapar
Hi Pinal
I havent check your all articles, but gone through some of them. It’s really excellent. Recentally i have started working as a SQL Server DBA(2008), i would like to learn basic DBA stuffs, if its there any link or blog please let me know. Thanks a lot
Pinal,
i have a question regarding Insert Statement.
i have a scenario to insert into a table.
for example:
INSERT into table2 values
([lsp_id]
,[prev_lsp_id]
)
select
ISNULL([lsp_id],”),
ISNULL([lsp_id],”)
) from table1
i need use calculated value ISNULL([lsp_id],”) in both the
fields instead of writing two times because i do have very big transformation if i write 2 times it will consume more time.
for now i am inserting null value into second one and updating it using bulk update.
how can i bulk insert calculated value more than once in a insert statement ? Please help me on this one.
Hello Dave Pinal,
Greetings, I’m a big fan, any time I need to research something, your site is at the top of my list.
I have a problem running a store procedure and sending the results to a table:
Here is my script (written on MSSQL2000):
CREATE TABLE #tUsers
(
UserName sysname NULL,
GroupName sysname NULL,
LoginName sysname NULL,
DefDBName sysname NULL,
UserID smallint NULL,
SID smallint NULL
)
INSERT #tUsers
EXEC SP_HELPUSER
However, this script fails when the database is using ALIASES, as the EXEC SP_HELP user part returns TWO results sets instead of just one.
Any idea on how to workaround this dilema?
Any help would be greatly appreciated.
Thank you
Miguel
Hi Pinal,
I am regular follower of your site. I have problem while deleting the database user. I have 3 databases in my server and have users and able to delete a user in one database with the help of below query.
select dp2.name as role, dp1.name as owner
from sys.database_principals as dp1 inner join sys.database_principals as dp2
on dp1.principal_id = dp2.owning_principal_id
where dp1.name = ”
But when i try to delete the other database user, it is not working. Can you please help me in this regard.
M. Mallikharjuna Rao
@Malikharjuna
Can you please share with us what script you are using to delete a user from specific database
Also, please paste your error message that you got while deleting user from database.
I believe you are using SQL Server 2005 or above version.
Above details are required in order to answer your question.
~ Peace.
I am using SQL Server 2005 SP3. Please find below the script which i am executing for deleting a user under database.
select dp2.name as role, dp1.name as owner
from sys.database_principals as dp1 inner join sys.database_principals as dp2
on dp1.principal_id = dp2.owning_principal_id
where dp1.name = ‘username’
The above script i got from some other community.
Assume that i have three database like DB1, DB2 and DB3 and have users DB1user, DB2user and DB3user. I have successfully delete DB1user from DB1 database. But when i execute the above script for removing DB2user, it is executing but not showing roles for changing the owner.
Regards
M. MallikharjunaRao
@Mallikharjuna
How can you delete a user executing a select statement. You CANNOT delete a user by executing a select statement.
Please provide more information.
~ Peace.
Gud Evening Sir,
Sir I am working as a system administrator at patiala (Punjab). I have connecte my client PC to server name sscspta. i copied data from server to my client machine by loging using \\sscspta command in run. But from some days it gives the error \\sscspta is not accessible. you might not have the permissions……………. contact your system administrator……..
Please help me.
Thank’s in advance.
@Dalbir
It is very abivious from the error message, that you lost access to shared folder on your remote server.
Assign proper permissions on folder, going to folder properties and then go to security tab, and assign proper permissions to Windows ID with which you are copying data from server to Client.
~Peace.
Dear Sir,
My name is Mahender Singh, presently i am working in Getit Infoservices Pvt. Ltd as a Juniour Database Administrator due to some personal reason i couldn’t complete my dba training, i am working here for last two years. When i face some crucial problems doing database job your article always help me & i heartly thanks to you. i would like to take sql database training for you, but i can’t left my job because my family depends on me.
can u help me?
Regards
Mahender Singh
Hai,
In one table I have values as
Account Ledger
Account Debit
Cost of Balance
now i am writing query as:
SELECT list FROM tablename
I want to add ‘ALL’ which is used in another application.
So I am write as:
SELECT list FROM tablename
UNION
SELECT ‘ALL’ AS list
I get output as
Account Ledger
Account Debit
ALL
Cost of Balance
But the required output for me must be as:
ALL
Account Ledger
Account Debit
Cost of Balance
That is I want to display ALL at the top.
Thank You.
Hi Karthik
you can use UNION ALL as shown below
SELECT ‘ALL’ as LIST
UNION ALL
SELECT LIST FROM
(
Select ‘Account Ledger’ AS LIST
UNION
SELECT ‘Account Debit’
UNION
SELECT ‘Cost of Balance’
) tablename
Hi ,
I’m trying to implement search for my website, i’m storing content in my db ,and i manage to find exactly the page and article but i don’t know
how to return a search snippet from my content to display in search results (the snippet doesn’t have to be a constant length) and i just wondering how exactly i can manage to do it?
any suggestions?
Thanks.
Can you post some sample data with expected result?
Sir i am working on a real estate website project.. I have created a database and tables but not ubderstanding how to create relationship b/w them. Plzz help. the tables are as below,
USE [RealEstate]
GO
/****** Object: Table [dbo].[Buyers] Script Date: 07/18/2010 19:58:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Buyers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PropertyType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Location] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Area] [float] NULL,
[Budget] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
==========================================================
USE [RealEstate]
GO
/****** Object: Table [dbo].[Registeration] Script Date: 07/18/2010 19:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Registeration](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmailID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PassQ] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PassAns] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Registeration] PRIMARY KEY CLUSTERED
(
[UserName] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Registeration] WITH CHECK ADD CONSTRAINT [FK_Registeration_Registeration] FOREIGN
KEY([UserName])
REFERENCES [dbo].[Registeration] ([UserName])
GO
ALTER TABLE [dbo].[Registeration] CHECK CONSTRAINT [FK_Registeration_Registeration]
==========================================================================================
USE [RealEstate]
GO
/****** Object: Table [dbo].[Sellers] Script Date: 07/18/2010 19:59:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sellers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ListingDays] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Location] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Area] [float] NULL,
[TotalPrice] [money] NULL,
[Negotation] [varbinary](50) NULL,
[Description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PropertType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Photo] [image] NULL,
[ListedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Sellers] PRIMARY KEY CLUSTERED
(
[PropertType] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Sir,
I need a query to display the column name in a table, that column having the value ‘Sales’
Refer this post. This exactly does what you wanted. Let me know if you have any problems
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
Hello sir,
we are developing a software using vs2005 + sql2005.
And we are facing a problem.
We want to develop this software in gujarati language.
So we stored the data in gujarati but when we fire a query from front end in gujarati to sql the database doesnot produce any output .
If we use access the same thing works fine but with sql2005 it does not so please sir help us out.
Thank you for your support and help.
Regards – Sunil kapadia
I want to write a trigger that will create a unique document ID upon initial “SAVE” of new record, but am drawing a blank as to how to do it. . .
I want the ID to be in the format of YYYY-###
YYYY = ‘current year’
‘-’
### = (this should be a 3 digit sequential # that starts at 001 on Jan 1st of each year — so that the first new record saved on Jan 1, 2011 = 2011-001
but the subsequent documents would be numbered in order saved (i.e. 2011-002, 2011-003, 2011-004 . . . 2011-100 etc)
And same for Jan 1, 2012 – it’s unique ID would be 2012-001 and so on.
Can you give me some direction?
This is what you should use
select cast(year(getdate()) as char(4))+cast(datepart(dayofyear,getdate()) as char(3))
As I understand it, this would give me the format I want but
. . . wouldn’t it give me the same # for all records saved on the same day each year?
I need a trigger that will “look-up” the # of the last record saved for the current year and increment by one (1) for the new record; which in combination with the ‘YYYY-’ will become the unique reference ID. This will allow me to prioritize and keep a track of the number of records submitted by year.
Have an identity column and use computed column
declare @t table(id int identity(1,1), document_id as cast(year(getdate()) as char(4))+right(’00000′+cast(id as varchar(10)),5),othercols….)
HI ..
TOPIC:SQLAgent job behaviour
I deleted sql agent jobs couple of days back and i am still getting the failure alerts for those jobs stating not able to retive the step for the job.
I checked all the system tables related to the jobs and found nothing related to those jobs.
What could be tha cause of this .Does it have anythin to take care about the temporary files.If yes which temp files do i need to delete.
Thanks you .
I’m trying to write a query that allows me to calculated the number of days elapsed between admissions.
Table Example:
PatientAcct|AdmissionDate|DischargeDate|Facility
1234567|1/1/2010|1/5/2010|WMC
1234567|1/6/2010|1/10/2010|WMC
2345678|2/1/2010|2/2/2010|WMC
3456789|2/3/2010|2/5/2010|WMC
3456789|2/7/2010|2/10/2010|WMC
3456789|2/11/2010|3/1/2010|WMC
Current Query: (it calcuates the difference between the dates without checking if the disharge date occurred before the admission date)~(not what I want)
SELECT PatientAcct, AdmissionDate, DischargeDate, Facility,
DATEDIFF(day, DischargeDate,
(SELECT MAX(AdmissionDate) AS AdmissionDate
FROM dbo.Test_Readmission AS B
WHERE (A.PatientAcct = PatientAcct) AND
(A.AdmissionDate > DischargeDate))) AS ElapsedDay
FROM dbo.Test_Readmission AS A
(What I would really want is a query which matches the dates then ranks the discharge dates such that when the discharge date occurs before the admission date it does a datedifference to calculate the interval between the dates)
Anyone know what the code is to get it to calculate the date from last
discharge date to current admission date? I’m creating a SQL view in SQL
2005. Thanks for help!
Dear sir,
i am a regular user of your blogs…..i am facing a big problem……i am working on SQl reporting services…..I have a table Contact and another one Payment…in contact table i have all details about a user and in Payment i have details about the payment user has given….htere is a column named dated in Payment table which states the date on which the payment has been given……
Now the situation is my client requires a report which shows details of all those users who have made a single payment for each year for last five years…..means only those users need to be shown on the report who have made only 1 payment for each year starting from July 2005 till July 2010…..The year start from 1 July till 30 June next year……What i am doing is am using five queries for five separate years and then making an intersection for all queries…..so the result is all those users who have given only one payment for each year for five years……But the issue am facing is the query is taking 1 minute 38 seconds to execute…Is there some better way to do it
Thanks & regards
Sumit Thapar
@Sumit.
Would it be possible to provide a sample data with your script, how you are doing it right now. It would be helpful to debug.
The sooner you provide, sooner you will get solution.
How many no. of records are we talking about here. If not too many, then I am sure, there will definitely be a way to improve performance of your query.
~ Peace.
hi imran,
Thanks for the reply…i ll give u a scenario…..suppose i have entries for each user date wise…..like user 1 made a payment on 27 december 2005… now i take the year from 1 july 2005 to 30 june 2006…now user 1 has one entry for this year…there might be many users like this one who have just contributed only one payment for an year…. i have to fetch the records for all these users…..what am currently doing is something like following:
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2009-07-01′,101)
and CONVERT(DATETIME,’2010-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2008-07-01′,101)
and CONVERT(DATETIME,’2009-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2007-07-01′,101)
and CONVERT(DATETIME,’2008-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2006-07-01′,101)
and CONVERT(DATETIME,’2007-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2005-07-01′,101)
and CONVERT(DATETIME,’2006-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
as you can see each query gives me the no of users who gave only one payment for that year…after that i make an intersect to find all the common records for these 5 years to get all the records…..Please check and give me some better solution..
thanks & regards
Sumit Thapar
Hi Pinal,
I want to upload a file from a PC to FTP using SQL Server 2008. I used the below link’s guidance
[link removed.]
and I was able to copy the source file to the ftp site. But, the copied or destination file is empty. Can you please let me know where I am going wrong?
Thanks a lot in advance.(I am not well versed in SQL but my team SQL DBA has met with an accident that I am responsible to complete this task in a day so please help me).
Hi,
Is there’s any SQL statements that will help us return a NULL, if a match is not found and if a match is found I want that value to be displayed.
ex
CREATE TABLE table01 (field01 varchar(15), field02 int)
INSERT INTO table01(field01, field02) VALUES (‘One’,1)
–This is a satisfying selection
select field01 from table01 where field02 = 1
–so the output should be “One”
–This is a non satisfying selection
select field01 from table01 where field02 = 2
–so the output should be “NULL”
Thanks in advance
hi shibu,
try case statement in ur query…..it can help
Thanks
Sumit
If exists(select field01 from table01 where field02 = 2)
select field01 from table01 where field02 = 2
else
select null
sir
i wanna know about the performance of
select *
and
select col1,col2,….
which is better and why
what the step perform by them ?
Use the second method with explicitely specifying the column names. If there is any column added newly, your query still sorks fine. But * will reflect it too
hi imran,
Thanks for the reply…i ll give u a scenario…..suppose i have entries for each user date wise…..like user 1 made a payment on 27 december 2005… now i take the year from 1 july 2005 to 30 june 2006…now user 1 has one entry for this year…there might be many users like this one who have just contributed only one payment for an year…. i have to fetch the records for all these users…..what am currently doing is something like following:
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2009-07-01′,101)
and CONVERT(DATETIME,’2010-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2008-07-01′,101)
and CONVERT(DATETIME,’2009-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2007-07-01′,101)
and CONVERT(DATETIME,’2008-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2006-07-01′,101)
and CONVERT(DATETIME,’2007-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
intersect
SELECT * FROM (
SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2005-07-01′,101)
and CONVERT(DATETIME,’2006-06-30′,101)
and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
WHERE tbl.cnt=1
as you can see each query gives me the no of users who gave only one payment for that year…after that i make an intersect to find all the common records for these 5 years to get all the records…..Please check and give me some better solution..
thanks & regards
Sumit Thapar
Hi,
I want to delete the Project Name from the Explorer. if i built the any report buy the visual studio then the same reflect on explorer if i want to delete the some reports from the explorer than how can i do the same.
Please suggest me the same ASAP. i am still waiting for your reply..
Warm Regards,
Krishna Chaudhary
Hi,
I want to delete the Project Name from the Explorer. if i built the any report by the SSRS visual studio then the same reflect on explorer if i want to delete the some reports from the explorer than how can i do the same.
Please suggest me the same ASAP. i am still waiting for your reply..
Warm Regards,
Krishna Chaudhary
Please tells me about project deletion from explorer window in the ssrs
What does the intersect in sql 2008. Please suggest…
It is used to get common rows from the two or more tables
(select 1 union select 2)
intersect
(select 1 union select 4)
hi Krishna,
Intersect gives you the common records from both tables on left and right…..
for eg..
select ContactId from tblContacts
Intersect
select ContactId from tblPayments
will give you ContactId ‘s which are both in tblContacts and tblPayments
there are two conditions using Intersect:
1)The number and the order of the columns must be the same in all queries
2)The data types must be compatible
Code Well….
Thanks
Sumit Thapar
Thanks you So much Mr. Sumit
Hi,
I want to use the pivot function in sql M Pasting raw data and
report which i need from the raw data
raw Data
AGENT_ID DISPO DISPO_COUNT
5464 – Sukhpal, Sukhpal CBL 20
5464- Sukhpal, Sukhpal CBL1 4
654158 – kiran, kiran CBL 24
654158 – kiran, kiran CBL1 4
54464 – singh, Ovend CBL 9
54464 – singh, Ovend CBL1 3
89554 – Chander, Naresh CBL 4
Need below Report From Above Raw Data
AGENT_ID CBL CBL1 Grand_Total
5464- Sukhpal, Sukhpal 20 4 24
654158 – kiran, kiran 24 4 28
54464 – singh, Ovend 9 3 12
89554 – Chander, Naresh 4 4
Please write the query use with pivot for the same
it’s Very urgent..
Warm Regards,
Krishna Chaudhary
Follow this post. It has the code and examples on how to use PIVOT in SQL Server 2005 and above
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Thank you so much Madhivanan….
SELECT Screenname
FROM inthemo_fTS.dbo.table
inner join
FREETEXTTABLE(INTHEMO_FTS.dbo.Table,Screenname),’bars’) as ftt on ftt.[Key]=Table.rowno
For this query not getting results for Bar only getting to bars .
I have two database in one data base its working fine i am getting for both bar and bars treating as same
But in another database its looking like separate.
Can u give us quick reply pls i was stuck in production system …….
Hi ,
How can i sum the column’s value if some columns are null in Sql 2008
as
columnA columnB columnC columnD
5 null 6 null
null 7 null 8
8 null 5 7
how can i sum all columns if i have 50 lak records and any column can be null
I need
Select columnA+ColumnB+ColumnC+ColumnD from test_table
if i am writting
select Emp_name, (ifnull(columnA,0)+Ifnull(columnB,0)+ifnull(columnC,0)+ifnull(columnD,0)) as SumOfRecords from Test_table
the same query not executing.
Please write the right query for the same requiremant fro sql 2008.
Use isnull instead of ifnull
I require a function such that I will be passing 2 dates i.e. StartDateTime and EndDateTime.
Office working hours are from 9:30 AM to 1:30 PM and 2:30 PM to 6:30PM
1:30PM to 2:30PM is lunch time.
So when I pass the StartDate and EndDate the function should return the time taken in hrs
excluding the lunch time i.e. 1:30 to 2:30, excluding holidays and excluding sundays.
For Example: if I give,
1) StartDateTime = 2010-07-22 13:30:00
EndDateTime = 2010-07-22 14:30:00
Then function should return 0 (since it is lunch time).
2) StartDateTime = 2010-07-24 18:25:00
EndDateTime = 2010-07-26 09:35:00
Then the output should be 10 minutes since 25 is holiday and after 6:30 it is not counted.
Hi dear hope you ill be fine please help me to solve my problem.
Dear i want to create a distributed database in SQL server 2005 is such a way that i have five departments and each department has its own type of Data have same fields. I want to add .ndf files to my database for each department and thus each department can directly insert there data to their particular file have each file on different computers over then network.
is there any possibility to do so .
Please help and send me some step by step configuration or settings
Regards,
Naveed Naeem
hi Pinal,
I’m using STUFF() function combined with FOR XML PATH(), to obtain a comma-separated list of value. Here’s an example:
SELECT STUFF(
(SELECT ‘,’ + MYTABLE.field
FROM MYTABLE
ORDER BY MYTABLE.field
FOR XML PATH(”)),1,1,”) AS LIST
that return value1,value2,value3……
OK? well, now I want to create a generic function in which i can specify a table and a field to apply that function against, but to do so I guess I need to build a sql-string, put it in a varchar variable, and execute it somehow.
Which is the best way?
thanks, Balanza
Try this code
declare @table varchar(100), @column varchar(100), @sql varchar(max)
select @table=’table name’, @column=’column name’
set @sql=’SELECT STUFF(
(SELECT ‘,” + @column+’
FROM ‘+@table+’
ORDER BY ‘+@column+’
FOR XML PATH(””)),1,1,””) AS LIST’
exec(@sql)
Hi Pinal,
I am very new to SQL SERVER. I faced 1 question in interview, ie suppose in server has 10 databases and sysadmin wants to give permission to sql login for 1 specific database, but remaining databases should not appear in that server when that particular sql login connect to the same instance..
Please help me how to give permission.
Thanks
Sri Chitti Durga Rao Kona
if tempdb is full,what will you do?
1. Take the log back with truncate_only
2. Shrink the tempdb
3. better to maintain tempdb on different drive
Hi Pinal,
i have one issue regarding create a dynamic database using stored procedure.
i paste my stored procedure and problem below:
i had created one script file “Xyz.sql’ and it’s path is “c:\scripts\xyz.sql”. this file have a create tables and create stored procedures implementation
i had created one stored procedure “ConfigureDatabase”
////////////////////////////////////////////////
CREATE PROCEDURE [dbo].[ConfigureDatabase](
@DatabaseName VARCHAR(100)
)
AS
BEGIN
DECLARE @SqlCreate VARCHAR(MAX)
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DatabaseName)
BEGIN
SET @SqlCreate = ‘CREATE DATABASE ‘+ @DatabaseName +’ ON PRIMARY
( NAME = N”’+ @DatabaseName +”’, FILENAME = N”E:\TEST DATA\’+ @DatabaseName +’.mdf” , SIZE = 9675456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N”’+ @DatabaseName +’_log”, FILENAME = N”E:\TEST DATA\’+ @DatabaseName +’_log.ldf” , SIZE = 52416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)’
EXEC(@SqlCreate)
END
EXEC (‘USE ‘ + @DatabaseName)
EXEC master.dbo.xp_cmdshell ‘osql -E -S PCT26 -i “c:\scripts\xyz.sql”‘
END
///////////////////////////////////////////////////////////////
when i execute [ConfigureDatabase] stored procedure
like EXEC [ConfigureDatabase] ‘test’
at that time new ‘test’ database is create and i try to set current database using Inline USE syntax, becuase ‘Use’ keyword doesn’t support in stored procedure.
but all tables and stored procedure define in xyz.sql file are created in ‘master’ database instead of ‘test’ database
i want to create all stored procedure and table in ‘test’ database
Please help me.
Thanks,
Sujal Ramani
@Sujal.
OSQL accepts database as parameter, asking in which database you want to run the query.
Take off, Use Database Dynamic SQL in your script and use below script.
Declare @Sqlcmd varchar(1000)
Set @Sqlcmd = ”’osql -E -d ‘+@DatabaseName+’ -S PCT26 -i “c:\scripts\xyz.sql””’
print @Sqlcmd
EXEC (‘master.dbo.xp_cmdshell’+@Sqlcmd)
In above script we are passing database name as parameter, so this script is executed in that specific database.
Let us know if this does not solves your problem.
~ Peace.
Hi everyone,
I am working on Sql Server Reporting Services 2008…i have a report that shows records more than 3 lakh…..now when i try to export the report , it throws an error as a worksheet in excel can only take not more than 65k records…i ve heard that if i use page breaks after specific rows a new worksheet will be created after that specified number of rows….Please anybody help me how to add page breaks after specific number of rows……am desperately waiting…
Thanks
Sumit Thapar
I would like to ask one questing about SQL index. Is it any side effect on creating a lot of index on one table and how to improve the sever performance to handle over 10 millions of records?
Thanks so much :)
Creating lot of indeices is not a good idea. You should rightly identify the key columns and created index
The maximum is 5, all are non-clustered indexes. Is that will be OK?
Thanks so much, Madhivanan!!! :)
Hi Pinal,
I’m working on a database that uses Table and Index Partition. I have made a fresh new copy of the original database schema in a development server: this time the new database will use partitions. My goal now is to restore (copy the contents of) the original database to this new database. The issue is that the original database did not use different partitions only PRIMARY partition. When I ran the restore utility in SQL Server Management Studio I get the error “The backup set holds a backup of a database other than the existing ABCD”. However, the two databases are identical. I am trying to use the BK file that I made of the original database to copy the contents of my original database to the new one database because I want to run queries and determine any performance improvement between using partitions and not using partitions. What’s the best way to get the data from the old database to the new one? This is a large database of 1 TB… I’m relatively new to DBA (actually not really a DBA, but doing some DBA work for sure these days)… Any suggestions?
I do not know how to connect a user to a database to manually enter data. I have configured the server for remote connections. Is there a string to enter into the browser to connect the user to the database to allow manual data entry?
Allowing users to enter data manually is not good idea. Have an application that takes inputs from the users and insert to the table
Hi dear hope you ill be fine please help me to solve my problem.
Dear i want to create a distributed database in SQL server 2005 is such a way that i have five departments and each department has its own type of Data have same fields. I want to add .ndf files to my database for each department and thus each department can directly insert there data to their particular file have each file on different computers over then network.
is there any possibility to do so .
Please help and send me some step by step configuration or settings
Regards,
Naveed Naeem
Hi Pinal sir,
i have a confusion regarding a query for nth highest salary which you wrote in your following blog:
http://blog.sqlauthority.com/2007/04/27/sql-server-query-to-retrieve-the-nth-maximum-value/
the query is
SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
my confusion is how does the query match the (N-1) value
if i try “SELECT * FROM TBLSALARY WHERE (2)=(2)” , it shows me all the records of the table
Thanks
Sumit Thapar
You can’t compare like that. You need to count how many salaries for there that is greater than the present salary
Thank you sir…..got it
Hi,
I want to delete the Project Name from the Explorer. if i built the any report by the SSRS in visual studio then the same reflect on explorer if i want to delete the some reports from the explorer than how can i do the same.
Please suggest me the same ASAP. i am still waiting for your reply..
Warm Regards,
Krishna Chaudhary
Hi,
I am using SQL server 2008 for my java application, and port is 1433, static port. when ever it goes for database connection, i think it is generating a session Id (49244,49266… like this) but my antivirus is TrendMicro and it is blocking my application not to connect with the server machine. So is there any possibility to fix the sessionId, so i can give that particular port or within a range in TrendMicro. Please give a solution for my problem.
with regards,
bhanu
Pinal
I have a job that sends email alerts, up to recently the text email has been sufficient. However the client now wants rich html with an image. I have tried a few methods but so far have drawn a blank. I’m using sql2005.
DECLARE @HTML VARCHAR(MAX);
SET @HTML = N” +
N’ ‘ +
N”;
EXEC msdb.dbo.sp_send_dbmail @profile_name = N’TestProfile’,
@recipients = N’martin.mclarnon@firemelon.com’,
@subject = N’Subject’,
@body = @HTML,
@body_format = N’HTML’,
@file_attachments = UNC Path to image on SQL Server;
This method added the image as an attachment but does not embed it. Any help would be appreciated.
Regards
Martin McLarnon
Hi,
I want to separate by space a firstname middlename lastname from a single column to separate columns in sql 2008
as
Question —-
CUstName
krishna Kumar Chaudhary
Need result ——–
Firstname middlename lastname
krishna kumar chaudhary
its very urgent…..
Warm Regards,
Krishna Chaudhary
If your name always has three parts
select parsename(name,3),parsename(name,2),parsename(name,1) from
(
select replace(name,’ ‘,’.') as name from table
) as t
Thanks Mr. Madhivanan
Please tell me one think more
if i don’t know how many space in a name
as
name
krishna kumar chaudhary aaaa bbbb cccc
vineet kumar chaudhary aaaa bbbb cccc ddddd eeee
vikas kumar chaudhary aaaa bbbb ccc dddd eeee pp
sohan kaur
should be create column as per maximum space count
Do you want to create columns based on each space?
Post your expected result
One question related to your post …I have question table and answer table with quesid as foriegn key in answer table. there is another similar table structure and I want to insert from one table to another table i am using following query for it…
–DECLARE @QuestionID AS BIGINT
–INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionTextActive)
–SELECT AreaID,UserID,QuestionType,QuestionText,Active
–FROM ContributedQuestions
–WHERE CQuestionID in (18,19,20)
–SET @QuestionID = SCOPE_IDENTITY()
–INSERT INTO QBAnswers ( Answer,QuestionID,IsCorrect)
–SELECT Answer,@QuestionID,IsCorrect FROM ContributedAnswers
–WHERE CQuestionID in (18,19,20)
Now to insert mulitple row at one time I used query like this
DECLARE @lclMySQL as varchar(MAX)
SET @lclMySQL = ‘INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionText,Active)’
SET @lclMySQL = @lclMySQL +’SELECT AreaID,UserID,QuestionType,QuestionText,Active FROM ContributedQuestions’
SET @lclMySQL = @lclMySQL + ‘SET IsActive =0 WHERE CQuestionID IN (‘+@QuestionID+’)
‘ where I am using @question id as string of quesiton id but in this case how can I insert question id as foriegn key to Answer table???
Is there some way in which i can use IN keyword in where condition to insert multiple rows and also insert forign key value in to answer table?????
Hi Pinal
I do have one question regarding to Index.
Can you please let me know how do i know the creation and update date of perticular index?
I have tried lots of blog, but didn’t get the accurate answer.
I hope to hear soon from you.
Thanks a lot in advance
Hi Pinalkumar,
Currently I got an issue to return results of stored procedure cross databases:
Database A – stored procedure B, account ”UserA” has db_datareader and db_datawriter permisions.
Database C – table D, role “execRole” has “grant “ and “with Grant” permissions. (I think account“UserA’ belongs to role “execRole”, otherwise I cannot login and create proceudre, etc)
Because store procedure B returns lot of records, we plan to save them into table D of database C. Under db_owner’s account, the stored procedure can be executed successfully.
But under an account “UserA”, the stored procedure got an error:
Msg 1088, Level 16, State 7, Procedure procGetWorkHours, Line 38
Cannot find the object “B” because it does not exist or you do not have permissions.
Thanks in advance!
Helen
Hi Pinal,
Forgot to mention that in the stored procedure, there is logic like this:
– @LockName = ‘DefaultLock’
SET NOCOUNT ON;
BEGIN TRAN @LockName
TRUNCATE TABLE C.dbo.D
INSERT C.dbo.D
select …….
from …..
COMMIT TRAN @LockName;
Regards,
Helen
if your issue yet not resolved then :-
as per the error
Cannot find the object “B” because it does not exist or you do not have permissions.
if you can provide the code, it can be digged further
Sir
i take database backup in sql server 2005 in windows 7..
i need to restore the backup file into sql server 2005 in windows xp sp2.
plz replay
Have you tried restore command?
Restore database your_db from disk=’backup path’
How put a primary key on a view in SQL Server 2005
Why do you need this?
Hi friends
Today I was working on a query, and I got stuck at a place.
We have this payroll system, where every month we get the “straight time” for the entire month, but the “over time” is paid a part in this month and the other part in the next month.
I will take the month of Jul as an example.
My Jul salary will have the “straight time” for Jul 01 to Jul 31. But my overtime is from Jun 21 to Jul 18th.
Now, I need a report where I need to tell the weekly pay that I will get. The calculation of the month for “Straight time is direct, but for Week ending 25th Jul, I do not get an overtime till next month, and The next month for the same period i get only an overtime for 25th jul.
The starting and ending week is saved in a table pay_cutoff
(payMonth datetime, startweek datetime, endweek datetime)
2010-06-01, 2010-05-23, 2010-06-20
2010-07-01, 2010-06-27, 2010-07-18
The weekly transactions are stored pay_weeklytran
(emp_id int, week datetime, ot_hours, ot_rate)
now my requirement is that I assign the correct “paymonth” for each record in “pay_weeklytran”
the query I used finally was,
select a.emp_id, (select b.paymonth from pay_cutoff as b where a.week between b.startweek and b.endweek) as paymonth, a.ot_hours * a.ot_rate as ot_earned
from pay_weeklytran
Please tell me if there is a better way to do it, I feel that this query is a bit messy.
thanks in advance
restoration not depends on OS. if you able to install the sql server on OS then you will be able to store it.
why do you want PK on views…… views are just virtual table so refer the columns from table which have PK and use them in your where condition to fetch data fast in view
Dear Sir,
I have one sql 2000 DATABASE by name ‘ACCOUNTS’ which is working smoothly. But at the time of restoring the backup the the database on sql 2005 it shows error saying the “possible schema corruption run ‘DBCC CHECKCATALOG” for specific erros. During restoration process the it shows restored upto 100%, but at the end the end of the procell the above error is getting displayed.
Secondly, i ran DBCC CHECKCATALOG it gives out the following results which i am not able sort out. Please help……
Server: Msg 2513, Level 16, State 5, Line 1
Table error: Object ID 1233190868 (object ‘DF__D0520081__Recove__4980FFD4′) does not match between ‘SYSOBJECTS’ and ‘SYSCOMMENTS’.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 1235286885 (object ’1235286885′) does not match between ‘SYSCOMMENTS’ and ‘SYSOBJECTS’.
DBCC results for ‘current database’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Regards,
Akash R. Kambli,
System Administrator,
Panaji Head Post Offce,
Goa 403001
Here is my scenario.I have 10 different tables and I want to create a Search for all of them.Like In drop down list the column name would be displayed and if I will give a value of that particular column then It will search a particular row for that table.How can i Create storedprocedure for that?
Thanks.
Refer this post. This is exactly what you are looking for
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
Hi,
I have pass last weekend 70-432 (sql 2008 ) exam. Prometric gave me paper with total score and it says if this is your first microsoft exam you will recd. email from microsoft regarding your MCP id and information how to recd. welcome package. Since this is my first exam with microsoft what do i need to do next?
can some one please suggest.
Thank you
Tejal
Hi,
I have a question for you. Do you know if there is a way to retrieve a single file or a filegroup from a database backup without attaching it to database. I would like to obtain those files without creating a new database or restore them to the original database.
Thanks.
I have two different database server and one one databases on each server.How do I write trigger that fired from one database server and update to antoher database server.Thanks.
You can make use of three part names.
Dbname.username.tablename
It couldn’t work.My issue is there are 2 different machines.And each database is on different machine.So do I need to use linked server or something else?
Hi,
What is exact difference between DTS and SSIS in sqlserver?
Thanks
Koteswar rao
sir , i want to know about the following query please explain me .
ALTER TABLE dbo.CompCol ADD
FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)
this query is used by you in the computed and persisted column blog
thankyou
sir , i want to know about the following query please explain me .
ALTER TABLE dbo.CompCol ADD
FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)
this query is used by you in the computed and persisted column blog
thankyou
Hello,
I had modified an SP on my database and then rolled back it after my testing. Now, I do not have any copy of the changes I made. Is it possible to retrive those changes from the database \ logs etc, and get the changed SP?
Thanks
Gopal
Hello sir,
I m first time reading ur sites its g8 but i m new in sql so pls describe backup and restore process in 2005….
Read about Bacup and Restore commands in SQL Server help file
Are you Shilpi Sharma,a Student of IIHT? You can just right click on your database name tasks and backup then select the location that where you want to store your backup file.That’s it.
to Gopal,
restore from your backupset to some newdatabase name and then copy the old sp from there to your db
Hi,
i have mstsc.exe version 6.1, installed in my system. till few days back copy/paste function was working perfectly. Now, even though all the settings in “Local resources” are activated, but still i’m unable to copy to/from remote desktop to my local. I also tried killing the process rdpclip.exe and again starting the same, but that also failed. Please suggest me some other options.
Hi pinal,
can u plz help me on following issue
The database cannot be opened because it is version 655
How did you get this error? Did you mean version 6.5?
The database cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
Could not open new database. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
how to maintain transaction in two database in sqlserver?
Ok here is the scenerio:-
you have to database in sqlserver named DataA and DataB
now if you have inserted a record in DataA (Accounts table) it should also insert a record in DataB (Payments table).
But if anything goes wrong in one of the Database then you should rollback both.
How can you achieve that in sqlserver.
You have to maintain transaction across both the tables….
-Anil
How to Protect/Encrypt data in sqlserver?
is the rdp you doing on server exist in same environment from where you copying.
Sometime this problem comes even though the environment is same. Try cleaning the temp file in serer by doing start-run-%temp% and delete all temp files there.
it should resolve the issue. if not then try rebooting if possible and left no alternate
how do I unsubscribe to this website. I am recieving a lot of emails and it is causing problems
Goto wordpress.com and unsubscribe from the post
Hi there,
I just wanted to thank you for your time and effort on this site. I often get directed to your blog by google when I’m searching for stuff, and your information and advice is ALWAYS spot on.
Thanks SOOO much for all your hard work – you make life easier for the rest of us.
Cheers,
Scott.
Hi Guys,
I am new to SQL and my requirement is simple:
“If X-Date + 90 days < Current Date", then do something.
Now I am getting the 'X-Date' value from a xml blob using a X-query. Its in the format – MM/DD/YYYY
How do I write the code for this
Use
SET DATEFORMAT MDY
before the query
how to maintain transaction in two database in sqlserver?
Ok here is the scenerio:-
you have two database in sqlserver named DataA and DataB
now if you have inserted a record in DataA (Accounts table) it should also insert a record in DataB (Payments table).
But if anything goes wrong in one of the Database then you should rollback both.
How can you achieve that in sqlserver.
You have to maintain transaction across both the tables….
-Anil
How to Protect/Encrypt data in sqlserver ?
Hi Pinal,
Can U help me out know about magic tables. and how they work? I mean how they execute. there Architecture .. and can we manipulate them by our code?
Thanks
Sarika
I hope you are refering inserted and deleted tables
They take struture of the underlying tables in which a trigger is created
Hi Pinal,
In one of my project, there needs to run a cleanup job which will deletes the records in a set of selected tables according to a retention period defined in a configuration table. During the deletion, the log file is getting loaded while we execute the job as the delete command is adding the delete log into the .ldf file. Please advice an alternative to remove the logging of the delete command in the ldf file.
Thanks in advance.
Pradeep Kumar K R
what is the most common used stored procedures in any database?
thanks.
Why do you want to know this?
Runnng a profiler may give you some ideas
Dear Pinal,
Would you please recommend/suggest any Link, PDF or Book for a Database Architect related? From Basic to Advanced any level of book is also ok.
Shaiju CK
Hello sir
How can I know the statistics of the database are up to date or not?
Hi,
I am a commerce graduate but i have a gr8 interest to do the course of DBA or SQL so can you plz give me the advice which course is better for me for my future.
Thanks,
Gagan
Dear Pinal,
I am Developer working in small compnay.I have one Query i can i display Month name and Number of days in a month .i need like this
Jan-31
Feb-28…..
….
…..
so on
Can you help me
thanks
Bharath
select left(datename(month,getdate()),3)+’-'+cast(day(getdate()) as char(2))
Hi,
I have an query in sql update:
I am having a table which will maintain last one year data ( monthwise)in a single row. The table has 13 columns
1. Key then 2 -13 monthly names ( Jan…Dec)
How do I update particular month data using single query ( in Jan, we need to update Jan data, in Feb, we need to update feb data only …..) dynamically?
Regards
Ak.
You may need to post some sample data with expected result
Can anyone suggest the best way to achieve this…
I have a string, I need to extract words, its line positions and its word positions. Also I need to eliminate ignore words from it. See example below…
String: “SQLAuthority.com is trademark of Pinal Dave”
I need output like
Word————————WordPos————–LinePos
SQLAuthority.com 0 0
trademark 1 21
Pinal 2 34
Dave 3 40
When calculation word position we should no consider ignore words (is, of….)…
When calculating Line position we should consider everything calculate position of the first letter of the word
Hi,
I am a Hugh fan!
Is it possible to copy a directory from one directory to the other using xp_cmdshell?
I know copying files works great but how do copy entire directories?
I thank you in advance!
You can copy folders in the same way you copy the files. The syntax is same
Hi Pinal
I have written a dll for executing SQL queries which come from my business logic class. I have put unique key constrains on my table.
I want know how to capture the error id of the Unique Key error.
Hello pinal,
don’t know but think, I know you from previous work.
Have you worked / studied in nirma university / L&T ?
kaumil
Hello Pinal,
I am very impressed with your site and wonderful helpfulness.
I wonder if you can help me?
I am getting 2 errors from my SQL server:
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed.
The source is MSSQLSERVER and category as Logon with event id 17806
AND….
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: ]
These errors are stopping access to the application using the SQL database held on this SQL server.
I have searched everywhere and cannot find a solution.
Please, please can you help?
How did you get these error?
Are you connecting SQL Server via application?
These errors are in the event logs of the SQL server
Halo Pinal,
I always refer your blog for notes and doubts. Very informative and helpful. I am facing an issue with SSIS Package Configuration. I am confident that you would be definitely able to help me out.
I have a Development Server with a Development Database. I have a Source_Table in which I have three columns (Server_Name, Database_Name and Table_Name). In future, we may move few tables to a new server due to space issues. The Table_Name Values will remain the same, but when moved to a new server and database, this table will be updated. So, The Server_Name and Database_Name values could probably be changed when few tables are moved.
I have SSIS packages configured on this server. I am trying to configure these packages to make them dynamic to pick the values of Server_Name and Database_Name from this Source_Table.
I can configure two variables, for Server and Database, and store the package configuration in XML file and manually change the values, whenever there is a change in the Server or Database values in the Source_Table. But, my team says, they don’t want to touch any of the packages or config files, they want these to be pulled from a table. So the package configuration through XML configuration is rules out in my case.
I am using the package configuration through SQL Server. But, when I change values in the SSIS Configurations table, the package doesn’t pick values from table; instead, it pulls value from the variable declared in the package. The variable declared has the value of Server and Database stored in it.
How can I configure my package in such a way that the package picks the Server_Name and Database_Name for a particular Table_Name values from the Source_Table?
Hello pinal
i got problem which i dont understand
i alreaedy have sql express 2008. and my application work ok. after i upgrade to sql express r2 thru update then my application not work. the error is connection error and i check previous sql use .\sqlexpress to connect to database
how can i fix this
hope can help me
thank you
Checkout coneectio strings for this
http://www.connectionstrings.com
Hello Pinal,
i have 1 table & field like(questionsno,A,B,C,D) . From this table i want to get questionsno,[which field have values- field name)
ex: 1- A,B(1,1,0,0)
Is it possible to get result in above?
Post table structure, sample data with expected result
Hello Pinal,
I am working on Sql server 2008 almost from year and i used to read Your blogs. Basicaly My whole work depend on sql when ever i found difficulties in generating queries or in procedure i search in your site and most of the time i found correct answer .
Pinal i want ask u about certification in Sql server 2008 .
How can i get certified in sql please tell me.
Hi Pinal,
I’m a regular reader of your blogs and learned alot from this site. Please accept my sincere thanks for your wonderful posts all the time.
I’ve a question, How do we create any user who will have SQL Error Logs view permission in SSMS.
I tried using granting execute permission to xp_readerrorlog but didnt it work. Could you please help to understand if I’m missing anythning.
Thanks again.
Regards,
Kanchan
i have my database with the columns as follows:
keyword, part1_d1, part1_d2 …….. part1_d25, part2_d26, ……part2_d34
FYI: d1 through d34 are documents..
how can I give a query to obtain columns with column_name like ‘%part1%’; as below
keyword, part1_d1, part1_d2, …….. part1_d25
I tried the query:
select (Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS where COLumn_NAME like ‘%part1%’) , keyword from sample
But it dint work…
Please let me know what to do?
That is not possible until you use dynamic SQL. Why do you want to do this?
script for stored Procedure to check availability in sql server 2005
script for stored Procedure to check availability of login id in login form in sql server 2005
look for sys.syslogins view in SQL Server 2005
Hi Pinal,
Is it possible to get the day like “Sunday”, “Monday”,”Tuesday”,”Wednesday”,”Thursday”,Friday” and “Saturday” by using Datetime column in the table ?
My requirement is to get the count per day wise ,In the table i have count and datetime field. need to convert datetime field to “day” (Sunday etc…)
can you send me is it possible using sql server 2000
Regards
velu
select datename(weekday,getdate())
Hi i have a search procedure which will search from a table of patients containing around 10 laks of records and and around 20 fields. i need to query the table with around 10 fields which may or may not be included in the search criterion based on the user selection. i have two options in front of us. writing a dynamic sql query is the first or the second one is which i have given below. Can you guide me in finalizing a best solution.
DECLARE @Name as Varchar(100)
DECLARE @Title as int
DECLARE @dob as date
DECLARE @PageIndex as int
DECLARE @PageSize as int
DECLARE @MAXRECORDTOSELECT AS INT
set @Name = null
set @Title = null
set @PageIndex = 0
set @PageSize = 20
SET @MAXRECORDTOSELECT = @PageIndex * @PageSize + @PageSize
IF ( @MAXRECORDTOSELECT = 0 )
BEGIN
SELECT @MAXRECORDTOSELECT = COUNT(*) FROM PAT_PATIENTMASTER_SVIEW
END
;WITH CTE_PATIENTMASTER AS
(
SELECT TOP(@MAXRECORDTOSELECT)
PM_ID_PK, PM_ExternalID, PM_MRN, FullName, PM_AKA, PM_DOB, PM_Sex_FK, PM_SSN, PM_Title_FK,
PM_PreferredContactMethod, PM_Confidentiality_FK, PM_ExemptFromReport,
PM_Active, PM_PatientType, PM_BloodGroup, PM_PreferredDisplyName, PMEx_Note, Age,
ROW_NUMBER( ) OVER ( ORDER BY FullName DESC)
AS ROWNUMBER FROM PAT_PATIENTMASTER_SVIEW
WHERE PM_LastName Like case when @Name is null then PM_LastName else @Name end
AND PM_Title_FK is null OR PM_Title_FK = CASE WHEN @Title IS NOT NULL then @Title else PM_Title_FK end
)
SELECT PM_ID_PK, PM_ExternalID, PM_MRN, FullName, PM_AKA, PM_DOB, PM_SSN, PM_Title_FK,
PM_PreferredContactMethod, PM_Confidentiality_FK, PM_ExemptFromReport,
PM_Active, PM_PatientType, PM_BloodGroup, PM_PreferredDisplyName, PMEx_Note, Age, SL_SexCode AS ____
FROM CTE_PATIENTMASTER
LEFT OUTER JOIN GEN_Sex_Lookup ON PM_Sex_FK = SL_ID_PK
WHERE ROWNUMBER > (@PageIndex * @PageSize) AND ROWNUMBER <= ((@PageIndex + 1) * @PageSize) ORDER BY ROWNUMBER DESC
I want to move records from online database to local server. Please suggest me how to do it.
I’m taking backup but it remains in that server means unable to move from bak file to local server
If it is in server’s directory, copy the backup file to local system and run restore command
You can try copying data using import / export wizard.
Before you do import, disable all FK constraints on local DB so that you won’t get any constraint violation errors.
Take care about IDENTITY columns….
Hi Pinal,
I am new at learning SQL server 2008.And i wish to master the DBA concepts completely
I request you to please suggest some Good Books to learn the basic concepts speically administration stufff
Thanks a lot in advance
Hitesh
Dear Pinal
I have installed MS SQL Server 2008 R2 Express with Advanced Services in my machine. During installation i used default settings. The report sever database credentials are as thus;
SQL Server Name: TLOX-PC
Database Name: ReportServer Report Server Mode: Native Credential: Service Account Login: NT Authority\NetworkService Password: ********* When i try to run the report manager URL over the browser, i receive the ‘authentication required’ window where im prompted to enter Username and password. Since im using the service account and the authentication mode is set to windows, im assuming that i should straight away see the report manager on the browser. I did not set the above password and i dont know what these stars(“*”) represent. Is there a way i could change this password or at least see it? Thank you.
Hi there,
I am having the same problem. Can you please let me know how to solve this issue if you have one.
Thanks,
Ranj
Dear Pinal,
This is Sajid Kamal Sr. Software engineer, we are working in Healthcare ERP.
I regularly follow your blogs.
This time we are facing very serious problem in SQL Server 2005.
When user(s) saved a bill and get printed, some times data lost from transaction tables.
All the saving is done through procedure under single transaction.
Is it possible that after committing the data, data get removed from the table?
I need your help.
Thanks & Regards
Sajid Kamal
Sr. Software Engineer
Akhil Systems Pvt. Ltd
New Delhi
Dear Pinal,
I need ur help. my question is that how do make database for sent emails for every user using SMTP server, if we are using outlook exp?
Hellp,
I find this site extremely helpful, but I can’t seem to find anything on how to make a sqlagent job fail from a stored proc.
Briefly, a sql agent job calls a stored proc. If a particular set of circumstances occurs the stored proc. emails me and I would like it to cause the job to fail.
Any ideas would be helpful.
Thanks.
Susan
HI Pinal,
How can we use “EXEC” or “sp_executesql” in User Define Function ?
It is not possible to use dynamic sql in a function
Hi pinal,
I badly need your help, I’ve a weird situation.
I’ve a stuck query, normally it runs with in seconds, but on a bad day it stuck forever, everytime it’s stuck, it’s stuck for hours and hours, it’ll never completes.
Activity monitor showing it as green, runnable, there’s no locking or blockings from other users either. Index usage is close to threshold.
From server side, no disk activity, no I/O, which means nothing is happening. That’s the problem being said, here’s is the solution we are doing as of now..
Kill that query, update the stats for all the tables involved in that query.. that’s it, when you run the same query, it’ll run in seconds. So, “update stats” is the key to unlock this stuck query situation, we are doing it everytime. It’s still a puzzle, we’ve no idea how this “update stats” is solving this. We do update stats everyday morning 5 am, as a scheduled job with default sample rate. Still, during the middle of the day, we’ve this stuck query.
Hi Pinal,
Here is my problem.
I have a string for e.g: “12/09/2010 XYZxyz”.
I want to check from a program if the string has alphabets from caps A to Z or small a to z. If yes I need to run something.
How do I write a query for this
Thanks,
Bunty
Hi You can try PATINDEX..
If PATINDEX(‘%[A-Za-z]%’,’12/09/2010 XYZxyz’) > 0
print(‘found’)
else
print(‘not found’)
Hi Pinal
i need find certain transactions in the table two that have been writted more that 2 times.
See my code below.
DECLARE @my_GID UNIQUEIDENTIFIER
DECLARE my_cursor CURSOR FOR
SELECT GID FROM tbt_tableOne WHERE GID IN (SELECT tbl1GID FROM tbt_tableTwo);
OPEN my_cursor;
FETCH NEXT FROM my_cursor
INTO @my_GID;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT [ID],tbl1GID FROM tbt_tableTwo where tbl1GID = @my_GID
–i basically want to identify all entries in table2 that have more than 2 of the same GID from table1
–should i make use a nested cursor?
FETCH NEXT FROM my_cursor
INTO @my_GID;
END
CLOSE my_cursor;
DEALLOCATE my_cursor;
I want to start a blog but do not know how to start it. Can you please let me know the process and steps to do it. Your help will be much appreciated. Also I enjoy you blog and have used on several occasions to find solutions.
Thank you
Hi
I have to apologise in advance for my lack of knowledge about this subject.
My problem is I have been running smse 2005 for about 3 years with no problems until last Sunday when the error message below appeared. I have read through all the previous blogs and tried all the suggestions with no luck.
I am running the system on a local machine to run queries on a backup of the database. using the following
Server Type Database Engine
Server name computername/sqlexpress
Authentication Windows Authentication
user name Computer name/my user name
SQL Server Browser is running
Built in account is Local System
Host is computername
Shared memory is enabled
The only difference I can see from previous quesions id that I have Microsoft sql server , error:2 at the end
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared memory Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, error:2)
you can register at https://www.blogger.com/start and get a new blog
when i am try to install msde 2005 express on windows xp sp2 there is following error showing that
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see “How to View Setup Log Files” in SQL Server Books Online.
can u help me please
Hi Pinal,
SSRS – Where wil the deployed .rdl files save?
HI Pinal,
how to get only unmatched records from tables using full outer join?
Regards,
Naresh
Hi Naresh,
You can find unmatched records by Full Outer Join by checking NULL conditions as follows:
DECLARE @TableA TABLE(ID INT, Value VARCHAR(1))
DECLARE @TableB TABLE(ID INT, Value VARCHAR(1))
INSERT INTO @TableA VALUES(1, ‘A’)
INSERT INTO @TableA VALUES(2, ‘B’)
INSERT INTO @TableA VALUES(4, ‘D’)
INSERT INTO @TableA VALUES(5, ‘E’)
INSERT INTO @TableA VALUES(6, ‘F’)
INSERT INTO @TableB VALUES(1, ‘A’)
INSERT INTO @TableB VALUES(2, ‘B’)
INSERT INTO @TableB VALUES(3, ‘C’)
INSERT INTO @TableB VALUES(4, ‘D’)
SELECT a.ID,
a.Value,
b.ID,
b.Value
FROM @TableA A
FULL OUTER JOIN @TableB B ON a.ID = b.ID
WHERE (a.ID IS NULL OR b.ID IS NULL)
Thanks,
Tejas
SQLYoga.com
Hi Pinal,
When I am trying to access Report Manager (http://localhost/Reports), I am getting Access Denied error as follows:
—————————————————————————
Server Error in ‘/Reports’ Application.
Access is denied.
Description: An error occurred while accessing the resources required to serve this request. You might not have permission to view the requested resources.
Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists). Ask the Web server’s administrator to give you access to ‘C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\home.aspx’.
Version Information: Microsoft .NET Framework Version:; ASP.NET Version:2.0.50727.3614
—————————————————————————
The Report Server Service Account is set to “Use built-in account: Network Service” but it still asking for the user credentials. I’ve searched in lot of websites but couldn’t find an answer to fix the issue. Your help would be much appreciated.
Regards,
Ranj
Hi Ranj,
Which Operating system you are using?
If you are using Vista, then run “IExplore” with Administrator rights.
Right click in “Iexplore” and select “Run as Admisnistrator” and try that.
Let me know if it helps you.
Thanks,
Tejas
SQLYoga.com
Hi!
I need your regarding SQL Job.
I have created one Console application with .NET to create one .dat file, then I need to create this file daily. so for that I have created one Job for this But it can not run successfully and give me error : the System can not find the path specified.
Even if i execute .exe manually it works fine.
Can u help me out for this?
Thanks a lot in advance!
Note that the path you specify must be on Server’s directory and not in your local system
Hi..
I am facing a strange situation. when i executing the following query it is returning the error
SELECT s.row_id as shipment,
SUM(CAST(la5.attr_value AS INT)) AS Cases
FROM
shipment s WITH(NOLOCK)
INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id
INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id
INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND
s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local
INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = ‘CaseQty’ AND attr_grp = 3)
WHERE s.spare2 = ‘RELEASED’
AND shift.shift_desc = ‘c’
AND DAY(ss.production_date) = DAY(’2010-08-09 00:00:00.000′)
AND MONTH(ss.production_date) = MONTH(’2010-08-09 00:00:00.000′)
AND YEAR(ss.production_date) = YEAR(’2010-08-09 00:00:00.000′)
GROUP BY s.row_id
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ‘N’ to data type int.
The column type of attr_value of lot_attr table is a USER DEFINED DATATYPE (Based on nvarchar datatype). containing both numeric type and non numeric type of data.
But when i executing the following query
SELECT s.row_id as shipment,
–SUM(CAST(la5.attr_value AS INT)) AS Cases
la5.attr_value AS Cases
FROM
shipment s WITH(NOLOCK)
INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id
INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id
INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND
s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local
INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = ‘CaseQty’ AND attr_grp = 3)
WHERE s.spare2 = ‘RELEASED’
AND shift.shift_desc = ‘c’
AND DAY(ss.production_date) = DAY(’2010-08-09 00:00:00.000′)
AND MONTH(ss.production_date) = MONTH(’2010-08-09 00:00:00.000′)
AND YEAR(ss.production_date) = YEAR(’2010-08-09 00:00:00.000′)
–and isnumeric(la5.attr_value)=1
–GROUP BY s.row_id
It is returning data like ..
Shipment Cases
12436 40
12436 40
12436 27
12437 27
12437 24
12437 24
12437 40
We can see there is no non numeric type data fetched..
if we executing the following query we are geting result what is expected .. just I have added isnumeric(la5.attr_value)=1 in the WHERE condition
SELECT s.row_id as shipment,
SUM(CAST(la5.attr_value AS INT)) AS Cases
FROM
shipment s WITH(NOLOCK)
INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id
INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id
INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND
s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local
INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = ‘CaseQty’ AND attr_grp = 3)
WHERE s.spare2 = ‘RELEASED’
AND shift.shift_desc = ‘c’
AND DAY(ss.production_date) = DAY(’2010-08-09 00:00:00.000′)
AND MONTH(ss.production_date) = MONTH(’2010-08-09 00:00:00.000′)
AND YEAR(ss.production_date) = YEAR(’2010-08-09 00:00:00.000′)
and isnumeric(la5.attr_value)=1
GROUP BY s.row_id
Shipment Cases
12436 107
12437 115
My question is if there is no Non Numeric data selecting in that particular query the why I am geting error in the first query. In the Second query you can see.. there i no non numeric data found.. and in the Thrid query you can see if we add ISNUMERIC() FUNCTION then it is executing without returning any error..
HI Pinal,
how can i use case statement in join conditions?
Regards,
Naresh
like below
ON CASE WHEN t1.col=t2.col1 then t2.col1 ELSE t2.col2 end
sir can we create sql backup on outside server location.
I want to backup databases on my local disk,it is possible ?
Use UNC Path
backup database your_db to disk=’\\your_sysname\shared_folder_name\file_name’
Dear sir
how to insert microsoft comma separated vales file(.csv) in sql server 2000,kindly help me sir
Use BULK INSERT as show below
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
or make use of import/wzport wizard
hi,
I have a three tables where the information is
countrycode,countryname
1,India
Another table information is
StateCode,StateName,CountryCode
1,Tamilnadu,1
2,Delhi,1
then another table information is
CityCode,CityName,StateCode,CountryCode
1,Chennai,1,1
2,Madurai,1,1
3,New Delhi,2,1
I want the query result as
CountryName,no of states,no of cities
India,2,3
Please let me know as early as possible the tell your suggestion or query for this.
Thanks
Samy
Select c.countryname,count(s.statecode) as states,count(ci.citycode) as cities from country as c inner join states as s on c.countrycode=s.countrycode inner join city as ci on s.statecode=ci.statecode
Hello Pinal Dave,
I have a situation where I need to insert records from a temp table into a table that has a large number of records only when they don’t already exist in the large table.
In reality I only need to consider records from the large table that were inserted within the past few hours.
I am using a left join construct to do this as below:
INSERT myTable (TicketType,TicketKey,Provider,ExpirationDate,ErrorCode)
SELECT
vr.TicketType,
vr.TicketKey,
vr.Provider,
vr.ExpirationDate,
vr.ErrorCode
FROM
#MyTemp vr LEFT JOIN myTable vrt
ON
vr.TicketKey = vrt.TicketKey
AND
vr.Provider = vrt.Provider
WHERE
vrt.TicketKey IS NULL;
My questions are:
1. Is there a better way to do this?
2. Would it be better to add a where clause to limit the records I left join to in the large table?
3. Would it be better to create a view with a where clause to limit the records being compared?
I have downloaded the SQL Server 2008 questions and answers pdf but can’t print it and I need to. Can I purchase the rights to print it from you?
I am working on sql2pdf conversion.
From google i got the sp for convert using (see this link )
http://hosteddocs.ittoolbox.com/GB2.010807.pdf
The pdf file is not generated in the C:\.
When i appended the following code:
EXEC @hr = sp_OADestroy @object
IF @hr 0
BEGIN
EXEC sp_OAGetErrorInfo @object
END
It displays the error as :
Error: 0×80042730
Source: ODSOLE Extended Procedure
Description: The passed object is invalid.
HelpFile: NULL
HelpID: 0
Can you please solve the problem.
Regards,
Dilip
Hi
I am trying to create some stored procedures in SQL 2005 after using 2000 for a number of years. I am encountering two problems that never occurred with the earlier version and cannot resolve the problems after numerous attempts. I will list the 2 problems in separate requests for help –
1) I have an ID integer column that I want to compare to a string of passed parameters. I know that implicit conversion is no longer done but all attempts to convert or cast the integer field to varchar still result in the error message:
Conversion failed when converting the varchar value ’1,2′ to data type int.
I am building a querystring based on incoming parameters which is executed at the end. There are two ways I had tried to convert (using convert and cast) –
example 1 – cast as varchar during select:
Set @qry= ‘SELECT Person.PersonID,Job.JobID,Cast(JobOrg.OrgTypeID as varchar(5)) OrgTypeVarChar, ‘
then compare to incoming parameter:
Set @qry = @qry + ‘ AND OrgTypeVarChar IN(”’ + @OrgTypeList + ”’) ‘
I have also tried to not convert the data type in the select but convert in the comparison:
Set @qry = @qry + ‘ AND Cast(JobOrg.OrgTypeID AS varchar(5)) IN(”’ + @OrgTypeList + ”’) ‘
Both result in the same error. Any advice would be greatly appreciated.
Hi,
I am working on the Full Text Search in Sql Server 2005. I have created a catalog and then an index on a particular table.
I am searching for data using FREETEXTTABLE(tablename,*,@SearchKeyword). So I am searching in all columns in the index since any of the columns can contain the keywords.
Is there is a way for me to know which columns in the index contained my search keywords?(without having to go and look at each column in the resultset)?
I would appreciate any help in this regard. Thanks.
Kalyan.
Hi Pinal
I need your help / ideas to perform advanced search on a string… I cannot use FTS because of many other constraints…..
For ex: I have a string like below.
“SQL Server 2005 Express Edition is the next version of MSDE and is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005.”
User’s search would be: Express near4(“next”)
Sentence should have “Express” word and “next” word should be there in either left or right side 4 words of “Express”.
In the above sentence “next” is the 4th word (right side) from “Express” word so this should come as result…
If user enters Express near3(“next”) — above sentence should not come because “next” word is not there 3 words….either side
Hi Penal,
I am using Access as a frontend application. My access forms include subforms. These subforms have a rowsource to temporary files. (On opening a form, I use code to generate a random number and use sql to create the table in my SQL database.
I have decided to migrate to developing code using SQL stored procedures, however I still wish to keep access as my frontend application.
Is there a way to use #temp files in SQL and send Access the name of the file which will inturn be used as the rowsource of the access subform?
Dear Penal,
How can I find the k-nearest neighbors (like GPS device) without scanning the whole table?
Here’s what I found that can find the nearest w/o scanning the whole table, but it does not give k-nearest.
http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx
Thank you!
hi Dave,
will you help me to get query to calculate difference between times ,data type is declared as varchar
thanks in advance
with regards
S.Vanitha
Post some sample data with expected result
I want to write storeprocedure for retriev some data
Table1:Group
GroupId GroupName
1 ABC
2 XYZ
3 PQR
4 BNC
Table2:- Contact
ContactId Name GroupId
1 Jone 3,2,4,1,
2 Michle 2,5,
3 Tiger 1,4,
In parameter i pass the GroupId Like 1 ,2 ,3
Suppose I am pass 2
I want result like this
1 Jone XYZ
2 Michle XYZ
Please help me for How to write a store procedure for that
Thanks .
Nishar
Hi Dave,
could you help me, our sql server is operated in US, so the data in the datetime field is storing in US Timezone Format.
At the time of retreiving the Data , i need to convert into UK Timezone.
How can acheive this UK timezone ?
Could you send me some sample query
Thanks & Regards
Velu
You should do formation at the front end application. Otherwise use convert function
select convert(char(10),getdate(),103)
Hi Pinal,
I have scheduled hrs for each employee based on this i need to calculate percentage of employee who scheduled less than 24 hrs ,percentage of employess who scheduled between 24 and 48 hrs and more than 48 hrs.
SCheduled hrs will be weekly wise.
Ouput should be like this
Scheduled hrs 8/1/2010 8/8/2010 8/15/2010 8/22/20
Less than 24hrs 45% 64% 76% 48%
B/W 24 and 48 45% 35% 12% 61%
more than 48 10% 1% 12%
Hi.
I have 20 tables in my database and almost more than 100 columns.My scenario is i have one stored procedure which connects 9 table and 51 columns.From that I need a search functionality.Like I can type any thing in one box and it will give me all the search result in rows with these columns and tables.I don’t need to define any table or column in the query because table may be more and column also may be more..How is it possible?
I appreciate if any one can help me.
Thanks.
Refer this post. This does exactly what you want to do
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
Hi Dev,
SSAS
Is the parent child dimension need to be created from single table? is it compulsory?
Thanks,
Naresh D
Hi Pinal,
I know the internal execution of SQL Query.
but , I want to know the internal execution of WHERE Clause.
If there is ‘ABC’ table with 100 rows & 2 column(ID , NAME) & i am using WHERE Clause.
SELECT * FROM ABC WHERE ID > 10
suppose it gives me 60 rows. for that 60 rows they execute whole table using 100 rows.. right !!!!!!!!!!!!!!!
but if query is like that,
SELECT * FROM ABC WHERE ID > 10 AND NAME = ‘PRAKASH’
suppose this time it gives me 40 rows.
so, for getting this result SQL executes 100 rows 2 times????
1st time for ‘ ID > 10 ‘ == 100 rows
2nd time for ‘ NAME = ‘PRAKASH ‘ == 100 rows
OR
its use the following things.
1st time for ‘ ID > 10 ‘ == 100 rows
2nd time for ‘ NAME = ‘PRAKASH ‘ == 50 rows
HI Dave,
I knew about you and this site before a couple of months and am satisfied so much, now I have a question to ask you which your answer can help me in a great way.
i have a select query as follows.
SELECT column_name , data_type
FROM information_schema.columns
WHERE table_name = ‘transaction_queue’
SELECT ‘ColumnName’,'DataType’
which returns:
transaction_queue_id int
policy_id int
transaction_queue_status_type_id int
‘ColumnName’,'DataType’
My request is:
How can i get the SECOND RESULT SET as header (first row in the result) as below
column_name data_type
transaction_queue_id int
policy_id int
transaction_queue_status_type_id int
since I am beginner to SQL, I was little bit struggled and unable to answer my senior’s question, kindly help me to find a best and short-cut solutions for this.
SELECT ‘ColumnName’ as column_name,’DataType’ as DataType
union all
SELECT column_name , data_type
FROM information_schema.columns
WHERE table_name = ‘transaction_queue’
Hi Pinal,
my Application is scheduling agents(employees) for the compaign(callcenter) like dell,microsft,ibm.compaign will be under region.Should i need to maintain compaign and region columns in master table(EmpId,EmpName,Compaign,Region)
Hi Pinal
I am trying to know now is, how the sql engine is parsing this query?…
When you use TOP in the inner query then tsql uses the inner query as virtual table.
When I dont use TOP it errors out my.Why is the inner query not used as inline view or virtual table
Use script below…
create table record_dt (reldate varchar(10));
insert into record_dt values (’2010 09 7 ‘);
insert into record_dt values (’2010 0830 ‘);
insert into record_dt values (’1999 01 26′);
insert into record_dt values (’1998 12 01′);
insert into record_dt values (’1999 03 09′);
insert into record_dt values (’2009 02 10′);
insert into record_dt values (’2008 10 28′);
Query below does not DOES NOT WORK
select * from (
select
convert(datetime,replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) = 1)
) as inn
where inn.rel_date 4
and (isdate(replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) = 1)
) as inn
where inn.rel_date 4
and (isdate(replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) = 1)
) as inn
where inn.rel_date<=getdate()
Thanks
Sam
hi,
your articles are more helpful for me thanx ,
i have a dought i.e,
when we use “object” datatype in sql server.
i faced this Question in an interview
Thanks in Advance ,
Rama krishna
There no such datatype. Are you using SQL Server?
hi,
yes i am using sql server only , ok thanx
Hafiz Sajid Kamal
Dear Pinal,
This is Sajid Kamal Sr. Software engineer, we are working in Healthcare ERP.
I regularly follow your blogs.
This time we are facing very serious problem in SQL Server 2005.
When user(s) saved a bill and get printed, some times data lost from transaction tables.
All the saving is done through procedure under single transaction.
Is it possible that after committing the data, data get removed from the table?
I need your help.
Thanks & Regards
Sajid Kamal
Sr. Software Engineer
Akhil Systems Pvt. Ltd
New Delhi
There is no way of data loss until there are some other programs or triggers delete them
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID
Thank you very much for your help.