bcp_moretext & NULLs

When sending two 'text' columns using BCP, and using bcp_moretext because
the first is very large, and if the second is NULL, then how do you send a
NULL value using bcp_moretext?Peter (schmidt.peter@.comcast.net) writes:
> When sending two 'text' columns using BCP, and using bcp_moretext because
> the first is very large, and if the second is NULL, then how do you send a
> NULL value using bcp_moretext?

I have never used bcp_moretext, but what happens it you set the length to 0
with bcp_collen?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

bcp_bind and SQL DATETIME type

Hi all,
I'm working on a simple ODBC API bulk load application and I've run
into a problem. I am binding variables in memory and sending the rows
as prescribed in the ODBC 3.0 API Reference and on MSDN. Everything
works fine for SQL VARCHAR and SQL INTEGER data types; however, I run
into issues when I try to bind SQL DATETIME data types. I can't seem
to find examples of how this should be done anywhere... Should my
variable that I'm binding to be a C-Style string or a
SQL_TIMESTAMP_STRUCT or a time_t structure? It's all a blur...
Anyone know where I can find a sample that bcp_binds to a DATETIME
column in a table?
TIANever mind, issue resolved.
"Michael C#" wrote:

> Hi all,
> I'm working on a simple ODBC API bulk load application and I've run
> into a problem. I am binding variables in memory and sending the rows
> as prescribed in the ODBC 3.0 API Reference and on MSDN. Everything
> works fine for SQL VARCHAR and SQL INTEGER data types; however, I run
> into issues when I try to bind SQL DATETIME data types. I can't seem
> to find examples of how this should be done anywhere... Should my
> variable that I'm binding to be a C-Style string or a
> SQL_TIMESTAMP_STRUCT or a time_t structure? It's all a blur...
> Anyone know where I can find a sample that bcp_binds to a DATETIME
> column in a table?
> TIA
>|||Hello Michael,
Can you please show me an example that "works fine for SQL VARCHAR"? I just
can't send text using the bcp...
TIA

quote:

---

Originally posted by Michael C
Never mind, issue resolved.
"Michael C#" wrote:

> Hi all,
> I'm working on a simple ODBC API bulk load application and I've run
> into a problem. I am binding variables in memory and sending the rows
> as prescribed in the ODBC 3.0 API Reference and on MSDN. Everything
> works fine for SQL VARCHAR and SQL INTEGER data types; however, I run
> into issues when I try to bind SQL DATETIME data types. I can't seem
> to find examples of how this should be done anywhere... Should my
> variable that I'm binding to be a C-Style string or a
> SQL_TIMESTAMP_STRUCT or a time_t structure? It's all a blur...
> Anyone know where I can find a sample that bcp_binds to a DATETIME
> column in a table?
> TIA
>

---

|||Hi Michael, i am facing similar issue i.e. failing of bcp_binf for datetime
datatpye. Can You please share the solution that you have for this issue?
Thanks in advance.
From http://developmentnow.com/g/111_200...
ype.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

BCP in using bcp_sendrow fails, not enough columns bound

Hello, first posting:
I am using bcp_sendrow to INSERT data from program variables into the
database.
I am sending less columns than the database contains, because new
columns need to be added to the tables before the program is changed.
However, despite the fact that the unspecified columns have default
values associated with them, I still get the error message:
"Not enough columns bound"
every time I attempt to insert a line. It does not matter if the
column is NULLable or not, I have determined that it will fail if the
unspecified columns are at the end of the table. Unspecified columns
in the middle of the table automatically get populated with the default
value or NULL.
Has anyone else come across this problem? Is there some sort of switch
I can use to make this work? The only solution I have come up with is
to insert new columns in the middle of the table, which I shouldn't
have to do (and it's slow at that).
Thanks in advance,
RobBind all columns and set the columns to ignore as NULL like this:
r = bcp_bind(hdbc, /* Database Handle */
(const LPCBYTE)"", /* Empty String */
0, /* 0 prefix len */
SQL_NULL_DATA, /* length of -1 = set column to NULL */
(const LPCBYTE)"", /* Terminator Char */
1, /* Terminator Length = 1 for \0x00 */
SQLCHARACTER, /* Column type */
Col_Num /* Column Number */
);
"Robert B" <rob@.automatedfinancial.com> wrote in message
news:1148604213.015728.148440@.38g2000cwa.googlegroups.com...
> Hello, first posting:
> I am using bcp_sendrow to INSERT data from program variables into the
> database.
> I am sending less columns than the database contains, because new
> columns need to be added to the tables before the program is changed.
> However, despite the fact that the unspecified columns have default
> values associated with them, I still get the error message:
> "Not enough columns bound"
> every time I attempt to insert a line. It does not matter if the
> column is NULLable or not, I have determined that it will fail if the
> unspecified columns are at the end of the table. Unspecified columns
> in the middle of the table automatically get populated with the default
> value or NULL.
> Has anyone else come across this problem? Is there some sort of switch
> I can use to make this work? The only solution I have come up with is
> to insert new columns in the middle of the table, which I shouldn't
> have to do (and it's slow at that).
>
> Thanks in advance,
> Rob
>|||Thanks, Mike,
I have no problem binding to a column and inserting NULL data. My
problem is that my program will fail with "not enough columns bound" as
soon as a new column is added to the table, even if the new column is
set to NULL, default NULL. I need to maintain some sort of
backwards-compatibility where the program will continue to work even
though I have not specified all the columns.
Rob|||I'm working on a similar problem myself right now. The fix I'm looking at
is to grab the column information from the table ahead of time and bind the
columns dynamically using that information. The info is available in the
INFORMATION_SCHEMA views, and I'm also testing a couple of other methods
myself. It's definitely more work, but definitely necessary in my case.
"Robert B" <rob@.automatedfinancial.com> wrote in message
news:1148659354.289141.188110@.j55g2000cwa.googlegroups.com...
> Thanks, Mike,
> I have no problem binding to a column and inserting NULL data. My
> problem is that my program will fail with "not enough columns bound" as
> soon as a new column is added to the table, even if the new column is
> set to NULL, default NULL. I need to maintain some sort of
> backwards-compatibility where the program will continue to work even
> though I have not specified all the columns.
> Rob
>

Batch process for sending mail

Hello everyone,

I want to write a batch process that will run everyday and send emails.

The scenario is as follows:

There is a websummit held every year and people can register for it. However the request is subjected to be approved/rejected by the admin belonging to that area. Whenver a person enrolls, the RequestStatus is 0. The admin has to change the RequestStatus to either 1(accepted) or -1(rejected).

I want to write a batch process that will send mails to all admins belonging to their particular areas, everyday at 8.00 A.M. The email will be sent whenever the request has not been changed(ie. RequestStatus=0) and their is a lapse of more than 7 days for their area or the request count for their area has exceeded 5.

Table structure:

Admin

AdminID

Email

AdminArea

AdminID

AreaCode

Area

AreaCode

AreaName

WebSummit

SummitId

RequestorName

DateOfRequest

RequestStatus

AreaCode

Regards,

Vidya.

Hi,

Kindly advise.

Regards,

Vidya

|||

You'll probably want to use the master.dbo.xp_sendmail procedure, using the @.query parameter.

Roughly, create a SQL Server Agent job to run at 8:00AM

and run code that creates a table of recipients, then execute xp_sendmail for each recipient with the query to provide the information in the email you want them to see.

|||

Hi,

Thanks for reading. Some code will be useful.

Regards,

Vidya

|||

Here's the basic template for the script to be executed.

You'll need to adjust the queries to be exactly what you need.

I just did a simple query to get the admin who had 5 or more requests present.

Code Snippet

createtable #Admin (AdminID int, Email varchar(50))

insertinto #Admin values(1,'admin1@.mycompany.com')

insertinto #Admin values(2,'admin2@.mycompany.com')

insertinto #Admin values(3,'admin3@.mycompany.com')

insertinto #Admin values(4,'admin4@.mycompany.com')

createtable #AdminArea (AdminID int, AreaCode int)

insertinto #AdminArea values(1, 100)

insertinto #AdminArea values(2, 200)

insertinto #AdminArea values(3, 300)

insertinto #AdminArea values(4, 400)

createtable #Area (AreaCode int, AreaName varchar(50))

insertinto #Area values(100,'Area 100')

insertinto #Area values(200,'Area 200')

insertinto #Area values(300,'Area 300')

insertinto #Area values(400,'Area 400')

createtable #WebSummit ( SummitId int, RequestorName varchar(50),

DateOfRequest datetime, RequestStatus int, AreaCode int)

insertinto #WebSummit values( 1,'George Jetson',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Jane Jetson',getdate(), 1, 200 )

insertinto #WebSummit values( 1,'Fred Flinstone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Wilma Flintsone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Barney Rubble',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Betty Rubble',getdate(), 1, 100 )

droptable #to

select a.AdminID, a.Email

into #to

from #Admin a

where a.AdminID in

(

select a.AdminID

from #Admin a

innerjoin #AdminArea aa

on a.AdminID = aa.AdminID

innerjoin #Area b

on aa.AreaCode = b.AreaCode

innerjoin #WebSummit w

on b.AreaCode = w.AreaCode

groupby a.AdminID

havingcount(*)>4

)

declare elist cursorfor

select adminid, email from #to

declare @.id int, @.email varchar(50), @.cmd nvarchar(4000)

open elist

if@.@.cursor_rows> 0

begin

fetch next from elist into @.id, @.email

while@.@.fetch_status=0

begin

set @.cmd = N'put your query here WHERE AdminID = '+cast(@.id asvarchar(10))

exec master.dbo.xp_sendmail @.recipient=@.email, @.query=@.cmd,

@.subject='Summit Follow-up'

fetch next from elist into @.id, @.email

end

end

close elist

deallocate elist

|||

Thanks Dale.

That was very helpful.

Regards,

Vidya.