Bulk insert problem, any ideas?

Hello, i am trying to get this to work, i made a SP that send internalmessages to x number of users, the users is located in a variable called @.To, they are seperated by commas.

INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, w, a)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

the function iter_charlist_to_table takes the usernames inside of @.To and returns a table of usernames, i then want to insert a record for each of these users.

When i try to run this:

EXEC SendInternalMessageToUsers
@.From= N'nouser',
@.To= N'Dirk,piffo,Steve',
@.Subject= N'Test',
@.Message= N'This is to test message'

I get the following result:

Msg 512, Level 16, State 1, Procedure LaberMail_SendInternalMessageToUsers, Line 36

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

any ideas?

|||

Well, the error was returned from a select statement (a subquery in one to be precise), but you did not show us the code for the query.

My idea is to show us the select statement... :)

|||

The SP contains, one insert, one update and one select that returns the results back to my program, the insert statement is solved, that one works and inserts the correct values when i remove the update and select. The same message appears for both the update statement and the select statement.

-- Works
INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, weight, adminmessage)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

-- Not working
UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

-- Not working
SELECT profile_publicinfo.username, profile_publicinfo.emailFROM profile_publicinfoINNERJOIN settings_settingsON(settings_settings.username= profile_publicinfo.username)WHERE(profile_publicinfo.username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))AND(settings_settings.post_newmailemail= 1)

these statements calls this function:

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERFUNCTION [dbo].[iter_charlist_to_table]
(@.listntext,
@.delimiternchar(1)= N',')
RETURNS @.tblTABLE(listposintIDENTITY(1, 1)NOTNULL,
strvarchar(4000),
nstrnvarchar(2000))AS
BEGIN
DECLARE @.posint,
@.textposint,
@.chunklensmallint,
@.tmpstrnvarchar(4000),
@.leftovernvarchar(4000),
@.tmpvalnvarchar(4000)
SET @.textpos= 1
SET @.leftover=''
WHILE @.textpos<=datalength(@.list)/ 2
BEGIN
SET @.chunklen= 4000-datalength(@.leftover)/ 2
SET @.tmpstr= @.leftover+substring(@.list, @.textpos, @.chunklen)
SET @.textpos= @.textpos+ @.chunklen
SET @.pos=charindex(@.delimiter, @.tmpstr)
WHILE @.pos> 0
BEGIN
SET @.tmpval=ltrim(rtrim(left(@.tmpstr, @.pos- 1)))
INSERT @.tbl(str, nstr)VALUES(@.tmpval, @.tmpval)
SET @.tmpstr=substring(@.tmpstr, @.pos+ 1,len(@.tmpstr))
SET @.pos=charindex(@.delimiter, @.tmpstr)
END
SET @.leftover= @.tmpstr
END
INSERT @.tbl(str, nstr)VALUES(ltrim(rtrim(@.leftover)),ltrim(rtrim(@.leftover)))
RETURN

@.From, @.Subject, @.Message, @.To are sent as parameters to the program, the @.To contains the usernames seperated by commas, ex. "John,Steve,Andrew,Patrick,"
(It is always a extra comma after the last username in the @.To parameter)

Patrick

|||

You are doing a basic no-no in this statement:

UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

When you state that username must = the result of a subquery (that's the select s.nstr etc. is), the subquery can only return 1 row. If it returns more than one row, how would sql server know which one you meant?

I think you may want to change to

...WHERE (username IN (SELECT ...

The IN operator works of a list of items, which can be hard-coded or supplied via query. (I work in several flavors of sql databases and my test database is down at the moment, so I can't double check the syntax.)

|||

You have the same problem in the select statement. I don't have time to work thru that one, but I'm wondering why you just don't join the table function results instead of doing a subquery. It will run faster and be easier to understand.

|||

How do join that function table into the select statement?

I solved the problem, with the IN instead of =, like you said, it worked for both the select and the update

Bulk insert problem

Hey,

I am trying to do a bulk insert from a txt file. I am trying to allow the query to read the txt file off the users local computer. This is the code I am using:

BULK INSERT TbleTestBulk
FROM 'C:\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

I also tried this

BULK INSERT TbleTestBulk
FROM '\\host_name\C$\westportela.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

and this

BULK INSERT macomber
FROM '\\wcsserver\SSP\txt frm mms\WES.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

none work.

I basically get this error for each:
Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file '\\wcsserver\SSP\txt frm mms\WES.txt' could not be opened. Operating system error code 53(The network path was not found.).

Anyone got any suggestions. I am really new to this so all help would be welcomed.

Thanks

MikeDid the client share that location?

Why not copy the file to the server...I would suggest that that is the preferred method...you would want to take the network out of the equation when loading or dumping data...

My own opinion (MOO) *

* Actually not really...it's in a few books I've read

Bulk Insert and Decimal type

Hi there

I am trying to write a program which will bulk load data from a bcp file into a newly made database on the users PC.

I create the data from an existing DB using SQL-DMO BulkCopy.
I then load it into the users DB using "Bulk Insert " transact SQL.

It all works fine on SQL Server 2000. However on SQL Server 7.0 whenever the .bcp file is being loaded into a table with a field of type decimal, it throws an OLEDB stream error. Even when the .bcp file is empty.

I have tried exporting/importing the data as tab delimited and as native, but it seems to make no difference.

This has really got me stumped and I am running out of time. Can anyone help?

Thanks.

justinOK I just discovered from the Microsoft web site that there is a bug in SQL Server 7.0. Using Bulk Insert on a table that includes a default value for decimal or numeric data typed fields, throws an error.

There is no solution. It is incurable. The workaround is to "use bcp instead."

Programatically that would be an issue, so i will have to use DMO.

Building a mailing list

Hi folks,

I'm building a DTS package which needs to mail a list of users nightly

The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.

I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.

The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3...etc).

I'm a bit unsure on how to go about doing / writing this.

Can anyone help?

Thanks,

Davethis would be far easier if you trashed the dts, built a stored procedure and then created a job to run the procedure.|||Thought about that but my Stored Proc skills are nothing to write home about. If you have anything I could tweak to suit my needs I'd be grateful.

Thanks,

Dave|||Try running this sql through Query Analyzer:declare @.MyEmailString varchar(8000)

select @.MyEmailString = coalesce(@.MyEmailString + ',', '') + coalesce(email, '')
from employees
where role = 'MGR'

select @.MyEmailString|||Thanks Blindman.

Still trying to get my head around the syntax and use of Coalesce but it works.

Thanks again,

Dave|||Hi Blindman,

I need to run the code below against a MySql database.
It works fine when I modify it for an MS SQL db however I'm getting syntax errors when running it against a MySQL (5.1.9) db.

Any ideas?

Thanks,

Dave|||I wouldn't be surprised if this syntax didn't work in MySQL. Try posting your question in the MySQL forum.|||Lord, where's R937 when somebody mentions his favorite toy! You probably want the GROUP_CONCAT (http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html) function in MySQL.

<Afterthought>
After posting this, I realized that Rudy would probably pitch a fit, mostly because he enjoy's pitching fits. Note that I do NOT consider MySQL to be a toy, Rudy just likes to play with it because MySQL offers functions that violate the basic rules of relational algebra (such as the GROUP_CONCAT() function). This violation of hte rules doesn't make MySQL a bad product in any way, although I see it as removing MySQL from the category of Relational databases since it doesn't follow the basic rules required for a relational database.

-PatP

Building a community

Hi!

Hello. I have now started to build my own community. And I have some questions on the database.

For the users to login I use the login control and all the users information is stored in the ASPNETDB.MDF database.
In the web.config file I have created some profiles for saving some information about the users (Name, Birth, Town) and so on.

Now. All the users in this community will have their own profile page, Guestbooks ++.

So I was wondering if I should create tables for all features like guestbook, profile pages or should I do this by using Profile (ASP.NET).

How many users does ASPNETDB support?.

I suppose it depends on which server are you running it, Mostly it is limited to database size and is limited to

4 GB maximum database size on SQL Express but no limit in paid versions.

And it probably depends on your system speed to prevent user to wait couple of minutes to login.

And you can always create separate user database for each of your web page if 4GB is too small for you.

Thanks

Build conditional WHERE clause

Hi All,

I'm building a simple stored proc to be the basis of an SQL Report.

I've set up my parameters to default to NULL, as the users will have the option to filter the parameters to build the report. I have 2 datetime params which are causing me a problem.

I have a condition which checks if these 2 params are NULL, if they are, it just continues and executes the SQL query, and all is well. However, if they contain dates, I am trying to build a where condition in the variable named @.where_clause, and then I append this to the end of my SQL statement.

- If my @.where_clause is empty, the stored proc returns the desired results
- If the @.where_clause is NOT empty, my stored proc executes but returns NO results
- And last, if I modify the stored proc and remove the WHERE clause completely, and just add the "AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time". to the end of my statement in plain SQL, then it works.

** It only seems to not work when I put build my WHERE clause in a variable **

I hope this is not too confusing.

Here is the stored proc as it is now....not working.

ALTER PROCEDURE [dbo].[spReport_ProductSerialAssemblyResults]
-- Add the parameters for the stored procedure here
@.pec varchar(30) = NULL,
@.rel varchar(30) = NULL,
@.ver varchar(10) = NULL,
@.sn varchar(50) = NULL,
@.start_time datetime = NULL,
@.end_time datetime = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @.where_clause varchar(MAX)

set @.where_clause = '';

if ((@.start_time is not null) and (@.end_time is not null))
BEGIN
set @.where_clause = ' AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time';
END



-- Insert statements for procedure here
SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1) AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial) + @.where_clause;


END

To do what you are trying to do where you take a character variable and execute it as SQL code you need to use dynamic SQL. See the EXECUTE statement, and the sp_executesql stored procedure for info on dynamic SQL.

However, in your case dynamic SQL is not necessary (and it should be avoided unless necessary). What you can do in your SELECT statement is simply this

SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1)
AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial)
AND (tblProducts.start_time >= @.start_time
AND tblProducts.start_time <= @.end_time
OR (@.start_time IS NULL OR @.end_time IS NULL))

|||

Please take a look at the link below for various solutions that doesn't require dynamic SQL code.

http://www.sommarskog.se/dyn-search.html

|||Thank you both for the great feedback...