hi, good day,
when i run query in query analyzer
exec sp_test '2006-07-21'
it show result , however when i run it using BCP tools
exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '
it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure
how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,
thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way
CREATE PROCEDURE [SP_TEST]
--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B
SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D
----------BEGINNING OF PROCESS------
SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2
)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well
CREATE PROCEDURE [SP_TEST]
AS
SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER
----------------------
SELECT DISTINCT
COUNT(CUST_CD)
FROM
#TMP_CUSTOMER
GO
when i run in query analyzer , using "exec SP_TEST" , it work
but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
do u have any idea on it ?|||I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.
I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.
Wait until Monday, and lets see if any other forum members recognize this error.|||Hi
Did a quick RTFM:
Copying Data To or From a Temporary Table
When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".
HTH
EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.|||thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,
This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.
bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:
SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF
If you use the "queryout" operator with an exec statement, it issues the following command:
SET FMTONLY ON
exec ...
SET FMTONLY OFF
Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.
FYI, a solution with a table variable WILL work, e.g.
<code>
create proc dbo.bills_test
as
begin
declare @.output table (col1... , col2 ... , ...)
insert @.output
select ...
select col1, col2 ...
from @.output
end
<\code>
you can play with this in query analyser, to get a stored proc that will work with bcp...
HTH,
Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work
will tempdb object will causing any performance issue , i really not sure ..
:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.
Oh, for the love of god....
In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO
CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders
SELECT * FROM @.myTabVar99
GO
EXEC mySproc99
exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'
DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Thursday, March 29, 2012
Can we have a folder as input path for SSIS package, and process all files within it?
Can we have a folder as input path for SSIS package, and process all files within it?
My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?
Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1
Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.
Regards,
MS Guy
MS Guy wrote:
Can we have a folder as input path for SSIS package, and process all files within it? My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?
Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.
Regards,
MS Guy
There's a few things that make the answer "It depends" but there's a few things you should look at:
1) MULTIFLATFILE connection manager. This connection manager points to a colelction of files (usually all files in a folder) and you can then apply a single operation (such as loading the contents of all those files into a table) in one step. Usually the metadata of the files needs to be the same - it depends on what operation you're applying.
2) Use a Foreach loop container to loop over the files and process them individually. There's a bit more guidance on this here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx plus Darren and Allan have some good stuff on this at www.sqlis.com.
-Jamie
Sunday, March 25, 2012
Can views be used in query builder?
I tried it but got an invalid object error. I then copied and pasted the sql statement contained in the view but it doesn't run correctly inside query builder and only shows 1 valid row, and a 2nd row that's blank the the first column and has a number 1 in the second column. The view runs correctly in SQL Server Management Studio Express.
How about wrapping the view inside a stored procedure?
|||Stored procedures can't be used in query builder either. All I can do is put in a dummy SQL statement (like Select 1 as column1, 2 as whatever) and then change it to a stored procedure later when I have access to the "data" tab in [Design].
That doesn't seem right but is the only way I can figure out how to do it.
Thursday, March 22, 2012
Can V run DTS from an remote machine?
Hi,
i have developed an web-database enabled application, wherein the admin will be importing the data from a remote machine to the server.
Is it possible to run the DTS remotely?
Also, is there need to install the sqlserver in the remote machine on which the admin is working ? i mean to say, other than server, do i need to install sqlserver on the remote machine too..
Thanx in advance
It looks as if solution of problem is not clear to you also or you are moving in wrong direction .
wherein the admin will be importing the data from a remote machine to the server.
With the help of DTS it is not possible.
Possible solution can be uploading the data to server through webapplication and then calling DTS through stored procedures in webapplication.
Other solution can be uploading files using DTS FTP task and then performing transfromation operations.
|||Hi,
thanx for the reply. i will try it out|||Hi Bhatia,
hmm, i think, the way i have put my question sounds confusion.
Lemme clearly explain u what the prob is .
I have developed an webenabled application using ASP.NET,C#,SQL server 2000. The application is all about having students database-all the universities.
Here, the admin will have the privilege to upload students data onto the database and all other users can only retrieve it.
I want to design the application such a way that, admin can upload the data onto the server remotely, mean to say, he can do that, signing in from any remote machine.
While uploading, admin should access the datatables of the server, import the data from the current machine(remote machin thru which hes signed in) onto the server.
How can i design this...hmm, how can i call the DTS of the server, signing in from the remote machine.
Hope this explains better..
|||Hello Kiran,
You must first upload the student data to the server through web application.
For uploading tutorial visit this link
http://www.aspheute.com/english/20000802.asp?PrinterFriendly=True
After the file is uploaded to server then only you can transform file to the server.
you have to create DTS package for transformation and stored procedure for calling DTS package.
1.Create a new package
Right click on package window > Package properties
-Set Global Variables
SourceText String
DestTable String
-Now Drag and drop Text file (Source) and Microsoft OLE DB Provide for SQL Server(Destination).
Connect them with Transform data task.(Add dummy data in text file and SQL Server Table to check it is working fine).
-Now add Dynamic Properties Task from Task.
Edit it.
Now set property
1.Connections > Text File(Source) > Data Source
Set Global Variables (SourceText variable)
2.Tasks > DTS Tasks > DestinationObjectName
Set Global Variables (DestTable variable)
Your workflow will set in this way
Dynamic Properties Task --->On Success--> TextFile(Source)-->Data Transfrom Task-->Microsoft OLE DB Providefor SQL Server(Destination).
Step2. Create stored procedure that will call this DTS package from web application.
CREATE Procedure DtsTransform
@.ServerName nvarchar(30),
@.UserName nvarchar(30),
@.Password nvarchar(30),
@.DtsName nvarchar(250),
@.SourceFile nvarchar(200),
@.DestTable nvarchar(200)
AS
DECLARE @.ERROR int -- For Hold Error Number
DECLARE @.CMD varchar(1000) -- Dts Run Command
DECLARE @.DtsPassword varchar(30)
BEGIN
SET @.ERROR = 0
SET @.CMD = 'dtsrun /S '+@.ServerName+' /U '+@.UserName+' /P'+@.Password+' /N '+@.DtsName +' /A SourceText:8='+ @.SourceFile +'/A DestTable:8='+ @.DestTable
EXECUTE @.ERROR = master..xp_cmdshell @.CMD
SELECT @.ERROR = COALESCE( NULLIF ( @.ERROR, 0 ), @.@.ERROR )
END
RETURN @.ERROR
GO
Hope this will solve your problem.
|||That is not correct a DTS package running with SQL Server Agent populates online back with deposits collected from AS400. The job runs for about three hours. I ran Profiler on it and watched SQL Server Agent deposit 50 transactions every three to five seconds. Search this forum I helped some one less than two months ago. If you cannot do it does not mean it is not possible. In the 1990s I started posting the need to install SQL Server Agent with service account for Replication, today click on the Replication Wizard and you will get a message telling you to do so. Technology is there for you to explore and improve. Hope this helps.|||Hi Bhatia,
well i am very new to DTS, and i dint get that DTS package transformation. I do know how to upload the file though.
can u plz explain me whats the DTS package for and how to create that?
thanks in advance
|||Hi,
i am totally confused with this. What r u trying to explain?
Thanks inadvance|||Hello Kiran,
You cannot develop all this in one flow.
Start with uploading .
See if you are able to upload student data or not .
After this shift to DTS part.( for transformation of text file to Sql server database).
i have developed an web-database enabled application, wherein the admin will be importing the data from a remote machine to the server.
Is it possible to run the DTS remotely?
Also, is there need to install the sqlserver in the remote machine on which the admin is working ? i mean to say, other than server, do i need to install sqlserver on the remote machine too..
Thanx in advance
If you are on the same Network you register the server and it becomes local to you and if needed you remote into the server and do anything it feels local. To use DTS remotely you need SQL Server Agent installed with a service account. Try the links below for configurations. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
|||Hello Kiran,It looks as if solution of problem is not clear to you also or you are moving in wrong direction .
wherein the admin will be importing the data from a remote machine to the server.
With the help of DTS it is not possible.
Possible solution can be uploading the data to server through webapplication and then calling DTS through stored procedures in webapplication.
Other solution can be uploading files using DTS FTP task and then performing transfromation operations.
|||Hi,
thanx for the reply. i will try it out|||Hi Bhatia,
hmm, i think, the way i have put my question sounds confusion.
Lemme clearly explain u what the prob is .
I have developed an webenabled application using ASP.NET,C#,SQL server 2000. The application is all about having students database-all the universities.
Here, the admin will have the privilege to upload students data onto the database and all other users can only retrieve it.
I want to design the application such a way that, admin can upload the data onto the server remotely, mean to say, he can do that, signing in from any remote machine.
While uploading, admin should access the datatables of the server, import the data from the current machine(remote machin thru which hes signed in) onto the server.
How can i design this...hmm, how can i call the DTS of the server, signing in from the remote machine.
Hope this explains better..
|||Hello Kiran,
You must first upload the student data to the server through web application.
For uploading tutorial visit this link
http://www.aspheute.com/english/20000802.asp?PrinterFriendly=True
After the file is uploaded to server then only you can transform file to the server.
you have to create DTS package for transformation and stored procedure for calling DTS package.
1.Create a new package
Right click on package window > Package properties
-Set Global Variables
SourceText String
DestTable String
-Now Drag and drop Text file (Source) and Microsoft OLE DB Provide for SQL Server(Destination).
Connect them with Transform data task.(Add dummy data in text file and SQL Server Table to check it is working fine).
-Now add Dynamic Properties Task from Task.
Edit it.
Now set property
1.Connections > Text File(Source) > Data Source
Set Global Variables (SourceText variable)
2.Tasks > DTS Tasks > DestinationObjectName
Set Global Variables (DestTable variable)
Your workflow will set in this way
Dynamic Properties Task --->On Success--> TextFile(Source)-->Data Transfrom Task-->Microsoft OLE DB Providefor SQL Server(Destination).
Step2. Create stored procedure that will call this DTS package from web application.
CREATE Procedure DtsTransform
@.ServerName nvarchar(30),
@.UserName nvarchar(30),
@.Password nvarchar(30),
@.DtsName nvarchar(250),
@.SourceFile nvarchar(200),
@.DestTable nvarchar(200)
AS
DECLARE @.ERROR int -- For Hold Error Number
DECLARE @.CMD varchar(1000) -- Dts Run Command
DECLARE @.DtsPassword varchar(30)
BEGIN
SET @.ERROR = 0
SET @.CMD = 'dtsrun /S '+@.ServerName+' /U '+@.UserName+' /P'+@.Password+' /N '+@.DtsName +' /A SourceText:8='+ @.SourceFile +'/A DestTable:8='+ @.DestTable
EXECUTE @.ERROR = master..xp_cmdshell @.CMD
SELECT @.ERROR = COALESCE( NULLIF ( @.ERROR, 0 ), @.@.ERROR )
END
RETURN @.ERROR
GO
Hope this will solve your problem.
|||That is not correct a DTS package running with SQL Server Agent populates online back with deposits collected from AS400. The job runs for about three hours. I ran Profiler on it and watched SQL Server Agent deposit 50 transactions every three to five seconds. Search this forum I helped some one less than two months ago. If you cannot do it does not mean it is not possible. In the 1990s I started posting the need to install SQL Server Agent with service account for Replication, today click on the Replication Wizard and you will get a message telling you to do so. Technology is there for you to explore and improve. Hope this helps.|||Hi Bhatia,
well i am very new to DTS, and i dint get that DTS package transformation. I do know how to upload the file though.
can u plz explain me whats the DTS package for and how to create that?
thanks in advance
|||Hi,
i am totally confused with this. What r u trying to explain?
Thanks inadvance|||Hello Kiran,
You cannot develop all this in one flow.
Start with uploading .
See if you are able to upload student data or not .
After this shift to DTS part.( for transformation of text file to Sql server database).
Can Update Statistics with fullscan cause contention problems?
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default...b;en-us;195565
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default...b;en-us;195565
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
Can Update Statistics with fullscan cause contention problems?
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;195565
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!Update stats will not cause blocking, but it can be CPU intensive. For more
info on what type of locks are employed during statistics updation, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;195565
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:Os3tkWcZEHA.3988@.tk2msftngp13.phx.gbl...
I would like to run Update Stats with fullscan on our transactional database
but I want to make sure that there will be no issues with concurrency while
this is running. I realize that the elapsed time will be much higher but I
just want to ensure that this doesn't take some locks that will cause issues
with concurrency.
Any comments would be greatly appreciated.
Thanks!
Can Triggers inititate an SSIS package run?
All:
I have a need to initiate an SSIS package based upon additions/changes that would be made to a db table. Can this be done and if so how?
Thanks!
Sure. Use xp_cmdshell to call dtexec.exe. Or, set up a SQLAgent job and call it using sp_start_job.
-Jamie
|||Thanks Jamie! I have never worked with Triggers before but will give your solution a shot.
Begin: (Stupid Question)
Where do I find sp_start_job
End;
|||Found it in msdb database. Thanks!Tuesday, March 20, 2012
Can This Query Be Improved?
The query displayed below currently takes approximately 5-6 seconds to run in SQL Query Analyzer. It returns 685 rows. In my opinion, 5 seconds seems way to long, so I am wondering if there is a way to optimize this query. Does anyone have suggestions on what I could do to improve the performance of this query?
DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'
SET NOCOUNT ON
SELECT DISTINCT
SalesLead.SalesLeadID,
SalesLead.Prefix,
SalesLead.FirstName,
SalesLead.LastName,
SalesLead.Email,
SalesLead.Phone,
SalesLead.LastContact,
Schools.SchoolID,
Schools.SchoolName,
Schools.City AS 'SchoolCity'
FROM SalesLead
INNER JOIN jnSalesLeadSchool
ON SalesLead.SalesLeadID = jnSalesLeadSchool.SalesLeadID
INNER JOIN Schools
ON jnSalesLeadSchool.SchoolID = Schools.SchoolID
LEFT OUTER JOIN jnSalesLeadDepartment
ON SalesLead.SalesLeadID = jnSalesLeadDepartment.SalesLeadID
LEFT OUTER JOIN Department
ON jnSalesLeadDepartment.DepartmentID = Department.DepartmentID
LEFT OUTER JOIN jnSalesLeadOpportunity
ON SalesLead.SalesLeadID = jnSalesLeadOpportunity.SalesLeadID
LEFT OUTER JOIN AdoptionOpportunity
ON jnSalesLeadOpportunity.OpportunityID = AdoptionOpportunity.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames
ON AdoptionOpportunity.CourseNameID = CourseNames.CourseNameID
LEFT OUTER JOIN SalesLeadNotes
ON SalesLead.SalesLeadID = SalesLeadNotes.SalesLeadID
WHERE
SalesLead.Active = 1
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Address1 LIKE '%' + @.SearchTerm + '%'
OR SalesLead.City LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Email LIKE '%' + @.SearchTerm + '%'
OR SalesLeadNotes.Note LIKE '%' + @.SearchTerm + '%'
OR Schools.SchoolName + ' - ' + Schools.City LIKE '%' + @.SearchTerm + '%'
OR Department.Name LIKE '%' + @.SearchTerm + '%'
OR CourseNames.CourseName LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Term LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Chances LIKE '%' + @.SearchTerm + '%'
)
ORDER BY SalesLead.LastName
Thanks in advance!
AaronPerhaps the execution plan has a hint. How much has the distinct? Don't know how expensive it really is. It might be an option to rewrite the whole thing to match the exact search-type so there's no OR-left.
Whats the rowcount without the where-clause?|||Creating an index on Saleslead.Active might help a bit. Enabling full text indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_74oj.asp) will help a lot, but at a significant cost in disk space and INSERT/UPDATE performance.
-PatP|||According to the execution plan, the DISTINCT is costing 2%, but I need that to filter out the duplicate information that the OUTER joins cause.
The rowcount without the where clause is 15,617.
According to the execution plan, the two biggest costs are:
38% - SORT(Sorting the Input) - ARGUMENT: ORDER BY:(jnSalesLeadSchool.SalesLeadID ASC)
19% - INDEX SEEK(Scanning a particular range of rows from a non-clusted index) - OBJECT: SalesLeadNotes.IX_SalesLeadNotes.SalesLeadID, SEEK: SalesLeadNotes.SalesLeadID = SalesLead.SalesLeadID|||LIKE '%something'??
That's a scan everytime|||I understand that the wildcard matches are probably the biggest problem area, but is there any way around them besides a full text search?|||I was gonna say...
"Drop back and punt"...
Ah the Giants of yesteryear....
how does this do?
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||I was gonna say...
"Drop back and punt"...
Ah the Giants of yesteryear....
how does this do?
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||Brett,
I actually tried exactly that yesterday before going home. Here is the problem: if any of those fields are null (which some are), the entire string will default to NULL - which means rows are not matched. To combat this, I tried using an ISNULL(field, '') on each field while concatenating, but the overhead of the ISNULL cancelled out the gain.|||OK, then punt...
Or simplify the criteria...
or...and this is my last shot before FULLTEXT
DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'
SET NOCOUNT ON
SELECT DISTINCT * FROM (
SELECT
x.SalesLeadID,
x.Prefix,
x.FirstName,
x.LastName,
x.Email,
x.Phone,
x.LastContact,
x.SchoolID,
x.SchoolName,
x.City AS 'SchoolCity'
FROM ( SELECT * FROM SalesLead a
INNER JOIN jnSalesLeadSchool b
ON a.SalesLeadID = b.SalesLeadID
INNER JOIN Schools c
ON b.SchoolID = c.SchoolID
WHERE SalesLead.Active = 1
AND ( REPLACE(a.FirstName, @.SearchTerm, '') <> a.FirstName
OR REPLACE(a.LastName, @.SearchTerm, '') <> a.LastName
OR REPLACE(a.Address1, @.SearchTerm, '') <> a.Address1
OR REPLACE(a.City, @.SearchTerm, '') <> a.City
OR REPLACE(a.Email, @.SearchTerm, '') <> a.Email
OR REPLACE(c.SchoolName, @.SearchTerm, '') <> c.SchoolName
OR REPLACE(c.City, @.SearchTerm, '') <> c.City
) AS X
LEFT OUTER JOIN jnSalesLeadDepartment d
ON X.SalesLeadID = d.SalesLeadID
LEFT OUTER JOIN jnSalesLeadOpportunity f
ON X.SalesLeadID = f.SalesLeadID
LEFT OUTER JOIN SalesLeadNotes i
ON X.SalesLeadID = i.SalesLeadID
LEFT OUTER JOIN Department e
ON X.DepartmentID = e.DepartmentID
LEFT OUTER JOIN AdoptionOpportunity g
ON X.OpportunityID = g.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames h
ON g.CourseNameID = h.CourseNameID
WHERE
OR REPLACE(i.Note, @.SearchTerm, '') <> i.Note
OR REPLACE(e.[Name], @.SearchTerm, '') <> e.[Name]
OR REPLACE(h.CourseName, @.SearchTerm, '') <> h.CourseName
OR REPLACE(g.Term, @.SearchTerm, '') <> g.Term
OR REPLACE(g.Chances, @.SearchTerm, '') <> g.Chances
) AS XXX
ORDER BY LastName|||Thanks Brett, but that query takes exactly the same amount of time. I like your idea of using Replace rather than the wildcard comparison, that was clever.
It looks like fulltext is my last resort - are there any rules agains using fulltext searching on columns that contain such a small amount of text (usually less than 100 characters)?|||ow btw: why the order by on a distinct ?|||What you want is really a full-text search. We are beating this mouse bloody when you really want an ox. Byte the bullet and move on!
-PatP
DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'
SET NOCOUNT ON
SELECT DISTINCT
SalesLead.SalesLeadID,
SalesLead.Prefix,
SalesLead.FirstName,
SalesLead.LastName,
SalesLead.Email,
SalesLead.Phone,
SalesLead.LastContact,
Schools.SchoolID,
Schools.SchoolName,
Schools.City AS 'SchoolCity'
FROM SalesLead
INNER JOIN jnSalesLeadSchool
ON SalesLead.SalesLeadID = jnSalesLeadSchool.SalesLeadID
INNER JOIN Schools
ON jnSalesLeadSchool.SchoolID = Schools.SchoolID
LEFT OUTER JOIN jnSalesLeadDepartment
ON SalesLead.SalesLeadID = jnSalesLeadDepartment.SalesLeadID
LEFT OUTER JOIN Department
ON jnSalesLeadDepartment.DepartmentID = Department.DepartmentID
LEFT OUTER JOIN jnSalesLeadOpportunity
ON SalesLead.SalesLeadID = jnSalesLeadOpportunity.SalesLeadID
LEFT OUTER JOIN AdoptionOpportunity
ON jnSalesLeadOpportunity.OpportunityID = AdoptionOpportunity.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames
ON AdoptionOpportunity.CourseNameID = CourseNames.CourseNameID
LEFT OUTER JOIN SalesLeadNotes
ON SalesLead.SalesLeadID = SalesLeadNotes.SalesLeadID
WHERE
SalesLead.Active = 1
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Address1 LIKE '%' + @.SearchTerm + '%'
OR SalesLead.City LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Email LIKE '%' + @.SearchTerm + '%'
OR SalesLeadNotes.Note LIKE '%' + @.SearchTerm + '%'
OR Schools.SchoolName + ' - ' + Schools.City LIKE '%' + @.SearchTerm + '%'
OR Department.Name LIKE '%' + @.SearchTerm + '%'
OR CourseNames.CourseName LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Term LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Chances LIKE '%' + @.SearchTerm + '%'
)
ORDER BY SalesLead.LastName
Thanks in advance!
AaronPerhaps the execution plan has a hint. How much has the distinct? Don't know how expensive it really is. It might be an option to rewrite the whole thing to match the exact search-type so there's no OR-left.
Whats the rowcount without the where-clause?|||Creating an index on Saleslead.Active might help a bit. Enabling full text indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_74oj.asp) will help a lot, but at a significant cost in disk space and INSERT/UPDATE performance.
-PatP|||According to the execution plan, the DISTINCT is costing 2%, but I need that to filter out the duplicate information that the OUTER joins cause.
The rowcount without the where clause is 15,617.
According to the execution plan, the two biggest costs are:
38% - SORT(Sorting the Input) - ARGUMENT: ORDER BY:(jnSalesLeadSchool.SalesLeadID ASC)
19% - INDEX SEEK(Scanning a particular range of rows from a non-clusted index) - OBJECT: SalesLeadNotes.IX_SalesLeadNotes.SalesLeadID, SEEK: SalesLeadNotes.SalesLeadID = SalesLead.SalesLeadID|||LIKE '%something'??
That's a scan everytime|||I understand that the wildcard matches are probably the biggest problem area, but is there any way around them besides a full text search?|||I was gonna say...
"Drop back and punt"...
Ah the Giants of yesteryear....
how does this do?
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||I was gonna say...
"Drop back and punt"...
Ah the Giants of yesteryear....
how does this do?
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||Brett,
I actually tried exactly that yesterday before going home. Here is the problem: if any of those fields are null (which some are), the entire string will default to NULL - which means rows are not matched. To combat this, I tried using an ISNULL(field, '') on each field while concatenating, but the overhead of the ISNULL cancelled out the gain.|||OK, then punt...
Or simplify the criteria...
or...and this is my last shot before FULLTEXT
DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'
SET NOCOUNT ON
SELECT DISTINCT * FROM (
SELECT
x.SalesLeadID,
x.Prefix,
x.FirstName,
x.LastName,
x.Email,
x.Phone,
x.LastContact,
x.SchoolID,
x.SchoolName,
x.City AS 'SchoolCity'
FROM ( SELECT * FROM SalesLead a
INNER JOIN jnSalesLeadSchool b
ON a.SalesLeadID = b.SalesLeadID
INNER JOIN Schools c
ON b.SchoolID = c.SchoolID
WHERE SalesLead.Active = 1
AND ( REPLACE(a.FirstName, @.SearchTerm, '') <> a.FirstName
OR REPLACE(a.LastName, @.SearchTerm, '') <> a.LastName
OR REPLACE(a.Address1, @.SearchTerm, '') <> a.Address1
OR REPLACE(a.City, @.SearchTerm, '') <> a.City
OR REPLACE(a.Email, @.SearchTerm, '') <> a.Email
OR REPLACE(c.SchoolName, @.SearchTerm, '') <> c.SchoolName
OR REPLACE(c.City, @.SearchTerm, '') <> c.City
) AS X
LEFT OUTER JOIN jnSalesLeadDepartment d
ON X.SalesLeadID = d.SalesLeadID
LEFT OUTER JOIN jnSalesLeadOpportunity f
ON X.SalesLeadID = f.SalesLeadID
LEFT OUTER JOIN SalesLeadNotes i
ON X.SalesLeadID = i.SalesLeadID
LEFT OUTER JOIN Department e
ON X.DepartmentID = e.DepartmentID
LEFT OUTER JOIN AdoptionOpportunity g
ON X.OpportunityID = g.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames h
ON g.CourseNameID = h.CourseNameID
WHERE
OR REPLACE(i.Note, @.SearchTerm, '') <> i.Note
OR REPLACE(e.[Name], @.SearchTerm, '') <> e.[Name]
OR REPLACE(h.CourseName, @.SearchTerm, '') <> h.CourseName
OR REPLACE(g.Term, @.SearchTerm, '') <> g.Term
OR REPLACE(g.Chances, @.SearchTerm, '') <> g.Chances
) AS XXX
ORDER BY LastName|||Thanks Brett, but that query takes exactly the same amount of time. I like your idea of using Replace rather than the wildcard comparison, that was clever.
It looks like fulltext is my last resort - are there any rules agains using fulltext searching on columns that contain such a small amount of text (usually less than 100 characters)?|||ow btw: why the order by on a distinct ?|||What you want is really a full-text search. We are beating this mouse bloody when you really want an ox. Byte the bullet and move on!
-PatP
Can this be used as a replacement for Crystal Reports?
I am not familiar with this tool. Can I create reports that can be run
from a C# application with parameters passed to it?
Any help is greatly appreciated.VS 2005 has two new controls: winform and webform. The controls can be used
in local or server mode. In server mode you give it the parameters and call
the server which the control then shows the rendered report. In local mode
you give it the report and the tableset data. However, you have to do lots
more work to handle subreports and other types of reports. My feeling is, if
you plan on not having a server around that Crystal reports is designed more
along the lines of your app providing the data. Reporting Services is
designed as a service oriented architecture and although in local mode you
can give it the data, it was not designed with that in mind. With a server
in the picture then the answer is absolutely yes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg Smith" <gjs@.umn.edu> wrote in message
news:eu9RG9ZYHHA.4440@.TK2MSFTNGP03.phx.gbl...
>I am not familiar with this tool. Can I create reports that can be run
>from a C# application with parameters passed to it?
>
> Any help is greatly appreciated.
from a C# application with parameters passed to it?
Any help is greatly appreciated.VS 2005 has two new controls: winform and webform. The controls can be used
in local or server mode. In server mode you give it the parameters and call
the server which the control then shows the rendered report. In local mode
you give it the report and the tableset data. However, you have to do lots
more work to handle subreports and other types of reports. My feeling is, if
you plan on not having a server around that Crystal reports is designed more
along the lines of your app providing the data. Reporting Services is
designed as a service oriented architecture and although in local mode you
can give it the data, it was not designed with that in mind. With a server
in the picture then the answer is absolutely yes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg Smith" <gjs@.umn.edu> wrote in message
news:eu9RG9ZYHHA.4440@.TK2MSFTNGP03.phx.gbl...
>I am not familiar with this tool. Can I create reports that can be run
>from a C# application with parameters passed to it?
>
> Any help is greatly appreciated.
Monday, March 19, 2012
Can the TEMPDB be backed up?
Had an alert fire telling me the temdb was full and to run a backup on it. W
hen I tried it gave me the following error:
MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
Backup and Restore operations are not allowed on database tempdb, BACKUP DAT
ABASE is terminating abnormally.
HELP!Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
> HELP!
>|||Thanks, read the alert wrong. Hey, Im a newbie. Ran the TRUNCATE SCRIPT, all
is well
-- Aaron Bertrand [MVP] wrote: --
Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
hen I tried it gave me the following error:
MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
Backup and Restore operations are not allowed on database tempdb, BACKUP DAT
ABASE is terminating abnormally.
HELP!Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
> HELP!
>|||Thanks, read the alert wrong. Hey, Im a newbie. Ran the TRUNCATE SCRIPT, all
is well
-- Aaron Bertrand [MVP] wrote: --
Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
Can the TEMPDB be backed up?
You need to run a "Full Backup" on all of your other
Databases.
It may also be a good idea to look at scheduling "Full
Backups" on a more frequent basis. Whenever "Full
Backups" are done, the TempDB is automatically re-sized to
remove all of the temporary data. As TempDB is a system
database, it cannot be manipulated or controlled by any
user.
HTH
Tony C.
>--Original Message--
>Had an alert fire telling me the temdb was full and to
run a backup on it. When I tried it gave me the following
error:
>MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
>Backup and Restore operations are not allowed on database
tempdb, BACKUP DATABASE is terminating abnormally.
>HELP!
>
>.
>> Backups" on a more frequent basis. Whenever "Full
> Backups" are done, the TempDB is automatically re-sized to
> remove all of the temporary data.
I don't think that's true. The only "automatic" resizing of tempdb occurs
when SQL Server restarts, where it reverts to its original size.
> As TempDB is a system
> database, it cannot be manipulated or controlled by any
> user.
Also not true. See http://support.microsoft.com/?id=307487 for manual
methods.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Databases.
It may also be a good idea to look at scheduling "Full
Backups" on a more frequent basis. Whenever "Full
Backups" are done, the TempDB is automatically re-sized to
remove all of the temporary data. As TempDB is a system
database, it cannot be manipulated or controlled by any
user.
HTH
Tony C.
>--Original Message--
>Had an alert fire telling me the temdb was full and to
run a backup on it. When I tried it gave me the following
error:
>MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
>Backup and Restore operations are not allowed on database
tempdb, BACKUP DATABASE is terminating abnormally.
>HELP!
>
>.
>> Backups" on a more frequent basis. Whenever "Full
> Backups" are done, the TempDB is automatically re-sized to
> remove all of the temporary data.
I don't think that's true. The only "automatic" resizing of tempdb occurs
when SQL Server restarts, where it reverts to its original size.
> As TempDB is a system
> database, it cannot be manipulated or controlled by any
> user.
Also not true. See http://support.microsoft.com/?id=307487 for manual
methods.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Can the TEMPDB be backed up?
You need to run a "Full Backup" on all of your other
Databases.
It may also be a good idea to look at scheduling "Full
Backups" on a more frequent basis. Whenever "Full
Backups" are done, the TempDB is automatically re-sized to
remove all of the temporary data. As TempDB is a system
database, it cannot be manipulated or controlled by any
user.
HTH
Tony C.
>--Original Message--
>Had an alert fire telling me the temdb was full and to
run a backup on it. When I tried it gave me the following
error:
>MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
>Backup and Restore operations are not allowed on database
tempdb, BACKUP DATABASE is terminating abnormally.
>HELP!
>
>.
>
> Backups" on a more frequent basis. Whenever "Full
> Backups" are done, the TempDB is automatically re-sized to
> remove all of the temporary data.
I don't think that's true. The only "automatic" resizing of tempdb occurs
when SQL Server restarts, where it reverts to its original size.
> As TempDB is a system
> database, it cannot be manipulated or controlled by any
> user.
Also not true. See http://support.microsoft.com/?id=307487 for manual
methods.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Databases.
It may also be a good idea to look at scheduling "Full
Backups" on a more frequent basis. Whenever "Full
Backups" are done, the TempDB is automatically re-sized to
remove all of the temporary data. As TempDB is a system
database, it cannot be manipulated or controlled by any
user.
HTH
Tony C.
>--Original Message--
>Had an alert fire telling me the temdb was full and to
run a backup on it. When I tried it gave me the following
error:
>MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
>Backup and Restore operations are not allowed on database
tempdb, BACKUP DATABASE is terminating abnormally.
>HELP!
>
>.
>
> Backups" on a more frequent basis. Whenever "Full
> Backups" are done, the TempDB is automatically re-sized to
> remove all of the temporary data.
I don't think that's true. The only "automatic" resizing of tempdb occurs
when SQL Server restarts, where it reverts to its original size.
> As TempDB is a system
> database, it cannot be manipulated or controlled by any
> user.
Also not true. See http://support.microsoft.com/?id=307487 for manual
methods.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Can the TEMPDB be backed up?
Had an alert fire telling me the temdb was full and to run a backup on it. When I tried it gave me the following error:
MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
Backup and Restore operations are not allowed on database tempdb, BACKUP DATABASE is terminating abnormally.
HELP!
Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
> HELP!
>
|||Thanks, read the alert wrong. Hey, Im a newbie. Ran the TRUNCATE SCRIPT, all is well
-- Aaron Bertrand [MVP] wrote: --
Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
Backup and Restore operations are not allowed on database tempdb, BACKUP DATABASE is terminating abnormally.
HELP!
Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> MIcrosoft SQL_DMO (ODBC SQLSTATE: 42000)
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
> HELP!
>
|||Thanks, read the alert wrong. Hey, Im a newbie. Ran the TRUNCATE SCRIPT, all is well
-- Aaron Bertrand [MVP] wrote: --
Why would you want to back up tempdb?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Robert" <rsalazar@.cbbank.com> wrote in message
news:2D5A012C-C61B-4985-B609-D6DB8E8F4D08@.microsoft.com...
> Had an alert fire telling me the temdb was full and to run a backup on it.
> When I tried it gave me the following error:
> Backup and Restore operations are not allowed on database tempdb, BACKUP
> DATABASE is terminating abnormally.
Sunday, March 11, 2012
can stored procedure run more than 1 sql statements
Hi all,
I have 2 independent stored procedures(I cannot join them because of
the group condition are different however they have the same cust id)
but one for reading the summary and other showing the details of the
summary. Now I want to run these two stored procedures in order to
get the both return results for generating a .Net crytral report. Can
I get the results form both stored procedures and how can I do that.
Please help me. Thanks a lot.
Hellosixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello
I have 2 independent stored procedures(I cannot join them because of
the group condition are different however they have the same cust id)
but one for reading the summary and other showing the details of the
summary. Now I want to run these two stored procedures in order to
get the both return results for generating a .Net crytral report. Can
I get the results form both stored procedures and how can I do that.
Please help me. Thanks a lot.
Hellosixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello
Hi,
Why not use views for this. You could achieve with that.
-Manoj|||..NET should be able to handle multiple result sets from a single sp.
You can "spell out" both SQL/T-SQL code blocks in the same sp or you
can create two seperate sp's and call sp B from sp A via an EXECUTE
statement.
Good Luck
sixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello|||groups@.rlmoore.net (rm) wrote in message news:<4c61f49.0309191421.14d680a4@.posting.google.com>...
> .NET should be able to handle multiple result sets from a single sp.
> You can "spell out" both SQL/T-SQL code blocks in the same sp or you
> can create two seperate sp's and call sp B from sp A via an EXECUTE
> statement.
> Good Luck
I have try the 2nd method (spA exec spB) but it seem that nothing can
return at the .net crystal report even I do not use the subreport. is
there other methods?
Pls help me.
Thx
Hello
Can SSRS 2005 be installed on Windows XP SP2?
I installed SQL Server 2005 Developer edition on a Windows XP SP2 to run side by side with SQL Server 2000 dev edition and SSRS 2000.
Can SSRS 2005 be installed to run on Windows XP SP2?
Thanks
Yes you can.
I have RS2005 installed on my portable computer (with Windows XP SP 2) and it's work fine - even the development tool (VS2005). Of course you need to have IIS installed on your computer.
Good luck.
Can SSIS package be run from WebService?
Dear,
Can SSIS package be run from WebService?
P.S. SSIS package and Webservice are located in the same computer.
Thanks much!
It is solved.
And 1 more quest. How can I check the saved SSIS package in Microsoft SQL Server Management Studio?
Thanks much!
|||Yes. When you connect to a server from SQL Server Management studio - change the Server type from 'Database Engine' to 'Integration Services'
Hope that helps.
Thanks,
Loonysan
Thanks for your reply.
Once I click the MSDB folder, the error message, Login timeout expired is shown.
Do you have any idea about it?
Thanks
|||Hi Michael,
The follwoing URL - http://emea.windowsitpro.com/SQLServer/Article/ArticleID/46723/46723.html
(Look at Roles section) talks about the various security permissions required to access/execute SSIS packages.
If this doesn't help - Please post this question in a new thread.
Thanks,
Loonysan
Sorry I don't find "Roles Section" mentioned in the URL u post to me.
Could u mind explanning to me or give me another URL for me to reference?
Thanks much!
|||Search for "Roles" in that URL - the second found result is the start of Roles section.
Hope that helps.
Thanks,
Loonysan
Sorry.. I just found 1 "Roles" in the URL, http://emea.windowsitpro.com/Articles/Index.cfm?ArticleID=46723&DisplayTab=Article u given.
The content is as the below
Security in SSIS
Like every other feature in SQL Server 2005 Integration Services (SSIS), the product's new security features are noticeably different from their DTS counterparts. SSIS still uses passwords and encrypts sensitive data, but the approach has changed substantially, simplifying automated package execution, protection, scheduling, and editing. SSIS security features fall into five functional categories: encryption, for securing packages or parts of packages; sensitive-data protection, for identifying and protecting passwords and other sensitive data; SQL Server roles, for controlling access to packages stored in SQL Server; digital code signing, for ensuring that a package hasn't changed; and integration of SQL Server Agent subsystems, for securely storing and executing packages. Let's take a closer look at these new security features and get some guidelines for how and when to use them. I'd like to extend special thanks to Sergei Ivanov, the Integration Services team developer who wrote these features, for answering all my questions and making sure I got the details right. . . .
Thanks for your help!
|||Not sure why you aren;t able to see the following text in that URL
Roles
SSIS adds three new roles that affect the way you run packages in SQL Server Agent and access packages stored in SQL Server. The db_dtsoperator role is the most limited new role. Users in this role can only enumerate (i.e., determine which packages are available) and view existing packages; operators can't create or modify SSIS packages. The db_dtsltduser role lets users create and modify their own packages and enumerate existing packages. And the db_dtsadmin role lets users create, modify, enumerate, and view all packages. Systems administrators are automatically in the db_dtsadmin role.
You must be in one of these three roles to access SSIS packages. If you aren't, and you attempt to enumerate a package by opening the Packages node for the SSIS Server object explorer in SQL Server Management Studio, you'll get the error message that Figure 2 shows.
You can find the new SSIS roles in the Management Studio MSDB database node under the Security, Roles, Database roles node. These roles apply only to MSDB and control access to the sysdtspackages90 table through the stored procedures that Figure 3 shows. You can find sysdtspackages90 and the stored procedures for accessing it in MSDB.
If you look at the sysdtspackages90 table in MSDB, you'll see two columns of particular interest to this discussion. The readrole column specifies the logins or roles that have read access to the package. Readers can enumerate, execute, and export a package from SQL Server. The writerole column specifies the logins or roles that have write access to the package. Writers can delete, rename, edit, and save a package to SQL Server. By default, these two columns are null.
To open the Package Roles dialog box, right-click a package in the Packages node and select Package Roles, as Figure 4 shows. Figure 5 shows the Package Roles dialog box default settings. When the readrole column is null, all users in any SSIS package role can enumerate and read that package. When the writerole field is null, only users in the db_dtsadmin role and the package's creator can modify or delete the package. To prevent a role from viewing and executing a given package, you must remove the role from the Reader Role comma-separated list. You can also add other roles to the reader and writer role lists. For example, I can add the Agent User, Limited User, and Security Administrator roles to the package reader role by entering them in a comma-delimited list as SQLAgentUserRole, db_dtsltduser, and db_securityadmin. I can also assign users and roles to a package's writer role to let them perform write operations such as DELETE and MODIFY to that package only.
To better understand how these roles interact and see which role has what rights, take a look at Table 1, which shows the rights assigned to each role and the activities that a user in each role can do by default. The table shows that the db_dtsoperator role can't work with its own packages; this limitation exists because the db_dtsoperator role can create packages, so that role can't own any. The table also shows that the Writerole db_stsoperator can't import packages because you can't have a write role on a non-existent package. The Assigned User isn't really a role, but I included it because you can assign a SQL login or role to a package reader or writer role. For example, by default, the db_dtsoperator isn't assigned to the writer role and doesn't have write privileges for packages. However, operators you explicitly place in the writer role will have all write-oriented privileges for that package.
By now your head is probably swimming in roles, but you need to know about one more new role in SQL Server 2005: the SQLAgentUserRole. A user must be in this role to create SQL Server Agent jobs, and users in this role can manage only the jobs that they create. This role is important because it affects a common SSIS usage scenario. After they're built, tested, and deployed, many packages reside on a SQL Server box in a back room somewhere, and a SQL Server Agent job triggers them when they need to run. Because SQL Server Agent integration is important to the security of such packages, let's take a closer look at how it works.
|||Thanks for ur help.
But I find that I don't have any db_dtsoperator role in the server roles in DB and I cannot select different authentication mode once I login into Integration Services.
Can you give me some ideas? Thanks much!
Can SRS 2000 rdl files be published to and run on sql server 2005?
We're planning on upgrading from sql server 2000 to 2005 and we're hoping
that SRS 2000 .rdl files will run on sql server 2005.
Can anyone confirm if this is the case or not.
Thanks in advance for your help.Yes. However, when I upgraded I had some small weird problems. I then opened
up the rdl files with the RS 2005 designer, converted to RS 2005 and
re-deployed and the problems went away. Nothing major and we were able to
function but obnoxious. I can't tell you exactly what would occur, its been
awhile since I upgraded.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sherman In Denver" <sherman@.newsgroup.nospam> wrote in message
news:3524E97D-9C2A-4D56-8A7E-2FE28E427A3B@.microsoft.com...
> We're planning on upgrading from sql server 2000 to 2005 and we're hoping
> that SRS 2000 .rdl files will run on sql server 2005.
> Can anyone confirm if this is the case or not.
> Thanks in advance for your help.
>|||Hello Sherman,
I agree with Bruce. There are some changes in the RDL file format between
RS2000 and RS2005, I would suggest you open the SSRS 2000 RDL file in SQL
2005 BI Studio's report designer, it will help convert your RDL to RS 2005
format.
Also, if you still want to use RS2000 reports on SQL Server 2000 later, I
suggest you make an backup for the RS 2000 RDL files.
In addition, you can search for some former threads discussing on RS
2000--> RS 2005 report converting if you meet any particular converting
issues.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
How are you doing on this issue. Does Bruce and Steven's reply and
suggestion helps you some? If there is still anything we can help, please
feel free to post in the newsgroup.
Thank you for using our MSDN Managed Newsgroup Support Service!
Sincerely,
Luke Zhang
Microsoft Online Community Support
This posting is provided "AS IS" with no warranties, and confers no rights.
that SRS 2000 .rdl files will run on sql server 2005.
Can anyone confirm if this is the case or not.
Thanks in advance for your help.Yes. However, when I upgraded I had some small weird problems. I then opened
up the rdl files with the RS 2005 designer, converted to RS 2005 and
re-deployed and the problems went away. Nothing major and we were able to
function but obnoxious. I can't tell you exactly what would occur, its been
awhile since I upgraded.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sherman In Denver" <sherman@.newsgroup.nospam> wrote in message
news:3524E97D-9C2A-4D56-8A7E-2FE28E427A3B@.microsoft.com...
> We're planning on upgrading from sql server 2000 to 2005 and we're hoping
> that SRS 2000 .rdl files will run on sql server 2005.
> Can anyone confirm if this is the case or not.
> Thanks in advance for your help.
>|||Hello Sherman,
I agree with Bruce. There are some changes in the RDL file format between
RS2000 and RS2005, I would suggest you open the SSRS 2000 RDL file in SQL
2005 BI Studio's report designer, it will help convert your RDL to RS 2005
format.
Also, if you still want to use RS2000 reports on SQL Server 2000 later, I
suggest you make an backup for the RS 2000 RDL files.
In addition, you can search for some former threads discussing on RS
2000--> RS 2005 report converting if you meet any particular converting
issues.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
How are you doing on this issue. Does Bruce and Steven's reply and
suggestion helps you some? If there is still anything we can help, please
feel free to post in the newsgroup.
Thank you for using our MSDN Managed Newsgroup Support Service!
Sincerely,
Luke Zhang
Microsoft Online Community Support
This posting is provided "AS IS" with no warranties, and confers no rights.
can tables themselves be fragmented? Index ID 0?
When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
CoryIndex ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragm
entation this is
impossible for the datapages of a heap table (index 0) as there is no order.
This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that t
he heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table doe
s not have a clustered
> index. The only way to remove fragmentation is to create a clustered index
on that table or to
> export all the data, truncate it and import it all back in again. Index I
D of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
CoryIndex ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragm
entation this is
impossible for the datapages of a heap table (index 0) as there is no order.
This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that t
he heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table doe
s not have a clustered
> index. The only way to remove fragmentation is to create a clustered index
on that table or to
> export all the data, truncate it and import it all back in again. Index I
D of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
can tables themselves be fragmented? Index ID 0?
When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a hea
p, so scan density etc.
are meaningless for a heap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Cory Harrison" wrote:
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a hea
p, so scan density etc.
are meaningless for a heap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Cory Harrison" wrote:
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>
can tables themselves be fragmented? Index ID 0?
When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory
Index ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>
|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragmentation this is
impossible for the datapages of a heap table (index 0) as there is no order. This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that the heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table does not have a clustered
> index. The only way to remove fragmentation is to create a clustered index on that table or to
> export all the data, truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory
Index ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>
|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragmentation this is
impossible for the datapages of a heap table (index 0) as there is no order. This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that the heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table does not have a clustered
> index. The only way to remove fragmentation is to create a clustered index on that table or to
> export all the data, truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
Subscribe to:
Posts (Atom)