I have a table that is being replicated.. i would like to
increase the size of this column.. Is there a way to do
this without dropping the subscription?
Thanks,
niv
It can be done indirectly but it's not nice! You could add a new column with
the new datatype (sp_repladdcolumn), do an update on the table to populate
the column, then drop the column (sp_repldropcolumn). Do this again to
create the column having the same original name.
Alternatively, as you say, you can drop the publication then recreate from
scratch.
We're hoping that such things will be simpler in SQL Server 2005.
Regards,
Paul Ibison
sql
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Increase column length in all tables with column
Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
Alan
Alan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Alan Z. Scharf a crit :
in
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||look up the table "syscolumns" in books on line.
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
Alan
Alan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Alan Z. Scharf a crit :
in
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||look up the table "syscolumns" in books on line.
Increase column length in all tables with column
Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a crit :
in[vbcol=seagreen]
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a crit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a crit :
in[vbcol=seagreen]
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
Increase column length in all tables with column
Hi,
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a écrit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a écrit :
> > Hi,
> >
> > I there a way to increase the length of the same-named varchar column
in
> > all tables at once in SQL2000?
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
> >
> > Thanks.
> >
> > Alan
> >
> >
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
I there a way to increase the length of the same-named varchar column in
all tables at once in SQL2000?
Thanks.
AlanAlan Z. Scharf a écrit :
> Hi,
> I there a way to increase the length of the same-named varchar column in
> all tables at once in SQL2000?
If you have used a SQL DOMAIN and a "use case" like Power Designer it
will be a children job !
Otherwise you will need to write a long script with many steps...
like this one :
CREATE TABLE T_IMPORT
(IMP_ID INTEGER,
IMP_NOM VARCHAR(16),
IMP_DATE CHAR(6))
INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
-- changing IMP_DATE CHAR(6) to CHAR(8)
BEGIN TRAN
ALTER TABLE T_IMPORT
ADD TMP_IMP_DATE CHAR(6)
UPDATE T_IMPORT
SET TMP_IMP_DATE = IMP_DATE
ALTER TABLE
DROP IMP_DATE
ALTER TABLE
ADD IMP_DATE CHAR(8)
UPDATE T_IMPORT
SET IMP_DATE = TMP_IMP_DATE
ALTER TABLE
DROP TMP_IMP_DATE
COMMIT TRAN
and also if no constraints is on the column.
A +
> Thanks.
> Alan
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks for your reply.
I guess it may be more trouble than it's worth.
Alan
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ePiKc2pTGHA.5332@.tk2msftngp13.phx.gbl...
> Alan Z. Scharf a écrit :
> > Hi,
> >
> > I there a way to increase the length of the same-named varchar column
in
> > all tables at once in SQL2000?
> If you have used a SQL DOMAIN and a "use case" like Power Designer it
> will be a children job !
> Otherwise you will need to write a long script with many steps...
> like this one :
> CREATE TABLE T_IMPORT
> (IMP_ID INTEGER,
> IMP_NOM VARCHAR(16),
> IMP_DATE CHAR(6))
> INSERT INTO T_IMPORT VALUES (254, 'Dupont', '251159')
> INSERT INTO T_IMPORT VALUES (321, 'Durand', '130278')
> INSERT INTO T_IMPORT VALUES (187, 'Dubois', '110401')
> -- changing IMP_DATE CHAR(6) to CHAR(8)
> BEGIN TRAN
> ALTER TABLE T_IMPORT
> ADD TMP_IMP_DATE CHAR(6)
> UPDATE T_IMPORT
> SET TMP_IMP_DATE = IMP_DATE
> ALTER TABLE
> DROP IMP_DATE
> ALTER TABLE
> ADD IMP_DATE CHAR(8)
> UPDATE T_IMPORT
> SET IMP_DATE = TMP_IMP_DATE
> ALTER TABLE
> DROP TMP_IMP_DATE
> COMMIT TRAN
> and also if no constraints is on the column.
> A +
>
> >
> > Thanks.
> >
> > Alan
> >
> >
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||look up the table "syscolumns" in books on line.
Increase # of chars Query Editor when looking at large varchar field
How can I increase the # of chars so that when I copy a varchar(5500) field from Query Editor back into my Query pane, it displays all the characters, and doesn't cut it off?That is a limitation of the query windows.You will have to navigate through the menu Tools > Options > Query results > Result to text > Maximum numbers of characters displayed in one rows (<- increase this here)
HTH, Jens K. Suessmeyer.
http://www.wsqlserver2005.de|||Thank you, I know I increased that, but was looking at a record which only had a short length of chars to begin with so I wasn't seeing that the increase was actually working but good to know I was in the right place at least. Thanks!sql
Monday, March 26, 2012
Incorrect syntax near the keyword 'OR'.
Hi,
I have a stored procedure
CREATE PROCEDURE dbo.Retrieve
(
@.SEARCH_STRING nvarchar(200),
@.COUNT int
)
AS
DECLARE @.STRING_COUNT varchar(3)
DECLARE @.SQL varchar(1000)
SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE ' +
@.SEARCH_STRING + '% )'
EXEC (@.SQL)
The stored procedure is created successfully.
But I get the error when I try to use it: (Retrieve '',10)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.
Thanks
KiranAnswered in .programming. Please don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kiran" <Kiran@.nospam.net> wrote in message
news:O9oiPrX#EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a stored procedure
> CREATE PROCEDURE dbo.Retrieve
> (
> @.SEARCH_STRING nvarchar(200),
> @.COUNT int
> )
> AS
> DECLARE @.STRING_COUNT varchar(3)
> DECLARE @.SQL varchar(1000)
>
> SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
> SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
> WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE '
+
> @.SEARCH_STRING + '% )'
> EXEC (@.SQL)
> The stored procedure is created successfully.
> But I get the error when I try to use it: (Retrieve '',10)
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'OR'.
>
> Thanks
> Kiran
>sql
I have a stored procedure
CREATE PROCEDURE dbo.Retrieve
(
@.SEARCH_STRING nvarchar(200),
@.COUNT int
)
AS
DECLARE @.STRING_COUNT varchar(3)
DECLARE @.SQL varchar(1000)
SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE ' +
@.SEARCH_STRING + '% )'
EXEC (@.SQL)
The stored procedure is created successfully.
But I get the error when I try to use it: (Retrieve '',10)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.
Thanks
KiranAnswered in .programming. Please don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kiran" <Kiran@.nospam.net> wrote in message
news:O9oiPrX#EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a stored procedure
> CREATE PROCEDURE dbo.Retrieve
> (
> @.SEARCH_STRING nvarchar(200),
> @.COUNT int
> )
> AS
> DECLARE @.STRING_COUNT varchar(3)
> DECLARE @.SQL varchar(1000)
>
> SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
> SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
> WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE '
+
> @.SEARCH_STRING + '% )'
> EXEC (@.SQL)
> The stored procedure is created successfully.
> But I get the error when I try to use it: (Retrieve '',10)
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'OR'.
>
> Thanks
> Kiran
>sql
Friday, March 9, 2012
incomprshensible code
Hello
Create an empty table. The table has two columns, one varying length
and one integer.
We are going to INSERT new rows(s) into the table, but only assign
data to the column named x.
The source of data for the INSERT is the result set of a SELECT. The
SELECT does not read any table, it just returns one row. The one
column in the result set is a character string made up by
concatenating (using the + operator) three other strings. The string
in the middle is created using the CHAR function. The CHAR function
converts a number ranging from 0 to 255 into an ASCII character. The
ASCII character that corresponds to 1 is not a printable character;
for me the results look like an empty square.
This returns the one row of the table.
x y
---------------- ----
bob
fred NULL
The code is correct, though it looks like a simple example of
something rather than anything of actual use.
>
>
>
Create an empty table. The table has two columns, one varying length
and one integer.
>
>
We are going to INSERT new rows(s) into the table, but only assign
data to the column named x.
>
>
The source of data for the INSERT is the result set of a SELECT. The
SELECT does not read any table, it just returns one row. The one
column in the result set is a character string made up by
concatenating (using the + operator) three other strings. The string
in the middle is created using the CHAR function. The CHAR function
converts a number ranging from 0 to 255 into an ASCII character. The
ASCII character that corresponds to 1 is not a printable character;
for me the results look like an empty square.
>
>
This returns the one row of the table.
x y
---------------- ----
bob
fred NULL
>
>
The code is correct, though it looks like a simple example of
something rather than anything of actual use.
>
Roy Harvey
Beacon Falls, CT
Thank You very much.
My friend gave me stored procedure, and I understand this code:
create table t (x varchar(50), y int)
insert into t (x)
select 'bob' + char(1) + 'fred'
select * from t
Please explain me and corect this code.
AndrzejOn Wed, 14 Nov 2007 11:05:00 -0800, J?dru? <anowacki.pila@.interia.pl>
wrote:
Quote:
Originally Posted by
>Hello
>
>My friend gave me stored procedure, and I understand this code:
>
>create table t (x varchar(50), y int)
Create an empty table. The table has two columns, one varying length
and one integer.
Quote:
Originally Posted by
>insert into t (x)
We are going to INSERT new rows(s) into the table, but only assign
data to the column named x.
Quote:
Originally Posted by
>select 'bob' + char(1) + 'fred'
The source of data for the INSERT is the result set of a SELECT. The
SELECT does not read any table, it just returns one row. The one
column in the result set is a character string made up by
concatenating (using the + operator) three other strings. The string
in the middle is created using the CHAR function. The CHAR function
converts a number ranging from 0 to 255 into an ASCII character. The
ASCII character that corresponds to 1 is not a printable character;
for me the results look like an empty square.
Quote:
Originally Posted by
>select * from t
This returns the one row of the table.
x y
---------------- ----
bob
fred NULL
Quote:
Originally Posted by
>Please explain me and corect this code.
The code is correct, though it looks like a simple example of
something rather than anything of actual use.
Roy Harvey
Beacon Falls, CT|||On 14 Lis, 21:03, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
Quote:
Originally Posted by
On Wed, 14 Nov 2007 11:05:00 -0800, J?dru? <anowacki.p...@.interia.pl>
wrote:
>
Quote:
Originally Posted by
Hello
>
Quote:
Originally Posted by
My friend gave me stored procedure, and I understand this code:
>
Quote:
Originally Posted by
create table t (x varchar(50), y int)
>
Create an empty table. The table has two columns, one varying length
and one integer.
>
Quote:
Originally Posted by
insert into t (x)
>
We are going to INSERT new rows(s) into the table, but only assign
data to the column named x.
>
Quote:
Originally Posted by
select 'bob' + char(1) + 'fred'
>
The source of data for the INSERT is the result set of a SELECT. The
SELECT does not read any table, it just returns one row. The one
column in the result set is a character string made up by
concatenating (using the + operator) three other strings. The string
in the middle is created using the CHAR function. The CHAR function
converts a number ranging from 0 to 255 into an ASCII character. The
ASCII character that corresponds to 1 is not a printable character;
for me the results look like an empty square.
>
Quote:
Originally Posted by
select * from t
>
This returns the one row of the table.
x y
---------------- ----
bob
fred NULL
>
Quote:
Originally Posted by
Please explain me and corect this code.
>
The code is correct, though it looks like a simple example of
something rather than anything of actual use.
>
Roy Harvey
Beacon Falls, CT
Thank You very much.
Wednesday, March 7, 2012
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much 
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Subscribe to:
Posts (Atom)