Support MS SQL databases

Just correctly updated the version number. This would really be for 6.x-dev but there is not tag yet. As per http://groups.drupal.org/node/1496 I think we should keep the discussion, patch going here to get this rolling for D6!

I took a look at some existing MS SQL code and saw that it needs a lot of work. I'm marking this postponed for now, but if anyone with more MS SQL know-how is welcome to take a stab. :)

Okay, I'll stop being a wuss...sortof. Here is a half-done drupal.mssql.inc. I'm just going to post this in increments just in case any one else feels the need to add to it. Note: this is unworking, half-done code...do not try and use it. I did somewhat of a port of Souvent22's code, but it could use some more eyes. I'm trying to keep track of // TODO:'s in there so we know what needs work.

Another note, this was copied over from database.mysql.inc to get a good starting point so there is still some mysql calls in there that need mssql equivalents. Any one out there with better MS SQL knowledge than I?

Changed to version 6.x-dev.

Updated patch for HEAD. Still A LOT of work needing to be done, doesn't even install yet. Just want to keep the progress here in case anyone wishes to help out.

http://drupal.org/node/98835 relates to the installer not working with backslashes for db host name.

Posting to track thread

RobRoy,

I've had back and ofrht discussions about 1 patch vs multiple, with more people leaning towards multiple patches. But I agree, this should be 1 patch.

So, should I use your current patch, merge with my patch, and roll out another "1 patch to rule them all" ?

Hey Souvent22,

I took the mysql code from HEAD and changed stuff over that way. I'm not sure how you went about it, but the base of my code might be fresher and more in sync with HEAD. It may be a better starting point from that regard, but I've not looked at your new code, just the old stuff. I agree that one patch is the way to go.

Thanks for the work on this, when I get some time I'll try and do a bit of a merge. Keep posting your patch as you make progress so we can both work off it.

:)

Also, I have very limited knowledge of MS SQL, so you'll have to let me know the best way to go about things from a MS SQL perspective.

Merged patch. "1 patch to rule them all" :) .

Update about the above "merged" patch.
With that patch against head, the install "runs". You will see table show up perfectly (kind of) in SQL Server. However, the problems mentioned on the drupal groups site still persist, which are (snippet from the groups):

NOTE: This has been tested with:

• PHP 5.1.6
• SQL Server Standard 2005
• ntwdblib.dll v. 2000.80.194.0 (you will need this updated version and not the one that comes with your php install)

So, it's current status, it runs and creates the db nicely. But, there are some bugs to be had:

• the system table for some reason has the status column set to 0 except for system.module for some reason
• when an error happens, nothing it thrown back because the connection immediatley closes. perhaps we need to look at a better way to trap and handel errors. (the system just sits until execution timeout)
• after manipulating the system table, i had to modify the blocks.module sql call because it calls a "distinct" on the page column (which is a text datatype) and throws an error cause mssql says its not comparable, so i took off the distinct modifier for now, and i'm investigating that SQL statement.

Hopefully, now that we have 1 "merged" patch, this effort and move forward faster. :).

Full working version.
This installed in my system many times with no errors or noticies.

This patch addresses 2 issues:

1. A bad SQL statement crashes the system and causes a time out.
I have addressed this by just placing a @.
Statements that produce an error are just catalouged into the watchdog.

2. The system table seems to not have enabled any modules.

The problem with all the 'statuses' being 0 was in the DDL of the system table. the status was set to an int field (rightly so), however, drupal (in module.inc) checks with $existing === '0' (meaning a char, and it must EXACTLY be a char). So a simple change to the DDL, and voila.

I have a tested this patch many times, and it works great. I will only put my stamp of approval on this patch if you are running SQL Server 2005 (if you wnat to test, sql server 2005 express is free and pretty robust for a free DB). you must also be using the latest SQL Server client. See the comments above for a link and more information.

Note: You will have to make a change in your blocks.moule. Remove "DISTINCT" from 2 places (it's only in 2 places), or your blocks will not show. This is a bug that I am addressing in another patch. The sql in blocks.module needs to be changed.

Check this out to abstract the DISTINCT part: http://api.drupal.org/api/HEAD/function/db_distinct_field

Will check the other stuff later. Thanks for working on this!

There was a small issue in the way the MS SQL escapse strings (', ", etc.) vs. other DB's. Alter the drivers escape string function. Works very nicely now.

RobRoy,

Ah thanks. However, the query would still not work because it is the general "logic" that MS SQL doesn't like. In MS SQL's view, you have a full text field (pages) which you are asking the DB compare to other rows and see if it's "distinct". Well, since a text field can be, 2 GB (i'll have to check my figures, but pretty large), it says, no way, your logic is in correct. so the query needs to be re-written.

Unfortunatly, MS-SQL doesn't allow "selective distinction". Like, SELECT DISTINCT (nid, node) type FROM node;

This can be solved though IF you are running SQL Server 2005.

So a decision needs to be made...support SQL Server 2000+, or 2005+?
With 2005 there is a varchar(max) data type that acts just like mysql's, and will allow quiries like i mentioned above.

So, what does everyone think?

What I mean is that we should use http://api.drupal.org/api/HEAD/function/db_distinct_field in blocks.module or wherever else that has a hardcoded DISTINCT. And then in our mssql db_distinct_field function we can deal with it however we want. Or am I missing something? We should file separate issues for those though.

This is getting there. It needs some code cleanup

- Whitespace removal
- Structure the CREATE TABLES consistently (some are way indented some aren't, I'd use the indents like in locale.install.)
- Some mysql references in includes/database.mssql.inc
- We need to handle errors more gracefully and I'm not sure the best way to do it as I commented out that error code in _db_query

I'm just writing down tasks as I see them.

I think we should just target 2005. We want to make this as clean as possible and we don't need to go that far back.

Killing your strong tag here. :)

Hmmm...not sure how to fix that.

Sorry 'bout that. Hopefully that fixes it. :)

Re-Rolling becuase my other 2 patches were working aginst a fresh HEAD, thus re-rolled, tested, and this works against HEAD.

looks good ... you should not have to regex for LIMIT queries and rewrite them. Those queries all run through db_query_range(). Just rewrite that function to use TOP and then delete the gnarly regex from _db_query().

Thanks moshe. I'll probabley start tackling db_query_range at the begging of next week.

Re-Rolling patch. The blocks table was missing the 'title' field.

@Souvent22 - you mention that a change to blocks.module is needed for this patch and that this is handled in a different issue. Where is that issue? I'd like to move it along.

I'm a bit sad that to drop MSSQL 2000 support. I know it is ancient, but enterprises upgrade their DB platform very very slowly. If possible, could someone elaborate on the problem. Maybe we can patch Drupal to behave nicer.

Moshe,

MS SQL 2005 has new 'Large Data Type' columns. It is best explained by a snippet I pulled from informit.com. However, since most people will not be doing Transactions/Stored Procs/Functions with PHP->MS-SQL and for that matter Drupal->MS-SQL, these columns could be revereted back to TEXT data types instead of varchar(max) data types.

For a full understanding, go to the article ref link and read the first page about the handeling of the new data types. This is just a small snippet. The full article talks about how in SQL Server 7->2000, you had to span large text columns over many columns, and this was not pretty. There are some MSDN articles on this change also.

Article Ref: http://www.informit.com/articles/article.asp?p=327394&seqNum=6&rl=1

Large Value Data Types

In SQL Server 2000 (and 7) the maximum size for VARCHAR and VARBINARY was 8,000 and for NVARCHAR 4,000. If you had data that potentially exceeded that size, you needed to use the TEXT, NTEXT, or IMAGE data types (known as Large Object data types, or LOBs). This was always a hassle because they were hard to work with, in both retrieval and action statements.

This situation changes in SQL Server 2005 with the introduction of the MAX specifier. This specifier allows storage of up to 231 bytes of data, and for Unicode it is 230 bytes. When you use the VARCHAR(MAX) or NVARCHAR(MAX) data type, the data is stored as character strings, whereas for VARBINARY(MAX) it is stored as bytes. These three data types are commonly known as Large Value data types. The following code shows the use of these data types in action....

Note: The bytes for the new varchar(max) etc. columns are 2³¹ bytes and 2³⁰ bytes for unicode, not 230 bytes. I forgot to use the "sup" tag.

Hm. Perhaps I should split the .instlal files, and make a new db-type. mssql, mssql2005 (like mysql, mysqli).

I have currently updated (but haven't rolled out) the db patch so that your mssql version shows up on the admin/status page. I'll roll out a patch after I get some of the MS-SQL server stats to show up. I would like some community input as to what stats people would like to see (if there are any other MS-SQL developers out there). You may have to give read-access to the Drupal account to some tables in the sys schema. But, the stats avail. from MS-SQL server FAR out weight the number of stats for MySQL (mysql ~ 250 stat vars, mssql ~ thousands stat vars). So...what do you guys want to see on the stat page? I can' just dump out a thousand stats on the sql page. :).

Right now, the major ones I think are:

• Threads open
• Current Quries

I don't want to put too much on the page. I want it to be a quick diagnosis tool. Major tuining must be done at the console or using SQL Server tools. This screen should be for looking at symptons of a problem, and not for final diagnosis (I think).

thanks for the clarification.

could we define those fields conditionally depending on the mssql version? i don't see why not, since all this schema creation is done in PHP. so older versions will use type=TEXT. Is there core code that will fail under TEXT?

@moshe

You're right, i think a simple preg_replace would do the trick, and I can check the version. I'll try and get that in this week.
afaik, having text will not break any core modules. This was more a 'big-picture' call, meaning if you're working with Drupal tables on the other side (in the Visual Studio/T-SQL/MS enviroment), that varchar(max) etc. would be preferred. However, this is a mute point because if you have sql-server 7.0 or 2000, then obviously you're not going to be able to use the varchar(max) datatypes.

I need to make myself a "TODO" though to add to the system_update. Perhaps under the admin/sql, there should be a way to update your tables if you upgrade from 7.0/2000 -> 2005, to alter the neccessary tables columns to the better/new datatypes.

I should also start a hand book in the Drupal books section so that people can ont what to look for when/if they are trying to use Drupal/PHP+MS-SQL. There are a few ref. articles (performance, installation, etc.) in which I'd like to keep the links to in a central place.

Update:

• Search module did not work becuase MS-SQL does not have a GREATEST function. This patch creates a UDF (User Defined Function) GREATEST that implements this during the installation of mssql. The code for is can be found in the system.install.
• On the admin screen, one can now see the version of MS-SQL they are running
• Currently varchar(n) and *not* varchar(max) is used, or text. This may change during the final roll out

If you are testing this, until I/we (community) come to a final decision, if you are not using SQL Server 2005, you will have to change varchar(max) to varchar(4000) instead. This is just for now. This will change and the driver will automatically select the correct column type for your db. I have no time to write the reg-ex for this now, if someone wants to roll one, I wouldn't hold them back. :)

Update,

uploads do not seem to work with this mssql patch, and i don't know why. They show up, but they don't get copied to the files directory, nor does the node get upated. very strange. Can anyone verify?

Keeping up with head.

Had to re-roll because patch would not apply due to changes from HEAD.

Tested this patch against a clean install.

Note: This patch will work with SQL Server 2000. text was changed to varchar(4000). I doubt that a block would have enough chracters in it's page 'limiting' area to reach 4000. I could be wrong.

*Important Rollout*

I found out all the patches before this one are kind of useless. The sessions table had the _session_ column set to varchar(1024). Thus, uploads were not working, and i was getting some quirky thinking going on. This fixes that by setting the column to 'text'.

* Uploads fixed with this patch
* Sessions table fixed with this patch

Added 'title' field to blocks table install. Synced against head.

- Split verisoin so the string isn't so long, and added versions back in. (some how dissapeared)

I've attempted to adapt the MySQL LIMIT function to work with SQL Server 2000 with no luck. After several attempts, I could not create a generic enough MSSQL query to accomodate all MySQL queries using LIMIT. My only solution was to post-process the recordset using mssql_array_seek in function _db_query:

$last_result = mssql_query( $query, $active_db);

if (isset($offset) && $offset > 0 && $last_result && $last_result != '1' && mssql_num_rows($last_result) > $offset) {
mssql_data_seek($last_result,$offset);
}

But I hate to hack it up this way... any ideas? SQL Server 2005 offers a solution with a new paging function, but for the rest of us stuck with 2000, it's not so easy. I really urge everyone working on this to make sure it's 2000+ compatible :)

don't bother with regex. you just have to get db_query_range() to work right. thats where the LIMIT stuff is generated. drupal already abstracted this for us.

He's right. I'm all over the place with projects and I had meant to reply earlier. So this is going to be a quick explination;
SQL Server, Oracle, and most other large 'enterprise' DB's implement the SQL:2003 ANSI standard, which does not define a LIMIT type clause.

Instead it defines this:
SELECT
ROW_NUMBER() OVER(ORDER BY nid ASC), nid, title
FROM node

I have already wreslted with this, I just havne't had time to finalize and test it, but this is what I believe needs to happen.

1. All db_range_queries need to have an ORDER BY clause (we'll deal with those that don't later)
2. Take in the query, say: SELECT nid, title, created FROM node ORDER BY created DESC WHERE type = 'story' ($from = 10, $count = 20)
3. Pop off the ORDER BY clause (they can not appear in the ROW_NUMBER clause
4. Reformat the query as follows:

$to = $from + $count
SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER(ORDER BY created DESC) as drupal_pager_number,
    nid, title, created
    FROM {node}
    WHERE type = 'story'
  ) as drupal_range_query
WHERE drupal_pager_number BETWEEN $from AND $count;

(the below comments are NOT optimal, but taking bad SQL statements into account)
5. If no ORDER BY is given then find the first column that has an index
6. if no column has an index, use an arbitrary column

So something to that effect. I've used this ROW_NUMBER query on a sub-query with 102,000+ rows, and rows very fast, just as fast as LIMIT so it seems, Although, I have no numbers to back this up.

But just wanted to get that out there to see what people thought, and how to use some regexs to split the SQL up as needed to re-write it in this format.

When I say he's right, i meant that yes, we should do all this in db_query_range as moshe says.

With SQL 2005, it supports the SQL:2003 ROW_NUMBER(), however 2000 doesn't. I'm thinking that we are going to have to put our heads togather and create a Stored Procedure for SQL 2000 servers, like DRUPAL_PAGER, and have the server handel it server-side in the proc (because you'll prob. need cursors), and then spit back the subset as a result-set.

sounds good, earnest ... note that wikipedia describes an alternate syntax for LIMIT is MSSQL. See http://en.wikipedia.org/wiki/Comparison_of_SQL_syntax#Limiting_results_o....

i'm ok with a sproc for this in sql2000 (or sql2005, for that matter)

Moshe, that is the alternate syntax I used, which ended up not working for certain queries - it had something to do with ordering by the same column multiple times, but I can't recall the exact error. Like I said, I ended up pulling the entire recordset and seeking to the offset with PHP. I considered the sproc, but that too seems like a hack, albiet a better one than mine. This is really the biggest hurdle to jump for getting Drupal to work with MSSQL 2000. Once this is overcome, everything else *should* go more smoothly....

I dont' think a sproc is a hack of a solution to this problem. If anything, it's a better solution because the SQL engine will have pre-compiled and pre-examined the SQL statement for the best execution process.

I think the main thing is getting the bits of information that we need split up in the SQL clause. Meaning, the main parts to any solution are (and I may be wrong, I'm throwing this out there):

1. The SELECT clause and what's being selected
2. The ORDER BY clause so we know how to order results
3. The WERE clause so we know how to filter them.

You mentioned that you had to pull the entire recordset, and use a cursor (which cursors are expensive as we all know). I think the better solution is rewriting the SQL statement into one of the fomrats that has been discussed here, and letting the SQL engine filter the results for you. It's much faster and less expensive.

So with that said, it comes down to 2 options (even if we go with a sproc or not):
1. Rewrite the SQL statement
2. Use a cursor (which we could do client side, but i wouldn't want to)

I agree that the sproc is the best solution thus far. My thoughts:

Create a temp table and select use a cursor to grab the desired rows specified in LIMIT. This would work, but we'd need to determine the columns and column types from the original query to populate the temp table with.

The parameters of that I can think of would be:

1. @query = the query without the LIMIT clause at the end
2. @columns = columns being selected (comma delimited)
3. @limit = the limit¹
4. @offset = the offset^1,2

¹optional
²if offset is not specified, then the issue is becomes simply SELECT TOP @limit FROM (@query) [AS] derived_table and I think we could avoid creating the temp table all together

For example:

SELECT w.wid x, u.name, u.uid FROM watchdog w INNER JOIN users u ON w.uid = u.uid ORDER BY x DESC LIMIT 5, 50

Would become:

@query = "SELECT w.wid x, u.name, u.uid FROM watchdog w INNER JOIN users u ON w.uid = u.uid ORDER BY x DESC"
@columns = "w.wid, u.name, u.uid"
@limit = "50"
@offset = "5"

Then, the maximum number of rows we'd need to pull would be the offset + limit - 55. So,

1. Adjust @query to SELECT TOP (offset + limit)
2. Create the temp table with the appropriate cols - i'll need help doing this. We'd need to include an id column (id_col). I think there are some stored procedures in the master db that can help with this.
3. Insert all the records from the query into the temp table.
4. SELECT * FROM #temp1 WHERE id_col > OFFSET

I'm sure I'm missing some important steps, but this is one way I would go about it. Once we decide on the right way, I'd be interested in contributing.

What do you all think?

Re-rolling against RC1

Re-rolling.

I am posing another patch for watchdog (see the groups.drupal.org/enterprise ). Other than that, as long as your SQL is in good format (e.g. no over-lapping columns. Bad: "SELECT n.*, u.* FROM node n INNER JOIN users u ON n.nid = u.uid" (you're already getting the uid from node, why are you getting it twice if say you want the users name). Good: "SELECT n.*, u.name FROM .....".

Note: This is currently only compatible with SQL Server 2005. If someone wants to take a look at how I implemented it, and suggest a SQL Server 2000 implementation, or create a SP from SQL Server 2000, it would go along way to moving this forward.

Updates

• Fixed db_query_range function
• Began implementing better error handeling from SQL Server (sometimes you get a "Statement terminated" when the real error is a FK constraint failure.
• Minor fixes here and there. Too many to list.

If your SQL is good, I consider this patch alpha...perhaps even beta stable. 3rd party verification is needed.

- Souvent22

SQL Clean up reference. This SQL statement in watchdog currently breaks in the SQL Server Drupal driver's db_query_range function: http://drupal.org/node/105540 . This will prob. be fixed soon. Until then, apply this referenced patch, and all is well.

Has this patch been tested with Drupal 5.0?

+1

Subscribing

Tested against 5.2.

REMEMBER: You will need the updated ntwdblib.dll (I'm currently running verion 2000.80.194.0). The one that comes in your PHP install/directly is WAY outdated and will not work. Search the web to download the dll. I'll prob. post it on my blog sometime so it's readily available. You'll also need to obviously enable mssql support.

Subscribing :-)

subscribing too... :)

another subscriber

So I need to know what the interest level in this patch is and if i should put an effort to get this included into 6.0 ? What does everyone think?

i am very interested. whomever maintains this DB will immediately become the MS Drupal guru which will likely be a prestigous and lucrative position if thats desired. My .02.

@Souvent22: YES, put into 6.0, please :-)

Merged a few items from the clients patch into this patch. Also cleaned up a lot of my comments and took out misc. functions. Fixed temporary tables. Also added a fix for these PHP "bugs":

http://bugs.php.net/bug.php?id=26996
http://bugs.php.net/bug.php?id=25777

I tested this about 4 times and it installed fine. So i think this is ready for some review now.

- Earnest

excellent, earnest. that bug #26996 was what finally drove me away from maintaining mssql in the first place. the last straw, if you will.

i checked you code and i saw varchar(max). This is incompatible with MSSQL 2000!? On the other side you are not using the new MsSQL 2005 range function or i overseen it... this should be a very big performance hit but incompatible with mssql2k.

Will this core extension support mssql2000 or only mssql2005? should the database.mssql*.inc not named like the sql version? this looks to me - regarding the range function - more advantages then disadvantages, while it make clear what version is required and there need no conditions inside - if version X then X1 elseif version Z then Z1... not sure what is the prefered way. Doing a SELECT @@version or xp_msver 'ProductVersion' seems required to distinguish between the versions... but i'm not sure how performant it is to select the version for every (range) request...

However using MsSQL 2005 and not using the range functions sounds not good to me...

Hass,

Regarding the range function, are you speaking of table partitioning? 'cause that's a whole other conversation.

Regarding VARCHAR(MAX), yes, i know it's not sql 2000 compatible. In the thread I had made a call out about support 2000 vs 2005 (e.g. varchar(max) vs TEXT).
It is *much* easier to do 2005 with varchar(max) than the TEXT column. however, I do plan on going back and making a mssql7 version after this one is stabilized. hm, i suppose i could change this to mssql9 to differentiate, but i wanted to call this one mssql because 2005 is, IMHO, Microsoft's first "real definitive DB release". it supports most of the industry standard SQL practices without most of the quircks that were found in 2000. not that 2000 is a bad product, but i believe this sql code will be usable for at least 2 more SQL Server releases.
I could do a SELECT @@version. Doing the xp_* functions isn't usually best practice as those are usually secured away and not avail to "regular" SQL Server users/logins. However, all in all there are only a few "quirks" (all beit some what major), but 90% of the code of database.mssql.inc will be usable on the 2000 port, so I think a simple switch will be fine and we'll be able to just have one database.mssql.inc. Really, the problem with the 2000 port is the TEXT field AFIK. That's what really sets the 2 things apart.....regarding Drupal support anyway. There are many instances where people have had to resort to concatenanted varchar(2000), or varchar(x) fields because TEXT is just a b@#!$ to work with. But right now, i vote get the 2005 support. I know it's a 50/50 thing out there as a lot of companies use 2000 still; although many i talk to are "staging" there 2005 rollout because 2005 adds so many "must have" features. but if I can get 2005 stable and in, i'll start porting back to 2000 immediatley. we have 2.5-3 months before the code freeze. i'm thinking perhaps this will get in in a month, so that leaves me 2 months to port 2000. i think that's a good timeline.
Thoughts?

Oh, one more big note about sql 2000 support.....db_query_range. 2005 has the ROW_NUMBER function, which is not avail in 2000. This will have to be some type of temp table function, but that's kind of slow. I do have a solution though started in the patch though (it's the reason it's in there right now). There's a function called "find key" or something like that. The thought process goes like this:

- One way to do paging is if you know a one-dimensional cardinality about the table (e.g. primary key, or unique key). You can use this to do a "double-mirror" technique using TOP to page the information (see below for short explanation of technique). However, this information (key) is not passed to us by the user (unless we get something in the ORDER BY clause, but this is not guranteed). So, the function looks up the table information of one of the tables and looks for the primary key of the table, and then we can use that to page the table.

*Double-mirror
Let S = start row
Let E = end row
Let T = total tuple set (num rows)
Flip sql (order backwards) and grab the top (T - E + (S-E)) rows, order set forwards
From that tuple set (sub-query), grap top (S-E) rows

*update i shouldn't say one-dimensional key, but you need to be working/ordering on a key, else performance goes to hell.

*update i shouldn't say one-dimensional key, but you need to be working/ordering on a key/index, else performance goes to hell.

I'm still interested in this as well. Souvent, would you mind listing the features of your patch that you know to be INcompatible with SQL Server 2000 (e.g., VARCHAR(MAX), ROW_COUNT, etc.). That way some of us can get Drupal running on 2000 and then work to help you with some of the major incompatibilities (e.g, Paging).

Thanks for everything you've done so far... I think that an addition like this to the core would be real helpful for those using SQL Server as well as the Drupal project itself (working into the 'mainstream').

*Update: ROW_NUMBER I mean, not ROW_COUNT

SELECT SERVERPROPERTY('productversion') looks better for the version check...

regarding "range" i'd like to correct me. i mean ROW_NUMBER.

no matter - if only mssql2005 is currently supported, we have bought a sql 2005 - 2 weeks ago :-) and for me i don't need 2000 support anymore, but i'm sure - not every company like to upgrade for drupal only. cpu licenses have high investment costs... However, having MSSQL on the new features list for the next release will be something that will push drupal spreading... :-).

Just wanted to chime in and show my support for this work. Would be great to have MSSQL support in Drupal 6. I don't have a MSSQL server to test with, so it will be up to you guys to mark this RTBC. I'll do a code review though, so make sure it's clean and elegant. ;-)

Hass, Yes, I do use ROW_NUMBER. If you check the patch, it's used during db_query_range.

I'll post a listing of "incompatible sql2000" features just as an FYI. Perhaps only in the code, and snippet in the Handbook just as a "TODO" for a sql2000 backport.

Once 6 gets back to being usable (my last check-out a few days ago left it some what un-usable, so i'm waiting), i'll tidy up the admin screen for sql server to show some db stats, and then post back for review.

Speaking of stats, anyone/everyone want to chime in on what they'd like to see? Right now i'm thinking:
- num procs
- num current threads
- num quries
- db size
- log size
- last backup
- backup method (FULL, SIMPLE, INCREMENTAL)
- errors
- memory usage
- Service pack level of OS
- Service pack level of SQL Server

I also think I'm going to call this mssql9 instead of mssql. just cause it's easier to track version numbers (sql2000 = 7.0, 2005 = 9.0, etc.) that the "Year Versioning" that we are accustomed to, which is really for marketing; unless there is some objection.

i think support for mssql in general and specific for 2000 = 8.0 will possibly not implemented by other module developers. If they use simple and not so highly optimized query's it's ok to have only one mssql code for all versions (today we know). if we worry all module developers about all DB versions we won't get this inside the modules i think... same discussion should come up for mysql 4 and 5... and it looks like this has been solved with one file / logic.

Aside, why are you not using with (nolock) (read-uncommitted)? This is a VERY big performance hit... and it prevents deadlocks on high stress sites... we got much of them in past and changed all SQL code for this. Now the site works very well under high load and every recurring developer mistake tells us again - this is a simple must have. So i can say for sure - every SELECT should have with (nolock) added for best performance and solving possibly deadlock reasons.

Abstract/Background:
For those who are totally un familiar with SQL Server. WITH (NOLOCK) is something you can add to a query (SELECT, UPDATES) so that the server ignores any and all TABLE locks and wider (page-locks, extent locks, and lower are respected). So if you have a transaction that has a table locked or some query that's updating a table and it's going to take 1 min. to do so, if you hit the table with the WITH (NOLOCK) modifier, you will get results instantly w/o having to wait for the table to be unloced. e.g. SELECT nid, title FROM node WITH(NOLOCK) WHERE nid > 10;

In our production site, we currently use WITH (NOLOCK). And you are correct, it's a GREAT feature. However, it is also what I consider and "application feature". Meaning, it is not really general enough in that you do not have to know about the application to use it.

To show why this is important, lets take and example, but you must remember this:

All SQL statements are run through the _db_query function.

So, lets take an example, take db_next_val. If use WITH (NOLOCK), you will most certainly get a collision of ID's because the SQL statements ignore table locks and see that (for example) 32 is the higest value, thus they will both return 33.
The site we run runs many many quires a second (on the order of 800 spking to 1500 a second). On the site we wanted the speed of WITH (NOLOCK). So we implemented a random ID number generation technique and dropping the sequential table creation (I can post on our method if anyone is interested).

It should be noted that we did run into DEADLOCK conditions under very high load (only during heavy content creation). So switched to random-number-generation and all was good.

I had tried to add WITH (NOLOCK) also to the _db_query_range function. However, that introduced problems but I can't remember what they were. Pehraps it was that WITH(NOLOCK) can't go in a sub-query, but i don't think that's it. I'd have to go back through my docs to find the specific reason why. But, perhaps we could setup _db_query to take an extra var that sets the lock condition, like _db_query($sql, $debug = FALSE, $lock_condition = MSSQL_WITH_NO_LOCK), and db_next_val will call _db_query($sql, FALSE, MSSQL_TRANS_DEFAULT), meaning it will honor the transactions set lock condition (meaning just run normally as to how the server and/or environment is setup) by not using any LOCK modifiers.

So, I agree, WITH (NOLOCK) is one of the features that leaves MySQL behind; talked and demonstrated it at the last Drupal meet up. But, it's the Drupal arch. that makes it somewhat of a problem because WITH(NOLOCK) is something to use when you know your application. IMHO. Thoughts?

i wonder how you are able to use with(nolock) in an UPDATE... i think this is not possible.

Yes, the sequences table is a possible problem. it should be solved in a different way. maybe a regex that does not insert with(nolock) inside if table is sequences or if function is db_next_id... or something similar. ok, if there are auto_increment fields we don't have to think about this problem :-). the other way should be a positive table list...

Regarding lock conditions i'm sorry - you are wrong. MySQL and some other DB's have this feature, too. I know for sure this is possible in ColdFusion with the DataDirect JDBC Drivers... it's a config setting only and MySQL have the same read-uncommitted performance boost. So this feature should be named more general for e.g. read-uncommitted or something shortened telling what is really the technical background...

however - in general - all should be read-uncommitted aside of minor pieces requires a table lock and we should ask why and if this is really required or cannot be changed to boost drupal...

OT: if drupal.org will run read-uncommitted with their mysql server THIS site should be really faster, too... and drupal.org get's everyday slower and slower... adding a comment here is no fun.

OT: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html

Hass,

Yes, this is true for InnoDB which implements most of the SQL-92 transaction level standards, however the majority of people run MyISAM, and most of the high-load sites run a combination of MyISAM + InnoDB tables. MS-SQL only has one storage engine type, so this is not as much of a problem. Again, it's a lock feature for when you know your application (thus you'd have to alter certain tables to InnoDB to get the diff. isolation levels that InnoDB gives).

also, yes, nolock is for select statements only, gets dropped/ignored when using other statements.

postgres and most all other db's support locking hints in some form/fashion. changing this behavior should be talked about in another thread though; it's beyond ths scope of this patch (meaning it's a change in the methodology of the db_layer. this patch is to add an addtl layer using the current methodology). I'll keep this in mind when rolling out my next patch though.

I also know that one of my modules (timesheet.module) requires/uses InnoDB tables, and thus it's select statements need to 1. be in a transaction, and 2. would NOT want to use the read-uncommitted mode during some sql statements. Changing this would break this. I don't know of any other modules in which this would be a problem though.

As i know - Drupal 5 requires InnoDB or creates InnoDB Tables only, isn't it? So you are correct for past... but not today.

drupal 5 uses InnoDB, but doesn't require it afaik.

again though, that's a seperate issue in my mind though (the locking issue).

For both 2000 and 2005, has anyone run into this issue? In short, it's a bug that prevents one from pulling more than 255 characters from SQL Server into an object/array using php_mssql.dll/IIS/PHP. I'm about to see if this is an issue with Drupal (I've noticed node_revision.teaser is stored in varchar(4000), so technically it should be cut off to 255 characters if queried). Does anyone have experience with this bug and how to get around it?

I believe the issue with that is the the ntwlib that they are using. I bet (although it did not specify in the bug report) they are using the default one that comes from the PECL extentions. This one is very outdated. If you use the updated one you should have no problem. I currently am having no issues. I can give you a link to the updated ntwlib if need be. I'm going to post it on my blog when I get a chance for easy access, as anyone connecting to a sql2005 db will have to use the updated ntwlib. Perhaps with Zend doing Zend-Core with MS, they'll release an updated version...but i doubt it as i believe they are going ODBC with the Zend-Core for IIS.

Although, something did just dawn on me. I am currently using varchar, where I believe i should be using nvarchar. nvarchar allows for the storing of uni-code data; but it takes twice as much space as varchar. I'm going to do some investigating on the issue, and see what the best course is and how much it matters.

*Update - I've confirmed that this IS an issue, at least with SQL 2000. I created a page that had over 255 characters of body content, and the preview showed only 254 characters rather than up to the 4000 the database can store for a node preview. This is not an issue with text-type columns, only varchar as far as I can tell, so this may not pose a huge problem for the rest of the system. Again, this is using the php_mssql.dll with an IIS install of PHP.

Souvent22, I'm using version 2000.80.194.0, which I believe is the latest. I had also tried nvarchar, but that didn't seem to make any difference.

pcorbett,

Hm. Ok, i'll take a look into this issue. Very strange. Yes, that is the most up-to-date version of ntwlib.

updating status to need work

- Earnest

A while back I solved the Limit problem using this method and it should work on 2000+

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&...

Here is the example they use.

select * from (
 select top 10 emp_id,lname,fname from (
    select top 30 emp_id,lname,fname
    from employee
   order by lname asc
 ) as newtbl order by lname desc
) as newtbl2 order by lname asc

Well that’s my 2 cents.

@Souvent22
Sorry about that I think I opened the follow up form before you posted then posted after you re-updating the issue.

Hm, ok, I think I have a solution. I don't totally like it though because I do not know how it will effect performance.

With that said, a work around is:

SELECT CONVERT(MyVarCharField as TEXT) MyVarCharField FROM [Table]

Of course, we will have to keep information about which columns in the tables are varchar fields (this is easy to aquire from the system/model table and could be cached unless a schema change is detected).

Some fields are currently TEXT (body of node_revisions, session table data field) and will not be cast.

Off the top of my head, I don't think this would be a large noticeable performance hit because (this is pure speculation) upon compiling the SQL statement, the output would just be mapped different, and then an error is thus thrown if the data type does not match. e.g. If I don't cast the mapping is varchar => varchar, and the varchar output handler handles/checks the output. varchar => text, the text handler handles the output. Again, this is just a random thought w/o anything to back it up. I'll work on getting some research/numbers to support or disporve this.

But for now, that's the only option I see. Thoughts?

It also should be noted that on the linux platform using the FreeTDS library 8.0+, it seems that this VARCHAR problem is not an issue.

Many threads have verified this, but can anyone here on the Drupal thread verify?

I think you're right, this is only a Windows/IIS issue with the php_mssql.dll driver. The FreeTDS driver is supposed to be much better, but as far as I know, it is only working with PHP v5.1.6 so far...

Not sure if there was a reason for this, but the menu table creation script seems to be off. When I go to access the menu settings, I receive the following error:

user warning: Invalid column name 'description'. query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (482, 470, 'admin/content/types', 'Content types', 'Manage posts by content type, including default status, front page promotion, etc.', 0, 22) in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 193.

This is the SQL in system.install for MSSQL:

     db_query("CREATE TABLE {menu} (
        mid int NOT NULL default '0',
        pid int NOT NULL default '0',
        path varchar(255) NOT NULL default '',
        load_functions varchar(255) NOT NULL default '',
        to_arg_functions varchar(255) NOT NULL default '',
        access_callback varchar(255) NOT NULL default '',
        access_arguments varchar(2000),
        page_callback varchar(255) NOT NULL default '',
        page_arguments varchar(2000),
        fit int NOT NULL default '0',
        number_parts int NOT NULL default '0',
        vancode varchar(255) NOT NULL default '',
        visible int NOT NULL default '0',
        parents varchar(255) NOT NULL default '',
        depth int NOT NULL default '0',
        has_children int NOT NULL default '0',
        tab int NOT NULL default 0,
        title varchar(255) NOT NULL default '',
        parent varchar(255) NOT NULL default '',
        type int NOT NULL default 0,
       PRIMARY KEY (mid)
     );");

And this is the SQL for MySQL:

 db_query("CREATE TABLE {menu} (
        mid int unsigned NOT NULL default '0',
        pid int unsigned NOT NULL default '0',
        path varchar(255) NOT NULL default '',
        title varchar(255) NOT NULL default '',
        description varchar(255) NOT NULL default '',
        weight tinyint NOT NULL default '0',
        type int unsigned NOT NULL default '0',
        PRIMARY KEY (mid)
      ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ");

Looks like the description and weight columns are gone - is there a reason for this?

To make the menu settings page work for me, I needed to allow nulls for menu.page_arguments and menu.access_arguments (not sure why they didn't default to allow nulls upon install). Also, I needed to add a weight column of smallint type (to allow for negative numbers).

the schema may have changed latley. i haven't checked. i'll try and re-roll a patch tommorrow. thanks for bringing this up.

The discrepancy may be due to the fact that I am referencing the mysql install script for Drupal 5 and not 6. My goal is to use this before 6 is released. Do you think that it would be difficult to get this working for Drupal 5?

Re-sync with 6.0.

- updated local table (more languages table)

I did not have time to go through all 80 tables and dare I say how many fields to make sure that there were no other major schema changes. This patch is also untested. i'll test it when I get a chance, or if someone wants to take it on, please feel free to post any problems you hvae with it.

As far as 5.0, I do have a 5.0 patch ready to go and stable that I can release. I remember someone asked about a 5.0 patch. Let me know if you're interested in it. The DB-Engine will remain un-changed, it's only the schema's that are really different from 5.0 to 6.

I'm interested in your 5 patch, is it the one that you posted a bit earlier this year? Feel free to post your latest one. I'm not running a copy of 6 right now, but when I am I'd be happy to help test for you.

I'm currently trying to install the latest drupal release version with the above latest patch. I'm running the following:
- Win2k3 SP2 IIS6
- SQL Server Express 2005
- PHP 5.2.1 with ntwdblib.dll latest version 2000.80.194.0

I've created a database call odex, with a user called odex_db who is given the db_owner

When I first got to install.php, I had the page and keyed in the details. There after, the page went to install.php?profile=default. However, the page is totally empty. No database was created, no error message at all.

When I tried to redo it again, it seems that some process is locking up includes\bootstrap.inc and install.php is locked by some process. It seems to be stuck there. After I restarted the IIS process, the directory and files mentioned could then be deleted.

Anyone can provide help?

Further to my details above, I noticed the second time round that the patching was slightly off as seen below. Is there anything that I should be worried about?

patching file modules/locale/locale.install
Hunk #1 succeeded at 73 with fuzz 2 (offset -8 lines).
patching file includes/install.inc
Hunk #3 succeeded at 337 (offset 6 lines).
patching file modules/search/search.install
patching file modules/profile/profile.install
patching file modules/book/book.install
patching file modules/poll/poll.install
patching file modules/contact/contact.install
patching file modules/aggregator/aggregator.install
patching file modules/drupal/drupal.install
patching file modules/system/system.install
Hunk #1 succeeded at 1068 (offset -42 lines).
patching file modules/statistics/statistics.install
patching file modules/forum/forum.install
patching file includes/install.mssql.inc
patching file includes/database.mssql.inc

Watcherq,

Looks like I need to to a patch re-roll. I'll do one tonight. I feel 6 has stabilized enough that I can dive into it. Look for a patch late tonight. Hmm...I'm planning on going to happy hour, so make that late late late tonight...make that tommorrow. :). Def. tomorrow, then I can deal with any issues come the start of next week.

Hi Souvent,
Thanks for your help. Note that it hoisted the inetinfo.exe such that I had to use taskmanager to kill it; using the restart option didn't seem to respond...

I bet I can 100% say what the problem probably was. There is a "race" condition during the install process that I've dealt with from day one. Updates to HEAD could have changed my "check" for this condition.

What happens is the install phase is using a "side-effect" during the install process, it makes a sql to select something from the system table. If this is a fresh install, this throws an error; and the race condition is as follows:
1. Select from system table
2. db_layer throws error because system table doesn't exists
3. db_layer calls watchdog
4. db_layer throws error because the table watchdog doesn't exist
5. go to step 3 and repeat till you smell funny things start to burn...and this time it isn't dinner.

I have an if clause that checks to see if the "arg" function exists as a flag to say that we are "NOT" in the install phase. I'll look into this issue more though. Thanks for the addtl info though.

I'll also post the 5.0 patch on my blog, and in another thread. I don't wanna mix the 2 patches in this thread.

Hi Souvent,
Thanks for your help. What you described seems to be a deadlock... No wonder the process locks up.
I hope you can help me out by beginner of week as I have a deadline to show it to my client :S Thanks.

Submitted 5.1 patch thread. This is just a copy of a previous patch. If you're testing against Drupal 5, please use that thread to post issues regarding *schema* issues (table not found ,etc.). All other issues should stay on this thread. The core of the driver will be merged into the 5.0 patch; the other thread is helpful to keep the schema editions seperate (HEAD vs 5.x)

http://drupal.org/node/132565

tested this patch against current head and it installed ok for me.

March 31st head snap-shot.

Just a note, I have my own internal road map:

1. Stabalize Install
- Get the install to install with no bugs, and handeling errors correctly (e.g. connection doesn't exist, user doens't have permissions to create tables, etc.).
2. stabalize working of "default" modules
3. Enable another module
4. stabalize working of that module
5. go back to step 3 until all "core" modules work accordingly.

So right now, just need people to just "install" to make sure the install is stable. i'll be re-rolling a 6.0 patch soon.

will this patch integrated into D6?

Now after Schema API 1 has been commited http://drupal.org/node/144765 - are you planing to update the patch to get this into D6???

Subscribing - I did MCDBA for SQL Server 2000 and I'm getting rusty - I'll help test the current install patch on both 2000 and 2005 and I also want to work on the MSSQL driver for the Schema API.

subscribe :)

i face a critical problem when review and implement MSSQL driver: we can use IDENTITY(1, 1) for auto increment handling, with last insert id supporting; BTW, we are not able to insert value into IDENTITY field manually, unless we call SET IDENTITY_INSERT table1 ON and SET IDENTITY_INSERT table1 OFF explicitly.

this will cause a great problem with system.install: we are trying to insert some value as predefined information, e.g. uid = 0. we never know when will we need to due with such manually insert action, so it is a bit complicated to implement such unlock/lock action.

currently i have 3 ideas:

1. keep on using IDENTITY, to keep most features. on the other hand, use preg_match to search out "ALL" INSERT statement, and unlock/lock explicitly for "ALL" cases
2. implement sequence by using procedure and "ONE" sequences table, plus helps of trigger, as manual auto-increment implementation. this is a bit similar as case of oracle. BTW, it is too complicated since MSSQL don't support sequence natively, we can't fetch a correct "last insert id" of current session, under this implementation
3. implement sequence by using procedure and "MULTIPLE" sequence tables (each table comes with an IDENTITY field), plus helps of trigger. in this case we will INSERT a dummy record into those sequence tables for fetching the last insert id (let it work as a sequence generator...). anyway, it is even much complicated when compare with above solution :(

besides these solutions, we may also solve the problem by a very simple way: don't manually insert value into "serial" field, and always use its auto generate feature, even during installation. e.g. change the following:

<?php
  db_query("INSERT INTO {users} (name,mail) VALUES('','')");
?>

into:

<?php
  db_query("INSERT INTO {users} (uid,name,mail) VALUES(0,'','')");
  db_query("UPDATE {user} SET uid = 0 WHERE uid = 1");
?>

anyway, this also means we will "give up" the first id (== 1) :(

P.S. here is my latest implement (include http://drupal.org/node/147947 and http://drupal.org/node/165957, patch based on latest CVS HEAD). hope someone can help about this topic :)

You should never insert something in an identity field... however oracle allows this. i don't know why you are trying to do this.

this is not only the case for oracle, but also for mysql, pgsql and db2... they all allow this, but only mssql not...

P.S. a better idea after brain storming: why don't we combine the unlock/lock IDENTITY feature together with db_lock_table() and db_unlock_tables()? some benefits:

1. we will have tablename information by calling db_lock_table(), so we can call SET INSERT_IDENTITY table1 ON successfully
2. there is no contradiction when combine this handling with db_lock_table(): we hope to insert some critical information into the table, and so we lock it :)
3. on the other hand, db_lock_table() is a standard function among abstract DB API, so we can call it without using database specific query nor function

any suggestion?

As know there is a way to insert a number inside a auto increment field in mssql, but it requires some dirty sql requests and this should only be done by hand for fixing wrong things... However this is possible, it should never be done while it may destroy your db table references... it doesn't makes sense to insert something into an auto incrementing field that should count up automatically, isn't it? :-)

Not everything possible should be done IRL.

@hass: you seems discussing the problem of this issue: http://drupal.org/node/167801, which should solve in there :)

BTW, i hope to discuss about the possibility of overcoming this difficulty within MSSQL, says internally. currently, i try to implement db_lock_table() and db_lock_tables() as below, and it is able to help about solving the problem:

<?php
/**
 * Lock a table.
 * This function automatically starts a transaction.
 */
function db_lock_table($table) {
  global $last_table;
  db_query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; SELECT * FROM {' . db_escape_table($table) . '} WITH (HOLDLOCK)');
  if (isset($last_table) && $last_table) {
    db_query('SET IDENTITY_INSERT {' . db_escape_table($last_table) . '} OFF');
    $last_table = NULL;
  }
  db_query('SET IDENTITY_INSERT {' . db_escape_table($table) . '} ON');
  $last_table = $table;
}

/**
 * Unlock all locked tables.
 * This function automatically commits a transaction.
 */
function db_unlock_tables() {
  global $last_table;
  if (isset($last_table) && $last_table) {
    db_query('SET IDENTITY_INSERT {' . db_escape_table($last_table) . '} OFF');
    $last_table = NULL;
  }
  db_query('COMMIT TRANSACTION');
}
?>

therefore within system.install, i change into this way, and so the insertion become successful without any error:

<?php
  db_lock_table('users');
  db_query("INSERT INTO {users} (uid,name,mail) VALUES(0,'','')");
  db_unlock_tables();
?>

we don't like to handle by this method, isn't it? anyway, before we have a better solution, we shouldn't stop exploring possibility, right?

latest version. at least complete all core installation, but fail after first visit top page: still success after "install complete" message, visit top page (first time), and then error (after refresh). this is usually due to cache handling. i will discover and solve the problem later :)

latest version, include all pre-required patches. just apply it to CVS HEAD directly :)

it is now able to complete all core modules installation, face the top page (also means BLOB/CLOB handle successfully), and login/logout as site admin. some path may have permission error (e.g. admin/build/modules), but this will require for an indeed data comparison between MySQL and MSSQL version :(

anyway, this version will be a good starting point for further more debug and development :)

Attempted to apply the patch from Aug 18th and failed. I'll take a look at why tommorrow sometime though. It's probabley just head has gone through some changes since then or something :).

This seems more like a feature request. I think we should not add this in code freeze. Moving this to D7 and marking it normal.

Thank God: http://www.iis.net/php
And here's the link to the new driver (not yet available): http://www.microsoft.com/sql/technologies/php/default.mspx

Now there's native support for PHP that will save us from having to deal with freeTDS and other things, for example, the current limitation between PHP and SQL Server where it limits the length of text pulled from a varchar to 255. I currently have to change any varchar(255) and over to TEXT to avoid this issue.

Has anyone dealt with FreeTDS??? Yeah then you know what a big deal this is :)

What's going on in here anyway? I'm focused on getting SQL Server 2000 to work with D 5.2, but a lot of my work can be ported to 2005/2008 easily.

Subscribing

Many questions from a user who is not all that technical:

1. Is this patch ready to go on a production server with Drupal 5.x?
2. If I wait for Drupal 6 or 7, can I move from MySQL to MSSQL without a lot of problems?
3. How does this patch affect, if at all, other programs using MSSQL?
I have a proprietary package running on IIS/MSSQL and my main site running on Apache/MySQL. I was hoping to consolidate onto one box so I don't have both Apache/MySql and IIS/MSSQL systems, but my gut tells me to leave well enough alone for now. Thoughts?

@natcolley: If you are talking about #118:

1. We have no stable MSSQL patch for D5.
2. D6 is almost release, and it will not coming with MSSQL support; I am working for that in D7 but I can promise nothing ;-(
3. Ask this question in case of MySQL ;-p

Multiple database supporting is not a easy job which can handle within a single-simple patch. According to my latest research progress result, there is a lot of changes in order to have a fully-cross database core (http://drupal.org/node/172541, already support PDO + MySQL/PostgreSQL, and also Oracle oci8; DB2 is in progress and almost complete), especially when we have a huge-pluggable core now a day.

On the other hand, as there is a lot of limitation in MSSQL + PHP now a day, that is not easy to start development (http://groups.drupal.org/node/7787). Without a stable toolkit, all developments are just a joke... I will come back to this topic whenever it is stable enough ;-)

Finally, I would like to convene people to have a look in my personal research project Siren. With a good and solid common database API within core, we can speed up all databases development easily. I am going to provide unofficial multiple database support for Siren 1.0 (which will stay besides Drupal 6.0 life cycle), and also contribute its progress result for D7 whenever it is open for public development. Most issues are ready for D7. I would like to invite people who interested in this topic, to give a hand for these issues review and comment it ;-)

Dear hswong31 - thank you for your reply. I think I'll stay put for the time being but keep an eye out for your progress. Good luck!

Are there any updates regarding Ms SQL Server 2005 support for Drupal 5.x or 6.x including the patches that have been made available?. Have these patches been stabilized? or Are they ready to use for production environments?

Thanks in advance for your time.

I'm trying to install drupal 6.3 on SQL Server 2005 using patch drupal-6.x-dev-mssql-0.3.patch given by hswong3i. Using this patch empty tables are created.
Please someone help regarding this.
thanks in advance

@hswong3i: Now - after PDO stuff has been committed #225450: Database Layer: The Next Generation... are you going to refresh the MsSQL 2005/2008 driver? Would be happy to test :-). I think time is running for us and we can ignore MsSQL 2000 now...

Just a note that as the new DB API supports contrib-based drivers (not module-based, but they can be dropped in without modifying core) I do not believe an MS SQL driver belongs in core. It's too niche, and quite frankly I don't feel we should be supporting a closed source database in core for a Free Software project.

An MS SQL driver should be possible to implement in contrib, however, if MS provides decent PDO support.

http://us2.php.net/manual/en/ref.pdo-dblib.php

Agreed with Crell. Given what happens with PostgreSQL (people whine if it breaks but there is particularly little help) I do not think there is an overwhelming need in core for MS SQL.

I think other open source projects shown that MsSQL, Oracle and DB2 support is very important. More companies will give it a try if their DB is also supported and this could help Drupal to spread and maybe give some more sponsors. Many many people waited *years* to be able to add their DB driver to Drupal without patching core what suxxx much! All the tries to add support are died while people are unwilling to patch core. Additional high grade enterprise database system support would be good for Drupal, too. Today this enterprise users simply move away as their DB is unsupported... not everyone likes to install mySQL - or can install.

We shouldn't close this case as many patches already exist and they shouldn't get lost as they are reusable for PDO conversion. So leave the case open for someone willingly to take to job and maybe we move this case to a new db specific project...

Let's postpone it until there's a working driver in contrib then.

I am very interested in this. The US Senate's servers run MS SQL. Dodd's site was built in Drupal, although it required a lot of custom work. I'd love to see more Senators running Drupal (especially given that I've already work on one Senate site in another commercial CMS).

Just adding to the noise... We've seen this hate throughout the issue queue towards specific bug fixes for Internet Explorer. I think the hate for proprietary DB's is misplaced. That's like saying Firefox shouldn't run on Windows or MacOS because they are proprietary. Quite the contrary, it's one of its biggest strengths.

If there's an itch, someone will scratch it (with a patch). That being said, where's the patch? :-p

I wish I had the skill to provide it. :-( As for Internet Explorer, that hatred is thoroughly justified, though it should be directed at the developers who wrote that browser, not anyone or any patches here.

For a quick "fix" in D5, I've posted the MSSQL-compatible code in my sandbox. See http://groups.drupal.org/node/15557. This is by no means a proper fix, but it works for me. Hoping to get things into D7 soon.

Adding issue tags. Note: for this to be a reality, there needs to be a strong MS SQL maintainer or maintainers to keep support up to date, including tests. Feel free to rally around this issue, and use the "ms sql" issue tag to find each other.

What's more important is for there to be a PDO-MSSQL driver for PHP, which right now is only barely functional from what I understand. I don't know that this is even possible until that happens. If you know C, I'm sure the PHP-Internals folks would love to hear from you. :-)

(ODBC is another option, but that PDO driver is kinda iffy too, IIRC.)

I do not agree with your opinion. MS SQL 2005/8 is not a niche product.
With MS Server 2008 having implemented native PHP support without any third party tools, this envireonment ist getting more and more intresting.
Now I can have dotNet and php running on the same machine and on the same database (mysql, mssql, oracle, db2 or whatever ).
The basic MSSQL Database is for free and fast enough for most projects and its much more secure and stable than any free version of mySQL.
I will try to integrate myself now in the testing and development of the DRUPAL 6.x MSSQL support and will try to help where I can.

We should not try to spit too much on M$ products, there is a huge amount of people not using any linux/unix based OS, so why leave them outside?
I know mySQL is running fine with Microsoft, but the MSSQL is running even better and it is a lot easier to integrate in MS-office, MOSS, BIZtalk & Co

my2Cents

I would say the MS SQL is not niche in relation to DMs in use in general, but for Drupal usage is very well could be. That being said, I am working on an implementation that is scheduled to move to MS SQL within the next 6 months. We are using D6 right now. Are there any plans to add to contrib to support MSSQL on D6, or just for D7?

I think hswong3i have really wasted enough time on a try to implement a D6 version with oracle and mssql... as module support is also very difficult I think nobody should waste more months on this and better spend the time on implementing a D7 version only... but it's not my time and only a suggestion.

Now that we have a proper database layer, there is no point in postponing this anymore.

Note that the Zend_DB framework already has some code we could reuse (for example the tricky "range" emulation).

I posted a BOF session: http://dc2009.drupalcon.org/node/2798 - if you're interested in getting together to chat about SQL Server... I think it might help just to meet, decide on a direction, and actually get something done :)

For a BoF to be at all useful, we first need to confirm the existence of a working, stable PDO-MS SQL driver, and one that we are able to use. Without that, there's nothing we can do.

SQLite already class DatabaseStatement_sqlite extends DatabaseStatementPrefetch implements Iterator, DatabaseStatementInterface has this. The only thing that currently is really tied to PDO is the connection but the way we are using factories, if DatabaseConnection_mssql implements all the methods we call then I do not think there is anythign that really mandates PDO.

The connection object is kinda important. And even in SQLite, we have our own statement object but it's wrapping a PDOStatement, which does the actual execution. If we are not using PDO for the connection and statement object, then we need to do ALL of our own prepared statement handling, too, in a type-safe way, in user-space. Do you seriously want to do that?

Even if we could hypothetically implement an MS SQL driver entirely in userspace, we do have limited development resources (read: time) and they'd be far better used elsewhere, including working on a real MS SQL-PDO driver rather than trying to hack something together that kinda-sorta works.

I am fine with whatever, really, I am just pointing at things that's possible and what's not. I just want to be sure that interested parties who are likely not the current DBTNG maintainers can implement their driver.

Note that in #133 I postponed this not until dbtng was committed - it had already been committed, but until there was a working driver in contrib. Considering our lack of resources for maintaining the existing database support in core, that's still a reasonable position IMO, so putting this back at postponed.

Why is http://pecl.php.net/package/PDO_DBLIB telling us PDO_DBLIB 1.0 stable if the driver is marked "experimental"? Strange...

really, why mark it postponed? it is a feature request. IMO, that just makes it harder to find for the people that want to collaborate on the feature. perhaps the driver should mature in contrib a bit before coming to core but no such delay was imposed on the SQLite driver. So, if a few responsible devs get behind this, then immediate commit to core is possible, IMO.

I've contacted Frank M. Kromann (PDO_DBLIB project lead) to get the low-down on the driver's status... he's been helpful in the past, so hopefully I'll hear from him soon. I worry, however, that the driver is 4 years old and possibly won't support newer SQL Server 2005 and 2008 features...

I researched this a little during the holidays:

* There is currently no useful PDO drivers for any decent version of SQL Server (hear: >= 2005). The two drivers (ODBC, and DBLIB) both lack proper support for N[VAR]CHAR(max) types, which are needed for arbitrary length string support.
* The only decent driver is the one from Microsoft [1]. It is, indeed, completely possible to write a Drupal driver that doesn't rely on PDO. The SQL Server driver offers decent prepared statements. *But*, it also does some very strange non-standard things with PHP references, which makes it a real mine field to use.

I have some not even alpha quality code in my Windows virtual machine. I could post it here if I manage to extract it from there.

[1] http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx

Here is the patch.

@matt_paz
you are absolutly right, that there is a big amount of IIS in production.
A good administrated Windows Server with IIS 7 (2003 / 2008) is no bigger risk then a good administrated Unix/Linux with an apache.
Plus the speed difference between Apache and the FastCGI is not a real issue anymore with IIS7.

@all DB friends
If we want Drupal to become something special on this overcrowded CMS market, we need some good support for the MS SQL and Oracle.
The free version of MS-SQL Server is serving the needs of small sites on Windows Server.
But much more, Drupal can become a very intresting alternative to the Sharepoint Portal Server for a lot of companies.

We should try to make this a much bigger topic then it is, to reach more and more demand and more and more people to ask for it.
If money is needed - as always :O) - we should try to find out how much and try to raise it.
I am sure some bigger comapnies would show intrest, there is a lot of demand for direct MS-SQL Support and Drupal.
I would also spend some money for a project like this and try to help with work and testing.

Regards Tanguero

@Damien Tournoud
I'm assuming your patch requires Windows IIS, MSSQL Server 2005 & MSSQL for PHP driver.

Can you detail what works and what doesn't work with your patch?

FYI, it would seem from the sqlphp blog that the MSSQL PHP driver is being actively developed and works with MSSQL Server 2008. Here's some resources if it helps. I'm sure some Microsoft developers would be willing to help out with questions.

The sqlphp blog
http://blogs.msdn.com/sqlphp/

Cumulative update dated Oct 2008
http://www.microsoft.com/downloads/details.aspx?FamilyId=61BF87E0-D031-4...

Microsoft's MSSQL Server driver for PHP Forum
http://social.msdn.microsoft.com/forums/en-US/sqldriverforphp/threads/

More interesting info.. It would seem the MSSQL Server 2005 for PHP driver is licensed under the Microsoft Public License. You can download the source and compile it with Visual C++.

Code repository.. Very Sourceforge like..
http://www.codeplex.com/SQL2K5PHP

The readme file..
http://www.codeplex.com/SQL2K5PHP/SourceControl/changeset/view/21088

Download the driver here
http://www.codeplex.com/SQL2K5PHP/Release/ProjectReleases.aspx?ReleaseId...

If I understood the above correctly... It's more the question of a missing MSSQL2K5/MSSQL2K8 PDO driver... and the driver provided by MS seems to be a native driver, but not PDO. We need a PDO driver...

@pcorbett
According to the MSSQL PDO page, Wez Furlong may also be an additional contact.

Seeing that the project hasn't been updated since 2005, mostly small compile fixes here and there according to the CVS log, I doubt there has been any significant feature updates.

MSSQL PDO development is stalled from what I can tell. It may be worth gathering support to move that project forward independently by way of advocacy, bounties, code sprints, create greater support for MSSQL in PHP in general, etc. The source is available for those parties interested in investing. I wonder if Microsoft would help that effort?

@hass
Agreed. That being said, I'm simply spreading knowledge and my research from googling. We need not discourage those crazy enough to implement it outside of PDO. :-)

Maybe someone have/find a email of the MS guys developing the native driver? We could ask them about a status of PDO support... I guess the PDO project is stalled because MS started to work on it's own driver and this was publicity known... I think they can do this job much easier and quicker. Maybe MS already have something in their desk not yet public...

FYI --- I've reached out to some Microsoft contacts and pointed them at this thread. I'm hoping we can get some interest from MSFT directly in working on the PDO driver.

There are departments that have a mandate to get PHP working better on Windows, and I think this would fall under that. I'll update here and/or get any folks working on this directly to comment if we can get direct support.

@momendo
I just emailed Wes as well since I have not heard from Frank. My guess is that they will point us toward either improving their driver or to the M$ one. I agree that it is probably out of date.

My feeling is that we're dealing with a proprietary product and we may just have to suck it up and depend on a proprietary driver to get us through this - hopefully Boris' efforts will get us somewhere with them. My hunch is that they are up to something Drupal over there and just being all Microsoft about it :)

In this gets in core, are patches going to be held because they fail on MS SQL? Who is going to maintain this driver?

@chx: trust me to fight over all I can against any new driver getting into core without a reliable maintainer :)

A reply from Wez Furlong, maintainer of PDO (among other things...) wez@php.net: "My recommendation is just to use PDO_ODBC with the freetds or "SQL Native Client" drivers, depending on whether you're on unix or windows."

Not terribly informative, but it sounds like he doesn't recommend PDO_DBLIB at least... I can inquire further if anyone wants more details.

Well, then we'd need someone to write an ODBC backend for the new API. Doable I suspect, but given my past experience with ODBC quite painful. (Finding an ODBC driver that doesn't suck is a challenge in itself.) It will likely also need to follow the manual-buffering approach of SQLite.

I would call that a separate issue from this thread, however.

Well, then we'd need someone to write an ODBC backend for the new API. Doable I suspect, but given my past experience with ODBC quite painful. (Finding an ODBC driver that doesn't suck is a challenge in itself.) It will likely also need to follow the manual-buffering approach of SQLite.

As I stated in #154, neight ODBC nor DBLIB manage the new nvarchar(max) types, at least that was the result of my testing (even when using the native SQL Server 2005 ODBC driver). Thus, they are perfectly useless for our purpose.

@Damien Tournoud: I currently run a Drupal 5/PHP 5/MSSQL 2005/FreeTDS (php_dblib.dll) site and it can work with nvarchar(MAX) fields fine last time I checked. This is using a custom database.mssql.inc abstraction. I'm not sure, however, how PDO might change things in this case, however - I'm just using straight PHP, non-PDO functions like mssql_fetch_row, mssql_query, etc. Since I'm more of an advanced end-user and only an aspiring database/PHP guru, I could be totally missing something...

I haven't had time to attempt, but would something like this work?

On Windows:

- Official PHP or Zend PHP build
- php_pdo_odbc.dll active in php.ini
- PDO dsn ~ $db = new PDO("odbc:Driver={SQL Native Client};Server=localhost;Database=dbname",$user,$pass)

(The SQL Native Client isn't the same as the newer SQL Server 2005 ODBC driver, but Wez mentioned that it "has the best level of support for long columns and unicode data..." and it is "the best available way to talk to SQL Server from Windows systems.." Thoughts?

On Unix:

- unixODBC or iODBC installed
- FreeTDS libraries + FreeTDS ODBC driver
- PHP's PDO_ODBC built against unixODBC or iODBC
- PDO DSN ~ $db = new PDO("odbc:Driver=FreedTDS;Server=dbhost;tds_version=8.0;client_charset=utf-8;database=dbname", $user, $pass)

I think we should still meet at a BOF, if possible. If someone has a copy of windows server on their laptop, perhaps we can give some of these suggestions a try: http://dc2009.drupalcon.org/node/2798

My experiences in the past with connecting to an MS SQL server over ODBC, using FreeTDS or something else, have been singularly awful. I wasn't using PDO at the time, so I don't know if it makes a difference there, but I am not encouraged.

I think things have changed recently. I'm going to try and make time to test this and report back here (although this might become my "project" on the plane to DC). If others want to, please do so as well. Depending on how things go, I'll definitely want to meet up and chat.

Have someone heard anything from MS directly?

Not me, but the above configuration was recommended by Wez Furlong from PHP.

Update on MSFT communication: some email has been exchanged again (today), I have suggested that the best path would be a *native* PDO client, as it also means it is under PHP License, and it can help people other than Drupal. Haven't penetrated to technical people directly, trying to get them engaged here directly.

If someone else wants to formulate more directly what we want (i.e. ODBC is crappy or whatever), that would help. Perhaps a wiki page on groups.drupal.org would help.

@Boris: I've been telling MS representatives that we need a native PDO driver for MS SQL since quite literally DrupalCon Boston. It's already been formulated. I don't see a wiki page being any improvement over "Make a PDO driver that works and handles UTF-8 properly and the Drupal part is easy, full stop" said in person repeatedly.

@Crell: just making sure that nothing is lost in translation. I'll keep pointing back to this thread as gospel and/or loop you in via email if we can get someone at the technical level directly.

I managed to get a hold of one of the Microsoft developers. Here is what he said.

------------------------------------------------------------------------------

Hey! Thanks for the inquiry.

The short answers to your question are:
1) There is a PDO ODBC driver. Whether it fits your needs or not, I
don't know, but it should work with SQL Server.
2) We are evaluating creating a PDO driver. We definitely appreciate
knowing that Drupal is looking at PDO, as it helps our evaluation.

As for timeline, I can only say that "we are evaluating creating a PDO
driver." :)

Thanks, and if you have any interest in seeing it, feel free to voice
your opinion(s) at our regular support forum:
http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/threads/

Thanks again.

Jay Kint
SQL Server Driver for PHP Developer

wrote:
> User antgiant has contacted you through CodePlex using the Contact User
> form. Replying to this email will reply directly to the user. If you do not
> wish to allow being contacted, you may indicate so on your profile page.
>
> The following is the message from antgiant:
>
> I'm very interested in a cross platform mssql driver for php, preferably a
> pdo driver. Also Drupal the CMS we use (1 of the top 3
> http://www.waterandstone.com/downloads/2008OpenSourceCMSMarketSurvey.pdf)
> has a very active group looking to add mssql support
> (http://drupal.org/node/74308).
>
> Can you offer any advice on what drivers to use or what timeline there is
> for a PDO driver?
>
> Thank you very much for your time.

I should add that there are currently no threads on that forum asking for a PDO driver. The closest to such a request is http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/754... So perhaps we all need to go over there and make some noise. Any volunteers so start the thread?

I spoke with Damien Tournoud at a small (3 people) MSSQL BoF at DCDC - he mentioned that he and Dries had contacted Microsoft and had been told something similar but also that Microsoft was willing to put some resources on the project.

@Damien Tournoud: Any thoughts on this? Do you see the patch you shared in #155 as being something we should work with or, assuming Microsoft helps out, should we wait...?

I agree that trying to organize some kind of thread so that Microsoft "gets it" is a good idea, but I would want to make sure everyone is on the same page before doing so. Sounds like we are more or less...

I commented on the thread linked above. There are some MSFT folks heading to SXSW, I am trying to set up Crell or someone else to go to dinner with them.

Could anyone do a quick "State of Drupal on MSSQL 2009" report. Seems like this has been worked on a lot since 2003, but I'm not sure from all the posts what to try to pull together to make this work today April 2009.

We need:

IIS, MSSQL, Drupal (v. 6 preferrable, but 5 and/or 7 would also be fine), and the required drupal patches to match the Drupal version. Any special PHP drivers or other requirements would also be helpful.

I've heard installing Drupal to a MSSQL database is possible and a requirement for my client. Appreciate the great work that seems to have been done on this so far and any help navigating the path.

Thanks!

The current status is that we're all waiting on Microsoft to develop a PDO-friendly PHP driver. There are "hacks" to get Drupal working with MSSQL -- for example, I have it running on D5 in a production environment and have shared code in my sandbox http://cvs.drupal.org/viewvc.py/drupal/contributions/sandbox/pcorbett/dr....

Thanks for this. I'm wondering whether based on the fact this thread indicates further discussion at DrupalCon in early March with Microsoft about a PDO Driver and it is now approaching 1st May with no further comment, is this indicative of a decision to not pursue a further solution to the Drupal on MSSQL question?

I am trying to download your sandbox with TortoiseSVN to test out your implementation, but I am getting:

Command: Checkout from http://cvs.drupal.org/viewvc.py/drupal/contributions/sandbox/pcorbett/dr..., revision HEAD, Fully recursive, Externals included
Error: Repository moved temporarily to
'/viewvc.py/drupal/contributions/sandbox/pcorbett/drupal-mssql/'; please relocate
Finished!

I am not very familiar with Drupal Sandboxes so maybe I am doing something wrong to access the files in the way that I am trying. I'll keep working on it. Was your sandbox version the only working implementation from the first discussions I find of Drupal on MSSQL in 2003 in this and other threads?

Just curious whether you used this sandbox version to actually install Drupal to your MSSQL database?

Thanks for any information you or others are willing to share.

Sorry. My misread...looks like the hope was to hook up a dinner at SXSW-March 13-22nd.

Any word?

hpcalaf: you are looking at Drupal 6, so you'll need to follow pcorbett's instruction for that. This is an open feature request / discussion for Drupal 7.

Several people have been trying to have deeper discussions with folks at MSFT, since the database layer has changed over in Drupal 7. Code freeze isn't until September, with release likely in the new year.

Thank you Mr. Corbett and Mann for the this information....it has been really helpful.

I've been struggling with this for some time and like pcorbett's sandbox implementation, I was able to pull together the pieces to get a 5.x Drupal installation page that allows me to select mssql as my database type and supply credentials for accessing the specific precreated database from the various patches I have found in this thread. After supplying the correct credentials though (I have confirmed that I can access the database using OBDC and SQLExpress Client) the script returns to the initial entry point without response or error. I have the MSSQL driver installed for PHP and that it is loaded is confirmed by phpinfo(). Getting this to work will provide helpful information for me. I would appreciate any thoughts or troubleshooting tips to help figure out what might be going wrong.

MSSQL on Drupal 6 is my real goal though. Contrary to Mr Mann's indication, Mr. Corbett's work is not a Drupal 6 implementation, but Drupal 5. Reading between that fact and both of your comments it seems that most folks have determined to abandon Drupal 6 on MSSQL, hoping for a workable solution in Drupal 7 after Jan 2010? I would appreciate comment on whether that is the actual state of affairs or whether some workable Drupal 6 for MSSQL solution exists. For example, did anything further happen with hswong3i's work in August 2007. I have tried creating a version of Drupal 6 with these patches that allows for installation into a MSSQL Database, but it is even less successful for me than Mr. Corbett's Drupal 5 version.

I would appreciate your comments as I advise a client on an appropriate CMS platform for their current and future needs.

Oh sorry, if this is the wrong place to be asking these questions; historically it seemed the correct thread. Is there another more appropriate thread discussing Drupal 6 on MSSQL?

Henry

This is a feature request thread, so no, this is not the best place for a lengthy discussion :P

See the Enterprise group on groups.drupal.org, where pcorbett also posted about his code, and you can have an actual discussion there.

@hpcalaf: as one clear statement - do not use any of this MsSQL core patches or try to run Drupal on MsSQL expect you are crazy or you'd like to waste your time. Run it on MySQL or if you don't like it - use pgSQL. You should also expect issues form time to time with pgSQL as many developers do not test their software on pgsql. So if you'd don't like to run into issues with core and every module - use MySQL and nothing else. If you'd nevertheless like to go with MsSQL - tell your customer that this makes no sense today and he need to invest the 10th in man power (if this is really enough). Save the MsSQL license fee and wasted working hours today and wait for the day MsSQL/Oracle/DB2 becomes officially supported by Drupal.

@ Boris Mann I got it! :-) I will pursue this at my own risk on Enterprise group on groups.drupal.org.
@hass I hear and take seriously your advice from all I have read to this point. I will let them know!

On behalf of my client....MSSQL support would be helpful to make Drupal the no-brainer choice for CMS. Wishing you success as you move toward implementation of this important feature request.

Peace, Henry :-)

Today Microsoft released Version 1.0 of their non-PDO SQL Server Driver (See Here). Since there is clearly active development happening right now I have created a thread on their MSDN page requesting a PDO driver. If you are interested in having this please let them know in the thread.

If we don't tell them we want a PDO driver the evaluation process they are going through right now will probably end without a PDO driver.

So please make some noise. Let them know we would value a PDO driver. (Here)

hi
i'm new drupal user !

how can build a drupal website with PHP , MS SQL ?
Please explain one by one !

I'm using Drupal 6.13

new features will only be added to the latest version of drupal.

please, do not change the version, as this'll slow down development, because issues might go of the radar.

Moving to D8, that doesn't stop someone working on this for contrib in D7.

If you are interested in Microsoft making a PDO driver, they have a survey up asking for input on future direction. Please go provide some input. https://www.surveymonkey.com/s.aspx?sm=ZyUzG1TqA30QaEgAS9FYuQ_3d_3d

quick quote from http://www.php.net/manual/en/ref.pdo-odbc.php

On Windows, PDO_ODBC is built into the PHP core by default. It is linked against the Windows ODBC Driver Manager so that PHP can connect to any database cataloged as a System DSN, and is the recommended driver for connecting to Microsoft SQL Server databases

Considering that PDO driver for MSSQL is still experimental, what about implementing the ODBC driver instead?

The problem there is still that we'd need a readily-available way to test it. Few people will have ODBC setup, or a DB to connect to with it. So I'm still not sure that is a good idea for core.

As a contrib driver, though, +1. We already have an Oracle driver in contrib that works fine, so an ODBC driver there would be great.

Just wanted to post that the survey (link in #196) is closed.

But there looks to be another Microsoft Connect feedback posting that looks like it might be worth chiming in on:

https://connect.microsoft.com/SQLServer/feedback/details/541956/pdo-driv...

Thanks for the link, I'll put in my vote.

I was wondering, is the comment posted there,

Posted by akhenath0n on 3/16/2010 at 8:54 AM
you can use the adodb extension : PDO_DBLIB
http://php.net/manual/fr/ref.pdo-dblib.php

useful in this context?

http://www.php.net/manual/en/ref.pdo-dblib.php clearly states

[The PDO_DBLIB] extension is EXPERIMENTAL. The behaviour of this extension including the names of its functions and any other documentation surrounding this extension may change without notice in a future release of PHP. This extension should be used at your own risk.

also, i've added my vote.

So Microsoft just announced at Drupalcon support with a new PHP CTP PDO driver that can reliably run Drupal 7. I can't find the download link just yet. Has anyone tried it?

Update

The link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=df4d9cc9-459c-4...

Temporarily, you can refer to:

• Blog post: http://www.commerceguys.com/about/news/drupal-7-sql-server-preview
• Microsoft page: http://microsoft.com/web/drupal

The project page is at http://drupal.org/project/sqlsrv, I still need to push the code there, but you can use it to report bugs.

I'm closing this feature request. We could consider moving that to core in Drupal 8, but we really want a different issue to do that, because 200+ comments is just not manageable.

http://www.commerceguys.com/resources/articles/135 and http://www.microsoft.com/web/drupal/. Needs a little work but it looks are very very close.

This are great news!

Aside, are we able to track the "usage" of this DB driver - that is not a module? Maybe also very important for security updates... oracle driver also seems not having a module info file... so no way to track and inform about security issues that could really be inside a DB driver :-(

"Aside, are we able to track the "usage" of this DB driver - that is not a module? Maybe also very important for security updates... oracle driver also seems not having a module info file... so no way to track and inform about security issues that could really be inside a DB driver :-("

We'll be happy to help, what do you need from us?

Ashay Chaudhary
Program Manager, SQL Server Driver for PHP
Microsoft

The root issue is that we cannot actually ship third-party database drivers as proper Drupal modules, and as a consequence the usage of those will not be tracked properly. See #783812: Allow db drivers outside of 'core'

Then we got the route that SimpleTest had to for a while:

1. We maintain a patch that pre-pares core for use with SQL Server. This patch will have to pass all core tests so that we can maintain that the "patched" version is as clean and stable as a "normal" core.

2. This patch will have to stay in sync with the minor version of core.

3. My providing this as a module which essentially only has a patch, we can thus track the usage.

I am still not 100% clear on the concerns, I'll take this offline with Damien to understand it and then see how we can help.

Oracle #777746: Implement usage tracking and call home for security releases implements a helper module for config and will solve the update/security warnings and tracking by this way

A MSSQL PHP Driver is available.

SQL Server Driver for PHP 2.0 CTP2 – June 2010:
Driver: http://www.microsoft.com/downloads/details.aspx?FamilyID=df4d9cc9-459c-4...
Blog discussion: http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/705...

SQL Server Driver for PHP 1.1 - October 2009:
Older version: http://www.microsoft.com/downloads/details.aspx?FamilyID=ccdf728b-1ea0-4...

Closing out this issue -- please see http://drupal.org/project/sqlsrv and open a new issue against Drupal 8.x if someone agrees to provide the support and patches required.

I need the clean version of mssql patch for drupal 6 since am using dreamweaver for php I cannot find a way to merge the patches.
Please can someone place a clean patch of this file.
Thanks
SK

me too, I need a clean and running version of drupal 6 that supports mssql.

Thank you in advance. Appreciate your effort. :D

Me too looking for same soulution...I need a clean and running version of drupal 6 that supports mssql. can any one help me out of this :-)

We are looking to implement the following combination:

MSSQL
Red Hat
Drupal 7

Is this possible?