OK, I'm stumped. The 37 GB log file associated with my 22 GB database continues to grow--even though the database file is backed up each week and the database is not experiencing heavy use. I've reviewed numerous log-reated threads on the SQL Server forums, but I'm still confused as to why my log file keeps growing.
The recovery model for this database is set to Full. The database is backed up every Thursday night. Just the transaction log is backed up on all other nights. (I realize that I could simply change to the Simple recovery model and just backup the entire database each night; however, I was hoping that backing up the database once a week and backing up just the log six nights a week would result in much smaller usage of my hosting company's off-site backup device.)
sys.databases reveals the following for this database: log_reuse_wait=0; log_reuse_wait_desc=NOTHING. DBCC SQLPERF (LOGSPACE) returns a 'Log Space Used' of 0.1946281% and a Status of 0.
My hosting center uses Ahsay Online Backup to perform the nightly backups. The backup log indicates that the log file is being backed up each night--actually, the backup log indicates that the night after the weekly database backup, one log file is backed up, the night after that two log files are backed up, etc., right up until the sixth night when six log files are backed up. I'm not sure if this is the way backups are supposed to work or not.
It's my understanding that the log file won't truncate until SQL Server believes that the transactions in the log file are committed and (from what I've been reading) a checkpoint occurs. Is that correct or do I have a wrong understanding? In any case, do I need to truncate the log myself, perhaps as part of regular maintenance (as some of the threads indicate)? If so, how can I be sure that my truncating the file won't cause a loss of any data? Lastly, is it conceivable that the hosting center's online backup tool is somehow interfering with log process?
As I inferred above, I'm not currently doing any regular maintenance on this database, but I need to start; first, however, I'd like to find out why the log file continues to grow. I'm concerned that if I don't resolve this, the drive will fill up.
I have a transaction that calls one other sproc and also executes another set of queries, but for some reason I'm getting error 266: "Msg 266, Level 16, State 2, Procedure AddUserHaveTag, Line 26. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1."There is NO transaction in the sproc AddTag. It is also included below.Here is the sproc with the transaction:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddUserHaveTag] 6 (7 @UserHaveID int,8 @Tag varchar(24),9 @UserHaveTagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 DECLARE @ErrorCode int14 DECLARE @TagID int15 DECLARE @TagExists bit16 17 BEGIN TRAN 18 19 -- Call proc to add tag to Tags table 20 EXEC AddTag @Tag, @TagID OUTPUT, @TagExists OUTPUT 21 22 -- Check for errors 23 IF @ErrorCode <> 0 GOTO ERROR24 25 -- Check for existing record, otherwise insert 26 IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID)27 BEGIN28 SET @UserHaveTagExists = 129 RETURN 030 END31 ELSE32 BEGIN33 INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID)34 SET @UserHaveTagExists = 035 END 36 37 -- Check for errors 38 IF @ErrorCode <> 0 GOTO ERROR39 40 COMMIT TRAN 41 42 ERROR:43 IF (@ErrorCode <> 0)44 BEGIN45 PRINT 'Unexpected error occurred!' 46 ROLLBACK TRAN47 END Here is the AddTag sproc:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddTag] 6 (7 @Tag varchar(24),8 @TagID int OUTPUT,9 @TagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 14 IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag)15 BEGIN16 SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag17 SET @TagExists = 118 RETURN 019 END20 ELSE21 BEGIN22 INSERT INTO Tags (Tag) VALUES (@Tag)23 SET @TagID = SCOPE_IDENTITY()24 SET @TagExists = 025 ENDAny advice?Also if you see any glaring errors or things I could be doing better, I'm open to suggestions. I'm fairly new to sprocs and transactions. Thanks,Travis
Hi!I'm studying to have my MCSE 70-228 certification and I'm trying somethings with backing up transaction logs and shrinking it.Here's what I do:There is no activity in the database by the way.I have a transaction log of 1792 kb...I do the following command:BACKUP LOG TestDB TO TestDBBackupDBCC SHRINKFILE ('TestDB_Log',0)The transaction log is now 1280 kbI do the same command and finally my transaction log is now 1024kb...Any idea why it didn't shrink it at 1024 kb the first time?Thanks!Jeff
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON Begin distributed Tran update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 and DONE = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
I am getting this error :Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
Hi, I will give someone a script that creates a database using : create database mydatabase my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737 go use mosaikDB737
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[FileListInput]( [FileName] [char](50) NULL ) ON [PRIMARY] END
use master select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb') select * from mosaikDB737.dbo.DBlistOutput
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this. If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems FROM Table1 Inner join on table1.assettag = table2.assettag where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1 SET query1.ScheduledItems = True Where query1.Status = Scrubbed
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference? Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is successful, my return value is coming back correctly (to whatever i set it)....but if there is an error such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP. It comes back blank. (The literature I've read says that processing should continue in the SP, so you can perform error processing...is that right?)
I set the return var in my ASP as: objCommand.Parameters.Append objCommand.CreateParameter("return",_ adInteger,adParamReturnValue,4) and read it back as: strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah if @@error = 0 return(100) else return(200)
I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody
My goal:
I have been written a GPS program that has an database. The database will be replicated to an central web server. That will say one web server and x numbers of laptops that has same GPS program. :) All laptops uses internet to replicate.
Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:
Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.
Problaby I need some kind of an database on client and subscription to make the replication. :confused:
please help me, I'am almost finished with my project only replication part is over my head :(
if someone can point me to right direction in this issue. I would be greateful. :cool:
I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'xp_sendmail'.
How can I use xp_sendmail using a dabase other than Master?? Please Help.
I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together. So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?
select somedata, somedata, somedata, somedata From kpi..temptablel l left outer join @temps s on l.x = s.x left outer join @tempf f on l.x = f.x left outer join kpi..temptablee e on l.x = e.x left outer join @tempn n on l.x = n.x
where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname fromemp wheresex = 'F' and salary>(selectavg(salary) fromemp group by sex havingsex='M')
selectlastname fromemp wheresex = 'F' and salary>(selectavg(salary) fromemp wheresex='M')
also is it wise to use Group by and having in sub-queries?
Hi I have an slq Express mdf at path X and I copy it to path Y. When I open it up (from the Y path) using sql mgmt studio, it shows that it's from path X. Why? How can I get sql mgmt studio to recognize it as a separate mdf, distinct from the one at path X?
I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.
i need a database for my Windows CE application which i can update from a desktop application.
I tried the SqlCeConnection. This works good on the device, but i found out, that i need a sql server on the desktop or someone else to get access to the device server. This is a problem for me, because i cannot insall such a sever on the desktop.
So i searched and searched....I found infos about the ole connection, but i cant find the namespace?!?
Can anyone give me a hint what the best solution could be?
I'm trying to get up-to-speed on developing new websites using Visual Web Designer and SQL Server Express.
I have previously installed various Microsoft web development components (Visual Studio 2005, .NET Framework 1/2/3 and SQL Server 2005). I've also tried out the new Web Expressions Beta (and Design and Blends, altho the install keys for the latter two never worked and I never received an answer as to what to do for that in the appropriate forums).
TODAY, I'm just trying to get Visual Web Developer and SQL Server Express installed so that I can start down the path of "Connecting to an Existing Database" as outlined in the (downloaded) "Microsoft Visual Web Developer 2005 Express Edition - Build a Web Site Now!" PDF gude (page 138: "Start Visual Web Developer and display the Database Explorer window.").
HOWEVER, when I attempt to install "SQLEXPR32.exe" I get the following message after all the unpacking seems to complete:
"SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Franework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme."
NOW, my first confusion is this: is "SQLEXPR32.exe" Server 2005 or SQL Server Express -- and what's the difference? Following this bit of unhelpful error message, I searched through the SQL Server Express pages and found a live link to a "uninstall tool" that hinted that it could solve my left-over garbage problems. However, then I run that I get:
"The setup has encountered an unexpected error in datastore. The action is Write_CommitFlag .... blah-blah-blah"
so it sounds like the automatic uninstall tool has gotten lost.
ODDLY, the codish window which follows contains the following:
"
Reference to undefined entity 'nbsp'. Error processing resource 'file:///C:/DOCUME~1/Kevin/LOCALS~1/Temp/IXP000.TMP/failed....
<!-- div id="RPCreated" style="display:none"> -----------------------------^
" which very peculiarly looks like something has gotten lost and confused over a non-breakable space? (" "). Huh?
ME? I'm totally lost! Is there no step-by-step cookbook that I can use to just start all over and go through the painful (DSL) downloads and get my show on the road?
Sign me ... "depressed on the garden isle of Kauai ..."
After running a disk cleanup and defrag, I now have this icon and it tells me SQL is not connected. What is it? I am on a campus which supplies "high speed" internet connection... Please, help! I'm not savvy at any of this!
There are two properties in using CheckpointFile: CheckpointUsage and SaveCheckpoints: It says: SaveCheckpoints indicates whether the package saves checkpoints while CheckpointUsage indicates whether the package uses checkpoints.
The confusion is: If CheckpointUsage is set to NEVER, and SaveCheckpoints is set to be YES, is there any checkpoint file saved on disk even if a filename is specified? It is easy to find out through a simple testing, but the teminologies here are kind of confusion.
I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.
I need to go from this...
PKcol1 PKcol2 PKcol3 col4 col5 col6 col7 A 2007 1 Y N N N A 2007 2 Y Y N N A 2007 3 N N N Y
into this....
A 2007 1 col4 Y A 2007 1 col5 N A 2007 1 col6 N A 2007 1 col7 N A 2007 2 col4 Y A 2007 2 col5 Y A 2007 2 col6 N A 2007 2 col7 N A 2007 3 col4 N A 2007 3 col5 N A 2007 3 col6 N A 2007 3 col7 Y
Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?
I was watching the "To Do List" AJAX video created by Joe Stagner and while watching the video I noticed (in designer) Joe use a Dataset to populate a Gridview and this got me thinking. I have a few gridviews in my app at work that just use SqlDataSources, and they seem to work fine, but is it better to use Datasets ?
I have a gridview that is tied to a SQLDataSource control. As the gridview is filled some of the ASPX code calls some code in the code behind page. Where I am having trouble is that in one of these calls I need to some how pass the id filed ("LISTID")of the table for the SQL statement in the code behind page and I'm not sure how to do it. The code behind function is always using the same id and I do not know how to change it. Below is all the code.
Here is the calling code in the ASPX page. "DeleteBtn" Visible='<%# IsDeleteBtnVisible %>' runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" ForeColor="#003399"> Here is the entire ASPX code. class="header"> class="title">My Lists "Span1"> Current List Filter: "DropDownList2" cssclass="filterdropdown" AutoPostBack="true" runat="server"> "Active" Value="False"> "Done" Value="true"> --------------------------------------------------------------------------------"separator2"/> "list2"> "ScriptManager1" EnablePartialRendering="true" runat="server"> "Up2" UpdateMode="Conditional" runat="server"> "GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="LISTID" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" EnableViewState="False" GridLines="None" AlternatingRowStyle-BackColor="#FFFFCC" AlternatingRowStyle-Font-Size="Small" ForeColor="#FFFFCC" Font-Size="Small" AlternatingRowStyle-ForeColor="Black" Width="100%" HeaderStyle-ForeColor="White"> "False"> "LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update" ForeColor="#003399"> "LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" ForeColor="#003399"> "LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" ForeColor="#003399"> "DeleteBtn" Visible='<%# IsDeleteBtnVisible %>' runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" ForeColor="#003399"> "buttons"> "commands"> "ITEM_DETAILS" HeaderText="Details" SortExpression="ITEM_DETAILS" ControlStyle-ForeColor="#003399"> "name_edit"> "name"> "name"> "Pri" itemStyle-HorizontalAlign="Center" SortExpression="Priority" ControlStyle-ForeColor="#003399"> "DropDownList1" Width="75" SelectedValue='<%# Bind("Priority") %>' runat="server"> "High" Value="3"> "Medium" Value="2"> "Low" Value="1"> "pri" Text='<%# FormatPriority(Eval("Priority")) %>' runat="server"> "priority"> "#003399"> "priority"> "DUEDATE" itemStyle-HorizontalAlign="Center" HeaderText="Due Date" DataFormatString="{0:MM/dd/yyyy}" SortExpression="DUEDATE" > "Center"> "COMPLETEDON" itemStyle-HorizontalAlign="Center" HeaderText="Completed On" DataFormatString="{0:MM/dd/yyyy}" SortExpression="COMPLETEDON" > "Center"> "Done" itemStyle-HorizontalAlign="Center" SortExpression="IsComplete"> "CheckBox1" runat="server" Checked='<%# Bind("IsComplete") %>'> "iscomplete"> "iscomplete"> "Done" Text='<%# FormatDone(Eval("IsComplete")) %>' runat="server"> "Button2" runat="server" CommandName="Something" DataTextField="LISTID" DataTextFormatString="{0} active items" text='<%# Eval("LISTID") %>'> "Empty">No lists "White"> "#FFFFCC" Font-Size="Small" ForeColor="Black"> "Div1"> "Up3" UpdateMode="Conditional" runat="server"> Add New List: "AddItem2" cssclass="newitem" runat="server"> "Button1" runat="server" OnClick="AddListBtn_Click" Text="Add"> "txtdate" runat="server"> "Button1" EventName="Click"> "SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FastTrackConnectionString %>" SelectCommand="SELECT * FROM [TODO]"> Here is the code behind page code. Protected Function IsDeleteBtnVisible() As Boolean ' Return IIf(itemCount = 0, True, False) 'This is for my gridview Dim strConn2 As SqlConnection = New SqlConnection Dim cmd2 As SqlCommand = New SqlCommand Dim rs2 As SqlDataReader Dim test1 As String Dim test2 As String strConn2.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("FastTrackConnectionString2").ToString() strConn2.Open() cmd2.Connection = strConn2 cmd2.CommandText = "SELECT LISTID, (SELECT COUNT(0) AS ItemCount FROM TODO_SUB_ITEMS WHERE(LISTID = TODO.LISTID) AND (ISCOMPLETE ='False')) AS ItemCount FROM TODO WHERE (ISCOMPLETE ='False')" rs2 = cmd2.ExecuteReader() rs2.Read() Dim testlistid As Integer = rs2(0) Dim intholder As Integer = rs2(1) strConn2.Close() cmd2 = Nothing strConn2 = Nothing Return IIf(intholder = 0, True, False) End Function
I noticed that the online books say the following: Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Also, at another place in online books, it says: The table hints are ignored if the table is not accessed by the query plan. From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?
I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'xp_sendmail'.
How can I use xp_send mail using a dabase other than Master?? Please Help.
Hello, I have a table which resides on a diff.filegroup. The space allocated is 7700MB. Now ehen I use sp_spaceused 'table_name' to see how much space is left it's giving me data as rows reserved data index Unused at 1000hrs 629879 6777904 5068344 8 1709552 After an hour 637537 6780336 5070800 8 1709528 After 2hours 643883 6782560 5072904 0 1709656 After 2.5hrs 646887 6783584 5073920 0 KB 1709664 kb after 3hrs 647239 6783712 5074056 0 KB 1709656 K If you note rows are increasing,so is the reserved place and data but unused is also increasing.I think it should come down. What behavior is this?How can I find out how much space is left in this table residing on diff.file group? TIA
select count(a.callid) from tbl1 as a inner join tbl2 as b on a.calldefid=b.calldefid where a.programid=175
select count(a.callid) from tbl1 as a inner join tbl2 as b on a.calldefid=b.calldefid where b.programid=175
callid - pk on tbl1 calldefid - nonclustered index on both tbl1 and tbl2 programid - nonclustered index on both tbl1 and tbl2 tbl2 is the smaller table
from my understanding, the second query will run faster because you reduce the records in the smaller table, then join to the larger table (tbl1).
but can you explain to me why limiting the rows on tbl1 first, then joining to tbl2 would take longer?
