Set a existing column of MS SQL table as NOT NULL
How to Set a existing column of MS SQL table as NOT NULL? ...
sql-server-2005
MSSQL2005: Restore database without restoring full text catalog
When restoring my database i have a problem with the physical file of the full text catalog being in use. The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\MyCatalog' cannot be overwritten. It is being used by database 'demo2'. I use this restore statement RESTORE database demo from disk = N'c:\temp\demo.bak' WIT...
Minimum requirements to use AspNetSqlMembershipProvider
I'm using an AspNetSqlMembershipProvider for my application. On my development machine I recently deinstalled SQL Server 2005 Express and Management Studio Express so that I could install the full version of Management Studio. I chose not to install SQL Server 2005 because, well, I don't want to have unnecessary things running on my mac...
SQL SERVER 2005: Drop all the tables, stored procedures, triggers, constriants and all the dependencies in one sql statement.
Hi, Is there any way in which i can clean a db in SQl Server 2005 i.e by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one sql statement. REASON FOR REQUEST: I want to have a db script for cleaning up an existing db which is not in use rather than creating new ones, specially ...
Inserting an attribute in multiple XML Nodes using XML.modify() in SQL 2005
I have an @XML document created from a single select statement. <root> <node> <node1> <targetNode> </targetNode> </node1> <node1> <targetNode> </targetNode> </node1> <node1> <targetNode> </targetNode> </node1> </node> <node> ...... </node> </root> I want to insert the xsi:nil as an attribute of 'ta...
Dynamic sql statement to update a variable
Hi, my sql statement is something like this below DECLARE @OLD_NAV_VALUE AS INT DECLARE @FINAL AS INT SELECT @OLD_NAV_VALUE = [col1] from TBL_BA where DATE = @id_Date SET @FINAL = @OLD_NAV_VALUE * 50 But the problem i am haveing here is that the column name in the select statement which is given as [col1] is a dynamic value. So i a...
How do you get SQL 2008 reporting services to securely work for multiple apps?
We have a setup where we have multiple instances of an application - one instance for each customer. We call a lot of our reports via URL, passing in parameters on the querystring. Early on, when we were on 2005, we identified a problem with this: I could change my querystring a bit and get into someone else's data. We got around the...
Visual Studio hangs when deploying a cube
Hello All, I'm having an issue with an Analysis Services project in Visual Studio 2005. My project always builds but only occasionally deploys. No errors are reported and VS just hangs. This is my first Analysis Services project so I am hoping that there is something obvious that I am just missing. Here is the situation I have a cub...
Delete backup file in transact-sql during database copy/restore
I have a "copy" stored procedure for a database. During the copy, I backup the database with something like: exec('backup database [' + @source + '] ' + 'to disk = N''' + @backupdir + '\copybackup'' ' + 'with noformat, noinit, name=N''copybackup-full'', ' + 'SKIP, NOREWIND, NOUNLOAD, STATS = 10;'); And then create and emp...
Ordering hierarchy from recursive query results in SQL 2005
I've got a 'Task' table with the following columns (the TaskOrder is for ordering the children within the scope of the parent, not the entire table): TaskId ParentTaskId TaskName TaskOrder I've got this CTE query to return all the rows: with tasks (TaskId, ParentTaskId, [Name]) as ( select parentTasks.TaskId, parentTa...
any way to detect a TimeStamp column in a DataTable from SQL Server (fillSchema???)
When updating a datatable from a "select * from sometable", I am iterating the columns in the datatable to dynamically create an update or insert statement, based on datatable.rowstate and DataTable.PrimaryKey. However, I can't seem to find any property that indicates whether a column is a TimeStamp in SQL Server and must therefore be i...
Complex queries in SQL Server 2005 database
I have a table in my database in which records conceptually can be children of other rcords. The table has a non-null name field. I need to ensure that each name in a set of children is unique, but not across the entire database. I'd like to enforce this using a constraint inside the Database. What's the best way to accomplish this? I kn...
SQL Server 2005 date comparison - collation issues
In my DB, I have a table that was created from an Excel sheet, via a Linked Server option. I am now trying to compare its contents versus one of my main tables. The tables I am comparing are in the same database. There is a date column in both tables, both types are datetime and have Collation of SQL_Latin1_General_CP1_CI_AS, the s...
How to move a database using a differential backup?
Hi, I'm using SQL Server 2005. I need to move a database to a new server with minimum downtime. Currently my option is to take everything offline, perform a backup, copy the backup to the new server, restore the backup and bring everything back online. The problem here is that the backup file is about 10Gb, therefore, it takes about ...
Obtaining the Following Error in SQL Server 2005 <The server principal "XYuser" is not able to access the database "Ydb" under the current security context.>
System Specifications... Microsoft SQL Server Management Studio 9.00.4035.00 Microsoft Analysis Services Client Tools 2005.090.4035.00 Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852) Microsoft MSXML ...
Must full text index catalog name be unique on same SQL server? (MSSQL 2005)
Consider there is one MS SQL server 2005 instance with 3 databases on it ... Dev, RC and Production. All of them have (more or less) the same schema. All of them have a "same" table with full text index on it. What happens if the name of full text index catalog is the same on all 3 databases? Do they share the same physical catalog? Is...
Sql Server Agent Required For Remote Connections?
I'm connecting to a remote sql server instance with the following connection string "Network Library=DBMSSOCN;Data Source=xx.xxx.x.xxx,1433;Initial Catalog=MyDatabase;User Id=MyUserId;Password=MyPassword;Connect Timeout=120;" Sql Server Browser is running Local and remote connections are allowed using TCP/IP only The application ran...
SQL insert statement with select
I have the following SQL statement INSERT INTO A_Table (field1, field2) SELECT field1, field2 FROM B_Table WHERE field1 NOT IN ( SELECT field1 FROM A_Table); basically, I need to copy all the data from B_Table to A_Table, with a constrain: field1 from B_Table not existing in A_Table. I need to add this constrain since field1 ...
Get just the Date from grouping in select from DateTime column in SQL Server
I need to group some records based on a date but it is a date and time field and I need to ignore the time part of is and just group by the date part - here is my SQL as it stands: SELECT AutoShipItems.CustomerID,AutoShipItems.NextOrderDate, Customer.FirstName,Customer.LastName, Customer.EmailAddress FROM AutoShip...
SQL Functions and Recursion
Suppose you write a function in SQL Server that conditionally calls itself. If you are writing the function from scratch, finish it, and try to create it, SQL Server complains. The complaint is that the function you call from your function doesn't exist. Of course it doesn't, it's recursive! To actually make it work, you have to comm...