What is 'type' in systypes?
In sys.systypes ... what exactly is 'type'? What exactly does .... bigint > int > varchar ... order mean when I execute select * from sys.systypes order by type desc ...
sql-server
Why doesn't Microsoft.SqlServer.Smo appear in the Add References dialog
I have used Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll in a number of VS2008 projects but they don't appear in the VS2010 Add Reference dialog. Why did they disappear and how can I add them to my VS2010 project? ...
How to add a default value to an already existing column?
I have an existing column in my SQL Server database. I have tried about everything I can think of but can not get a default value to be added to the column. What works in every other database is alter table mytable alter column mycolumn set default(now()) --mycolumn is a datetime How do I do this in SQL Server? The error I get f...
C# and SQL - sub select from a table parameter
Below is a code snippet for passing a table as a parameter to a query that can be used in Sql Server 2008. I'm confused though about the "SELECT id.custid FROM @custids id". Why does it use id.custid and @custids id...? private static void datatable_example() { string [] custids = {"ALFKI", "BONAP", "CACTU", "FRANK"}; DataTabl...
Handling auto-incrementing IDENTITY SQL Server fields with LINQ to SQL in C#
I'm building an ASP.NET MVC site that uses LINQ to SQL to connect to SQL Server, where I have a table that has an IDENTITY bigint primary key column that represents an ID. In one of my code methods, I need to create an object of that table to get its ID, which I will place into another object based on another table (FK-to-PK relationshi...
Picking Random Names
I saw an interesting post sometime back but with no solution. Trying luck here: There is a table which contain 10 names (U1, U2, U3..and so on). I have to choose 5 names everyday, and display one as the Editor and 4 as Contributors While selecting the random names, I have to also consider that if one user is selected as Editor, he can...
Is SSIS able to query flat files from another Windows Server?
I pretty new SQL Server Integration Server (SSIS) user. Is SSIS able to query data from text files located in another Windows Server? I mean that when SSIS is installed on Windwos Server A, is SSIS able to query data from e.g. one folder containing text files in Windows Server B (under same domain)? I have used only SAP BO Data Integrato...
Resource database in SQL Server
I need to know what the Resource Database in SQL Server is, and why can't we see this physical file in the SQL Server installed directory? ...
Is it possible to make a non-nullable column nullable when used in a view? (sql server)
Hi, To start off I have two tables, PersonNames and PersonNameVariations. When a name is searched, it finds the closest name to one of the ones available in PersonNames and records it in the PersonNameVariations table if it's not already in there. I am using a stored proc to search the PersonNames for a passed in PersonNameVariationand...
sql user defined function
for a table valued function in sql why cant we write sql statements inside begin and end tags like- create function dbo.emptable() returns Table as BEGIN --it throws an error return (select id, name, salary from employee) END go while in scalar valued function we can use these tags like create function dbo.countemp() returns int as b...
how to Invoke User-Defined Functions That Return a Table Data Type
here my code- create function dbo.emptable() returns Table as return (select id, name, salary from employee) go select dbo.emptable() error: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.emptable", or the name is ambiguous. while when I run sp_helptext emptabl...
Opaque tenant identification with SQL Server & NHibernate
Howdy! We're developing a nowadays-fashionable multi-tenanted SaaS app (shared database, shared schema), and there's one thing I don't like about it: public class Domain : BusinessObject { public virtual long TenantID { get; set; } public virtual string Name { get; set; } } The TenantID is driving me nuts, as it has ...
Monitioring Locks
We are using Livelink for our records management and if a user moves a folder with lost of sub folders it presents a lock on the database and slows the whole system down. Dispite sending many warnings out to users this is still happening. Is there any sort or monitoring tool that will give us a an early warning system as to when the lock...
How can i rename my column in sql table?
how can i rename column name via alter table in Ms sql 2005 forexample: alter table tablename rename "old col name" to "new col name" ...
What is better: sql string or stored procedures?
I have a data access layer which uses the Enterprise Library. Now I was wondering, which is better: Using stored procedures to create my SQL, or writing SQL strings within the data access classes? ...
DBCC CHECKDB WITH DATA_PURITY gives out of range error
Hi there, I have restore a SQL Server 2000 database onto SQL Server 2005 and then run DBCC CHECKDB WITH DATA_PURITY and I get this error: Msg 2570, Level 16, State 3, Line 2 Page (1:19558), slot 13 in object ID 181575685, index ID 1, partition ID 293374720802816, alloc unit ID 11899744092160 (type "In-row data"). Column "NumberOfShares...
select xml data column into flat reporting table
We have a xml column in SQL Server 2008. We need to do reporting off the data in the xml so we're going to select the xml into a flat table. The flat table has columns that correspond to various nodes in the xml. What is the best way to do this using SSIS? Is this a good approach? Or should we just try and write the reports directly of...
MSSQL2008: DTC Transaction - Internal abort
Hi all, I write a small own replication - a trigger which fires an DTC INSERT to another server (one reason for my own "replication": while trigger is running it calculates some data, another: it works from an express version to an express version). When I do the initial insert from the same Host with the windows authentification it wo...
Mass data store with SQL Server
We need management 10,000 GPS devices, each GPS device upload a GPS data every 30 seconds, these data need to store in the database (SQL Server 2005). Each GPS device daily data quantity is: 24 * 60 * 2 = 2,880 10 000 10,000 GPS devices daily data quantity is: 10000 * 2880 = 28,800,000 Each GPS data approximately 160Byte, the amoun...
Is there a way to prevent Triggers from being disabled?
I have a trigger on a table that should never be disabled. It performs certain checks and there have been occasions when other developers have disabled it to get around it. This is not good so I want to be able to turn off trigger disablement on this table alone. Is this possible? If not, any suggestions please. thanks. WORKAROUND:...