Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1600 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
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 Dave
Could you please explain detail about Magic tables and what are its use in a new post
Thanks,
Ahmed Tabrez
If you are referring about the tables inserted and deleted, they are explicitely used in the triggers whose data structure rely on the underlying tables in which the trigger is created
Hi:
Is it possible to create a computed column that will serve as primary key using a VarChar column and an Idenity int column.
For example:
VarChar column value = X
Identity value = 1
Primary Key column = X1
Thanks!
Yes it is something like this
create table testing(i int identity(1,1), x varchar(10), n as (x+cast(i as varchar(10))) persisted primary key )
Sir,
I faced a problem while putting validation rules in sql server 2005. I need to put some validation rules on text type of field. I tried to add check constraint with the field having data type as text but it gave me error message. I again tried to add a trigger to validate the input but it gave the error for text type of field.
So please tell me how to validate input for text type of field to check whether it has values like ‘%some text%’.
Please give me answer ASAP.
Thanks.
I once placed a query but didn’t get any answer, hope for this time.
Hi madhivanan,
yes you are correct i was referring to the inserted and deleted tables which are created in trigger when a new row is added or deleted .
now my query is how can we query r see results of deleted table
can any one explain with an example
Thanks,
Ahmed Tabrez
Hi Dave,
I post it with too much hope.I am an intern-ship student doing migration database. I’ m newbie for most of things.I have administrator right. Batch file is saved with ANSI.
I want to run batch file (include sqlcmd ) with sql server agent.
I change ISQL command to SQLCMD and make it sql server agent jobs.I can’t parse syntax and got following error:
// An exceptional occurred while executing a Transact-SQl statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Incorrect syntax near ‘LOADS’.
An expression of non-boolean type specified in a context where a condition is expected, near ‘C:’.
The label ‘C’ has been already been declared. Label names must be unique within a query batch or stored procedure. (Microsoft SQl Server, Error:102)//
—————My syntax is (batch file)
(converted from below original isql)
REM JOB LOADS DAILY LEW FILE
REM 21/7/2000 Change to MSSQL
REM
ebrun.exe C:\Shared\NPAC_Bat_2008_Test\lewd.ebx
REM check lec exist
IF exist C:\Shared\NPAC_Bat_2008_Test\lewd goto process
exit
:process
sqlcmd -U user -P Password -S Myserver C:\Shared\NPAC_Bat_2008_Test\trlewd.log
bcp npacdb..lewd_tmp in c:\Shared\NPAC_Bat_2008_Test\lewd -fC:\Shared\NPAC_Bat_2008_Test\lewd.fmt -Sfmsdev01>C:\Shared\NPAC_Bat_2008_Test\bcplewd.log
sqlcmd -U user -P Password -S Myserver C:\Shared\lewd.log
D:
del C:\Shared\NPAC_Bat_2008_Test\lewd
——————————————————————
original isql (2000 version)
REM JOB LOADS DAILY LEW FILE FROM POWERSUPPLY
REM 21/7/2000 Change to MSSQL
REM
ebrun.exe C:\Shared\NPAC_Bat_2008_Test\lewd.ebx
REM check lec exist
IF exist C:\Shared\NPAC_Bat_2008_Test\lewd goto process
exit
:process
isql -Uuser -Ppassword -Sserver C:\Shared\NPAC_Bat_2008_Test\trlewd.log
bcp npacdb..lewd_tmp in c:\Shared\NPAC_Bat_2008_Test\lewd -fC:\Shared\NPAC_Bat_2008_Test\lewd.fmt –Uuser -Ppassword -Sserver>C:\Shared\NPAC_Bat_2008_Test\bcplewd.log
isql -Uuser -Ppassword -Sserver c:\npac\schedule\logs\lewd.log
D:
del C:\Shared\NPAC_Bat_2008_Test\lewd
then I change to this way………………..
REM ‘LOAD’
:setvar firstdir “C:\Shared\lewd.bat”
-setvar secdir “C:\Shared\trlewd.txt”
:setvar thirddir “C:\Shared\trlewd.log”
:setvar forthdir “C:\Shared\lewd.fmt”
:setvar fifthdir “C:\Shared\bcplewd.log”
:setvar sixthdir “C:\Shared\lewd.txt”
:setvar seventhdir “C:\Shared\lewd.log”
if exist $(firstdir) goto process
:process
sqlcmd -U user -P Password -S myserver -i $(secdir)
go
bcp npacdb..lewd_tmp in $(forthdir) -U user -P Password -S myserve
go
sqlcmd U user -P Password -S myserve -i $(sixthdir)
go
:D
del C:\Shared\lewd
———————————
I still got this error
Incorrect syntax near ‘:’ . (Microsoft SQL Server, Error 102)
Pls Pls Pls Pls help me! I m torturing one week already with this.I don’t know what to do. Tomorrow is dead line. Hoo!
If anything wrong, pls forgive me.
Thank you very much in advance.
Thanks & Regards,
Kaysi
Hi Pinal,
I am software engineer at pune with 4+ years of experience in SQL server.
The problem I am facing is I am into technicle support kind of things where I am into correcting the client database records to make sure that our software works fine for the clients. In this job I am not into writting any procedures,triggers or any complex codes. Before this I was into development there I was in touch with writting procedures, triggres, cursors and I was quite good at it.
Now I have no issues with writting all these things, but wherever I go for the interviwes they are asking for the Query tuning and optimization. Unfortunately I do not have any experience in this fields.
I have already gone through most of your blogs in query optimization and tuning tried at my end also read lots of books/articles on this, but I am not very much confident about it.
Can you please suggest me the best way to face these interviwes and if you know anybody from pune which provides training on advance SQL Query tuning and optimization?
Thanks!
Sachin :)
Hi,
We are using the following SP in SQL Server 2008 R2 to retrieve tables having changes (Insert, Update, delete) from a specific version (Using Change Tracking). But it takes about 3 minutes for a 1000 tables database. Is there any way to increase the performance?
ALTER PROCEDURE [dbo].[spGreenChangedTables] ( @Last_version BIGINT, @SYS_CHANGE_CONTEXT VARCHAR(250) = ‘GreenReplicationChanged’, @TableName sysname = ” ) AS
BEGIN SET NOCOUNT ON; –============================== DECLARE @SQL NVARCHAR(MAX) DECLARE @TBL SYSNAME DECLARE @RET INT SET @SQL = ‘ CREATE TABLE #TMP(ID int IDENTITY(1,1) PRIMARY KEY,TBL SYSNAME) ‘ + CHAR(13) SELECT @SQL = @SQL + ‘ INSERT INTO #TMP(TBL) ‘ + CHAR(13) + ‘ SELECT TOP 1 ”’ + TGRT.Name + ”’ ‘ + CHAR(13) + ‘ FROM CHANGETABLE(CHANGES ‘ + TGRT.Name + ‘ , ‘ + CAST(@last_version AS VARCHAR(50)) + ‘) AS CHNG ‘ + CHAR(13) + ‘ WHERE 1 = 1 ‘ + CHAR(13) + ‘ AND ISNULL(CHNG.SYS_CHANGE_CONTEXT,0) CAST(”’ + CAST(@SYS_CHANGE_CONTEXT AS VARCHAR(MAX)) + ”’ as varbinary(128)) ‘ + CHAR(13) + ‘ ;’ + CHAR(13) FROM sys.tables AS TGRT INNER JOIN sys.change_tracking_tables AS CTT ON TGRT.object_id = CTT.object_id WHERE 1 = 1 AND TGRT.name LIKE ‘tbl%’ AND TGRT.Name = CASE @TableName WHEN ” THEN TGRT.Name ELSE @TableName END –================================================================== SET @SQL = @SQL + CHAR(13) + ‘SELECT * FROM #TMP ‘ –PRINT @SQL EXEC(@SQL) END
Thanks in Advance,
Maral
Hi,
I am not an SQL pro. But my simple requirement is as follows. I have 2 SQL Tables one having Tagnames and the other having the corrsponding tag values for the Tagnames. I have to create a new table where Tagnames will be the column names and the corresponding tag values for that will be under each column. Could anybody can help me with some sample code for this.
Regards,
Manash
Hi Pinal,
How can i contact you? Please mail me the details of it.
Thanks & Regards
Sumedha
Hi Sumedha,
Did you get the email address or any contact of Pinal?
Thanks,
Abdul
Sir,
I am having three tables
1 Trans_transnar (view) ‘ AROUND 1 LAKE RECORDS
2 Arbills ‘ 30000 RECORDS
3 Argls ‘ 200 RECORDS
I Want to Update ARBILLS TABLE ,
By Select data from TRANS_TRANSNAR If that data Is
NOT EXIST IN ARBILLS, THEN ONLY I Have To INSERT DATA INTO THE ARBILLS Table. And Also I have To Check
With ARGLS TABLE. If The GLCODE IN THE ARGLS Only
Select From The TRANS_TRANSNAR VIEW.
I write the Query for that. Its working.
But I dont know perfortmancewise it is correct or not.
If its NOT a Correct Method. Please Tell
us how to write this Query.
Thank you,
Insert into USERDATA.DBO.ARBILLS (trtype,COCODE,BRCODE,loccode,glcode,slcode,trdt,trno,BILLNO,billdt,billamt,BALAMT,regno)
Select f.trtype,f.COCODE,f.BRCODE,f.loccode,f.glcode,f.slcode,f.trdt,f.trno,f.REFNO,f.refdt,ROUND(f.tramt,2,0) AS BILLAMT,ROUND(f.TRAMT,2,0) AS BALAMT,isnull(F.regno,’-') as regno FROM
(SELECT A.COCODE,A.BRCODE,A.LOCCODE,A.TRTYPE,A.TRNO,A.TRDT,A.GLCODE,A.SLCODE,A.TRAMT,A.REFDT,A.REFNO,A.DRCRFLAG,SUBSTRING(A.NARRATION,21,50) AS REGNO FROM ARGLS B ,trans_transnarr A
WHERE ( A.COCODE = ‘C0001′ AND B.LOCCODE = ‘L0001′) AND B.LOCCODE = A.loccode AND B.GLCODE = A.glcode AND CANCDT IS NULL and a.refdt 0 and f.drcrflag = ‘D’ and len(f.slcode) >0)
/* To Create Table */
If Object_ID (‘Details’) Is Null
Begin
Create Table Details
(
FirstName Varchar(100),
MiddleName Varchar(100),
LastName Varchar(100)
)
End
Else
Begin
Print ‘Change the table Name’
End
GO
/* To Insert Records */
Insert Into Details (FirstName,MiddleName,LastName)
Values (‘ABC’,'DEF’,'GHI’)
GO
/* To Retrieve Records */
Select FirstName,MiddleName,LastName From Details
GO
I want out put like this……
Please help
ColumnName Value
———- —–
FirstName ABC
MiddleName DEF
LastName GHI
Hi Akash,
In data base the values comes like
fname middlename lastname
ABC DEF GHI
In front end you may change
Create Table Details
(
FirstName Varchar(100),
MiddleName Varchar(100),
LastName Varchar(100)
)
GO
Insert Into Details (FirstName,MiddleName,LastName)
Values (‘ABC’,'DEF’,'GHI’)
GO
Select FirstName,MiddleName,LastName From Details
GO
I want out-put Like This
Please help……
Select ‘FirstName’ ‘ColumnName’,'ABC’ ‘Value’
Union
Select ‘MiddleName’,'DEF’
Union
Select ‘LastName’,'GHI’
Order By Value
Hi all,
pls…………………..help me
I really don’t know what’s wrong with my sqlcmd command
Pls help me point out where did I wrong.
Thanks million
Thanks & Regards,
kaysi
what is your sqlcmd ?
Function vs Stored Procedure. Which one is faster? and why?
Hi,
How r u comparing the efficiency of func and SP ?
Each of these two are meant for diff usage.
Func : should return scalar/table to the calling statement
SP : execute a series of sql statements but not embedded in any sql statement. SPs are compiled (if there is not Dynamic sql) unlike Funcs are recompiled and executed
static vs dynamic query. Which one is faster? and why?
Static queries are always faster as the objects are resolved during compilation. If you dont know to which object you need to refer, then you would go for Dynamic sql – here, objects names are resolved during runtime which makes the sql to be recompiled
what is clustering? how many types are there? which one is best ?
Why dont you search for the same in google/bing?
Clustering is a concept of grouping related items.
What is your question about ? Are you asking about Indexes ?
Hi Pinal,
I have created a linked server from SQL 2000 to 2008 using SQL OLE DB Provider, but querying the remote SQL 2008 server only works intermittently. I get: Server: Msg 8179, Level 16, State 1, Line 2 Could not find prepared statement with handle -1. I installed the SQL 2008 native client on the SQL 2000 server but still the same. The Windows firewall is not enabled on the server either. Any ideas?
The query I am using is:
SELECT mmatter, clname1 + RTrim(‘ ‘ + IsNull(clname2, ”)), mdesc1 + RTrim(‘ ‘ + IsNull(mdesc2, ”)), mopendt, mbillaty, msupaty
FROM matter
INNER JOIN client ON matter.mclient = client.clnum
INNER JOIN udf ON matter.mmatter = udf.udjoin AND udtype = ‘MT’ AND udfindex = 87
WHERE mstatus ‘CL’
AND udvalue = ‘LSLONDOFF’
AND NOT EXISTS
(SELECT *
FROM RemoteServer.Extranet.dbo.Matter Matter
WHERE Reference COLLATE Latin1_General_CI_AI = matter.mmatter COLLATE Latin1_General_CI_AI
AND IsNull(Matter.PendingDelete, 0) = 0
AND Matter.SiteID 2)
ORDER BY 1
GO
Thanks
Stuart
Hello Sir,
I have a problem.Please help me out.Here is the situation.
There is a database test1 on server1.
under this database test1 ,there is a table t1 having 5 columns.
name
roll
phone
address
address2
Now there is another database test2 on server2.
under this database test2. I want to have a replica of t1 as t1 but with some more columns say 5 columns i.e this table will have 5+5 = 25 columns.
name
roll
phone
address
address2
marks1
marks2
marks3
marks4
marks5
what i want to achieve is that, if there is any changes in any column values in t1 under database test1 on server1,that should automatically reflect in t1 under database test2 on server2.
i.e, these 2 tables should always be in sync but marks1,marks2,…values should remain intact.I donot want to loose these values.
How can i do this?Please help me out ASAP.
Thanks,
Dhiraj
Check for DML triggers on test1.t1 table
Thanks Vinod,
Here is what i have done on test1.t1
ALTER TRIGGER [Driver_update_Rtsbos2]
ON [dbo].[DriverInfoTest]
AFTER INSERT
AS
BEGIN
insert into webserver.RtsBosDataBase.dbo.driverinfonew (ID,Name,DriverPin,Sex,FatherName,NRICNo,DateOfBirth,
PhoneNo,Address,OwnerId,LicenseNo,LicenseIssueDate,issuePlace,
LicenseValidDate,Licensetype,TaxiNo,SmartCardID,
DriverType,Reference1,RefAddress1,Reference2,RefAddress2,status,
FrID,deletestatus,driverid,CompanyId,SubsStatus,
SubsStartFrom,SubsActivate,language,CUGCardNo,CabNo)
(select ID,Name,DriverPin,Sex,FatherName,NRICNo,DateOfBirth,
PhoneNo,Address,OwnerId,LicenseNo,LicenseIssueDate,issuePlace,
LicenseValidDate,Licensetype,TaxiNo,SmartCardID,
DriverType,Reference1,RefAddress1,Reference2,RefAddress2,status,
FrID,deletestatus,driverid,CompanyId,SubsStatus,
SubsStartFrom,SubsActivate,language,CUGCardNo,CabNo from orix.dbo.driverinfotest where ID=@@identity)
END
After creating above trigger I was getting an SQL exception ‘The partner transaction manager has disabled its support for remote/network transactions’
Then after doing some settings on both servers,this exception has gone but a new problem has come.Now it is giving ‘No transaction is active’.Although there is a transaction on test1.t1.
Can u suggest me any solution?
Thanks,
Dhiraj
Hi,
I want to know who we can use 2 server for our single application .how we can mange them.also cache servers
1) Is there a quick way to find out what port number SQL is listening to? I have 10 instances running on one server and I could find out by going to SQL log but it’s bit time consuming.
please tell me in any query to retrive this listner port list?
Regards
harish
You wrote a blog entry about setting up Database Mail – http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/ – which was great and I was able to send out email tests in SQL Server 2008 R2 with no issues.
Now, however, I want to use that account to send out Notifications for various Job failures and the account does not appear as an option in a given Job’s Notifications tab. Can you please help?
I have created your query from your article series:SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2 per the following: I am geting this error, How do I assign the required permission
Msg 262, Level 14, State 1, Procedure vw_ViewLimit1, Line 4
CREATE VIEW permission denied in database ‘AdventureWorks’.
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N’[dbo].[vw_ViewLimit1]‘))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
– Create View on sample tables
CREATE VIEW vw_ViewLimit1
AS
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
GO
Hi Dave,
I have a project where I need to create a #temp table that has uniquely named rows for a given condition. so..
If x=1
Create table #temp(
name varchar(10),
address varchar(20)
)
If x=2
Create table #temp(
name varchar(10),
Phone varchar (12)
)
Seems like even if the condition is NOT true, SQL is trying to create the temp table anyway as my results always tell me that there is already an object called #temp. =(
any suggestions greatly appreciated!!
SQL Server does not allow same table name in IF-ELSE constructs. While parsing, SQL Server creates a plan and reserves the object name as #temp. for x=2, same name #temp is used. Hence the error.
Try the below sql :
declare @x int
set @x=2
If @x=1
begin
Create table #temp(
name varchar(10),
address varchar(20)
)
end
else If @x=2
begin
Create table #temp2(
name varchar(10),
Phone varchar (12)
)
end
i need to synchronize two or more sql servers..
In vb.net windows application,i used sql server 2008 for database.
For deployemnt and implementation process, we used currently the sql server is in the web.client have different locations and want to access application from any where.
The problem is at some rural area the internet down time is high and user unable to do data entry job in the application.Then the requiremnt have changed to use local server for each branches . After completing data entry work and they want to update to sql server in the web.during that process latest transactions changes from local server have to update in web server, mean time another branch local server will also update to server in web.and local servers have to get exchange data through web server.When user click update button, only todays transactions from local server should update to web server and vice versa and also another branch latest data that had already updated in web server have to transfer to local server.
What are the possible ways to do this ?
could you guide me for this situation?
is any tool available for this?
Friends
I am working on some reports in SSRS. In our region, we have our week ending on friday. So when I work on weekly report and I want to use the “datepart” function, the result is not as expected.
I tried using the “set datefirst 5″ and the “set language arabic”, but this did not help.
What is required is that,
declare @myDate datetime
set @myDate = ’2010-10-30′
select datepart(wk,@myDate)
should return 45 not 44
For Nov,
The Weeks are
45: Oct 30 – Nov 05
46: Nov 06 – Nov 12
47: Nov 13 – Nov 19
48: Nov 20 – Nov 26
Note: Nov 27 – Dec 03, that is Week 49, is considered to be in Dec for weekly reports.
For Dec,
The Weeks are
49: Nov 27 – Dec 03
50: Dec 04 – Dec 10
51: Dec 11 – Dec 17
52: Dec 18 – Dec 24
53: Dec 25 – Dec 31
Please let me know if we can work out a logic to handle this case.
Thanks.
Shibu P
Friends,
I have received help from another form. I am posting an update on my earlier post.
It was my mistake, I should have used “SET DATEFIRST 6″ as my start date is saturday.
SET DATEFIRST 6
GO
declare @myDate datetime
set @myDate = ’2011-01-01′
select datepart(wk,@myDate)
Regards
Shibu P
Hi Dear Pinal
Can you Explain what is difference between this two statement:
1)select @@trancount
2)DBCC opentran
when I Use TransactionScope in C# Statement No.1 return 0 but No.2 return Result.when I Use begin Tran in sql server No.1 return 1 but No.2 return nothing.
what is difference between them?
thanks A lot
SQL Server help file has more details about them
select @@trancount : @@trancount is a automatic variable which returns integer values – no. of transactions open in a session.
DBCC opentran : gives informational messages for the last transaction
I am creating an SSIS package and my test data has a lot of dups, so I am trying to strip them out of the data before inserting it into my production table. The following code from a stored procedure runs fine with SQL Mgmt Studio and removes the records from my data but won’t execute and remove dups from an SSIS Execute SQL Server Task:
DECLARE @quie int
DECLARE @quievalue varchar(100)
DECLARE Dups CURSOR FOR SELECT Max(RxClaimStageId), ClientId + PatientID + DateofService + CAST(ListedDrugID As varchar(11))
FROM Stage.RxClaim
GROUP BY ClientId, PatientID, DateofService, CAST(ListedDrugID As varchar(11))
HAVING COUNT(ClientId + PatientID + DateofService + CAST(ListedDrugID As varchar(11))) > 1
OPEN dups
FETCH NEXT FROM dups INTO @quie, @quievalue
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM Stage.RxClaim Where ClientId + PatientID + DateofService + CAST(ListedDrugID As varchar(11)) =
@quievalue AND RxClaimStageId @quie
FETCH NEXT FROM dups INTO @quie, @quievalue
END
CLOSE dups
DEALLOCATE dups
Any ideas why or know of any settings on the connection string I can alter to make this work? I have even copied this to another Execute SQL Task so it runs before my call to the stored procedure (so it is running twice) and it still does not execute and doesn’t throw any errors.
Disregard my earlier question, the problem is me. I modified dates after the attempt to remove dups, which actually caused the dups. I have it working now. So it would work on the second attempt.
Thanks,
Jim Denny
how can i automatically refresh a cube.when data updates in the table,it should affect the cube also.how can i done that
Hi Pinal,
We are using SQL 2005. Now my Database MDF file size is approximatly 50GB and running out disk space.
We have an network drive where we have approximately 5TB of disk space.
We want to move Database MDF and LDF to network Drive.
Is it possible to move MDF and LDF files on network drive and use the same SQL Server?
If yes how we can achieve this.
Please help
Anshul Kumar
Hello Sir,
I have one stored procedure which gives me 2 tables (result sets)
Create Proc GetJobs
As
Begin
–Here JobID is primary key
Select JobID, JobName from JobQueue where Status = ‘In Progress’
Select JobID, JobName from JobQueue where Status = ‘Processed’
End
There is one problem. Sometimes I got same JobID in both result sets. Because when system execute first query then Job Status is “In Progress” and before start executing on second query if Job goes in “Processed” state (by executing below UpdateJobStatus stored procedure) then system show that Job ID is in second result set also.
Create Proc UpdateJobStatus
@JobID int,
@Status varchar(20)
As
Begin
Update JobQueue Set Status=@Status where JobID = @JobID
End
Is there any way by which I can restrict others users/query/stored procedudre to update the JobQueue table during execution of GetJobs stored procedure? I know that lock might work for this but how I don’t know. Please help me.
Thanks a lot in advance!
Regards
Paresh
1. Set the Storage Settings to “Real-time ROLAP�?(standard setting) and in “Options�?selected Storage Mode as “ROLAP�? Enable Proactive Caching.
2. In the General tabs, checked “Drop outdated cache�?Latency 0 seconds (default)
3. Under Notifications, “SQL Server�?option selected and no Tracking tables specified
………………….thus i set my ROLAP cube but it doesnot work.i want to process the cube for new data.\
please any one answer for this solution ?
Hello Pinal ,
I have a question for you regarding SQL 2008 R2 Installation . Basically I am a starter asp.net developer and I am trying to install SQL 2008 R2 with Analysis Service on my machine . My Problem is regarding ” COLLATION ” thing which needs to be specified while server configuration . While configuring server I want to specify ” Collation ” instead of service accounts. I was asked by my boss to install SQL 2008 R2 with Analysis Services . I can find Collation attribute ” SQL_Latin1_General_CP1_CI_AI” for Database engine. But I cannt find the same attribute for Analysis Service . Instead of desired Collation attribute I found ” Latin1_General_CI_AI ” . Now Set Up is giving me a warning saying that both of these collations must be same . I tried to find some solution for this in internet but couldnt find any . Some says that I need to change my default language settings to English US , currently I have Windows 7 OS with English India as language settings .
Please Help me ……..
Hi,
Can any one demonstrate with screen shots how to build a add-in for sql server 2005.
Something like executing a query or something like that.
Dear Mr. Pinal,
i am facing a problem with SQL 2000 server. We are using a software intachange of intasoft for changemanagement and its backend is sql 2000 database and 2003 server (OS).
It used to send SQL mail through our exchange server account.
But now we are getting email but all are in wired charecter set like below:
䴼呅⁁瑨灴攭畱癩∽潃瑮湥祔数
in Body, though subject is in English as usal. WHile we tried test email, it sent in English text.
Please reply me soon.
Thanks and best regards,
EKHAN
Was wondering if you could help. I have a .mdf file, no .ndf or .ldf file, anyway to attached or restore ? I don’t need the actual data , but the database tables.
thanks
Hi Pinal,
I am fairly new here. Let me know if iam posting at wrong place.
our database is partitioned on monthly basis and i am seeing large amount (nearly 50-70%) of space available in the data files. These files are not expected to grow(in large amount) in future. So i want to remove the unused space. What is the reason for that large amount of unused space and how to eliminate it? I tried shrinking the file which decresed the unused space to decent size but that resulted in fragmentation. To avoid fragmentation I rebuilded the indexes which again brought the unused space to the original size.
Is there any way to find which table (which field ) is causing this.
thanks in advance,
sneha
Hi Pinal,
Look, is there a way to migrate all my reports files from one reportserver to other?
It’s kinda boring moving the reports one by one between servers everytime we need to migrate.
Thanks for advance!
Hello Sir,
I want to move only stored procedure from one database to another database using sq l-query. or any other way…Please suggest the solution.
Thanks in Advance,
Avdesh Kataria
[remove phone number]
There is another way. Right click on your database name then go to task then Generate Scripts then select your database name then next then select stored procedure then next then select the store procedure which you want to create or copy to another database. Then next and finish. So it will create a new query window. Copy them and paste into your another database and execute it. Thank. Sorry if something wrong.
Hi Dave
I have a question regarding database design.
We have a table (TBLACTIVI) containing activities and their properties (attributes).
An activity can meet various properties. However, as more properties are added, the table grows horizontally (adding more features) and this is filled with values T or F as appropriate.
One option we have is to leave these features in another table -TBLACTIVI_PROPERTIES this table would have the catalog of properties- and a third table that links the two tables.
Thus, in this way, when an activity has a property, we fill the record in the intermediate table.
Which of the designs is best?
With an option, we will end with a table containing multiple attributes and the access would be via index.
With the other option, we remove such access but to find the properties will require an join operation between 3 tables
Thanks in Advance,
R.A.VilledaRuz
Hello sir,
When I am running A DTA for index recommendations,the DTA stopped with error “Tuning process exited unexpectedly” in consuming workload step.Can you pls suggest the possible solutions?
Hello sir,
When I am running A DTA for index recommendations,the DTA stopped with error “Tuning process exited unexpectedly” in consuming workload step.Can you pls suggest the possible solutions?
Hi.
Firstly many thnaks for your blog and code samples, there very useful.
Very quick question which has been causing much scratching of heads. I am in the progess of re-engineering an ETL package which has to run on a local PC. The ETL is eating up a lot of RAM so I’ve broken it down and put a couple of loop containers in it. The first is to loop through years and the second one contained within the first loops through the months of the year. Now the only way I have found so far is to add an execute process task which fires off a batch file which in turn kills the local SQL Server Service and restarts it. This releases the cached memory and subsequently helps the process run a bit quicker. Is there any other way in which the memory can be released without stopping and restarting the service?
Many thanks
Nick
Does any one know how to update and image field??
something like this
Update T
SET img=’c:\img.jpg’
where t.id=12
Is it a varchar datatpye?
Hi Pinal,
Basically, I’ve list of sps, which giving timeout while called from .net code. However, when i tried to call same sp from sql server IDE, it executes in fraction of seconds.
If i drop those sps & create it again, it works fine.
I just want to know that dropping & creating any database object will make impact on performance issue?
Looking forward for your suggestion on this, as we’re facing this issue since very long time.
Regards,
Kaushal
Hi Dave
I have been following your blog for a long time now.
This is first time I have a question for you.
How to edit a DTS package saved inside SQLServer ?
Let says I did a import/export of data from 1 server to another using export/import wizard.
When it gives me a option of running it immediately/save as DTS. I save it as DTS inside SQL Server.
Now if want to edit due to a change in column mapping or sql itself. How do I access the package and edit it?
Currently there seems to be no way of editing it.
It doesn’t show under MSDB in the integration services (object explorer).
thanks
Ganesh
Hi, I have an issue in Sql server stored Procdure. I want to retrieve three rows. My searching criteria is like on EmpId which has int datatype. Now if I am writing a storedProcedure then how can I retrieve all three data. Becuase if I am using in operator then it will give me conversion error. because I am passing parameter. The id’s are not fixed. So is there any other way to write a stored procedure. or How can i convert data from int to varchar.Thanks.
How are you passing the parameter values? Post some sample data
Hi
Just post your code will give u solution,
Hi!,
I want difference between SQL 2005 and SQL 2008
Just tell me some basic differences so that i can tell it in the interview which will be useful and simple.
Thanks
Ashish Fugat
[email removed]
Search for “What is new in sql server 2008?” in google
Hi Pinal Dave
Can you share knowledge realted to SQL Server Clustering.
Regards
RP
Hi Pinal,
I have been constantly visiting ur site and it is really helpfull.
Currently i am working on huge database wherein i am creating joins of multiple tables based on user selection.
IF user does all by all by all selections the query execute successfully, but if few options are selected it gives the below error.
Creating or altering table ‘FakeWorkTable’ failed because the minimum row size would be 10950, including 230 bytes of internal overhead. This exceeds the maximum allowable table row size of 8094 bytes.
Internal Query Processor Error: The query processor encountered an unexpected error during execution.
Could not understand how come memory does not full when sleections are huge and it fills when selections are small.
Please share ur feedback at the earliest humble request.
Hi Pinal Sir,
I have an issue,
When i send mail (front end C#,back end SQL 2005) , it
shows to mail entries in mail box:
Code is OK,button get disabled after First click till i get this issue.
Can you Help me?
Hi Pinal :-),
Can you please explain what CLR waits are all about. I use Quest PA to monitor my key instances and I have noticed tha CLR wait have increased sharply one of them and I think it might be contributing the CPU pressure.
Thank you
Mark Johnson
Pinal,
I am getting this error every minute the sharepoint crawl starts
Logon Error: 18456, Severity: 14, State: 38.
LogDate ProcessInfo Text
2011-01-13 11:00:01.650 Logon Login failed for user ‘domain\SPDevelFarm’. Reason: Failed to open the explicitly specified database. [CLIENT: ]
2011-01-13 11:00:01.660 Logon Error: 18456, Severity: 14, State: 38.
38 – Initial database could not be determined for session (SQL 2008)
This is showing up every minute as it is running crawl for sharepoint
domain\develfarm account is sysadmin
any suggestions
Thank you
Neel
I want to know whether table or stored procedure are being used or not in server and DB.
Beucase most of the tables and SP which are not using the my application since long.so i want to find the those objects.
can you please let me know how to find this
One option is to run a profiler and run each functionality of the application. Anaylse the result of profiler and see what are not used
Hi Pinal,
Which one is correct and why?
sum(isnull(marks)) or isnull(sum(marks))
Hi Balu
isnull(sum(marks),0) this is correct ,
In this if the marks have values it will return the total sum of marks , if no values it will return 0
isnull —> it will return any values or 0 , null will not come in fields
and isnull expects two parameters
Sathish,
ThankYou for replying. But actually I want to know about isnull(sum(marks),0). Why is it not correct?
if all the rows have null for that column, isnull(sum(marks),0) is returning 0. And even it is checking whether it is null for only the sum. If we are using sum(isnull(marks,0)), it may be checking each row whether it is null. Please correct me if I am wrong.
empid name city
001 Raja Sydney
002 ram Melbourne
003 lokesh cbe
001 Raja Sydney
002 ram Melbourne
003 lokesh cbe
My question is how to delete single row when two rows having same values, only the three columns.
Post the expected result
Hi Balu
isnull(sum(marks),0) this is correct ,
In this if the marks have values it will return the total sum of marks , if no values it will return 0
isnull —> it will return any values or 0 , null will not come in fields
and isnull expects two parameters
Sathish,
Again thanks buddy.
Actually somebody told me that isnull(sum(marks),0) can cause error and he suggested me to use only sum(isnull(marks,0)). But I didnt find a scenario where it can cause error.
Balu,
Sathish is right in explanation.
Its nothing to do with error out.
As, ISNULL() returns expr2 if expr1 is null. or expr1 if expr1 is not null. In case, if expr1 is null then it checks for expr2.
actually both statements would return same value. Question here is what u want to achieve here.
Hi,
We are upgrading from SQL Server 2005 (SP3) to SQL Server 2008 R2.
I did the following:
1. Ran the SQL Server Upgrade Advisor and took care of the issues.
2. Moved the Logins SQL Server 2005 to SQL Server 2008.
3. Backed up the SQL Server 2005 Databases and restored them to SQL Server 2008 R2.
So far so good…
My questions:
1. We have SQL Server Reporting Services (SSRS) reports on SQL Server 2005.
Should I open these reports in BIDS 2005 or BIDS 2008 and then apply them ontu the SQL Server 2008 R2 server?
2. We have SQL Server Integration Services (SSIS) packages on SQL Server 2005.
Should I open these SSIS packages in BIDS 2005 or BIDS 2008 and then apply them ontu the SQL Server 2008 R2 server?
3. I understand that SQL Server 2008 SSRS does not use IIS. So how does one configure SSRS on SQL Server 2008 R2?
Any help, tips or pointers appreciated.
Thanks
Friends,
I am getting an error “Invalid use of a side-effecting operator ‘SET COMMAND’ within a function.”
when I am creating a function
–
CREATE FUNCTION [dbo].[CustAgingTrans_ufn]()
RETURNS @retCustAgingTrans TABLE
(
JOURNALNUM VARCHAR(25),
VOUCHER VARCHAR(25), ACCOUNTNUM VARCHAR(25),
TransDate DATETIME,
Narration VARCHAR(150),TrnWKDat VARCHAR(25),
firstWKDay DATETIME, lastWKDay DATETIME,
TransDayDiff DATETIME
)
AS
BEGIN
SET DATEFIRST 6
INSERT INTO @retCustAgingTrans (
JOURNALNUM, VOUCHER, ACCOUNTNUM,
TransDate,
Narration, TrnWKDat,
firstWKDay, lastWKDay,
TransDayDiff)
SELECT
m.JOURNALNUM,
c.VOUCHER,
c.ACCOUNTNUM,
c.TransDate,
c.AccountTxt Narration,
CAST(DATEPART(YY, c.TRANSDATE) AS VARCHAR) + ‘ – WEEK (‘ + REPLICATE(’0′,2 – DATALENGTH(RTRIM(DATEPART(WK, c.TRANSDATE)))) + CAST(DATEPART(WK, c.TRANSDATE) AS VARCHAR) + ‘)’ AS TrnWKDat,
DATEADD(dd,(DATEPART(dw, c.TRANSDATE) – 1) * – 1, c.TRANSDATE) AS firstWKDay,
DATEADD(dd, 7 – DATEPART(dw,c.TRANSDATE), c.TRANSDATE) AS lastWKDay,
DATEDIFF(D,c.TRANSDATE,GETDATE()) TransDayDiff
FROM LedgerTrans c
INNER JOIN LedgerTable m
ON c.Ledg_ID = m.Ledg_Id AND c.AccountType = ‘CUS’
RETURN
END
–
The error is from the SET, I need to use this as my week starts on saturday. Please help me sort out this problem.
Regards
Shibu P
In a function, it is not possible to use a SET command
One alternate is
SET DATEFIRST 6
SELECT * FROM CustAgingTrans_ufn
Thanks, in fact that is what I am using now.
I was hoping that since functions would allow SET to be used in functions as is the case with procedures. And both of them are in the “programmability” section, why this difference.
I should be a difference that is caused by the method in which a procedure is executed and a function is executed in stored procedure.
When I posted this question, I was hoping to get more information in this regard.
Regards
Shibu P
Thanks, in fact what I am using now is .
–
SET ….
SELECT * from dbo….
–
I was hoping that since procedures would allow SET to be used in functions would also allow them as both of them are in the “programmability” section. why this difference?
It should be a difference that is caused by the method in which a procedure is executed and a function is executed. When I posted this question, I was hoping to get more information in this regard.
Regards
Shibu P
Hi Pinal,
I have table like below,
first_nm last_nm Gender
Raj s F
Rajp e M
Kar Sh M
De re M
Sw ko F
Kal la F
Sa Sa M
now i need to update my table each row last_nm with another record last_nm within gender(means female-female,male-male)
example:
first_nm last_nm Gender
Rajp re M
Kar e M
De Sh M
Sw s F
Kal ko F
Raj la F
Thanks,
Srr
Am using below procedure,
declare c1 scroll cursor for select distinct gender from table
open c1
declare @gender_var varchar(2)
fetch first from c1 into @gender_var
while(@@FETCH_STATUS = 0)
begin
DECLARE c2 scroll CURSOR
FOR SELECT last_nm FROM table where table.gender=@gender_var FOR UPDATE OF last_nm
declare @rindex bigint=0 declare @prev_ln varchar(20) declare @lastnm_var varchar(20) declare @CLN varchar(20)
OPEN c2
while(@@FETCH_STATUS = 0)
begin
set @rindex=@rindex+1
if (@rindex=1)
begin
FETCH first FROM c2 INTO @lastnm_var
set @prev_ln=@lastnm_var
print @prev_ln
end
if(@rindex>1)
begin
set @CLN=@lastnm_var
print @CLN
update table set last_nm=@prev_ln –where CURRENT of c2
set @prev_ln=@CLN
print @prev_ln
end
FETCH next FROM c2 INTO @lastnm_var
end
CLOSE c2
DEALLOCATE c2
fetch next from c1 into @gender_var
end
–update table
–set last_nm=@prev_ln where rnk=1
CLOSE c1
DEALLOCATE c1
the above query getting error.
What was the error you got?
Hi Pinal,
I would like to ask in SQL Server how I can have SQL statements especially alter statements. I’m aware of that Generate Script option. In my case it will not work always since it gives whole CREATE statement.
The situation is like this I have DB and my remote developer also have same DB. Whenever I do a change in any of the objects I have to pass the alter SQL statement to my developer so that he can execute the statement in his local DB.
For some reason I can’t go for products like embarcadero and redgate.
Could you pls give me solution?
Kind Regards,
Pappachan
Hello,
I need your urgent help.
I have configured the “Merge Replication” in SQL Server 2008 and SQL server Compact 3.5 and the synchronization process is running excellent.
But in synchronization i want to handle delete condition. Condition 1:- when i delete the records from Sql server compact (mobile database) after synchronization the records deleted from mobile database SHOULD NOT BE deleted from server database .
Condition 2:- But if i delete the records from server database, after synchronization the records deleted from server database MUST ALSO BE deleted from Mobile Database.
I tried my making delete_tracking false for replication but by this only my first condition is satisfied.
sp_changemergearticle
@publication = ‘MobilePublication’,
@article = ‘AttachmentTable’,
@property =’delete_tracking’,
@value = ‘false’
I found on MSDN that both the condition can be satisfied by configuring the merge option change
http://msdn.microsoft.com/en-us/library/ms173005.aspx
I am still not able to find Merge Agent and on which identity does it runs?
from two weeks i am struggling to find the solution.
Please help!!
Thanks in advance.
SachinC
Hi,
I am kushal Basappa from India.
Currently residing in Australia.
I have just completer my masters in Information Technology from QUT Queensland Australia.
I am very much interested in Database field and hence- at the moment preparing for 70-433 Certification.
As you can see I am still a student, Dreaming to become a DBA.
Can you please suggest me – how to pass 70-433.
Exams.
Thanks
Kushal Basappa
Hi Pinal,
How we can reduce time while creating index’s or adding column in table which contains 27 billion records….
In our Production environment it was taking 20 hr approx.
Thanks
Rahul
Can you plz explain why the below query executes within seconds in sql but takes hours to generate the result in SSRS
select
CONVERT(CHAR(10),INVOICEDATE ,103)”Date”, invoiceid, customercode “Customer Code”,
ym.itemid “Product Code”, IB.BATCHID “Batch Number” ,
CONVERT(CHAR(10),EXPDATE,103) “Expiry Date” ,
code , SUM(qty) tot
from salesdim_mel YM ,INVENTDIM ID , INVENTtABLE it , BATCH IB
WHERE YM.INVENTDIMID=ID.INVENTDIMID
and it.itemid=ym.itemid and it.dataareaid=’YIA’ AND ID.DATAAREAID=’YIA’
and it.itembuyergroupid = (@itembuyergroupid)
and it.itemgroupid =@itemgroupid
AND IB.BATCHID=ID.INVENTBATCHID and IB.BATCHID like (@batchNumb+’%')
AND ym.itemid=IB.ITEMID
AND IB.DATAAREAID=’YIA’
and salesoriginid in (SELECT FieldName FROM SplitList(@salesoriginid ,’,'))
AND INVOICEDATE between @stdate and @edate
and code in (SELECT FieldName FROM SplitList(@salestype ,’,'))
and it.itemid between @StartItem and @EndItem
GROUP BY ym.itemid,customercode , IB.BATCHID,CONVERT(CHAR(10),INVOICEDATE ,103) ,
CONVERT(CHAR(10),EXPDATE,103) , code, invoiceid
ORDER BY CONVERT(CHAR(10),INVOICEDATE ,103)
END
Hi Pinal,
I have been assigned a task of enhancing Performance issue of one of the custom applications at work. The problem is at the database level not the front end.
The database consists of lots of views. Each view references other views using unions and inner joins whicn intern references other views.
Views are not schema bound, therefore cannot create indexes on them. On other hand, views that can be schema bound have unions. Also the Business logic has been done in DAL layer (Lot of mathametical caluations). Each view is over 100 lines of code without any comments.
Other problem is that tables are not indexed properly. running each view takes over half hour. Some of the views have update statements which causes locks till view has finished executing which intern locks users at front end.
Please let me know your toughts about how can i proceed further.
Thank you
Hi,
Which is the best collation for Japanese language? Why cant we use Latin for the japanese text?
your blog is really a true guide to SQL learners, tkx for great posts :)
Hello Sir,
If we use generalized stored procedures for Insert/update/delete , will it make any issues in performance?.
Is it a good practice to use generalized procedures? what are the pros and cons of these.
Can you please answer these questions?
Deja,
What generalization SPs are you looking here ? Could you please elaborate your Qstn .
Hello Sir,
I am new to SQL, I am using SQL Server 2005. I want your help to solve my issue. I’ve a table with the following Data.
Table Name: MasterDetails
FieldName ID NAME ParentID
1 A 0
2 B 0
3 C 1
4 D 2
5 E 1
Now my query is: Need to display data as below
If ParentID is 0 >> display just NAME and ID
if ParentID is not 0 >> display NAME where ID is ParentID along with NAME (i.e row 3 has parentid 1 so it should display A-C, B-D, A-E). I hope you will get the idea how I want the data.
Krunal,
Please phrase your question in more meaningful. Things are not clear here
i have a table with an id, doctors, and vist date along with other cols. I want count visit so for the same id, doctor and date, visit count will be one. how do I do this?
select id, doctors, vist_date , count(*) from table
group by id, doctors, vist_date
doctor id patient visit date
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/1/2010 10:24
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/1/2010 10:24
Here is the table I have I want to count number of visits,
for the same id, doctor and visit date visit count =1,
how do I count and put it in the table? Thanks so much
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 11/23/2009 11:26
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 4/20/2010 17:17
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 11/23/2009 11:26
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/14/2010 15:40
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 11/23/2009 11:26
dr.smith 0011111 mr.jerry 5/26/2000 0:00 8/4/2010 0:00 GRP A No Action Taken 7/14/2010 15:40
Hi Pinal,
I am trying to create a linked server that receives data from Windows Search 4 (or Windows Desktop Search) so I can associate filename and path information from my files with my metadata in SQL Server 2005. I can link to Windows Search using VBScript using a simple script such as this:
‘To run this snippet, save it to a file and run it using cscript.exe from a command line.
‘Running the .vbs file with Windows Script Host may cause dialog boxes to open for each item returned from the index.
On Error Resume Next
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open “Provider=Search.CollatorDSO;Extended Properties=’Application=Windows’;”
objRecordSet.Open “select system.itempathdisplay from systemindex”, objConnection
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item(“System.ItemPathDisplay”)
objRecordset.MoveNext
Loop
This provides path information (in DOS) to all of my files in the index as follows:
C:\temp\myDoc.doc
C:\temp\page.html
C:\temp\sheet.xls
What I would like to do is get the same info from within SQL Server 2005 (or similar) using a linked server. In the past I have done this using Indexing Service, but that is an antiquated solution, and it seems that I should be able to link to Windows Search in a similar way.
I can run the following query in SQL Server (and the connection test is passed):
SELECT * FROM OPENQUERY(“Windows Search3″, ‘SELECT prop:System.itempathdisplay FROM SYSTEMINDEX’)
Where Windows Search3 is the name of my linked server. This will return the following error:
OLE DB provider “Search.CollatorDSO” for linked server “Windows Search3″ returned message “One or more errors occurred during processing of command.”.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query “SELECT prop:System.itempathdisplay FROM system.search.store:file” for execution against OLE DB provider “Search.CollatorDSO” for linked server “Windows Search3″.
This error seems a little further ahead (or at least different) than the error that other web posters have stated in the past:
OLE DB provider “Search.CollatorDSO” for linked server “WinSearch4″ returned message “Command was not prepared.”.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Search.CollatorDSO” for linked server “WinSearch4″ reported an error. Command was not prepared.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Search.CollatorDSO” for linked server “WinSearch4″.
Which is the result of:
SELECT * FROM OPENQUERY(“Windows Search3″, ‘SELECT System.itempathdisplay FROM SYSTEMINDEX’)
You will notice the only difference is prop: before system.itempathdisplay. I think my sql is quite close to what it should be to work, but it seems that something is missing. I think this connects somehow to one of the options in this API http://msdn.microsoft.com/en-us/library/ff684394%28v=vs.85%29.aspx , but it must be lacking something. I don’t know enough of how SQL Server repackages this info and sends it off to Windows Search, so I am now clueless. Either a SQL guru or a Windows Search master might be able to jump the last hurdle to make this thing work. It seems like there is only a small piece of the puzzle missing.
I will dump my linked server info for reference:
/****** Object: LinkedServer [Windows Search3] Script Date: 01/31/2011 17:23:36 ******/
EXEC master.dbo.sp_addlinkedserver @server = N’Windows Search3′, @srvproduct=N’Microsoft OLE DB Provider for Search’, @provider=N’Search.CollatorDSO’, @datasrc=N’SYSTEMINDEX’, @provstr=N’Application=Windows’
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’Windows Search3′,@useself=N’True’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’collation compatible’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’Windows Search3′, @optname=N’use remote collation’, @optvalue=N’true’
My only other thought is that I am having an authentication or impersonation issue, but I am using the security setting (as quoted on the Linked Server Properties page for Security):
“Be made using the login’s current security context”
Conclusion:
I know many people have been looking for this solution, and it seems that it should have been made available sooner. Any help or solution or even agreement appreciated.
hello mr pinal.
i want to some help from you.
i want to bind all sql server within my LAN are in to DropDownList.
and when i select this connection then all tabels of this connection bind to another dropdownlist.
can you pls hel me??
I want to list down all instances of SQL Servers in my window application. How can I get the list of SQL Server instances? Is there any SQL Query for this?
Hello Dave,
In one of our projects we have three different log in system users, each user has their own contact persons, for the first two users they have only one contact person and for the third user we have multiple contact persons.
For that scenario we have created three table with their own business fields, and the contacts of the first two users we are storing in the same respective tables, and for the third user we have created contacts table separately, now we want to have a single table for storing all the contacts, is it a good practice to have a single table for all the contacts for the entire system or is it ok to keep the table like as it ease.
Please give us your comments and suggestions.
Thanks,
Shalem
Hi Dave,
I have an interesting issue I have a MSSQL 2005 Express database that I want to be able to replicate a few tables to another SQL server. The reason for this is the SQL Express server handles live data and I want to setup a reporting database that users can hammer without affecting production. One of your posts went over a method to resolve this but I wanted to see if you could send me the details on how to go about this. I’m not a sql guru so I’m not sure of all the options available to me.
Thank You ahead of time Dave and e-mail when your suggesion.
Hi Pinal,
I have a query regarding OPENXML. I have seen many examples where in WITH clause of OPENXML, people use ‘@’.
For example in the following example, we can see @OrderID, @CustomerID, @OrderDate, @ProductID, @Quantity.
I am very confused about what this does???
Please let me know as i am really getting mad about finding the use of ‘@’.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =’
‘
–Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
– SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, ‘/ROOT/Customer/Order/OrderDetail’,2)
WITH (OrderID int ‘../@OrderID’,
CustomerID varchar(10) ‘../@CustomerID’,
OrderDate datetime ‘../@OrderDate’,
ProdID int ‘@ProductID’,
Qty int ‘@Quantity’)
Hi Pinal,
i forgot to include the XML string in the previous post.
I have a query regarding OPENXML. I have seen many examples where in WITH clause of OPENXML, people use ‘@’.
For example in the following example, we can see @OrderID, @CustomerID, @OrderDate, @ProductID, @Quantity.
I am very confused about what this does???
Please let me know as i am really getting mad about finding the use of ‘@’.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =’
‘
–Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
– SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, ‘/ROOT/Customer/Order/OrderDetail’,2)
WITH (OrderID int ‘../@OrderID’,
CustomerID varchar(10) ‘../@CustomerID’,
OrderDate datetime ‘../@OrderDate’,
ProdID int ‘@ProductID’,
Qty int ‘@Quantity’)
Hi Pinal
I have MS ACCESS database in One CLINET
and i want to link ms access database to SQL SERVER 2008 ok
but can i create second database in sql server that if the row modify or delete or insert into linked database in sql server in second database i see modify- but second database is copy of first database but one column additional in second database like below:
——-
MS ACCESS database >> Table_1 with A,B COLUMN
——-
MS SQL server LINKED database >> Table_1 with A,B COLUMN
——
MS SQL server Copy LINKED database>> Table_1 with A,B,C
COLUMN
———-
every change in ms access database i see in linked database
——–
i want that i see every change in linked database i see in second database but value of C column in entry data
——
how to do this thank
Do you want to keep null for the for the additional column?
yes i want to update later this column
Can you use this?
Insert into target_table(col1,col2)
select col1,col2 from source_table
hi friends
i venkat i have critical problem
i have one table consisting of 4 columns like as follows
Emp_no Date Punch_Time In_Out
1 110101 0930 p10
1 110101 0932 p20
1 110101 0935 p10
2 110101 0930 p10
2 110101 0936 p20
3 110101 0939 p10
4 110101 0930 p10
4 110101 0937 p20
same empno first time P10 and same empno second time p20
in my hand above table with lot of records with different combination. my resulting table is empno no is 2 time with p10 and p20 first punch and last punch.
like below result table
please tell the logic i am using .net (windows applications)
1 110101 0930 p10
1 110101 0935 p20
2 110101 0930 p10
2 110101 0936 p20
3 110101 0939 p10
4 110101 0930 p10
4 110101 0937 p20
Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
windows application with sql server 2005
select
*
from
northwind..orders as o
where
(select count(*) from northwind..orders where customerid=o.customerid
and orderdate>=o.orderdate)<=2
order by customerid,orderdate desc
thankyou Madhivanan
i clear my problem but some thing i am getting this =2 if i place =2 is working 1 columns correct all employees . but my problem is both first columns and second columns correct. i hope you have the solutions.
Can you post some sample data with expected result?
Hi Pinal,
I just want to know reg microsoft certification. I just want to write MCTS 70-433. Whether 70-433 is expired or what? can you please tell me if not when it will expired.
For this 70-433 is that pre requiste is required or not? Once i clear this exam i can able to get certified as MCTS or any more exams need to write. Pls put a copy to mail id eve. bcos i dotn know hw to again come n see the reply for my post.
Thanks & Regards
Shanthi
Hi Pinal,
I’m using SQL Server 2008 R2 build. When i use TIME/DATE datatype in create query it gives error:
“Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #2: Cannot find data type time.”
Query:
create table ShowDetails(show_id int,show_timing time(3));
When i try to set the compatibility level to 100
EXEC sp_dbcmptlevel movietickets, 100;
it gives error :
Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 70
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of the database compatibility level are 60, 65, 70, 80, or 90.
Please help !!
Note that time datatype is available from version 2008 only
madhivanan,
I have writtern i’m using
“SQL Server 2008 R2 build”.
Also issue is not only with TIME datatype but also with DATE datatype.
If you are on SQL 2008 R2 then use Alter Database instead of sp_dbsmptlevel. This is deprecated.
Here is the syntax:
ALTER DATABASE movietickets SET COMPATIBILITY_LEVEL = 100.
I am facing a problem in Mirroring the Database, Can you send me the procedure how to fix my problem, I am follow the bellow procedure but finaly it showing error.
First I take a Full backup of my Primary server Database after that I restore this backup in my other server with Nonrecovery Restore
Then I go to Primary Database and start the mirroring there I set the Configure Security—> Include Witness Server —> No then set the Primary server with port no and Set the Mirror Server Instance with conection credential and set the port no also
Then I am unable to understand the Service Accounts there I didn’t mention Principal and Mirror and next I finish this its process and show successfuly completed.
Then Next widow is asking me Start Mirroring and Do Not Start Mirroring——–> I press Start Mirroing and finaly I got error “An error Occured while starting mirroring. Alter Failed for Database ‘Quantum’.(Microsoft.SqlServer.Smo)”
hi…
can you please teach me if how can I import the data from excel to datagrid by using the connection of SQL…. please teach me how… I would really be needing it for my thesis…
: 4th Yr. Comsci Student
Use openrowset function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Hi,
I need your help to install SQL 2008 on windows 7 professional…can you please help.
Hi Pinal,
Jus came across one strange thing when working with SQL Server 2005 and .Net.
Well in my database table I have a column which is of type Varchar and stores Alphanumeric chars. Over here in my get stored proc when reading this column I use a case statement to return numbers according to the string. When I read this column in dotnet using SQLDatareader I can see their type as Int32 but I want it to be read as smallint. Other than doing a cast in the query do we have any other approach to read them as int16 in the datareader?
Thanks
Kris
You need to cast it to smallint in the query
Hi Dave,
Is it possible to get the date and time of a last executed query before the instance was restarted?
Thanks
Hi ,
I need to connect to azure Database to sql server 2005 with 32 bit. I have tried the Microsoft.Synchronization class to achive that . but i am not able to do that . pls help
Hello Pinal,
I am working with calculated field from virtual column. This seems very easy one but some reason I could not find answer yet.
Can you help me to resolve the following problem?
SELECT
10 AS Column1,
20 AS Column2,
Column1+Column1 AS Total,
((Total*30)/100) AS PerOfTotal
When I tried above SQL, i get the following error.
Msg 207, Level 16, State 1, Line 4
Invalid column name ‘Column1′.
Msg 207, Level 16, State 1, Line 4
Invalid column name ‘Column1′.
Msg 207, Level 16, State 1, Line 5
Invalid column name ‘Total’.
How do I solve this issue?
Thank you
Shailen
Hi, sorry for my english, but the quick answer is that you should not use the alias “Column1″ (or Column2) in the expression, so the SQL is:
SELECT
10 ,
20 ,
10 + 20 AS Total,
(((10 + 20)*30)/100) AS PerOfTotal
Shailen,
You can not directly refer alias as column names very next to its static values unless we consider columns from either resultset / inline view at present.
Well, I could provide 1 solution but i’m sure there may exist better than mine.
with cte(Column1, Column2)
as (SELECT 10 AS Column1, 20 AS Column2)
select *, ((Total*30)/100) AS PerOfTotal
from (select Column1, Column2, Column1+Column2 AS Total from cte) as new_cte
– output : 10 20 30 9
Is this what output you are looking for ?
Or else below could be the solution. Correct me if i’m wrong
SELECT
10 AS Column1,
20 AS Column2,
10 + 20 AS Total,
(((10 + 20 )*30)/100) AS PerOfTotal
Beware of inplicit convertions
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx
another one :
select Column1, Column2, Total, ((Total*30)/100) AS PerOfTotal
from (select Column1, Column2, Column1+Column2 AS Total
from (select 10 as Column1, 20 as Column2) as v1) as v2
Or… if you really want those columns name, try this dirt SQL:
SELECT Column1, Column2, Total, ((Total*30)/100) AS PerOfTotal
FROM ( SELECT Column1, Column2, Column1+Column1 AS Total
FROM ( SELECT 10 AS Column1, 20 AS Column2) as a ) as b
Hello Pinal,
I need to migrate Postgre database to SQL Server 2005. Can you suggest me available tools and proper way to do the same.
Thank You
Hello Pinal,
We have an requirement where we need to pick up sql files one by one and then review the file for microsoft best practices and log errors if any of the file is not matching the criteria.
Is there any tool availavle for the same.
Please let me know.
Hi Pinal,
Need some help understanding this concept,
I’ve a table which has around 1 lack records. It has 3 columns, ID1 , ID1, Description, with ID1 (Left most column) and ID2 forming the primary key on the table (Composite Clustered index).
When I do a simple join operation like following,
SELECT * FROM dbo.CompositeTable C1
INNER JOIN dbo.CompositeTable C2 ON C1.ID2 = C2.ID2
It takes a lot of time to execute. Around 1 min in query analyzer.
I went ahead and checked the statistics build on this table and found that, there are 2 statistics created on the table,
1. Containing ID1 on top and ID2. This one has lot of imformation stored.
2. Containing only ID2, when I click on the details of this stat, I see a message in SQL server pane — “No statistics information available.”.
What does this mean? Also, why does SQL server does not use the clustered index properly on such a join (I can see some table spool created when I check the actual execution plan)
Regards, Vinay
Hi,
It’s posible to create a table from a select union
Example:
CREATE TABLE C as
(SELECT * FROM a
WHERE …
UNION
SELECT * FROM B
WHERE …
)
SELECT * FROM A
UNION
SELECT * FROM C
Kahuna,
The above syntax is applicable only to views.
use this :
select a.* INTO C
from A a
union select b.*
from B
Note : columns type in both Table A and B should be of same type.
and also the number of columns should match
Hello Pinal,
I inherited a database that was very poorly designed. Lately the traffic to the db’s web site has gone up dramatically and all of a sudden I am plagued with lots of deadlocks. I am a programmer with some knowledge of databases but this problem is definitely over my head. The deadlocks don’t seem to be localized to any one place or type of sql. I get them with combinations of updates, inserts, and select statements on various tables.
After cleaning up a lot of statements I found out how to do a trace. Below is an example of one where there are several updates on the same table.
This database has a very high number of inserts and updates being executed. I am almost convinced that my problem is being caused by the fact that I have clustered compound primary keys all over the place. The only problem is I don’t really understand the mechanism as to how the deadlocks are occuring. I could just change the pks and see what happens but I would rather understand the why first.
So my questions are:
1. Is my assumption right that lots of inserts and updates on tables with compound pks can deadlock?
2. If my assumption is correct could you explain the mechanism at work?
Thanks,
Nat
btw- I would like to thank you for all the information you provide on your site. It’s extremely helpful. It’s the first place I go when I have sql server questions.
DEADLOCK_GRAPH
2011-02-25T11:21:17.710
15
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
unknown
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
unknown
(@P0 bit,@P1 bit,@P2 nvarchar(4000),@P3 nvarchar(4000),@P4
nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 int,@P8
nvarchar(4000),@P9 int,@P10 int,@P11 int,@P12 nvarchar(4000))UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
(@P0 bit,@P1 bit,@P2 nvarchar(4000),@P3 nvarchar(4000),@P4
nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 int,@P8
nvarchar(4000),@P9 int,@P10 int,@P11 int,@P12 nvarchar(4000))UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
UPDATE
MemberInfo SET
ChargeOff=@P0,Delinquent=@P1,MemberAddress=@P2,MemberClass=@P3,MemberName=@P4,MemberNumber=@P5,PricingGroup=@P6,Relationship=@P7
WHERE AppCode = @P8 AND IdNumber = @P9 AND Family = @P10 AND Sequence = @P11
AND Source = @P12
xxx
2011-02-25T11:21:17.710
Server_name
AQ==
23345777
1
Dear Pinalkumar
I’ve got a column in a SQL Server table that is type XML. I want to change multiple values in the XML in a stored proc. I’ve already found the XML.Modify(…) command and have it working for a single value. Can I change multiple values in the XML in one update command or do I need to do it as multuple update commands?
Is it possible to do this or should i have to use invidule update command for each column.
Regards Faisal
I am experiencing difficulties using a Stored Procedure with w/multiple parameters and accessing the results using the ReportViewer (local Mode) control in an ASP based website. I am not sure if you deal with this at all but possibly you could point me in the direction of someone who does know about using the reportviewer? I have been searching the web for samples and help on the forums but have had no luck. I am willing to pay for services but not sure where to turn?
The problem is not in the SP as I get the correct results when running in SQL it is in the setup between SQL and reports. I dont want to clog up your comments section with details if you are unable to assist. I just thought I woiuld ask because I have learned much from your blog post.
Thanks
Can you explain what you have tried and what difficulty you faced?
I have found that since I am working with a local report that I am better off creating the dataset from the stored Procedure having had passed the SP the required parameters and then setting the report data set to the dataset I created fro the SP.
Which then works great and displays the report as expected.
What I do not understand now is how to create the report without dragging the TableAdapter control onto the form and populating it via the wizard which will add the actual fields returned from the SP into my available data objects and assigning them to the report fields so they bind correctly. Once that is completed I wam wondering if I can then delete the Physical table adapter from the form and still have it work from the dataset created in code?
Pinal,
I am getting same error as Neel,
Error: 18456, Severity: 14, State: 38.
Login failed for user ‘MOSS\PRD_MOSSAdmin’. Reason: Failed to open the explicitly specified database. [CLIENT: xx.x.x.xxx]
Tried every single solution available (such as, providing access to this user to master db, checked all SQL Jobs, checked all web.config, can open Management studio and see all db using this user, etc…). Nothing works.
Can you please porovide your opnion?
Thanks,
Gopal
Just wanted to say thank you. I have been doing a lot of searches for advanced SQL abilities and around half the time this is where I find the answer to my questions with great examples that cleanly and simply show how to use them.
I need to upgrade the following components of sqlserver 2005 express edition using commmand : UPGRADE=Client_Components,Connectivity,SQL_Tools90,SQLXML
When i upgrade the above using SQL Server 2005 standard edition CD, will it effect the other services also?
Please suggest
Greetings!
I wonder on stange behaviour of SQL Server Transaction LOG.
The following script makes transaction log growing and finally ends with 9002. I understand it. There is one VERY LONG tansaction:
USE [master]
GO
CREATE DATABASE [testw] ON PRIMARY
( NAME = N’testw’, FILENAME = N’C:\temp\testw.mdf’ , SIZE = 4096KB , MAXSIZE = unlimited, FILEGROWTH = 4096KB )
LOG ON
( NAME = N’testw_log’, FILENAME = N’C:\temp2\testw_log.ldf’ , SIZE = 512KB , MAXSIZE = 512KB , FILEGROWTH = 10%)
GO
USE testw
go
create table test
(txt nchar(4000));
go
DECLARE @i int;
SELECT @i=1;
BEGIN TRANSACTION
while(1=1)
begin
INSERT INTO test VALUES(CAST(@i as NCHAR(4000)));
select @i=@i+1;
end
END TRANSACTION
But when I change the location of BEGIN/END TRANSACTION (inside while), so I have many small transactions, the log does not grow! The recovery model is full. Why one big transaction takes much place in transaction log and many small transactions not?
DECLARE @i int;
SELECT @i=1;
while(1=1)
begin
begin transaction
INSERT INTO test VALUES(CAST(@i as NCHAR(4000)));
select @i=@i+1;
end transaction
end
Please help,
Hello,
I need some help regarding tuning.. I am unable to solve one mystry….
Using profiler i discovered that same query performing almost similar number of reads and writes is taking longer and longer to execute..i.e. the duration is increased every time i execute the query given the total number of records in table are same
I am sure you know why this is happening… i will be very glad if you share this knowledge with me
Thanks
Zeeshan,
Please help us in providing query to understand better the performance.
DELETE FROM [CO SRC Job Table]
WHERE [Company RESOURCE] = @Resource
AND [Interval] = @Period
It was taking 10 ms and now it is taking around 15 ms
I changed the index from clustered to non clustered which increased the duration further therefore i changed the type back to clustered
Here i must mention that the time taken to execute had increased more but this morning when i checked, it got executed taking 15 ms
Another query is
UPDATE [CO SRC Job Table]
SET [Days per Month]=B.[Days per Month],
[Hours per Month]=B.[Hours per Month],
[Hours per Day]=B.[Hours per Day],
[Days per Week]=B.[Days per Week],
[Weeks per Month]=B.[Weeks per Month],
[Rate of Pay]=B.[Rate of Pay],
[Rate frequency]=B.[Rate Frequency]
FROM [PER REM Payrate] B
WHERE [CO SRC Job Table].[Interval] = @Period
AND [CO SRC Job Table].[Employee] = B.[Employee]
AND B.[Start date] = [CO SRC Job Table].[Period End]
AND [CO SRC Job Table].[Company Resource] = @Resource
48 ms with 87 writes …. but the strange thing is that later it took 49 ms and the number of writes were only 24…(Reads were same)
Other queries are longer and more complex, if this does not help, then i will try to provide those as well..
Thanks alot for response and your time
Zeeshan,
I dont believe, changing index from clustered to non-clustered or vice versa (as you said above) would solve the issue.
What I suggest is to check the Index page is Fragmented!!
Query :
select avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(), — your database id
object_id(”) — your tablename
, null, null, ‘detailed’)
Check what is value of it. If its between 10-30%, you got to re-organize. Else if more than 30%, Rebuild indexes…
I have installed SQL Server 2000 Enterprise edition with service pack 4 instance on Windows server 20008 R@ Data Center OS.After installing SQL 2000 i have renamed machine.
But After renaming machine name i couldnot able to connect that instance remotely.
but when i try machine name with port number i m able to connect.
I did not understand what is the problem.
Please, provide me some solution .
Thanks in Advance
Hi Pinal,
I am just starting to learn MS SQL Server, previously I was working with Teradata. I guess there is no options to see the SQL execution history in SQL Server..rite?? And I can’t retain my result window unclosed for all the queries am executing..rite?? I am confused because all these options were there in Teradata..Please correct me if am wrong..
Thanks,
Nithya
Make use of profiler
Hi Pinal
I facing a problem with large transaction-log , we are using temp-db , is there is any way we can avoid transaction log , because it is filling temp-db , and I can not make recovery mode to simple .
any advice
thanks
Hemanshu
I facing a problem with large transaction-log , we are using temp-table , is there is any way we can avoid transaction log , because it is filling temp-db , and I can not make recovery mode to simple
Hemanshu
Hi,
I have 2 different environment (INT and UAT).
Today I write a small function to stress test the connections to UAT database.
The C# function will loop a class method to get data from branch and the staffs for all the branches.
There is about 550 branches and each branch have about 3-10 staffs.
Result is, both UAT and INT instance totally mess up and down. It prompt me this error.
Logon failed for login ‘CRM’ due to trigger execution.
Changed database context to ‘CRM_Database’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
I have asked the server team to help on this since I don’t have enough permission to investigate.
But they have come back and said there is no way to recover the instance and they have to reinstall the whole instance.
According to the server team, the faulty is cause by my stress test (too stress of making connection to database).
I got 2 questions.
1. I only connect to UAT instance, how come INT instance also crash?
2. Are there really no way to recover the instance?
I done some research, I know is need to drop the trigger Tr_ServerLogon. But I tried that in my local machine, It said cannot find the object.
I’m not sure whether the server team did that but I believe they can find this suggestion from internet.
I really wish to know is there any solution to recover this issue.
hi sir,
can you help me to install sql server jdbc connector i downloaded but don’t know how can i use it,
thanks
soufya,
Anyone can sort this? (using: SQL/My SQL/Oracle)
CategoryID ParentCategoryID Category
1 0 Flower
2 1 Jasmine
3 1 Rose
4 1 Orchid
5 0 Animal
6 5 Cat
7 5 Dog
8 6 Tiger
9 3 Red Rose
10 3 White Rose
11 9 Small Red Rose
My final answer should be like this:
CategoryID ParentCategoryID Category
1 0 Flower
2 1 Jasmine
4 1 Orchid
3 1 Rose
10 3 White Rose
9 3 Red Rose
11 9 Small Red Rose
5 0 Animal
7 5 Dog
6 5 Cat
8 6 Tiger
What is the logic for this sorting?
Hello sir,
I just wanted to tell you that I love your website.
It contains many useful advices on SQL SERVER.
Keep up the good work.
Have a wonderful day,
Roni Vered.
Hi Pinal
I m using sql server 2005 & I faced a problem while Bulk Insert is occured.
error as below
Fatal error 3624 occurred at Mar 16 2011 3:58PM. Note the error and time, and contact your system administrator. A severe error occurred on the current command. The results, if any, should be discarded. Location: lckmgr.cpp:10846 Expression: GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition () SPID: 75 Process ID: 1824
plss help..
regards
Ankur
Dear Pinal ;
I am planning to do Logshipping on sql server 2000 and 2008 servers , SAP is the application running for this DB.
I have already restored the standby Database , didnt choose the NON Recovery mode.
Is there any option to change the mode to Non recovery?
Thanks in advance.
Regards
SAM
HI
I have an interesting scenario which has happened with one of my clients.
The issue is that a table “dissapeared” – i say this because there is no log in the log file which shows a DROP TABLE on this table. And from what i have found, in trying to recreate this table from a create statement which i generated from the backup, it complained that the Primary key was still there. I tried to drop the PK using alter table Drop constraint it said couldn’t find table.
My biggest question is that how is it possible for a table to be removed without the PK and FK’s being removed with the table?
My understanding of relational databases is that PK’s are “related” to tables – and should be removed when a table is removed.
I have checked google and i dont see any issue like this before.
Your Quick response would be most appreciated – as i am completely dumbfounded by this.
Hola Dave:
Tengo el siguiente escenario:
Servidor Windows 2003 con MS-SQL 2008, actualmente migrado de MS-Sql 7.
En la actualidad estamos teniendo problemas graves en nuestras aplicaciones, ya que depende de la configuracion regional de cada PC. En aquellos PC cuya Configuracion Regional es English(United States) los datos se nos convierten mal ya que los separadores de coma decimal (coma) y separador de miles(punto) son diferentes a la mayoria de los PC que tienen (punto) y (coma) en los cuales nuestras aplicaciones funcionan bien, es decir los datos numericos se presentan y se graban en las tablas correctamente, no sucediendo el caso de English donde da overflow de estos campos cunado los numero son muy grandes!!!!
Alguna idea de lo que esta sucediendo?
Tiene importancia la intercalacion del SQL que tiene el servidor y la intercalacion SQL del PC cliente con configuracion regional English?
Aprecio mucho toda la ayuda que me pueda dar en este sentido.
Saludos
Ing Miguel Rivero
Hi Pinal,
I am a regular at your blog and they are very informative and interesting. I had a question, is there a direct and simple way to implement Fuzzy grouping and fuzzy lookup in SQL Server without using SSIS? If yes could you please guide me.
sir i am downloading data from BioMetric Device
date format is mm/dd/yyyy and time format is h:mm:ss tt
first i taken data table it is coming exactly after that i am inserting bulk data to Database sql server one table. but it is not showing exactformat wat ever is there in DataTable.
it is not showing AM or PM. it showing DataTable but not showing AM or PM in SQl server table.
before it showing correct after one week is not showing for this reason i am unable to transfer sql server table to one more table it showing System.Outof Memory Exceptions. if i give manually it is not showing this error .wat can i do to solve this error.
Hi Pinal,
Congratulations on doing spectacularly well!
Your blog has helped me several times during the course of my work & I find the contents to the point, easy to understand & interestingly simple to follow……
I parallely work on 3 flavors of DBMSs – SQL Server 2005 / 2008, Oracle 10g & DB2 9.5 & my work area consists of ETL, SQL Tuning, Data Modelling & Database Design….
I would really like to know your recommendations on the best resources / books for the aforesaid topics……
It would be great if you could mention your favorites too…
Dear Dave:
Thank you for sharing your knowledge.
I develop hardware/firmware architectures/devices.
I have a client who needs that a device that I made, makes a SQL query on TCP to his database.
I program my device in C ¿from where I can lear the sintaxis to make (hand made) a packet with a query to a SQL server, that he understand it, and answer it?
Thank you very very much
Sergio
Hi Pinal,
I have read many of your articles and has helped a lot.
Can you please brief me as to if it’s possible to import the following data into SQl Server and the best way to go ahead with it. A sample of the data is given below:
–Columns
Column1
Column2
Column3
Column4
Column5
–Data
XYZ
999999
ABC
9999999
2011-03-25 06:01:00.0
DEF
888888
GHI
8888888
2011-03-24 06:01:41.0
Thanks for your help.
PRMP
Refer thi post
http://beyondrelational.com/blogs/madhivanan/archive/2009/05/05/import-from-text-file-single-column-to-multiple-columns.aspx
I used the code below to move the Model database and Model log file to new locations.
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_path’ )
After running the commands I stopped the SQL server and moved the physical files to the correct location.
Now I am unable to start SQL server, I get error
I think the problem maybe that for FILENAME=new_path I put “J:\SQL Server\MSSQL.1\MSSQL\Data”
but did not include model.mdf and modellog.ldf in the file path.
Now I couldnt start my database engine.. How to overcome from this prob??
Hi Pinal,
I would like to know wether we can update all the column in a table using Update statement….
Can you suggest me what type of query we can use to update all the column of a table….
Note: the table is created dynamically….
Hope you will respond to my query….
Thanks in advance….
Thanks & Regards
Prithviraj MK
One option is to use a dynamic sql. But why is the table created dynamically?
Hi Pinal,
Thanks for your session at teched 2011 .
I am having question .We have hosted 500 databases in a single instance.
Now ,I have problem to identify the databases that are being not used.
Its difficult to find these databases which are not used.Can you let me
know whether any good soultion for this.
Thanks
Srinivas
If you want to find it make offline eash database and run the application. There wont be a problem if it not used by any application. Other method is to run a trace and see if any database is not used
may i know in which format we should store age in terms of year and month
1 Store number of months
2 Store year and month seperately
Hi,
Am completely new to Database Technology. I have a problem, I need to convert a database script that i have been using to create my database in SQL 2000. The script includes all the Views, Stored Procedure and User roles and permissions.
I need to convert this script so I can run it on SQL 2008 and create a table for use on it.
Sample:
CREATE TABLE dbo.tbl_users (
id bigint IDENTITY (1, 1) NOT NULL ,
column… nchar (50) NOT NULL ,
column… char (50) NOT NULL ,
column… NOT NULL ,
column… NOT NULL ,
) ON PRIMARY
GO
ALTER TABLE dbo.tbl_users WITH NOCHECK ADD
CONSTRAINT IX_tbl_users UNIQUE CLUSTERED
(
email
) WITH FILLFACTOR = 90 ON PRIMARY
GO
Please help!
hi,
i have read article on self joins already.
i am engineering student. i am working on project for navigation purpose for academics. i have table for routes available and cities and their respective locations. now i have one problem regarding selfjoins in one table. i have a table which has three fields
ROUTE_ID CITY_ID STOP_NO
1 3 0
1 4 1
1 7 2
2 7 0
2 4 1
2 3 2
like above table there is listing of route stops within a route. the above table have one route in both directions i.e. target city for routeid 1 is source city for routeid 2 and vice versa . this is how i have saved the data.
I am giving two city ids as input parameters for source (from) to target(to) and want to know the routeid which contains these cities. i am able to find the route using self join, but the problem is the the source city should lower stopno from the target city stopno.
so currently if i search for routeid from cityid 4 to 7 i get routeid 1 and 2 as result. my requirement is it should give me routeid 1 because cityid 4 (stopno is 1)is earlier to cityid 7(stopno is 2) .
[email removed]
Thanks in advance.
Hi,
I am a regular reader of ur blogs and they are awesome.I am having an issue if you can help me plz.
I have a DB in SQL 2000 which is being replicated,the issue is that one of the Table have a ‘TEXT’ field.during Transactional Replication,the Agent stops many times,i have increased the size ‘max text repl size’ ,its gone better but still its an issue,is there any other way i can replicate the ‘Text’ field.
Thanks
Where are you replicating it? If you replicate it to versions from 2005, use varchar(max) datatype
No,I am replicating it on sql 2000.I read on MSDN that we can replicate Logged Test by using WRITETEXT AND UPDATETEXT.Not sure how to do that.?
Hello,
I want to configure reporting service 2008 can you tell the steps to configure it. we met you when you come to NIC,Gandhinagar i attend seminer on silver light. we are facing the Credential problem using it.
Thanking you,
Chintan
Hi Pinal,
Could you please help with the below issue.
If suppose I have 2 flatfiles:
\\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER
\\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 INVOICE_ORDER
Archive folder is \\ODCDEV01\LOAD\Archive
I get these flatfiles daily..monday through friday. On Monday I get previous friday’s file. On Tuesday I get Monday’s file to process.The date on filename i.e (2011-03-30-22-00-01) gets this way.( Night batch runs and the max load date is loaded in a sql table and that date is included in the filename by the application team..)
I am creating two packages.
One for loading the below flatfile
\\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER
second package for loading below flat file
\\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 INVOICE_ORDER
Package logic I use is (same logic for two packages)
1.implementing business logic to select the row inserted by the application team to get the load date.
2. using sequence container
a. In that I am using For Each loop . I am trying to create 2 variables. One to hold the archive folder that I could do (\\ODCDEV01\LOAD\Archive). Secong variable is to hold the “\\ODCDEV01\LOAD\SCH 2011-03-30-22-00-01 PURCHASE_ORDER”. As the date in this path changes dynamically I have to capture this and assign to a variable. I am unable to write the expression and use this variable in the for each loop container. I am not sure what datatype I have to give while declaring this variable as it has date and string data in the filename.
Inside the foreach loop, I am giving dataflow task to load data from flatfile to OLEDB destination. Here again in the flatfile connection string , I dont know how to write the expression.
After dataflow task I am using File System Task to move flatfile to archive folder. Here again I am unable to give the source connection. Destination connection I could give in the expression.
So my question is how to write the expression to declare the source variable? Please let me know if you have any questions? Thanks a lot for your time and help.
Thanks,
Naveen.
Hi,
I have a group in AD that logins to windows and what i want to do is, the same AD group to access also SQL Server using the AD credentials.
my sql server is setup to use mixed mode (windows and sql server login). so lets say my AD group name is :
fxb-dss-fsx-sqladmin,
this group have(2 user) access to win 2008(OS) only. Same group have to access SSMS also.
so i want to give access to the above group to use also SQL Server.
OS: Win 2008
SQL: sql 2008
I have around 50 Sql Server and more then 100 Instance, NO 3rd party tools we used here.
can anybody guide me how to do this?
Thanks.
New DB
I’m trying to create a Database in Microsoft SQL Server 2008 R2. I can sign in under Windows Authentication a set up a database with Login & Password. Everything goes fine until I goto login in to my newly created database. It say ‘login in fail error message 18456′ when I go back to look at my login & password that I created, the password is not the same. It’s twice as long and I don’t know what the password is because it’s all dots. I trying to change it but it never works. Can you help me figure this out? Thanks.
Hello Sir,
I am Student. I have Two table
tblEmp
Emp.ID|Name|Salary|Dep.ID
1 abc 100 1
2 xyz 200 2
3 pqr 300 1
tblDep
Dep.ID|Name
1 D1
2 D2
Now Update Salary 10% where Dep Name is ‘D1′ and 20% where Dep Name is ‘D2′ in Single Query use join.
Hi Mr Pinal,
I was wondering if you could shed some light on a problem that I’m having. I have a SQL Server 2008 installed on a remote desktop. One of my team mates is having trouble accessing it by the server name when he tries to remotely access the Server via SQL Management Studio. I however can connect to it using the IP and the server name. I’ve tried all the steps you indicated on one of your guides. I tried to detect all available servers on the local network but nothing shows up. I’m stuck right now and I was hoping you could shed some light on this. Thank you very much.
Regards,
Adrian
hello sir
when u are free and feeling cool then please write an article about how to debug a query or store procedure in sql server 2008 R2
i am trying many times but failed to enable the debug in sql server R2
so i make a kind request to you, to write a step by step article that how we can enable debugging in sql server 2008 R2 and then how to debug a query or stored procedure
and how to check the query performance and some another hows that is fruit full for me and another sql guys
i have a requirement like if i give the table name i need to generate Script for that existing table with constraints also please help me how to get create table script with constraints of that existing table ….
Hi Pinal,
Can you give me tsql script for dynamically comparing tables in two different database with same name and i want to return the varying columns in database.
Regards
Rahul Trehan
For quick immediate answer leave a comment at my Facebook page and let me know at my twitter ID
Hi Pinal,
i am a Junier SQL developer. Now my job is finding the missing indexes in our live database. When i try to find the missing indexes from the database by using DMV’s it does’t allow me to run this queries. Then i explain the situation to my manager then he said,generate a script for this live database and load it into local database server and text it. i did the same thing what he said, But the problem is when i execute the query for missing indexes it doesn’t show any indexes in this database.
Is it possible to find the missing indexes on local database server after script it from the remote desktop?
please help me
thank you,