Please Evaluate This Approach To Shrinking Log Files

Sep 8, 2006

I've been tasked with taking over the support for a client's SQL Server
database. I'm not a DBA by profession, I'm a software developer who
uses SQL Server as a database designer.

The clients have reported that the server is running out of disk space
and examination shows that the log files for several of the databases
are at 5Gb or more.

After reading around the subject I suggested the following sequence of
operations:

-- Select the name of the database you want to shrink
USE MyDB

-- Dump unwanted transactions
dump tran MyDB with truncate_only

-- Get the name of the logfile
SELECT * from sysfiles

-- Having examined the rows returned by this use the log file....

-- Shrink the file to required size (in MB)
DBCC SHRINKFILE('MyDB_log', 10)

Is this a reasonable approach? Please bear in mind that I'm pretty new
to this, and I have many other tasks to do besides manage the server.
A previous DBA has set up good maintenance plans etc. so everything is
being properly backed up (well, I think it is)

If this IS a good approach, would it be reasonable to do this on, say,
a monthly basis as a scheduled job? Obviously the step

SELECT * from sysfiles

which gives us the physical name of the log file would be removed and
the job would operate explicitly on each log file for each database in
turn.

Many thanks for reading.

William Balmer.

View 2 Replies


ADVERTISEMENT

Shrinking Log Files

Feb 1, 2000

Hi again

Damn, people, how to shrink log file. I've tried several times with
DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY)
and
DBCC SHRINKFILE (log_name, final_MB, TRUNCATEONLY)

and the log still is _untouched_

I have got big table, about 190 mln records (15 GB) and after simple update the log is very big but unnessesery.

Michal Pajkowski

View 3 Replies View Related

Shrinking Files

Nov 3, 2006

What is the best way to Shrink a Data file and Log Files.

View 1 Replies View Related

Shrinking The Log Files

Apr 11, 2008

Hello,

we are running on sql server 2005 and in some of our production databases the log files are twice as bigger than the data files. we are planning to reduce the log file size. I saw the option on the management studio to shrink the log file. I just have some questions on this.
as long as we are doing on the production server can we do that while the database is online or do we need to take the database offline? any help would be appreciated. Thank you!

.

View 9 Replies View Related

Shrinking Log Files After Backup

Jul 22, 2003

Hello everybody.
I have SQL2000 sp3 standard
with 50 db's

All db set for full recovery
and autoshrink
Backup done with Tivoli

full backup once a week
log backup done every 12 hrs

Problem .. shrinking logs

every 20 min I run job
DBCC SHRINKFILE (My_db_logFile) for every db

70% of the time I am getting
message similar to
-------------
Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
--------------
1. I checked with sp_who2
The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."

Why i getting "Cannot shrink ..." ?

even if job runs right after backup of the log files ,I still have messages.

Thank you

Alex

View 6 Replies View Related

Shrinking Database Files

Jul 23, 2005

I have a database file LEAR_Index(yes, it hold index data) from a havehave recently removed a bunch of data.It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink thefile to lean 30-40Gb.I´ve been trying this:dbcc shrinkfile('LEAR_Index',40000)But to no apparent avail; the file did not shrink.I´ve tried using enterprise manager for this but it consistenlycrashes when performing this operation.#Any thoughs, idear as to what i might be doing wrong?

View 6 Replies View Related

Shrinking Databases Or Files

Jun 26, 2006

Hi,
I have some doubts about Shrinking databases or files.
while shrinking a file, I learned that we are alllowed to shrink more than the minimum size of the file, does not it bring damage to the data in that file ??

View 13 Replies View Related

Shrinking Database Files In Production

May 15, 2008

Hi,

We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.

thanks

View 5 Replies View Related

DB Engine :: Database Files Shrinking

Jul 6, 2015

I have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?

View 7 Replies View Related

SQL Security :: Shrinking Log Files With Defragmentation?

Aug 6, 2015

I have a no of databases in full recovery model whose files are many times their datafiles. It is because these databases were copied from the development servers and in the development servers they were not taking the transactional log backups although once in the production server it is ensured that a transactional log is taken once in a day atleast. I plan to shrink the logfiles using the dbcc commands. However I am afraid that it may lead to severe defragmentation and performance hits.

We are using Sql Server 2008R2 enterprise edition which is clustered.

In this context my questions are:-

1)What is the best course to do the shrinking of log with out defragmentation?

2)Can I do the shrinking when the database is in use or is online in production?

3)Will the shrinking of the logfile improve the performance in any manner like that of the i/o operations or paging?

4)Can I do the shrinking of the log files alone without the shrinking of the corresponding data files?

View 10 Replies View Related

Transact SQL :: Shrinking Data Files In AlwaysOn

Jun 21, 2015

I am using Sql Server 2012 in always on configuration with multi subnet failover clustering. Size of data file has suddenly increased, i dropped all the unnecessary table from database three days back. day before yesterday i tried shrinking data file using DBCC command but it is taking too much time. is there any other option for deallocating the space.

View 7 Replies View Related

DB Design :: Clear Space On The Drive - Shrinking Log Files

May 26, 2015

I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file. 

Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?

View 5 Replies View Related

Can't Get More Than First Field To Evaluate.

Jan 8, 2007

I am having trouble getting an

View 6 Replies View Related

Evaluate Numaric Value

Apr 17, 2008

I havea flat file source.All of the coulmns in the source file are string of 5o length( default). There going to be couple of numeric columns like beginning balance, end balance etc. Now I have to evaluate those column to see if there is any column has more then two digit after decimal point. I have to do it through vb.net scripting in SSIS.

Can some one please give me some suggestion how can I achieve it?

Thanks.

View 3 Replies View Related

Evaluate A String Expression

Aug 9, 2006

Hi,

I have a table which has expression as a column and the data looks similar to the following .



ID (int) Expression (nvarchar data type)

1 8*(1/2)-6

2 278*(1/4)-2

3 81*(3/5) +4



I now have the expression as an nvarchar. (Ex: 8*(1/2)-6)

Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string. I need to write a procedure for this.

I am unable to cast/convert nvarchar to float/int

Any sample code would be greatly appreciated.

This a very urgent requirement for me.Please get back

Thanks

Swapna

View 8 Replies View Related

Programmatically Evaluate Expression

Jun 13, 2006

I would like to evaluate expressions from within my execute function in a custom task. I saw this post from about 8 months ago detailing how it would be possible:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=117564&SiteID=1

Is there an updated response (or more detailed example) to the question? Am I really risking a lot by using this undocumented feature?

Thanks!

View 3 Replies View Related

Sp_start_job Does Not Evaluate "@server_name" Parameter

Apr 30, 2001

We want to start a job remotely on another SQL7 server.

innocently we thought that should be no problem with sp_start_job.

We used the command listed below:

use msdb
go
EXEC sp_start_job @job_name='MyJobName', @server_name='blablabla'

The strange thing is, that sp_start_job ignores the @server_name at all.

Even if we enter complete nonsense for @server_name the job gets started as long it is present on the local sqlserver instance.
sp_start_job seems to search locally for the given job_name, only.
Consequently if we enter the remote server's name the job fails as the
jobname is not present on the local sqlserver.

Any ideas what's going wrong here.

Greetings from Mannheim, Germany
Ricardo

View 2 Replies View Related

How To Evaluate Data In Multi Rows

Oct 19, 2013

I am trying to evaluate dates within my sub queries for A,B,C,D. If there is a count > 0 in the 'A' category, I need to evaluate if the servicedates in B,C,D are within 30 days of 'A'. How would I code this within my current code?

select
customernumber,
SUM(CASE WHEN category = 'A' then 1 else 0 end) 'A',
SUM(CASE WHEN category = 'B' then 1 else 0 end) 'B',
SUM(CASE WHEN category = 'C' then 1 else 0 end) 'C',
SUM(CASE WHEN category = 'D' then 1 else 0 end) 'D'

[Code] ....

My BEFORE the case statement:

Customernumber A B C D SERVICEDATE
123456789 1 0 0 0 2012-02-07
123456789 0 0 1 0 2012-02-28
123456789 0 0 1 0 2012-03-06
123456789 0 1 0 0 2012-12-17

My output after case statement:

Customernumber A B C D
123456789 1 1 2 0

What I need my output to look like after case statement:

Customernumber A B C D Service w/in 3 mnths
123456789 1 1 2 0 2

View 1 Replies View Related

Need To Evaluate Stored String Formula

Jun 18, 2007

In 'MyTable' I have the following columns...

TotalNumber (numeric)
Weighting (numreric)
Hours (numeric)
Minutes (numeric)
Formula (nvarchar)

'Formula' column stores a literal string of the formula that may include some, none or all of the previous columns or be NULL.

Here are some examples of the actual strings it stores...

Weighting*Hours
Weighting+(TotalNumber*Hours)
Weighting*Hours)+(TotalNumber*2)

etc etc

All I want to do is create a UDF that will evaluate these strings as math formula and return the value depending on the values of the other columns in the row.

Bear in mind that there may not be a string formula at all for some rows, in which case the value of teh Hours column alone should be the result.

I can do this in vb using the 'Replace' function but am having difficulty in translating it over to T-SQL.

Here is the vb version i use in Ms Access...

getFormula(strDutyType As String, dblTotalNumber As Double, dblWeightingAs Double, dblHours As Double, dblMinutes As String, strFormula As Variant)

If IsNull(strFormula) Or strFormula = "" Then
getFormula = dblHours
Exit Function
End If

'Create the expression string with literal values
strExpression = Replace(strFormula, "TotalNumber", _
CStr(dblTotalNumber), , , vbTextCompare) _
strExpression = Replace(strExpression, "Weighting", _
CStr(dblPF), , ,vbTextCompare)
strExpression = Replace(strExpression, "Hours", _
CStr(dblHours), , , vbTextCompare)
strExpression = Replace(strExpression, "Minutes", _
CStr(dblMinutes), , , vbTextCompare)

'Evaluate the math of the literal expression
getFormula = Eval(strExpression)

-----------

Many Thanks

View 1 Replies View Related

Evaluate Expression In Custom Component

Dec 12, 2007

Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example?

An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2.

I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator.

Thanks!

-Thames

View 5 Replies View Related

Custom Components - How To Evaluate A Property As An Expression?

Apr 11, 2008



Hi all,

I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far.

I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component.

I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"?


Thanks!

View 6 Replies View Related

Error With XML Task Saying Expression Must Evaluate To A Node-set

Sep 4, 2007

Hello friends'
I am new to integration services,first time I'm using XML Task Control in SSIS,
I am stuck with problem saying....
"[XML Task] Error: An error occurred with the following error message: "Expression must evaluate to a node-set.".
I am trying to perform XPath operation type and accumulating values in a variable.
I have no clule about error if u can help to come around the problem.

View 1 Replies View Related

Power Pivot :: How Does EVALUATE Function Work

Oct 24, 2015

playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model.  I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.

View 4 Replies View Related

Derived Columns Expressions Evaluate Incorrectly

Oct 26, 2007

All,

I have seen it happen frequently that I type in a perfectly valid SSIS expression (this is easy for me since I am an old hand at C++/C/C#) in a row in a Derived Column transformation, and it turns red. Or sometimes, I will have an invalid expression that I correct, but it stays red. Finally I have also seen it happen that I make some change in the data flow pipeline and suddenly a Derived Column transform develops an error. I then go into the Derived Column transform and find that the expression has turned red. So, I literally have to go into the expression in these cases, and make a trivial change to them to get the red error to go away. Alternatively, I can cut the derived column expression text, and then paste it back in and it works (this is most telling.)

So, it seems to me the Derived Column is somehow holding onto some meta data about the Derived Column that is getting out of date (rather than re-evaluating the correctness of the Expression.) One thing I usually can do to repro this at times is to remove a column (that the Derived Column depends upon) from the pipeline and then re-add it. When I go into the Derived Column it will be red, and then like I said I have to tweak the expression to force SSIS to re-evaluate the expression.

Anyway, I'm curious if anyone else has seen this?

Thanks.

View 10 Replies View Related

Script Component In Loop Tries To Evaluate Connection

May 12, 2006

I have a script component which loads a file which is in a custom format. The script component is inside a For Each Loop Container and it uses a flat file connection manager. The loop sets the connection string for the connection manager.

The problem I'm having is that the connection string needs to be set to something every time that I start the package but I don't know ahead of time what file there will be, so I get a System.IO.FileNotFoundException error on the script component. If I manually set the variable for the connection string and point it to a file that exists, then the package runs fine but at the end of the package the connection string is set to the last file loaded and this file will no longer exist the next time the package runs.

I hope this makes sense...

View 1 Replies View Related

Reporting Services :: Evaluate Two Values That Came From Different Dataset

Sep 17, 2015

I have an ssrs (report builder) with 2 dataset. the first dataset is a summary if records which the report has a column name qty and i put also a total qty summary in the last rows. the second dataset is a raw data and have a column name qty, also i put a total qty summary in the last row.  The requirements is to be able to evaluate or check the total qty under dataset1  from total qty of dataset2 if equal else if not equal i have to make the font as red so that the user will inform that the total qty has a discrepancy. the users will validate from raw data which are the one items that have a missing qty. How to work on this or is this appilcable in report builder.

View 4 Replies View Related

How Do I Evaluate The Toggle State Of A Textbox Within An Expression?

Apr 10, 2008



Hi!

On a Reporting Services 2005 report, I have a table with a single group row and a single detail row (and about 5 columns). The details are set up to toggle visibility based on a textbox in the group header called "txt_Order_Number". The order line item details are set with their initial visibility set as hidden. So, when you click on the Plus sign next to the Order Number, the detail rows display below.

However, what I'd like to do is change the background color of the toggled group header row when it is toggled and the details are displayed. Here's what's going on . . .

The row with the Order header information has every textbox set in bold. That's good. It makes a subtle distinction between the order detail rows and the order header row, so when you're looking at the dollar amounts on each line item and trying to add them up in your head to see whether they correctly add up to the dollar amount in the order header row, you can at least more easily see the total amount. However, it makes the whole overall report look funny because all of the order header information is in bold. What I really want to do is change the background color of the order header row to light gray or some other subtle color and change the font to Bold when the details are toggled to visible in order to distinguish that row as the header row.

In order to do this, I think I somehow need to get to a certain element of the Reporting Services object model called "Textbox.ToggleState". If I can get to that piece of the puzzle in an expression or even in code within the report, that should tell me the state of the textbox's toggle switch and I should be able to use that information to conditionally set the color of the textbox.(or every textbox in the row probably.) Here's all I could find out about this on TechNet

http://technet.microsoft.com/en-us/library/microsoft.reportingservices.reportrendering.textbox.togglestate.aspx


I just can't figure out how to get to that piece of information in a Reporting Services expression or in the report's code block.


Any suggestions? I'd appreciate a response even if it's "You can't do that." Actually, the best response someone might be able to provide might be a link to a sample showing how to get to any of the textbox properties like that.

Thanks!
Karen

View 13 Replies View Related

QP Is Missing And It Is Required To Evaluate Default Expressions?

Sep 17, 2007

Hi everyone

I have a really odd problem that has manifested its self. I have a Windows Mobile 6 device that I debugging my application on, the application uses a local Sql Compact sdf file, if I just run the app, when it comes to executing some ado.net against the database I get the following error:-


QP is missing and it is required to evaluate default expressions. Ensure that sqlceqp30.dll is in the same directory as the storage engine (sqlcese30.dll)

If however I open the the database using query analyzer first on the device then everything is ok until I next soft reset the device


Has anyone else come across this?

Thanks in advance
Dan

View 8 Replies View Related

How To Evaluate Working Day Through Script Component In SSIS

Apr 11, 2008

how to evaluate working day through script component in SSIS





I have a data source. I need to verify if the date belongs to working day, if it does belongs to holiday or weekend ,then I need to send that row to the out put table. I think the only way I can verify this working day issue through script component. Can any one give me some idea/thoughts?



You all have a wonderful weekend.



Thanks

View 17 Replies View Related

Select Case Does Not Evaluate The Value Returned By Datareader Or Dataset

Sep 13, 2006

I am working on a company sign out sheet for our Intranet. The application accesses a sql database.  I tried using a datareader as well as a dataset to access a small piece of information regarding the group that each employee belongs to in the company.  My last attempt was to assign the value to the text property of a text box and evaluate the text from the text box with the Select Case but that did not work either.The application is supposed to generate an email to our receptionist as well as to the group technical assistant responsible for each group according to the value that is passed into the Select Case statement.  With the datareader as well as the dataset and even now with creating a text box and accessing the text property, I was able to assign the group value to a variable (I can see the value in the subject of the email), but when the variable is supposed to be evaluated by the select case statement it skips right through all of the cases to case else and uses only the receptionist's address in the email as if it doesn't even see the value of the variable.   I have searched for a possible answer to the problem I am but so far have had no luck.  Any ideas?   I included the part of the code that I am having trouble with: '********************************************************************************************' New DataSet is created to determine the group the employee belongs to so that the appropriate' group technical assistant is emailed when employee signs out'******************************************************************************************** ' declare variablesDim sqlGroup As String ' string variable to store sql statement for datasetDim BLGroup As String ' string variable to store the bl group of the employeeDim emailAddress As String ' string variable to store resulting email address Dim groupDS As DataSet ' dataset variableDim groupAdapter As SqlDataAdapter ' sql data adapter variable' sql statement to access group value from the databasesqlGroup = "SELECT BLGroup FROM BLGroupsView WHERE (RTRIM(fname) + ' ' + LTRIM(lname)='" & employee & "')" ' repopen the connection (leftover from working with the datareader)Connection.Open()' create new SqlDataAdapter applying sql statement and connectiongroupAdapter = New SqlDataAdapter(sqlGroup, Connection)' create new dataset groupDS = New DataSet()' populate the datasetgroupAdapter.Fill(groupDS, "BLGroupsView") ' get the value stored in the BLGroup column (only one row is returned at a time) BLGroup = groupDS!BLGroup' Create GroupTextBox TextBox control.Dim GroupTextBox As New TextBox()' Set options for the UserTextBox TextBox control.GroupTextBox.ID = "GroupTextBox"GroupTextBox.Visible = "False"GroupTextBox.Text = BLGroup' just trying anything with this next line, even when i didn't set this the select case statement did not seem to see the value returned by the variableGroupTextBox.runat = "server"  ' use a select case statement to evaluate the value of the group (didn't work when I passed in the BLGroup variable eitherSelect Case GroupTextBox.TextCase "bh"emailAddress = "receptionist@myaddress.com; bhassistant@myaddress.com"Case "env"emailAddress = "receptionist@myaddress.com; envassistant@myaddress.com"Case "sw"emailAddress = "receptionist@myaddress.com; swassistant@myaddress.com"Case "fac"emailAddress = "receptionist@myaddress.com; facassistant@myaddress.com"Case "www"emailAddress = "receptionist@myaddress.com; wwwassistant@myaddress.com"Case ElseemailAddress = "receptionist@myaddress.com"End Select '*************************************************************************************************' set new message objectDim myMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage()'**********************************************************' set message properties'********************************************************** myMessage.From = "me@myaddress.com"myMessage.To = emailAddressmyMessage.Subject = employee & " Signing Out at " & TimeOut.Text & " " & BLGroupmyMessage.Body = employee & " Signing Out at " & TimeOut.Text & " for " & Destination.Text & " will potentially return at " & EstTimeIn.Text'***********************************************************' set smtp server namesmtpMail.SmtpServer = "mailserver1"' send email using smtpsmtpMail.Send(myMessage) It sends the message and returns the BLGroup value everytime but only sends to the receptionist.  Thanks for your time! 

View 1 Replies View Related

Evaluate Logic Output As Resulting Field From Query

Nov 25, 2005

have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.

For a very general example: In the .NET code, the following variables are recognized:
sex="M"
Paid=4350.00
Outstanding=28000.50

One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..

EVAL EXPR
BOOL Sex='F' and Paid/Outstanding < 27.50
BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F'
INT Paid*52.33

In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?

If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....

View 3 Replies View Related

Evaluate My Stored Procedure For Paging, Filtering, And Sorting

Apr 28, 2008

This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.

This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).

CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset
(
-- paging
@startRowIndex INT = 1,
@maximumRows INT = 999999,

-- sorting
@sortColumn NVARCHAR(30) = 'title_id',
@sortDirection NVARCHAR(4) = 'ASC',

-- filtering
@title VARCHAR(100) = NULL,
@type VARCHAR(30) = NULL,
@price MONEY = NULL
)
AS
BEGIN

DECLARE @sql NVARCHAR(4000)
DECLARE @parameters NVARCHAR(4000)
DECLARE @tableSource NVARCHAR(4000)
DECLARE @orderByExpression NVARCHAR(4000)
DECLARE @searchCondition NVARCHAR(4000)
DECLARE @uniqueKey NVARCHAR(30)


-- set the unique key used to ensure the rows are sorted deterministically
SET @uniqueKey = 'title_id'



-- build the FROM table source used throughout this procedure
SET @tableSource = 'titles t
inner join publishers p on t.pub_id = p.pub_id'




-- build the WHERE search condition used to control filtering throughout this procedure

SET @searchCondition = '(1 = 1)'

IF @title IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')'
IF @type IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')'
IF @price IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'




-- build the ORDER BY expression used to control the sorting throughout this procedure

SET @orderByExpression = @sortColumn + ' ' + @sortDirection

-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique
IF @sortColumn <> @uniqueKey
SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection





-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order

SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey +
' FROM ' + @tableSource +
' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant OUTPUT, @start_row_id sql_variant OUTPUT'

DECLARE @start_row sql_variant
DECLARE @start_row_id sql_variant

EXEC sp_executesql @sql, @parameters, @rowcount = @startRowIndex, @start_row = @start_row OUTPUT, @start_row_id = @start_row_id OUTPUT



-- Get the filtered subset of results

-- add sql to filter the results based on criteria passed in as parameters
SET @sql = 'SET ROWCOUNT @rowcount; ' +
'SELECT
t.title_id,
t.title,
t.price,
t.type,
p.pub_name,
p.city,
p.state,
p.country
FROM ' + @tableSource +
' WHERE (' + @searchCondition + ') AND '

-- add sql to control the starting row
IF @sortDirection = 'ASC'
SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )'
ELSE
SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'

-- add sql to control the ordering of everything
SET @sql = @sql + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'

EXEC sp_executesql @sql, @parameters, @rowcount = @maximumRows, @start_row = @start_row, @start_row_id = @start_row_id


-- Reset the rowcount for others
SET ROWCOUNT 0

END;
GO

View 14 Replies View Related

T-SQL (SS2K8) :: XQuery Syntax To Evaluate Math Formula

Feb 25, 2015

I want to evaluate a math formula inside of a function, so I can't use dynamic SQL.

I found that the query on an XML-variable with a literal works well.

DECLARE @sParsedFormula varchar(200);
DECLARE @xFormula xml;
SET @xFormula = '';
SET @sParsedFormula = '1+2';
SELECT @xFormula.query('3+3') , @xFormula.query('sql:variable("@sParsedFormula")')Output (1st value is correctly evaluated, 2nd not):
"6", "1+2"

I can't directly pass @sParsedFormula to the query otherwise I get "The argument 1 of the XML data type method "query" must be a string literal". What is the correct XQuery-syntax to evaluate the SQL:variable?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved