Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
Chris
Hi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Tuesday, March 27, 2012
Converting computed columns to fixed
Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') =
1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John
Converting computed columns to fixed
Hi,
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John
I'm using sql server 2000 sp4, and wish to convert a computed column
into a 'fixed' column. I have a script which does this:
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1)
BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL
END
GO
BEGIN TRANSACTION
IF N'MyColumnExpanded' IN (SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'MyTable')
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT
GO
However, this script also needs to cater for the case where the column
is already correctly fixed. In that case I get an error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyColumnExpanded'.
It seems to be the UPDATE line that's causing the problem. Even though
the block shouldn't execute in this case, query analyzer still seems
to be parsing it and reporting problems.
So is there a way to achieve this operation without errors?
Thanks,
ChrisHi Chris
On Feb 1, 12:39 pm, chris.chatfi...@.gmail.com wrote:
> Hi,
> However, this script also needs to cater for the case where the column
> is already correctly fixed. In that case I get an error:
> Server: Msg 207, Level 16, State 1, Line 1 Invalid column name
> 'MyColumnExpanded'.
>
> So is there a way to achieve this operation without errors?
> Thanks,
> Chris
This works for me on SQL 2000 SP4 + hotfix 2187
CREATE TABLE MyTable ( Number int not null default 1,
Quantity int not null default 1,
MyColumn AS Number * Quantity )
GO
INSERT INTO Mytable ( Number, Quantity ) SELECT 3, 3 UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3 UNION ALL SELECT 4, 5 UNION ALL SELECT 4, 6 UNION ALL
SELECT 4, 7
GO
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
(6 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'MyColumn'
*/
SELECT * FROM MyTable
GO
IF (COLUMNPROPERTY(OBJECT_ID('MyTable'), 'MyColumn', 'IsComputed') = 1) AND
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' ) BEGIN
ALTER TABLE [MyTable] ADD [MyColumnExpanded] int NULL END GO
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =N'MyTable' AND COLUMN_NAME = N'MyColumnExpanded' )
BEGIN
UPDATE [MyTable] SET [MyColumnExpanded] = [MyColumn]
ALTER TABLE [MyTable] DROP COLUMN [MyColumn]
EXEC sp_rename 'MyTable.MyColumnExpanded', 'MyColumn', 'COLUMN'
END
COMMIT TRANSACTION
GO
/*
The command(s) completed successfully.
*/
SELECT * FROM MyTable
GO
John
Thursday, March 8, 2012
convert SQLServer Expresss database to sqlserver2000
I have some question about SQLServer Expresss.
1. I want attach a SQLServer Expresss database to sqlserver2005.is it possible? How?
2. Can I get script by SQLServer Expresss?
3. And my important question is: How can I convert a SQLServer Expresss database to sqlserver2000?
Please help me.
Thanks in advance.
If both Express and the other edition are in the same network, register the Express to the full edition and use the Backup and Restore Wizard to Backup the database in Express then Restore in the full edition. You could try auto attach which may not work because regular attach require both MDF(Microsoft data file) and LDF(log data file). If you did not create the database with .SQL code or the GUI in Management Studio in Express then you may not have the LDF.
http://msdn2.microsoft.com/en-us/library/ms190631.aspx
Now to 2000 create a blank database in 2000 and then use DTS in 2000 to move your data and tables to 2000 because there are differences. I have posted a FAQ of how to move databases in the thread below. Hope this helps.
http://forums.asp.net/thread/1454694.aspx
|||Hello
as you said I tried to Backup the database in Express but I couldn't Restore it in full edition .
I get this errore: Error 3205
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)
What's the problem? :(
Thanks for your helping
|||
The Backup and Restore wizard have nothing to do with .NET you are supposed to do it in Management Studio, so I have posted a FAQ which includes a Backup free chapter of a book use it. Hope this helps.
http://forums.asp.net/thread/1454694.aspx
Wednesday, March 7, 2012
Convert SQL 2000 Script to SQL Server 2005
Help me convert this script to SQL Server 2005:
use master
go
exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
go
grant all on dbo.xp_getsequence to public
go
EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.newid INT
SELECT @.seqname = db_name() + '..' + @.seqname
EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
RETURN(@.newid)
END
go
grant all on system_function_schema.fn_getsequence to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF @.inputvalue is null
SELECT @.rtnvalue='.'
ELSE
BEGIN
IF LTRIM(RTRIM(@.inputvalue))=''
SELECT @.rtnvalue='.'
ELSE
SELECT @.rtnvalue=@.inputvalue
END
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_blank2dot to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF LTRIM(RTRIM(@.inputvalue))='.'
SELECT @.rtnvalue=''
ELSE
SELECT @.rtnvalue=@.inputvalue
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_dot2blank to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat2 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
VARCHAR(500),@.str3 VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat3 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
sql_variant)
RETURNS sql_variant AS
BEGIN
RETURN (isnull(@.inputvalue,@.replacement))
END
go
grant all on system_function_schema.fn_nvl to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_tochar(@.inputvalue sql_variant)
RETURNS VARCHAR(500) AS
BEGIN
RETURN CAST(@.inputvalue AS VARCHAR)
END
go
grant all on system_function_schema.fn_tochar to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
VARCHAR(10),@.length INT)
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.newseqid VARCHAR(50)
SELECT @.prefix = coalesce(@.prefix, '')
SELECT @.length = coalesce(@.length, 0)
SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
- len(@.newseqid)) + @.newseqid
RETURN(@.newseqid)
END
go
grant all on system_function_schema.fn_getfullseq to public
go
CREATE PROCEDURE ReloadG
AS
EXEC xp_cmdshell 'START Command ', no_output
go
grant execute on master..ReloadG to public
goM P
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
What if the user provides NULL for one of the parameters?
Visit www.projectdmx.com/tsql/
"M P" <mponteres@.gmail.com> wrote in message
news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> Help me convert this script to SQL Server 2005:
> use master
> go
> exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> go
> grant all on dbo.xp_getsequence to public
> go
> EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> RECONFIGURE WITH OVERRIDE
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> RETURNS INT AS
> BEGIN
> DECLARE @.newid INT
> SELECT @.seqname = db_name() + '..' + @.seqname
> EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> RETURN(@.newid)
> END
> go
> grant all on system_function_schema.fn_getsequence to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF @.inputvalue is null
> SELECT @.rtnvalue='.'
> ELSE
> BEGIN
> IF LTRIM(RTRIM(@.inputvalue))=''
> SELECT @.rtnvalue='.'
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> END
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_blank2dot to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF LTRIM(RTRIM(@.inputvalue))='.'
> SELECT @.rtnvalue=''
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_dot2blank to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat2 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> VARCHAR(500),@.str3 VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat3 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> sql_variant)
> RETURNS sql_variant AS
> BEGIN
> RETURN (isnull(@.inputvalue,@.replacement))
> END
> go
> grant all on system_function_schema.fn_nvl to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_tochar(@.inputvalue sql_variant)
> RETURNS VARCHAR(500) AS
> BEGIN
> RETURN CAST(@.inputvalue AS VARCHAR)
> END
> go
> grant all on system_function_schema.fn_tochar to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> VARCHAR(10),@.length INT)
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.newseqid VARCHAR(50)
> SELECT @.prefix = coalesce(@.prefix, '')
> SELECT @.length = coalesce(@.length, 0)
> SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> - len(@.newseqid)) + @.newseqid
> RETURN(@.newseqid)
> END
> go
> grant all on system_function_schema.fn_getfullseq to public
> go
> CREATE PROCEDURE ReloadG
> AS
> EXEC xp_cmdshell 'START Command ', no_output
> go
> grant execute on master..ReloadG to public
> go
>|||I am not familiar with the script. I have a task in checking for possibility
of this script to be used on SQL Server 2005. Can you help?
"Uri Dimant" wrote:
> M P
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> What if the user provides NULL for one of the parameters?
> Visit www.projectdmx.com/tsql/
>
> "M P" <mponteres@.gmail.com> wrote in message
> news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> > Help me convert this script to SQL Server 2005:
> >
> > use master
> > go
> > exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> > go
> > grant all on dbo.xp_getsequence to public
> > go
> >
> > EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> > RECONFIGURE WITH OVERRIDE
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.newid INT
> > SELECT @.seqname = db_name() + '..' + @.seqname
> > EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> > RETURN(@.newid)
> > END
> > go
> > grant all on system_function_schema.fn_getsequence to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF @.inputvalue is null
> > SELECT @.rtnvalue='.'
> > ELSE
> > BEGIN
> > IF LTRIM(RTRIM(@.inputvalue))=''
> > SELECT @.rtnvalue='.'
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > END
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_blank2dot to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF LTRIM(RTRIM(@.inputvalue))='.'
> > SELECT @.rtnvalue=''
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_dot2blank to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat2 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500),@.str3 VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat3 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> > sql_variant)
> > RETURNS sql_variant AS
> > BEGIN
> > RETURN (isnull(@.inputvalue,@.replacement))
> > END
> > go
> > grant all on system_function_schema.fn_nvl to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_tochar(@.inputvalue sql_variant)
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > RETURN CAST(@.inputvalue AS VARCHAR)
> > END
> > go
> > grant all on system_function_schema.fn_tochar to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> > VARCHAR(10),@.length INT)
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.newseqid VARCHAR(50)
> > SELECT @.prefix = coalesce(@.prefix, '')
> > SELECT @.length = coalesce(@.length, 0)
> > SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> > SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> > - len(@.newseqid)) + @.newseqid
> >
> > RETURN(@.newseqid)
> > END
> > go
> > grant all on system_function_schema.fn_getfullseq to public
> > go
> >
> > CREATE PROCEDURE ReloadG
> > AS
> > EXEC xp_cmdshell 'START Command ', no_output
> > go
> > grant execute on master..ReloadG to public
> > go
> >
>
>
use master
go
exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
go
grant all on dbo.xp_getsequence to public
go
EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.newid INT
SELECT @.seqname = db_name() + '..' + @.seqname
EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
RETURN(@.newid)
END
go
grant all on system_function_schema.fn_getsequence to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF @.inputvalue is null
SELECT @.rtnvalue='.'
ELSE
BEGIN
IF LTRIM(RTRIM(@.inputvalue))=''
SELECT @.rtnvalue='.'
ELSE
SELECT @.rtnvalue=@.inputvalue
END
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_blank2dot to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF LTRIM(RTRIM(@.inputvalue))='.'
SELECT @.rtnvalue=''
ELSE
SELECT @.rtnvalue=@.inputvalue
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_dot2blank to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat2 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
VARCHAR(500),@.str3 VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat3 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
sql_variant)
RETURNS sql_variant AS
BEGIN
RETURN (isnull(@.inputvalue,@.replacement))
END
go
grant all on system_function_schema.fn_nvl to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_tochar(@.inputvalue sql_variant)
RETURNS VARCHAR(500) AS
BEGIN
RETURN CAST(@.inputvalue AS VARCHAR)
END
go
grant all on system_function_schema.fn_tochar to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
VARCHAR(10),@.length INT)
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.newseqid VARCHAR(50)
SELECT @.prefix = coalesce(@.prefix, '')
SELECT @.length = coalesce(@.length, 0)
SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
- len(@.newseqid)) + @.newseqid
RETURN(@.newseqid)
END
go
grant all on system_function_schema.fn_getfullseq to public
go
CREATE PROCEDURE ReloadG
AS
EXEC xp_cmdshell 'START Command ', no_output
go
grant execute on master..ReloadG to public
goM P
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
What if the user provides NULL for one of the parameters?
Visit www.projectdmx.com/tsql/
"M P" <mponteres@.gmail.com> wrote in message
news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> Help me convert this script to SQL Server 2005:
> use master
> go
> exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> go
> grant all on dbo.xp_getsequence to public
> go
> EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> RECONFIGURE WITH OVERRIDE
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> RETURNS INT AS
> BEGIN
> DECLARE @.newid INT
> SELECT @.seqname = db_name() + '..' + @.seqname
> EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> RETURN(@.newid)
> END
> go
> grant all on system_function_schema.fn_getsequence to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF @.inputvalue is null
> SELECT @.rtnvalue='.'
> ELSE
> BEGIN
> IF LTRIM(RTRIM(@.inputvalue))=''
> SELECT @.rtnvalue='.'
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> END
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_blank2dot to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF LTRIM(RTRIM(@.inputvalue))='.'
> SELECT @.rtnvalue=''
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_dot2blank to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat2 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> VARCHAR(500),@.str3 VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat3 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> sql_variant)
> RETURNS sql_variant AS
> BEGIN
> RETURN (isnull(@.inputvalue,@.replacement))
> END
> go
> grant all on system_function_schema.fn_nvl to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_tochar(@.inputvalue sql_variant)
> RETURNS VARCHAR(500) AS
> BEGIN
> RETURN CAST(@.inputvalue AS VARCHAR)
> END
> go
> grant all on system_function_schema.fn_tochar to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> VARCHAR(10),@.length INT)
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.newseqid VARCHAR(50)
> SELECT @.prefix = coalesce(@.prefix, '')
> SELECT @.length = coalesce(@.length, 0)
> SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> - len(@.newseqid)) + @.newseqid
> RETURN(@.newseqid)
> END
> go
> grant all on system_function_schema.fn_getfullseq to public
> go
> CREATE PROCEDURE ReloadG
> AS
> EXEC xp_cmdshell 'START Command ', no_output
> go
> grant execute on master..ReloadG to public
> go
>|||I am not familiar with the script. I have a task in checking for possibility
of this script to be used on SQL Server 2005. Can you help?
"Uri Dimant" wrote:
> M P
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> What if the user provides NULL for one of the parameters?
> Visit www.projectdmx.com/tsql/
>
> "M P" <mponteres@.gmail.com> wrote in message
> news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> > Help me convert this script to SQL Server 2005:
> >
> > use master
> > go
> > exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> > go
> > grant all on dbo.xp_getsequence to public
> > go
> >
> > EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> > RECONFIGURE WITH OVERRIDE
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.newid INT
> > SELECT @.seqname = db_name() + '..' + @.seqname
> > EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> > RETURN(@.newid)
> > END
> > go
> > grant all on system_function_schema.fn_getsequence to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF @.inputvalue is null
> > SELECT @.rtnvalue='.'
> > ELSE
> > BEGIN
> > IF LTRIM(RTRIM(@.inputvalue))=''
> > SELECT @.rtnvalue='.'
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > END
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_blank2dot to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF LTRIM(RTRIM(@.inputvalue))='.'
> > SELECT @.rtnvalue=''
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_dot2blank to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat2 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500),@.str3 VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat3 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> > sql_variant)
> > RETURNS sql_variant AS
> > BEGIN
> > RETURN (isnull(@.inputvalue,@.replacement))
> > END
> > go
> > grant all on system_function_schema.fn_nvl to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_tochar(@.inputvalue sql_variant)
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > RETURN CAST(@.inputvalue AS VARCHAR)
> > END
> > go
> > grant all on system_function_schema.fn_tochar to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> > VARCHAR(10),@.length INT)
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.newseqid VARCHAR(50)
> > SELECT @.prefix = coalesce(@.prefix, '')
> > SELECT @.length = coalesce(@.length, 0)
> > SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> > SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> > - len(@.newseqid)) + @.newseqid
> >
> > RETURN(@.newseqid)
> > END
> > go
> > grant all on system_function_schema.fn_getfullseq to public
> > go
> >
> > CREATE PROCEDURE ReloadG
> > AS
> > EXEC xp_cmdshell 'START Command ', no_output
> > go
> > grant execute on master..ReloadG to public
> > go
> >
>
>
Friday, February 24, 2012
Convert MySQL script to SQL Server script
Hi there,
I have a few MySQL scripts that I need to run in SQL Server. However the
syntax is just slightly different and I was wondering if there is a tool out
there that can quickly convert these scripts so that they will work with SQL
Server?
Anyone have any ideas (other than manually converting them)?
Thanks,
Wes
Hi,
There is one possible way how to do it, however, it's not as direct as
you may wish.
Let me outline what to do. You will have to run the scripts "above"
MySQL database and reverse engineer them in CASE Studio 2 (a database
modeling tool) that allows you to do the conversion to MSSQL in just a
few clicks. Then you can generate a script compatible with MSSQL.
You can try to do it in the CASE Studio 2 demo version, which is for
free >
http://www.casestudio.com/enu/download.aspx.
To learn more what this product is about, please feel free to visit:
http://www.casestudio.com/enu/default.aspx.
Finally, I think this could help you until somebody else gives you
better advice.
Good luck.
Vladka
Wes <Wes@.discussions.microsoft.com> wrote in message news:<2C048E65-7A06-49C1-BE96-8A5F3716E4EF@.microsoft.com>...
> Hi there,
> I have a few MySQL scripts that I need to run in SQL Server. However the
> syntax is just slightly different and I was wondering if there is a tool out
> there that can quickly convert these scripts so that they will work with SQL
> Server?
> Anyone have any ideas (other than manually converting them)?
> Thanks,
> Wes
|||Hi Vladka,
I downloaded, installed and tried CASE Studio 2, but to no advantage. It
will not allow me to import or open an existing script. I tried everything I
could think of, but could not get it to allow me to use my existing script.
Thanks for trying though!
Wes
"Vaclav Frolik" wrote:
> Hi,
> There is one possible way how to do it, however, it's not as direct as
> you may wish.
> Let me outline what to do. You will have to run the scripts "above"
> MySQL database and reverse engineer them in CASE Studio 2 (a database
> modeling tool) that allows you to do the conversion to MSSQL in just a
> few clicks. Then you can generate a script compatible with MSSQL.
> You can try to do it in the CASE Studio 2 demo version, which is for
> free >
> http://www.casestudio.com/enu/download.aspx.
> To learn more what this product is about, please feel free to visit:
> http://www.casestudio.com/enu/default.aspx.
> Finally, I think this could help you until somebody else gives you
> better advice.
> Good luck.
> Vladka
>
> Wes <Wes@.discussions.microsoft.com> wrote in message news:<2C048E65-7A06-49C1-BE96-8A5F3716E4EF@.microsoft.com>...
>
|||Hi Wes,
I'm sorry for the inaccurate formulation. You're right, CASE Studio 2
(CS2) can load only the physical database, not SQL scripts. (As I
wrote - it wasn't a direct way... ) However, I'd like to specify my
previous message.
1.Please, try to generate the script physically in MySQL database.
2.Then reverse engineer this database in CS2 again. For the details
concerning reverse engineering, please have a look at the Help file
Contents folder > Reverse engineering.
3.Furthermore, you will have to do the database conversion. Please, see
the Help file of CS2 > Contents
folder > Model conversion for the details.
4.Finally, you'll be allowed to generate the scripts of the converted
database automatically. Please, see the Help file again > Contents
folder > Databases > MS SQL > Generating SQL scripts.
Well, this will certainly help you. Good luck!
Here are the needed links once again:
http://www.casestudio.com/enu/default.aspx
http://www.casestudio.com/enu/download.aspx
Let me also add a link where you will find some useful movies that could
help you: http://www.casestudio.com/enu/dmovies.aspx
I look forward to your reply.
Vladka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I have a few MySQL scripts that I need to run in SQL Server. However the
syntax is just slightly different and I was wondering if there is a tool out
there that can quickly convert these scripts so that they will work with SQL
Server?
Anyone have any ideas (other than manually converting them)?
Thanks,
Wes
Hi,
There is one possible way how to do it, however, it's not as direct as
you may wish.
Let me outline what to do. You will have to run the scripts "above"
MySQL database and reverse engineer them in CASE Studio 2 (a database
modeling tool) that allows you to do the conversion to MSSQL in just a
few clicks. Then you can generate a script compatible with MSSQL.
You can try to do it in the CASE Studio 2 demo version, which is for
free >
http://www.casestudio.com/enu/download.aspx.
To learn more what this product is about, please feel free to visit:
http://www.casestudio.com/enu/default.aspx.
Finally, I think this could help you until somebody else gives you
better advice.
Good luck.
Vladka
Wes <Wes@.discussions.microsoft.com> wrote in message news:<2C048E65-7A06-49C1-BE96-8A5F3716E4EF@.microsoft.com>...
> Hi there,
> I have a few MySQL scripts that I need to run in SQL Server. However the
> syntax is just slightly different and I was wondering if there is a tool out
> there that can quickly convert these scripts so that they will work with SQL
> Server?
> Anyone have any ideas (other than manually converting them)?
> Thanks,
> Wes
|||Hi Vladka,
I downloaded, installed and tried CASE Studio 2, but to no advantage. It
will not allow me to import or open an existing script. I tried everything I
could think of, but could not get it to allow me to use my existing script.
Thanks for trying though!
Wes
"Vaclav Frolik" wrote:
> Hi,
> There is one possible way how to do it, however, it's not as direct as
> you may wish.
> Let me outline what to do. You will have to run the scripts "above"
> MySQL database and reverse engineer them in CASE Studio 2 (a database
> modeling tool) that allows you to do the conversion to MSSQL in just a
> few clicks. Then you can generate a script compatible with MSSQL.
> You can try to do it in the CASE Studio 2 demo version, which is for
> free >
> http://www.casestudio.com/enu/download.aspx.
> To learn more what this product is about, please feel free to visit:
> http://www.casestudio.com/enu/default.aspx.
> Finally, I think this could help you until somebody else gives you
> better advice.
> Good luck.
> Vladka
>
> Wes <Wes@.discussions.microsoft.com> wrote in message news:<2C048E65-7A06-49C1-BE96-8A5F3716E4EF@.microsoft.com>...
>
|||Hi Wes,
I'm sorry for the inaccurate formulation. You're right, CASE Studio 2
(CS2) can load only the physical database, not SQL scripts. (As I
wrote - it wasn't a direct way... ) However, I'd like to specify my
previous message.
1.Please, try to generate the script physically in MySQL database.
2.Then reverse engineer this database in CS2 again. For the details
concerning reverse engineering, please have a look at the Help file
Contents folder > Reverse engineering.
3.Furthermore, you will have to do the database conversion. Please, see
the Help file of CS2 > Contents
folder > Model conversion for the details.
4.Finally, you'll be allowed to generate the scripts of the converted
database automatically. Please, see the Help file again > Contents
folder > Databases > MS SQL > Generating SQL scripts.
Well, this will certainly help you. Good luck!
Here are the needed links once again:
http://www.casestudio.com/enu/default.aspx
http://www.casestudio.com/enu/download.aspx
Let me also add a link where you will find some useful movies that could
help you: http://www.casestudio.com/enu/dmovies.aspx
I look forward to your reply.
Vladka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Convert MySQL script to SQL Server script
Hi there,
I have a few MySQL scripts that I need to run in SQL Server. However the
syntax is just slightly different and I was wondering if there is a tool out
there that can quickly convert these scripts so that they will work with SQL
Server?
Anyone have any ideas (other than manually converting them)?
Thanks,
Wes"Wes Graves" <wesgraves@.hotmail.com> wrote in message news:<XSbfd.27529$Pl.741@.pd7tw1no>...
> Hi there,
> I have a few MySQL scripts that I need to run in SQL Server. However the
> syntax is just slightly different and I was wondering if there is a tool out
> there that can quickly convert these scripts so that they will work with SQL
> Server?
> Anyone have any ideas (other than manually converting them)?
> Thanks,
> Wes
I think, you can start mysql in ANSI mode and generate the scripts
with this standard which is ready to use in SQL server with no
modification. see the following page for more details:
http://dev.mysql.com/doc/mysql/en/ANSI_mode.html|||Thanks, I'll give that a try.
Wes
"Muharram Mansoorizadeh" <muharram_m@.yahoo.com> wrote in message
news:a85d0c0e.0410261616.2ce82b84@.posting.google.c om...
> "Wes Graves" <wesgraves@.hotmail.com> wrote in message
> news:<XSbfd.27529$Pl.741@.pd7tw1no>...
>> Hi there,
>>
>> I have a few MySQL scripts that I need to run in SQL Server. However the
>> syntax is just slightly different and I was wondering if there is a tool
>> out
>> there that can quickly convert these scripts so that they will work with
>> SQL
>> Server?
>>
>> Anyone have any ideas (other than manually converting them)?
>>
>> Thanks,
>>
>> Wes
> I think, you can start mysql in ANSI mode and generate the scripts
> with this standard which is ready to use in SQL server with no
> modification. see the following page for more details:
> http://dev.mysql.com/doc/mysql/en/ANSI_mode.html
Friday, February 10, 2012
Convert database from sql 2005 to sql 2000
Hi,
Can anybody tell me that how to convert database from sql server 2005 to sql server 2000. I tried with generated script and in that i selected the option for sql server 2000 but still while trying to run that script it is throwing an error. the error is:
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '('.
plz help.
Thanks,
Poonam
Hi,which service Pack are you using, there had been issues in the RTM built which were fixed in SP1. (Unfortunately I can′t remember any details about that). Could you please post the non-working snippet here ? If you are not sure where the error occurs (as the parser will begin the new numbering after each GO statement), try to execute the script step-by-Step to narrow down the problem.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
convert data file type from unix to pc using stored procedure?
Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht :
....
Do While NOT oInFile.AtEndOfStream
oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)
Loop
....
After that, I will use a BULK INSERT to input data to ms sql.
I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!!
I guess it's because the file is not being converted into a correct format??
Can you give more information? e.g.: sample text used in BULK INSERT, BULK INSERT command you're using, and the schema of the destination table.
Subscribe to:
Comments (Atom)