How should I manage tables that refer to site 'events'. i.e. certain activities a user has done on a website that I use for tracking. I want to be able to do all kinds of datamining and correlation between different activities of users and what they have done. Today alone I added 107,000 rows to my SiteEvent table. I dont think this is ...
Anyone point my at a utility for syncing sql server logins after restoring a database to a different server? I know I can do it with scripts, but I've been using a wonderful little utility - 'Db Maint Sync SQL Logins' (http://www.dbmaint.com/SyncSqlLogins.asp) to do it on sql server 2000 which unfortunately doesn't support 2005 and I'...
Let's suppose I'm using the Northwind database and I would like to run a query via a stored procedure that contains, among other parameters, the following: @Offset to indicate where the pagination starts, @Limit to indicate the page size, @SortColumn to indicate the column used for sorting purposes, @SortDirection, to indicate ascendan...
I need to select from a table and filter on dates greater than a specified date. The problem I'm having is that the dates are stored as nchar(20) and I can't seem to get it converted to date in the where clause. SELECT CONVERT(DATETIME,log_time,20) from dbo.logs where CONVERT(DATETIME,log_time,20) > '10/20/2008' Msg 241, Level 1...
I have a table in an MS SQL Server db. I want to create a script that will put the table and all records into another db. So I right-click the table in Management Studio and select Create-To new query editor... but all I get is the table structure. How exactly do I get the values too? ...
I've run into a strange situation that seems to involve long text overflowing. I'm using fn_pcre_replace (from the xp_pcre extended stored procedure) on an nvarchar(max) column, and when I replace with more characters than were there originally, it returns NULL if the original string was over 8000 characters long. For example, this Se...
I'm trying to enable SqlCacheDependency through my StructureMap IoC, I'm using LinqToSql I have the code done to take care of the Linq Caching but not quite sure how to go about setting up the SqlCacheDependency as it requires putting this in a global.asa file void Application_Start(object sender, EventArgs e) { string connectionSt...
For some reason, new projects set the version number of the assembly to 1.0.*, which then assigns what appears to be a random number once compiled. Why is this not set to 1.0.0.0 like all other projects. Thanks. ...
Way back when I was working in an Oracle shop I took the CONNECT_BY for granted. Now I'm stuck working with SQL Server 2005 and have some nasty object hierarchies. Specifically, we have a self referencing table where all child records have a column with their parent's id. Currently we have a view that maps children to levels in the hi...
I have a bunch of tables using user-defined data type for PK column. Is it possible to change this type Using SQL Server 2005? ...
Our usage case is a database responsible for accounts, sessions, licenses, etc. — it needs to be polled upon client startup, so high reliability is crucial. As such, we'd like to replicate across different servers in physically separate networks, just in case an entire datacenter happens to go down. I have successfully set up a push subs...
I installed SQL Server 2005 sometime ago and forgot the administrator password I set during setup. How can I connect to SQL server now? EDIT: I think I only allowed Sql Server Authentication. Login with integrated security also does not work. ...
I had an unusual problem yesterday where I was suddenly unable to insert records into a table with an identity column. A simple insert like this: INSERT INTO MyTable (Column1, Column2) VALUES ('text', 236764) Started throwing a primary key constraint violation. I ran DBCC CHECKIDENT on the table, and realized that SQL Server had stop...
Would it be possible to define layouts depending on the report type? ie. The layout size for PDFs would be different from the web layout. Let's say we publish the report to the web. The user has the option of exporting this custom report to PDF. We want to be able to specify how the report should look like when exported to PDF. ...
I need to be able to schedule a job on the 10th of each month and have it run for a set number of days or until a specified date. Is there any way to customize sql server agent jobs using some sort of API or something? Thanks. ...
I have a number of generated .sql files that I want to run in succession. I'd like to run them from a SQL statement in a query (i.e. Query Analyzer/Server Management Studio). Is it possible to do something like this and if so what is the syntax for doing this? I'm hoping for something like: exec 'c:\temp\file01.sql' exec 'c:\temp\fil...
My account is in the securityadmin role and I cannot grant myself sysadmin permission. I wish to gain access to a database so I can add my account to a particular role within it. As I don't yet have access to the database I can't use the UI. Does anyone know if this is possible and what SQL commands will achieve this in SQL Server 2005?...
Whats the best way to import data from SQL Server 2005 into Exchange 2007? I have seen references to xp_sql2exchange but the companies website has been down for several weeks so Im unable to download the stored procedure. Interested in 3rd party solutions as well if it makes the job easier. ...
We have an application that use MSSQL, when we move to 2005, basically we recommend to our client to apply the backward compatibility package and that’s it, but somebody ask me what to do to be 100% compatible on 2005. I was thinking on 1. Compatibility level: SQL Server 2005 (90) 2. Remove any DMO reference, and replaced by SMO 3. Conne...
I have a set of tables that are used to track bills. These tables are loaded from an SSIS process that runs weekly. I am in the process of creating a second set of tables to track adjustments to the bills that are made via the web. Some of our clients hand key their bills and all of those entries need to be backed up on a more regular s...