In SQL Server 2005, what is the difference between len() and datalength()?
What is the difference between len() and datalength() in SQL Server 2005? ...
sql-server-2005
full-text index not updating (SQL 2005)
I have a table that was doing a fine job of full-text search until last week. There are over seven million records, and I see it has over seven million entries in the full-text index. But nothing more recent than a week old shows up in the search results. I can see in the table's full-text index properties that there are a growing n...
How to get a list of fields in a unique constraint
I have a table with a unique constraint on one or more fields. Given the constraint name, I want to query the system views to get the list of fields that are part of this constraint. SQL Server 2005. Thanks. ...
Execute an SSIS Package created with VS2008 on SQL Server 2005
Maybe a stupid question but... I created an SSIS package using VS2008 Professional. I want to deploy and execute it on a server running SQL Server 2005. When I try to run it I get an error stating the version of the Execute Package Utility on the server is different than the version of the package I'm trying to execute (makes sense, si...
MS Access Application - Convert data storage from Access to SQL Server
Bear in mind here, I am not an Access guru. I am proficient with SQL Server and .Net framework. Here is my situation: A very large MS Access 2007 application was built for my company by a contractor. The application has been split into two tiers BY ACCESS; there is a front end portion that holds all of the Ms Access forms, and then on ...
Performance of data
I have 60 columns in a table. 1). I want to add one more column to that table. Will there be any impact on performance? 2). How many columns can I add? 3). any idea for the avoid recursion. [I have no idea here - annakata] ...
Does Visual Studio Professional come with SQL Server Developer (not Express) edition?
A co-worker and I are disagreeing on this. He thinks we have licenses for SQL Developer because we purchased VS 2005 Professional. It's been a while since I've installed it but I don't recall that being part of the installation whatsoever. It's always been a separate install and license. Been to the MS VS site and can't find anything...
SQL 2005 Select data where conversion fails
Is there any way for me to test a data conversion in a select statement and only return rows where the conversion fails? IE: SELECT * FROM my_table WHERE CONVERT(datetime, [colA]) = NULL I'm open to any SQL hacks/trickery. ...
SQL Server "Maximum Server Memory" - seems to have no effect on Virtual Size
On 32 bit windows xp, sql server express, I set SQL Server "Maximum Server Memory" to 200 MB, but as soon as I start it, in Sysinternals Process Explorer I still see Virtual Size consumed of 1.5 GB. Now I know this is no big deal if the Virtual Size is small, but when other applications max out the physical memory (Internet Explorer cou...
In Sql Server 2005, is there a way to query the total disk space (physical hard drive(s)) available on the server?
I found this in googling, but I don't think it's the complete code? http://www.dba-oracle.com/t_sql_server_2005_windows_script_display_disk_space_usage.htm I was hoping there was a simple system stored procedure, like for finding the total size of MDF file (exec sp_helpdb DATABASE_NAME). Any suggestions would be appreciated. ...
Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"
I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me: 1. Run both and time each query 2. Run both and get "Query Cost" from the actual execution plan Here is the code I run to time the queries... DBCC FREEPROCCACHE GO DBCC DROPCLEANBUFFERS GO DECLARE @start DATETI...
SQL Server 2005: Audit random record deletion.
This may seem like a dumb question, but I'm in a head-> wall situation right now. I work on a massive ERP application in which the SQL Server 2005 database is updated by multiple disparate applications. I'm trying to figure out where the deletes in a particular table are originating from. I tried using the Profiler but I'm not able to...
Can I use a store procedure output in an update statement in SQL Server?
Hi all, I'm converting some data in SQL Server 2005. I have a table update like this: update Invoices set Invoices.InvoiceReference = 'NewRef' where Invoices.InvoiceReference='Unknown' But what I'd like to plug in instead of 'NewRef' is the output from a stored procedure that uses parameters from the columns of the Invoices table. T...
Access denied when trying to move files with xp_cmdshell
Hi Im trying to use some T-SQL to move some files from one directory to another. Im using xp_cmdshell to call the move command Just like this: create table #output(line varchar(2000) null) insert into #output exec master..xp_cmdshell 'move /y "D:\files\*.txt" "D:\oldfiles"' But the files inst move and the #output table contains this o...
How to temporarily disable the log in SQL2000/2005?
Is there a way to stop the log file from growing (or at least from growing as much) in SQL2000/2005? I am running a very extensive process with loads of inserts and the log is going to the roof. EDIT: please note I am talking about an batch-import process not about everyday update of live-data. Cheers! ...
Database Design Problem
I'm trying to design a database for a server and database inventory and I'm looking for a good database design. We have tables for server clusters, stand alone servers and databases. I would like to represent the following relationships in the database: A one to many relationship from cluster to servers. A one to many relationship fro...
Attaching DB with full-text catalogs under a different name?
I have a database with regular full-text catalogs. I want to detach this database, copy it to a different server and attach it under a different name (same name but with '_BAK' appended to it). I am using SQL Server 2005. Here is the error trying to attach DATABASE with full-text catalogs under the name DATABASE_BAK Warning: Identity ...
Why is connection pooling behaving differently on different servers?
We have a program that reads in a stream of input and writes it to the database. There is no user input. This program is currently running in parallel on both the development and the production server, with the same data as input, writing to different output servers. On the development server everything is fine; around 30-odd pooled co...
Teradata Linked Server in SQL 2005
I am trying to connect to Teradata using a Linked Server in SQL Server 2005. When I try to use SQL Server to connect using OPENQUERY, I get the following errors: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "TDOLEDB.1" for linked server "TERADATA" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot g...
Dimension Security in SSAS & SSRS
I am stuck with a problem of implementing security at dimension level in SSAS. Here is what I did - 1. Defined a role in SSAS and applied security at dimension level (Unchecking cube dimensions that I don't want this role to access and setting Allowed & denied Sets). 2. Tested using Cube Browser, it worked fine. 3. Tested using SSRS, no...