If, like me, you've been using PHP's PDO via the MSSQL driver, be aware that newer versions of PHP do not support MSSQL natively. Microsoft has it's own PHP driver now, or you can use the FreeTDS MSSQL extension, but I strongly recommend just switching to use the provided ODBC and PDO_ODBC extensions and save yourself the trouble. If you're already using PDOs, then you just have to create the object a little differently. For example:
$sql = new PDO("odbc:Driver={SQL Native Client};Server=ip.add.re.ss;Database=dbname;Uid=user;Pwd=password");
That worked for me. Not sure what you can do about the old mssql_guid_string function if you need that, though. I have the FreeTDS lib installed as well and that lets me call it, until I figure out how to do the same thing in ODBC.
Yes, ASP fans, that's a ONE LINE connection to a database. That's all anyone should need!
I'm trying to wrap my head around a nasty MS-SQL one-to-many JOIN problem and I just can't get it to work out.
Basically, I have table a and table b, where a.index = b.index. b can have multiple entries per a.index, but I only care about the first match.
What I want to do is get something like this:
SELECT a.field1, a.field2, b.field
FROM a
JOIN b ON a.index = b.index
WHERE a.field1='this'
...etc...
But for every additional entry in b, I get another row for a. The only field guaranteed to be unique in b is a uniqueid field. It's hard to explain because the whole thing is a complicated mess.
Basically, a is a table of invoices, and b is a table of products added to the invoice. Most of the time, we have one product per invoice, but not always. I only care about seeing the first match. I thought a MIN() or MAX() would get me where I need to be, but it doesn't work—the only thing that's ever different in b is the uniqueid.
It'd be great if you could just JOIN TOP 1 in MSSQL but you can't. Any ideas?
9:05pm Update: I just had a thought about this: how about JOINing to a view that is just a simple SELECT DISTINCT a.uniqueid FROM b? I'll have to try this out tomorrow…
I just spent the last five hours trying to move a Microsoft CRM (version 3) SQL/Reporting Server to a new piece of hardware. Despite having good documentation, it was an incredible chore, and in the end I still ended up having to abort the migration and go back to the old server.
It was really frustrating, because I was 95% of the way there—everything was working except the Reports, and they only weren't visible in the CRM application (if you called them directly, they worked fine. I suspect it has something to do with permissions/trusts between the servers, but I couldn't find it tonight. Plus, I'm just not very smart after working for 14+ hours straight.