How to change mm/dd/yyyy to desired format
hi guys, I have date in format mm/dd/yy.For eg:4/1/2009 means April 1 2009.I want to get format as April 1,2009 in sql.Can anybody help? ...
sql-server-2005
What to do when I want to use database constraints but only mark as deleted instead of deleting?
I am working in a project where database items are not deleted, but only marked as deleted. Something like this: id name deleted --- ------- -------- 1 Thingy1 0 2 Thingy2 0 3 Thingy3 0 I would like to be able to define something like a UNIQUE constraint on the name column. Seems easy, right? Let's imagine a scena...
Size of a single Record ? SQL
I have a scenario where every user has been allocated 2 MB of database space. Now I have to show Percenatge usage of their allocated space, to do so I need to know the size of single record in the Table. I have tried to use sp_spaceused and even wrote a custom procedure making use of datalength .. but they show vast difference in their ...
Does the C# Convert.ToDateTIme function read date as "dd/mm/yyyy" or "mm/dd/yyyy"?
Does the C# Convert.ToDateTIme function read date as "dd/mm/yyyy" or "mm/dd/yyyy"? I have the same application on my local machine which I uploaded to my remote shared server. It was working perfectly on my local machine reading "dd/mm/yyyy", but on my remote machine, it seems to read dates as "mm/dd/yyyy". I have the same culture setti...
Is it ok to load .net dlls into SQL Server as UNSAFE?
When creating a SQL Server CLR stored procedure, I noticed that I couldn't reference anything in the .net framework as I would normally. After some reading around, I realised that assemblies needed to be loaded into the database first. Therefore, I loaded in the ones I need but due to P/Invoke had to use the UNSAFE permission set. I...
Why doesn't SQL Server come preinstalled with .net Framework for CLR Integration?
If I want to reference something in the .net framework for use in my CLR stored proc, for example, I have to first load it into the Sql server database. Why isn't it preinstalled? Is it performance related or for security issues or what else? thanks. ...
Data synchronizing with SQL Server Management Studio
I was just about to buy redgate.com's compare package when I came to realize it seems highly unlikely a task like this isn't something that can be performed from the Management Studio. I have a development database and I have a live database. I develop my application adding stored procedures, correcting small things in the data structur...
How to recover database from MDF in SQL Server 2005?
I have an MDF file and no LDF files for a database created in MS SQL Server 2005. When I try to attach the MDF file to a different SQL Server, I get the following error message. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database...
Aggregate SQL Function to grab only the first from each group
I have 2 tables - an Account table and a Users table. Each account can have multiple users. I have a scenario where I want to execute a single query/join against these two tables, but I want all the Account data (Account.*) and only the first set of user data (specifically their name). Instead of doing a "min" or "max" on my aggregate...
SQL Server 2005 - Foreigh Keys with Cascaded Delete
Is there a way around this in SQL Server 2005? (It bugs me, and every time I encounter it I get in to a strop. But this is the first time I've had to deal with AND been on Stack Overflow. Please, save what little sanity I posess!) DimensionTable: id INT IDENTITY(1,1) FactTable: source_id INT NOT NULL, target_id INT NOT NULL I...
Moving data between oracle and sql server using Linked server
Hello Everyone, I have set up Oracle Linked server on Sql Server 2005 box using Oracle provider oledb and its working fine from sql server 2005 to oracle 9i, i.e. When i run distributed query from sql server i get data from oracle server to sql server. Now I don't have any clue how do i run distributed query from Oracle server and get d...
"Reports -> custom report" menu option in SQL server 2005 management studio express is missing. why?
I have installed: MS SQL Server 2005 9.00.4035.00 SP3 Express Edition Microsoft SQL Server Management Studio Express 9.00.2047.00 Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.080118-1840) Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 7.0.6001.18000 Microsoft .NET Framework 2.0.50727.3074 Opera...
Culture changing inexplicably during ASP.NET web application request.
I'm having a weird issue when where the date format of from the date fields changes from 4/16/2009 12:00:00 AM to 16/04/2009 00:00:00. I set the application to write out each stored proc that fired with the same corresponding date field if it exists. Here's what came out. You'll notice that the format switch inexplicable half way thro...
Exporting SQL Server data with redistributed components
I'm writing an application that is trying to bulk export data from a SQL Server database (with a given connection string) to a local file (in whatever format). Normally the sql server utility BCP.exe would be ideal, however, the bcp utility may not be available on the machine my app is running on. Also, bcp is not considered a redistri...
Where to move the business logic when moving it out of the database
I have a CRUD-heavy ASP.NET application with all the business logic in Stored Procedures. As an example, there is an UPDATE stored procedure that's ~500 lines long and contains large amounts of conditional logic, referencing multiple tables & UDFs. The proc takes in the field name being updated and the new value, sets a bunch of declar...
Troubleshoot Sql Server 2005 Connection issue
I have a .Net 3.5 App using a SQL Server Express 2005 database that allows connections over TCP/IP. The computer that has the SQL Server installed is part of a domain. The program is installed on 3 computers and share the same database. The connection string is in a file that is shared among all the computers. SQL Server is installed on ...
How to insert text with single quotation sql server 2005
I want to insert text with single quote Eg john's to table in sql server 2005 database ...
Weird SQL Server query problem
So I have this weird problem with an SQL Server stored procedure. Basically I have this long and complex procedure. Something like this: SELECT Table1.col1, Table2.col2, col3 FROM Table1 INNER JOIN Table2 Table2 INNER JOIN Table3 ----------------------- ----------------------- (Lots more joins) WHERE Table1.Col1 = db...
SQL Server 2005 Date Localization
I have a procedure that defaults todays date if no parameter is passed into it. CREATE Procedure GetDirectoryRenewalReminders (@DateToday datetime = Null) As If @DateToday Is Null Begin Set @DateToday = GetDate() End ... Go How can I ensure that the GetDate() value will default to the current UK time when called f...
How to compare the structure of two MS SQL databases?
I need a free tool or some kind of tip/technique to compare the structure of two Microsoft SQL databases. Preferably MS SQL 2005. The technique I tried to make work was generating scripts from both databases and then comparing the two files but SQL Server generates object in random order. ...