I'm struggling a bit with a script i created which generates SQL. Basically, I need to know what newline character to use when using that SQL in MS SQL server 2005.
Code: Select all
$sql = "USE [ampbase_stock] \r
GO \r
/****** Object: Table [dbo].[" . $tables[$i]["name"] . "_TEMPORARY] Script Date: 09/07/2011 17:14:30 ******/ \r
SET ANSI_NULLS ON \r
GO \r
SET QUOTED_IDENTIFIER ON \r
GO \r
SET ANSI_PADDING ON \r
GO \r
CREATE TABLE [dbo].[" . $tables[$i]["name"] . "_TEMPORARY]( \r";
for($j=0;$j<$columns[0]["numrows"];$j++)
{
$col_list .= "," . $columns[$j]["COLUMN_NAME"];
// checks id column
if($columns[$j]["COLUMN_NAME"] == "id")
{
$sql .= "[id] [bigint] IDENTITY(1,1) NOT NULL, \r";
} // if($columns[$j]["COLUMN_NAME"] == "id")
else
{
if($columns[$j]["TYPE_NAME"] == "varchar" or $columns[$j]["TYPE_NAME"] == "nvarchar" )
{
$type = "[varchar](" . $columns[$j]["PRECISION"] . ") COLLATE SQL_Latin1_General_CP1_CI_AS";
}
else
{
$type = "[" . $columns[$j]["TYPE_NAME"] . "]";
}
if($columns[$j]["NULLABLE"] == "0")
{
$null = "NOT NULL";
}
else
{
$null = "NULL";
}
if($columns[$j]["COLUMN_DEF"] <> NULL)
{
$default = $columns[$j]["COLUMN_DEF"];
}
$sql .= "[" . $columns[$j]["COLUMN_NAME"] . "] $type $null $default, \r";
}
} // for($j=0;$j<$columns[0]["numrows"];$j++)
$sql .= "PRIMARY KEY CLUSTERED \r
( \r
[id] ASC \r
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] \r
) ON [PRIMARY] \r
\r
GO \r
SET ANSI_PADDING OFF";
$sql = str_replace("\r","\n",$sql);
$evolution -> quickSql($sql);
[text]USE [ampbase_stock]
GO
/****** Object: Table [dbo].[dms_batch_no_TEMPORARY] Script Date: 09/07/2011 17:14:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dms_batch_no_TEMPORARY](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[created_date] [datetime] NULL ,
[modified_date] [datetime] NULL ,
[disabled_date] [datetime] NULL ,
[module_group_id] [int] NULL ,
[module_id] [int] NULL ,
[cluster_id] [int] NULL ,
[workgroup_id] [int] NULL ,
[location_id] [int] NULL ,
[owner_id] [int] NULL ,
[description] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dms_doc_type_id] [int] NULL ,
[record_status_id] [int] NULL ,
[sort_order] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF[/text]
If i copy paste this into SQL SERVER Management Studio, it works no problem. However, if i use this in php i get the following error:
[text]
SQL Error Thrown: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'GO'.,
SQL Query: USE [ampbase_stock] GO /****** Object: Table [dbo].[dms_batch_no_TEMPORARY] Script Date: 09/07/2011 17:14:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[dms_batch_no_TEMPORARY]( [id] [bigint] IDENTITY(1,1) NOT NULL, [created_date] [datetime] NULL , [modified_date] [datetime] NULL , [disabled_date] [datetime] NULL , [module_group_id] [int] NULL , [module_id] [int] NULL , [cluster_id] [int] NULL , [workgroup_id] [int] NULL , [location_id] [int] NULL , [owner_id] [int] NULL , [description] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [dms_doc_type_id] [int] NULL , [record_status_id] [int] NULL , [sort_order] [int] NULL , PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
[/text]
As you can see it doesn't break it into different lines. I've tried \r \n ; char(10) char(13) etc and no joy.
Any help would be appreciated!