In MS SQL Server 2005, how can I create a script consisting off all the objects in the database (tables, stored procedures, views)? I want to use the script to create the database using SMO or to update its objects (alter, drop) if they already exist. I know about the scripting feature in SQL Server Management Studio, I am however not fa...
When is it appropriate to use connection pooling and what happens when the maximum number of connections is reached? ...
I have a small job that takes a text file of email/zip codes and inserts them into a sql server 2005 table. It reads each line of the source file, checks to make sure that it parses into a valid email address/zip code, creates a sql insert command, adds it to a string builder and eventually executes it. I want to do a single execute inst...
What are the required steps to properly allow domain users access to reports via the Reporting Services web site? What I've tried: Added users through Reporting Services site (i.e. http://servername/Reports) Given users access through SQL Management Studio Result: users are continuously prompted by the browser for their credential...
I have recently stumbled upon a problem with selecting relationship details from a 1 table and inserting into another table, i hope someone can help. I have a table structure as follows: ID (PK) Name ParentID<br> 1 Myname 0<br> 2 nametwo 1<br> 3 namethree 2 e.g This is the table i need to sel...
Hi all, Currently our .NET application constructs XML data in memory that we persist to a SQL Server database. The XElement object is converted to a string using ToString() and then stored in a varchar(MAX) column in the DB. We dind't want to use the SQL XML datatype as we didn't need any validation and SQL doesn't need to query the XML...
I have a console application (in C#) to read data from SQL server (Microsoft SQL 2005) and write data to SQL server. What I need right now is to add a trigger to a table to exec the console application when any row of data is changed. Not sure what SP available on Microsoft SQL server (2005) to launch a console application? and how can ...
Hi I am trying to return a collection which may potentially have null date values. However, if they are null - I want to keep them that way and not just use a arbitrary date instead. If it was a string, I'd check for nulls like thus: calEventDTO.occurrenceType = dr.IsDBNull(10) ? null : dr.GetString(10); How would I do the same for a...
For our deployments, I just want to drop the existing stored proc, then re-create in the same script. The script wizard only seeems to give me a mutually exclusive option - I'm looking for a Drop-Then Create Option. Am I missing something? ...
I have a csv file : 1|1.25 2|23.56 3|58.99 I want to put this value in a SQL Server table with SSIS. I have created my table : CREATE TABLE myTable( ID int, Value numeric(4,2)); My problem is that I have to create a Derived Column Transformation to specify my cast : (DT_NUMERIC,4,2)(REPLACE(Value,".",",")) Otherwise, SSIS don't ...
I have a select query. I want to replace that select query with another select if that returns no rows. For instance lets say I have: Select * from Temp2 if (@@rowcount=0) select * from Temp1 At the c# end, I retrieve it as a dataset. So if no rows are returned, it would do another select. But this select would be in Tables[1] not T...
I have a SQL 2005 database with one large table. I have ran a DELETE to prune some items from the table and I want to free the space back to the OS. I have tried the following commands DBCC SHRINKDATABASE (MyDB, TRUNCATEONLY); DBCC SHRINKFILE (MyTable, TRUNCATEONLY); and I have also rebuilt the clustered index with the following com...
I have a MS SQLServer 2005 table which includes an ipaddress column, and I would like to count entries by subnet. Unfortunately the ipaddress column is a varchar with sample data like: 192.168.10.20 10.1.2.3 I think the best approach would be to convert the ipaddress to an integer, and then I could apply the appropriate bitmask. Are...
Hi, We have a system with 2 clients (which will increase). These two clients connect to the same server/database, however neither should be able to see the others sensitive information. There is however some shared non sensitive information. There is also an administrative department who does work on behalf on both of the clients. They...
Hi, I have an array of bytes in my C# class, and need to persist it into a SQL Server 2005 database using NHibernate. What SQL data type should I use for the column, and how do I got about using NHiberante to do the persistance for me? If the answer is a custom NHibernate type, is there any sample code knicking around to do this? Would...
I have a database that I'm publishing to a remote site using Snapshot replication (only method that will work given the application and database schema). Because the replica database tables are unavailable while the database is refreshing, I am taking and delivering snapshots twice daily. For the target user population this is okay 99%...
Hi All, I need to migrate the data (including the tables, procedures, schemas) from Sql Server 2005 to Oracle 10g. Can someone suggest me a way to do it? Thanks. ...
How can I backup and restore SQL Server 2005 Agent job schedules? ...
I have a Microsoft SQL server 2005 and I tried to enable Broker for myDatabase with those TSQL: SELECT name, is_broker_enabled FROM sys.databases -- checking its status 0 in my case ALTER DATABASE myDatabase SET ENABLE_BROKER The alter database takes long time to process. Now it is over half hour and it is still running. Not sure if...
SQL Server 2005 Question: I'm working on a data conversion project where I'm taking 80k+ rows and moving them from one table to another. When I run the TSQL, it bombs with various errors having to do with converting types, or whatever. Is there a way to find out what row caused the error? ===================== UPDATE: I'm performing...