Normally to find the actual sql that executes i go up in the trace until i find the relevant cursor prepare. In this case the one for 197 cursor prepare.
But this time around i cannot find it, these are app servers connected so for all i know the cursor could be prepared at 5am in the morning and re-used all day long.
Is there anyway (adding certain profiler events or something), so that i can see the actual statement held in the cursor ?
I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
I'm getting a "Input string was not in a correct format." when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw. Datasource is called xSqlIB and formview is called fmvIB. Any ideas?
I have a XL source file, the first column contains the name, 199001,19902.....,199012, In SSIS package using XL source if click the 1st column contains headings it automatically covert my heading as name,F2,F3.....F13.
But i need as it is like Name,199001,19902,.....,199012 as a heading than i am using unpivot transformation and convert the column into row to my staging table.
Please sort out my problem i need the XL column like i mentioned above( Name,199001,19902,.....,199012 )
Hi, How do I check what is the actual DB size that is currently being use? e.g. I set 10 GB as initial size. A few days and a few transactions later, how can I know how much was used since it is still under 10GB and checking the physical DB file will not tell me anything.
I am running 6.5 sql and work with a traffic and billing software ( called NOvar) from another company(encoda system) which does a lot of scheduling, reporting etc
I dont know the contents of table (100 table ) and their column or which table its querying to take out reports
Can i create a trace to know the syntax each time some thing is executed.
I also need to create customized reports, can this be done by sql reporting or does i need to go from crystal reports or someone else For i dont know any language except sql and HTML
======================================================== I have one table store below information, and other one table is store staff name and phone number. how to display of table c data by new column and using the key staff_code1=staff_id or staff_code2= staff_id or staff_code3=staff_id ? ---------------------------------------------------------
Table A staff_code1,staff_code2,staff_code3
Table B staff name staff_id staff_phone -----------------------------------------------
Table C display in new column . staff name-1 code1 staff name-2 code2 , staff name-3 code3 peter id-01 susuan id-03 david id-05
I have a stored procedure that will execute with less than 1,000 reads onetime (with a specified set of parameters), then with a different set ofparameters the procedure executes with close to 500,000 reads (according toProfiler).In comparing the execution plans, they are the same, except for the actualand estimated number of rows. When the proc runs with parameters that producereads that are less than 1,000 the actual and estimated number of rows equal1. When the proc runs with parameters that produce reads are near 500,000 theactual rows are approximately 85,000 and the estimated rows equal 1.Then I run:DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEIf I then reverse the order of execution by executing the procedure thatinitially executes with close to 500,000 reads first, the reads drop to lessthan 2,000. The execution plan shows the acutual number of rows equal to 1,and the estimated rows equal to 2.27. Then when I run the procedure thatinitially executed with less than 1,000 reads, it continues to run at lessthan 1,000 reads, and the actual number of rows is equal to 1 and theestimated rows equal to 2.27. When run in this order, there is consistency inthe actual and estimated number of rows and the reads for both executionswith differing parameters are within reason.Do I need to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on productionand then ensure that the procedure that ran close to 500,000 reads is runfirst to ensure the proper plan, as well as using a KEEP PLAN option? Or,what other options might you recommend?I am running SQL 2000 SP4.--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200609/1
Hi Everybody, I like to compute the actual physical database file size using queries.
From the EM database properties, the file size shown is not matching with the master..sysaltfiles.size
Ex. Actual file size is 12MB and 1MB for MDF & LDF respectively. 'sysaltfiles' shows 1464 & 96 resp. Since they are shoing it as 8K Pages, 96*8 = 768KB is not matching with 1024KB for the LDF file. Is there any overheads (additional space from the physical file for file headers) allocated?
I have an instance where a process is trying to insert a Customer into the Customers table and CustomerLastName is a non nullable field. Customer's last name is Null. Should this be the reason this Customer is never in the end table?
Hello. I've seached the this forum and others for this question of mine but couldn't get any thing. Everyone know that in MSSQL, the precision of datetime is currently limited to 1/300th of a second. My question is, is there any way to store the actual millisecond in the a table? Also, later, able to pull it up from the table? What are the options?
i'm hoping someone can help me out with this problem.
i have a database called Tracking.mdf and its associated logfile Tracking_log.ldf
i need to build another database based on the above with more tables and keep the data separate hence the requirement to make a copy of the tracking data file i've tried changing its name but it will not attach. i can change its database name to whatever i want but somehow can't find out how i can say change its actual filename to Tracker.mdf and Tracker_log.ldf
I€™m using LTO3 tape drive (400GB) the used media is for LTO2, currently I€™m looking for LTO4(1.6TB) but I need the backward compatibility to support LTO3 tapes so I can go for LTO2 media or LTO3 media.
I need to know the size of the database backup (Ms SQL2005 SP1 x64-bit, on Ms Windows 2003 R2 x64-bit) on the tape drive. I can see the backup size of the database but not the size on the tape which is using hardware compression.
Sql Server has many data types. For Example: smallint Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes. I want to know that If it contains like 0 or 100 or 1000 or -200 or -2000 or more or less. What will its actual size? 2 bytes or change with the value. Please also mention the reference with your answer. if available.
In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.
Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.
So, what I need to know is: is there a sql statement that can return this type of information?
Example:
Table Names in Database: Customers, Suppliers
Columns in Customers Table: Name, Phone, Email, Address
I click on the word "Customers" in the first dropdownlist.
I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.
I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.
I've gotten sort of fed up with dealing with regional date settings onthe client side and am considering the following scheme - justwondering if anyone has a negative view of it or not:Instead of@StartDate datetime,@EndDate datetimeUse:@StartDaysDiff int,@EndDaysDiff intIn the front end app take the desired date and do a DATEDIFF with thecurrent date, then pass the date diff as a parameter as an integerrather than deal with the dates at all.Then...DECLARE @TodayDate datetime, @StartDate datetime, @EndDate datetimeSELECT @TodayDate = GETDATE()SELECT @StartDate = DATEDIFF(DAY,@StartDaysDiff,@TodayDate)SELECT @EndDate = DATEDIFF(DAY,@EndDaysDiff,@TodayDate)SELECTa.SomethingFROMdbo.Appointments aWHEREa.AppointmentDate BETWEEN @StartDate and @EndDateJust wondering ...Thanks,lq
I have gone through the table partitioning in MSDN, like Designing Partitions to Manage Subsets of Data . But how to do this in actual world, since some db need to partition for 7 days, then archive these days records on the 8th day, while other prefer 14 days/monthly, and run this repetitively for many years? If this is running weekly, how can i generate the scheme and function dynamically? What if ID for row count is not viable?
Sure this will not work:
Code Snippet CREATE PARTITION FUNCTION [TransactionRangePF1] (datetime) AS RANGE RIGHT FOR VALUES ('10/01/2003', '10/02/2003', '10/03/2003', '10/04/2004', '10/05/2004', '10/06/2004', '10/07/2004'); GO
In a dataflow, I've an OLE DB source before a script component. When I link the Red Output of the source to the script component, I want to get the COLUMN NAME (which is the source of error), instead of the default ErrorColumn (LineageID). I'm not able to succeed with the following code snippet.
My code is something like this:
Dim temp_Int_EmpID As Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn90 temp_Int_EmpID = Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumnCollection90.GetInputColumnByLineageID(Row.ErrorColumn)
Pls. let me know if it's possible to achieve what I require & if so, how.
Having a SQL Server 2012 Enterprise (x64) on a Windows 2012 R2. We need to know, a reliable way, the number of processor sql server is using at a give time. We already know how many total processor are available to sql by getting info from sys.dm_os_sys_info.
For instance, a server has 40 processors, we want to know how many of those are being used at a given time. Since the load on the server may not be that high, we would like to know how many processors we can eliminate and the load will still be unaffected.
After watching the server performance for a while, we are predicting we may only need 16. But we would like to get some statistics before we reduce it to this number.
STEP1: CREATE TABLE Trace(Statement VARCHAR(MAX)) INSERT INTO Trace VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted  from History'), ('Insert into Account  Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL, N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT', @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.
I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.
Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.
I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.
Snippet that is not working:
declare @db_name varchar(80)
declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.