I'm porting a process which creates a MASSIVE CROSS JOIN of two tables. The resulting table contains 15m records (looks like the process makes a 30m cross join with a 2600 row table and a 12000 row table and then does some grouping which must split it in half). The rows are relatively narrow - just 6 columns. It's been running for 5 h...
Here is the problem: for your reference: http://www.freeimagehosting.net/uploads/b443e7a1fe.jpg database entries 1,2 and 3 are made using jython 2.2.1 using jdbc1.2. database entry 4 is made using vb the old to be replace program using odbc. We have found that if I copy and paste both jython and vb MailBody entries to wordpad directl...
Hi, I'm working on a design for a hierarchical database structure which models a catalogue containing products (this is similar to this question). The database platform is SQL Server 2005 and the catalogue is quite large (750,000 products, 8,500 catalogue sections over 4 levels) but is relatively static (reloaded once a day) and so we a...
I'd like to hear from anyone who has experience with Redgate's tools for Sql Server 2005. In particular, are their backups 100% reliable? Their claims about backup speed and compression sound almost too good to be true. For those who have used Redgate's backup, do they handle transaction log backups (and therefore restoring up to a sp...
I'd like to drop a user in a SQL Server script but I'll need to test for existence first or I'll get script errors. When dropping tables or stored procs, I check the sysobjects table like so: IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[up_SetMedOptions]') AND OBJECTPROPER...
Assuming following definition: /// <summary> /// Replaces each occurrence of sPattern in sInput with sReplace. This is done with the /// CLR: new RegEx(sPattern, RegexOptions.Multiline).Replace(sInput, sReplace). The result of the replacement is /// the return value. /// </summary> [SqlFunction(IsDeterministic = true)] public static S...
With SMO objects using Server.JobServer.jobs to get a list of jobs, I can find the status of each job. For those that are currently executing I would like to find the SPID it is executing on. I can also get a list of the server's processes using Server.EnumProcesses(). This gives me a list of currently active SPIDs. I want to match th...
I'm trying to create a LINQ to SQL class that represents the "latest" version of itself. Right now, the table that this entity represents has a single auto-incrementing ID, and I was thinking that I would add a version number to the primary key. I've never done anything like this, so I'm not sure how to proceed. I would like to be able ...
I have a dynamic query that returns around 590,000 records. It runs successfully the first time, but if I run it again, I keep getting a System.OutOfMemoryException. What are some reasons this could be happening? The error is happening here: public static DataSet GetDataSet(string databaseName,string ...
Is there any way for a DBA to peek in on the execution plan of a long-running query in SQL Server 2000? I know how to get the SQL being run using fn_get_sql(). And yes, theoretically if open a new connection and set the environment flags the same, it should generate the same plan for the SQL. However, I'm in a data warehouse environme...
Hi all, I've created a CLR stored procedure that I'm running on SQL 2k5 and I'm wondering if there's any way to get line numbers for exceptions thrown by the .NET code. When an Exception is thrown, I get something along the lines of Msg 6522, Level 16, State 1, Procedure myProcedure, Line 0 A .NET Framework error occurred during exec...
Hi Guys, One of my columns type is DateTime (Date Registered). I cannot create a query that filters all the data for eg. All registrations who registered on the 22/10/2008 between 18:00 and 20:00. Thanks ...
I am developing a .NET (2.0) WinForms utility that connects to a SQL Server 2005 database. I have found reference material, such as Avoiding Plaintext Passwords for handling the actual encryption of the connection string data. I now need to implement the encryption during the installation process so that an administrator can install the ...
In SQL Server Profile, I have it tracing SP:StmtStarting events. The "TextData" includes information like the following: EXEC MySortedProc @param, NULL, @param2, NULL What would I have to trace (or can I?) to view the value of those parameters? ...
Good day, I'm having a problem with asp.net 2.0 viewstate. Basically, I want to disable the viewstate for a gridview. However, when I do so, as soon as there is a post-back, before even getting into Page_Load, I get a 'Login failed for user sa ...'. If I enable the viewstate back, everything runs smoothly. In PageLoad, I set the conn...
I want to limit the number of records returned by calling ROW_NUMBER(), but I am curious as to if you can call it in dynamic sql? Also, Can you use CTE's in dynamic sql? ...
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY hrl.Frn) as Row, hrl.unq, hrl.LcnsId, hc.Business,hc.Name,hc.Phone, hrl.Frn,hrl.CallSign, hrl.gsamarkettypeid, gmt.[Market Type Code] + ' - ' + gmt.gsamarkettype, hrl.gsalatitude,hrl.gsalongitude, rsc.RadioServiceCode + ' - ' + rsc.RadioService, GrantDt, Ex...
I have an SSIS package that executes several tasks. I manually added an event handler inside Business Intelligence Studio 2005 at the package level for the OnExecStatusChanged event. My question is, how can I add a handler for this event inside C#? I have loaded the package as pointed here and I also created a custom class inherited fro...
I want to dynamically get the name of the current stored procedure inside itself for logging purposes. ...
I want to access Microsoft SQL Server Compact Edition databases from Java. How can I do that? I searched for JDBC driver for SQLCE, but I didn't find any. ...