How To Make Sure No Other Table Writes Happen Between 2 SQL Statements ?

Nov 25, 2007

Ok, here is my situation.....

When someone navigates to a user's profile page on my site, I present them with a slideshow of the user's photos using the AJAX slideshow extender.  I obtain the querystring value in the URL (to determine which user's page I'm on) and feed that into a webservice via a context value where an array of photos is created for the slideshow.  Now, in order to create the array's size, I do a COUNT of all of that specific user's photos.  Then, I run another SQL statement to obtain the path of those photos in the file system.  However, during the time of that first SQL query's execution (the COUNT statement) to the time of the second SQL query (getting the paths of the photos), the owner of that profile may upload or delete a photo from his profile.  I understand this would be a very rare occurrence since SQL statements 1 and 2 will be executed within milliseconds of each other, but it is still possible I suppose.  When this happens, when I try to populate the array, either the array will be too small or too large.  I'm using SqlDataReader for this as it seems to be less memory and resource intensive than datasets, but I could be wrong since I'm a relative beginner and newbie.   This is what I have in my vb file for the webservice.....
Public Function GetSlides(ByVal contextKey As String) As AjaxControlToolkit.Slide()
     Dim dbConnection As New SqlConnection("string for the data source, etc.")

     Try
          dbConnection.Open()

          Dim memberId = CInt(contextKey)
          Dim photoCountLookupCmd As New SqlCommand _
               ("SELECT COUNT(*) FROM Photo WHERE memberId = " & memberId, dbConnection)
          Dim thisReader As SqlDataReader = photoCountLookupCmd.ExecuteReader()

          Dim photoCount As Integer
          While (thisReader.Read())
               photoCount = thisReader.GetInt32(0)
          End While
          thisReader.Close()

          Dim MySlides(photoCount - 1) As AjaxControlToolkit.Slide

          Dim photoLookupCmd As New SqlCommand _
               ("SELECT fullPath FROM Photo WHERE memberId = " & memberId, dbConnection)
          thisReader = photoLookupCmd.ExecuteReader()

          Dim i As Integer
          For i = 0 To 2
               thisReader.Read()
               Dim photoUrl As String = thisReader.GetString(0)
               MySlides(i) = New AjaxControlToolkit.Slide(photoUrl, "", "")
          Next i
          thisReader.Close()

          Return MySlides

     Catch ex As SqlException

     Finally
          dbConnection.Close()

     End Try

End FunctionI'm trying to use the most efficient method to interact with the database since I don't have unlimited hardware and there may be moderate traffic on the site.  Is SqlDataReader the way to go or do I use something else?  If I do use SqlDataReader, can someone show me how I can run those 2 SQL statements in best practice?  Would I have to somehow lock writing to that table when I start the first SQL statement, then release the lock after I execute the second SQL statement?  What's the best practice in this kind of scenario.

Thanks in advance.

View 3 Replies


ADVERTISEMENT

Reads And Writes To A Sql Server Database Per Table

Aug 1, 2006

Is it possible to find the reads/writes to a sql server table ?

View 2 Replies View Related

What Will Happen When A New Row Inserted In A Table Where A Cluster Index Is Defined

Sep 7, 2007

As we all know that cluster index physically sort the table. Now what will happen if I insert a new row in the table which voilating the sort rule of table.
Will it inserte at the end of table or table is reordered as cluster index always keep table in physically sorted . Please cleaar my doubt.

View 3 Replies View Related

Make A Horizontal Table Into A Vertical Table

Nov 19, 2004

Hi All,
Any assistance would be greatly appreciated.

I have a current table which I create on a regular basis from a text file with a layout similar to this:
TypePolicy #AmountRider 1 AmtRider 2 Amt
B1112H24.341212.34

This text file is brought into a staging table with each field (even the amount field) as a varchar (12). I then assign types in a later step in my DTS package.

What I need to do is stack the riders under each policy so for each policy where there is a rider, there is a new row for every rider.
So in the example I've given, there would be 2 additional rows for the original first row since there are two riders.
TypePolicy #Amount
B1112H24.34
R11112H12
R21112H12.34

I plan on doing this by first creating a table with just the Type, Policy #, and Amt fields, and then using a series of insert queries where I take the rider (if there is one) and append it onto the table.

However, I'm getting the following error message when I try:
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Basically, it wouldn't let me put an 'R1' in the Type column.
How can I get this to work!?!?

Thanks in advance for your help

View 6 Replies View Related

Naming A New Table From A Make Table Query

Mar 25, 2004

I want to use a Make Table Query to generate a new read-only Table. This I can do but my problem is that I want to be able to name the new Table containing only the records from a calendar year; e.y. Rents2001 and the next year another new table would be created and called Rents2002 and next year Rents2003 ...............

I require the Table to be generated yearly. I know I could do this in other ways but I really require the Table as once I have it I will be doing other things with it to give the final report.

Any suggestions how I can generate the Table with the YEAR being in the Table Name as part of running the Make Table Query? Thanks

View 4 Replies View Related

What Will Happen If...

Dec 13, 2007

What would happen if I deleted all the contents of the tables like MSmerge_*.

In case it needs to be known, we have a merge-replication scenario with about 25 servers under SQL 2000 SP3.

Cheers,

Kias

View 13 Replies View Related

How Long Ago Did Something Happen

Jan 7, 2008

Hey, ive got a listof events that have occured on my site, updates etc. and Im trying to show how long ago the updates happened. For instance, say the date of an update is : 16/10/2007 15:16:03 I want the Label to say "Happened over 2 months ago" etc.Now ive tryed to use an IF statment but I cant seem to get it right :         DateTime dt = Convert.ToDateTime("16/10/2007 15:16:03");        if(dt.ToShortDateString() == DateTime.Now.ToShortDateString())        {   //happened today            if(dt.ToShortTimeString() == DateTime.Now.ToShortTimeString()||dt.ToShortTimeString() < DateTime.Now.AddMinutes(-1).ToShortTimeString())            {   //happened within a minute                UpdateLabel.Text = "About A Minute Ago";            }        }        else if (dt.ToShortTimeString() == DateTime.Now.AddDays(-1).ToShortTimeString())        {   //happened yesturday            UpdateLabel.Text = "Updated Yesturday";        }        else if (dt.ToShortDateString() == DateTime.Now.ToShortDateString() || dt.ToShortDateString <= DateTime.Now.AddDays(-7).ToShortDateString())        {            UpdateLabel.Text = "Updated Last Week";        }Any ideas where Im going wrong? Im probally staring it straight in the face, but I cant see it. Thanks in advance John 

View 1 Replies View Related

What Will Happen -- ROLLBACK Or NONE?

Jul 14, 1999

Hi,

what will happen in the following occasion?

checkpoint --> begin tran --> system failure

In MOC (SQL 7.0 Implemetation), it says there will be rollback, but in my humbel opinion, there should be nothing happened.

I think there was no dirty writing in this case. No data pages written, no log
pages written to disk. Am I right? Then, there might be nothing happened
instead of ROLLBACK.

If I am wrong, would you please let me know what is wrong in my thougt?

Thanks in advance,

View 2 Replies View Related

It Can&#39;t Happen, SQL Locks Up

Dec 26, 1999

I am using SQL Server 6.5, when two or more independent applications put transactions through SQL, it locks up. Example of locks up.

When the OrderLines table is locked, then I put the following (Select * from OrderLines) then the query does not return any values, the world goes round and round, the only way out is to shut down and cross my fingers whilst SQL goes into recovery mode.

I have read through some of the documentation, such as deadlocks, livelocks and lock starvation but it say none of these will lock the whole machine. But somehow simultaneous transactions can, and the current activity dialog goes red, bright red.

Any ideas?

View 1 Replies View Related

Serious SQL Problem - Anyone Had This Happen?

Nov 22, 2002

I have a SQL 2000 server(sp2) on Win2000 SP2. My largest database is about 5 gig. Log shipping went astray and had to reinitialize. WHen I did the part where it actually backups the init db to disk failed with the following

3041 :
BACKUP failed to complete the command BACKUP DATABASE [Development] TO DISK = N'G:SQLBACKUPdevlog' WITH INIT , NOUNLOAD , NAME = N'Development backup', NOSKIP , STATS = 10, NOFORMAT

In the NT event log I got the following

{Lost Delayed-Write Data} The system was attempting to transfer file data from buffers to DeviceLanmanRedirector. The write operation failed, and only some of the data may have been written to the file.

MS had an article on this

http://support.microsoft.com/default.aspx?scid=kb;en-us;293842.

There solution is Win2000 SP3. Has anyone had these errors before.

View 1 Replies View Related

Do Indexes Help Writes?

Sep 26, 2005

I have an application that is insertting thousands of records houlry. The server's hard drives are staying maxxed out. My boss says there is an index problem. I say it is a drive subsystem issue.

Any help would be appreciated to understand this performance problem.

View 2 Replies View Related

Reads / Writes Per Second.

Oct 30, 2006

How can You find the reads and writes per second of your hard drives in sql. I am reading my SQL book and it says that your average disk should have 125 or less i/o's. And it gave the forumal but as mentioned I don't know how to find the reads and writes.

View 4 Replies View Related

Writes Are Not Commited Right Away

Mar 25, 2008

Hi!

First of all I want to tell you that I'm not a dba or tuning expert but I've ran a trace on a database with perfomance problems and I've found a strange thing.

The user creates orders for their service people in the organisation. I can see in the trace that inserts are done but they don't produce any writes rightaway. However after 10-15 minutes all the writes are done, what could make the actual write be delayed so much. The application is developed using .net.

/Magnus

Jesus saves. But Gretzky slaps in the rebound.

View 8 Replies View Related

Move LDF -Has Anyone Seen This Happen?

Jul 20, 2005

HiWe have a SQL 2000 (sp3) server with a database that I set up to have theMDF on D: and the LDF on E:. All was going along fine for several months andone day the server rebooted. The SQL log says something like "Service isshutting down due to server shutdown."When it came back up the LDF File was on D: (in the same folder as the MDF).The original LDF was gone from E:The SQL Log doesn't ever say anything like "Recreating LDF in defaultlocation." or any anything else that would explain what happened.I assume the change happened during the reboot (actually I didn't notice ituntil a week and a half later). I am relatively sure no human did anythingthat caused the log file to move.So has anyone seen this happen before.?TIA-Dick

View 1 Replies View Related

Just Where Does The Work Happen?

Feb 27, 2008

Hi everyone,

I'm relatively new to SSIS - i've played with the technology a little and i've created some DTS packages previously.

I'm in a scenario where I have 3 servers with an instance of SQL 2005 installed. Server A is a live server with lots of statistical data, Server B is a backend server specifically provided for handling heavy data processing. Server C is another live server, but it's essentially empty.

I would like to transfer some data from Server A, summarise it using the processing power of Server B and then store the resulting summary data in Server C. One of my main requirements is to have minimum processing performed on Server A and C.

I created an SSIS package which produces the required results, but I've no real idea where the hard work is being performed. For example, in my SSIS i have an OLEDB data source which uses a select statement with a couple of parameters to determine which data to return from Server A, i then have an aggregate transformation on the output before an OLEDB data destination for Server C. So where does the aggregation get performed?

Im hoping because i'm running my SSIS package on Server B that in the above instance, all the source data is pulled onto Server B (maybe into a tempdb?) and then aggregated by Server B before then being transferred onto Server C.

Please let me apologise in advance for the lengthy post - but just trying to explain this to others may help me work it out in my head (at the very least)

thanks,
Andrew

View 5 Replies View Related

Two Select Statements On One Table

Apr 1, 2004

Hi,

newbie here.

Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this.

SELECT column1
FROM table1
WHERE column2 = (select column2 from table1 where column3 = 14)

Any ideas greatly appreciated.

View 1 Replies View Related

How To Get Statistics On DML Statements Per Table

Aug 18, 2006

Can I get statistics on which type of DML statements (e.g. insert, delete, update) that are executed by users on a table without creating triggers? I want to be able to show the number of executed statements per statement type. I have tried the 2005 Profiler but it outputs the entire batch statement which makes it a bit more difficult to create statistics.

Rgds

Bob



View 1 Replies View Related

What Happen To My Sql Server 2000

Apr 9, 2008

 im using Vb2005 and Sql server 2000.when i open enterprise manager n try to make the connection, then i got error msg like this" A connection could not be established to (LOCAL)Reason: SQL server does not exists or access denied.ConnectionOpen (Connect()) ..Please verify SQL Server is running and check your SQK Server registration properties (by right-clicking on the (LOCAL) node) and try again" When i open sql server service manager and try to start my sql, its do not change from stop to start.. and one more thing is, if  i plan to use sql server 2005..do i need to uninstall sql server 2000? 

View 6 Replies View Related

COUNT Of READS And WRITES On A 6.5 Db.

Jul 21, 2000

Is there a way to get a total count of all SELECT, UPDATE, DELETE and INSERT statements to a SQL Server 6.5 database during a 12 hour period? I'm thinking maybe someone knows of a software that reads the log or monitors the server... I've been looking at the performance monitor and, although it has good information, it doesn't capture DML's.

FYI - it's for capacity planning.

TIA,
Mike

View 1 Replies View Related

Track Reads And Writes

Mar 5, 2008

GUys,

Is there any way track tables which have most no of reads and writes from a database of 400 tables.

Thanks

View 9 Replies View Related

Viewing Events As They Happen?

Mar 5, 2007

Hi,I am trying to debug some queries that are being generated by anexternal program, and I have no way of finding out what the actualsyntax of the query is from within the program itself. This means thatwhen the query fails I am left with only a very unhelpful message.So what I was wondering, was whether there is a way that I can view theactual queries that are sent to SQL server as they happen, or if areal-time solution doesn't exist, then some way I can look back afterthe event and see the syntax of the queries?Ideally, what I would like to do is see the queries in the form theywere sent to the server, ie "SELECT * FROM foo WHERE bar='foobar'", asthis would help me to figure out where the generated queries are goingwrong.Cheers,--Dylan Parryhttp://electricfreedom.org | http://webpageworkshop.co.ukThe opinions stated above are not necessarily representative ofthose of my cats. All opinions expressed are entirely your own.

View 5 Replies View Related

When Does Transaction Doom Happen.

Aug 23, 2006

I got the following message..What does it mean and where it happens? By reading few articles it seems that if the Rollback is not the first statement within the catch block, then this error might occur. However I want to know why it happens? I am not using any trigger in my app.

Transaction doomed in trigger

View 4 Replies View Related

Make A Backup Of A Table

Apr 27, 2007

What is the best way (short of backing up the entire DB) to make a copy of a Table so that It can be easily restored. We have a table that we want to make some serious changes to, but I want to make sure I can restore if if I need to (if the changes don't work)

View 10 Replies View Related

Make Table Unreadable

Sep 19, 2007

Hi all, I have a problem. I need a query that blocks table and makes it unreadable. I decided to use WAITFOR to long blocking.


BEGIN TRAN myStopReadTrans

USE MyDatabase
SELECT * From dbo.AB with(readpast,updlock)

WAITFOR DELAY '1:00:00'

COMMIT



USE MyDatabase
Select name from dbo.Clients

View 10 Replies View Related

How To Make Table Name Dynamic

Dec 31, 2007



Hi,

In my application i need to access mutiple table. I'm writing a stored procedure
in which i need to access tables such as TB01,TB02 .. Basically TBFY
where FY is parameter.

I tried for OPENQUERY, but that needs me to add a linked server, which i don't
think is a good idea.

Can anyone suggest on how can i do so?


I'm using SqlServer2000.

Thanks.

View 6 Replies View Related

How To Make Table Name Dynamic

Nov 19, 2007

Hi,

I have a requirement where i need to build the table name dynamically in the following queries.


declare @REF_DATA_TYPE nvarchar(20)

set @REF_DATA_TYPE='COUNTRY'



these are 4 cases where i need to use the table name dynamically

1. IF exists(select 1 from 'MD_REF_'+@REF_DATA_TYPE where code=@code_T)

2. Update 'MD_TB_REF_'+@REF_DATA_TYPE

3. from @ACTUAL_DATA p join 'MD_REF_'+@REF_DATA_TYPE T on T.code=P.code

4. INSERT INTO 'MD_REF_'+@REF_DATA_TYPE(Code,[Name],Description)


But i am getting error when i do this.

Please let me know what to do to solve this

Thanks in advance

View 5 Replies View Related

Help Required W.r.t Alter Table Statements

Aug 7, 2001

Hello ,
I am having a problem related to Alter table syntax in MS SQL Server .
What I want to do is in a single alter table statement I want to add as well as modify columns in a table.

For E.g.
alter table testtable add fld1 numeric(10),
alter column fld2 numeric(15)
But I am getting an error.are these kinds of statements possible in MSSQL server ?I was able to do so in Oracle.I just wanted some confirmation or examples of whether this is possible in MS SQL Server also.
Fast Help would be extremely appreciated.
Thanks,
Anand

View 2 Replies View Related

Dynamic SQL Reading Statements From Table

May 1, 2007

Hi,I'm using a 3rd-party app's back end which stores SQL statements in atable, so I have no choice but to use dynamic SQL to call them (unlesssomeone else knows a workaround...)Problem is, I can't get the statement to run properly, and I can't seewhy. If I execute even a hard-coded variation likeDECLARE @sql nvarchar(MAX)SET @sql ='SELECT foo FROM foostable'sp_executesql @sqlI get: Incorrect syntax near 'sp_executesql'.If I runsp_executesql 'SELECT foo FROM foostable'I get: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.which I understand, as it's omitting the N converter--so if I runsp_executesql N'SELECT foo FROM foostable'it's fine. I don't understand why the first version fails. Is it somesort of implicit conversion downgrading @sql? Every variation of CASTand CONVERT I use has no effect.This is SQL Server 2005 SP2. Thanks in advance.

View 11 Replies View Related

Table Variables And Update Statements

Jul 20, 2005

HiI'm using the SQL 2000 table variable to hold 2 different fact sets.I'm declaring the variable @CurrentTable and inserting into it using aSELECT statement with no problems.I'm leaving certain of the columns null in order to later update themwith the PK.Problem is in the UPDATE syntax I'm usingUPDATE @CurrentTableSET ManagerTitle = (select mgrs.pos_title from mgrs) wheremgrs.pos_num = @CurrentTable.MgrPosNumIt is insisting I declare the @CurrentTable variable when I try to useit in the where clause.Is it simply out-of-scope or am I really doing something foolish?Andrew

View 2 Replies View Related

Transact SQL :: Select (sum) Statements With Two Table?

Jun 5, 2015

I have a database with two tables (Table1 and Table2)

looking for something like:

Table1:
+----+-------+----------+
| id   | Name | email    |
+----+-------+----------+
|  1 | name1 | mail1    |
|  2 | name2 | mail2    |
|  3 | name3 | mail3    |
|  4 | name4 | mail4    |
|  5 | name5 | mail5    |
|  6 | name6 | mail6    |
+-- +-------+----------+

Table2:
+----+------------+---------+------+
| id   | table1_ID | fee       | type |
+---+-------------+---------+------+
|  1 |     1           | 20000  |     1 |
|  2 |     3           | 1000    |     1 |
|  3 |     1           | 10000  |     1 |
|  4 |     3           | 1000    |     1 |
|  5 |     5           | 500      |     1 |
|  6 |     5           | 500      |     2 |
|  7 |     1           | 60000  |     2 |
|  8 |     2           | 1000    |     2 |
+----+------------+---------+-------+

i want the query that return:

+--------+-------+---------------------------------+---------------------------------+--------+
| name  | email | a:sum(fee) where type=1   | b:sum(fee) where type=2   |  b/10  |
+--------+-------+---------------------------------+---------------------------------+--------+
|name1 | mail1 |         30000                        |       60000                         |  6000|
|name2 | mail2 |           0                              |        1000                          |  100    |
|name3 | mail3 |          2000                         |          0                              |   0       | 
|name4 | mail4 |           0                              |          0                              |   0       |
|name5 | mail5 |          500                           |         500                           |   50     |
|name6 | mail6 |           0                              |          0                              |   0       |
+--------+-------+---------------------------+---------------------------------------+---------+

View 4 Replies View Related

Transactions And Create Table Statements

Jan 27, 2007

I have some problems getting my SSIS package to run in a transaction, I wonder if anyone can assist.

What I want to do is run one package, which consists of a

- 1) create staging table SQL statement

- 2) read (all) from Access MDB to staging

- 3) copy (only new) from staging table to real table

- 4) drop the staging table

I changed the package to "require" a transactions, and left all containers in it to "supported" (default, I think). I left all the transaction type to default "serializeable".

The package does not work, since the table I created on step one is not "seen" when step 2 wants to insert into it. If I set the package transaction back to "supported" (default) the package works, but an error on step 2 or 3 will not rollback changes and not remove the staging tables...

So, my question really is: How do I make step 1 results visible to step 2 in the same transaction? Or do I need to take a completly different approach for this?

Stupid question I think, but I seem to not be able to find the right way to handle this situation...

Thanks for reading!

Ralf

View 4 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View 1 Replies View Related

Required Field Empty - How Could This Happen?

Feb 21, 2008

Hello! I have a web form in VB.NET & ASP.NET 1.1, with data being saved to a SQL 2005 database with multiple tables. The primary key on all these tables is a unique identifying number that the user enters. However, one record in this database (across all tables) has no identifier. All other information seems to have saved without problem, but there's no identifier, which is very wierd for several reasons: 1) the form should not allow the user to proceed without it; 2) all the tables in the database do not allow nulls for the column, and 3) it's the primary key!
Any thoughts on how this might have happened?
Thanks!
Kaiti

View 2 Replies View Related







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