In Microsoft SQL Server 2005, there is the option to store within the database under "Database -> Type -> XML Schema Collection" a XML schema to validate against if you are viewing the database within SQL management studio. What I need to do is store the schema under the specified path above, and then within code pull that schema out t...
Similar to this question only the other way of flow. http://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql I need to be able to save a image field out into the file system and be able to name the file with only using SQL. I don't want to use TEXTCOPY either because I need to use the c...
Will Try-Catch capture all errors that @@ERROR can? In the following code fragment, is it worthwhile to check for @@ERROR? Will RETURN 1111 ever occur? SET XACT_ABORT ON BEGIN TRANSACTION BEGIN TRY --do sql command here <<<<<<<<<<< SELECT @Error=@@ERROR IF @Error!=0 BEGIN IF XACT_STATE()!=0 BEGIN ...
I have changed a column name in a table in my SQL Server 2005 database. I also have a rather large collection of stored procedures that may or may not be referencing that column. Is there a way to find what stored procedures are referencing that column without actually going through each stored procedure and search for it manually? Is...
Here's what I'd like to do. For each table in linkedserver.database whose tablename is like 'text%' (inside loop) A. If current_table exists locally, drop it B. select * into table.name (local) from linkedserver.tablename (copy schema + data) C. Possibly check for errors and Print some text about it? Next Any idea if this script ...
I want to send SQL server Dashboard & Health Report to my client on daily basis, so is there ant script / tool to generate report for sql server 2005. ...
When I run SQL Server 2005 Database Tuning Advisor, it gives a recommendation to create an index, but it will recommends to index a column which already has an index on it. Why does it give a recommendation to create the same index again? Here is my SQL: SELECT t.name AS 'affected_table' , 'Create NonClustered Index IX_' + t.name +...
I work with an enterprise application and have picked up some tips for DB design All tables should have the following fields that helps in audit trail - LastChangedBy, LastChanged, LastChangedPage All your stored procedures that have dynamic SQL should have the @bDebug parameter. By default it's set to 0. If it's set to 1, print out t...
I can extract the month and day by using Day(Date()), Month(Date()). I cant extract hours, with HOUR(Date()). I get the following error. 'HOUR' is not a recognized built-in function name. How can I extract hours? ...
I am trying to list last visits per hour in last 24 hours. I thought the following code would do it, but I get the outcome below. Any idea where I screwed it up? [,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0],[,0], Dim ListHours() HoursInCurrentDay = "24" Redim ListHo...
The string passed to my custom function is the following: SELECT key FROM ubis WHERE MemberID = '144' AND To >='11/7/2009 9:11:23 pm' AND From <= '11/7/2009 9:11:23 pm' Public Shared Function GetDataTable(ByVal CmdText As String) As DataTable Dim myConn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").Con...
I am trying to list last a website's statistics. I listed Last 30 days with; CONVERT(VARCHAR(10), S.DATEENTERED, 101) BETWEEN CONVERT(VARCHAR(10), GETDATE()-30, 101) AND CONVERT(VARCHAR(10), GETDATE(), 101) and this month with; RIGHT(CONVERT(VARCHAR(10), S.DATEENTERED, 103), 7) = RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) but ...
Does performance of a database (SQL Server 2005) decrease if I shrink it? What exactly happen to the mdf and ldf files when shrink is applied (Internals???) ...
I'm trying to rebuild a web server in a virtual pc. Installed required software and Microsoft SQL Server 2005. I've had full backups of my databases but it is not possible to restore from those backups in this new installation. By the way, backups are fine, i can restore from them on original server. In summary, how can I copy whole dat...
I am trying to reduce fragmentation in all of the indexes for a database running on SQL Server 2005. Currently I am trying to use ALTER INDEX in conjunction with sp_MSforeachtable, to apply it to all of the indexes for all of the tables: sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD;" But for some reason this doesn’t always seem to ...
I'm not looking to relocate the database to another server entirely, but just move the data file(s) and log file to another drive with more space. I've seen conflicting directions on how to do this, so I'm looking for the recommended proper way of doing it. ...
OK this is the situation.. I am enabling fulltext search on a table but it only works on some fields.. CREATE FULLTEXT CATALOG [defaultcatalog] CREATE UNIQUE INDEX ui_staticid on static(id) CREATE FULLTEXT INDEX ON static(title_gr LANGUAGE 19,title_en,description_gr LANGUAGE 19,description_en) KEY INDEX staticid ON [defaultcatalog] WIT...
Hi All, Is there a system SP or dmv that allows me to select indexes in my DB based on columns and table names? What I am trying to do is to drop indexes based on columns with string datatypes as I am in the process of altering my collation settings. Thanks. ...
We use Access Project files to connect with SQL Server. Our SQL Server 2000 has been upgraded to SQL Server 2005 and now my project files are reporting an incompatibility and the error "This recordset is not updatable" when trying to modify data. So: 1) Will upgrading to Access 2007 and then creating project files solve this problem? ...
When using Linq-to-SQL, adding a column to an existing table, and setting a default value on that new column, it seems that Linq to SQL ignores the default value. Has anyone else experienced this behaviour? Is there a way to fix it, so that Linq-to-SQL or SQL Server automatically sets the default value? The column I added was of type ...