multiple rows insert: command

Showing posts with label command. Show all posts

Friday, March 30, 2012

How to execute a dynamic sql command in user define func

Hi,
I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
thank you.
regards,
florenceleeIf you want the output of this dynamic SQL in a table,
you can use "insert into #Temp exec(@.finalstring)" and then process the temp
table. The #Temp table should be defined before.
"florencelee" wrote:
> Hi,
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> thank you.
> regards,
> florencelee
>|||Hi,
But we can not run the exec inside a user define
function, am i right?
For eg, in the store procedure, we can put exec
(@.finalstring) but how we write in the user define
function?
>--Original Message--
>If you want the output of this dynamic SQL in a table,
>you can use "insert into #Temp exec(@.finalstring)" and
then process the temp
>table. The #Temp table should be defined before.
>"florencelee" wrote:
>> Hi,
>> I have 3 string
>> select @.string = 'SELECT'
>> select @.string2 ='colname'
>> select @.string3 ='tablename'
>> select @.finalstring = @.string + @.string2 + @.string3
>> inside the user define function, we can not put in
>> exec command, how we run this dynamic sql command in
the
>> user define function?
>> thank you.
>> regards,
>> florencelee
>.
>|||See my other reply...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:38a301c49f9f$406bf590$a301280a@.phx.gbl...
> Hi,
> But we can not run the exec inside a user define
> function, am i right?
> For eg, in the store procedure, we can put exec
> (@.finalstring) but how we write in the user define
> function?
>
> >--Original Message--
> >If you want the output of this dynamic SQL in a table,
> >you can use "insert into #Temp exec(@.finalstring)" and
> then process the temp
> >table. The #Temp table should be defined before.
> >
> >"florencelee" wrote:
> >
> >> Hi,
> >> I have 3 string
> >>
> >> select @.string = 'SELECT'
> >> select @.string2 ='colname'
> >> select @.string3 ='tablename'
> >>
> >> select @.finalstring = @.string + @.string2 + @.string3
> >>
> >> inside the user define function, we can not put in
> >> exec command, how we run this dynamic sql command in
> the
> >> user define function?
> >>
> >> thank you.
> >> regards,
> >>
> >> florencelee
> >>
> >.
> >sql

Wednesday, March 28, 2012

How to enter this command line sql statement...??

:confused: I am trying to use a program called ODBCVIEW to query a database from the command line, and write it to text file. The instructions look easy, But I am illiterate to translating command line syntax.
I am not sure if I use the ">" , "[", etc... or if I leave them out. Can some one show me exactly how the finished code is supposed to look (in laymans)...

The program I am using to query the DB is called ODBCVIEW. Here is a link to the page with the syntax(located at the bottom) - http://www.slik.co.nz/HTML_help/odbc_view.htm

...and I have also pasted it below:

-COPIED FROM WEBSITE:
ODBCView also supports a non-interactive command line mode to execute a query and save the results to a text file. The syntax is as follows:

ODBCView.exe <DSN=DataSourceName;UID=User;PWD=Password> [<SQLScript.sql>] [<OutputFile.htm|csv|txt>]

Where:

DataSource
The desired datasource name.

UID
An optional user name to logon to the database

PWD
An optional user password

SQLScript
The query to execute.

OutputFile
The path to the output file. The files extension determines the format. Use htm or html to HTML, csv for CSV.If you are only trying to query a database and write the results to a file, better try WinSQL (http://www.download.com/3001-10255_4-10213451.html). ;)|||:D Just what I was looking for!

I'm installing it now - Does it have a 'command-line' mode?

Monday, March 26, 2012

How to encrypt and decrypt stored procedures?

I encrypt my procedures using with encryption clause, but I do not how to decrypt again.

Is there a command or utility for encrypt and decrypt in Sql 2000? How about Sql 2005?

Thanks

Haydee

Decryption is weak and can be cracked by searching on google for the specific algorithms, there was a thread sometime ago, which might be useful to you:

http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/34b309b76ba574b4

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens is right, the procedure encryption is actually referred to as obfuscation in Books Online. Also, there is no SQL Server command for decrypting it back.

Thanks
Laurentiu

|||

Thanks for your comments

and is there a tool in Sql Server 2005 in order to protect the code? What can I do? I need to install a project in the customer, and I would like to protect it.

Thanks again for your help.

Haydee

|||You could use third party components to accomplish this, there sure can be found some by searching in google for them.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI,

I am using MSDE 2000 and I will be deploying it with my software application. I have invested a good bit into my database schema and I don't want it to be viewed by others.

I can not see why some user can not take the .mdf (multiple mdf's actually) and sp_AttachDB or attach them to their instanced SQL server using EM. I of course do not want this.

Maybe someone can clear up the limitations and types of SQL security that can assure no one can simply attach the MDF to see the structure, let alone the data.

As far as I can see there is Network security as to authentication for a live/instanced SQL server and this has no ability to prevent an MDF from being re-attached and viewed/queried.

I also see EncryptByPassPhrase which I can use prior to executing a query (if I understand this process which is data remains in encrypted state until its about to be used, then decrypted in memory (I presume ? otherwise someone could grab a snapshot of the mdf while it's in decrypted state ? {or SQL server has a temp region when using encryption where it places the decrypted data I take it}) and then I have to encrypt it again after processing.

Neither of these look like they can obfuscate or lock the db schema information, such as table names, structures, fields, field types/attributes etc.

Sooooooo.....

How can I prevent a user from seeing the underlying table structures and does anyone know if column encryption will cost me 10 years off my life time wise on large data sets ?

Thanks

|||

See this recent thread for a discussion of this topic:

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

Thanks
Laurentiu

|||

The only feature for protecting code in SQL Server is the WITH ENCRYPTION clause that we discussed so far. It is weak not necessarily because the encryption is weak (it uses RC4), but because the encryption key can be easily found. An attacker will focus on finding the encryption key rather than breaking the encryption algorithm in such a solution. This is a general problem and for any solution you consider, you should look at how easy it is for someone to find the encryption key.

This is basically a DRM solution, and I have talked about the difficulty of creating an unbreakable DRM solution on other threads, more recently in:

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

Thanks
Laurentiu

How to encrypt all existing stored procedure?

I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.

But how about if I want encrypt existing stored procedures?

Which command should I use ?

how about scripting them all off, and using the following:

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

add your encryption, and you're all set......

|||There is no easy way. You have to script out the SQL modules (Create or Alter) and then add the WITH ENCRYPTION option to the definitions. There is no DDL to just encrypt SQL modules without specifying the definition. Why do you want to encrypt your code? The encryption mechanism is really obfuscation and it is a reversible encryption. So it is possible to get the original code from the encrypted content. If you want to protect your code then you should look at legal binding terms and copyright mechanisms.|||As of now, I mark this thread as no answer.

Wednesday, March 21, 2012

how to enable "open command box here" in Win2k3?

I know it's not the proper place for this question, but thank you if you
can tell me the answer or instruct me where I shall post this question.
Denys
MCSD for .Net, MCDBA*sigh* Please go to a Windows newsgroup. I wouldn't expect a
basket-weaving question in a BMW newsgroup, even if it was about how to
weave while in the back seat of a moving BMW.
"Denys Wang" <denyswang@.163.com> wrote in message
news:O0M68EOjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>I know it's not the proper place for this question, but thank you if you
>can tell me the answer or instruct me where I shall post this question.
> --
> Denys
> MCSD for .Net, MCDBA|||Aaron Bertrand [SQL Server MVP] wrote:
> *sigh* Please go to a Windows newsgroup. I wouldn't expect a
> basket-weaving question in a BMW newsgroup, even if it was about how
> to weave while in the back seat of a moving BMW.
>
What if you were looking to learn how to weave a basket in the shape of
a BMW?
David Gugick|||point taken.
Aaron Bertrand [SQL Server MVP] wrote:
> *sigh* Please go to a Windows newsgroup. I wouldn't expect a
> basket-weaving question in a BMW newsgroup, even if it was about how to
> weave while in the back seat of a moving BMW.
>
>
> "Denys Wang" <denyswang@.163.com> wrote in message
> news:O0M68EOjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>
>
>

Monday, March 19, 2012

How to edit this script to make it work right?

After run this command:
select 'sp_spaceused ' + name + 'go' from sysobjects where
type = 'U' order by name
I got results like:
sp_spaceused PSPRCSRQSTFILEgo
sp_spaceused PSPRCSRQSTMETAgo
sp_spaceused PSPRCSRQSTSTRNGgo
How to edit the script to seperate go at the line end and
make it like:
sp_spaceused PSPRCSRQSTFILE
go
sp_spaceused PSPRCSRQSTMETA
go
sp_spaceused PSPRCSRQSTSTRNG
goLookup REPLACE() function in SQL Server Books Online.
--
Anith|||Instead you can use :
SELECT 'EXEC sp_spaceused ' + name + CHAR(13) + 'GO'
FROM sysobjects
WHERE type = 'U'
ORDER BY name ;
--
Anith

Friday, March 9, 2012

how to Drop an index if exists

Hi all,

I come from MySQL.

In sqlserverce

I'm looking for an equivalent command..to

"DROP INDEX IF EXISTS inventory.idxItems"

or somthing similar so that I do not get an error or crash the C# app when I try to drop a nonexisting index..

also for droping a table.

Use can use the INFORMATION.SCHEMA veiws, both for tables and indexes, like this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ’MyUserTable’

or

SELECT INDEX_NAME
FROM INFORMATION_SCHEMA.INDEXES
WHERE INDEX_NAME = ’MyUserIndex’

For more information (not a lot though), see http://msdn2.microsoft.com/en-us/library/ms174156.aspx

Friday, February 24, 2012

How to do multiple rows insert?

I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;

Already tryed 2 methods.

1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();

Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.

2)
SqlCeCommand command = Connection.CreateCommand();

command.CommandText

= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";

if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();

command.ExecuteNonQuery();

Connection.Close();

Using this method i found out bug (or so i think).

I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.

Any help would be appreciated. Thnx in advance.

P.S.
Sorry for bad english.

Hi,
why open, execute and close?

Why not open at the beginning, do all your executes, and only close when you exit the program?

That should be much quicker

Pete

|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.

But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.

If I could cut data incertion to ~2 mins than it would be OK.|||

Try using parameterized queries for bulk inserts.

// Arranging Data in an Array.

const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;

// Do the inserts using Parameterized queries.

Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";

for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}

Connection.Close();

|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()

For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand

Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"

Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()

Debug.Print(Date.Now)

For loopCount = 1 To 10000

rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)

Next loopCount

Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try

Debug.Print(Date.Now)

End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.

My solution in the end.

connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines

strArray = buffer.Trim().Split(separator);

if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();

strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}

strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();

Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.

By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)

And in data file values are writen:

someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....

But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.

P.S.
Again thank you very much.

|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar

How to do multiple rows insert?

I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;

Already tryed 2 methods.

Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.

2)
SqlCeCommand command = Connection.CreateCommand();

command.CommandText

= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";

if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();

command.ExecuteNonQuery();

Connection.Close();

Using this method i found out bug (or so i think).

I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.

Any help would be appreciated. Thnx in advance.

P.S.
Sorry for bad english.

Hi,
why open, execute and close?

Why not open at the beginning, do all your executes, and only close when you exit the program?

That should be much quicker

Pete

|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.

But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.

If I could cut data incertion to ~2 mins than it would be OK.|||

Try using parameterized queries for bulk inserts.

// Arranging Data in an Array.

const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;

// Do the inserts using Parameterized queries.

Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";

for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}

Connection.Close();

|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert

Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer

cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn

For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount

My solution in the end.

Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.

By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)

And in data file values are writen:

someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....

But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.

Friday, March 30, 2012

How to execute a dynamic sql command in user define func

How to execute a dynamic sql command in user define func

Wednesday, March 28, 2012

How to enter this command line sql statement...??

Monday, March 26, 2012

How to encrypt and decrypt stored procedures?

How to encrypt all existing stored procedure?

Wednesday, March 21, 2012

how to enable "open command box here" in Win2k3?

Monday, March 19, 2012

How to edit this script to make it work right?

Friday, March 9, 2012

how to Drop an index if exists

Friday, February 24, 2012

How to do multiple rows insert?

How to do multiple rows insert?

multiple rows insert

Blog Archive

About Me