Simultaneous Snapshots?
May 2, 2006
If multiple snapshots for the same report attempt to generate at the same second, is there something in place to prevent them from conflicting with each other?
Our SSRS 2005 application will use a console app that we are writing to change the default parameters on each report and call the CreateReportHistorySnapshot method. This application will be multi-threaded, so there is a possibility that multiple versions of the same report with different parameters will attempt to run at the same time. We need to be sure that these snapshots will not conflict and return the appropriate SnapshotHistoryID for that report run.
From looking at the History table in the ReportServer database it looks like there are uniqueIDs and other keys that would prevent duplicates, but what if the requests come in at the same time? Is SSRS 2005 smart enough to delay for a second so that there is not a duplicate? Since the way you reference these snapshots in your URL is with the Snapshot Time in UTC format, that only goes out to full seconds (2006-05-02T00:00:00).
Sorry for the length, but I could not think of how to condense this.
Thanks,
Steve
View 4 Replies
ADVERTISEMENT
Mar 12, 2006
I have C# Express and the SQL Express Management Studio CTP installed. So far, I've been forced to disconnect from the server in one app to connect to it in the other. I understand there is something around a 4 connection limit in SQLServer Express, so I don't see the problem.
During one of these episodes, SQL Mgmt Studio hung while opening DB properties, then the SQLServer service couldn't be stopped. Now the DB can't be opened even after a reboot.
Help?
View 10 Replies
View Related
Mar 29, 2000
How can I simulate 1,000 simultaneous users in my database?
View 3 Replies
View Related
Mar 22, 1999
Is there a limit to the # of tasks ( Dumps) to a HD device ,which SQL 6.5 can perform simultaneously?
View 1 Replies
View Related
Oct 22, 2007
Hi,
I have an application resource that is accessible from multiple clients applications that all have access to a common SQL Server db. By this, I mean, multiple clients can potentially connect to this resource at any given time. I want to limit the usage of this resource to one client at a time and was thinking about simply using SQL to maintain the name of the client that currently has "control" of the resource.
My question pertains to the case where two clients are executing a SQL query, simultaneously, to check whether someone has control of the resource or not. If two users were to simultaneously execute this query, do i have to lock the table in order to ensure that both users are not viewing data in an incorrect state? Or, does SQL Server ultimately execute the queries in a specific order? Meaning, the requests are simultaneous from a human standpoint but one request may have actually come in a millisecond before the other one and SQL server executes that query first, and then the second query. Is this how it works?
Thanks,
Paul
View 2 Replies
View Related
Feb 14, 2008
Hi All,
I have a Windows 2003 Server. Planning to use SQL Server to act as the main Database Server which needs to cater to over 100's of simultaneous connections.
Can anyone share with me the default maximum simultaneous connections and the maximum allowed simultaneous connections for the following versions:
- MSDE
- 2000
- 2005 Express
- 2005
Hope I can get more professional advice from you guys.
Thanks.
View 1 Replies
View Related
Jul 11, 2006
Using the Import/Export Wizard, it is easy to set up SSIS jobs to import multiple tables in one task. However, SSIS appears to grab a connection to the source database and the destination database for each table indicated in the task sometime prior to doing the actual data transfer. Even though the transfer seems to be done a few tables at a time, all the connections are held throughout the execution of the task.
This has two effects:
1. The task may fail if the number of connections exceeds the limit of user connections set for the database
2. There is a severe impact on the other users of the databases (in my case, the source is a production system)
Is there any way to control the number of simultaneous connections that SSIS data transfer packages initiate?
I can, of course, define the package to have a small number of tables (thus limiting the connections), but in real life I need to transfer almost 700 tables, so limiting the number of tables per task to 20 or 25 produces an awkwardly large, and error prone, number of tasks. And since this definition must be done for multiple (not quite identical) databases, the extra effort of defining small collections of tables is signifcant.
View 4 Replies
View Related
Aug 14, 2007
Greetings!
Are there any limit of simultaneous users and/or connections when running SQL Express? The free license of SQL Express, does that include use by any number of users/devices?
Regards
Harald Hedlund
View 5 Replies
View Related
Apr 7, 2008
Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables?
For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?
View 3 Replies
View Related
Jul 20, 2005
hello to all,there are two databases named A and B.One database contains employee id as primaryI have another project database which includes that employee id asforeign key. How can I reflect my updations,deletionsthank you in advance,vishnu
View 2 Replies
View Related
Oct 15, 2007
Is there a way to get the Report Manager to allow a single user to
execute more than 1 report at a time? Currently, each user can only
have a single report being executed at a time. It looks like any
other reports are being queued and executed serially.
View 1 Replies
View Related
Oct 23, 2007
How can I prevent a package, or a section of a package, from being run more than once simultaneously? The package makes use of a staging database table, and if two copies of this package are run at the same time, there will be a race condition and possible corruption of the data in the database table. I have also noticed that the SSIS logging gets messed up and starts overwriting itself if I have more than one of these packages run at the same time.
What I am thinking of should work the same way as a serializable transaction lock on a normal query, where one query has to wait if another query has a lock on the table. I don't want the package to throw an error, I want it to wait until the other one is done.
I have been trying to use transactions in a test SSIS package, but they are not quite doing what I want them to do. I put various SSIS steps in a sequence container and require a serializable transaction. I have also tried putting the transaction at the package level, but that is not preventing simultaneous package runs.
Is there a built-in way to do this that is easy to implement? Otherwise, what I could do is insert a flag into the database indicating that a package is running, then make sure to reset the flag on any error or on completion. It seems like this alternate method could be error-prone though.
I am using SP2.
View 8 Replies
View Related
Feb 8, 2007
I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are:
tbltest
ECID – int (PK)
View 2 Replies
View Related
Mar 30, 2001
Hi,
could somebody please let me know what is the maximum number of simultaneous users/connection per single SQL Server 7.0 box? Licenses are not the issue here, rather the number of physical connections supported before another server needs to be purchased.
Another variation of this question would be what's the highest number of databases that 7can be created on SQL 7.0 server and what is the highest number of physical connections supported per database? Again, licenses are not the issue. Thanks a lot in advance.
View 2 Replies
View Related
Sep 28, 2007
I was going over this article , and was intrigued by this statement "If SQL Server 2005 Express is running on Windows XP Home, it is limited to five simultaneous connections. If it is running on Windows 2000 or Windows XP Professional, it is limited to 10 simultaneous connections. However, these are limitations of the operating system and not of SQL Server 2005 Express."
What are simultaneous connections? Is this related to Max worker threads defined as 255 on other editions? How does underlying OS impose how many connections can be done to SQL server. And finally how many simultaneous connections can be done in Windows Server 2003?
View 11 Replies
View Related
Jul 26, 2007
Hello
I am trying to call the same package with different starting parameters using asynchronous method calls.
My code is
namespace ReconHost
{
public partial class HostContainer : Form
{
public delegate void InvokePackageHandler(
Microsoft.SqlServer.Dts.Runtime.Application app,
int var
);
Microsoft.SqlServer.Dts.Runtime.Application app;
public HostContainer()
{
InitializeComponent();
for (int i = 0; i < 2; i++)
{
app = new Microsoft.SqlServer.Dts.Runtime.Application();
InvokePackageHandler asyncInvokePackageHandler = InvokePackage;
asyncInvokePackageHandler.BeginInvoke(app, i, null, null);
}
}
public void InvokePackage(Microsoft.SqlServer.Dts.Runtime.Application app, int var)
{
Package pkg = app.LoadPackage(@"c:GRSGRSGRSTest_Async.dtsx", null);
pkg.Variables["intVar"].Value = var;
pkg.Execute();
pkg = null;
}
}
}
If I set the for loop to loop just once, the package is executed fine. Any more than once and only one instance of the package is executed.
Does anyone know what I am doing wrong? I know it is possible to execute the two package instances simultaneously because you can use DTexecUI.exe on two clients to do this.
Thanks in advance
Tomo
View 3 Replies
View Related
Mar 26, 2007
Have 3 reports that runs using a shared schedule to generate historic snapshots. This has be working for months and months but out of nowhere it changed.
So now when ever the schedule runs it will generate the historic snapshot for 1 report but NOT the 2 remaining. As a strange twist the report that is successful changes from time to time...
SQL Server 2000, RS+SP2.
Any ideas?
Thanks
Regards
Jonas
View 2 Replies
View Related
Jul 30, 1998
Hi everyone!
Is it possible to control the number of simultaneous connections one login id can have?
I`d like to avoid my users to share their login ids and passwords to help them enter data into the system.
Thanks,
Mauricio
View 5 Replies
View Related
Dec 1, 2005
Hello everyone, I am hoping someone can help me with this problem. Iwill say up front that I am not a SQL Server DBA, I am a developer. Ihave an application that sends about 25 simultaneous queries to a SQLServer 2000 Standard Edition SP4 running on Windows 2000 Server with2.5 GB of memory. About 11 of these queries are over views (all overthe same table) and these queries are all done from JDBC but I am notsure that matters. Anyway, initially I had no problem with thesequeries on the tables and the views with about 4 years of information(I don't know how many rows off hand). Then we changed the tables toreplicated tables from another server and that increased the amount ofdata to 15 years worth and also required a simple inner join on 2columns to another table for those views.Now here is the issue. After times of inactivity or other times duringthe day with enough time between my test query run I get what lookslike blocking behavior on the queries to the views (remember these allgo to the same tables). I run my 25 queries and the 11 view queriesall take about 120 seconds each to return (they all are withinmilliseconds of each other like they all sat there and then werereleased for processing at the same time). The rest of the queries arefine. Now if I turn around and immediately run the 25 queries again,they all come back in a few seconds which is the normal amount of time.Also, if I run a query on one of views first (just one) and then runthe 25 queries they all come back in a few seconds as well.This tells me that some caching must be involved since the times are sodifferent between identical queries but I would expect that one of thequeries would cache and thus take longer but the other 10 would befast, not all block for 2 minutes. What is more puzzling is that thisbehavior didn't occur before where now the only differences are:1) 3 times more data (but that shouldn't cause a difference from 3seconds to 120 and all tables have been through the index wizard with aSQL trace file to recommend indexes)2) There is now a join between 2 tables where there wasn't before3) The tables are replicated throughout the day.I would appreciate any insight into this problem as 120 seconds is waytoo long to wait. Thanks in Advance.Chris
View 1 Replies
View Related
Mar 30, 2007
Hi everyone. I hope that my question isn't too broad, but here it goes...
I am trying to figure out the best way to scale a SQL Server database so that it can handle a billion simultaneous users querying the same tables, and can easily scale to handle many billions of simultaneous users. The database must also have 999.99% availability. The number of licences and amount of hardware needed is not an issue.
Thanks in advance.
View 15 Replies
View Related
Jul 27, 2006
Hi Everyone,
I have a software solution that consists of two separate applications that share a SQL Server Express database on a single computer. Everything is working great, except when both of the applications want to access the database at the same time. All I can find is information on configuring multi-user access in a distributed system. I'd appreciate any information on configuring multi-application access on a single computer.
Thanks,
Mike
View 4 Replies
View Related
Jul 13, 2005
We will soon be going to a new ERP system using MS SQL 2000. I'm looking into possible backup strategies. The database size will be about 50 Gigs.
We have a SAN, which the new ERP system will be backed up on. I know a little about a SAN, but I need to make decisions about the types of backups to make. We can have 10 active snapshots, so I could use snapshots during the day as a point in time backup. I could also use the SAN snapshot instead of using a full backup. Would it be safe to use the SAN snapshots instead of the normal SQL Server backups? I’m not sure how long it will take to do a full SQL Server backup because the server is still in testing mode. I’m not getting the big picture about when to use snapshots and when to use regular SQL Server backups.
Is it possible in Windows 2000 Server to stop the transactions and flush the buffers for a few seconds to do a snapshot? From what I understand, the database could be left in a suspect state if you use snapshots to restore a database.
Thanks in advance for any help!
View 5 Replies
View Related
May 16, 2008
I think I've read some conflicting advice in BOL. Maybe someone can clarify it for me.
Under "Database Mirroring and Database Snapshots" it says:
"You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot"
To my mind, to enjoy a noteworthy performance gain, that mirror database would need to be on another server.
But then you read under "Database Snapshots":
"Multiple snapshots can exist on a source database and always reside on the same server instance as the database."
So how do you get the snapshot to the mirror?
View 2 Replies
View Related
Nov 9, 2007
HiWhat would be the quickest way to create a backup and revert programon an sql (2000) database?- Can you create a transaction on a database, regardless of theconnections and thenrollback it all via an external program- Could you monitor the changes with profiler and then reverse those?- If desperate, could you backup the db from a tool and then restoreit? (too slow to be practical?)Not sure how to do this so any offers would be appreciatedta
View 3 Replies
View Related
May 13, 2008
Hopefully not too much of a stupid question.
At the moment, we are using SSRS to report off an Oracle database, if we take a snapshot of the data on the Oracle database and put it within SQL, will we expect an increase in performace/response.
And is it a good idea to keep the snapshot in the same place where SSRS is installed?
Thanks in advance.
View 1 Replies
View Related
Mar 12, 2008
I want to share one of my doubt about SSRS and report server,
if i use snapshots or cache to my reports in report server, is it going to make increase in performance?
if it is going to make increase in performance,then SQLSERVER DATABASE is going to have any burdens,
means is it going to down the performance of Database?
Pls give me Responce to my Questions....
View 3 Replies
View Related
Jun 15, 2007
Hi
I am creating a full Merge replication setup between two servers
Is there any way that I can set it up so that I do not have to create a snapshot.
Ie I would gurantee that the data and structutre is EXACTLY the same on both the subscriber and the publisher.
The problem is that my data base is over 4GB and it is quicker for me to get the data from one server to the other by doing a backup and restore.
I am using SQL 2005 with the latest SP
Cheers
Ants
View 4 Replies
View Related
Mar 20, 2007
Hello, I have one store procedure that writes something data to one physical sql table 'MyTable' and on beginning I first call:DELETE FROM MyTableProcedure returns at the end data from 'MyTable' as on SELECT. PROBLEM:I started SQL Manager on my Laptop and on Computer near me and all pointed to same database (on Server) and I run that procedure simultaneously on Laptop and Computer at the same time (two hands on F5 button on these computers). Occasionally when clicking with both hands at the same time I got no records at the end of execution (on both computers), otherwise I got the results.WHAT I THINK IS PROBLEM:Somhow one SP start working and it did not come to the end and another computer called this SP and execute first command (DELETE FROM MyTable) and I got empty records at the end..WHAT I NEED:I need to prevent this situation to get empty records. All computers must get these records always. How I can use some sort of LOCKs to do this?Or to do that on C# application level using LOCK command? Thanks in advanceAleksandar
View 2 Replies
View Related
Aug 28, 2007
I am having the huge db of 80 GB and trying to configure merge replication. The intial snapshot application is failing due to some schema issues. I have made some necessary changes to avoid the schema issues. If I try start the merge agent it is going to re-initialize the snapshot and reapplying the whole snapshot will take atleast 10-15 hrs.
Is there any way to resume the snapshot from the point of failure.. avoiding the reapplying all the data which has already got transfered to subscriber.
View 1 Replies
View Related
Apr 30, 2007
Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
View 1 Replies
View Related
Aug 7, 2006
Hi
I have setup my web synchronisation with very few problems until now. Initially I set the snapshots to generated by the subscriber when they first synchronise, this worked perfectly every time until a bottleneck occured after I reinitialised all the subscribers. When more than one subscriber then initialises the snapshot agent, the snapshot jobs fail because there is already a job running for user distributor_admin. The agent then retries etc, etc.. until all retries have failed. Ultimately only one of the snapshots is successfully created only after all other jobs have failed and run out of retries, it is then left alone to carry out its job.
Following this I made the decision to run with pre-generated snapshots instead so that I can schedule the server load and avoid these bottlenecks. Using the same publication I unchecked the option "Automatically define a partition and generate..." in the publication properties and proceeded to add each of the partitions specifying the correct "Host_Name()" value etc. After this I selected one partition and clicked "Generate the selected snapshots now". The snapshot was duly created but when synchronised the snapshot is not collected and used. Weirdly the merge porcess begins enumerating changes at the publisher followed by downloading 3100 chunks of data before receiving an error concerning the format of the message from the distributor.
If I check the option to allow auto generation, the synchronisation begins absolutely perfectly but only once it has generate a snapshot (Ignoring the one I created previously).
To add further weirdness, I created a small app which would generate the dynamic partitions using RMO. I used the sample code implicitly from BOL and it duly generated the dynamic snapshot job. I can then create a new Job class passing it the JobServer and JobName, it gives me the correct job but I cannot then call the Start() method because it keeps telling me the Job object is not created even though It is clearly enabled as a job in SSMS and can be right_clicked and started from there.
Even partition jobs created from within the SSMS appear as state "Creating" when I make an instance of it using the Job class.
What am I missing? Are my pre-generated snapshots not available to the subscriber because they seem to still be in the creating state? Is this a SQL Server configuration issue or have I setup the publication incorrectly?
Any help would be much appreciated.
Many thanks
Rab
P.S. Code snippet below
Imports Microsoft.SqlServer.Replication
Imports Microsoft.SqlServer.Management.smo
Imports Microsoft.SqlServer.Management.smo.agent
Imports Microsoft.SqlServer.Management.Common
Public Class frmSnapshot
Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
Dim hostName As String = txtSiteID.Text.Trim
' Define the server, database, and publication names
Dim publisherName As String = "WDU340"
Dim publicationName As String = txtOrgID.Text.Trim
Dim publicationDbName As String = publicationName
Dim distributorName As String = publisherName
Dim publication As MergePublication
Dim partition As MergePartition
Dim snapshotAgentJob As MergeDynamicSnapshotJob = Nothing
Dim schedule As ReplicationAgentSchedule
' Create a connection to the Distributor to start the Snapshot Agent.
Dim distributorConn As ServerConnection = New ServerConnection(distributorName)
Try
' Connect to the Publisher.
distributorConn.Connect()
' Set the required properties for the publication.
publication = New MergePublication()
publication.ConnectionContext = distributorConn
publication.Name = publicationName
publication.DatabaseName = publicationDbName
' If we can't get the properties for this merge publication,
' then throw an application exception.
If (publication.LoadProperties() Or publication.SnapshotAvailable) Then
' Set a weekly schedule for the filtered data snapshot.
If RunJob(publication, snapshotAgentJob, distributorConn, hostName) = False Then
schedule = New ReplicationAgentSchedule()
schedule.FrequencyType = ScheduleFrequencyType.OnDemand
'schedule.FrequencyRecurrenceFactor = 1
'schedule.FrequencyInterval = Convert.ToInt32("0x001", 16)
' Set the value of Hostname that defines the data partition.
partition = New MergePartition()
partition.DynamicFilterHostName = hostName
snapshotAgentJob = New MergeDynamicSnapshotJob()
snapshotAgentJob.DynamicFilterHostName = hostName
' Create the partition for the publication with the defined schedule.
publication.AddMergePartition(partition)
publication.AddMergeDynamicSnapshotJobForLateBoundComClients(snapshotAgentJob, schedule)
RunJob(publication, snapshotAgentJob, distributorConn, hostName)
End If
Else
Throw New ApplicationException(String.Format( _
"Settings could not be retrieved for the publication, " + _
" or the initial snapshot has not been generated. " + _
"Ensure that the publication {0} exists on {1} and " + _
"that the Snapshot Agent has run successfully.", _
publicationName, publisherName))
End If
Catch ex As Exception
' Do error handling here.
MessageBox.Show(String.Format( _
"The partition for '{0}' in the {1} publication could not be created.", _
hostName, publicationName) & ": " & ex.Message)
Finally
If distributorConn.IsOpen Then
distributorConn.Disconnect()
End If
End Try
End Sub
Private Function RunJob(ByVal publication As MergePublication, ByVal snapshotAgentJob As MergeDynamicSnapshotJob, ByVal distributorConn As ServerConnection, ByVal hostName As String) As Boolean
Dim jobs As ArrayList
Dim iJob As Integer
Dim bExists As Boolean = False
jobs = publication.EnumMergeDynamicSnapshotJobs()
For iJob = 0 To jobs.Count - 1
snapshotAgentJob = DirectCast(jobs(iJob), MergeDynamicSnapshotJob)
If snapshotAgentJob.DynamicFilterHostName = hostName Then
'Run the Job
bExists = True
Dim server As New Server(distributorConn)
Dim job As New Job = (server.JobServer, snapshotAgentJob.Name)
MessageBox.Show(String.Format("About to run the dynamic snapshot job: {0} with status: {1}", job.Name, job.State.ToString))
Try
job.Start()
Catch ex As Exception
MessageBox.Show(ex.ToString)
Throw
End Try
Exit For
End If
Next
End Function
End Class
View 7 Replies
View Related
Mar 28, 2007
I posted last year about getting and Expand All/Collapse All link working in reports, and it does work perfectly for on-demand reporting. I was able to get these working using the boolean report parameter and then the Jump to Report option going back to itself changing that parameter.
The issue that I have just discovered is that when a report with these Expand All/Collapse All links are in a report snapshot, clicking those links will cause it to re-render the report from the datasource at the time the link is clicked. This is an issue for one of the datasources we are using because the data is always changing, so when it goes back to re-render the counts and data returned are completely different then they were when the snapshot was first created. As time goes on the data will no longer even be in the datasource since it can only maintain 4 months of data.
Is there another way to get all detail groups to expand/collapse at once that does not require the report to be completely re-rendered from the datasource?
View 1 Replies
View Related
Feb 2, 2007
I am using SQL2005 merge replication and have pull subscribers on a low bandwidth link
I am compressing the snapshot into an alternate folder. Files are not put into the default folder
When I start a synchronization, I would expect the cab file to be copied to the subscriber and then the files to be extracted locally at the subscriber in order to apply the snapshot
However, what appears to be happening is that the files are being extracted from the cab file on the publisher (in a UNC specified directory) and then copied in their uncompressed form to the subscriber - resulting in an extremely slow snapshot application.
Any ideas what I am doing wrong? I have read about the options for using FTP to transfer snapshot files, but I am not clear whether I have to use FTP in order to transmit a compressed snapshot. I don't want to use FTP unless I need to.
Any suggestions would be much appreciated
aero1
View 8 Replies
View Related