Well, it's happened again. An upgrade to SQL server was released without my having any idea about it. SP2 came out today and the only way I knew it was I happened to see a headline on Yahoo. Similar things have happened in the past with HotFixes or security rollups. Sometimes it's rather embarrassing, especially when your boss challenges you on the most recent security updates for SQL. Is there any way which we can be notified of new upgrades/updates to SQL Server? I need to know when there is a new hotfix, update, service pack, or whatever so that I can install it as needed. So far, the only way I've found these was by accident. I'm signed up with the Microsoft Download Notification email list, so I was able to see that SP2 CTPs were released, but I've never seen it include hotfixes or rollups.
If something like a SQL update notification service doesn't exist, then I would ask that Microsoft look at putting one together. Thank you!
I have a lengthy report and a summary viewof this report. I'dlike for the summary view to be sent out in a subscription as the message body and have the detailed report be sent out as a PDF attachment. I know you can't do this in the default subscription UI, but I was wondering if there was a programmatic way to make this happen?
On the development environment i've got replication running with no trouble but when deploying in the production environment i get the error below
Has anyone seen this before and solved it? Another question: How to enable verbose history when using the MS SQL Server Management Studio to create a subscription.
Any help is welcome,
Ralf
TITLE: Microsoft SQL Server Management Studio------------------------------A call to SQL Server Reconciler failed. Try to resynchronize.HRESULT 0x80004005 (29006)The schema script 'd:MSSQL epldatauncTEST_KANSCE TEST20070522165445 blNaw_2.sch' could not be propagated to the subscriber.HRESULT 0x80070003 (0)The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.HRESULT 0x80045017 (0)------------------------------BUTTONS:OK------------------------------
I have a transactional replication that was running fine for about few months, but eversince there was a massive update of records at the publisher site and when I use the replication monitor, i notice Status: Performace Critical, Subscription:[localservername]:[Orders_Repl], Performance:Critical, Latency:03:34:47.
I was thinking of redoing the whole replication but before i am about to proceed. Can you guys explained what happen and what does it meant when Latency:03:34:47. I don't remember seeing a Latency:03:34:47.
I am hoping to solve this critical performance issue hope you guys can help. Thanks
there's lots of info on the web about rs.exe adding objects to RS, but what about rs.exe deleting them, for cleaning house, without "breaking" anything?
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
Hi! I have created a sql script with publish provider and the file has all information about ASPNETDB and now i want to drive the sql script into my databas on my webbhosting. How do i? i have SQL Server Management Studio Express to admin my databas. any?
I'm building a C# database application that access a remote sql 2005 database. For the moment I am using sql express edition. My application will be running in several REMOTE camps which only have an internet connection via sattelite. The sattelite connection has a very high latency. I am wondering what workarounds or solutions are available for this situation. All applications need to access the same database and preferebly be notified when changes take place on the database.
I have a question: On my WM5 devide I run 2 query's right after eachother:
DELETE FROM test,
ALTER TABLE test ADD COLUMN abc bit NOT NULL",
I get an error on the second statement. When I change the statement in "NULL" instead of "NOT NULL" it works. test is a very large table. It seems as if query 2 is executed while the engine is still processing query 1. And then you propably get the NOT NULL statement violation. Is this possible??? I know that writing the registry has some latency because WM5 uses flash instead of ram. Does SQL Compact have the same latency? Or is this another problem?
Hi all, There is a sql express database .mdf file which I can access locally through local web pages using windows authentication and integrated security = true. Now I want to publish this db and the web pages to my hoster's server so as to access the db from internet. The connection string currently is in the web config as below:<add name="NORTHWNDConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|NORTHWND.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> The database is currently located in the App_Data folder. Please give me some step by step solution to configure the db to access from internet. Thanks a lot in advance.
--> Cursor for fetching all publications in all databases DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR SELECT [name] FROM sys.databases WHERE is_published > 0
OPEN curPublishedDatabases FETCH curPublishedDatabases INTO @PublishedDBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications' INSERT INTO @Publications (PublicationDatabase, PublicationName) EXEC sp_executesql @SQL
FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName END
CLOSE curPublishedDatabases DEALLOCATE curPublishedDatabases
--> Cursor for posting tracer tokens DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications
OPEN curPublications FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName WHILE @@FETCH_STATUS = 0 BEGIN SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT'; SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT' EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT
UPDATE @Publications SET TracerTokenID = @TracerTokenID, TracerTokenPostTime = GETDATE() WHERE PublicationPK = @PublicationPK
FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName END CLOSE curPublications DEALLOCATE curPublications
--> Wait two minutes for all tokens to be commited at all subscribers WAITFOR DELAY '000:02:00.000'
--> Then check the results for each posted token DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications
OPEN curTokens FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID WHILE @@FETCH_STATUS = 0 BEGIN --> Insert token history for each token SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + '' INSERT INTO @Latency (DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency) EXEC sp_executesql @SQL
--> Make sure that the PublicationPK is added to the token history UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL
--> Clean up the tracer token SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + '' EXEC sp_executesql @SQL
FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID END CLOSE curTokens DEALLOCATE curTokens
SELECT b.PublicationDatabase, b.PublicationName, a.Subscriber, a.SubscriberDB, a.OverallLatency, b.TracerTokenPostTime FROM @Latency a INNER JOIN @Publications b ON a.PublicationPK = b.PublicationPK
We have setup up transactional replication with dedicated distributor in SQL Server 2005 environment. I have noticed that during particular time of the day latency is increasing dramatically. I have been checking Tracer Tokens and Total latency during that time is around 30-40 min (both publisher to distributor and distributor to subscriber is taking much longer that normal). Normally, it is less than 10 sec. I was wondering if there is a way to pinpoint exact cause of the latency. This is pull subscription. I would appreciate if someone can share (or point to the right direction) best practice on transactional replication setup/maintenance. My understanding is that only committed transactions are replicated, correct? I checked database on publisher and didn't see any outstanding long running transaction.
I have been working on a project to evaluate transactional replication in SQL Server 2005 to find out if it will be suitable to provide real time reporting without impacting the primary database server. Thus far I have not seen latency better than several seconds replicating a change to a table or by utilizing the "tracer tokens" troubleshooting functionality. It usually takes a couple seconds from the publisher to the distributor and a couple more from the distributor to the subscriber.
I guess my question is actually whether it is possible to achieve sub-second (milliseconds) latency for transactional replication under any circumstances. Someone on another forum told me it was not, so I thought I would ask a separate community to confirm. Regarding the hardware, the latency on ping is less than 1ms between the servers and the servers themselves are decently powered quad processor boxes w/ 4GB of ram each. It seems to have the same latency of 3-5 seconds regardless of whether I publish a single table or all of the tables in the database. I currently have it configured with the publisher on one server and the distributor/subscriber on the second with the log reader and distributor agents set to run continuously. If it is possible, please recommend configuration of the agents or the topology of the PDS to achieve this. Thanks in advance for any insight you provide.
I have a SSDT project that intermittently fails with the following error when I try to publish via Visual Studio by double-clicking the publish.xml file...
(330,1): SQL72014: .Net SqlClient Data Provider: Msg 1205, Level 13, State 68, Line 5
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
(326,0): SQL72045: Script execution error. Â The executed script: IF EXISTS (SELECT 1 Â Â Â Â Â Â FROM Â [master].[dbo].[sysdatabases] Â Â Â Â Â Â WHERE Â [name] = N'$(DatabaseName)') Â Â BEGIN Â Â Â Â ALTER DATABASE [$(DatabaseName)] Â Â Â Â Â Â SET READ_COMMITTED_SNAPSHOT OFFÂ Â Â Â Â Â Â WITH ROLLBACK IMMEDIATE; Â Â END
This is a hyperV client running on my laptop. I'm connecting to the client's VPN inside the hyperV client and using the "runas" method to open Visual Studio to spoof the domain credentials i use on the clients network. This allows me to connect to servers on the client's domain via apps as if I was logged in with appropriate credentials (not sure how to phrase that better). I can elaborate if deemed necessary, but even the client's FTEs are having same problem and they're sitting in the building.
Windows Server 2012 R2 StandardSQL Server 2014 CU6 (12.0.2480)Visual Studio Premium 2013 (12.0.31101.00) Update 4Â
Windows Server 2012 R2 (VMWare)SQL Server 2012 SP2 (11.0.5058).Here are some observations...
Happens about 60% of the time during the day (I just keep re-running until it deploys successfully)...late at night and on weekends, it doesn't seem to happen with anywhere near as high a frequency.Only seems to happen when I use "Always re-create database" option (don't recall incrementals having this problem...but admittedly I'm not doing many of those as we're still in the full-speed-ahead-development phase)This does NOT happen when I publish to my local SQL Server instance..Deadlock xEvent trace on target instance. Unfortunately it didn't seem to be able to capture all the info and the deadlock graph doesn't render (error below) checking the Single User Mode during the publish. This seems to increase the frequency with which this issue occurs.
I have database (DB) on the server SQL1. This database (DB) is published on SQL1 server for SQL2 server. So, SQL1 is publisher and SQL2 is subscriber for (DB). Can I publish database (DB) on the server SQL2 for the server SQL3? I would like to implement this scheme of Publication/Subscription:
<o:p></o:p> SQL1 -> SQL2 ->SQL3..
I know, that it is simpler just to publish DB for SQL2 and SQL3 from SQL1, but, because of network connectivity, I can't do that...
i want ti publish a c# project connected to sql database, i introduced the database into my project, i use the connection string:Â
 SqlConnection cn = new SqlConnection(@"Data Source=CONTA-LAPTOP;Initial Catalog=RaducuIonel;Integrated Security=True");  if right click on the database-> properties iti show me next connection string: Data Source=CONTA-LAPTOP;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATARaducuIonel.mdf";Initial Catalog=RaducuIonel;Integrated Security=True  Â
...when i connect to server in sql i use windows autentification. And the last thing: when i publish the project it ask me: application will check for updates from [URL] or application will not check for updates. I select first option: to check for updates from here:Â [URL] .to another computer my application cannot connect to database (login error)Â .what i am doing wrong?Â
I created an app in vs2005 and everything works perfectly in the development environment. I've published the site to my local IIS, but when I try to log in, it doesn't allow me. I'm assuming that it has something to do with the db not working correctly. Unfortunately I don't know how to debug to see where the problem is coming from now that it's running on my 'live' IIS. Does anyone have any ideas on what could be the problem. I have some settings in the web.config, which have been working perfectly. <appSettings><add key="UserName" value=""/><add key="Password" value=""/><add key="Server" value="xxxxxxsqlexpress"/><add key="Database" value="xxxxx_xxx_users"/><add key="UseTrusted" value="True"/></appSettings> I had a local guy at the office here saying that the ASP.NET user needs to be granted access to sqlexpress2005, but I wasn't sure on how to do that. So all in all, My app works in Visual Studio 2005, and not in IIS. There are no errors when loading various pages. They all load correctly and without error. It seems that it's just when I try to do anything with the DB. Thanks for the help. I appreciate it!
Is there anyway to check if server is having disk latency or IO issues?Found below in SQL error log
Date10/1/2014 8:28:58 AM LogSQL Server (Current - 10/1/2014 12:00:00 AM)
Sourcespid10s
Message SQL Server has encountered 8500 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:Fin.mdf] in database [Fin] (5). The OS file handle is 0x0000000000001368. The offset of the latest long I/O is: 0x0001104a7da000
Hello, I recently read the following book: http://www.amazon.com/Microsoft-Server-2005-Reporting-Services/dp/0072262397/ref=pd_bbs_sr_1/104-5275187-2065567?ie=UTF8&s=books&qid=1194662002&sr=8-1
It was excellent.
I was hoping to find an example of the following:
My company has a desktop application with a local database (sql server 2k express) I would like to be able to have local reports and if the user likes the report they are viewing, the user could "publish" that report to the server. Since the database is local, the report data source cannot point to that database. So, it would publish the report AND the data in it.
For this use case, what is the recommended strategy of moving the data with the report? Would i create a small xml file and then dynamically build a datasource that points to that xml? Any best practices advice?
Is there a way to publish the MDW (Data Collection) reports (Disk Usage history, Query Statistic history, and Server Activity history ) on SSRS server?!Â
It is not logical for all who wants to view the mentioned reports to open the SSMS ! Â
I've already check [URL] .... there, the writer is suggesting to recreate the reports from scratch on SSRS, that if I could figure their queries, which I don't think the only way that we have!
In my vb.net application, I have an attached database called dbMaster with the following tables:
tblCustomer, tblProducts, tblUsers
In visual studio 2005, is it possible to publish only one table? My problem is, is that I want to send my end users updated data from my tblCustomer, and am finding that on my test laptop, if I publish dbMaster, it sends all tables when I just want one to be sent.
Hi Everybody, Can anybody tell me how to get the number of commands delivered per minute in case of Merge Replication with Publisher and subscribers. This way, we can be sure that even if there is a latency (due to high volume transaction processing), replication is in good shape and things will catch up soon. Also if there are any other similar measures which can be monitored to make sure that replication is going on fine, it would be great
Please let me know If anyone has got information on same.
Hello,I'm an absolute newbie when it comes to SQL. I was told that SQLserver does not function well on a WAN where network latency between,say, the SQL server and a front-end server is greater than 250ms.I can't find anything information supporting this claim online, so Iwas hoping someone here could tell me if this is true or not?Thank You!!
I am looking into various options to improve latency of our application (we figured the latency is mainly because data persistence - writes and reads from DB). I am looking into In-Memory databases also. But, before making that decision (of using in memory databases), I would like to see if there is a way to configure SQL Server 2005 to get as close performance as in-memory databases?
My question: 1. Is there a way that I can configure SQL Server 2005 to use a CACHE that gets loaded as needed basis, so that future database reads/writes will happen to the cache as opposed to disk (db writes)? 2. Is SQL Server 2005 recoverable in such configurations? 3. Are there any ideas/resources where I can get more details? (Such as sample configurations with bench mark numbers, rpevious experiences..etc)
i am trying to publish an indexed view logbased publication but I'm getting "must be a schema-bound view with at least a clustered index to be published as a log-based indexed view article."
The table has clustered index and the view is schema-bound.
this is the script for schemabinding
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [nth].[musictracks1] with schemabinding
I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.
1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.
2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.
3) What are the commands to truncate inactive transactions and increasing the readbatchsize?
4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.
