I know I can specify it inline like this: CREATE TABLE dbo.MyTable ( Id INT NOT NULL CONSTRAINT PK_MyTable_Id PRIMARY KEY IDENTITY, Created DATETIME NOT NULL CONSTRAINT DF_MyTable_Created DEFAULT GETDATE() ) But I'd like to put all my constraints at the end of the table definition to keep them separate. I can't seem to find ou...
I need to return two fields from a database concatenated as 'field1-field2'. The second field is an int, but needs to be returned as a fixed length of 5 with leading 0's. The method i'm using is: SELECT Field1 + '-' + RIGHT('0000' + CAST(Field2 AS varchar),5) FROM ... Is there a more efficient way to do this? ...
Hi I have a C# code which does lot of insert statements in a batch. While executing these statements, I got "String or binary data would be truncated" error and transaction roledback. To find out the which insert statement caused this, I need to insert one by one in the SQLServer until I hit the error. Is there clever way to findout ...
I have a database that has a 28gig transaction log file. Recovery mode is simple. I just took a full backup of the database, and then ran both: backup log dbmcms with truncate_only DBCC SHRINKFILE ('Wxlog0', TRUNCATEONLY) The name of the db is db_mcms and the name of the transaction log file is Wxlog0. Neither has helped. I'm ...
The ident_Current function is returning a null on sql server 2005. After looking at some documentation it looks like it needs db_owner permissions to have visibility meta data, but thats only specified in documentation for sql server 2008.. can someone confirm that its the same for sql server 2005 ? , i cant find any documentation to co...
We have an in memory DataTable in a CLR procedure. After significant processing the DataTable has a lot of data that we need to load into a table in the database. Unfortunately, since we are using a context connection SqlBulkCopy will not work (throws error: The requested operation is not available on the context connection outline of c...
Has anybody any experience in using Gupta (formerly Centura) Team Developer? If so, what do you think of it in terms of its capability to support the development of mature, scalable, maintainable applications? Thanks ...
What is the recommended batch size for SqlBulkCopy? I'm looking for a general formula I can use as a starting point for performance tuning. ...
Our office uses SQL Server 2000, and by and large it has served our needs well. I'm looking into the possibility of upgrading to SQL 2005 or 2008 because of the new features not found in 2000 (when I saw Index usage statistics which told me which Indexes were the most used and which were never used, that blew me away). I'm not too conc...
I know that database transactions are used to ensure all the statements in the transaction will be executed or none of them will. But what about locking and thread safety, for example if I have an sproc which affects multiple tables and I use a transaction in that sproc, that sproc is called from different clients at the same time, will...
I added a couple of stored procedures to a SQL Server 2005 database at our company. The database uses Windows Authentication. Other users cannot see the procedures, although they can view the database/tables/stored procedures using SQL Server Management Express. What am I missing? ...
I'm trying to configure the excellent ScrewTurn Wiki product on my server, and haven't been able to make sense of the documentation. I'd like to configure it to use SQL Server for storage. Do I still need to specify a valid value for the PublicDirectory key in web.config? This is how I've tried to configure the connection so far, but...
how to Generate xml file in sql server 2005 using tsql ...
Hi, I'm having problems with our MSSQL database set to any of the Turkish Collations. Becuase of the "Turkish I" problem, none of our queries containing an 'i' in them are working correctly. For example, if we have a table called "Unit" with a column "UnitID" defined in that case, the query "select unitid from unit" no longer works beca...
I work for a large Canadian retailer, and we're currently considering using CozyRoc for a large ETL project of ours. I was wondering if anyone could recommend the software, ideally through an e-mail chain or over the phone. ...
Hello Team, We have a website where we need a scheduler to receive notifications (e-mail) on specific time. eg. a person setting reminder at 5 PM to attend the meeting at 4:45 PM, will receive email at 4:45 PM about same. As this site is hosted on shared server, we don't have any control over the server to run any SQL Job or scheduler...
Is there a way I can give developers permission to grant a user permissions over objects without giving them the option to create users or functions? I'm trying to limit developers permissions, I recently found out that developers had db_owner permissions in dev and prod environments! So I'm doing my best to stop this madness. Any good...
I'm trying to store a C# double in MS SQL 2005 as a float. However, there seem to be a range of small numbers which are valid doubles but which aren't valid floats. For example, when I try to store the value 1e-320 I get the error "the supplied value is not a valid instance of type float". This is consistent with the documentation for S...
Curious whether folks have setup 2 way transactional replication on the tables ASP.NET uses for SqlServer stored session state (ASPStateTempSessions and ASPStateTempApplications) and the tables used for membership, role, and personalization? How did it work out? Were there any gotchas? ...
Using Visual Studio .NET 2008 or 2005, is there a way to automatically generate properties for each column in an SQL Server database table? I am assuming other code-generation software exists that will do this. I know with Visio I can connect to my database and it will generate diagrams by table, this would be similar to that. ...