I'm putting together a database of locations for looking up nearest locations for a given address. As I started laying out the table going about my business I wondered what other people were doing and if there was a 'best practices' for some common datatypes. Such as phone numbers, addresses and even latitude and longitude. This is wha...
I think I've worked myself into a pretty stupid corner, here. Let me show you the schema. I've got a lot of things around it already but I've suddenly realized a big problem. Units - UnitId Rings - RingId Keys - RingId (FK) - KeyId (PK) - KeyLiteral (FK) - KeyValue Literals - LiteralId - LiteralValue It was good for a while. Basica...
I have this function in SQL Server to replace single quote. But when I insert single quote it throws an error on Replace(@strip,''','')) Create Function [dbo].[fn_stripsingleQuote] (@strStrip varchar(Max)) returns varchar as begin declare @CleanString varchar(Max) SET @var=(Replace(@strip,'','')) return @var end ...
Use OPENXML to get dt element in MSSQL 2005. How can I get xmlns:dt element in xml? For example, get a result set of two rows that list product id and country code. 121403 GBR 121403 USA declare @xmldata xml set @xmldata = '<?xml version="1.0"?> <data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://w...
We are trying to connect to a SQL Server instance used by the ACT CRM system. They have managed to lock things down so it isn't possible to connect to the SQL back-end using ODBC (there is some special utility that will add ODBC support that you can install if you purchase the primo version of the software, but that's just crazy). The ...
I've come across a strange query embedded in an application (yeah, great!): SELECT PersonId , Salutation , Email , Postcode FROM vw_NewsletterSubscriptions WHERE PersonId IN (SELECT PersonId FROM vw_NewsletterSubscriptions) AND NewsletterTypeID=1 AND UnSubscribeDate Is NULL GROUP BY PersonId , Salutation ...
I am currently using SSRS 2005 subscriptions to send email on certain events. I have maxed out the allowed number of subscriptions though and it has brought the emails to a halt. Is there anyway for me to allow more subscriptions? ...
I've created an ADO.NET connection manager, and a DataReader source with the following SQL Command: select 'test' as testcol INTO #tmp select * from #tmp If I click the refresh button in the DataReader component, I get SqlException "Invalid object name #tmp". The SQL statment itself is clearly valid and executes properly in s...
Hi, I am sorry if this sounds like a silly question, but I have googled for a while for a solution and no success. I am trying to establish a connection from VS 2008 to SQL Server. I am trying to use an user that my web application has been successfuly using to access the database from in its connection string. But when doing it from VS ...
I kind of have a feel for why the view is slower: The where clause is probably not applied at the same time. The results do seem to be the same, though. I am not sure what I can do about this, short of not using a view...which is not ideal, as I added the view to avoid code repetition, and I don't want to remove it if it isn't necessar...
I'm working on a hibernate entity mapping for a database view; when I do a criteria query against it, hibernate is generating bad SQL. Any help figuring out what the problem is with my mapping would be greatly appreciated! I have two mapped entities which I am trying to grab from a database view; the view has no other columns, just the ...
I have a table Posts which has a memberID and createdDate. I need to return the most recent post per member and the posts must be order with most recent at the top. I am not sure how to do this with Sql Server, can anyone help? ...
Okay so I have the sql to work this out as asked in the stackoverflow question here. Does anyone know how to translate this to linq 2 sql? I'm guessing that the easiest way is to add a stored procedure, but I am curious to see if it can be linq-a-fied :P select p.* from post p join ( select memberId, max(createdDate) as maxd ...
Say you have WIDGETS table: WidgetID int ColorID int (Lookup values: Red, Blue, Green, Yellow, Black, Brown) SizeID int (Lookup values: Small, Med, Big, Large) Weight int (Lookup values: UltraLight, Light, Normal, Heavy, UltraHeavy Ok that's the general idea of the table. I don't need the lookup names, what I do have is the lookupva...
I'm building my own clone of http://statoverflow.com/sandbox (using the free controls provided to 10K users from Telerik). I have a proof of concept available I can use locally, but before I open it up to others I need to lock it down some more. Currently I run everything through a stored procedure that looks something like this: CREA...
Hi, I want the best possible solution for the below stated problem. I am using SQL Server 2005 and asp.net 2.0. I have a master table where all the test scripts of different projects are stored. From the test scripts in master table i need to create a module and add the required test scripts to the module. UI design is: Create New Mod...
I want to migrate my MSSQL database into MYSQL. Is there any open source or free software for that? ...
Hi, I am using Sql server 2005. I have to give SQL insert script to my client. But before it insert into table. I want to check if that records already exists in table, it should not insert. Below are the insert query INSERT INTO [UserPoint].[dbo].[tblStatus]([Type],[Name],[DisplayOrder]) VALUES (UsageLevel,High,1) INSERT INTO [UserP...
I have already searched through SO and could not fins a workable solution for this. I am just trying to figure what is the syntax for multiple inner joins in Linq to Entities. Thanks ...
When I try to import an SQL Server database into access I am getting this error I am using OS: Windows Vista App: MS Access 2007 DB: MS SQL 2005 Error: Connection failed SQLState; "01000' SQL Server Error: 52 [Microsoft] [ODBC SQL Server Driver] [DBNETLIB] ConnectionOpen (Connect()). Connection failed; SQLState: '08001' SQL Server Err...