How can I format a Mailing Address so that I always push all non-null rows to the top? That is, I want to convert an address from the structure below to a mailing address. Here is the structure: [Line1] [varchar](50) NULL, [Line2] [varchar](50) NULL, [Line3] [varchar](50) NULL, [City] [varchar](50) NULL, [State] [varchar] (2) NULL, [P...
I have a huge table of > 10 million rows. I need to efficiently grab a random sampling of 5000 from it. I have some constriants that reduces the total rows I am looking for to like 9 millon. I tried using order by NEWID(), but that query will take too long as it has to do a table scan of all rows. Is there a faster way to do this? ...
I need to copy the indexes from one table to another. There are a LOT of indexes and I don't want to recreate them from scratch. Seems error prone anyways. I have copied the structure using SELECT * INTO [BackupTable] FROM [OriginalTable] But that doesn't copy indexes, constraints, triggers etc Does anyone know how to do this? ...
The Snapshot Isolation feature helps us to solve the problem where readers lock out writers on high volume sites. It does so by versioning rows using tempdb in SqlServer. My question is to correctly implement this Snapshot Isolation feature, is it just a matter of executing the following on my SqlServer ALTER DATABASE MyDatabase SET A...
I have a database table with these two columns: Amount: numeric (18,0) DecimalPlaces: numeric (18,0) This table can store amounts in various currencies, with the decimal place removed from the amount (I can't change this data model). For example, there might be two rows like this: 1290, 2 (This is £12.90, needs to appear as "12.90"...
I am using nHibernate and trying to implement some paging. If I run this code IList list = session.CreateQuery("FROM Author").List(); it returns 8 records. If I run this code though IList list = session.CreateQuery("FROM Author") .SetFirstResult(1).SetMaxResults(5).List(); it returns 0 records. When I look at the SQL generated ...
Hi, i want to use profiles and was able to use aspent_regsql -A p to install the tables. I can see them throught SQL management studio. Im actually using SQLExpress 2005, and my dbo.aspnet_SchemaVersions is populated. Does anybody know what could be going wrong? By the way, im preety sure my connection string and app code are allright...
I have a report table with all the combinations of dimensions as generated by the parameters set by the user. Each row contains 1 combination and a link to another report with charts. How do I pass that combination of parameters/dimensions to that other report? In the row, I have a textbox Action which has a "Go to report" hyperlink. I...
I have a MS Access database and I want to convert it to run on MS SQL Server. How can I export it? ...
How do I debug .NET code written within a script task in a SSIS package? The development environment allows placing a breakpoint however does not take me to the code like it would in regular .NET programming while debugging. Also, I am at a loss to understand how to add the SSIS package variables to the debug watch window? Currently on...
I'm trying to script an MS SQL Server 2005 database to a single file. So far I've tried using SQL Management Studio and the MS Database Publishing Wizard. Both tools will script the database objects without a problem although neither tool is scripting objects in the right order of creation. For instance the script might script a view ...
The count() function counts rows, and is very often used as count(*). If I had a table with phoneNo and firstName, count(phoneNumber) returns the same number as count(*). All columns are always present, if not populated, in each row. ...
I create a view with many joins. I want to group the resultset by some of the fields I selected. Does setting indices on these fields increases the performance of the query? I'm using MS SQL Server, but it should be nearly the same for all dbs, shouldn't it? SELECT table1.field1, table1.field2, table2.field1 FROM table1 INNER JOIN tabl...
Hi there, The information should be 2 date time columns(inserted/updated) with ms precision and should be automatically updated by the server whenever someone inserts, or updates a row. ...
How do I do a SELECT * INTO [temp table] FROM [Stored Procedure]? Not FROM [Table] and without defining [temp table] ? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine Trying the same, but using a stored procedure that returns data, is not quite the same. select * into tmpBusLi...
When viewing a DTS package via Disconnected Edit, Destination or Source Columns within a Transform Data Task have a DataType property with values like 129, 130, 131, 135. Where can I find a list of what these values mean? ...
We have a system that uses a SQL server 2005 database with a number of views these are automatically run daily and the result sets emailed to the client. I dont really know much about the system as a colleague of mine looks after it all but apparently its been working fine. We recently changed the admin password on the server SQL is in...
I'm working with an MSSQL 2000 database containing large amounts of Windows perfmon data collected for all servers in the environment. I'm using SSRS 2005 to build a custom report chart to visualize the metrics over time. If I wanted to view, say, the last month the extensive number of data points would create an ugly report with unrea...
As I mention in a related question about colocation vs renting web servers, I am considering setting up a web server cluster using four Mac Minis (Intel Core Duo) and Windows Server 2008. The website I'm developing is database-driven and uses ASP.Net MVC with Microsoft SQL Server 2008. I am completely inexperienced with setting up a we...
I have a SSIS package that has a child package that is failing. The error message isn't very helpful. The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020 The problem seems to be I am running out of Virtual Memory to complete the job. I found a forum thread that may help solve the problem. http:...