Can I Insert/Update Large Text Field To Database Without Bulk Insert?

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

You can't just use a plain old update statement and set the column = a parameter of the correct datatype?

|||

How do you indicate that a SqlParameter is of type nvarchar(max)? Any numeric length up to 4000 is easy, but beyond that I've come up empty.

|||

When I add a parameter to a command, I use the AddWithValue method instead of the Add method. That way I don't have to type in the datatype and the length, I just pass it text and it works.

It's possible that it will truncate on you using that method, but I've used it with ntext and long text values before

|||

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar)

or

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar,-1)

|||

cmd.Parameters.AddWithValue("@.Blobby",myTextBox.Text)

(or any other object's value instead of myTextBox)

|||

I normally don't recommend AddWithValue because it can cause some problems when it's unclear what the conversions (if any) should be. This comes into play when the result to be passed could possibly be a nvarchar or a more specific data type (integers, dates). Under certain circumstances, .NET decides to send the data to SQL Server as a nvarchar, and when it gets there, it realizes that it needs to be converted to a more specific data type, but the information needed to do the conversion correctly (because of culture formatting) isn't available on the server, or it uses the servers culture rather than the culture of the running page.

Using .Add with a specified datatype insures that the data conversion is done by .NET before sending the parameter on to SQL Server.

Can I Insert Line Numbers?

Hi. We are creating long reports that need to have unique reference numbers
assigned to each row. For example, the values 1-99 below would be computed at
report run time:
1 Transaction-A
2 Transaction-B
:
99 Transaction-x
I thought I could solve this by using a global variable and function within
the report code block such as:
Private Dim itemCount As Integer
Public Function GetCount() As Integer
itemCount += 1
Return itemCount
End Function
And then create a calculated field (Called LineNumber) that calls the
function:
=Code.GetCount()
But what I get is a seemingly random assignment of line numbers; I assume
due to ReptSvcs resolving the rows in a non-linear fashion.
Any thoughts on how I can get ordered line numbers?Try using static variables in the code
>--Original Message--
>Hi. We are creating long reports that need to have unique
reference numbers
>assigned to each row. For example, the values 1-99 below
would be computed at
>report run time:
>1 Transaction-A
>2 Transaction-B
> :
>99 Transaction-x
>I thought I could solve this by using a global variable
and function within
>the report code block such as:
>Private Dim itemCount As Integer
>Public Function GetCount() As Integer
> itemCount += 1
> Return itemCount
>End Function
>And then create a calculated field (Called LineNumber)
that calls the
>function:
>=Code.GetCount()
>But what I get is a seemingly random assignment of line
numbers; I assume
>due to ReptSvcs resolving the rows in a non-linear
fashion.
>Any thoughts on how I can get ordered line numbers?
>
>
>.
>|||The problem with statics in embedded code is that they are shared
among all instances of the report that are running. If two of the
reports using the static execute at the same time the results could
interleave.
--
Scott
http://www.OdeToCode.com
On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
wrote:
>Try using static variables in the code|||Thanks for the tips, but I think I got it working.
I used the same code as I mentioned below, but added an "ORDER BY"
quailifier to the dataset to sort the data in the same sequence as the report
displayed. This gave me sequentual numbers.
"Scott Allen" wrote:
> The problem with statics in embedded code is that they are shared
> among all instances of the report that are running. If two of the
> reports using the static execute at the same time the results could
> interleave.
> --
> Scott
> http://www.OdeToCode.com
> On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
> wrote:
> >Try using static variables in the code
>sql

Can I insert into the same table a new row with the "old" row field value?

I'm trying to insert values into the same database from itself.
Essentially, I want to add another row to the table for each row with
reg_cat_id = 3. But in this row, I want the original registration_id
to show up in the new row.
Here is my syntax below - this generates an error:
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
'05/05/2007')
SELECT REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
FROM Registration_Category t1
WHERE (REG_CAT_ID = 3)
ORDER BY REGISTRATION_ID
Any suggestions?On 30 Mar 2006 14:40:12 -0800, Dee wrote:
(snip)
>Here is my syntax below - this generates an error:
>INSERT INTO Registration_Category
> (REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
>VALUES (90, t1.REGISTRATION_ID, 'test', '05/05/2007', 'Y',
>'05/05/2007')
> SELECT REGISTRATION_ID, STAFF_ID,
>REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE
> FROM Registration_Category t1
> WHERE (REG_CAT_ID = 3)
> ORDER BY REGISTRATION_ID
>Any suggestions?
INSERT INTO Registration_Category
(REG_CAT_ID, REGISTRATION_ID, STAFF_ID,
REGISTRATION_DATE, APPROVAL_STATUS, APPROVEDDATE)
SELECT 90, t1.REGISTRATION_ID, 'test',
'20070505', 'Y', '20070505')
FROM Registration_Category AS t1
WHERE REG_CAT_ID = 3
Hugo Kornelis, SQL Server MVP

Can I INSERT INTO a temp tbl twice in one stored procedure

In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl?

Any help here is appreciated.

You should be able to insert into the temp table twice, or use a union to just do one insert. Have you actually tried and got an error? If so can you post the error you got.|||The error in my stored procedure is:

There is already an object named '#temp_UN' in the database.

I'm trying to do something like this:

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE...

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE... (This select has a different FROM

SELECT ... FROM tblClient INNER JOIN tblClient.ClientID = #temp_UN.ClientID WHERE...

if

object_id('tempdb..#temp_UN','U')isnotnull

drop

table #temp_UNGifts|||

For your second insert into the temp table you should be able to use the 'Insert into .. select .. ' syntax

Something like this:

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE...

INSERT INTO #temp_UN SELECT ClinetID, etc...FROM ... WHERE... (This select has a different FROM)

SELECT ... FROM tblClient INNER JOIN tblClient.ClientID = #temp_UN.ClientID WHERE...

HTH

|||

Thank you Steve, it looks like it was the order of things.

Can I insert data from a report with RS2005?

Hi All,
Here's my problem:
I need to pull some items into a table report. I have the items grouped
by a key field (AlertID) and a detail the the user can drilldown to for
each item. I'm having trouble with the linking, I try to link this
report to another report that takes the parameters and runs a stored
proc to insert the data for the item (Alert History). Each report works
fine on by itself, but I can't get them to link in anyway, I've tried
Jump to Report and Jump to URL.
Any suggestions would be greatly appreciated.
Thanks,
Damien Johnston
P.S. I also need know if there is a writable textbox and a checkbox
control available in RS?In general this is a bad idea (trying to write data). Some issues you would
have to deal with are: multi-user, cleaning up data when done, etc.
Given what you say I don't see why you need to do this. If your stored
procedure can figure out the data to insert then why can't you just have a
stored procedure returning the appropriate data to the report. Instead of
drill down you should be using drill through. Drill through is much more
efficient. Have a field that you highlight and color blue. Users understand
that means to click on it. Then use jump to report to call up the report
with the detail information.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dij0674" <dij0674@.hotmail.com> wrote in message
news:1161026120.846569.113660@.e3g2000cwe.googlegroups.com...
> Hi All,
> Here's my problem:
> I need to pull some items into a table report. I have the items grouped
> by a key field (AlertID) and a detail the the user can drilldown to for
> each item. I'm having trouble with the linking, I try to link this
> report to another report that takes the parameters and runs a stored
> proc to insert the data for the item (Alert History). Each report works
> fine on by itself, but I can't get them to link in anyway, I've tried
> Jump to Report and Jump to URL.
> Any suggestions would be greatly appreciated.
> Thanks,
> Damien Johnston
> P.S. I also need know if there is a writable textbox and a checkbox
> control available in RS?
>|||Bruce--Thanks for the reply, I'll look at alternative methods to solve
the problem.
Here are the details of what's required and what I have in place:
I have a database monitoring app the alerts when an event occurs (like
profiler)
I have specific events that need to be trapped and a historical record
needs to be kept of the original event along with any updates to an
event history field. These events are stored in a SQL DB
I need to be able to allow certain users to access this via some sort
of form/webpage, and update the events they are responsible for with
documentation, such as an email or an uploaded file.
I need to be able have a single parent to many children relationship
for the events. For example, event 1 can be chosen as the parent for
events 2,3,4,5,6 and event 1's event history will become the history
of the child events.
I sure I can get the SQL side of things, stored procs and table design.
But I'm struggling with a front end for my users; I was hoping that
RS had the ability to function in this way.
Any suggestions would be greatly appreciated.
Thanks Again,
Damien Johnston
Bruce L-C [MVP] wrote:
> In general this is a bad idea (trying to write data). Some issues you would
> have to deal with are: multi-user, cleaning up data when done, etc.
> Given what you say I don't see why you need to do this. If your stored
> procedure can figure out the data to insert then why can't you just have a
> stored procedure returning the appropriate data to the report. Instead of
> drill down you should be using drill through. Drill through is much more
> efficient. Have a field that you highlight and color blue. Users understand
> that means to click on it. Then use jump to report to call up the report
> with the detail information.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "dij0674" <dij0674@.hotmail.com> wrote in message
> news:1161026120.846569.113660@.e3g2000cwe.googlegroups.com...
> > Hi All,
> >
> > Here's my problem:
> > I need to pull some items into a table report. I have the items grouped
> > by a key field (AlertID) and a detail the the user can drilldown to for
> > each item. I'm having trouble with the linking, I try to link this
> > report to another report that takes the parameters and runs a stored
> > proc to insert the data for the item (Alert History). Each report works
> > fine on by itself, but I can't get them to link in anyway, I've tried
> > Jump to Report and Jump to URL.
> >
> > Any suggestions would be greatly appreciated.
> >
> > Thanks,
> >
> > Damien Johnston
> >
> > P.S. I also need know if there is a writable textbox and a checkbox
> > control available in RS?
> >

Can I GROUP BY aggregate Function (Like SUM)

Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yetyes, with a derived table, but why would you want to?

please show an example, using sample data to illustrate|||this is my case:

i have a table with a column datetime (I use to record time of calls) , i want to create table that hold data for each hour, so i round the column and want to group by this column in the same time|||you can round a datetime? please show your query|||This is the Query of round datetime

DATEADD(Hour, DATEDIFF(Hour, 0, cdrCallDate), 0)

You can change [hour] to day , minutes , second , etc...|||yes, you can GROUP BY that expression :)

and you cannot use second, it causes an overflow

:)|||thanks man for this information

Can I Force SQL to Accept INSERT List of Values Less Than Number of Columns?

I have a canned application that does INSERTs with lists of values without
column lists. The table has one additional (uniqueidentifier) column for merge
replication, so the application is inserting 7 values, but there are 8
columns. Is there a way to tell SQL to accept it anyway and just fill the
columns from left to right until it runs out of data? I hope so, because I
have no access to the source code.
--EricHello Eric. you could try with renaming that table (the one you insert in)
and creating a view with old name of the table you just renamed. In the view
definition specify all fields from the renamed table except the one that you
added for replication(uniqueidentifier) .
Hope this works,
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379450577312037@.news.microsoft.com...
> I have a canned application that does INSERTs with lists of values without
> column lists. The table has one additional (uniqueidentifier) column for
merge
> replication, so the application is inserting 7 values, but there are 8
> columns. Is there a way to tell SQL to accept it anyway and just fill the
> columns from left to right until it runs out of data? I hope so, because I
> have no access to the source code.
> --Eric
>|||If there isn't a column list specified in the INSERT then the number of
columns in the table must match the number of columns in the INSERT
statement (less the IDENTITY column, if any).
You could set a default for the uniqueidentifier column, rename the table
and then create a view under the original name containing all except the
extra column:
CREATE TABLE newname (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, c
UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ...)
CREATE VIEW oldname
AS
SELECT a,b
FROM newname
Then find the programmer and make him fix his code.
--
David Portas
--
Please reply only to the newsgroup
--|||"Tomislav Kralj" <tomislav.kralj1@.zg.tel.hr> wrote in message
news:bpi4j6$vq0$1@.sunce.iskon.hr...
> Hello Eric. you could try with renaming that table (the one you insert in)
> and creating a view with old name of the table you just renamed. In the
view
> definition specify all fields from the renamed table except the one that
you
> added for replication(uniqueidentifier) .
Oh, and i forgot. create view with VIEW_METADATA option !!!
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hrsql

Can I determine insert order without an explicit field

I want to be able to find the differences between the before and after
values in a table as updates occur. I thought an easy way to do this
would be to create another table with an identical structure and then
use an update trigger to insert the deleted and inserted rows into
that alternate table. I know what order the rows are in the table
since I will put them in there but how can I, without a time value
column, know which one was inserted into the table first?You can't. It would be very easy to add a column, e.g., InsertDate, with a
default of CURRENT_TIMESTAMP. In your alternate table you wouldn't need a
default on it.
HTH
Vern Rabe
"Computer User" wrote:

> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?
>|||ordering is an aspect of data selection, so you need some sort of
ordering column to indicate time based data.
there's no such thing intrinsically in a sql table as a row number, so
you really don't know what order the rows are in the table.
why wouldn't you want a datetime stamp column?
if you care that data was changed, wouldn't you want to know when it
changed?
you'll probably also want an indicator for which row it was
[deleted/inserted]
Computer User wrote:
> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?|||On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:

>ordering is an aspect of data selection, so you need some sort of
>ordering column to indicate time based data.
>there's no such thing intrinsically in a sql table as a row number, so
>you really don't know what order the rows are in the table.
>
I know that selection usually includes an "order by" clause, but the
data must be in the db in some order.

>why wouldn't you want a datetime stamp column?
>if you care that data was changed, wouldn't you want to know when it
>changed?
>
In this instance, I don't care when the data was changed, only that it
was. A web application is supposed to send an email to an
administrator showing db modifications. Having a "before" row and an
"after" row would make this easy.

>you'll probably also want an indicator for which row it was
>[deleted/inserted]
>
If I knew the order I would know which row it was because I will
insert the deleted row before the inserted row.|||email notifications aren't necessarily terribly reliable.
I find it advisable to have a screen ( as well ) where you can see
notifications.
If you want to be sure of the order then I suggest writing to a log
file would be better than a table.
The order that data is in will not be useful otherwise.
I would recommend creating a table which has a bunch of fields for
before and the same again for after.
Plus your primary (unique ) key, a datestamp and change indicator (
Insert, Update, Delete ).
Write this with your trigger.
What I'd do with it then depends on how dynamic the data is.
I would hope that it's not very dynamic of all this is almost certainly
a complete waste of time.
Anyhow.
Stick a screen on the front of your app that the administrator only
sees with the changes from yesterday and today presented on it.
Use the timestamp to drive the selection.|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
It's in the database in some order, true. But there is no guarantee of
the order in which the server will retrieve rows, unless you impose an
ordering. It is *entirely* up to the server in what order it returns a
set of rows, and the order you receive them in may depend on server
version, patches, number of processors, *workload*, *data volumes*,
*indexes* and *statistics*. (the * ones are ones likely to change just
in the day-to-day use of a database). So if you need to retrieve data
in an order based on when it was inserted, you best record that
information.
In general, for small tables, your data will be returned to you in the
order determined by the clustered index (if it exists), or the order in
which data was inserted (if no clustered index). However, this is for
very small tables (I think as soon as you start using two pages, the
server can start reordering the rows as it sees fit, but not sure)
Damien|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
>
no, it's not. it's wherever the dbms put it. it could be in order, it
might not be, even for clustered indexes.
there is no intrinsic row number or insertion order. if you want one,
you have to add one.

> In this instance, I don't care when the data was changed, only that it
> was. A web application is supposed to send an email to an
> administrator showing db modifications. Having a "before" row and an
> "after" row would make this easy.
>
so what's the problem with adding a column that will help you?
"i don't care when the data was changed..." - famous last words :)

> If I knew the order I would know which row it was because I will
> insert the deleted row before the inserted row.
if you really do not care and can honestly say that you will never care
when the data was changed, then you could add an identity column to your
auditing table.
your better bet would be a single row with before and after values for
each column being audited.

Can I create a Top n statement within a stored procedure using a parameter?

In a 'Top n' type statement I wish to be able to insert the n value
from a parameter, within a stored precedure eg

Having declared @.pageSize as a parameter I want to run the following
type of query :

SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
tblRoute_Header

When I attempt to do so I get an error mesage indicating incorrect
syntax. I do not get an error message if I specify 'n' directly as in
TOP 10

Am I missing something or is this not possible within a stored
procedure?

Best wishes, John MorganOn Mon, 12 Apr 2004 16:45:26 +0100, John Morgan wrote:

>
>In a 'Top n' type statement I wish to be able to insert the n value
>from a parameter, within a stored precedure eg
>Having declared @.pageSize as a parameter I want to run the following
>type of query :
>SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>tblRoute_Header
>When I attempt to do so I get an error mesage indicating incorrect
>syntax. I do not get an error message if I specify 'n' directly as in
>TOP 10
>Am I missing something or is this not possible within a stored
>procedure?
>Best wishes, John Morgan

The TOP clause will only take an integer value, not a variable.

There are two other ways to limit your output to @.pageSize rows:

1. Using proprietary syntax, not portable to other DBMS's

SET ROWCOUNT @.pageSize
SELECT DISTINCT routeID, routeName
FROM tblRoute_Header
WHERE ...
ORDER BY ...
SET ROWCOUNT 0

Note 1: Don't forget to SET ROWCOUNT 0 afterwards, or else all other
queries you execute will be limited to @.pageSize rows of output.
Note 2: Don't leave out the order by clause, or else your output will
be unpredictable. Result sets, like tables, are unordered by default.
If you get the first 10 from an unordered collection, there's no way
of predicting which 10 it will be, nor can anybody guarantee that
you'll get the same 10 if you get "the first 10" again.

2. Using ANSI-standard syntax:

SELECT DISTINCT routeID, routeName
FROM tblRoute_Header AS RH1
WHERE ...
AND (SELECT COUNT(*)
FROM tblRoute_Header AS RH2
WHERE RH2.routeID < RH1.routeID) < @.pageSize
ORDER BY routeID

Note 1: This is based on assumptions re your data structure. You need
to adapt it to your actual situation.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> In a 'Top n' type statement I wish to be able to insert the n value
> from a parameter, within a stored precedure eg
> Having declared @.pageSize as a parameter I want to run the following
> type of query :
> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
> tblRoute_Header
> When I attempt to do so I get an error mesage indicating incorrect
> syntax. I do not get an error message if I specify 'n' directly as in
> TOP 10
> Am I missing something or is this not possible within a stored
> procedure?
> Best wishes, John Morgan

TOP doesn't allow parameters, but SET ROWCOUNT does:

SET ROWCOUNT @.n

SELECT ...

SET ROWCOUNT 0

Don't forget to set ROWCOUNT back to zero immediately after your query, or
all the following statements will be affected too. Note that TOP without
ORDER BY, as in your example above, returns random rows - there is no
guarantee that you will get what you expect without the ORDER BY.

Simon|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> In a 'Top n' type statement I wish to be able to insert the n value
> from a parameter, within a stored precedure eg
> Having declared @.pageSize as a parameter I want to run the following
> type of query :
> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
> tblRoute_Header
> When I attempt to do so I get an error mesage indicating incorrect
> syntax. I do not get an error message if I specify 'n' directly as in
> TOP 10
> Am I missing something or is this not possible within a stored
> procedure?
> Best wishes, John Morgan

TOP doesn't allow parameters, but SET ROWCOUNT does:

SET ROWCOUNT @.n

SELECT ...

SET ROWCOUNT 0

Don't forget to set ROWCOUNT back to zero immediately after your query, or
all the following statements will be affected too. Note that TOP without
ORDER BY, as in your example above, returns random rows - there is no
guarantee that you will get what you expect without the ORDER BY.

Simon|||Thank you Simon for your help - appreciated,

Best wishes, John Morgan

On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
>news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>>
>>
>> In a 'Top n' type statement I wish to be able to insert the n value
>> from a parameter, within a stored precedure eg
>>
>> Having declared @.pageSize as a parameter I want to run the following
>> type of query :
>>
>> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>> tblRoute_Header
>>
>> When I attempt to do so I get an error mesage indicating incorrect
>> syntax. I do not get an error message if I specify 'n' directly as in
>> TOP 10
>>
>> Am I missing something or is this not possible within a stored
>> procedure?
>>
>> Best wishes, John Morgan
>TOP doesn't allow parameters, but SET ROWCOUNT does:
>SET ROWCOUNT @.n
>SELECT ...
>SET ROWCOUNT 0
>Don't forget to set ROWCOUNT back to zero immediately after your query, or
>all the following statements will be affected too. Note that TOP without
>ORDER BY, as in your example above, returns random rows - there is no
>guarantee that you will get what you expect without the ORDER BY.
>Simon|||Thank you Simon for your help - appreciated,

Best wishes, John Morgan

On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
>news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>>
>>
>> In a 'Top n' type statement I wish to be able to insert the n value
>> from a parameter, within a stored precedure eg
>>
>> Having declared @.pageSize as a parameter I want to run the following
>> type of query :
>>
>> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>> tblRoute_Header
>>
>> When I attempt to do so I get an error mesage indicating incorrect
>> syntax. I do not get an error message if I specify 'n' directly as in
>> TOP 10
>>
>> Am I missing something or is this not possible within a stored
>> procedure?
>>
>> Best wishes, John Morgan
>TOP doesn't allow parameters, but SET ROWCOUNT does:
>SET ROWCOUNT @.n
>SELECT ...
>SET ROWCOUNT 0
>Don't forget to set ROWCOUNT back to zero immediately after your query, or
>all the following statements will be affected too. Note that TOP without
>ORDER BY, as in your example above, returns random rows - there is no
>guarantee that you will get what you expect without the ORDER BY.
>Simon

Can disable DTC or Transactions when I use OLEDB provider for Oracle?

Hi,
I am trying to improve my data export operation. At this point I need to
disable implicit transactions when I use insert into [Linked Table] command.
How can disable transactions when I do a distributed insert or use DTS to
export table data ?
Thank you,
MaxHello Max,
You could disable transaction by right click the
package->Properties->Advanced, uncheck "transaction"
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Maxwell2006" <alanalan@.newsgroup.nospam>
>Subject: Can disable DTC or Transactions when I use OLEDB provider for
Oracle?
>Date: Tue, 4 Apr 2006 14:18:01 -0400
>Lines: 19
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Message-ID: <eES89OBWGHA.5044@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: mtl-hse-ppp170505.qc.sympatico.ca 65.94.105.17
>Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP0
9.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:426864
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi,
>
>I am trying to improve my data export operation. At this point I need to
>disable implicit transactions when I use insert into [Linked Table]
command.
>
>How can disable transactions when I do a distributed insert or use DTS to
>export table data ?
>
>Thank you,
>Max
>
>

Can connect to Database, but how do I insert values into SQL Database?

I was able to connect to the SQL Database Pension with table clients with table values: ID, State, Name.
'Create(connection)
Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
'open connection
conn.Open()

However, I'm not sure how to insert a new row with an incremental ID number and a new State and Name.

Sorry, I'm really new with VWD.

You need to read about ADO.NET before you do anything.

Google it.

Can bulkload update existing data in a table?

Hi.
I'm new to transfering data from XML to an SQL Database. I'm using

SQLXML Bulkload. I can insert data into the table successfully but I

cannot figure out how to update existing data. Is

this possible in Bulkload or should I use Updategrams instead? If it is, how can go about doing this?

I used Bulkload because I read up that it was good for large

file transfers.

Anyone?

Hi

you cannot really use bulkload for updating directly.... you could either use updategrams, or if you still load lots of data, you could use bulkload into a staging area and then do the updates and final inserts relationally from the staging tables into your final tables.

Best regards

Michael

|||Thanks so much!
I've decided to handle the updates separately and not use updategrams because its too meticulous to deal with especially since I hav to do this for different tables and xml files.

So what I'm doing is having a transition (temporary) table that will update the table. The mapping problem was solved by using Dictionary<>.

Thanks!

Can bulkload update existing data in a table?

Hi.
I'm new to transfering data from XML to an SQL Database. I'm using SQLXML Bulkload. I can insert data into the table successfully but I cannot figure out how toupdate existing data. Is this possible in Bulkload or should I use Updategrams instead? If it is, how can go about doing this?

I used Bulkload because I read up that it was good for large file transfers.

Anyone?

Hi

you cannot really use bulkload for updating directly.... you could either use updategrams, or if you still load lots of data, you could use bulkload into a staging area and then do the updates and final inserts relationally from the staging tables into your final tables.

Best regards

Michael

|||Thanks so much!
I've decided to handle the updates separately and not use updategrams because its too meticulous to deal with especially since I hav to do this for different tables and xml files.

So what I'm doing is having a transition (temporary) table that will update the table. The mapping problem was solved by using Dictionary<>.

Thanks!

Can bulkload update existing data in a table?

Hi.
I'm new to transfering data from XML to an SQL Database. I'm using

SQLXML Bulkload. I can insert data into the table successfully but I

cannot figure out how to update existing data. Is

this possible in Bulkload or should I use Updategrams instead? If it is, how can go about doing this?

I used Bulkload because I read up that it was good for large

file transfers.

Anyone?

Hi

you cannot really use bulkload for updating directly.... you could either use updategrams, or if you still load lots of data, you could use bulkload into a staging area and then do the updates and final inserts relationally from the staging tables into your final tables.

Best regards

Michael

|||Thanks so much!
I've decided to handle the updates separately and not use updategrams because its too meticulous to deal with especially since I hav to do this for different tables and xml files.

So what I'm doing is having a transition (temporary) table that will update the table. The mapping problem was solved by using Dictionary<>.

Thanks!

Can BULK INSERT be used like TEXTCOPY?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
processes the contents of the file, and I really just want to squirt the file in to the
column verbatim.
Thanks for any help you can provide!
John PetersonYes. You can use bulk insert but you need a format file.
e.g.
1 SQLIMAGE 0 999999 "" 2 coln ""
1= entire file
SQLIMAGE= datatype
0= prefix length
999999= file length/size
""= no terminator
2= column ordinal
coln= column name
""= no collation
Thus, the bulk insert looks like this:
bulk insert tb
from 'file.img'
with(formatfile='fmt.fmt')
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of
files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I
could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
construct. BULK
> INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
> processes the contents of the file, and I really just want to squirt the
file in to the
> column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
See this thread for an example:
http://groups.google.com/groups?q=405F-B2C5-7256A4B9870A
Steve Kass
Drew University
John Peterson wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
>INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
>processes the contents of the file, and I really just want to squirt the file in to the
>column verbatim.
>Thanks for any help you can provide!
>John Peterson
>
>|||Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
BTW: Do you need an *exact* binary file size in the format file, as Steve's link
suggests? Or will "oversizing" it suffice?
Thanks again!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
> BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That is,
> it processes the contents of the file, and I really just want to squirt the file in to
> the column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
It's got to be exact. If you oversize it, you get an unexpected
end-of-file:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
SK
John Peterson wrote:
>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>suggests? Or will "oversizing" it suffice?
>Thanks again!
>
>"John Peterson" <j0hnp@.comcast.net> wrote in message
>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
>>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That is,
>>it processes the contents of the file, and I really just want to squirt the file in to
>>the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>
>|||Thanks, Steve!
One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
I have very little experiencing in using VBScript (this is going to be called from a Web
page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
I've seen a few snippets -- but not a comprehensive example. Do you have any
recommendations/links on that route, perchance?
"Steve Kass" <skass@.drew.edu> wrote in message
news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
> John,
> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any information
> about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
> The provider did not give any information about the error.].
> The statement has been terminated.
> SK
> John Peterson wrote:
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell"
>>out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That
>>is, it processes the contents of the file, and I really just want to squirt the file in
>>to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>|||John,
I haven't done this myself, but you should be able to pass the image
data with a adLongVarBinary parameter.
You might need to create the parameter 1 byte longer than the length of
the data you're storing, according to
http://support.microsoft.com/default.aspx?scid=kb;en-us;190450
I also saw one suggestion that this meant you have to read back all but
the last byte of the stored image
value when retrieving the data, so you should be watchful:
http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
SK
John Peterson wrote:
>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
>I have very little experiencing in using VBScript (this is going to be called from a Web
>page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
>I've seen a few snippets -- but not a comprehensive example. Do you have any
>recommendations/links on that route, perchance?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>
>>John,
>> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
>>Server: Msg 4832, Level 16, State 1, Line 1
>>Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
>>Server: Msg 7399, Level 16, State 1, Line 1
>>OLE DB provider 'STREAM' reported an error. The provider did not give any information
>>about the error.
>>OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
>>The provider did not give any information about the error.].
>>The statement has been terminated.
>>SK
>>John Peterson wrote:
>>
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell"
>>out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That
>>is, it processes the contents of the file, and I really just want to squirt the file in
>>to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>
>>
>
>|||Great stuff! Thanks again, Steve! :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:eXQUuyjvEHA.2804@.TK2MSFTNGP14.phx.gbl...
> John,
> I haven't done this myself, but you should be able to pass the image data with a
> adLongVarBinary parameter.
> You might need to create the parameter 1 byte longer than the length of the data you're
> storing, according to
> http://support.microsoft.com/default.aspx?scid=kb;en-us;190450
> I also saw one suggestion that this meant you have to read back all but the last byte of
> the stored image
> value when retrieving the data, so you should be watchful:
> http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
> SK
>
> John Peterson wrote:
>>Thanks, Steve!
>>One other option I'm exploring is using a stored procedure with an IMAGE parameter.
>>But, I have very little experiencing in using VBScript (this is going to be called from
>>a Web page) and ADO Parameter objects for an IMAGE data. I've been searching on the
>>Web, and I've seen a few snippets -- but not a comprehensive example. Do you have any
>>recommendations/links on that route, perchance?
>>
>>"Steve Kass" <skass@.drew.edu> wrote in message
>>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>>John,
>> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
>>Server: Msg 4832, Level 16, State 1, Line 1
>>Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
>>Server: Msg 7399, Level 16, State 1, Line 1
>>OLE DB provider 'STREAM' reported an error. The provider did not give any information
>>about the error.
>>OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
>>The provider did not give any information about the error.].
>>The statement has been terminated.
>>SK
>>John Peterson wrote:
>>
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward.
>>:-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on
>>disk), and I'd like to squirt them in to a table that has an IMAGE column. I could
>>"shell" out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
>>construct. BULK INSERT looked close, but it also looks like the built-in analogue to
>>BCP. That is, it processes the contents of the file, and I really just want to
>>squirt the file in to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>
>>

Can BULK INSERT be used like TEXTCOPY?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
processes the contents of the file, and I really just want to squirt the file in to the
column verbatim.
Thanks for any help you can provide!
John Peterson
Yes. You can use bulk insert but you need a format file.
e.g.
1 SQLIMAGE 0 999999 "" 2 coln ""
1= entire file
SQLIMAGE= datatype
0= prefix length
999999= file length/size
""= no terminator
2= column ordinal
coln= column name
""= no collation
Thus, the bulk insert looks like this:
bulk insert tb
from 'file.img'
with(formatfile='fmt.fmt')
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of
files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I
could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
construct. BULK
> INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
> processes the contents of the file, and I really just want to squirt the
file in to the
> column verbatim.
> Thanks for any help you can provide!
> John Peterson
>
|||John,
See this thread for an example:
http://groups.google.com/groups?q=40...5-7256A4B9870A
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
>INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
>processes the contents of the file, and I really just want to squirt the file in to the
>column verbatim.
>Thanks for any help you can provide!
>John Peterson
>
>
|||Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
BTW: Do you need an *exact* binary file size in the format file, as Steve's link
suggests? Or will "oversizing" it suffice?
Thanks again!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
> BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That is,
> it processes the contents of the file, and I really just want to squirt the file in to
> the column verbatim.
> Thanks for any help you can provide!
> John Peterson
>
|||John,
It's got to be exact. If you oversize it, you get an unexpected
end-of-file:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
SK
John Peterson wrote:

>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>suggests? Or will "oversizing" it suffice?
>Thanks again!
>
>"John Peterson" <j0hnp@.comcast.net> wrote in message
>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>
>
>
|||Thanks, Steve!
One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
I have very little experiencing in using VBScript (this is going to be called from a Web
page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
I've seen a few snippets -- but not a comprehensive example. Do you have any
recommendations/links on that route, perchance?
"Steve Kass" <skass@.drew.edu> wrote in message
news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any information
> about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
> The provider did not give any information about the error.].
> The statement has been terminated.
> SK
> John Peterson wrote:
|||John,
I haven't done this myself, but you should be able to pass the image
data with a adLongVarBinary parameter.
You might need to create the parameter 1 byte longer than the length of
the data you're storing, according to
http://support.microsoft.com/default...b;en-us;190450
I also saw one suggestion that this meant you have to read back all but
the last byte of the stored image
value when retrieving the data, so you should be watchful:
http://groups.google.com/groups?hl=e...dlongvarbinary
SK
John Peterson wrote:

>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
>I have very little experiencing in using VBScript (this is going to be called from a Web
>page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
>I've seen a few snippets -- but not a comprehensive example. Do you have any
>recommendations/links on that route, perchance?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>
>
>
|||Great stuff! Thanks again, Steve! :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:eXQUuyjvEHA.2804@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> John,
> I haven't done this myself, but you should be able to pass the image data with a
> adLongVarBinary parameter.
> You might need to create the parameter 1 byte longer than the length of the data you're
> storing, according to
> http://support.microsoft.com/default...b;en-us;190450
> I also saw one suggestion that this meant you have to read back all but the last byte of
> the stored image
> value when retrieving the data, so you should be watchful:
> http://groups.google.com/groups?hl=e...dlongvarbinary
> SK
>
> John Peterson wrote:

Can BULK INSERT be used like TEXTCOPY?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files
(on disk),
and I'd like to squirt them in to a table that has an IMAGE column. I could
"shell" out
to use TEXTCOPY, but was wondering if I could leverage some built-in SQL con
struct. BULK
INSERT looked close, but it also looks like the built-in analogue to BCP. T
hat is, it
processes the contents of the file, and I really just want to squirt the fil
e in to the
column verbatim.
Thanks for any help you can provide!
John PetersonYes. You can use bulk insert but you need a format file.
e.g.
1 SQLIMAGE 0 999999 "" 2 coln ""
1= entire file
SQLIMAGE= datatype
0= prefix length
999999= file length/size
""= no terminator
2= column ordinal
coln= column name
""= no collation
Thus, the bulk insert looks like this:
bulk insert tb
from 'file.img'
with(formatfile='fmt.fmt')
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of
files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I
could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
construct. BULK
> INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
> processes the contents of the file, and I really just want to squirt the
file in to the
> column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
See this thread for an example:
http://groups.google.com/groups?q=4...C5-7256A4B9870A
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of file
s (on disk),
>and I'd like to squirt them in to a table that has an IMAGE column. I coul
d "shell" out
>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL co
nstruct. BULK
>INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
>processes the contents of the file, and I really just want to squirt the fi
le in to the
>column verbatim.
>Thanks for any help you can provide!
>John Peterson
>
>|||Thanks oj and Steve! I think that'll do the trick, even if it's a little aw
kward. :-)
BTW: Do you need an *exact* binary file size in the format file, as Steve's
link
suggests? Or will "oversizing" it suffice?
Thanks again!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of fil
es (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I cou
ld "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL c
onstruct.
> BULK INSERT looked close, but it also looks like the built-in analogue to
BCP. That is,
> it processes the contents of the file, and I really just want to squirt th
e file in to
> the column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
It's got to be exact. If you oversize it, you get an unexpected
end-of-file:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
SK
John Peterson wrote:

>Thanks oj and Steve! I think that'll do the trick, even if it's a little a
wkward. :-)
>BTW: Do you need an *exact* binary file size in the format file, as Steve'
s link
>suggests? Or will "oversizing" it suffice?
>Thanks again!
>
>"John Peterson" <j0hnp@.comcast.net> wrote in message
>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>
>
>|||Thanks, Steve!
One other option I'm exploring is using a stored procedure with an IMAGE par
ameter. But,
I have very little experiencing in using VBScript (this is going to be calle
d from a Web
page) and ADO Parameter objects for an IMAGE data. I've been searching on t
he Web, and
I've seen a few snippets -- but not a comprehensive example. Do you have an
y
recommendations/links on that route, perchance?
"Steve Kass" <skass@.drew.edu> wrote in message
news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> It's got to be exact. If you oversize it, you get an unexpected end-of-f
ile:
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
information
> about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows retu
rned 0x80004005:
> The provider did not give any information about the error.].
> The statement has been terminated.
> SK
> John Peterson wrote:
>|||John,
I haven't done this myself, but you should be able to pass the image
data with a adLongVarBinary parameter.
You might need to create the parameter 1 byte longer than the length of
the data you're storing, according to
http://support.microsoft.com/defaul...kb;en-us;190450
I also saw one suggestion that this meant you have to read back all but
the last byte of the stored image
value when retrieving the data, so you should be watchful:
http://groups.google.com/groups?hl=...
dlongvarbinary
SK
John Peterson wrote:

>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE pa
rameter. But,
>I have very little experiencing in using VBScript (this is going to be call
ed from a Web
>page) and ADO Parameter objects for an IMAGE data. I've been searching on
the Web, and
>I've seen a few snippets -- but not a comprehensive example. Do you have a
ny
>recommendations/links on that route, perchance?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>
>
>|||Great stuff! Thanks again, Steve! :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:eXQUuyjvEHA.2804@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> John,
> I haven't done this myself, but you should be able to pass the image data
with a
> adLongVarBinary parameter.
> You might need to create the parameter 1 byte longer than the length of th
e data you're
> storing, according to
> http://support.microsoft.com/defaul...kb;en-us;190450
> I also saw one suggestion that this meant you have to read back all but th
e last byte of
> the stored image
> value when retrieving the data, so you should be watchful:
> http://groups.google.com/groups?hl=...adlongvarbinary
> SK
>
> John Peterson wrote:
>

can any experts pl help me out in insert query..

hi, i have written this insert query for inserting data in database : "insert into leavemaster (leave_code,leave_desc,leave_type, leave_days, leave_valid_month, leave_amount, effective_date, max_limit, leave_num, maintain_bal, leave_encash, leave_fq, encash_limit, encash_fq, carry_frwd, negative_bal, max_encash_bal, leave_limit, holiday_lv, weekoff_lv, del_flag" & _
"values (@.leave_code, @.leave_desc, @.leave_type, @.leave_days, @.leave_valid_month, @.leave_amount, @.effective_date, @.max_limit, @.leave_num, @.maintain_bal, @.leave_encash, @.leave_fq, @.encash_limit, @.encash_fq, @.carry_frwd, @.negative_bal, @.max_encash_bal, @.leave_limit, @.holiday_lv, @.weekoff_lv, @.del_flag)"

and then have passed parameters like:

Dim leave_codeParam As New OleDbParameter("@.leave_code", OleDbType.VarChar, 3)
leave_codeParam.Value = txtLeave_code.Text
cmd.Parameters.Add(leave_codeParam)
for each fields of database table..
but syntax error in insert into statemtent is coming still.. can any experts pl help me out...There is a close bracket missing after del_flag

Regards,
J

can an INSERT statement RETURN a value?

I only know a little bit of SQL for Access databases, so sorry if this is a silly question!

Situation: I've to INSERT a new company in a SQL server table named 'companies'. This new company will automatically receive a unique ID (autonumber in Access terminology, don't know how to call it in SQL server)

Question: Can this insert statement return the ID it gave to the company? Or how do I get this ID to use it in an other table?

Thanks in advance!create proc <blah-blah> (@.blah1 varchar(blah), @.blah2 varchar(blah-blah) )
as
declare @.RetVal int, @.Error int
begin tran
insert <blah> (blah1, blah2) values (@.blah1, @.blah2)
select @.Error = @.@.error, @.RetVal = scope_identity()
if @.Error != 0 begin
raiserror ('failed to insert into blah', 16, 1)
rollback tran
return 1
end
commit tran
select NewIdentityValue = @.RetVal
return 0

OR, you can define @.RetVal as output parameter, this way you won't have to do a final SELECT. It's all up to your taste and preference.|||...in other words, NO, it can't, but you can put your Insert statement in a procedure that will return a value or an Output parameter.

blindman|||it actually appears that the answer is YES. explanation blindman?|||We're splitting hairs... yes you can get the ID... from the SELECT statement, no... from a stored procedure... yes...

ClipChips asked if the statement could return the ID. The statement itself cannot. But if you use a SP, you can retrieve it either as a SELECT to a recordset, or an OUTPUT parameter.|||It sounds like the answer is really our favorite "yes and no". Yes, @.@.error acts as a sort of return value, but in the strict definition of a return value, you can not have
exec @.retvalue = "insert into table values (...)" Does that explain it better?|||Yes, No?

You can get Id back without stored procedure!

create table test(id int identity primary key
,code varchar(10))
go
create trigger ins_test on test
for insert
as
select id from inserted
go
insert test values('A')
go
id
----
1

Just get recordset from command object...|||True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)|||Originally posted by Seppuku
True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)

But it is possible... ;)|||you can do it even without a trigger, but from a command object.

create table test1 (f1 int identity(1,1) not null, f2 char(1) not null)
go

Just assign the following command to it:
insert test1 (f2) values ('A') select RetVal=@.@.identity -- or scope_identity() for sql2k

Can an IDENTITY column be updated?

(SQL Server 2000, SP3)
Hello all!
I have a simple table:
create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
insert into Test (Name) values ('Test')
And I'd like to potentially update the IDENTITY column Id:
update Test set Id = 100 where Id = 1
However, I get the following error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Id'.
Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same error.
Is there any way to update a column with an IDENTITY property?
Thanks!
John PetersonThanks Sue! Ah, I see the blurb in BOL that says an IDENTITY can't be updated. Bummer.
:-(
It seems to me that in older versions of SQL Server, one could update a column with the
IDENTITY property. But, no longer (or my memory isn't what it once was ;-).
Thanks again!
John Peterson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:dc1njvsscenku5aoklp93tl6rjptcibdcf@.4ax.com...
> Identity columns can't be updated - I think it's documented
> under the UPDATE topic in BOL T-SQL reference. One possible
> option would be to set identity_insert on, use the existing
> values for a new record and insert the new record with the
> identity value you need to use and then delete the old
> record.
> -Sue
> On Wed, 13 Aug 2003 20:10:46 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3)
> >
> >Hello all!
> >
> >I have a simple table:
> >
> >create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
> >insert into Test (Name) values ('Test')
> >
> >And I'd like to potentially update the IDENTITY column Id:
> >
> >update Test set Id = 100 where Id = 1
> >
> >However, I get the following error:
> >
> >Server: Msg 8102, Level 16, State 1, Line 1
> >Cannot update identity column 'Id'.
> >
> >Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same
error.
> >
> >Is there any way to update a column with an IDENTITY property?
> >
> >Thanks!
> >
> >John Peterson
> >
>