This thread was created to keep a seperate thread for the 5.1 version of the SQL Server patch vs the HEAD (6.0) version.

The core database driver will basically be identical b/t the two however.

Comments

Watcherq’s picture

Ok, I tried the patch on a fresh drupal-5.1 download. There was no error in the patching. Yay!

The first time I tried the install page, I came up with the form to key in the info. After that, the system hit 100% with the CPU going to the SQLServer process. However, no table was created, and after a short while (about 1 minute), the load drops back to zero.

I tried running it again, but could never get back that page. I've restarted the web server, and sql server but again it didn't work. I noticed that the sites\default\settings.php was changed with the correct login details.

I tried again by blowing away the drupal directory and trying it again. No go; I've got a blank page again for index.php and install.php. When I tried deleting the directory, the following files were locked by the webserver and I had to restart the webserver. The webserver restarted easily (ie no need for kiling it on TaskMgr), and the files were released. They are:
includes\bootstrap.inc
includes\install.inc

To repeat my config:
- Win2k3 SP2 with IIS 6.0
- PHP 5.2.1 with updated ntwdblib.dll
- Drupal 5.1 with the patch above.
- SQL 2005 Express

Watcherq’s picture

Oooops, forgot to add that install.php was also locked

Souvent22’s picture

Try this patch. The first patch was just an old one I had. I tested this one, and it installed fine for me. Let me know if you have any issues installing. I know that this patch still needs tweaking though.

Souvent22’s picture

Humph. didn't get attached. Here ya go.

One issue I did see was that on the "Congrats. Drupal has been installed" screen, the linke to "Click here to go to you drupal site" wasn't there. I'll look into this later. But it does install fine. I created some stories, users, and comments to test. I'm going to merge some changes from my production db_layer engine from the client into this one in the next few days also for a more stable patch/db_layer.

Souvent22’s picture

ok, we'll try this again. my attachment won't post.

Souvent22’s picture

StatusFileSize
new84.4 KB

ah, finally got it.

Souvent22’s picture

StatusFileSize
new80.26 KB

Merged in driver-core changes from HEAD into 5.x patch

Watcherq’s picture

Hi Souvent,
Thanks for all the effort so early (?) in the morning. Ok, I've tried 5_1 and 5_2 patch, all patched fine but they still doesn't work. In both cases, I get a blank browser, the install.php locks up and requires the IIS to be restarted to be deleted. Not even sites\default\settings.php was set. No DB connection was establised as well.

I sense it is very close. If you want, I can put up the drupal source (post patch) to you to check.

Souvent22’s picture

Hm. Yes, could you email me that, or attach it as a zip? I just did the following and it worked for me:

1. download drupal 5-1
2. apply patch
3. go to site
4. select 'mssql' as database
5. set db options (db name, username, pass, port)
6. click install
7. site is up and running

You're using SQL-Server 2005 correct? and have the database created before installing?
It should catch errors though and not hang.
Oh, i've done this on both apache 2.2 and IIS 6 (running as fast-cgi). i'll try it as ISAPI also and get back to you.

Watcherq’s picture

Hi Souvent,
You can download it at http://203.117.183.229/drupal-5.1.zip I have put in php.ini as well.

pcorbett’s picture

Thanks for posting the 5 patch, Souvent. The patch worked fine for me on a clean install of 5.1, however, I get a lot of cron_run errors when I "run cron manually." It throws these errors after saying that cron ran successfully:

user warning: Invalid object name 'cron_runs'. query: SELECT COUNT(*) FROM cron_runs in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.

and

user warning: Invalid object name 'cron_run_info'. query: UPDATE cron_run_info SET run_status = 0, run_stop = 1175547597 WHERE riid = 0 in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.

I am using SQL Server 2000.... so that may have something to do with it. Anyone else?

pcorbett’s picture

There is also this error that shows up on the status report page where I believe there is an error with the IF statement - Is this a shorthand if/else statement? I have never seen that syntax before.

user warning: Line 1: Incorrect syntax near ','. query: SELECT TOP 100 GREATEST(IF(c.last_comment_timestamp IS NULL, 0, c.last_comment_timestamp), n.changed) as last_change, n.nid FROM node n LEFT JOIN node_comment_statistics c ON n.nid = c.nid WHERE n.status = 1 AND ((GREATEST(n.changed, c.last_comment_timestamp) = 0 AND n.nid > 0) OR (n.changed > 0 OR c.last_comment_timestamp > 0)) ORDER BY GREATEST(n.changed, c.last_comment_timestamp) ASC, n.nid ASC in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
Souvent22’s picture

pcorbett,

Ah. I apologize to everyone; I seem to of gotten my "multi-cron" patch merged in with the "sql-server" patch. Darn patches are running amuck! :). I'll clean it up and re-post a clean patch for sql-server only. Sorry about that. That's why you are getting "cron_runs" error, because it's a table that is created if you use the multi-cron patch.

Souvent22’s picture

StatusFileSize
new57.09 KB

Re-rolled patch taking out my multi-thread changes (sorry 'bout that).

Watchrq, i still haven't been able to re-create your issue. I tried to download your zip file, but link it dead. I had the zip fire previously, but I totally re-formatted my laptop today and have thus lost it. :-/.

Watcherq’s picture

I've lost access to that server as well. Let me patch later with your latest patch and get back to you very soon. Thanks for all your help.

Souvent22’s picture

I think the "IF" statement is a seperate issue and not because of the patch being dirty. I'll have a look into that also.

pcorbett’s picture

Can anyone else confirm that the latest patch fixed the cron_run issue? I'm still getting the following errors when running cron manually:

    * user warning: Invalid object name 'cron_runs'. query: SELECT COUNT(*) FROM cron_runs in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
    * user warning: Invalid object name 'cron_runs'. query: INSERT INTO cron_runs(run_date, run_status) VALUES(1175613562, 1) in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
    * user warning: Invalid object name 'cron_runs'. query: SELECT MAX(rid) FROM cron_runs in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
    * user warning: Invalid object name 'cron_run_info'. query: INSERT INTO cron_run_info(rid, module_name, run_status, run_date) VALUES(0, 'system-all-modules', 1, 1175613562) in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
    * user warning: Invalid object name 'cron_run_info'. query: SELECT MAX(riid) FROM cron_run_info in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
    * user warning: Invalid object name 'cron_run_info'. query: UPDATE cron_run_info SET run_status = 0, run_stop = 1175613562 WHERE riid = 0 in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
    * user warning: Invalid object name 'cron_runs'. query: UPDATE cron_runs SET run_status = 0, run_ticks = 0, run_stop = 1175613562 WHERE rid = 0 in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.

I'm not sure if this is causing any issues necessarily, but I also receive this error when running install.php for the first time:

user warning: Invalid object name '#missing_nids'. query: INSERT INTO node_comment_statistics (nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count) SELECT n.nid, n.created, NULL, n.uid, 0 FROM #missing_nids n in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 192.
pcorbett’s picture

On the "list users" page, the following error occurs:

user warning: Incorrect syntax near the keyword 'DISTINCT'. query: SELECT TOP 50 DISTINCT u.uid, u.name, u.status, u.created, u.access FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid WHERE u.uid != 0 ORDER BY u.created DESC in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 193.

I believe this is caused because the DISTINCT keyword appears after the TOP clause. A quick fix for this can be made in database.mssql.inc around line 423:

 if($from == 0) {
    $query = str_replace("SELECT ", "SELECT TOP $to ", $query);
    // DISTINCT fix
    if (!strpos($query, "DISTINCT")) {
	$query = str_replace("SELECT ", "SELECT DISTINCT ", $query);
	$query = str_replace("DISTINCT ", "", $query); 
    }
    // END DISTINCT fix
  }

This will also become an issue, I imagine, once I have a list of users long enough to page at which point code would need to be added to that section as well. I'm not sure if you'd agree with my particular solution, Souvent22, but I think something should be done to account for the occasional DISTINCT that comes through in a query.

pcorbett’s picture

Sorry, I left in a ! in front of 'strpos' (part of my debugging). Below is the correct code I used to patch the bug:

if (strpos($query, "DISTINCT")) {
  $query = str_replace("SELECT ", "SELECT DISTINCT ", $query);
  $query = str_replace("DISTINCT ", "", $query); 
}
Souvent22’s picture

pcorbett,

Hey, i'm looking into the TOP/Distinct issue now. But as for the cron_runs, not sure. If you open the patch up, and do a search for "cron_runs", nothing is found; did you blow away your 5.x and patch a fresh one?

Souvent22’s picture

ok, i've got a fix. but i need verification on something. those who have the patch working, if you could run this query:

SELECT DISTINCT * 
FROM ( 
	SELECT ROW_NUMBER() OVER(ORDER BY u.created DESC) AS rownumber, u.uid, u.name, u.status, u.created, u.access 
FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid WHERE u.uid != 0 ) 
as drupal_range_query

and then run this created:

SELECT DISTINCT * 
FROM ( 
	SELECT ROW_NUMBER() OVER(ORDER BY u.uid DESC) AS rownumber, u.uid, u.name, u.status, u.created, u.access 
FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid WHERE u.uid != 0 ) 
as drupal_range_query

Please tell me your results. here are mine:
Query 1: 25 seconds
Query 2: 0 seconds

Moral of the story...we can not order on a non-indexed field (notice that in query 2, the order by is on uid, not the created field). SQL Server is very fast...but very picky about searching on non-indexed fields, and even more picky on sorting on non-indexed fields. One can read more about this by referencing the MSDN, and reading about clustered and non-clustered indexes and indexes in general in SQLServer.
It's better if we tell it to not order by at all (e.g.: )

SELECT DISTINCT * 
FROM ( 
	SELECT ROW_NUMBER() OVER(ORDER BY @@SPID) AS rownumber, u.uid, u.name, u.status, u.created, u.access 
FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid WHERE u.uid != 0 ) 
as drupal_range_query

(code time: 0 seconds)

I only have 2 users in the DB. I'm hoping to be able to implement my _db_mssql_locate_table_index function which will find a better index if the field userd is not indexed. Oh, for reference, here is the original query from the user.module:

SELECT DISTINCT u.uid, u.name, u.status, u.created, u.access FROM {users} u LEFT JOIN {users_roles} ur ON u.uid = ur.uid  WHERE u.uid != 0  ORDER BY  u.created DESC
Souvent22’s picture

StatusFileSize
new193.81 KB

K, here's an updated patch.

Added a nice function that looks through the system tables, and creates a nice little table for us of all the tables.columns that are text types so I can use it as an easy reference later is we need to handle text columns in a special manner.

Futher more, more SQL problems. If you go to "Top page not founds", there is this query:

SELECT COUNT(DISTINCT(message)) FROM watchdog WHERE type = 'page not found' 

Message is a text field, so MSSQL throws an instant error because you can't "distinct" a text field (e.g. BLOB). Perhaps we can change the message field to a varchar(255) ? that's waht i currently did...but I know there is that PHP bug under windows in which varchars are limited to 255 even though you may set it to 4000. I'm thinking perhaps i can use the table i made and do a "CONVERT" on those fields when being fetched. e.g.:

SELECT COUNT(DISTINCT(CONVERT(TEXT,message)) FROM watchdog WHERE type = 'page not found'

but i'm thinking it'll go back and throw the other type of err....perhaps there's a better way we can find the top page not found errors all togather...hmm.....thoughts?

pcorbett’s picture

Souvent,

I think patch 5.4 is for the devel module and not for SQL Server enhancements...

pcorbett’s picture

The cron_run issue I was having earlier is fixed now. I must have not have tested from a clean install. Sorry.

pcorbett’s picture

When I added a "footer" block in the blocks menu and then clicked on "configure" the following warning appeared:

user warning: Ambiguous column name 'title'. query: SELECT bx.*, title FROM boxes bx INNER JOIN blocks bl ON bx.bid = bl.delta WHERE module = 'block' AND bid = 1 in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 193.

I fixed this by editing line 362 in block.module and adding the "bl" prefix to "title":

SELECT bx.*, bl.title FROM boxes bx INNER JOIN blocks bl ON bx.bid = bl.delta WHERE module = 'block' AND bid = 1

I couldn't find any existing bugs or threads about this, so perhaps it's particular to SQL SERVER (maybe MySQL is smarter and assumes title refers to the blocks table since all of the boxes columns are already being selected).

Can anyone else confirm this?

Souvent22’s picture

Confirmed. I had to "clean" the blocks code for the clients site. It's got some hairy stuff in it. As a rule of thumb though, all column tables should be name-spaced. It makes it easier to read the code also when looking at something the first time, and debugging. I'd open a ticket.

Souvent22’s picture

Sorry, that comment reads funny:

All columns should to name-spaced... is how it should read.

pcorbett’s picture

Also, I think the 5.4 patch you posted was incorrect or possibly a patch for the devel module.

pcorbett’s picture

When I make any changes to the User Access page or perform a search, I receive the following warning:

user warning: query: in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 193.

However, it does only seem to be a warning, as both functions work, it's just that the warning is thrown as well.

pcorbett’s picture

When I edit a page (and sometimes other content types as well) I receive the following warning:

warning: Invalid argument supplied for foreach() in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 293.

Can anyone else confirm this using SQL 2000 / MS SQL / Drupal 5.1 / IIS 6.0 - or anything else for that matter?

pcorbett’s picture

In mssql_install_driver_merged_5_3.patch, the primary key of the contact table is not set to auto increment and is instead set with a default value of '0':

Line 388: cid int NOT NULL default '0',

This prevents one from adding more than one contact list / category.

A fix would be for line 388:

cid int NOT NULL identity(1,1),
pcorbett’s picture

Back to the GREATEST function issue when viewing Administer > Logs > Status Report > Run Cron Manually:

SELECT TOP 100 GREATEST(
IF(c.last_comment_timestamp IS NULL, 0, c.last_comment_timestamp), n.changed) as last_change, n.nid 
FROM node n 
LEFT JOIN node_comment_statistics c ON n.nid = c.nid 
WHERE n.status = 1 AND (
(GREATEST(n.changed, c.last_comment_timestamp) = 0 AND n.nid > 0) OR (n.changed > 0 OR c.last_comment_timestamp > 0)) ORDER BY GREATEST(n.changed, c.last_comment_timestamp) ASC, n.nid ASC

The issues I see with this is that the IF statement is not correct, and the wrong number of parameters are being passed to the GREATEST function (usually only 2 instead of 3). I adjust it to read the following way, but tell me if I'm way off:

SELECT TOP 100 GREATEST(
ISNULL(c.last_comment_timestamp, 0), c.last_comment_timestamp,0), n.changed as last_change, n.nid 
FROM node n 
LEFT JOIN node_comment_statistics c ON n.nid = c.nid 
WHERE n.status = 1 AND ((GREATEST(n.changed, c.last_comment_timestamp,0) = 0 AND n.nid > 0) OR (n.changed > 0 
OR c.last_comment_timestamp > 0)) 
ORDER BY GREATEST(n.changed, c.last_comment_timestamp,0) ASC, n.nid ASC

I simply adjusted the IF statement to an ISNULL statement (which I think you're were trying to do) and added a 3rd parameter of zero (0) when the GREATEST function was called. Not sure if this is accomplishing what the original was, but it works.

pcorbett’s picture

The following fields are declared incorrectly (at least for SQL 2000) in mssql_install_driver_merged_5_3.patch. They are set to default as null (e.g, search_index table : "score float default NULL", but never actually set as nullable fields as they should be (e.g., score float NULL default NULL:

search_total.count
aggregator_item.timestamp
accesslog.title, .path, .url, .hostname
search.type
search_index.type, .fromtype, .score
profile_fields.title, .name, .explanation, .category, .page, .type

Can anyone confirm this? Does this work in SQL SERVER 2005?

pcorbett’s picture

Update on the DISTINCT issue concerning database.mssql.inc line 424. I ran into a case when viewing a taxonomy category that cased an error. The original code was using something like "SELECT TOP X DISTINCT" and I had suggested it be changed to " SELECT DISTINCT TOP X ...." Now, a case came by where the query looked like this "SELECT TOP X DISTINCT(x), y, z" which caused an issue because I was only finding and replacing instances of "DISTINCT " (note the blank space). I adjust the code to search for both a blank space and a "(" after DISTINCT... Open to suggestions on how to best do this:

  if($from == 0) {
    $query = str_replace("SELECT ", "SELECT TOP $to ", $query);
   
 // DISTINCT fix
   $d_match = '/DISTINCT(\s|.)/i';
   $d_replace = '/DISTINCT/';
   if (preg_match($d_match,$query)) {
     $query = str_replace("SELECT ", "SELECT DISTINCT ", $query);
     $query = preg_replace($d_replace, ' ', $query);
   }
   // END DISTINCT fix

Souvent22’s picture

pcorbett,

Thanks for all the updates. I'll try and roll all these into a patch, unless you can do a unified diff of your current setup. but i'll hopefully roll a patch and the end of this week.

pcorbett’s picture

On line 603 of database.mssql.inc, the syntax "SHOW TABLES" is used and I don't believe it is available in any release of SQL Server. As a result, attempting an install of CCK threw the following error:

user warning: Incorrect syntax near the keyword 'LIKE'. query: SHOW TABLES LIKE 'content_type_story' in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 194.

The line currently reads:

return db_num_rows(db_query("SHOW TABLES LIKE '{" . db_escape_table($table) . "}'"));

I suggest the following fix:

	return db_num_rows(db_query("select name from sysobjects where xtype = 'U' AND name LIKE '{" . db_escape_table($table) ."}'"));
pcorbett’s picture

The above may be different for SQL 2005. I think the tables that store this information are a bit different.

pcorbett’s picture

Here is a patch for database.mssql.inc. I can do others if you want, but keep in mind some of my edits were because I'm stuck with SQL 2000... I'm not terribly familiar with how to roll patches and upadates, etc. but any suggestions you have are much welcomed. I'm willing to help you out as much as I can. I'm in the middle of an install and setup right now, which is the reason for so many posts lately - hope they weren't too overwhelming!

pcorbett’s picture

The issue discussed earlier in this thread about the 255 character limit PHP (php_mssql.dll) places on queries pulling varchar data from SQL Server is still and issue as well. Supposedly freeTDS is supposed to work using a windows-ported version of php_dblib.dll. I tried it and even contacted the freeTDS team, but have not made it work so far. If anyone wants to try, email me and I can help.

The "quick fix" is to change the column in sql server from a varchar to a TEXT data type (assuming it's over 255 characters) and do what Souvent22 suggested earlier:

select CONVERT(VARCHAR(XXXX), column) . . .

This could probably be done on a larger scale with one of the db query functions that would just look for a select on a varchar field over 255 and insert the convert in there. So what this does is two things:

1. Allows you to grab all the data from the column (because it was converted to a TEXT field that isn't affected by the 255 char limit)
2. Allows you to SELECT DISTINCT because you can't do that with a TEXT field (hence, the CONVERT(VARCHAR(XXXX))

Make sense?

Souvent22’s picture

Ok, so announcment 1, I have found a time window to do some hard-core patching on the 5.1 branch of the sql-server module. Getting 5.1 stable will lead to 6.0.

pcorbett, i'll be going back, and rolling your patch into 1 unifying patch.

Everyone, we are in phase 3 of the clinets roll-out, and we are in the sql-audit phase. So, I get to go through drupal and analyze each and every sql-statement in core....look for a list of sql-statmets that I will be proposing patches for to speed up and/or change so that they are faster, better, and hopefully will make sql-server integration much easer than it currently is.

pcorbett, i'll need some feedback and testing from you after i implement the reg-ex to do the replace for varchar fields to text.
also, i'm still considering branching the patch futher into mssql7, and mssql9 (2000, 2005)...however, i say for now we'll keep with the current patch, and hopefully we'll just need a "if" or 2 in order to handel both 2000 and 2005.

....and so off to the dungeon for me...hopeuflly i come out alive

pcorbett’s picture

Good news, Souvent22. I'm here to test whatever you'd like. I have another possible issue. For the user-defined GREATEST function, I have to reference the function in my code by specifying the owner of the function: "dbo.GREATEST(.....)" otherwise, it doesn't recognize the function. Again, might just be a SQL Server 2000 thing, but thought I'd bring it up.

pcorbett’s picture

Does searching work for anyone? I've updated my index and run cron, but still it doesn't seem to be finding any terms, even ones I see that exist in the search_index table. I'll look into it some more see what I can find.

pcorbett’s picture

I vote for IF statements rather than another branch of the patch to account for 2000/2005 compatibility. Let me know if you need help knowing were those statements might need to go.

pcorbett’s picture

Maybe my own setup, but can anyone using mssql access the page anonymously? I keep getting access denied (and not the welcome screen because I have a post that was set to post to the front page).

pcorbett’s picture

Update. I'm listing below all of the issues I have run into w/ a note if it may be a MSSQL 2000-only issue.

  1. GREATEST. User defined function did not install automatically. Need to grant certain privileges first?
  2. GREATEST and IF statement. May be just a syntax error. I suggested a quick fix, but it may require more looking into. post #12, post #32.
  3. #missing_nids. post #17 (second part). Have not tried running install.php lately, but it may be fixed by now.
  4. SELECT DISTINCT issue. DISTINCT needs to follow SELECT and not SELECT TOP.... DISTINCT. Quick fix, but should be improved with regex possibly. posts #18 & #19 & #34
  5. Ambiguous column. Suggest a fix to the module maintainers. Have not heard back yet. (post #25)
  6. Warning messages. Pretty common throughout site... post #29
  7. For each warning messagse. Pretty common throughout site... post #30
  8. Identity missing. post #31
  9. DEFAULT. Missing declarations post #33
  10. VARCHAR > 255 chars. Suggested, Souvent22 working on regex. post #39
  11. watchdog.message. Set column to VARCHAR instead of TEXT since TEXT fields can't be sorted

Suspected SQL Server 2000???

  1. VARCHAR(MAX). VARCHAR(MAX) --> TEXT or VARCHAR(8000) (doesn't support VARCHAR(MAX))
  2. User Defined Functions. I had to reference the GREATEST function with the owner: dbo.GREATEST.... post # SQL SERVER 2000-specific?
  3. SHOW TABLES. Correct syntax? post #36
  4. TOP(n). SQL Server 2000 doesn't support wrapping the TOP number in parenthesis.
  5. CONCAT. Change "CONCAT" to "+" in statistics.module on line 321. CONCAT (MySQL) == + (MSSQL)

Misc

  1. Anonymous users might not be able to access the front page... might be my setup though
  2. Search doesn't return any results for me.

I also have CCK running well in addition to LDAP Integration & TinyMCE if anyone is interested.

pcorbett’s picture

Update #2

One last thing I promise....

I forget where this was an issue but I remember mentioning that the GREATEST function took 3 parameters and in some places only 2 where specified. I think I simply added a '' as the last param being run into the function. Oh, it's mentioned in post #32

abhi.pandey’s picture

Assigned: Souvent22 » abhi.pandey

Hi everybody,
I was trying to install Drupal with
IIS 5.x with XP
PHP Installed with ISAPI support on IIS
5.3 patch of MS SQL server 2005

After applying patch it keeps on saying for many files - 1 on 1 hunk ignored - and finishes fine. However when I used 5.4 patch version - it used to exit after generating an error. When I tried to reach the folder from iis (i.e. localhost) it either says no input file specified or earlier it was saying, there's no database configured to be used with IIS.

When I try with Apache, it says the username and password didnt' work for login. I have checked it numerous times and it's correct.

Can anybody help me out on this issue please?

pcorbett’s picture

I don't think the 5.4 patch is working. How did you patch with 5.3 ... Try patch -p0 < [patch file] It should patch with no errors. Also, make sure you are patching on a clean install of Drupal 5.1.

abhi.pandey’s picture

Yes! the patching worked. I was underestimating the powers of -p0!!!!

Now the new problem is on IIS that tells me this -
Your web server does not appear to support any common database types. Check with your hosting provider to see if they offer any databases that Drupal supports.

How do I solve this problem? I get this problem whether I use drupal as a virtual directory or whether I put it inside wwwroot folder.

pcorbett’s picture

Hm. If it didn't automatically bring you there, have you tried going to http://[your server]/install.php? There it should ask you for what type of database you are using. If not, you can also manually do it in your sites/default/settings.php file.

pcorbett’s picture

What happened here?

Souvent22 - March 30, 2007 - 19:43
Project: Drupal
Version: 5.1
Component: database system
Category: support
Priority: normal
Assigned: abhi.pandey ???????
Status: active
Attachment: mssql_install_driver_merged_5_0.patch (60.16 KB)

pcorbett’s picture

In user.module, I'm not sure how this got changed using the 5.3 patch (maybe I modified this myself???) but line 362 has the following:

$result = db_query("SELECT DISTINCT(CONVERT(VARCHAR(4000), p.perm)) FROM {role} r INNER JOIN {permission} p ON p.rid = r.rid WHERE r.rid IN (%s)", implode(',', array_keys($account->roles)));

But i'm not sure why the DISTINCT or the CONVERT are there. I'm removing both for now because neither seem to make a difference that I can see.

Souvent, I didn't see this in your patch so maybe I made this change in my sleep or something. No clue. If CONVERT/DISTINCT need to be there, I would suggest putting " AS perm" before FROM so that the later calculations will work. Otherwise this results in the anonymous user not being able to access content.

Let me know what you think about this one....

abhi.pandey’s picture

I figured out it is better to work with MySql in my case, so I will keep to that. Thanks for all the help.

As per the Assigned-to mess, I am sorry about it. I didn't know it changes whole thing. Now that I've already made a mistake, I can only set it back to Unassigned, should I do that?

pcorbett’s picture

No big deal. Just wondering. Strange that it lets anyone change who something is assigned to at the root level... hmm. Good luck.

pcorbett’s picture

While trying to get the search to work, I came across the use of the POW function in SQL. It can be found on line 878 of node.module. Since POW doesn't exist, at least for MSSQL 2000, I changed it to POWER in order to work:

$ranking[] = '%d * POWER(2, (dbo.GREATEST(n.created, n.changed, c.last_comment_timestamp) - %d) * 6.43e-8)';
pcorbett’s picture

To get pagination working with SQL SERVER 2000, I added the following to database.mssql.inc around line 471:

// pcorbett 5/1/07
// $query = "SELECT * FROM (
// SELECT
// ROW_NUMBER() OVER($order_by) AS rownumber,
// $select
// FROM $qfrom
// $where
// ) as drupal_range_query
// WHERE rownumber BETWEEN $from AND $to;";

$query = "SELECT TOP $to $select FROM $qfrom $where";

}
_db_query_callback($args, TRUE);
$query = db_prefix_tables($query);
$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);

// pcorbett 5/1/07
// return _db_query($query);
$last_result = _db_query($query);
if ($from > 0) {
mssql_data_seek($last_result, $from);
return $last_result;
} else {
return _db_query($query);
}
}

pcorbett’s picture

Starting on line 157 in database.mssql.inc, I believe this is around where the search query gets wiped out:

  // LENGTH is called DATALENGTH in MS SQL.
  // TODO: Preg search instead of strpos.
  if(strpos($query,"LENGTH")){
    $pattern = '/(.+)LENGTH(.+)/i';
    $query = preg_replace($pattern, '${1}DATALENGTH$2', $query); 
  }
  // No LIMIT in MS SQL. We use SELECT TOP n ... FROM ...
  // Can't do TOP because it is NOT a range.
  // TODO: Preg search instead of strpos.
  if(strpos($query,'LIMIT')){
    $pattern = '/(.+)LIMIT (\d+), (\d+)/i';
    $query_new = preg_replace($pattern, '${1}', $query);
    $limit = preg_replace($pattern, '$3', $query);
    $query = str_replace('SELECT', 'SELECT TOP '.$limit, $query_new);
    unset($pattern, $query_new, $limit);
  }

  // TODO: Not sure what this is. 
  // TODO: Preg search instead of strpos so we don't replace valid instances of 'relevance'.
  if(strpos($query,'relevance')){
    $query = str_replace('relevance', '_relevance', $query_new);
	}

If you see... a query using relevance is calculated from the search page and then run through this function. It gets completely cleared due to the next to last line above because $query_new was UNSET in the chunk of code above.

As the TODO says, I'm not sure what this is :)

Any ideas or progress so far?

I'll let you know if I find anything.

pcorbett’s picture

Search is working now.

To get it to work so far, I've done the following:

  1. On line 878 of node.module, CONVERT the result of the GREATEST function in order to allow for numeric calculations (the GREATEST function returns SQL_VARIANT data type). Also, at least for SQL 2000, POW is not a function, but POWER is:
    $ranking[] = '%d * POWER(2, (CONVERT(int,dbo.GREATEST(n.created, n.changed, c.last_comment_timestamp)) - %d) * 6.43e-8)';
  2. Commented line ~180 of database.mssql.inc: //$query = str_replace('relevance', '_relevance', $query_new); Not sure what to do with this, and not exactly sure why relevance is being adjusted this way. Will most likely find out soon with this commented out.
  3. Added this after line 149 in database.mssql.inc: $query = str_replace("##","#",$query); Appears that temp tables are being run through this function twice and therefore, we need to remove the extra # in order for the DB to recognize the temp table.

That's it. Obviously, there is more work to be done to streamline this a bit. But for now, it works.

pcorbett’s picture

I was getting the following error often:

warning: Invalid argument supplied for foreach() in C:\Inetpub\wwwroot\drupal51\includes\database.mssql.inc on line 303.

This can be remedied a couple ways in database.mssql.inc in the db_fetch_array() function:

  1. Wrap the foreach() in an in_array() to make sure the array isn't empty or
  2. Comment everything and just have the following in this function (similar to database.mysql.inc):
    if ($result) {
    return mssql_fetch_array($result, MSSQL_ASSOC);
    }

I'm not sure why the function differs that much from mysql.database.inc... any reason for the foreach()????

pcorbett’s picture

Sorry, in the above: in_array is supposed to be "is_array"

Souvent22’s picture

Catching up and reading. I'm thinking this patch may have to stay as just that..a patch. As I've had to hack many of the core modules to get their SQL up to par. But, i'm hoping to post a "conslidation" patch soon.

pcorbett’s picture

Title: SQL Server 2005 patch » {permission}.perm

Which modules did you have to hack? I'm using most with no issues unless already noted.

pcorbett’s picture

Title: {permission}.perm » which core mods?

Which modules did you have to hack? I'm using most with no issues unless already noted.

Souvent22’s picture

Title: which core mods? » SQL Server 2005 patch

I'll list them in a comment in a bit. just changing the title of the thread back :)

pcorbett’s picture

Title: SQL Server 2005 patch » DISTINCT on TEXT

I'm assuming SELECT DISTINCT on a MySQL column works, but as we know for SQL Server does not allow this. Earlier we were throwing around ideas about how to address the 255 character php length limit for SELECTing from a varchar data-type column. In reference to the user.module use of this in the user_access function (around line 365) I think my quick fix is wrong.

First, the permission.perm col doesn't need to be converted to VARCHAR because the col is already a TEXT type, which is not effected by the 255 char limitation in PHP. However, that col has a SELECT DISTINCT, which doesn't work in SQL Server. For now, I took out the DISTINCT all together and things seem to be working, but it's a hack.

I was thinking maybe we could use a temp table or some kind of array sort that would remove any dupes from it.

Anyway... yeah.

Souvent22 - what's your e-mail? We should chat or IM about this project if you have time - we might be able to get things done faster.

pcorbett’s picture

Title: DISTINCT on TEXT » SQL Server 2005
Assigned: abhi.pandey » pcorbett

FYI. I had modified CCK to work with SQL Server 2000. One issue that I have to address is when a column is deleted. What happens is that if there is a default value associated with the column, SQL Server throws an error saying that there are objects associated/dependent on this column and that the column can't be deleted. I'm assuming in MySQL, it just deletes all associated constraints when the column is dropped (makes more sense to me).

So, I think the way to address this is to drop the default constraint before the column is dropped. I'll post with more specific instructions later. Maybe this is something that should be part of _db_query... some kind of monitor that waits for a DROP COLUMN and then checks for constraints?

Souvent22’s picture

pcorbett,

Ping me on email: earnest [dot] berry at da gmail to the com. (yeah, it's cryptic, you can use my contact form also :) ). I'd like to try and get a day squared away and dedicate a few days to it. Meaning like work on it in direct contact on like a Monday, gather current issues, decided a path, re-visit on like a thrusday, something like that. nothing "intense", but I'm sure you're schedule, like mine, it's hard to get in even like 20 min. in sync with someone :). But yeah, ping me.

pcorbett’s picture

Title: SQL Server 2005 » Progress Update

I wanted to let everyone know that development on this has not ended. A considerable amount of work must be done now to address the various bugs posted here and get things more organized with a CVS/SVN. Hopefully soon we'll have another patch out soon. Anyone interested in helping test/debug is welcome to do so.

antgiant’s picture

subscribing

ricabrantes’s picture

Status: Active » Closed (fixed)

No activity, Closed..