|
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
|
|
|
|
|
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.
|
|
|
|
|
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...
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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...
|
|
|
|
|
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 ?
|
|
|
|
|
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.
|
|
|
|
|
oh great. thank you so much!!
|
|
|
|
|
hi all,
I am trying to copy the records from one table to another like this...
INSERT INTO [Claims].[dbo].[DocumentData]<br />
([DocumentId]<br />
,[DocumentData]<br />
,[DocumentName]<br />
,[ContentType]<br />
,[LastUser]<br />
,[LastDate])<br />
VALUES<br />
(SELECT DocumentQueueId, DocumentData, DocumentName, ContentType, AssignedTo, GetDate() FROM DocumentQueue WHERE (AssignedTo = 'nbad\SlabbertH'))
yet i get an error:
Msg 156, Level 15, State 1, Line 14<br />
Incorrect syntax near the keyword 'SELECT'.<br />
Msg 102, Level 15, State 1, Line 14<br />
Incorrect syntax near ')'.
what is the correct syntax for copying the records from one table to another?
After i copied the records to the other table i will truncate the previouse table....
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Remove 'Values' for select statement and also one ')' at the end of select statement
like
insert into Table()
(select from table1)
|
|
|
|
|
thank you... if this works... it would be a great start to a great weekend 
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
It's my pleasure to help you.
|
|
|
|
|
Thank you,
That did the trick...
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
hi
i want to fetch records from table where values in the column can be
(((joan AND smith) OR (sally AND rogers) OR ((pete OR peter) AND miller)) AND ((barbeque OR bbq OR bb-q) OR sauce OR chicken OR chikn OR flavor))
|
|
|
|
|
firstly what DB are you using MySql Oracle Access or MS SQL
|
|
|
|
|
|
I think this will help.
SELECT Col1, ColToSearch
FROM table
WHERE CONTAINS (ColToSearch, '"quick easy" or "Help"')
you must have full text index setup on the table field you are searching
|
|
|
|
|
Hi, Is there anyway to have SQL Server 2000 run a function on a database if a specific table is updated or a row added?
my problem is this I am having to work with two seperate databses for 2 sepreate programs, I need data from the first program in my second program,
but if anybody adds a new row or record to my table in database 1, I want to update the table in my second database using an sql server auto function, and the server must run a change on db2 if the table in db 1 is changed.
I hope this made sense, the main reason for doing this I do not have access to the source code for the first program so this must happen on the DB side.
|
|
|
|
|
i dont know exactly but i think it can be done with the help of Triggers
|
|
|
|
|
thanks man, I been searching the whole day for SQL watch, not trigger lol thanks man.
|
|
|
|
|
I have written a C# dll which makes a call to some unmanaged code.
When i tried to register this dll on SQL server 2005 i got the following error.
Msg 6215, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'AddNumbers' on type 'Project1._Class1' in safe assembly 'Interop.Project1' has invalid attribute 0x1003.
How do i register such a dll.
|
|
|
|
|
|
Good Morning All
i have the Following Cursor that Displays data , like this
<br />
SET NOCOUNT ON<br />
Declare @Property_ID int ,<br />
@Lis_key Varchar(12),<br />
@Effective_DAte Datetime,<br />
@Func_key Varchar(12),<br />
@Property_ID2 int ,<br />
@Lis_key2 Varchar(12),<br />
@Effective_DAte2 Datetime,<br />
@Func_key2 Varchar(12)<br />
<br />
Declare Find_Val Cursor for <br />
select p.Property_ID, p.Lis_key,v.Effective_Date,<br />
p.Func_key from Valuation v<br />
Inner Join Property p <br />
on p.Property_ID = v.Property_ID<br />
where V.Effective_date ='2002/07/01'<br />
And v.Val_Status_ID = 2<br />
And v.Archive_Date is NUll<br />
And p.Archive_Date is Null<br />
<br />
Open Cursor Find_Val<br />
<br />
FETCH NEXT FROM Find_Val INTO<br />
@Property_ID,<br />
@Lis_key,<br />
@Effective_DAte,<br />
@Func_key<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SELECT @Property_ID,@Lis_key , @Effective_Date , @Func_key<br />
<br />
--Declare<br />
DECLARE Second_cursor CURSOR FOR <br />
select V.Property_ID, P.Lis_key,v.Effective_Date,<br />
p.Func_key from Valuation v, Property p<br />
where v.Property_ID = @Property_ID<br />
And EFFECTIVE_DATE = '2008/07/01'<br />
<br />
OPEN Second_cursor<br />
<br />
FETCH NEXT FROM Second_cursor INTO<br />
<br />
@Property_ID2,<br />
<br />
@Lis_key2,<br />
<br />
@Effective_DAte2,<br />
<br />
@Func_key2<br />
<br />
IF @@FETCH_STATUS <> 0 <br />
<br />
WHILE @@FETCH_STATUS = 0<br />
<br />
BEGIN<br />
<br />
SELECT @Property_ID2,<br />
<br />
@Lis_key2,<br />
<br />
@Effective_DAte2,<br />
<br />
@Func_key2<br />
<br />
<br />
FETCH NEXT FROM product_cursor INTO <br />
<br />
@Property_ID2,<br />
<br />
@Lis_key2,<br />
<br />
@Effective_DAte2,<br />
<br />
@Func_key2<br />
<br />
END<br />
<br />
CLOSE Second_cursor<br />
<br />
DEALLOCATE Second_cursor<br />
<br />
FETCH NEXT FROM Find_Val into<br />
@Property_ID,<br />
<br />
@Lis_key,<br />
<br />
@Effective_DAte,<br />
<br />
@Func_key<br />
<br />
End <br />
<br />
CLOSE Find_Val<br />
<br />
DEALLOCATE Find_Val<br />
And when i run this Cursor i get the Following Error
Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Cursor'.
And this Points to this line
Open Cursor Find_Val
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Vuyiswa wrote: And this Points to this line
Open Cursor Find_Val
The CURSOR keyword isn't required here, only OPEN <cursorname>.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
