Binary with XML into db using NS/filesystemwatcher?

Have a network share that will routinely get an xml file and a pdf dropped together. The xml file contains metadata about the pdf. Want to insert the various metadata elements in the xml file and the blob'd pdf as a varBinary into a table row as they are dropped.

Would like the Watcher to fire C# code to insert the xml elements and blob the pdf into the table (or can this be set up declaratively?)

Assuming not, best NS architecture to do this?

Thanks, Dan

Sorry, the SQL NS has absolutely nothing to do with this scenario.

Recommendation: use the FileSystemWatcher in a custom Windows Service.

Binary Data Type

Hello Dears,

I was Maked Pictures table in sql server 2005 with Two Col pic_Id (int) and picture(binary)

i need now ro insert image into this table by Asp.net under VB.net Code i have in my form FileUpload and button control

and this is my Code

ProtectedSub btnLoad_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnLoad.Click

Dim content()AsByte = ReadBitmap2ByteArray(FileUpload1.PostedFile.ContentType)

StoreBlob2DataBase(content)

EndSub

Function ReadBitmap2ByteArray(ByVal FileNameAsString)AsByte()

Dim imageAs Drawing.Bitmap =New Drawing.Bitmap(FileName)

Dim streamAs IO.MemoryStream =New IO.MemoryStream()

image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)

Return stream.ToArray

EndFunction

Sub StoreBlob2DataBase(ByVal contentAsByte())

con.Open()

Dim cmAsNew SqlCommand("Insert into Pictures(Pic_Id,picture) values(@.id,@.pic)", con)

cm.Parameters.AddWithValue("@.ID", 1)

Dim p1AsNew SqlParameter

p1.ParameterName ="@.pic"

p1.SqlDbType = Data.SqlDbType.Binary

p1.Value = content

p1.Size = content.Length

cm.Parameters.Add(p1)

cm.ExecuteNonQuery()

EndSub

when i make run to my website it give me an Exeption in this statement

Dim imageAs Drawing.Bitmap =New Drawing.Bitmap(FileName)

it tell me that Parameter is not valid

i need help about this please

with my Best regard

khalil

In this line:

Dim content()AsByte = ReadBitmap2ByteArray(FileUpload1.PostedFile.ContentType)

You are passing in the contentType rather than a filename.

|||

HI

its does not work also its give me the same error what can i do

|||

Of course it gives you the same error, I didn't fix it, I just pointed out what was wrong. Pass in the filename of the file, not the contenttype.

binary data insert fails using dblib

who still uses the old dblib can help me?
thanks in advance:

1.create table & procedure in db:
test_table(uniqueidentifier a,varbinary50 b)
CREATE PROCEDURE insert_table
@.b_in varbinary
AS
insert into test_table(b) values (@.b_in)
GO

2.write program use dblib:
wchar_t str[120]=L"ABC";
dbrpcparam(dbproc[i], "@.b_in", (BYTE)NULL, SQLVARBINARY,
-1, 6, &str)

3.I can find the program executed successfully,but only 1 Byte is inserted:
a b
---------------
199D71BE-327A-4BC1-AEC8-ACB0C96076CA 0x41

how to insert the whole string into the database?See if you can spot the difference in the code below (tsk...the answer is there...):

declare @.i varbinary
set @.i = 0xffffffff
select @.i, datalength(@.i)
go
declare @.i varbinary(10)
set @.i = 0xffffffff
select @.i, datalength(@.i)
go|||Thanks! I have made a serious mistake *_*

Bill of material

I have a table with data
create table a
(
a01 char(4),
a02 char(4),
a03 int
);
insert into a
values('a','b',1);
insert into a
values('a','c',1);
insert into a
values('x','c',1);
insert into a
values('c','g',1);
insert into a
values('b','h',1);
insert into a
values('h','k',1);
insert into a
values('b','k',1);
insert into a
values('g','k',1);
if my entry where value "k", i want show result
a01,a03
a 2
x 1
how to do ....
-
thanks!Get a copy of TREES & HIERARCHIES IN SQL for help.|||thanks a lot!
this my tree sql parent to find child ,but i want child to find parent,
do you have any idea?
ALTER PROCEDURE BOMIA_PPart
(
@.MODE NVARCHAR(12)
)
AS
SET NOCOUNT ON
CREATE TABLE #TEMP_A
(
LVL INT,
PARENT NVARCHAR(12) ,
PARTNO NVARCHAR(12) ,
M CHAR(2),
P CHAR(2),
PARTDESC NVARCHAR(40) ,
UNIT NVARCHAR(3) ,
USAGE REAL,
LOCATION NVARCHAR(18) ,
SXRQ NVARCHAR(10),
SSRQ NVARCHAR(10)
)
DECLARE @.ENDTREE INT
DECLARE @.NLVL INT
SELECT @.ENDTREE = 0
SELECT @.NLVL=1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT 1,IB001 ,IB003 ,IB031,IB004,IB011,IB008,IB009
FROM BOMIB
WHERE IB001=@.MODE
WHILE (@.ENDTREE =0 )
BEGIN
SELECT @.NLVL=@.NLVL+1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT @.NLVL,
A.IB001 ,
A.IB003 ,
A.IB031,
A.IB004,
A.IB011,
A.IB008,
A.IB009
FROM BOMIB A, #TEMP_A B
WHERE A.IB001 =B.PARTNO
AND B.LVL=@.NLVL -1
AND B.P!='1'
IF NOT EXISTS (SELECT PARTNO COLLATE database_default FROM #TEMP_A WHERE
LVL =@.NLVL)
SELECT @.ENDTREE=1
END
UPDATE #TEMP_A
SET M=AA070,
PARTDESC=AA020,
UNIT=AA050
FROM INVAA,#TEMP_A
WHERE AA010=PARTNO
SELECT
CAST(REPLICATE('.',LVL)+CAST(LVL AS NVARCHAR(2)) AS CHAR(12)) AS ',
PARENT AS ',
PARTNO AS ',
M,
CASE WHEN P='1' THEN '-'
WHEN P='2' THEN '+'
WHEN P='3' THEN '*'
ELSE '.'
END AS P,
PARTDESC AS ',
UNIT AS ',
ROUND(USAGE,2) AS ',
SXRQ AS ',
SSRQ AS ',
LOCATION AS '
FROM #TEMP_A
DROP TABLE #TEMP_A
SET NOCOUNT OFF
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121909782.095848.154870@.g14g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for help.
>|||Did you bother to follow up my posting? TREES & HIERARCIES IN SQL
might be good read before you post again .sql

Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...|||I suggest you walk through the list of issues posted in my blog--there is a
whitepaper there that discusses these
issues.http://betav.com/blog/billva/2006/0...com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"THE JOK3R" <a@.a.a> wrote in message
news:O8v9ZKOjHHA.4936@.TK2MSFTNGP03.phx.gbl...
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
> news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> I both did everything...enabled TCP/IP and Remote Connection, opened port
> 1431 default port...

Big problem connecting SQLEXPRESS within a vb net application

Hello.
I've an application that has to do a bunch of "SELECT" and INSERT into a
couple of tables. On my machine everything works fine, but if I try to
deploy the app on a VM for a test i have many probs.
The most common is "(Named Pipes Provider, error: 40 - Could not open a
connection to SQL
Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
type
'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
Catalog=Clienti;Integrated
Security=True", where "Clienti" is the DB, but i tried the string I found on
http://www.connectionstrings.com/?carrier=sqlserver2005 too.
It's being absurd...please give me an help...thanks...
Ah, did you enable TCP/IP on the target server? Why are you using named
pipes? If you do, you must enable additional ports in the firewall (if there
is one).
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"THE JOK3R" <a@.a.a> wrote in message
news:%23GL$eDNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> Hello.
> I've an application that has to do a bunch of "SELECT" and INSERT into a
> couple of tables. On my machine everything works fine, but if I try to
> deploy the app on a VM for a test i have many probs.
> The most common is "(Named Pipes Provider, error: 40 - Could not open a
> connection to SQL
> Server)", but using (local)\SQLEXPRESS i've "Request for the permission of
> type
> 'System.Data.SqlClient.SqlClientPermission, System.Data, version=2.0.0.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e89'" too...I think it's a
> connectionstring problem. I used "Data Source=(local)\SQLEXPRESS;Initial
> Catalog=Clienti;Integrated
> Security=True", where "Clienti" is the DB, but i tried the string I found
> on http://www.connectionstrings.com/?carrier=sqlserver2005 too.
> It's being absurd...please give me an help...thanks...
|||"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> wrote in message
news:OpGW39NjHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Ah, did you enable TCP/IP on the target server? Why are you using named
> pipes? If you do, you must enable additional ports in the firewall (if
> there is one).
I both did everything...enabled TCP/IP and Remote Connection, opened port
1431 default port...
sql

BI Accelerator 1.1 and Bulk Insert

Dear Anyone,

I had been using BI Accelerator 1.1 for 2 years now. In our current project, I encountered the following error during the importing phase of BI.

I traced the problem to the bulk insert statement that BI Accelerator is executing which is ...

BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert] FROM 'C:\Program Files\Microsoft SQL Server Accelerator for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt' WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL Server Accelerator for BI\data\Project88\DTS\Import\Format\S_Fact_Brdcast Alert.fmt' , DATAFILETYPE = 'widechar', BATCHSIZE = 100000 , MAXERRORS = 100 )

The error that it generates is as follows:

Bulk Insert: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

We export data to text files that BI Accelerator picks up. All other files imports properly imported without problems except for one fact table text file. We use the same format file that BI Accelerator uses to export to file the data to make sure that there would be no incompatabilities. File size is about 2.3GB++, which is a standard size with our other fact table files that doesnt have any errors.

We also checked for data error which we found none. We also checked the txt file we generate, notepad confirms that it is a unicode file.

Can anyone please shed a light in what is happening. Is this a bug? As much as possible I dont want to place a workaround on this one since our entire framework for loading data is all the same and has already been set up. Any help would be apreciated.

Thank YouAny reply would be appreciated.

Joseph Ollero
jollero@.wizardsgroup.com

Better way to build a stored proc for an INSERT...

I've built a stored procedure where I'm inserting a row into two tables.
Both tables have a number of columns - and so I have to pass a rather larger
number of parameters to the stored proc. Like follows
INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1, @.cParm2,
... @.cParm25)
INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
@.cParm27, ... @.cParm50)
For any one row, however, at least a third of the columns are going to be
NULL, based on the value of one of the columns (a category column).
There's no opportunity to modify the table structure - it is what it is.
What I have "works", but I'm curious if there's a way that doesn't involve
as many parameters.
Thanks,
KevinKevin@.test.com wrote:
> I've built a stored procedure where I'm inserting a row into two
> tables.
> Both tables have a number of columns - and so I have to pass a rather
> larger number of parameters to the stored proc. Like follows
> INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1,
> @.cParm2, ... @.cParm25)
> INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
> @.cParm27, ... @.cParm50)
>
> For any one row, however, at least a third of the columns are going
> to be NULL, based on the value of one of the columns (a category
> column).
> There's no opportunity to modify the table structure - it is what it
> is. What I have "works", but I'm curious if there's a way that
> doesn't involve as many parameters.
>
> Thanks,
> Kevin
Write separate stored procedures for each "insert" type. So, let's say
your table has three logical implementations (design-issues aside), you
can write three separate insert SPs that only require the user pass
those that are asked.
The other option is to use defaults on the parameters, so if they are
not passed they default to an appropriate value:
Create Proc Test
@.Param1 INT = NULL
@.Param2 INT = NULL
as
Exec dbo.Test @.Param2 = 5
Exec dbo.Test @.Param1 = 3
Exec dbo.Test 1, 3
Exec dbo.Test
You may have to add some validation to the SP in the case where a user
leaves out a logically incorrect number of columns.
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks!...you've given me some good ideas to seriously consider, especially
having 3 stored procs.
Kevin

Better for perfomance

Hello all
Im using SQL Server 2005, ADO 2.9, OLEDB.
My question is;
Lets say we have 2 tables ITEMS and LOANS
When item is "checked out", i insert line into LOANS table and in ITEMS
table field state_id is changed from 1 to 3 using UPDATE ITEMS SET
STATE_ID=3 WHERE ID= Now, what is better for perfomance - INSERT and UPDATE querys are both in
source code (client side)
or INSERT is in source code and UPDATE using triggers on LOANS table using
AFTER INSERT?
Best Regards;
MeelisMeels
I'd go with single stored procedure . Clients just call it.
"Meels Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:B932E953-2275-47CD-8FCB-BC1A571B5F4B@.microsoft.com...
> Hello all
>
> Im using SQL Server 2005, ADO 2.9, OLEDB.
> My question is;
> Lets say we have 2 tables ITEMS and LOANS
> When item is "checked out", i insert line into LOANS table and in ITEMS
> table field state_id is changed from 1 to 3 using UPDATE ITEMS SET
> STATE_ID=3 WHERE ID=> Now, what is better for perfomance - INSERT and UPDATE querys are both in
> source code (client side)
> or INSERT is in source code and UPDATE using triggers on LOANS table using
> AFTER INSERT?
>
> Best Regards;
> Meelis|||The best solution is to create a great stored procedure that inserts/updates
into both tables and have the client app call the proc.
-Paul
"Meels Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:B932E953-2275-47CD-8FCB-BC1A571B5F4B@.microsoft.com...
> Hello all
>
> Im using SQL Server 2005, ADO 2.9, OLEDB.
> My question is;
> Lets say we have 2 tables ITEMS and LOANS
> When item is "checked out", i insert line into LOANS table and in ITEMS
> table field state_id is changed from 1 to 3 using UPDATE ITEMS SET
> STATE_ID=3 WHERE ID=> Now, what is better for perfomance - INSERT and UPDATE querys are both in
> source code (client side)
> or INSERT is in source code and UPDATE using triggers on LOANS table using
> AFTER INSERT?
>
> Best Regards;
> Meelis

Best way to update/insert/delete in sqlserver database

Hello
I've passed the last days going around with xml, sqlxml, uppdategrams,
diffgrams, transactions, sp's, everything, I suppose, but still have
not a clear line of thoughts...
Im new in the info systems world.. I'm currently developing an
windows app in vb.net running over sqlserver 2000. Till now I was
using simple inserts, updates and deletes in db tables (not much so
far) but now that performance and data consistency issues are being
discussed, I was trying to understand the best way to pass data
to/from database.
Clearly I want to have the business logic in server side, being
executed by storedprocedures, but in large updates, inserts and
deletes I must pass many data and that it's a problem.
For an update I was doing something like this:
Try
loTransaction = lcnPlada.BeginTransaction()
lcmActualiza.Transaction = loTransaction
For Each loLinha In tdtActualizaHistorico.Rows
lcmActualiza.CommandText = "insert into
stetiquetahistorico (encomenda,produto,tamanho,cor,etiqueta,etiquetaem issor,dataemissaoetiqueta,etiquetaqtd,ficheiroemis sao)
" & _
"values (" & CType(loLinha(0), Integer) &
"," & CType(loLinha(1), Integer) & ",'" & loLinha(2).ToString & "'," &
CType(loLinha(3), Integer) & "," & CType(loLinha(4), Integer) & ",'" &
Environment.UserName & "','" & Now & "', " & CType(loLinha(5),
Integer) & "," & tnNumeroEmissao + 1 & ")"
lcmActualiza.ExecuteNonQuery()
Next
loTransaction.Commit()
Return True
end try
But have logic (a for each) inside a transaction its not a good idea,
right?
I've seen many different examples of adodb objects constructing a
stream with the xml statements that executes a sp and maps the table
columns to perform updates or inserts and then
adodb.command.commandstream = stream
adodb.command.execute
and in server side creates a sp that
sp_xml_preparedocument
...
insert...
sp_xml_removedocument
Sqlserver books online has an example of it that works correct in
northwind database but what about using sqlcommand? or
sqlxmlcommand?
Can I use a sqlxmlcommand to execute a stream that was written in
code, instead of using templates?
Is there other ways than sqlxml to perform this kind of tasks?
Anybody can help me, please?
Thanks in advance
sp_xml_preparedocument/OpenXML is a T-SQL feature. So it is independent of
what client you are using to connect to the database. Steps are:
1. Write your stored proc to do the logic
2. Use the normal way that your client requires to pass arguments and
execute the stored proc.
You only need the command stream object if you are using FOR XML to get XML
back from the server.
HTH
Michael
"?scar Martins" <subdueme@.hotmail.com> wrote in message
news:229f0a23.0410141120.1efff3b8@.posting.google.c om...
> Hello
> I've passed the last days going around with xml, sqlxml, uppdategrams,
> diffgrams, transactions, sp's, everything, I suppose, but still have
> not a clear line of thoughts...
> Im new in the info systems world.. I'm currently developing an
> windows app in vb.net running over sqlserver 2000. Till now I was
> using simple inserts, updates and deletes in db tables (not much so
> far) but now that performance and data consistency issues are being
> discussed, I was trying to understand the best way to pass data
> to/from database.
> Clearly I want to have the business logic in server side, being
> executed by storedprocedures, but in large updates, inserts and
> deletes I must pass many data and that it's a problem.
> For an update I was doing something like this:
> Try
> loTransaction = lcnPlada.BeginTransaction()
> lcmActualiza.Transaction = loTransaction
> For Each loLinha In tdtActualizaHistorico.Rows
> lcmActualiza.CommandText = "insert into
> stetiquetahistorico
> (encomenda,produto,tamanho,cor,etiqueta,etiquetaem issor,dataemissaoetiqueta,etiquetaqtd,ficheiroemis sao)
> " & _
> "values (" & CType(loLinha(0), Integer) &
> "," & CType(loLinha(1), Integer) & ",'" & loLinha(2).ToString & "'," &
> CType(loLinha(3), Integer) & "," & CType(loLinha(4), Integer) & ",'" &
> Environment.UserName & "','" & Now & "', " & CType(loLinha(5),
> Integer) & "," & tnNumeroEmissao + 1 & ")"
> lcmActualiza.ExecuteNonQuery()
> Next
> loTransaction.Commit()
> Return True
> end try
> But have logic (a for each) inside a transaction its not a good idea,
> right?
>
> I've seen many different examples of adodb objects constructing a
> stream with the xml statements that executes a sp and maps the table
> columns to perform updates or inserts and then
> adodb.command.commandstream = stream
> adodb.command.execute
> and in server side creates a sp that
> sp_xml_preparedocument
> ...
> insert...
> sp_xml_removedocument
> Sqlserver books online has an example of it that works correct in
> northwind database but what about using sqlcommand? or
> sqlxmlcommand?
> Can I use a sqlxmlcommand to execute a stream that was written in
> code, instead of using templates?
> Is there other ways than sqlxml to perform this kind of tasks?
> Anybody can help me, please?
>
> Thanks in advance

Best way to select and insert a huge table in the same database

insert A (...) select ... from B inner join C on ....
will create huge transact log. Any better way?See 'Minimally Logged Operations' and 'Bulk-Logged Recovery Model' on BOL to
see if that can help in your specific case.
Ben Nevarez, MCDBA, OCP
Database Administrator
"nick" wrote:

> insert A (...) select ... from B inner join C on ....
> will create huge transact log. Any better way?|||On Sun, 26 Mar 2006 14:49:44 -0800, nick wrote:

>insert A (...) select ... from B inner join C on ....
>will create huge transact log. Any better way?
Hi Nick,
If you can't use bulk insert in your situation, try doing it in batches:
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn <= 10000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn > 10000
AND SomeColumn <= 20000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
(...)
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn > 90000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
If your recovery model is simple, you don't need the backup log
commands. For full or bulk-logged recovery, uncomment these statements.
Make sure that this batch is NOT included in a transaction. If you need
to be able to rollback the entire operation in case of any error, make a
full database backup just before starting and restore to it if things go
wrong.
Hugo Kornelis, SQL Server MVP

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications? I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!
"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)

> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications? I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)

> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.
|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.p hx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pas
s
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)

> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)

> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%23
0eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>

Best way to insert large amounts of data from a webform to SQL Server 2005

Hi

I have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).

What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.

Any ideas?
Thanks
Ed

Hi,

If you gonna insert or update in single table then use SQL Data adapter. But it needs to have primary or unique key to create update query via sql command builder otherwise its just insert all rows in your table.

If you want to update for above scenario there are two ways.

1. Create staging table in your database and insert your records using sqlbulkcopy class which can insert lakhs of data/s (i'm not sure abt amt), then create a stored procedure to update or insert rows from staging table to main table

2. You can write your update and insert command in sqldataadapter.sqlcommand property.

for example

create a table as testtable with id number and name as text

Sql query will be like this

update testtable
set name = @.namecolumn
Where id = @.idColumn
If @.@.Rowcount = 0
Insert into testtable (id,name) values(@.idColumn,@.nameColumn)

then add sql parameters(@.idcolumn,@.nameColumn) with your data table column names

then adapter will execute the query while you call update method

hope it helps

Best way to insert data to MSDE db

Hello.
I'm creating a stand-alone windows application using the MSDE server.
Performance issues in this application are big-deal to my client.
I need to insert data to the database in rate of – 2MB / sec.
Each insert action should insert ~ 10-40 MB to the database.
My questions are:
1. What is the fastest way to INSERT data to the database? Using insert from
c# code, scripts, stored procedure, other?
2. What is the fastest way to retrieve information from the database?
Thanks.
hi,
Eli wrote:
> Hello.
> I'm creating a stand-alone windows application using the MSDE server.
> Performance issues in this application are big-deal to my client.
> I need to insert data to the database in rate of - 2MB / sec.
> Each insert action should insert ~ 10-40 MB to the database.
> My questions are:
> 1. What is the fastest way to INSERT data to the database? Using
> insert from c# code, scripts, stored procedure, other?
nope... the fastest insert method is BULK INSERT (via BULK INSERT statement
or BCP.exe) that can even advantage from combined CPUs... and using BULK
LOGGED recovery model will help ...

> 2. What is the fastest way to retrieve information from the database?
data retrival is demanded to SELECT statements, and you have non other
ways.. you can optimize your index design to make the application works
better, you can even over-index your structure, but this will slow down
insertions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the quick response.
Can you direct me to a link of how to implement the command by code ?
I'm working with C#, and need to send the data from the code.
Do you know of a way to insert the data has an object?
I mean, the rows I want to insert are allready in a class/struct format, and
sending them as-is to the database, insetd of parsing them to insert command,
will be, I think,a fast way. What do you think?
"Andrea Montanari" wrote:

> hi,
> Eli wrote:
> nope... the fastest insert method is BULK INSERT (via BULK INSERT statement
> or BCP.exe) that can even advantage from combined CPUs... and using BULK
> LOGGED recovery model will help ...
>
> data retrival is demanded to SELECT statements, and you have non other
> ways.. you can optimize your index design to make the application works
> better, you can even over-index your structure, but this will slow down
> insertions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
Eli wrote:
> Thanks for the quick response.
> Can you direct me to a link of how to implement the command by code ?
> I'm working with C#, and need to send the data from the code.
> Do you know of a way to insert the data has an object?
> I mean, the rows I want to insert are allready in a class/struct
> format, and sending them as-is to the database, insetd of parsing
> them to insert command, will be, I think,a fast way. What do you
> think?
>
unfortunately BULK operations only supports flat data format and not
structured formatted data... you can use CSV, native SQL Server exported
data via BCP and so on, but not data from xml files and the like...
BULK operations accept a (txt) file to be imported, as described in
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Best way to insert data into tables without primary keys

I am working on a SQL Server database in which there are no primary
keys set on the tables. I can tell what they are using for a key. It
is usually named ID, has a data type of int and does not allow nulls.
However, since it is not set as a primary key you can create a
duplicate key.
This whole thing was created by someone who is long gone. I don't
know how long I will be here and I don't want to break anything. I
just want to work with things the way they are.
So if I want to insert a new record, and I want the key, which is
named ID, to be the next number in the sequence, is there something I
can do in an insert sql statement to do this?Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
See .programming
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tom c" <tomcarr1@.gmail.com> wrote in message
news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...
>I am working on a SQL Server database in which there are no primary
> keys set on the tables. I can tell what they are using for a key. It
> is usually named ID, has a data type of int and does not allow nulls.
> However, since it is not set as a primary key you can create a
> duplicate key.
> This whole thing was created by someone who is long gone. I don't
> know how long I will be here and I don't want to break anything. I
> just want to work with things the way they are.
> So if I want to insert a new record, and I want the key, which is
> named ID, to be the next number in the sequence, is there something I
> can do in an insert sql statement to do this?
>|||Will do Arnie. Sorry for the double post. I found .programming after I
had already posted here and then realized it was a better place for the
question and re posted it there. I won't do it again. Thanks for you
help.
Arnie Rowland wrote:
> Often, the quality of the responses received is related to our ability to
> 'bounce' ideas off of each other. In the future, to prevent folks from
> wasting time on already answered questions, please:
> Don't post to multiple newsgroups. Choose the one that best fits your
> question and post there. Only post to another newsgroup if you get no answer
> in a day or two (or if you accidentally posted to the wrong newsgroup), and
> indicate that you've already posted elsewhere.
> If you really think that a question belongs into more than one newsgroup,
> then use your newsreader's capability of multi-posting, i.e., posting one
> occurrence of a message into several newsgroups at once. If you multi-post
> appropriately, answers 'should' appear in all the newsgroups. Folks
> responding in different newsgroups will see responses from each other, even
> if the responses were posted in a different newsgroup.
>
> See .programming
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "tom c" <tomcarr1@.gmail.com> wrote in message
> news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...
> >I am working on a SQL Server database in which there are no primary
> > keys set on the tables. I can tell what they are using for a key. It
> > is usually named ID, has a data type of int and does not allow nulls.
> > However, since it is not set as a primary key you can create a
> > duplicate key.
> >
> > This whole thing was created by someone who is long gone. I don't
> > know how long I will be here and I don't want to break anything. I
> > just want to work with things the way they are.
> >
> > So if I want to insert a new record, and I want the key, which is
> > named ID, to be the next number in the sequence, is there something I
> > can do in an insert sql statement to do this?
> >

Best way to insert data into tables without primary keys

I am working on a SQL Server database in which there are no primary
keys set on the tables. I can tell what they are using for a key. It
is usually named ID, has a data type of int and does not allow nulls.
However, since it is not set as a primary key you can create a
duplicate key.
This whole thing was created by someone who is long gone. I don't
know how long I will be here and I don't want to break anything. I
just want to work with things the way they are.
So if I want to insert a new record, and I want the key, which is
named ID, to be the next number in the sequence, is there something I
can do in an insert sql statement to do this?Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
See .programming
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tom c" <tomcarr1@.gmail.com> wrote in message
news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...
>I am working on a SQL Server database in which there are no primary
> keys set on the tables. I can tell what they are using for a key. It
> is usually named ID, has a data type of int and does not allow nulls.
> However, since it is not set as a primary key you can create a
> duplicate key.
> This whole thing was created by someone who is long gone. I don't
> know how long I will be here and I don't want to break anything. I
> just want to work with things the way they are.
> So if I want to insert a new record, and I want the key, which is
> named ID, to be the next number in the sequence, is there something I
> can do in an insert sql statement to do this?
>|||Will do Arnie. Sorry for the double post. I found .programming after I
had already posted here and then realized it was a better place for the
question and re posted it there. I won't do it again. Thanks for you
help.
Arnie Rowland wrote:[vbcol=seagreen]
> Often, the quality of the responses received is related to our ability to
> 'bounce' ideas off of each other. In the future, to prevent folks from
> wasting time on already answered questions, please:
> Don't post to multiple newsgroups. Choose the one that best fits your
> question and post there. Only post to another newsgroup if you get no answ
er
> in a day or two (or if you accidentally posted to the wrong newsgroup), an
d
> indicate that you've already posted elsewhere.
> If you really think that a question belongs into more than one newsgroup,
> then use your newsreader's capability of multi-posting, i.e., posting one
> occurrence of a message into several newsgroups at once. If you multi-post
> appropriately, answers 'should' appear in all the newsgroups. Folks
> responding in different newsgroups will see responses from each other, eve
n
> if the responses were posted in a different newsgroup.
>
> See .programming
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "tom c" <tomcarr1@.gmail.com> wrote in message
> news:1157578232.173886.295680@.m79g2000cwm.googlegroups.com...

Best way to insert a DateModified in updates

how do i update the date modified Field in my DB. i was thinking of update trigger? i am using ASP 2 with DetailsView for Editing(Updates in SQL term). i can make a Update SP but think that might not be needed

If you use the SP you can define the date now to a variable. @.datenow = GetDate()|||

In the Update procedure, you can just "SET dateModified=GETDATE()".

In my view, Triggers are a little bad in the respect that they are hidden. If you look at a simple SQL statement, you really can't "trace" the code execution completely if your DB has a whole pile of Triggers firing all the time, which means, things might be happening that the SQL statement writer isn't aware of, and that can cause problems.

Of course, their not overall bad, but my personal preference is to use an SP, unless a Trigger is specifically required or for some odd reason more effecient.

Best Way to handle UID to another server

I have a need when a Update, Insert or Delete is done to a record in DB "A", it will send the appropriate UID to a different table in different DB "B".

My first thought was to have a trigger on the table in DB "A" simply call a stored procedure on DB "B" and do the UID.

However - my question is what is the best approach and what's the best way to establish the connection to DB "B" for the UID from within DB "A"? We can't use linked servers - DNSLESS string would be the preferred connect way. Not sure how to execute it within a trigger.

Is even using a Trigger to Stored Proc the best way?

What about Transaction Replication - which I've never attempted - is that a better way?

Just looking for some guidance here so I don't needlessly burn time down a path that isn't recommended or simply won't work.

Thanks as always for your input,

PeterI would never suggest using triggers that reference objects outside of their own database. A recipe for disaster.
A better strategy would be to have the trigger load your data into a staging table, and then have a job scheduled once per minute to transfer the data from the staging table to the remote server. This is very robust if you can handle latency of up to one minute (scheduled jobs can be set to run once per minute).
If you require zero latency, then I would look into replication as a solution.|||Thanks Blindman:

I did some BOL checking and replication sounds good, but the table the data needs to go into has different field names and is in use in another application. The target DB happens to be on the same server.

What about have a trigger call a stored procedure and pass the fields in question along with the key to the SP?

The SP would make the connection to the other DB and do the UID.

You remain helpful,

Peter|||I still don't like it. What you are trying to avoid is having your trigger fail and system "A" come to a crashing halt when system "B" goes down or the connection fails. Your solution needs to be strong enough to fail safely, or you are just doubling or trippling the possible points of failure.
The advantage of the staging tables is that, if the connection to system "B" is lost then system "A" continues merrily on, loading data into the staging tables. When system "B" comes back online the data in the staging tables is automatically transferred.
No downtown for system "A".
No data-loss for system "B".
No late-night hours getting both systems back in synch for "DBA".
If you can stand up to a 1 minute latency, this is the way to go.
If you can't handle the latency, then replicate to duplicate tables in system "B" and put the triggers on THEM.|||Blindman:

I can stand a latency of even up to 5 minutes.

My concerns:

1. The table I'm feeding updates to has a different name, naming scheme and field types. I'll need to manipulate the data I'm "pushing" to Server B regardless of the approach I take.
2. With #1, I didn't know if Replication can handle. Perhaps "Transactional Replication" isn't the proper type of replication for this
3. I'm totally new to Replication and it doesn't look trivial via BOL.

Any additional help here is appreciated.

I'm event considering having the application update the server B tables directly. I don't like that for a number of reasons including if someone updates the back-end directly (unlikely - but possible), it wouldn't be updating server B.

Peter|||The staging table method should handle all your concerns. I would recommend transforming the data when it is shuttled from the staging tables to the destination database, rather than when it is inserted into the staging tables, as you want your triggers to be as streamlined as possible.

Best way to do a dynamic bulk insert to a table

My current project is creating a social network for the university I work for. One of the features allows members of a group to send a message to all other group members. Currently, I run a foreach loop over each of the group members, and run a separate INSERT statement to insert a message into my messages table. Once the group has several hundreds members, everybody starts getting timeout errors. What is the best way to do this?

Here are two suggestions I've received: construct one sql statement that would contain multiple INSERT statements. It would be a large statement like:

INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user, @.to_user, @.subject, @.body); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user2, @.to_user2, @.subject2, @.body2); INSERT into [messages] (from_user, to_user, subject, body) VALUES (@.from_user3, @.to_user3, @.subject3, @.body3);

etc...

Or, do the foreach loop in a stored procedure. I know the pros and cons of sprocs versus dynamic sql is a sticky subject, and, personally, I'd prefer to keep my logic in the C# code-behind file. What is the best way to do this is an efficient manner? I'd be happy to share some code, if that would help. Thanks for your input!

I think what you want is something like this. 1 single query.

INSERT into [messages] (from_user, to_user, subject, body)
SELECT @.from_user,userid,@.subject,@.body FROM groupmembers WHERE groupid=@.groupid AND userid<>@.from_user

Your input parameters:

@.from_user = the sending user
@.subject = the subject
@.body = the body
@.groupid=the recieving groupid

|||

If you going to insert data to a table that alreay has some data, I suggest you to use whatgunteman wrote.

If not, I suggest you to use:

1SELECT *2INTO MyNewTable-- it will be created automatically here3FROM MyTable1 t14INNERJOIN5 MyTable2 t26ON (t1.rid = t2.rid)7

Good luck.

|||

Thanks that worked like a charm. I can't believe I never thought of putting a subquery in an insert statement.