I was asked for a comprehensive breakdown on space used within a specific database. I know I can use *sys.dm_db_partition_stats* in SQL Server 2005 to figure out how much space each table in a database is using, but is there any way to determine the individual and total size of the stored procedures in a database? (Short of opening each...
I'm collecting metadata using the sys.* views, and according to the documentation, the sys.identity_columns view will return the seed and increment values like so. CREATE TABLE ident_test ( test_id int IDENTITY(1000,10), other int ) SELECT name, seed_value, increment_value FROM sys.identity_columns WHERE object_id = OBJECT_ID( '...
Anyone have an idea of what this means for MS SQL Server? I'm also calling this from Coldfusion, although I'm guessing that doesn't make any difference. ...
I have a script that needs to extract data temporarily to do extra operations on it, but then doesn't need to store it any further after the script has run. I currently have the data in question in a series of temporary local tables (CREATE TABLE #table), which are then dropped as their use is completed. I was considering switching to ...
When I try my CLR UDF, I am getting this error: Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "getFileSize": System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture...
I have encapsulated a backup database command in a Try/Catch and it appears that the error message is being lost somewhere. For example: BACKUP DATABASE NonExistantDB TO DISK = 'C:\TEMP\NonExistantDB.bak' ..gives error: Could not locate entry in sysdatabases for database 'NonExistantDB'. No entry found with that name. Make sure that ...
VB.net web system with a SQL Server 2005 backend. I've got a stored procedure that returns a varchar, and we're finally getting values that won't fit in a varchar(8000). I've changed the return parameter to a varchar(max), but how do I tell the OleDbParameter.Size Property to accept any amount of text? As a concrete example, the VB co...
A little help needed. I'm receiving an xml file similar to this: <?xml version="1.0" encoding="utf-16"?> <dc:GRANTEE xsi:schemaLocation="http://www.blahblahblah.com/FullSchema test.xsd " xmlns:dc="http://www.blahblahblah.com/FullSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RPGID>90CU0024</RPGID> <PLANID>01...
I heard about SSIS, a new replacement of the old DTS. I believe it has the same functionality which I used to perform. Now, like any other product from Microsoft, you have to mess with 10 different choices for a single package. I would like to get your opinion of which edition of SQL Server 2005 I should buy to ensure that I can replica...
thanks in advance. ...
I am using a Cursor in my stored procedure. It works on a database that has a huge number of data. for every item in the cursor i do a update operation. This is taking a huge amount of time to complete. Almost 25min. :( .. Is there anyway i can reduce the time consumed for this? ...
I'm considering altering some tables to use nvarchar(50) as primary key instead of an int primary key. Using an int ID for a key really is irrelevant data, it's the string I'm interested in. What sort of performance hit will occur, or where do you research this? Other than cut and try that is. ...
I know several ways of writing paged query in SQL Server 2005. But I have no idea about their performance. Which one is the best and most optimized way for writing paging SQL queries? Using Row_Number() Using Partition by Using Temporary tables Using Nest Top N with Order by Some other way? ...
I have a SQL Server 2005 sp2 box where tempdb has either 8 data files or 5 data files dependingo n where you look. DBCC showfilestates and sys.database_files (both queried in tempdb) show 8 data files (tempdev - tempdev8), however when I query sys.master_files (in master db, which is also what the GUI uses), I only see 5 (tempdev, tempd...
As part of our build process we run a database update script as we deploy code to 4 different environments (due to crazy business rules, but that's another post). Further, since the same query will get added to until we drop a release into production (yet a few more domains to add to... frustrating); it HAS to be able to run multiple ti...
Using SSRS2005, can you pull in values specified in the SSRS Web.Config file (think appSettings)? I'm needing to build up a dynamic hyperlink in a series of reports and would like this to be based on a value set in a config file. At present, it seems my only options are to: Update the hardcoded variable in each report that is build...
I want to grant a user permission to create, alter, and execute stored procedures, but without the ability to create other database objects. I excluded them from the role db_ddladmin, but explicitly granted the permissions "Create procedure" and "Execute". These permssions appear in the effective permissions list. However, when I try ...
Would like to get a list of advantages and disadvantages of using Stored Procedures. The main advantage of SPs seems to be precompiled and an abstraction of data from the application. Give me your thoughts.... ...
Say I have an MSSQL table with two columns: an int ID column that's the identity column and some other datetime or whatever column. Say the table has 10 records with IDs 1-10. Now I delete the record with ID = 5. Are there any scenarios where another record will "fill-in" that missing ID? I.e. when would a record be inserted and giv...
I need customers to be able to download PDFs of letters that have been sent to them. I have read the threads about database vs filesystem storage of documents or images, and it does sound like the consensus is that, for anything more than just a few images, filesystem is the way to go. What I want to know: would a reasonable alternativ...