How to insert time in MS SQL DB

hi..

i want to store value of time in the database i.e. the 'time in' and 'time out' value for a particular entry of an employee. i have take datetime as a datatype in MS SQL 2000 database and my language is vb.net. How can i store time value in my database?

There is no datatype strictly for the time. I suggest storing the entire date and time value. What if your employee clocks in at 7:00 PM and clocks out at 3:00 AM?|||You told me write to store time in datetime data type along with date. But in what format should I enter the values if I want to store the values in 24 hrs. time format?|||Well, I didn't tell you to store the data in a datetime data type, but I did suggest it. You can see from thedatetime and smalldatetime documentation that SQL Server stores the data in two 4-byte integers, the first 4 bytes store the offset from the base date (January 1, 1900). The other 4 bytes store the number of milliseconds after midnight.

You might use something like this to set a date/time as a parameter value:

myCommand.Parameters.Add("@.DateTimeIn", SqlDbType.DateTime).Value = DateTime.Parse("2006-03-04 23:01:00")

|||

Datetime values by their nature do not have a "format". It's when you convert the datetime to a string that the string has a "format". (format is quoted because, yes, of course datetime has a format, as tmorton pointed out, but it's not what most people mean)

Just to point this out further, in tmorton's MyCommand.Parameters.Add statement above, you are giving the .value a datetime object. Datetime.Parse("some string"), takes the string in a format that datetime can covert to a valid datetime object, based on your current culture's standard date and time formats. The format tmorton chose (YYYY-MM-DD HH:MM:DD.SSS) is the one least likely to give any problems because it is universally available in all cultures, unlike say MM/DD/YYYY which is available in en-US, or (guessing) DD/MM/YYYY in en-GB, or DD.MM.YYYY in fr-FR (Sorry if these aren't the correct formats for England and/or France).

|||

Hi..

I got my problem soluation but a small problem still remailns.

I am displaying my records in datagrid with code as...

<asp:Label ID="timeinlbl" Width = 30 Runat = server text='<%#DataBinder.Eval(Container,"DataItem.timein","{0:hh:mm}")%>'>

It shows me time in '08:30' format. I want to also display am/pm format. What should I add to display the format 'am/pm'?

|||

geek.net wrote:

What should I add to display the format 'am/pm'?

Starting from here:BoundColumn.DataFormatString Property, I followed the link to:Formatting Overview. From there I followed the link to:Date and Time Format Strings, and then followed the final link to:Custom DateTime Format Strings. This page should help you discover the exact format string you need.

How to insert the value from the text box (ASP.NET 2.0) to the microsoft sql server 2000 d

Hello Friends,

I have a problem with ASP.net with dynamic data transfer from asp page to microsoft sql server 2000. For example , I have asp web page with one text field and a buttion.When I click the buttion, the value entered in the text field should be transfered from the text field to database.

Kindly See the following section c# code ....

SqlConnection objconnect = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = objconnect;

cmd.CommandText = " select * from Table_Age where Age = @.Age";

cmd.CommandType = CommandType.Text;

SqlParameter parameter = new SqlParameter();

parameter.ParameterName = "@.Age";

parameter.SqlDbType = SqlDbType.VarChar;

parameter.Direction = ParameterDirection.Output;

parameter.Value = TextBox1.Text;

objconnect.Open();

// Add the parameter to the Parameters collection.

cmd.Parameters.Add(parameter);

SqlDataReader reader = cmd.ExecuteReader();

********************this section c# code is entered under the button control************************************

connection string is mentioned in the web.config file.

In the above c# code , I have a text field , where I entered the age , the value entered in the text field should be sent to database. I have used SqlParameter for selecting the type of data should be sent from ASP.NET 2.0 to the microsoft sql server 2000. I am facing a problem where I am unable to sent the random datas from a text field to the database server.I have a created a database file in the microsoft server 2000.

after the excution of the fIeld value is assinged to NULL in the main database i.e microsoft the sql server 2000.

Can anyone help with this issue.

My mail id phijop@.hotmail.com

- PHIJO MATHEW PHILIP.

Hi

Remove :

parameter.Direction = ParameterDirection.Output;

since your direction should be input.

how to insert the range of ip address in SQL using stored procedures

hi

i need to insert the list of ipaddress using stored procedures.

the user will give the from and to range of IP ADDRESS.i've to insert all the possible ip address between those values.

how to do this..

LEts say the range is from 172.25.50.1 to 172.25.50.30 you can write a WHILE loop to loop through and insert records. There are other ways too but at this time of the night this is the best I can think of without putting any pressure on my brain...

|||

hi dinakar,

thanks for the help @. the late night,

i even wrote a while loop in stored procedure..

what i did is i converted the ips to bigint and made the loop.

now what i need is to place the dots in the corresponding positions from where it was taken out..

can u give any suggestions for this..

|||

i solved it in this way

-- LOOP START

Declare @.testvarchar(40)declare @.s1int,@.s2int,@.s3int,@.s4intset @.s1 =len(145)set @.s2 =len(145)set @.s3 =len(45)set @.s4 =len(45)set @.test ='1451592633'set @.test=substring(@.test,0,@.s1+1)+'.'+substring(@.test,@.s1+1,@.s2)+'.'+substring(@.test,@.s2+@.s1+1,@.s3)+'.'+substring(@.test,@.s1+@.s2+@.s3+1,@.s4)print @.test

-- END OF LOOPsql

How to insert the date

Dear all,
I got a fieldA as datetime (length 8).
When I select in query analyzer, it shows the following value
: 2003-10-27 11:36:27.640
now I get fieldA into a ADODB.recordset in VB with fields
rs_A("fieldA")
And I try to update another table with Field (FieldB) Field B
is also datetime with length 8)
with rs_A ("fieldA") i.e. update tableA set FieldB =
rs_A("fieldA")
The final update statement is query analyzer is :
update tableA set FieldB = 2003-10-27 11:36:27.640
The query analyzer complains incorrect syntax near 11:36 . It
cannot be updated.
How can I insert to FieldB with the complete date and time from
FieldA. Is that something like
update tableA set FieldB = '2003-10-27 11:36:27.640' or
update tableA set FieldB = format(2003-10-27 11:36:27.640)
Please help.
Thanks.hon123456 a écrit :
> Dear all,
> I got a fieldA as datetime (length 8).
> When I select in query analyzer, it shows the following value
> : 2003-10-27 11:36:27.640
> now I get fieldA into a ADODB.recordset in VB with fields
> rs_A("fieldA")
> And I try to update another table with Field (FieldB) Field B
> is also datetime with length 8)
> with rs_A ("fieldA") i.e. update tableA set FieldB =
> rs_A("fieldA")
> The final update statement is query analyzer is :
> update tableA set FieldB = 2003-10-27 11:36:27.640
> The query analyzer complains incorrect syntax near 11:36 . It
> cannot be updated.
> How can I insert to FieldB with the complete date and time from
> FieldA. Is that something like
> update tableA set FieldB = '2003-10-27 11:36:27.640' or
> update tableA set FieldB = format(2003-10-27 11:36:27.640)
1) there si no format about DATETIME SQL type storage it is just
DATETIME (in fact two integers)
2) when casting a string to a DATETIME the only format that is universal
is the ISO short DATETIME format which is :
YYYYMMDD HH:MM:SS.nnn
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose after
4) if you want to use exotic formats about DATETIME as string, set your
session dateformat parameter as above :
SET DATEFORMAT { ymd | mdy | dmy | ydm | myd | ymd }
5) prefer always use a explicit CAST like :
SET DATEFORMAT myd
SELECT CAST('03199722 00:11:22.333' AS DATETIME)
...
A +

> Please help.
> Thanks.
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks SQLpro,
For my query : update tableA set FieldB = 2003-10-27
11:36:27.640
Query Analyzer complains incorrect syntax near 11. What can I do to
make this query work?
Thanks.|||Thanks SQLpro,
Sorry to post again. I do not understand your point 3
suggestion
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose
after
Is that mean I need to change the query as follows:
update tableA set FieldB = 2003-10-27-11:36:27.640
Thanks.|||On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Is that mean I need to change the query as follows:
>update tableA set FieldB = 2003-10-27-11:36:27.640
Put quotes around the date string, and remove the dash between the
date and time:
update tableA set FieldB = '2003-10-27 11:36:27.640'
Roy Harvey
Beacon Falls, CT|||> Put quotes around the date string, and remove the dash between the
> date and time:
... and better yet, put a T between the date and a time part, making int th
e ISO 8601 format which
isn't dependent on any language setting for the login, etc.
(Hi, Roy! I'm happy to see you here :-) .)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:h3lt42hna2q4odv454dpujnrp1hib49od6@.
4ax.com...
> On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
> wrote:
>
> Put quotes around the date string, and remove the dash between the
> date and time:
> update tableA set FieldB = '2003-10-27 11:36:27.640'
> Roy Harvey
> Beacon Falls, CT

How to INSERT text that contains single quotes?

When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote.

For example, if they enter "It's great!" then it causes this error:
Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.

How can I allow text with single quotes to be inserted into the table?

Here's my code:

string strInsert = "INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES (@.GameID, @.UserID, @.Comment)";

SqlConnection myConnection = new SqlConnection(<<myconnectionstuff>>);
SqlCommand myCommand = new SqlCommand(strInsert, myConnection);

myCommand.Parameters.Add( "@.GameID", Request.QueryString["GameID"] );
myCommand.Parameters.Add( "@.UserID", (string)Session["UserID"] );
myCommand.Parameters.Add( "@.Comment", ThisUserCommentTextBox.Text );

try {
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}

catch (SqlException ex) {
ErrorLabel.Text = "Error: " + ex.Message;
}

finally {
myCommand.Connection.Close();
}

You write C# so they are called string literals in C# but in ANSI SQL they are Delimiters per ANSI SQL 92 and SQL Server is compliant so run a search for Qouted Identifier in SQL Server BOL(books online) and try the link below for SQL Server Delimiters. Hope this helps.

http://msdn2.microsoft.com/en-gb/library/ms176027.aspx

|||That code looks fine. Are you sure that is where the problem is?|||

(How can I allow text with single quotes to be inserted into the table?)

The person have not posted the code with the error but I replied the above and the error generated.

|||

Motley:

That code looks fine. Are you sure that is where the problem is?

Well it works fine unless I include a single quote in the text box somewhere.

Caddre, I've researched some more based on your reply, but I don't have easy access to the SQL Server settings - I'm on a shared hosting account. Is there some other workaround, maybe using a regular expression to replace a single quote with something else, at least temprarily while I'm processing the text? I tried entering /' instead, to see if an escape character would work, but SQL Server still won't take it.

|||

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

|||

Caddre:

Yes there is SET QUOTED_IDENTIFIER ON/OFF will help you deal with it. Try the link below for details.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

I'm wondering how to send the "SET QUOTED_IDENTIFIER ON" command within my ASP.NET page.

I'm using a command called ExecuteNonQuery(); -- so does that mean I can just append it to the beginning of my INSERT string, like this?

string strInsert = "SET QUOTED_IDENTIFIER ON GO INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES ..."

Does it last only during this query or will it stay on somehow until I turn it off?

Sorry, I'm a newbie with SQL Server and ASP.NET.

|||You have to use it in your stored proc and ExecuteNonQuery takes stored proc. Hope this helps|||

Yes, I understand. It should work fine for any value you place within the textbox control. That includes quotes. I do this quite often with no problems.

As for can you put the SET statement before your query, the answer is yes, but do not separate the commands with GO. You may place a semi-colon between them if you want to however, like:

SET {stuff}; SELECT {stuff}

|||

OK, I am trying to make sense of this, and it's just not making any sense to me. (I have limited experience with SQL and SQL Server and VBScript; I paid a programmer to write a script and after delivering a partly-working script, he promised to fix it and then disappeared. I think he couldn't solve this problem himself.)

I am getting the error message "Unclosed quotation mark after the character string" when I execute this SQL statement from VBScript, butno error when I execute the same SQL statement from within SQL Management Studio. Any suggestions would be extremely welcome.

INSERT INTO Raw_Datafeeds (ProductID,Name,MerchantID,Merchant,Link,Thumbnail,BigImage,Price,RetailPrice,Category,SubCategory,Description,Custom1,Custom2,Custom3,Custom4,Custom5,LastUpdated,Status,Last_Loaded)
VALUES
('454848129','Healthy Ears Ear Cleanser - Buy 2 Get 1 Free','10052',
'Pet Nutrition Products','http://www.shareasale.com/m-pr.cfm?merchantID=10052&userID=YOURUSERID&productID=454848129',
'http://www.petnutritionproducts.com/images/products/100/cat_ears.jpg',
'http://www.petnutritionproducts.com/images/products/400/cat_ears.jpg',15.98,0,'Home/Family','Pets',
'Healthy Ears Ear Cleanser is a veterinarian formulated combination of gentle ingredients that aid in the removal of dirt, wax and other unwanted matter from your cat''s ears.',
'Dog','Ear Care',' ',' ',' ','6/6/2006 1:35:01 AM','instock','10/25/2006 4:06:00 PM')

|||

The original problem in this thread could probably be solved by specifying the type of each parameter rather than letting the system guess.

As for mark's problem, it's obviously related to "cat''s" in your insert string, however since you haven't posted the code you are using in VBScript, it'd be difficult to say where the problem is. As a quick hack, you can replace all quotes in any parameter with '+CHAR(39)+' so that cat's would then become cat'+CHAR(39)+'s.

how to insert text / bitmap column

how to insert text / bitmap column
example pls :-)Originally posted by ligang
how to insert text / bitmap column
example pls :-)

Try the WRITETEXT and UPDATETEXT statements, examples can be found in BOL.

How to insert ten rows in one insert statement

Dear all,
I want to insert 10 rows data as follows
column-A column-B column-C
A A
1
A A
2
A A
3
A A
4
A A
5
In each row, the Column-A and column-B are the same. While column-c
will increase from 1 to 10.
How can I do that in one insert statement . Or I need to loop in VB
Program.
Thanks.If column C is an identity problem, then you just insert 'a', 'a' 10 times
(like your other question).
But otherwise, you could use rowcount and row_number like this:
set rowcount 10
insert into newtable
select 'a', 'a', row_number() over (order by quantity)
from sometable
where sometable has at least 10 rows.
Or, using a CTE instead of rowcount:
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable
)
insert into newtable select * from rownums where rownum <= 10
"hon123456" wrote:

> Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
> In each row, the Column-A and column-B are the same. While column-c
> will increase from 1 to 10.
> How can I do that in one insert statement . Or I need to loop in VB
> Program.
> Thanks.
>|||Thanks Rob.
When I try to run
select 'a', 'a', row_number() over (order by quantity) from sometable.
The query analyzer complains 'row_number' is not a recognized function
name.
By the way, can I have the complete sql statement for the follows
statement
with rownums as (select 'a' col1, 'a' col2, row_number() over (order by
quantity) as rownum
from sometable )|||Thanks Rob. sorry to post again. But I am wondering what does the
variable "quantity" come from in the following statement:
select 'a', 'a', row_number() over (order by quantity) from sometable.|||Haha - sorry... that's just one of the columns in 'sometable'. I meant to
change that.
If it's not recognising row_number(), then perhaps you're not running
SQL2005 ? :(
It's somewhat harder to do in SQL2000. Off the top of my head, the easiest
way to do it is to create a temporary table with an identity field, or
perhaps use a while loop and do 10 single inserts (after all, if it's only
10...).
One nice way would be to use an auxiliary table of numbers. You'll find it
useful for all kinds of other reasons too... but most of all, you can easily
just say "select 'a', 'a', num from nums where num <= 10"
Hope this helps,
Rob
"hon123456" wrote:

> Thanks Rob. sorry to post again. But I am wondering what does the
> variable "quantity" come from in the following statement:
> select 'a', 'a', row_number() over (order by quantity) from sometable.
>|||If you already had a table of sequential numbers this would be
simpler. You would simply replace derived table A with that table,
and limit the values to the range 1 to 10 in a WHERE clause.
INSERT SomeTable
SELECT A.column_a, 'A' as column_b, 'A' as column_c
FROM (select 1 as column_a UNION
select 2 UNION
select 3 UNION
select 4 UNION
select 5 UNION
select 6 UNION
select 7 UNION
select 8 UNION
select 9 UNION
select 10) as A
Roy Harvey
Beacon Falls, CT
On 18 Apr 2006 21:48:17 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Dear all,
> I want to insert 10 rows data as follows
> column-A column-B column-C
> A A
> 1
> A A
> 2
> A A
> 3
> A A
> 4
> A A
> 5
>In each row, the Column-A and column-B are the same. While column-c
>will increase from 1 to 10.
>How can I do that in one insert statement . Or I need to loop in VB
>Program.
>Thanks.

How to Insert Summary Info in a table at midnight everyday?

I have to populate a summary table everyday.
Need to count the number of (STATUS =)'Opened', 'Testing', 'Closed' and
'Pending' from the Cases table and INSERT these values into the
'Summary' Table at 10:00 PM everyday.
How do I schedule an Auto INSERT? Any help will be greatly appriciated
You can simply schedule a job using the SQL Agent job scheduler. Look in
BooksOnLine for more details on scheduling or SQL Agent.
Andrew J. Kelly SQL MVP
"rabig" <rabig@.yahoo.com> wrote in message
news:1161137144.561928.202420@.i3g2000cwc.googlegro ups.com...
>I have to populate a summary table everyday.
> Need to count the number of (STATUS =)'Opened', 'Testing', 'Closed' and
> 'Pending' from the Cases table and INSERT these values into the
> 'Summary' Table at 10:00 PM everyday.
> How do I schedule an Auto INSERT? Any help will be greatly appriciated
>
sql

how to insert sp_helptext output into a table?

hi Freinds,
SQL 2000
How can I insert the output of sp_helptext mytriggername into a table?
Sp_helptext out put is a table ! I need to collect the rows into a custom
table.
Thanks in advance,
PatCREATE TABLE #trigger
(
[text] VARCHAR(8000)
)
INSERT #trigger EXEC sp_helptext insContactEmailFormatNull
SELECT * FROM #trigger
DROP TABLE #trigger
Of course, you could have silly triggers that go more than 8000 characters
without a carriage return. In that case I would suggest CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
A
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23OW3DK1oFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi Freinds,
> SQL 2000
> How can I insert the output of sp_helptext mytriggername into a table?
> Sp_helptext out put is a table ! I need to collect the rows into a custom
> table.
> Thanks in advance,
> Pat
>|||> ... CREATE TABLE
#PeopleToShoot(EmployeeID INT) ...
I'd suggest a permanent table for that.
ML

how to insert several insert commands, triggers?

Hello, what i want is simple.

This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites.

Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list.

How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?

Patrick

Hello,

to insert multiple rows you can use select instead of values in the insert statement. So there is no need for a loop. It would be something like this:

INSERT INTO post_inbox (TopicID, UserName, Message)SELECT ( TopicId,--Is always the ID of Topic X, because of the WHERE UserName,--Is every user that is subscribed to Topic X'Hey, Topix X has a new answer, check it out!'--Maybe a message in their inbox?)FROM forum_favoritesWHERE TopicId = 123--ID op Topic X

So the select will select all users subscribed to Topic X, and you use that as the values for the insert.

Good luck!

|||

Hi Pafo,

From your description, I understand that you need to give each one, whose favorite list has Topic X, a message when any one replies to Topic X.

This can be done in many ways.

1. In the page code, when some one posts a reply, we can invoke some method to inform the observers. This will be more flexible in the logic layer.
2. If you need to do this in the database, we can use Triggers or we can extend the reply stored procedure to do this directly.

In my opinion, option 1 will be better, since users can customize whether to receive this message on their own.

HTH. If anything is unclear, please feel free to mark it as Not Answered and post your reply. Thanks!

How to insert right justifies leading zeros

I have a business rule in my environment where I need to insert right justified leading zeros in the column. For example if the value to be inserted is 12 than it should be inserted as 0000012. How can I do this

Chintan.

Here it is:

SELECT RIGHT('000000' + CAST(12 as SYSNAME), 7)

Thanks,
Zuomin
|||

The "leading zeroes" issue might be more of a display issue than a storage issue. Is your column a character field, integer field, or what exactly?

If your field is character (or varchar) then you can use concatenation and the RIGHT function to pre-pend your string with zero characters; however, if your field is integer understand that it is stored as a binary encoded integer and the zeroes are understood.

You can do something like zuomin has suggested for display purposes. Also, if you MUST carry this as a zero-filled key, you can store it in the table as a computed column -- again as a CHAR field similar to zuomin's suggestion.

|||

the rule applies to about 9600 rows so....what can be the best solution

Chintan

|||

as of now the columns are in Varchar, but this whole table is getting converted to Flatfile of fixed width and than it is submitted to client. In this case wot shud I allocate the column as Varchar or Integer taking flatfile into consideration.

|||

OK, but what is the datatype of your column?

|||

Varchar.

|||

I would suggest using something like zuomin's RIGHT solution.

|||

A bit of editorial: I have never really liked doing this. This makes it possible to have many different '1' records. Now it is possible to have record '1', '01', '001', etc as valid primary keys to this table. And I have seen this problem manifest several times. For me, I would rather store the integer field and have the application do the formatting.

|||

the problem here is 1. there is no primary keys

2. there is no application which will insert leading zeros

3. it is goin as a simple text files

so now if i use

SELECT RIGHT('000000' + CAST(trtyc as SYSNAME), 7)

this is just a select statement, but i need to insert leading zeros into the db so tht whenever the data goes in to flat files instead of showing 12 it will show 0000012

it might some kinda update statment

Chintan

|||

UPDATE yourTable SET yourVarCharColumn= RIGHT('000000'+yourVarCharColumn,7)

Without WHERE condtion, you will update all your records.

How to insert resultset from SP into a table? or use Select * From

I tried this (and other variations) in Query Analyzer - which did not work.
select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
'3/13/06', '3/13/06')
Is there a way to Select/insert the resultset from an SP? How to do this?
Thanks,
RichRich,
Create a temporary or permanent table with the same structure as the result
of the sp. Use:
insert into #t1
exec dbo.usp_p1 ...
AMB
"Rich" wrote:

> I tried this (and other variations) in Query Analyzer - which did not work
.
> select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
> '3/13/06', '3/13/06')
> Is there a way to Select/insert the resultset from an SP? How to do this?
> Thanks,
> Rich
>

How to insert resultset from SP into a table? or use Select *

Thank you. Yes, I did see that this is the only way to do it. My objective
is that an SP which I did not create and don't want to modify returns a
dataset that I need to order. So I want to read/write that data into a temp
table so that I can order it.
"Alejandro Mesa" wrote:
> Rich,
> Create a temporary or permanent table with the same structure as the resul
t
> of the sp. Use:
> insert into #t1
> exec dbo.usp_p1 ...
>
> AMB
> "Rich" wrote:
>Rich,
If you can order the resultset at the client side, then do it and you will
be liberating some load from your sql server.
AMB
"Rich" wrote:
> Thank you. Yes, I did see that this is the only way to do it. My objecti
ve
> is that an SP which I did not create and don't want to modify returns a
> dataset that I need to order. So I want to read/write that data into a te
mp
> table so that I can order it.
> "Alejandro Mesa" wrote:
>sql

how to insert records where PK is being violated on some records?

Hello,
I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
tbl2.ID is a PK. The problem is that tbl2 already contains some of the
records from tbl1 whcih is causing a PK violation. what tsql statement can
perform the insert without violating PK?
Insert Into tbl2(ID, fld2, fld3)
Select ID, fld2, fld3 from tbl1
where tbl1.ID Not In (Select ID from tbl2)
Any suggestions appreciated.
Thanks,
RichBegin by writing a query that only selects those rows from [tbl1] that do
not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
you may then insert this result into [tbl2]. This may perform better than
the NOT IN or NOT EXISTS method.
Insert Into
tbl2 (ID, fld2, fld3)
Select
T1.ID,
T1.fld2,
T1.fld3
from tbl1
left join tbl2 as T2
on T2.ID = T1.ID
where
T2.ID is null
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement
> can
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich|||Thank you all for your replies.
"Rich" wrote:

> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement c
an
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich

how to insert records where PK is being violated on some recor

How about this
Insert Into tbl2(ID, fld1, fld2)
Select Id,fld1, fld2 from
tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
Left Join ttbl2 t2 on t1.id = t2.id
where t2.id Is Null
Would this work also? I forgot that I left out another table.
"JT" wrote:

> Begin by writing a query that only selects those rows from [tbl1] that do
> not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
> you may then insert this result into [tbl2]. This may perform better than
> the NOT IN or NOT EXISTS method.
> Insert Into
> tbl2 (ID, fld2, fld3)
> Select
> T1.ID,
> T1.fld2,
> T1.fld3
> from tbl1
> left join tbl2 as T2
> on T2.ID = T1.ID
> where
> T2.ID is null
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
>
>It looks like it would.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00FBAEDE-5940-48DA-806D-5EB176AD10D7@.microsoft.com...
> How about this
> Insert Into tbl2(ID, fld1, fld2)
> Select Id,fld1, fld2 from
> tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
> Left Join ttbl2 t2 on t1.id = t2.id
> where t2.id Is Null
> Would this work also? I forgot that I left out another table.
> "JT" wrote:
>

How to insert records into two tables that references each other?

I have two tables , A1 and B1 . B1 has a reference key pointing to A1.ID which is a primary key. Now I want to INSERT a record into both table.

CREATE TABLE a1 (
T_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_Test INTEGER NOT NULL,
);

CREATE TABLE B1 (
B_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_SID INTEGER NOT NULL REFERENCES a1(T_ID),
);

-- A function to get the next primary key value

-- Get the next unique key from A1
CREATE FUNCTION [dbo].[getTicketNo]
(
)
RETURNS INT
AS
BEGIN
RETURN (SELECT Max(T_ID) from A1)
END

I can only insert a record into a table at a time. and I rely on dbo.getTicketNo() to get the lastest T_ID

I wonder how do you insert a new record into A1 and B1 and maintain referential integrity? You must call Insert twice to accomplish the job right?

What I want to insert a record into B1.SID is the primary key value (T_ID) that was created during "Insert into A1 values (0)", using dbo.getTicketNo()
I worry if I am going to insert a record into A1 first, then insert a record into B1 next, there is a chance the data wont be consistent; as I illustrated a situation below:

t A1 B1
0 (0)Insert
1 (1)Insert
2 (0)Insert
3 (1)Insert

dbo.getTicketNo() at t3 may return the wrong value if another process (1)insert is executed, that will change the primary key number to 2 which is incorrect

Can I lock up A1 and then unlock A1 after I finished working with B1?

If so, what would happen to the application if it tries to access into A1 during it is locked?

If you ask me this not a good approach to get the identity value.
You can use the following objects to get the last inserted identity value.

@.@.Identity or Scope_Identity()

1.Insert the data on Master table (A1)
2.Then get the last inserted value from those system functions or global varibale
3.Use the values on Child table (B1)

Example,

Insert into A1(T_Test) values (@.T_Test);
Insert into B1(T_SID) values (@.@.Identity)
--or
Insert into B1(T_SID) values (Scope_Identity())

NOTE: Don't use MAX function to get the last inserted identity value. It may be
Inserted by other concurrent users.

|||

Thank you ManiD

From your code, is there any chance that another INSERT query is executed by other concurrent users after the 1st Insert and before the 2nd Insert in your case?

time 0:00:00 (User 0) Insert into A1(T_Test) values (@.T_Test);
time 0:00:20 (User 1) Insert into A1(T_Test) values (@.T_Test);
time 0:00:21 (User 0) Insert into B1(T_SID) values (@.@.Identity)

If it is possible, what is the best approach to ensure that the second Insert into B1 is consistent to the master A1?

Thanks for your precious time

|||

In this case,

@.@.Identity hold the User1 Identity data..

So it always good idea to use SCOPE_IDENTITY() instead of using @.@.Identity..

SCOPE_IDENTITY always hold the current user's identity data...

|||

Ok,,, thank you ManiD

Let me do some research on SCOPE_Indentity() ...

Thank you for your kind replies

|||

When I google it, found some people mispelled SCOPE_IDENTITY() to SCOPE_INDENTITY() .....

Here is the MSDN for SCOPE_IDENTITY() if anyone got the similar issues can have a look

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

How to insert records into related tables?

Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.

How can I do this?
Thanks

This should be handled by your application. If you are using identity fields you can use the SCOPE_IDENTITY function to return the last value for the identity column.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi,
Can you please help me with the sintax?
Thanks|||To create a table, you can do something like:

CREATE TABLE Employee

(

EmployeeID INT NOT NULL

,EmployeeFirstName VARCHAR(15) NOT NULL

,EmployeeLastName VARCHAR(15) NOT NULL
)

CREATE TABLE Role

(

RoleID INT NOT NULL

,RoleName VARCHAR(15) NOT NULL

)

CREATE TABLE EmployeeRole

(

RoleID INT NOT NULL

,EmployeeID INT NOT NULL

)

INSERT Employee

(

EmployeeID

,EmployeeFirstName

,EmployeeLastName

)

VALUES

(

1

,'John'

,'Wayne'

)

INSERT Role

(

RoleID

,RoleName

)

VALUES

(

1

,'Rooster Cogburn'

)

INSERT EmployeeRole

(

RoleID

,EmployeeID

)

VALUES

(

1

,1

)

As WesleyB mentioned, you can take this a step further and make the ID columns INDENTITY columns and use the SCOPE_INDENTITY function to retrieve the value for the most recently inserted row. You can also add some declarative referential integrity if you wish. Check out Books Online for more information on this.

BTW - I'm writing this without access to a SQL Server to test the scripts so please forgive any typos.

HTH...

Joe

|||Ok,
Thanks.
I used scope_identity and it worked.|||

I addition you could have used OUTPUT to redirect the output to a resultset with the assigned IDENTITIES per row. This feature is SQL Server 2005 and above only.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to insert records into related tables?

Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.

How can I do this?
Thanks

This should be handled by your application. If you are using identity fields you can use the SCOPE_IDENTITY function to return the last value for the identity column.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi,
Can you please help me with the sintax?
Thanks|||To create a table, you can do something like:

CREATE TABLE Employee

(

EmployeeID INT NOT NULL

,EmployeeFirstName VARCHAR(15) NOT NULL

,EmployeeLastName VARCHAR(15) NOT NULL
)

CREATE TABLE Role

(

RoleID INT NOT NULL

,RoleName VARCHAR(15) NOT NULL

)

CREATE TABLE EmployeeRole

(

RoleID INT NOT NULL

,EmployeeID INT NOT NULL

)

INSERT Employee

(

EmployeeID

,EmployeeFirstName

,EmployeeLastName

)

VALUES

(

1

,'John'

,'Wayne'

)

INSERT Role

(

RoleID

,RoleName

)

VALUES

(

1

,'Rooster Cogburn'

)

INSERT EmployeeRole

(

RoleID

,EmployeeID

)

VALUES

(

1

,1

)

As WesleyB mentioned, you can take this a step further and make the ID columns INDENTITY columns and use the SCOPE_INDENTITY function to retrieve the value for the most recently inserted row. You can also add some declarative referential integrity if you wish. Check out Books Online for more information on this.

BTW - I'm writing this without access to a SQL Server to test the scripts so please forgive any typos.

HTH...

Joe

|||Ok,
Thanks.
I used scope_identity and it worked.|||

I addition you could have used OUTPUT to redirect the output to a resultset with the assigned IDENTITIES per row. This feature is SQL Server 2005 and above only.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

how to insert records from tiggers at batch update process

I want to split the records from table1 and insert the splited records to table2. The trigger is working fine when i am trying to update single record. but the trigger is not working properly at the time of batch update. At the time of batch update it is split the last record from the query and insert that into table2 instead of inserting all the records.

Pls help me........I suspect you wrote your trigger to work with one record at a time. I say this because of your comment of the trigger working for a one record update and the LAST record in a batch. Take a look at your trigger and ask yourself "What happens if I have three records to deal with in either the inserted OR deleted temp tables?"

Post your trigger and maybe we can offer some suggestions.|||Hi

This is the trigger i have used to split the records for various conditions from table1 and insert the splitted records into another table table2

The Problem i'm facing is if it is single update(if only one row is affected) the trigger is working fine,
but not for batch update.

if you are unable to follow this trigger pls give your own batch update example, we will try to implement that.

pls help me.

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS
Declare @.Id int
Declare @.Res_id nvarchar(8)
Declare @.DTime smalldatetime
Declare @.StartTime char(5)
Declare @.EndTime char(5)
Declare @.Pauze char(5)
Declare @.Hourworked nvarchar(5)
Declare @.Status char(1)
--Declare @.PPayroll bit
Declare @.Project nvarchar(10)
Declare @.Naam char(50)
Declare @.Type char(5)
Declare @.Hryear nvarchar(4)
Declare @.Totmin char(5)
Declare @.Endtimebr char(5)
Declare @.Stime char(5)
Declare @.Start char(5)
Declare @.Processed char(1)
Declare @.del_DTime smalldatetime
Declare @.del_StartTime char(5)
Declare @.del_EndTime char(5)
Declare @.Del_id int
Declare @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

Select @.Id=Id,
@.Res_id=Res_id,
@.DTime=[Datetime],
@.Start=Starttime,
@.EndTime=Endtime,
@.Pauze=Pauze,
@.Hourworked=Hoursworked,
@.Status=Status,
@.Project=Project,
@.Naam=Naam,
@.Type=Type,
@.Hryear=Hryear,
@.Processed=Processed
From inserted -- i ,Hours_tmp h where i.[Id] = h.[id]

Select @.del_DTime=[DateTime],
@.del_Starttime=Starttime,
@.del_endTime=Endtime ,
@.del_id=id
From Deleted

Delete From Selnmuamid
Where Res_id=@.Res_id
and Datetime=@.del_DTime
and Starttime >=@.del_starttime
and Endtime <=@.del_endtime

-- This is To Get total hours (hourworked column in the table ) in minutes

Select @.Totmin=Sum(Convert(Integer,(Left(@.hourworked,Char index('.',@.hourworked)-1) *
60)) +Convert(Integer,(Right(@.hourworked,Len(@.hourworke d) -
Charindex('.',@.hourworked))))),@.Endtimebr=Sum(Conv ert(Integer,(Left(@.Endtime,Charindex(':',@.Endtime) -1) *
60)) + Convert(Integer,(Right(@.Endtime,Len(@.Endtime) -
Charindex(':',@.Endtime))))),@.Stime=Sum(Convert(Int eger,(Left(@.Start,Charindex(':',@.Start)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))

--Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,
@.Hourworked,@.Status,@.Project,@.Naam,@.Type,
@.Hryear,@.Totmin,@.Endtimebr,@.STime,@.Processed|||basically I just wrapped your code in a while loop to step through each record in the inserted temp table. Then I moveded the delete Selnmuamid to the end.

This has NOT been tested!

Code:
------------------------------

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS

-- ----------------
-- One Declare is more efficient than multipule declares
-- ----------------
Declare @.Id int
, @.Res_id nvarchar(8)
, @.DTime smalldatetime
, @.StartTime char(5)
, @.EndTime char(5)
, @.Pauze char(5)
, @.Hourworked nvarchar(5)
, @.Status char(1)
-- , @.PPayroll bit
, @.Project nvarchar(10)
, @.Naam char(50)
, @.Type char(5)
, @.Hryear nvarchar(4)
, @.Totmin char(5)
, @.Endtimebr char(5)
, @.Stime char(5)
, @.Start char(5)
, @.Processed char(1)
, @.del_DTime smalldatetime
, @.del_StartTime char(5)
, @.del_EndTime char(5)
, @.Del_id int
, @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

select @.Id = min(ID) from inserted
while (@.Id is not null) begin
Select @.Res_id = Res_id
, @.DTime = [Datetime]
, @.Start = Starttime
, @.EndTime = Endtime
, @.Pauze = Pauze
, @.Hourworked = Hoursworked
, @.Status = Status
, @.Project = Project
, @.Naam = Naam
, @.Type = Type
, @.Hryear = Hryear
, @.Processed = Processed
-- ---------------------------------------------------
-- Hoursworked, Endtime and StartTime need to be converted to minutes
-- ---------------------------------------------------
, @.Totmin = Convert(Integer,(Left(Hoursworked,Charindex('.',Ho ursworked)-1) * 60)) + Convert(Integer,(Right(Hoursworked,Len(Hoursworked ) - Charindex('. ',Hoursworked))))
, @.Endtimebr = Convert(Integer,(Left(Endtime, Charindex(':',Endtime)-1) * 60)) + Convert(Integer,(Right(Endtime, Len(Endtime) - Charindex(':',Endtime))))
, @.Stime = Convert(Integer,(Left(Starttime, Charindex(':',Starttime)-1) * 60)) + Convert(Integer,(Right(Starttime, Len(Starttime) - Charindex(':',Starttime)))))
From inserted i
where id = @.id


-- This is To Get total hours (hourworked column in the table ) in minutes
/*
Select @.Totmin = Sum(Convert(Integer,(Left(@.hourworked,Charindex('. ',@.hourworked)-1) * 60)) +
Convert(Integer,(Right(@.hourworked,Len(@.hourworked ) - Charindex('. ',@.hourworked)))))
, @.Endtimebr = Sum(Convert(Integer,(Left(@.Endtime,Charindex(':',@. Endtime)-1) * 60)) +
Convert(Integer,(Right(@.Endtime,Len(@.Endtime) - Charindex(':',@.Endtime)))))
, @.Stime = Sum(Convert(Integer,(Left(@.Start,Charindex(':',@.St art)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))
*/
-- Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,@.Hourwor ked,@.Status,@.Project,@.Naam,@.Type,@.Hryear,@.Totmin,@. Endtimebr,@.STime,@.Processed

select @.Id = min(ID) from inserted where id > @.id
end

-- -------------------
-- The deleted temp table can be joined to the Selnmuamid table
-- to deleted the unwanted records. If you need the ID from the
-- inserted table then join the inserted table with the
-- deleted table and then join the Selnmuamid table.
-- This is a good example of how you can ditch the row-at-a-time
-- mentality and start using set theory for processing records.
-- -------------------
delete
from Selnmuamid s
join deleted d on s.Res_id = d.Res_id
where [s.Datetime] = d.del_DTime
and s.Starttime >= d.del_starttime
and s.Endtime <= d.del_endtime

/*
Select @.del_DTime = [DateTime]
, @.del_Starttime = Starttime
, @.del_endTime = Endtime
, @.del_id = [id]
From Deleted
where Id = @.Id

Delete
From Selnmuamid
Where Res_id = @.Res_id
and [Datetime] = @.del_DTime
and Starttime >= @.del_starttime
and Endtime <= @.del_endtime
*/

------------------------------

How to insert record to SQL server which is on the other machine from client computer?

I want to write simple sql database project that that works in the network. and i need to insert record from a client to a server that is on the other machine.

This is a pretty broad question. I suggest you do some additional research online or via any one of a number of excellent books, articles or whitepapers.

This will require that you (at least)

Install and configure the SQL Server on the remote system.

How to Insert Raw text into SQL table

HI all,
I have raw text like these:
test1
test2
test3
...
I want to pass these text in to a SP from Query Analyzer as a paremeter and
the SP will handle to import them to a sql table exactly like the format I
passed in: In this case sql table should store:
test1
test2
test3
...
How do you handle things like this.
Thanks,
tomtom d wrote:
> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a
> paremeter and the SP will handle to import them to a sql table
> exactly like the format I passed in: In this case sql table should
> store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom
You can try using the DTS Import-Export Wizard or BCP.
David Gugick
Imceda Software
www.imceda.com|||Hi
Try:
create table mytext ( col1 int not null identity(1,1), textval text )
CREATE PROCEDURE InsertMyText ( @.txtval text )
AS
INSERT INTO MyText ( textval ) VALUES ( @.txtval )
EXEC InsertMyText 'text1
text2
text3'
EXEC InsertMyText '
test1
test2
test3'
select * from MyText
"tom d" wrote:

> HI all,
> I have raw text like these:
> test1
> test2
> test3
> ...
> I want to pass these text in to a SP from Query Analyzer as a paremeter an
d
> the SP will handle to import them to a sql table exactly like the format I
> passed in: In this case sql table should store:
> test1
> test2
> test3
> ...
> How do you handle things like this.
> Thanks,
> tom

How to insert primary keys without using identity

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

AndreIt is possible with cursors, but would be awkward and I expect subject to problems if the table was having lots of records added by lots of users at the same time...

Why do you not want to use Autoincrement?

How to insert picture into Image column?

I'm using SQL Server 2000. What's the command to insert a picture (.bmp, .jp
g.
..) into an Image type column?
Regards,
Pedestrian, Penang.
Message posted via http://www.webservertalk.comi've had luck with BULK INSERT. Here's an example of how to use
it for binary files. The only awkwardness is that
you need to get the file size into the format file before each import,
and how best to automate that will depend on the particular situation.
set nocount on
go
create table DocFiles (
fileNum int identity(1,1),
fName varchar(40),
doc image
)
/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
with the byte length of the .doc file in place of 12755529:
8.0
1
1 SQLIMAGE 0 12755529 "" 1 c1
SQL_Latin1_General_Cp1_CI_AI
*/
-- Create the format file with T-SQL, if desired:
exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell
'echo 1 SQLIMAGE 0 12755529 "" 3 zf
SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
with (
FORMATFILE='e:\txtsrv\doc.fmt'
)
update DocFiles
set fName = 'e:\txtsrv\yourfile.doc'
where fName is NULL
select fileNum, fName as fileName, datalength(doc) as fileSize from DocFiles
GO
-- Steve Kass
-- Drew University
-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||here is another route using textcopy.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||Thanks for replying... Steve Kass... It's looks a bit complex here
I'll figure it out... since I don't see the purpose of the .fmt file...
Thanks for oj too...
Steve Kass wrote:
>i've had luck with BULK INSERT. Here's an example of how to use
>it for binary files. The only awkwardness is that
>you need to get the file size into the format file before each import,
>and how best to automate that will depend on the particular situation.
>set nocount on
>go
>create table DocFiles (
> fileNum int identity(1,1),
> fName varchar(40),
> doc image
> )
>/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
> with the byte length of the .doc file in place of 12755529:
>8.0
>1
>1 SQLIMAGE 0 12755529 "" 1 c1
>SQL_Latin1_General_Cp1_CI_AI
>*/
>-- Create the format file with T-SQL, if desired:
>exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell
>'echo 1 SQLIMAGE 0 12755529 "" 3 zf
>SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
>bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
>with (
> FORMATFILE='e:\txtsrv\doc.fmt'
> )
>update DocFiles
>set fName = 'e:\txtsrv\yourfile.doc'
>where fName is NULL
>select fileNum, fName as fileName, datalength(doc) as fileSize from DocFile
s
>GO
>-- Steve Kass
>-- Drew University
>-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1sql

how to insert picture into a table

how do I do that?
can I do this in query analyzer?Depends on your coding language, you should check in google for stream
+upload +sql server +<yourcodinglanguage>
Or just write your coding language here, perhaps we can provide you with
some interesting links.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
> how do I do that?
> can I do this in query analyzer?
>|||my table pictures is empty.
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I use textcopy.exe in dos environment, but I got error.
WHAT HAPPEN? I don't understand ERROR: Row retrieval failed.
I want to insert it, not update it. does that mean textcopy.exe only do
update?
TEXTCOPY Version 1.0
DB-Library version 8.00.2039
Type the SQL Server to connect to: LOCALW
Type your login: brit
Type your password: brit
Type the database: Northwind
Type the table: pictures
Type the text or image column: picture
Type the where clause: where 1=1
Type the file: C:\teaser_head2.jpg
Type the direction ('I' for in, 'O' for out): I
ERROR: Row retrieval failed.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Jen,
I don't want to use coding language such as C# or java to insert
picture,
I only want to use SQL build-in utility and/or straight SQL query to do
that. so I don't think textcopy is an option since it only does update.
"Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote in
message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Depends on your coding language, you should check in google for stream
> +upload +sql server +<yourcodinglanguage>
> Or just write your coding language here, perhaps we can provide you with
> some interesting links.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:OHvwa4UtFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||You can simply use DDL:
CREATE TABLE [dbo].[pictures] (
[pid] [int] NULL ,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into pictures
values (1,0x000000000101020201010)
So that part is easy. The hard bit is that you have to take your image and
turn it into the hexidecimal format. Can't help you there, I regret.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Jen,
> I don't want to use coding language such as C# or java to insert
> picture,
> I only want to use SQL build-in utility and/or straight SQL query to do
> that. so I don't think textcopy is an option since it only does update.
>
>
>
> "Jens Smeyer" < Jens@.remove_this_for_contacting_sqlserve
r2005.de> wrote
> in message news:OF28HKVtFHA.3236@.TK2MSFTNGP14.phx.gbl...
>|||One way to hack around it is to initialize row for textcopy and then update
picture in that row using textcopy.exe
so when you do select picture from the table, you 'll see hexidecimal code.
unfortunately, I can't set image to a local variable in sql server. I guess
I can't avoid application programming then.
thanks
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:efRiZ5VtFHA.1168@.TK2MSFTNGP10.phx.gbl...
> You can simply use DDL:
> CREATE TABLE [dbo].[pictures] (
> [pid] [int] NULL ,
> [picture] [image] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> insert into pictures
> values (1,0x000000000101020201010)
> So that part is easy. The hard bit is that you have to take your image
> and turn it into the hexidecimal format. Can't help you there, I regret.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:%238hiZfVtFHA.3080@.TK2MSFTNGP15.phx.gbl...
>

How to Insert or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

how to insert only distinct values from a Flat File

I have to insert the Values from the Flat Files , My table structures have Primary keys , how do i insert only the distinct values into the table without the ERROR VIOLATION OF Primary Key already exists a record.

Dropping and Adding Relationships after insert is a way but doesnt serve the whole purpose is there a way we can eliminate duplicate records based on their Primary key before inserting them into the Database.

You can do a lookup against your target table. Redirect the error output and send these rows to a sql destination for the target table (i.e. if the lookup didn't find it, it is a new row)

See the following thread...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Sort transformation has a check box that allows you to eliminate duplicates; another approach could be to use a agregation transformation.|||Thanks for the Quick Solutions i will do that and come back with the feedback, what about the performance which one is better the sort or Aggregates using group by or Lookup|||Are there duplicate primary key's in your flat file? If so you will need to go with either the aggregate or sort solutions (I may have misread your question, the lookup will find any primary keys already in the table before you start to insert your new records). I don't know which of these will be more effecient for you, but you can always try them both out for a few test runs and see for yourself as it will often depend on environment and data being loaded as to which will come out ahead.|||

The Lookup approach will not detect duplicates within the batch being processed. You would need to change it to no-cache mode. Sort and aggregation transformation should be give you about the same performance. In general the no-cache lookup, sort or aggregation approach are not great from the performance standpoint; but that would depend on many factors; so test and measure yourself.

Sort/aggregation transformation cache the full set of rows in RAM; so if the volume of data to be de-duplicated is huge; the system could run out of memory.

An alternative could be to use an staging table and then let the DB engine to do the dedup work. (e.g. http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html )|||

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

|||There are two fundamentals being discussed in this thread. We've yet to get the crucial question answered though:

Are you trying to dedupe the source data before going into the destination table, or are you trying to prevent duplicate records from getting inserted (unique data from the source, but not necessarily all unique in the destination) and hence raising a primary key violation?

Deduping the source data can be done with an aggregate or the sort transformations. You could also load the data into a staging table (as Rafael stated) and then run a SQL statement against that data (select DISTINCT perhaps). Using the techniques described in the thread linked to earlier, you can ensure that your data does not violate primary keys by using a lookup. Note that you may have to do a combination of all of the above.|||

Dev2624 wrote:

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

Use the sort transformation instead.|||I am trying to Prevent the Duplicate Records from getting inserted . i am working on it will post back with the results. Thanks!!!sql

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to insert on table with a variable name

Here is the jist of what I want to do, but I know the syntax is wrong...please help

CREATE PROCEDURE mySP
(
@.tablename As varChar(50),
@.input_name As varChar(100)
)
As
INSERT @.tablename (name)
VALUES (@.input_name)

assume the table exists already, and has a 'name' field in it.

thanks guysKatie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

is there a way around using 'exec'?|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

Yea, I just know basics of stored procedures, I'd love to hear how this sort of thing is usually done.

How to insert numbers from a text box to a Sql database table column’s type numeric?

Hi,

I am getting an error when I try to insert a number typed in a text box control into a Sql database table column's type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:

System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.decimal, "Measure"))

A piece of the SP inside Sql server:

USE market26
GO
ALTER PROC new_offer
@.Offer_id bigint, @.Measure numeric(6,2) = null, …

What is wrong? Why it doesn' t accept the number typed in my text box?

Thank you,
CesarThis is not correct:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

This would probably work (not tested):

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal);
mParm.Size = 13; // Max size in Bytes of the Decimal number
mParm.Precision = 8; // Total number of digits allowed (right + left of decimal point
mParm.Scale = 2; // Set the number of decimal places the Parameter value is resolved
mParm.set_IsNullable(true);
mParm.Value = decimal.Parse(myTextBox.Text.Trim());
CmdInsert.Parameters.Add(mParm)

|||Hi,

I always write my ASP.NET commands to talk with Sql Stored Procedures thus:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

I think that it is correct, and it works fine.

I have never used a text field to insert numeric data into Sql database, but, I have to define all these parameter properties in order to pass a numeric data type to the database?

Thanks|||That's interesting. There is no Constructor attributed to the SqlParameter Class which accepts a Constructor looking like this:

New SqlParameter(<string>,<SqlDbType Enum>, <string>)

as far as I know...But hey, go for it!|||Take a look at this piece of an article and let me know what do you think about it:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Set up parameter for stored procedure
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@.CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This example makes all the parameter settings explicit. Some developers like this style, and it's good for instructional purposes. However, some developers prefer an equivalent alternative that has fewer lines of code:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
cmdOrders.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@.CustomerID").Value = "ALFKI"
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This code behaves exactly the same as the previous example. However, it only needs two lines of code for each parameter rather than six. When a stored procedure has a lot of parameters (as some of our later examples do), this can be quite a difference on lines of code required, so we'll use that form from this point onward.

The complete article is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp|||Hey! Perhaps you have misunderstood me. I meant that I always use this structure:
(Which I knew that something was wrong)

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

Instead of this:

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal)
mParm.SourceColumn = "Measure"
CmdInsert.Parameters.Add(mParm)

I knew that something was wrong, I suspected that something it lacked in my SqlParameter, and you helped me with your example!, it lacked only the size property which in my case is 5. I am sorry, the only thing that I saw strange is the structure you wrote, and the amount of parameters which I don' t need to use (in this case). I always use the shorter alternative ;-).

So, the correct SqlParameter in my case is:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal,5, "Measure"))

And now works fine.
Thank you very much!
Cesar

How to insert NULL when using bcp_moretext?

How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order must al
so be bound with a NULL vardata -- i.e. once you use bcp_moretext for a 'tex
t'/'image', it's really all *following* that must be also sent with bcp_more
text. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with length
0, for that column. Setting length to SQL_NULL_DATA will cause an error late
r, whether you try to call bcp_moretext with length SQL_NULL_DATA, with leng
th 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since the d
ocumentation doesn't cover this at all.
Thanks,
Jim FloodCan I expect some sort of response from Microsoft in this group within two d
ays, as part of the benefit of my MSDN subscription?
-- Jim Flood wrote: --
How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order mu
st also be bound with a NULL vardata -- i.e. once you use bcp_moretext for a
'text'/'image', it's really all *following* that must be also sent with bcp
_moretext. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with le
ngth 0, for that column. Setting length to SQL_NULL_DATA will cause an error
later, whether you try to call bcp_moretext with length SQL_NULL_DATA, with
length 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since
the documentation doesn't cover this at all.
Thanks,
Jim Flood|||I'm checking with our BCP guy.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Jim Flood" <bezspam@.bezspam.cz> wrote in message
news:C95C8F5A-BF58-48F3-A516-AC5980947BDE@.microsoft.com...
> Can I expect some sort of response from Microsoft in this group within two
days, as part of the benefit of my MSDN subscription?
> -- Jim Flood wrote: --
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5
DB-LIB BCP). Setting the param length to zero (bcp_bind or bcp_collen) for
7.0 BCP tells it that you are sending a zero-length param. You still need to
call bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Brannon,
Have you heard back about the BCP problem yet? Is there anything else I need
to do besides post to this group, to get the two-day response from Microsof
t? It's been well over two days.
Thanks,
Jim Flood|||Hi Jim,
I've faced the same problem. I can't insert NULLs using bcp_moretext.
Have you found a solution?
Thanks in advance,
Alberto.
"Jim Flood" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:0BB0853D-430D-4299-88D2-7343C6C024A8@.microsoft.com...
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB
BCP). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP
tells it that you are sending a zero-length param. You still need to call
bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Alberto,
If all of your NULLs are lined up contiguous to the left in the set of colum
ns for a given row, then just avoid bcp_moretext for those columns -- althou
gh, according to the documentation, this isn't supposed to work. But, once y
ou've set bcp_moretext for
a column, going left-to-right, then all other columns to the right will have
to be bcp_moretext'ed as well, and then you can't set any of them to NULL.
So, I have no solution.
Can I please get some kind of response from Microsoft on this problem? I bel
ieve my MSDN subscription should guarantee a two-day response time, and it h
as been *far* more than two days.
Jim Flood