Storing video duration time in sql server.
What's the most appropriate type used to store the duration time information of a video in sql server? ...
sql-server
Schema, Owner for objects in MS SQL
By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema (I think ms sql 2000 calls it owner, while ms sql 2005 calls it schema) The owner/schema is really a role or user in the database. I've always left the default of dbo, but I've recently seen some examples in microsoft training books where some of ...
View output of 'print' statements using ADOConnection in Delphi
Some of my MS SQL stored procedures produce messages using the 'print' command. In my Delphi 2007 application, which connects to MS SQL using TADOConnection, how can I view the output of those 'print' commands? Key requirements: 1) I can't run the query more than once; it might be updating things. 2) I need to see the 'print' results e...
Migrating MOSS 2007 from SQL 2000 to SQL 2005
We have an installation of MOSS 2007 that is using MS SQL 2000 as the backend database. The web front end for MOSS is on one server and the database on a separate server. We would like to move the database to a MS SQL 2005 server. Is there a recommended approach for doing this? Does anyone know of any resources that offer tips, trick...
T-SQL: How to get user-defined data-type of the column by it's ID or smth?
I have the query like this SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE FROM MY_DB.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'OrderId' ORDER BY TABLE_NAME GO OrderId column is of user-defined data type. But DATA_TYPE in the query shows underlying system type (i.e. bigint). How to show user-defined type name? ...
Why don't more .NET applications use MySQL or a DAO that allows for the use of MySQL?
I suppose this question could just as easily be applied to PHP with regard to MSSQL. However, with some versions of MSSQL being so expensive, I always wondered, what is the real advantage of choosing it over MySQL for .NET development? Is it really difficult to use .NET tools in conjunction with a MySQL database? Why not create some sort...
How to migrate from MS Access to SQL server 2005
I have a VB.NET windows application that pulls information from an MS Access database. The primary role of the application is to extract information from Excel files in various formats, standarize the file layout and write that out to csv files. The application uses MS Access as the source for the keys and cross reference files. The w...
Sql Data Type for Primary Key - SQL Server?
Which sql data type should we use for number bases primary key: int bigint numeric float ...
What causes DbDataAdapter.Fill to fill a DataSet with the incorrect number of columns?
I have a windows service connecting to a SqlServer database on the local box. This works fine most all the time. At a large customer, however, the database connectivity gets corrupted for some rare and unknown reason. When this happens, calls to DbDataAdapter.Fill return a DataSet with a different number of columns than in the select ...
Tunneling proxy on VS 2008.
Is there a way to tunnel a proxy to access a remote sql server database on visual studio 2008? I'm working in a project at college that uses a sql server database but the access is restricted to the internal network. I'd like to access the database from my home too. Thanks in advance. ...
Why use "Y"/"N" instead of a bit field in Microsoft SQL Server?
I'm working on an application developed by another mob and am confounded by the use of a char field instead of bit for all the boolean columns in the database. It uses "Y" for true and "N" for false (these have to be uppercase). The type name itself is then aliased with some obscure name like ybln. This is very annoying to work with for...
SQL custom functions in Enterprise Library
Is there a way to execute SQL custom functions with Enterpise Library? I've tried Database.ExecuteScalar() but for some reason it returns null. This is my function: Database db = DatabaseFactory.CreateDatabase("ConnectionString"); DbCommand cmd = db.GetStoredProcCommand("FunctionName"); db.AddInParameter(cmd, "Value1", DbType.String, P...
Calculate mode (or frequency) distribution of a value over time in SQL Server
Given the following table, how does one calculate the hourly mode, or value with the highest frequency by hour? CREATE TABLE Values ( ValueID int NOT NULL, Value int NOT NULL, LogTime datetime NOT NULL ) So far, I've come up with the following query. SELECT count(*) AS Frequency, DatePart(yy, LogTime) as [Year], DatePart...
Display active session in SQL Server 2005
In Oracle, there's a view called V$SESSION that lists all active sessions in database. Is there any similar view in SQL Server 2005? ...
How Can I Log and Find the Most Expensive Queries?
Hi folks The activity monitor in sql2k8 allows us to see the most expensive queries. Ok, that's kewl, but is there a way I can log this info or get this info via query analyser? I don't really want to have the Sql Management console open and me looking at the activity monitor dashboard. I want to figure out which queries are poorly wri...
SQL Server 2000 Personal Edition: SQL Query Analyzer is not appearing
I installed SQL server 2000 personal edition in my pc. I am now accessing it using windows authentication account. I can use the enterprise manager but when I try to access the sql query analyzer, there aint no window appearing for the query analyzer. Can someone help me on this? Thank you. ...
Best-practices for localizing a SQL Server (2005/2008) database
Question I'm sure many of you have been faced by the challenge of localizing a database backend to an application. If you've not then I'd be pretty confident in saying that the odds of you having to do so in the future is quite large. I'm talking anout storing multiple translations of texts (and the same can be said for currency etc.) f...
Escape a string in SQL Server so that it is safe to use in LIKE expression
How do I escape a string in SQL Server's stored procedure so that it is safe to use in LIKE expression. Suppose I have an NVARCHAR variable like so: declare @myString NVARCHAR(100); And I want to use it in a LIKE expression: ... WHERE ... LIKE '%' + @myString + '%'; How do I escape the string (more specifically, characters that ar...
SQL Server query against two linked databases using different collations
Hi folks I've got 2 remote databases as part of a query select p.ID,p.ProjectCode_VC,p.Name_VC,v.* FROM [serverB].Projects.dbo.Projects_T p LEFT JOIN [serverA].SOCON.dbo.vw_PROJECT v on p.ProjectCode_VC = v.PROJ_CODE The problem is that serverB uses collation Latin1_General_BIN and serverB uses Latin1_General_CP1_CP_AS and the query ...
Get SQL Server Connection info from app without debugging?
We are troubleshooting a SQL Server 2005 connection issue on a closed-source third-party ASP.NET application. The application is a .NET application and the developing company is using a custom formatted configuration file that stores the SQL Server authentication information. The application when trying to connect to our SQL Server box...