insertion scheduled job in SQL

Hi guys,
i want to make a scheduled job that inserts 10 user defined row each minute continously in enterprise managerI'd write a stored procedure to do the insertion and then use SQL Agent to schedule it to run every minute.
But, what do you mean by "10 user defined row"?

Inserting row with single quote

I'm inserting a row of people's names and addresses to a table. This seems to work great, unless the person has ' in their name, for example O' Riordan. When the address has single quote (such as Wilder's Path), I'd get the same error, unclosed quote when inserting the row.

What would be the best way to work around this, other than read the line and replace it with a space.

In a literal you need to double embedded quotes, so it'd be 'O''Riordan'.

The best solution is to use a parameterized query, then you don't have to worry about embedded quotes.

|||How to use parameterized query? the program i currently using are java.. can show me some examples on how to use parameterized query. ^-^|||

Parameterized query is very useful but you can also use string concatenation. I've written a sample on my blog on how to insert records with single quotes. Here's a sample TSQL script

PRINT 'This is the man''' + 's choice'

In order to do this, you use a two-single quote approach. Notice the use of a concatenation operator to separate the first part of the sentence and the one that comes after the single-quote and the use of a two-single quote approach. This simpy means that every time you have a single-quote character included in your string, you have to do string concatenation to accomodate the insertion of a single-qoute in your string.

|||

Take a look at these links

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

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

Hope this helps

|||Sorry.. after looking thru the website i still dun know how cause my sql statement is like that..

String query = "INSERT INTO addresses (" +
"company, address, name, hp, " +
"phone, fax, email, start, day, month, year, " +
"mrc, isp, des, sale, mark" +
") VALUES ('" +
fields.company.getText() + "', '" +
fields.address.getText() + "', '" +
fields.name.getText() + "', '" +
fields.hp.getText() + "', '" +
fields.phone.getText() + "', '" +
fields.fax.getText() + "', '" +
fields.email.getText() + "', '" +
fields.start.getText() + "', " +
Integer.valueOf(dd) + ", " +
Integer.valueOf(mm) + ", " +
Integer.valueOf(yy) + ", '" +
fields.mrc.getText() + "', '" +
fields.isp.getText() + "', '" +
fields.des.getText() + "', '" +
fields.sale.getText() + "', '" +
fields.mark.getText() + "')";

I may need to type S'pore in the fields.address.getText() but instead of ' i need to type " which is very troublesome... in this way how should i replace the " to ' ?

|||If you're not comfortable with this approach, create a function in your application that replaces the single quote to double quote. The sure-st way there is is to create a parameterized query

Inserting row with single quote

I'm inserting a row of people's names and addresses to a table. This seems to work great, unless the person has ' in their name, for example O' Riordan. When the address has single quote (such as Wilder's Path), I'd get the same error, unclosed quote when inserting the row.

What would be the best way to work around this, other than read the line and replace it with a space.

In a literal you need to double embedded quotes, so it'd be 'O''Riordan'.

The best solution is to use a parameterized query, then you don't have to worry about embedded quotes.

|||How to use parameterized query? the program i currently using are java.. can show me some examples on how to use parameterized query. ^-^|||

Parameterized query is very useful but you can also use string concatenation. I've written a sample on my blog on how to insert records with single quotes. Here's a sample TSQL script

PRINT 'This is the man''' + 's choice'

In order to do this, you use a two-single quote approach. Notice the use of a concatenation operator to separate the first part of the sentence and the one that comes after the single-quote and the use of a two-single quote approach. This simpy means that every time you have a single-quote character included in your string, you have to do string concatenation to accomodate the insertion of a single-qoute in your string.

|||

Take a look at these links

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

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

Hope this helps

|||Sorry.. after looking thru the website i still dun know how cause my sql statement is like that..

String query = "INSERT INTO addresses (" +
"company, address, name, hp, " +
"phone, fax, email, start, day, month, year, " +
"mrc, isp, des, sale, mark" +
") VALUES ('" +
fields.company.getText() + "', '" +
fields.address.getText() + "', '" +
fields.name.getText() + "', '" +
fields.hp.getText() + "', '" +
fields.phone.getText() + "', '" +
fields.fax.getText() + "', '" +
fields.email.getText() + "', '" +
fields.start.getText() + "', " +
Integer.valueOf(dd) + ", " +
Integer.valueOf(mm) + ", " +
Integer.valueOf(yy) + ", '" +
fields.mrc.getText() + "', '" +
fields.isp.getText() + "', '" +
fields.des.getText() + "', '" +
fields.sale.getText() + "', '" +
fields.mark.getText() + "')";

I may need to type S'pore in the fields.address.getText() but instead of ' i need to type " which is very troublesome... in this way how should i replace the " to ' ?

|||If you're not comfortable with this approach, create a function in your application that replaces the single quote to double quote. The sure-st way there is is to create a parameterized query

Inserting row and scope_identity()

With a query such as,

SET NoCount ON
INSERT INTO ...
VALUES ...
SELECT scope_identity()
SET NoCount OFF

, can I trust that the id I get back is for the row that I just inserted, or
can it be incorrect because of other "simultaneous" inserts? I need it for
an ASP page.

Thanks,
Marcus[posted and mailed, vnligen svara i nys]

Marcus (lumbus@.ludd.luth.se) writes:
> With a query such as,
> SET NoCount ON
> INSERT INTO ...
> VALUES ...
> SELECT scope_identity()
> SET NoCount OFF
> , can I trust that the id I get back is for the row that I just
> inserted, or can it be incorrect because of other "simultaneous"
> inserts? I need it for an ASP page.

800 grader, du kan lita p mej, du kan lita p mej.

Yes, you can trust it. scope_identity is local to your process - in
fact local to the scope (procedure, batch etc) you are in.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Tack s mycket - Thank you. ;)

Marcus

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94DC29958E0AYazorman@.127.0.0.1...
> [posted and mailed, vnligen svara i nys]
> Marcus (lumbus@.ludd.luth.se) writes:
> > With a query such as,
> > SET NoCount ON
> > INSERT INTO ...
> > VALUES ...
> > SELECT scope_identity()
> > SET NoCount OFF
> > , can I trust that the id I get back is for the row that I just
> > inserted, or can it be incorrect because of other "simultaneous"
> > inserts? I need it for an ASP page.
> 800 grader, du kan lita p mej, du kan lita p mej.
> Yes, you can trust it. scope_identity is local to your process - in
> fact local to the scope (procedure, batch etc) you are in.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tack s mycket - Thank you. ;)

Marcus

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94DC29958E0AYazorman@.127.0.0.1...
> [posted and mailed, vnligen svara i nys]
> Marcus (lumbus@.ludd.luth.se) writes:
> > With a query such as,
> > SET NoCount ON
> > INSERT INTO ...
> > VALUES ...
> > SELECT scope_identity()
> > SET NoCount OFF
> > , can I trust that the id I get back is for the row that I just
> > inserted, or can it be incorrect because of other "simultaneous"
> > inserts? I need it for an ASP page.
> 800 grader, du kan lita p mej, du kan lita p mej.
> Yes, you can trust it. scope_identity is local to your process - in
> fact local to the scope (procedure, batch etc) you are in.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Inserting row and scope_identity()

With a query such as,

SET NoCount ON
INSERT INTO ...
VALUES ...
SELECT scope_identity()
SET NoCount OFF

, can I trust that the id I get back is for the row that I just inserted, or
can it be incorrect because of other "simultaneous" inserts? I need it for
an ASP page.

Thanks,
Marcus[posted and mailed, vnligen svara i nys]

Marcus (lumbus@.ludd.luth.se) writes:
> With a query such as,
> SET NoCount ON
> INSERT INTO ...
> VALUES ...
> SELECT scope_identity()
> SET NoCount OFF
> , can I trust that the id I get back is for the row that I just
> inserted, or can it be incorrect because of other "simultaneous"
> inserts? I need it for an ASP page.

800 grader, du kan lita p mej, du kan lita p mej.

Yes, you can trust it. scope_identity is local to your process - in
fact local to the scope (procedure, batch etc) you are in.

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

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

inserting records - mswebtasks error

SQL Server 2005 on a Windows XP PC

If I "Open" a table and try to insert a new record, I get an error:

The data in row 100 was not comitted.

Error Source: .Net SqlClient Data Provider.

Error Message: [Microsoft][SQL Native CLient][Sql Server] Invalid object name 'msdb..mswebtasks'.

SQL web Assistant: Could not execute the SQL statement.

press ESC....

Any ideas ?

thanks

John

Do you have any triggers on the underlying table?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Thankyou for the triggers comment. There was an old dis-used trigger hiding in the background !

thanks

John

|||Brilliant! I'd been rebuilding indexes many times thinking I had a corruption. I too had old triggers hidden I didn't know about.

inserting records - mswebtasks error

SQL Server 2005 on a Windows XP PC

If I "Open" a table and try to insert a new record, I get an error:

The data in row 100 was not comitted.

Error Source: .Net SqlClient Data Provider.

Error Message: [Microsoft][SQL Native CLient][Sql Server] Invalid object name 'msdb..mswebtasks'.

SQL web Assistant: Could not execute the SQL statement.

press ESC....

Any ideas ?

thanks

John

Do you have any triggers on the underlying table?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Thankyou for the triggers comment. There was an old dis-used trigger hiding in the background !

thanks

John

|||Brilliant! I'd been rebuilding indexes many times thinking I had a corruption. I too had old triggers hidden I didn't know about.

Inserting NULL values on Date Fields trhough DAL

I am using a DAL and i want to insert a new row where one of the columns is DATE and itcanbe 'NULL'.

I am assigning SqlTypes.SqlDateTime.Null.

But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL??

how can i put an empty date in the database?

THANK YOU!!!

check ifthishelps.|||

That does not work. Besides, in your example you are NOT using DAL. You are inserting directly to the DB, through a SQL statement.

Thannks,

JeffKish

|||

The code in the link below uses ADO.NET parameters. Hope this helps.

http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark