Say you have 2 database servers, one database is the 'master' database where all write operations are performed, it is treated as the 'real/original' database. The other server's database is to be a mirror copy of the master database (slave?), which will be used for read only operations for a certain part of the application. How do you...
My current development project has two aspects to it. First, there is a public website where external users can submit and update information for various purposes. This information is then saved to a local SQL Server at the colo facility. The second aspect is an internal application which employees use to manage those same records (conc...
I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this: update myTable set Col1=@col1, Col2=@col2 where ID=@ID if @@rowcount = 0 insert into myTable (Col1, Col2) values (@col1, @col2) My logic behind writing it in this way is that the update will perform an im...
In the past I've never been a fan of using triggers on database tables. To me they always represented some "magic" that was going to happen on the database side, far far away from the control of my application code. I also wanted to limit the amount of work the DB had to do, as it's generally a shared resource and I always assumed trigge...
Anybody ever get this error and/or have any idea on it's cause and/or solution? I'm asking this on behalf of a friend but there is info at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1 Update The connection string is "=.\SQLEXPRESS;AttachDbFilename=C:\temp\HelloWorldTest.mdf;Integrated Security=True" I sug...
My scheduled reports in SQL server won't run. I checked the logs and found the job that was failing. The error message in the log was: 'EXECUTE AS LOGIN' failed for the requested login 'NT AUTHORITY\NETWORK SERVICE'. The step failed. I'm using SQL authentication for the report so it shouldn't be issues with the permissions to t...
We're having sporadic, random query timeouts on our SQL Server 2005 cluster. I own a few apps that use it, so I'm helping out in the investigation. When watching the % CPU time in regular ol' Perfmon, you can certainly see it pegging out. However, SQL activity monitor only gives cumulative CPU and IO time used by a process, not what it's...
Anyone seen this before? CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:\Documents and Settings\..\App_Data\HelloWorld.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. I'm asking on a friend...
The company I work for has an old Access 2000 application that was using a SQL Server 2000 back-end. We were tasked with moving the back-end to a SQL Server 2005 database on a new server. Unfortunately, the application was not functioning correctly while trying to do any inserts or updates. My research has found many forum posts that ...
What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to ...
First off I understand that it is a horrible idea to run extremely large/long running reports. I am aware that Microsoft has a rule of thumb stating that a SSRS report should take no longer than 30 seconds to execute. However sometimes gargantuan reports are a preferred evil due to external forces such complying with state laws. At my ...
I've been using the Trial version of SQL Server Reporting Services for a while now. My company finally purchased the software from an online distributor, and for support of Oracle, we needed to upgrade to MSSQL 2005 SP2. Anyway, the "full" version of the software would not install, as it was already installed (It seems the installer ...
Another SSRS question here: We have a development, a QA, a Prod-Backup and a Production SSRS set of servers. On our production and prod-backup, SSRS will go to sleep if not used for a period of time. This does not occur on our development or QA server. In the corporate environment we're in, we don't have physical (or even remote ...
I'm using NHibernate on a project and I need to do data auditing. I found this article on codeproject which discusses the IInterceptor interface. What is your preferred way of auditing data? Do you use database triggers? Do you use something similar to what's dicussed in the article? ...
Is there a way to easily convert Crystal Reports reports to Reporting Services RDL format? We have quite a few reports that will be needing conversion soon. I know about the manual process (which is basically rebuilding all your reports from scratch in SSRS), but my searches pointed to a few possibilities with automatic conversion "acc...
How do you restore a database backup using SQL Server 2005 over the network? I recall doing this before but there was something odd about the way you had to do it. ...
We used the "undocumented" xp_fileexist stored procedure for years in SQL Server 2000 and had no trouble with it. In 2005, it seems that they modified the behavior slightly to always return a 0 if the executing user account is not a sysadmin. It also seems to return a zero if the SQL Server service is running under the LocalSystem accoun...
Came across this error today. Wondering if anyone can tell me what it means: Cannot sort a row of size 9522, which is greater than the allowable maximum of 8094. Is that 8094 bytes? Characters? Fields? Is this a problem joining multiple tables that are exceeding some limit? ...
I would like to have a nice template for doing this in development. ...
I am currently working on an application where we have a SQL Server database and I need to get a full text search working that allows us to search people's names. Currently the user can enter a into a name field that searches 3 different varchar cols. First, Last, Middle names So say I have 3 rows with the following info. 1 - Phillip ...