Insert/Update with dynamic database name

Hi

My problem is as follows:

I need to transmit data between two databases on the same server, but I
have to use dynamic database names (they must be configurable). For
example I need to achive sth like that:

insert into [database1].[dbo].[table1]
(select columns from [table2])

when database1 is not known at implementation stage.

I know I can use EXEC @.t_sql_code, but I wonder if there is any other
way? (OPENROWSET doesn't seem to suit my needs)

Thanks in advance

Amfiamfi1 (amfi1@.poczta.fm) writes:
> I need to transmit data between two databases on the same server, but I
> have to use dynamic database names (they must be configurable). For
> example I need to achive sth like that:
> insert into [database1].[dbo].[table1]
> (select columns from [table2])
> when database1 is not known at implementation stage.
> I know I can use EXEC @.t_sql_code, but I wonder if there is any other
> way? (OPENROWSET doesn't seem to suit my needs)

Well, one thing you can do is to use stored procedures:

SELECT @.spname = @.srcdb + '.dbo.getmydata'
INSERT table1 (...)
EXEC @.spname

Using a dynamic SP name is not as messy as have all the code in dynamic
SQL.

Now, your example indicates that it is the target database that is
unknown to you, in which case my suggestion does not work.

A faint possibility is to set up a linked server that loops back to
your own server. The target database would then be in the connection
string. You could thus say:

INSERT MYSERVER..dbo.table (...)
SELECT...

and you would set up the linked server as you need it. But there is
an overhead for the loopback. And I must that I have not tested if
it actually works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Insert unique rows in temp table

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.

MemberID + Month + Year should ne unique in Temp table

I don't think what you are doing is valid because a local temp table scope is very limited, but if it valid it will be covered in the link below by one of the best minds in T-SQL. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Insert Trigger How to work

Hi All!

I have a table which has fields |person_id| AND |fullname|

I would like to create and insert trigger so that no duplicate names shoudl be inserted, I tryed Following but am geeting confused Where I am going Wrong

I need to take an Input from user to this trigger as well how is it possiible??

create trigger ins_per
on persons
for insert as
Declare @.p_name varchar(45)
select * from persons where full_name=@.p_name
if @.@.rowcount>0
Begin
Print
'Sorry'
ROLLBACK TRANSACTION
EndA unique constraint does the trick!

Alter table x add constraint constraint_name unique nonclustered (Column 1,Column 2)

The combination column1 + column2 must be unique.

Hope it helps!sql

insert record into field names that contain brackets

Hi there,
I am having difficuly inserting a record into the database. I came to
realize that it may be because of my column name that contains brackets. It
inserts just fine with other fields that are the same data type but it stops
at the bracketed field name. How can I get around this? I can change the
column name to exclude any spaces because it's a pre-defined format. I tried
bracketing the field name within my code so it's the same as in the design
view but it still doesn't work. Can someone help me please. Here's my code
below:
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
Thanks!
luv2travel wrote:
> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains
> brackets. It inserts just fine with other fields that are the same
> data type but it stops at the bracketed field name. How can I get
> around this? I can change the column name to exclude any spaces
> because it's a pre-defined format. I tried bracketing the field name
> within my code so it's the same as in the design view but it still
> doesn't work. Can someone help me please. Here's my code below:
> ----
Can you post your table DDL.
In the meantime, try this:
create table ugly_columns (
col_normal int not null,
"col_ug]y" int not null )
insert into ugly_columns (
col_normal,
"col_ug]y" )
values (
1,
2 )
Select * from ugly_columns
David Gugick
Imceda Software
www.imceda.com
|||This is what I imagine the DDL would look like behind the scenes but I'm not
sure how SQL Server handles attribute names that contain spaces. All I know
is that when it appears in the design view, it's contained within square
brackets.
CREATE TABLE EMPLOYEES (
NAME varchar(20),
ADDRESS varchar(50),
EMAIL varchar(50),
[NUM CARS] int
)
I tried your suggestion using insert and it worked just fine with the
brackets. The only problem with using Insert is that I have several
attributes I would need to concatenate with their corresponding values. The
method I used makes it easier for me to query but somehow it doesn't like the
brackets. Any ideas why? Thanks.
"David Gugick" wrote:

> luv2travel wrote:
> Can you post your table DDL.
> In the meantime, try this:
> create table ugly_columns (
> col_normal int not null,
> "col_ug]y" int not null )
> insert into ugly_columns (
> col_normal,
> "col_ug]y" )
> values (
> 1,
> 2 )
> Select * from ugly_columns
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||luv2travel wrote:
> This is what I imagine the DDL would look like behind the scenes but
> I'm not sure how SQL Server handles attribute names that contain
> spaces. All I know is that when it appears in the design view, it's
> contained within square brackets.
> CREATE TABLE EMPLOYEES (
> NAME varchar(20),
> ADDRESS varchar(50),
> EMAIL varchar(50),
> [NUM CARS] int
> )
Brackets are for object names like tables and procedures unless you have
quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
Exec [dbo].[My Proc Has a Poor Name]
or
Exec "dbo"."My Proc Has a Poor Name"
Select * from [Hyphens-In-This-Table]
or
Select * from "Hyphens-In-This-Table"
From BOL:
The body of the identifier can contain any combination of characters in
the current code page except the delimiting characters themselves.
Why is there a bracket in the column name in the first place?
David Gugick
Imceda Software
www.imceda.com
|||If you open Enterprise Manager and enter a column name that contains a space
in Design View, SQL Server will automatically embrace it with brackets.
There's no way getting around it.
"David Gugick" wrote:

> luv2travel wrote:
>
> Brackets are for object names like tables and procedures unless you have
> quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
> Exec [dbo].[My Proc Has a Poor Name]
> or
> Exec "dbo"."My Proc Has a Poor Name"
> Select * from [Hyphens-In-This-Table]
> or
> Select * from "Hyphens-In-This-Table"
> From BOL:
> The body of the identifier can contain any combination of characters in
> the current code page except the delimiting characters themselves.
> Why is there a bracket in the column name in the first place?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||luv2travel wrote:
> If you open Enterprise Manager and enter a column name that contains
> a space in Design View, SQL Server will automatically embrace it with
> brackets. There's no way getting around it.
>
But in that case the brackets are not in the column, they are just the
delimiters used in the UI to prevent SQL Server throwing an error.
If you run sp_help <table>, you won't see brackets:
For example:
create table ugly_columns (
col_normal int not null,
[col space] int not null )
sp_help ugly_columns
Column_name
col_normal
col space
When you access the column from your code, you need to delimite the
column using brackets or double quotes if quoted identifiers are on.
I don't understand your code well enough to know why you are having a
problem. What is the error you are receiving?
David Gugick
Imceda Software
www.imceda.com
|||Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cindy
Mink', '4') it worked just fine but if I leave out the brackets for Num Cars,
it throws a run-time error. So I tried applying the same thing to the code
below by preserving the brackets and it threw an unhandled exception. Why
does it work one way and not the other? Is something wrong with the code
below?
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
"luv2travel" wrote:

> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains brackets. It
> inserts just fine with other fields that are the same data type but it stops
> at the bracketed field name. How can I get around this? I can change the
> column name to exclude any spaces because it's a pre-defined format. I tried
> bracketing the field name within my code so it's the same as in the design
> view but it still doesn't work. Can someone help me please. Here's my code
> below:
> ----
> COleSafeArray vaFieldList;
> vaFieldList.CreateOneDim(VT_VARIANT, 4);
> COleSafeArray vaValueArray;
> vaValueArray.CreateOneDim(VT_VARIANT, 4);
> long lArrayIndex[1];
> lArrayIndex[0] = 0;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
> lArrayIndex[0] = 1;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
> lArrayIndex[0] = 2;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
> lArrayIndex[0] = 3;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
> insert this field
> lArrayIndex[0] = 0;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
> lArrayIndex[0] = 1;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
> lArrayIndex[0] = 2;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
> lArrayIndex[0] = 3;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
> pRecordset->AddNew(vaFieldList, vaValueArray);
> pRecordset->Close();
> Thanks!
>
|||On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:

>Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cindy
>Mink', '4') it worked just fine but if I leave out the brackets for Num Cars,
>it throws a run-time error. So I tried applying the same thing to the code
>below by preserving the brackets and it threw an unhandled exception. Why
>does it work one way and not the other? Is something wrong with the code
>below?
(snip)
Hi luv2travel,
I don't recognise the language you are using. But it's clear that you
are not feeding it an ready-to-chew query. You are providing column
names and data and the programming language or calling interface somehow
transforms this into a query to feed to SQL Server.
Two things you might try, both based on the idea that maybe this
language "knows" that columns names with spaces must be bracketed or
quoted and therefore adds extra brackets or quotes to those you already
supplied:
1. Set up a profiler trace to capture the query actually sent to SQL
Server. This will provide absolute certainty about what's going on
behind the scenes.
2. Change this line:
>vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
>insert this field
to:
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
this will
If my theory is correct, the interface layer provided by your
programming environment will supply the brackets or quotes needed around
the column name NUM CARS and the insert will work.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||No it doesn't work. I decided to go ahead and use the EXECUTE command
instead. Thanks for your help though.
"Hugo Kornelis" wrote:

> On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:
> (snip)
> Hi luv2travel,
> I don't recognise the language you are using. But it's clear that you
> are not feeding it an ready-to-chew query. You are providing column
> names and data and the programming language or calling interface somehow
> transforms this into a query to feed to SQL Server.
> Two things you might try, both based on the idea that maybe this
> language "knows" that columns names with spaces must be bracketed or
> quoted and therefore adds extra brackets or quotes to those you already
> supplied:
> 1. Set up a profiler trace to capture the query actually sent to SQL
> Server. This will provide absolute certainty about what's going on
> behind the scenes.
> 2. Change this line:
> to:
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
> this will
> If my theory is correct, the interface layer provided by your
> programming environment will supply the brackets or quotes needed around
> the column name NUM CARS and the insert will work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||it looks like you are using php (just a guess)...if that is the case,
then you are going to have to escape the brackets (since they are both
regular expression reserved characters and used in php as well (for
array cells).
this is a long shot, but try:
vaFieldList.PutElement(lArrayI=ADndex, &(_variant_t("\[NUM CARS\]")));
if the statement works in sql analyzer then it is probably at the
script level...in which case you should at least tell us which one it
is and at most post the question in that group.
hth,
hans

insert record into field names that contain brackets

Hi there,
I am having difficuly inserting a record into the database. I came to
realize that it may be because of my column name that contains brackets. It
inserts just fine with other fields that are the same data type but it stops
at the bracketed field name. How can I get around this? I can change the
column name to exclude any spaces because it's a pre-defined format. I tried
bracketing the field name within my code so it's the same as in the design
view but it still doesn't work. Can someone help me please. Here's my code
below:
----
--
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will
not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
Thanks!luv2travel wrote:
> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains
> brackets. It inserts just fine with other fields that are the same
> data type but it stops at the bracketed field name. How can I get
> around this? I can change the column name to exclude any spaces
> because it's a pre-defined format. I tried bracketing the field name
> within my code so it's the same as in the design view but it still
> doesn't work. Can someone help me please. Here's my code below:
> ----
--
Can you post your table DDL.
In the meantime, try this:
create table ugly_columns (
col_normal int not null,
"col_ug]y" int not null )
insert into ugly_columns (
col_normal,
"col_ug]y" )
values (
1,
2 )
Select * from ugly_columns
David Gugick
Imceda Software
www.imceda.com|||This is what I imagine the DDL would look like behind the scenes but I'm not
sure how SQL Server handles attribute names that contain spaces. All I know
is that when it appears in the design view, it's contained within square
brackets.
CREATE TABLE EMPLOYEES (
NAME varchar(20),
ADDRESS varchar(50),
EMAIL varchar(50),
[NUM CARS] int
)
I tried your suggestion using insert and it worked just fine with the
brackets. The only problem with using Insert is that I have several
attributes I would need to concatenate with their corresponding values. The
method I used makes it easier for me to query but somehow it doesn't like th
e
brackets. Any ideas why? Thanks.
"David Gugick" wrote:

> luv2travel wrote:
> Can you post your table DDL.
> In the meantime, try this:
> create table ugly_columns (
> col_normal int not null,
> "col_ug]y" int not null )
> insert into ugly_columns (
> col_normal,
> "col_ug]y" )
> values (
> 1,
> 2 )
> Select * from ugly_columns
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||luv2travel wrote:
> This is what I imagine the DDL would look like behind the scenes but
> I'm not sure how SQL Server handles attribute names that contain
> spaces. All I know is that when it appears in the design view, it's
> contained within square brackets.
> CREATE TABLE EMPLOYEES (
> NAME varchar(20),
> ADDRESS varchar(50),
> EMAIL varchar(50),
> [NUM CARS] int
> )
Brackets are for object names like tables and procedures unless you have
quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
Exec [dbo].[My Proc Has a Poor Name]
or
Exec "dbo"."My Proc Has a Poor Name"
Select * from [Hyphens-In-This-Table]
or
Select * from "Hyphens-In-This-Table"
From BOL:
The body of the identifier can contain any combination of characters in
the current code page except the delimiting characters themselves.
Why is there a bracket in the column name in the first place?
David Gugick
Imceda Software
www.imceda.com|||If you open Enterprise Manager and enter a column name that contains a space
in Design View, SQL Server will automatically embrace it with brackets.
There's no way getting around it.
"David Gugick" wrote:

> luv2travel wrote:
>
> Brackets are for object names like tables and procedures unless you have
> quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
> Exec [dbo].[My Proc Has a Poor Name]
> or
> Exec "dbo"."My Proc Has a Poor Name"
> Select * from [Hyphens-In-This-Table]
> or
> Select * from "Hyphens-In-This-Table"
> From BOL:
> The body of the identifier can contain any combination of characters in
> the current code page except the delimiting characters themselves.
> Why is there a bracket in the column name in the first place?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||luv2travel wrote:
> If you open Enterprise Manager and enter a column name that contains
> a space in Design View, SQL Server will automatically embrace it with
> brackets. There's no way getting around it.
>
But in that case the brackets are not in the column, they are just the
delimiters used in the UI to prevent SQL Server throwing an error.
If you run sp_help <table>, you won't see brackets:
For example:
create table ugly_columns (
col_normal int not null,
[col space] int not null )
sp_help ugly_columns
Column_name
--
col_normal
col space
When you access the column from your code, you need to delimite the
column using brackets or double quotes if quoted identifiers are on.
I don't understand your code well enough to know why you are having a
problem. What is the error you are receiving?
David Gugick
Imceda Software
www.imceda.com|||Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cindy
Mink', '4') it worked just fine but if I leave out the brackets for Num Cars
,
it throws a run-time error. So I tried applying the same thing to the code
below by preserving the brackets and it threw an unhandled exception. Why
does it work one way and not the other? Is something wrong with the code
below?
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will
not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
"luv2travel" wrote:

> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains brackets. I
t
> inserts just fine with other fields that are the same data type but it sto
ps
> at the bracketed field name. How can I get around this? I can change the
> column name to exclude any spaces because it's a pre-defined format. I tri
ed
> bracketing the field name within my code so it's the same as in the design
> view but it still doesn't work. Can someone help me please. Here's my code
> below:
> ----
--
> COleSafeArray vaFieldList;
> vaFieldList.CreateOneDim(VT_VARIANT, 4);
> COleSafeArray vaValueArray;
> vaValueArray.CreateOneDim(VT_VARIANT, 4);
> long lArrayIndex[1];
> lArrayIndex[0] = 0;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
> lArrayIndex[0] = 1;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
> lArrayIndex[0] = 2;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
> lArrayIndex[0] = 3;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //wi
ll not
> insert this field
> lArrayIndex[0] = 0;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
> lArrayIndex[0] = 1;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
> lArrayIndex[0] = 2;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
> lArrayIndex[0] = 3;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
> pRecordset->AddNew(vaFieldList, vaValueArray);
> pRecordset->Close();
> Thanks!
>|||On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:

>Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cind
y
>Mink', '4') it worked just fine but if I leave out the brackets for Num Car
s,
>it throws a run-time error. So I tried applying the same thing to the code
>below by preserving the brackets and it threw an unhandled exception. Why
>does it work one way and not the other? Is something wrong with the code
>below?
(snip)
Hi luv2travel,
I don't recognise the language you are using. But it's clear that you
are not feeding it an ready-to-chew query. You are providing column
names and data and the programming language or calling interface somehow
transforms this into a query to feed to SQL Server.
Two things you might try, both based on the idea that maybe this
language "knows" that columns names with spaces must be bracketed or
quoted and therefore adds extra brackets or quotes to those you already
supplied:
1. Set up a profiler trace to capture the query actually sent to SQL
Server. This will provide absolute certainty about what's going on
behind the scenes.
2. Change this line:
>vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //wil
l not
>insert this field
to:
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
this will
If my theory is correct, the interface layer provided by your
programming environment will supply the brackets or quotes needed around
the column name NUM CARS and the insert will work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No it doesn't work. I decided to go ahead and use the EXECUTE command
instead. Thanks for your help though.
"Hugo Kornelis" wrote:

> On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:
>
> (snip)
> Hi luv2travel,
> I don't recognise the language you are using. But it's clear that you
> are not feeding it an ready-to-chew query. You are providing column
> names and data and the programming language or calling interface somehow
> transforms this into a query to feed to SQL Server.
> Two things you might try, both based on the idea that maybe this
> language "knows" that columns names with spaces must be bracketed or
> quoted and therefore adds extra brackets or quotes to those you already
> supplied:
> 1. Set up a profiler trace to capture the query actually sent to SQL
> Server. This will provide absolute certainty about what's going on
> behind the scenes.
> 2. Change this line:
> to:
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
> this will
> If my theory is correct, the interface layer provided by your
> programming environment will supply the brackets or quotes needed around
> the column name NUM CARS and the insert will work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||it looks like you are using php (just a guess)...if that is the case,
then you are going to have to escape the brackets (since they are both
regular expression reserved characters and used in php as well (for
array cells).
this is a long shot, but try:
vaFieldList.PutElement(lArrayI=ADndex, &(_variant_t("\[NUM CARS\]")));
if the statement works in sql analyzer then it is probably at the
script level...in which case you should at least tell us which one it
is and at most post the question in that group.
hth,
hans

insert record into field names that contain brackets

Hi there,
I am having difficuly inserting a record into the database. I came to
realize that it may be because of my column name that contains brackets. It
inserts just fine with other fields that are the same data type but it stops
at the bracketed field name. How can I get around this? I can change the
column name to exclude any spaces because it's a pre-defined format. I tried
bracketing the field name within my code so it's the same as in the design
view but it still doesn't work. Can someone help me please. Here's my code
below
----
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
Thanks!luv2travel wrote:
> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains
> brackets. It inserts just fine with other fields that are the same
> data type but it stops at the bracketed field name. How can I get
> around this? I can change the column name to exclude any spaces
> because it's a pre-defined format. I tried bracketing the field name
> within my code so it's the same as in the design view but it still
> doesn't work. Can someone help me please. Here's my code below:
> ----
Can you post your table DDL.
In the meantime, try this:
create table ugly_columns (
col_normal int not null,
"col_ug]y" int not null )
insert into ugly_columns (
col_normal,
"col_ug]y" )
values (
1,
2 )
Select * from ugly_columns
--
David Gugick
Imceda Software
www.imceda.com|||This is what I imagine the DDL would look like behind the scenes but I'm not
sure how SQL Server handles attribute names that contain spaces. All I know
is that when it appears in the design view, it's contained within square
brackets.
CREATE TABLE EMPLOYEES (
NAME varchar(20),
ADDRESS varchar(50),
EMAIL varchar(50),
[NUM CARS] int
)
I tried your suggestion using insert and it worked just fine with the
brackets. The only problem with using Insert is that I have several
attributes I would need to concatenate with their corresponding values. The
method I used makes it easier for me to query but somehow it doesn't like the
brackets. Any ideas why? Thanks.
"David Gugick" wrote:
> luv2travel wrote:
> > Hi there,
> >
> > I am having difficuly inserting a record into the database. I came to
> > realize that it may be because of my column name that contains
> > brackets. It inserts just fine with other fields that are the same
> > data type but it stops at the bracketed field name. How can I get
> > around this? I can change the column name to exclude any spaces
> > because it's a pre-defined format. I tried bracketing the field name
> > within my code so it's the same as in the design view but it still
> > doesn't work. Can someone help me please. Here's my code below:
> > ----
> Can you post your table DDL.
> In the meantime, try this:
> create table ugly_columns (
> col_normal int not null,
> "col_ug]y" int not null )
> insert into ugly_columns (
> col_normal,
> "col_ug]y" )
> values (
> 1,
> 2 )
> Select * from ugly_columns
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||luv2travel wrote:
> This is what I imagine the DDL would look like behind the scenes but
> I'm not sure how SQL Server handles attribute names that contain
> spaces. All I know is that when it appears in the design view, it's
> contained within square brackets.
> CREATE TABLE EMPLOYEES (
> NAME varchar(20),
> ADDRESS varchar(50),
> EMAIL varchar(50),
> [NUM CARS] int
> )
Brackets are for object names like tables and procedures unless you have
quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
Exec [dbo].[My Proc Has a Poor Name]
or
Exec "dbo"."My Proc Has a Poor Name"
Select * from [Hyphens-In-This-Table]
or
Select * from "Hyphens-In-This-Table"
From BOL:
The body of the identifier can contain any combination of characters in
the current code page except the delimiting characters themselves.
Why is there a bracket in the column name in the first place?
--
David Gugick
Imceda Software
www.imceda.com|||If you open Enterprise Manager and enter a column name that contains a space
in Design View, SQL Server will automatically embrace it with brackets.
There's no way getting around it.
"David Gugick" wrote:
> luv2travel wrote:
> > This is what I imagine the DDL would look like behind the scenes but
> > I'm not sure how SQL Server handles attribute names that contain
> > spaces. All I know is that when it appears in the design view, it's
> > contained within square brackets.
> >
> > CREATE TABLE EMPLOYEES (
> > NAME varchar(20),
> > ADDRESS varchar(50),
> > EMAIL varchar(50),
> > [NUM CARS] int
> > )
>
> Brackets are for object names like tables and procedures unless you have
> quoted identifiers set on (SET QUOTED_IDENTIFIER ON)
> Exec [dbo].[My Proc Has a Poor Name]
> or
> Exec "dbo"."My Proc Has a Poor Name"
> Select * from [Hyphens-In-This-Table]
> or
> Select * from "Hyphens-In-This-Table"
> From BOL:
> The body of the identifier can contain any combination of characters in
> the current code page except the delimiting characters themselves.
> Why is there a bracket in the column name in the first place?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||luv2travel wrote:
> If you open Enterprise Manager and enter a column name that contains
> a space in Design View, SQL Server will automatically embrace it with
> brackets. There's no way getting around it.
>
But in that case the brackets are not in the column, they are just the
delimiters used in the UI to prevent SQL Server throwing an error.
If you run sp_help <table>, you won't see brackets:
For example:
create table ugly_columns (
col_normal int not null,
[col space] int not null )
sp_help ugly_columns
Column_name
--
col_normal
col space
When you access the column from your code, you need to delimite the
column using brackets or double quotes if quoted identifiers are on.
I don't understand your code well enough to know why you are having a
problem. What is the error you are receiving?
David Gugick
Imceda Software
www.imceda.com|||Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cindy
Mink', '4') it worked just fine but if I leave out the brackets for Num Cars,
it throws a run-time error. So I tried applying the same thing to the code
below by preserving the brackets and it threw an unhandled exception. Why
does it work one way and not the other? Is something wrong with the code
below?
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);
long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
insert this field
lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();
"luv2travel" wrote:
> Hi there,
> I am having difficuly inserting a record into the database. I came to
> realize that it may be because of my column name that contains brackets. It
> inserts just fine with other fields that are the same data type but it stops
> at the bracketed field name. How can I get around this? I can change the
> column name to exclude any spaces because it's a pre-defined format. I tried
> bracketing the field name within my code so it's the same as in the design
> view but it still doesn't work. Can someone help me please. Here's my code
> below:
> ----
> COleSafeArray vaFieldList;
> vaFieldList.CreateOneDim(VT_VARIANT, 4);
> COleSafeArray vaValueArray;
> vaValueArray.CreateOneDim(VT_VARIANT, 4);
> long lArrayIndex[1];
> lArrayIndex[0] = 0;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
> lArrayIndex[0] = 1;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
> lArrayIndex[0] = 2;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
> lArrayIndex[0] = 3;
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
> insert this field
> lArrayIndex[0] = 0;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
> lArrayIndex[0] = 1;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
> lArrayIndex[0] = 2;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
> lArrayIndex[0] = 3;
> vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));
> pRecordset->AddNew(vaFieldList, vaValueArray);
> pRecordset->Close();
> Thanks!
>|||On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:
>Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cindy
>Mink', '4') it worked just fine but if I leave out the brackets for Num Cars,
>it throws a run-time error. So I tried applying the same thing to the code
>below by preserving the brackets and it threw an unhandled exception. Why
>does it work one way and not the other? Is something wrong with the code
>below?
(snip)
Hi luv2travel,
I don't recognise the language you are using. But it's clear that you
are not feeding it an ready-to-chew query. You are providing column
names and data and the programming language or calling interface somehow
transforms this into a query to feed to SQL Server.
Two things you might try, both based on the idea that maybe this
language "knows" that columns names with spaces must be bracketed or
quoted and therefore adds extra brackets or quotes to those you already
supplied:
1. Set up a profiler trace to capture the query actually sent to SQL
Server. This will provide absolute certainty about what's going on
behind the scenes.
2. Change this line:
>vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
>insert this field
to:
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
this will
If my theory is correct, the interface layer provided by your
programming environment will supply the brackets or quotes needed around
the column name NUM CARS and the insert will work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No it doesn't work. I decided to go ahead and use the EXECUTE command
instead. Thanks for your help though.
"Hugo Kornelis" wrote:
> On Fri, 4 Mar 2005 10:55:09 -0800, luv2travel wrote:
> >Well when I used INSERT INTO Employees (Name, [Num Cars]) VALUES ('Cindy
> >Mink', '4') it worked just fine but if I leave out the brackets for Num Cars,
> >it throws a run-time error. So I tried applying the same thing to the code
> >below by preserving the brackets and it threw an unhandled exception. Why
> >does it work one way and not the other? Is something wrong with the code
> >below?
> (snip)
> Hi luv2travel,
> I don't recognise the language you are using. But it's clear that you
> are not feeding it an ready-to-chew query. You are providing column
> names and data and the programming language or calling interface somehow
> transforms this into a query to feed to SQL Server.
> Two things you might try, both based on the idea that maybe this
> language "knows" that columns names with spaces must be bracketed or
> quoted and therefore adds extra brackets or quotes to those you already
> supplied:
> 1. Set up a profiler trace to capture the query actually sent to SQL
> Server. This will provide absolute certainty about what's going on
> behind the scenes.
> 2. Change this line:
> >vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not
> >insert this field
> to:
> vaFieldList.PutElement(lArrayIndex, &(_variant_t("NUM CARS"))); //maybe
> this will
> If my theory is correct, the interface layer provided by your
> programming environment will supply the brackets or quotes needed around
> the column name NUM CARS and the insert will work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||it looks like you are using php (just a guess)...if that is the case,
then you are going to have to escape the brackets (since they are both
regular expression reserved characters and used in php as well (for
array cells).
this is a long shot, but try:
vaFieldList.PutElement(lArrayI=ADndex, &(_variant_t("\[NUM CARS\]")));
if the statement works in sql analyzer then it is probably at the
script level...in which case you should at least tell us which one it
is and at most post the question in that group.
hth,
hans