Database

Re: Need to execute a very time consuming Stored procedure on a remote machine.

17-Mar-08 6:18

well if it doesnt go too well, can u give me some other alternative?

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Mark J. Miller17-Mar-08 6:34

17-Mar-08 6:34

I'm assuming your reference to a tool you found on CP implies you can create executables on the server and run them. Knowing exactly the amount of control you have over the the remote machine would make it easier to point to a single possible solution.

Can you setup a windows task?

If so I would use the sqlcmd tool: http://msdn2.microsoft.com/en-us/library/ms165702.aspx[^]. You could then create a .bat or .cmd file and tell it to execute the stored procedure with a single line of code and reference the file from windows task scheduler and tell it when to execute. Then it will execute without requiring you to be logged into the remote server.

The command would look something like this:

<br />
sqlcmd -S (local) -d databaseName -E -Q "exec storedProcname 'value1', 2, 'value3'"<br />

And the task would run as you or a windows account with permissions to the server/database. Or if you're using SQL Authentication:

<br />
sqlcmd -S (local) -d databaseName -u username -p myp@ssw0rd -Q "exec storedProcname 'value1', 2, 'value3'"<br />

NOTE: the parameter flags are case sensitive (-Q is not the same as -q).

This would be the best solution because any other solution would probably require you to be logged into the machine somehow (terminal services, etc.). This would still work as long as you can disconnect your session w/o it logging you off the machine which would allow any processes you start to continue to run. But setting up a task would be better, especially if for tasks which need to be run more than one time.

Re: Need to execute a very time consuming Stored procedure on a remote machine.

Rocky#17-Mar-08 6:46

Re: Need to execute a very time consuming Stored procedure on a remote machine.

17-Mar-08 6:46

Mark J. Miller wrote:
Can you setup a windows task?

oh no I'm afraid I can't. and the worse thing is that I can't even chk the status of the job I executed it coz I dont have any permission on the system table that is used to get the status of the job.

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Mark J. Miller17-Mar-08 6:56

17-Mar-08 6:56

What kind of access rights do you have on the remote machine? Can you use some sort of remote desktop tool?

In order to do what you describe you will need to somehow start a process on the remote machine from which you can disconnect w/o killing the process which is running on the remote machine.

Otherwise, if you don't have any access other than file copy and dbo access to the database then you'll have to ask the administrator of the machine to run the sqlcmd script for you I mentioned in my last post.

Re: Need to execute a very time consuming Stored procedure on a remote machine.

Rocky#17-Mar-08 7:11

avoiding 1/1/1900 in SQL SERVER?

17-Mar-08 7:11

Mark J. Miller wrote:
you'll have to ask the administrator of the machine to run the sqlcmd script for you

I think thats what I need to do. ur right!

its a shared server so they wont take the risk of allowing us to run windows services

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

John Sundar14-Mar-08 23:03

John Sundar

14-Mar-08 23:03

while updating or inserting a record in SQL SERVER, by default the DATETIME variable getting 1/1/1900. i need a trigger, so that whenever UPDATE or INSERT is made in a particular table, and if datetime consist of 1/1/1900, those values should replace as NULL.... can anyone provide the trigger for this? or is there anyother solution?

Currently i m using ASP.NET with C# (.net 2003, 1.1 framework) - KARAN

Re: avoiding 1/1/1900 in SQL SERVER?

Rocky#15-Mar-08 1:06

Re: avoiding 1/1/1900 in SQL SERVER?

15-Mar-08 1:06

I'm sorry I can write the whole Trigger for u but I'm gonna give u a few clues to start with . First you need to make an INSTEAD OF trigger for insert and updates

in that you will use the updated and inserted values. if the date in the inserted table matches 1/1/1900 dont issue an insert/update statement. Otherwise execute the insert statement.

I hope this will help you

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Mark J. Miller17-Mar-08 4:54

17-Mar-08 4:54

If you have full control of the application's source code, a better solution than a Trigger would be to check the value in the application code for 1/1/1900. If it matches 1/1/1900, then set the parameter value for your SqlParameter object to DbNull.Value instead of the actual datatime value. If you aren't using SqlParameter objects (which you should be) then set the value of the string to "NULL".

Example 1:

<br />
using(SqlCommand cmd = new SqlCommand(.. set ctor arguments ..)){<br />
  // setup cmd object<br />
<br />
  cmd.Properties.Add("@date", SqlDbType.DateTime);<br />
  if(myDateVariable.ToShortDateString() == "1/1/1900")<br />
    cmd.Properties["@date"] = DbNull.Value;<br />
  else<br />
    cmd.Properties["@date"] = myDateVariable;<br />
}<br />

Example 2:

<br />
StringBuilder sql = new StringBuilder("INSERT INTO myTable(dateColumn) VALUES(");<br />
if(myDateVariable.ToShortDateString() == "1/1/1900")<br />
  sql.Append("NULL");<br />
else<br />
  sql.Append(String.Format("'{0}'", myDateVariable));<br />

NOTE: The second example will expose you to Sql Injection attacks if the value you are concatenating to your SQL statement is a String variable, so I recommend the first method for all SQL statements.

Best Practice: Connection Strings to MS-SQL datasource from web application

Leo Smith14-Mar-08 9:18

Leo Smith

14-Mar-08 9:18

I have a question on what is the best method for database connections from a web page to an MS-SQL database (2005 currently)? Let me explain what this is coming from. I am cureently trying to define a standard practice of setting up our web apps from a web server point of view. I don't want Windows Authentication due to multiple browsers hitting and no guarentee that network user names and passwords would be encrypted (I know in the past netscape would pass the user names as clear text, I don't know if this has changed). So I wanted to use a domain name for the anonymous account (easy to port accounts between servers, since nothing has to be duplicated). Now comes the catch to the plan. When I try to setup the SQL connection string I run into several problems. I will list the two wethods I have tried and the problems assoicated with them.

1. <add name="CommonConnectString" connectionString="Data Source=Davinci;Initial Catalog=CommonTable;Persist Security Info=True;User ID=Local_DBO;Password=Ac90_&0w" providerName="System.Data.SqlClient"/>

Problem I have with this method is setup and replication of database. When the tables are replicated between servers, I have to delete the user account and recreate it for the new database (the old SID's don't work on the new machine).

2. <add name="CommonConnectString" connectionString="Trusted_Connection=Yes;APP=Microsoft Data Access Components;SERVER=DAVINCI;DATABASE=Common;UID=domain\webdbo;" providerName="System.Data.SqlClient"/>

This method works fine from a windows application, but won't work from a web application unless windows authenication is turned on (Account has been added to database with proper access rights). Replication should work, though I can't get this string to work, since the SID's are not on the server or the database.

Any assistance in helping me find a way to setup the servers to allow for replication without having to go back and delete and recreate users and account would be greatly appreciated (articles on the subject would assist in not having to get into a lengthy discussion, or assistance on the problem or limitations of the second connection string are probably going to be the easiest to deal with).

Thanks for any help,

Leo T. Smith
Program/Analyst Supervisor

Re: Best Practice: Connection Strings to MS-SQL datasource from web application

Mark J. Miller17-Mar-08 5:07

17-Mar-08 5:07

I would recommend using Windows Authentication. You can set it up independently of wither or not the web application is using windows authentication (ie. you can still use it when the web application is set to 'enable anonymous access'). You'll need to configure the application pool identity to a domain account. Then instead of adding a windows login to your SQL Server instances for the account add a group and grant permissions on your SQL Servers to that group. Then you can manage the user accounts used by your web server using your domain controller w/o affecting your sql servers or worring about SID issues.

I'm assuming your application doesn't require Windows Authentication, but just to cover all the bases in case you need it - you can temporarily set the identity of the user for a block of code and impersonate the windows domain account when you need to for database access while maintaining the identity of the user for all other contexts w/in the application.

Storing Compressed Image Into SQL Server

abdurrahman oğuz14-Mar-08 6:30

abdurrahman oğuz

14-Mar-08 6:30

i use BmpToArray function to store compressed image into sql server 2005 (column's type is image). when i tried to decompress it by using ArrayToBmp function, i received an array which is filled by 0's. so the result is wrong...

is there anything wrong?

Public Function BmpToArray(ByVal bmp As Bitmap) As Byte()
    If Not bmp Is Nothing Then
        Dim srcStream As New MemoryStream()
        Dim destStream As New MemoryStream()

        Try
            bmp.Save(srcStream, System.Drawing.Imaging.ImageFormat.Bmp)

            Dim buffer(CType(srcStream.Length - 1, Integer)) As Byte
            Dim gZip As New GZipStream(destStream, CompressionMode.Compress)

            gZip.Write(buffer, 0, buffer.Length)
            gZip.Flush()
            gZip.Close()
            gZip = Nothing

            Return destStream.ToArray()
        Finally
            srcStream.Close()
        End Try
    End If

    Return Nothing
End Function

Public Function ArrayToBmp(ByVal arr() As Byte) As Bitmap
    If (arr.Length > 0) Then
        Dim srcStream As New MemoryStream(arr)
        Dim destStream As New MemoryStream()

        Try
            Dim buffer(1024) As Byte
            Dim gZip As New GZipStream(srcStream, CompressionMode.Decompress)
            Dim readingByte As Integer = gZip.Read(buffer, 0, 1024)

            Do While readingByte > 0
                destStream.Write(buffer, 0, readingByte)
                readingByte = gZip.Read(buffer, 0, 1024)
            Loop

            gZip.Flush()
            gZip.Close()
            gZip = Nothing

            Return New Bitmap(destStream)
        Finally
            destStream.Close()
            srcStream.Close()
        End Try
    End If

    Return Nothing
End Function

thanks...

Re: Storing Compressed Image Into SQL Server

Rob Philpott14-Mar-08 6:38

Rob Philpott

14-Mar-08 6:38

I struggle a bit to read VB being more a C# man myself, but it looks like when you're compressing the array, you create a buffer the size of the stream and compress that without copying the data from the stream to the buffer first - hence compressing an empty array.

Also on the other side, you might need to seek to the beginning of the destination stream before creating the bitmap from it. Not sure about this though.

Regards,
Rob Philpott.

Re: Storing Compressed Image Into SQL Server [modified]

abdurrahman oğuz14-Mar-08 22:40

abdurrahman oğuz

14-Mar-08 22:40

thanks Rob

you are right! i was trying to compress 0's. i forgot to initialize the buffer. whenever init the buffer, everything is OK!

modified on Saturday, March 15, 2008 5:51 AM

Re: Storing Compressed Image Into SQL Server

Mark Churchill16-Mar-08 1:57

Mark Churchill

16-Mar-08 1:57

Just out of interest, why are you using Gzip? Is it a requirement that the images are stored in a lossless format?

Have you considered gif or png? Both of these are designed to compress images...

Mark Churchill
Director
Dunn & Churchill
Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.

insert user checkboxlist selections in table

C. L. Phillip14-Mar-08 4:26

C. L. Phillip

14-Mar-08 4:26

I have a single asp.net page with a checkboxlist to allow multiple selections for a field. On the database side i have a "main table" that is used to store log data the user types in. I have a table that is bound to the checklistbox with the selection options. finally i have a table to store the associated selections, especially it stores foreign keys for the selection options id, and the associated log id.

So my problem is .... how do i properly construct the insert so that I can insert the log into the "main table" and also the various checkboxlist selections into the other table with a single insert ?

Re: insert user checkboxlist selections in table

Mark J. Miller14-Mar-08 6:28

14-Mar-08 6:28

Assuming the only insert you need to make is to your selected options table (Log is already created, no edits required to the options table because they are simply for displaying available options), you can try something like this:

http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx[^]

Create a TVF which uses either the T-SQL or CLR method described in the article and pass the selected options and the log id to a stored procedure which uses it like this:

<br />
CREATE PROCEDURE dbo.AddSelectedOptions<br />
@LogId INT,<br />
@options VARCHAR(??)<br />
AS<br />
<br />
INSERT INTO selectedOptions(LogId, OptionId)<br />
SELECT @LogId, Data FROM dbo.function_string_to_table(@options)<br />
<br />
GO<br />

NOTE: "Data" would be the optionId as it is returned from the function defined in the article.