Error message when using create_user() on MSSQL

[ZaLiTHkA]

A user on the CodeIgniter forum (rip_pit) suggested I open a new issue here regarding problem I'm having. Link to original forum post

I didn't find any table setup script for MSSQL in the Ion_Auth download, so I've recreated the tables manually using the MySQL script for reference.

After posting the form, I get the following error:

Error Number:

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

INSERT INTO AESW_users (first_name, last_name, company, username, password, email, ip_address, created_on, last_login, active) VALUES (‘First’, ‘Last’, ‘Company’, ‘first last’, ‘91d926115b5d238dd12fd83e3cb7d8b7014e34ca’, ‘bla@bla.com’, ‘’, 1343032245, 1343032245, 1)

Filename: C:\EasyPHP\www\AESPortal_CI\system\database\DB_driver.php

Line Number: 330

The error log relating to this particular error is as follows:

ERROR - 2012-07-23 15:16:14 --> Severity: Warning --> mssql_query() [function.mssql-query]: message: Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query. (severity 16) C:\EasyPHP\www\AESPortal_CI\system\database\drivers\mssql\mssql_driver.php 143 ERROR - 2012-07-23 15:16:14 --> Severity: Warning --> mssql_query() [function.mssql-query]: Query failed C:\EasyPHP\www\AESPortal_CI\system\database\drivers\mssql\mssql_driver.php 143 ERROR - 2012-07-23 15:16:14 --> Severity: Warning --> mssql_query() [function.mssql-query]: message: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (severity 16) C:\EasyPHP\www\AESPortal_CI\system\database\drivers\mssql\mssql_driver.php 143 ERROR - 2012-07-23 15:16:14 --> Severity: Warning --> mssql_query() [function.mssql-query]: Query failed C:\EasyPHP\www\AESPortal_CI\system\database\drivers\mssql\mssql_driver.php 143 ERROR - 2012-07-23 15:16:14 --> Query error: Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

Not sure if there's a better way to get this info, so here's a copy of the script that SQL Management Studio puts out to create this table:

/****** Object:  Table [dbo].[AESW_users]    Script Date: 08/14/2012 09:57:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AESW_users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [ip_address] [varbinary](16) NOT NULL,
    [username] [varchar](100) NOT NULL,
    [password] [varchar](40) NOT NULL,
    [salt] [varchar](40) NULL,
    [email] [varchar](100) NOT NULL,
    [activation_code] [varchar](40) NULL,
    [forgotten_password_code] [varchar](40) NULL,
    [forgotten_password_time] [datetime] NULL,
    [remember_code] [varchar](40) NULL,
    [created_on] [datetime] NOT NULL,
    [last_login] [datetime] NULL,
    [active] [int] NULL,
    [first_name] [varchar](50) NULL,
    [last_name] [varchar](50) NULL,
    [company] [varchar](100) NULL,
 CONSTRAINT [PK_AESW_users] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AESW_users]  WITH CHECK ADD  CONSTRAINT [users_check_id] CHECK  (([id]>=(0)))
GO

ALTER TABLE [dbo].[AESW_users] CHECK CONSTRAINT [users_check_id]
GO

[ZaLiTHkA]

I just asked a friend who's had a fair amount more experience with SQL than I have, and we've managed to fix this. I was using the incorrect data type for the [ip_address] column, changing from varbinary(16) to varchar(16) seems to have fixed the issue. :)

[benedmunds]

Awesome, and just for future reference the MSSQL setup script is located here: https://github.com/benedmunds/CodeIgniter-Ion-Auth/blob/2/sql/ion_auth.mssql.sql

[ZaLiTHkA]

Thanks Ben, that will definitely come in handy for next time.