|
hi
which is better views or procedure where i have store the data and autogenrate a number and store its value in the database + doing some calculation for a paricular column in the web page code behind and storing the data in the same table.
thanks
sasi
|
|
|
|
|
Personally, I prefer to access all data through stored procedures as this adds some extra protection in my application as I can revoke the access to the tables which means that if anyone gains access they can only do what the stored procedures petmit them, rather than have unfettered access to the tables directly.
Views kind of come in the same category as they allow the actual tables to be hidden behind the façade of the view.
There are not really any advantages or disadvatages either way. It depends on how you prefer to work. If the calculation is simple enough then a view will be fine.
However, if you require to filter the rows at any point then a stored procedure may be better as you can filter early and this sometimes helps the query optimiser find a better plan.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
Can someone help me with this one i have writen a function that retrieves a Id from a table but the result is not found and i get the above error.
Public Function getID(ByVal strTable As String, ByVal strValue As String) As String
Try
Dim IDConn As SqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=EventMngt")
Dim IDString As String = "SELECT Id FROM Boekelo_Horses WHERE Horse_Passport = '" & strValue & "'"
If strTable = "Boekelo_Riders" Then
IDString = "SELECT * FROM Boekelo_Riders WHERE Rider_FEIId = '" & strValue & "'"
End If
Dim IdCommand As New SqlCommand(IDString, IDConn)
IDConn.Open()
Dim IDReader As SqlDataReader = IdCommand.ExecuteReader()
IDReader.Read()
getID = IDReader.GetString(0)
IDConn.Close()
Catch ex As Exception
MessageLabel.Text = strValue & " is not on the DataFile, try again.<br>Error text:" & ex.Message
getID = "-error-"
End Try
End Function
If i comment out the getID = IDRea... line then the function returns no error, if in the function it produces a error. But why??
|
|
|
|
|
gharry wrote: i have writen a function that retrieves a Id from a table but the result is not found and i get the above error
If there is no result then what can it cast to a string?
You need to check if the result is null first, then get the value (if it exists).
Use:
IDReader.IsDBNull(0) to determine if the value is null before trying to get an actual string value.
See Also: SqlDataReader.IsDBNull()[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
I replaced some code to check on it but no result.
Dim IDReader As SqlDataReader = IdCommand.ExecuteReader()
IDReader.Read()
If Not IDReader.IsDBNull(0) Then
getID = IDReader.GetString(0)
Else
MessageLabel.Text = strValue & " is not on the DataFile."
getID = "-nodata-"
End If
|
|
|
|
|
O stupid me, i found the problem.
Tryed to get a Id field that is autonumbering i forgot that that colomn is not a string but a Int32 value. After changing that it works
|
|
|
|
|
How can the location (ip address/hostname and instance) be changed for a publication/subscription merge replication scenario? Basically I have SERVER01\SQLInstance with a database that is setup to publish to itself as a distributor, then I have SERVER02\SQLInstance with a database that is a subscribor to that distributor. If I change the NETBIOS name of SERVER02 or if I am using IP addresses instead of NETBIOS names and the IP address of either machine changes, the syncronization will fail. Is there a way short of creating a new subscription that I can keep the syncronization working. IE. Can I change where the subscription is mapped to on the fly?
-Kevin
|
|
|
|
|
Hello
MS SQL Server do not have database security, if we stop the service and copy the database file to other computer we can see the schema & data without any password !
So is there is any method for securing the data in SQL Server ?
Thanks for any help![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Fad B wrote: MS SQL Server do not have database security,
Actually, it has quite a lot of security.
Fad B wrote: if we stop the service and copy the database file to other computer we can see the schema & data without any password
That is due to your misunderstanding of how it works, not a lack of security on the part of SQL Server. If you stop the service that controls the databases there isn't much SQL Server can do to secure your data.
Fad B wrote: So is there is any method for securing the data in SQL Server
You need to put in place a comprehensive policy of who gets access to the filesystem on the server so that they cannot move files around arbirtrarily. This also applies to backups. Who gets the physical media and where do they go has to be part of a comprehensive security policy.
You cannot place all of your security in one location. It is a multifaceted problem that must be tackled on all levels. You have seen that by stopping the SQL Server Service (something that won't happen in normal operation) you can get at the files, move them and attach them thus bypassing the inbuilt security in SQL Server. So, you must therefor secure your file system.
There is no single button that you can press on any computer system that will make it secure. As there is no way that it can be done. If you over secure a system then people who have a genuine need cannot access the information they need, if you undersecure it then people who shouldn't have access may be able to gain access. What access is permitted and what is not is determined by the business that it runs in.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
 Colin Angus Mackay wrote: You need to put in place a comprehensive policy of who gets access to the filesystem on the server so that they cannot move files around arbirtrarily. This also applies to backups. Who gets the physical media and where do they go has to be part of a comprehensive security policy.
You cannot place all of your security in one location. It is a multifaceted problem that must be tackled on all levels. You have seen that by stopping the SQL Server Service (something that won't happen in normal operation) you can get at the files, move them and attach them thus bypassing the inbuilt security in SQL Server. So, you must therefor secure your file system.
There is no single button that you can press on any computer system that will make it secure. As there is no way that it can be done. If you over secure a system then people who have a genuine need cannot access the information they need, if you undersecure it then people who shouldn't have access may be able to gain access. What access is permitted and what is not is determined by the business that it runs in.
Excellent points made. Security is in the eye of the beholder.
PC
|
|
|
|
|
Ok,
But I did not mean that,
I have SQL Desctop Engin & I'm installing it on any PC
I need to secure my data schema ! and my data !
Since I put them on install CD... so any one will install the database and get its desin...
So any solution to encrypt the database file without the help of the OS ?
Thanks
|
|
|
|
|
Ok,
But I did not mean that,
I have SQL Desctop Engin & I'm installing it on any PC
I need to secure my data schema ! and my data !
Since I put them on install CD... so any one will install the database and get its desin...
So any solution to encrypt the database file without the help of the OS ?
Thanks
|
|
|
|
|
so i've been collecting text files with data separated with ';'
I have been using a schema.ini file to define each text file...
example:
schema.ini:
[text.txt]
Format=Delimited(\;\)
ColNameHeader=False
MaxScanRows=0
Character=OEM
Col1=File Text
Col2=problem Text
Col3=serial Text
Col4=name Text
Col5=cube Text
Col6=time Text
data in text.txt:
ASSET;S/N-BIOS;XXXXXX;DPCSXXX;1KXXX;11/21/2005 5:57:43 AM
now, the data i'm going to be receiving in the the near future has different kinds of data separated by blocks all in the same text file. for example
data in text.txt:
[AssetData]
ASSET;S/N-BIOS;XXXXXX;DPCSXXX;1KXXX;11/21/2005 5:57:43 AM
[UserData]
XXXX;XX;XX;XX
How can I parse this data(previously using a Oledb connection with the schema.ini) into individual datasets?
Thanks alot in advanced
-- modified at 12:34 Thursday 29th December, 2005
|
|
|
|
|
I am having trouble to get export from sql. I can get import working well. When I am importing xls to sql and it was working fine. when I am exporting to new file and I tried to open xls file and it said it cannot open file bec of poor data. I have no idea how can I fix this one. I am hoping that yo can help this one.
_BulkCopyPtr spIBulkCopy;
HRESULT hr = S_OK;
spIBulkCopy.CreateInstance(__uuidof(BulkCopy));
CString strFile;
m_EditTextBox.GetWindowText(strFile);
spIBulkCopy->DataFilePath = strFile.AllocSysString();
spIBulkCopy->DataFileType = SQLDMODataFile_TabDelimitedChar;
//spIBulkCopy->DataFileType = SQLDMODataFile_NativeFormat;
spIBulkCopy->ErrorFilePath = "c:\errores.log";
spIBulkCopy->ServerBCPDataFileType = SQLDMOBCPDataFile_Native;
spIBulkCopy->UseBulkCopyOption = true;
//spIBulkCopy->UseExistingConnection = true;
spIBulkCopy->IncludeIdentityValues = true;
m_ctrlDBTable.GetWindowText(strFile);
_bstr_t bstrFile(strFile);
int nCount = m_spITables->Count;
for (int i=0; i<ncount; i++)
="" {
="" m_spitable="m_spITables-">Item(i + 1, vtMissing);
if(m_spITable->GetName().operator==( bstrFile))
break;
m_spITable = NULL;
}
hr=m_spITable->ExportData(spIBulkCopy);//it worked with importData
|
|
|
|
|
GoodQuestion wrote: Export xls from sql
The easiest way to export data from a database to an excel file is through an Select into querry. I know this works on an Access db, never thought of trying it on an SQL server but it should still work (using ADO).
CString str;
str.Format("SELECT * INTO [Excel 8.0;DATABASE=%s].[%s] FROM [%s]", ExcelFile, Worksheet, table);
//pConn is a ADODB Connection
pConn->Execute(str.AllocSysString(), NULL, adExecuteNoRecords);
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
-- modified at 6:31 Thursday 5th January, 2006
|
|
|
|
|
Thanks for reply my question. That means to me alot. Sadly, I cant get it working bec of DB_E_ERRORSINCOMMAND stuff. I dont understand what is DB_E_ERRORSINCOMMAND for? But I know error is related to spIConnection->Execute bec that is where error came from. can you please explain what is its cause? THANK YOU!!
_bstr_t bstrConnect( L"driver={sql server};"
L"server=COYDELL;"
L"Database=brntbl;UID=sa;PWD=;" );
_ConnectionPtr spIConnection;
spIConnection.CreateInstance( __uuidof(Connection ) );
spIConnection->ConnectionString = bstrConnect;
spIConnection->Open("DSN=BurnDB","","", NULL);
CString bstrCommand;
bstrCommand.Format("SELECT * INTO [Excel 8.0;DATABASE=C:\\exceltest.xls].[HoleDiameters$] FROM [HoleDiameters]");
HRESULT hr = spIConnection->Execute(bstrCommand.AllocSysString(),NULL,adExecuteNoRecords);
spIConnection->Close();
"HoleDiameters" is name of sheet and Table from DB
|
|
|
|
|
Hello,
I have a similar post in Asp.Net forum. My questions are related with Asp.Net 2.0 and SQL 2005 so I am not sure if this shouldn't be the best forum to post this message.
I am working on an Asp.Net 2.0 / MSSQL 2005 web site with authentication.
My web site has two type of users: "students" and "professors". They are not related!
All users need to login. Students use the role "student" and professors use the role "professor".
The information for students is different from the information of professors.
I could put all students and professors in the same table and leave some fields blank.
My option was to create the following tables:
A) "Authentication" with the fields:
[UserId], [Password], [PasswordFormat], [MobilePIN], [Email], [PasswordQuestion],
[PasswordAnswer], [IsApproved], [CreateDate], [LastLoginID] and [UserRole].
B) "Students" with the field [StudentID] and all the information fields for students.
C) "Professors" with the field [ProfessorID] and all the information fields for professors.
My questions are:
1. Am I following the right approach by using these 3 tables?
2. Should I place all info from users (students, professors and authentication) in "Authentication" table?
3. Should I keep only two tables, "Students" and "Professors" and move the login information to each record in these tables?
4. If using the 3 tables how can I know the info for a user after it logins? I could use a foreign key in Authentication table but I have 2 tables: students and professors.
Anyway this is really confusing.
Can someone, please, give me some advice of how to create the tables and set this authentication?
Thanks,
Miguel
|
|
|
|
|
Miguel,
Your approach is good, and placing all user info in one table is okay. If you were to use two tables, then you'd probably need separate login pages for students and professors. For the last question, I would keep track of the session (or a cookie, this would require cookies to be enabled on the one hand) and tie it to the user, maybe in the authentication table. I do not know much about asp.net but this is how I would do it with a database and php. Seems like you are on track and I hope this helps some.
Paul
|
|
|
|
|
If you were to go with a session id, you'd need a table holding the session id and the user associated to the session id. All you'd have to do is pass the session id from page to page.
Paul
|
|
|
|
|
hi
i have the following code
the code
CREATE PROCEDURE proc_InsertOrder
(@OrderId int output,
@CustomerName varchar(50),
@ShippingAddress varchar(50)
)
AS
INSERT INTO Orders (CustomerName, ShippingAddress)
VALUES
(@CustomerName, @ShippingAddress)
SELECT @OrderId=@@IDENTITY
when i try to execute it asks for declaration of the OrderID if i declare then it asks for declaration of CustomerName now i dont know how to deal with this situation can anyone tell what i can do.
Procedure 'proc_InsertOrder' expects parameter '@OrderId', which was not supplied.
or is there any proper code for the decalaration
thanks
sasire18
|
|
|
|
|
It depends on how you are executing the procedure. As per my knowledge the correct way of executing the procedure is :
Declare @OrderID int
exec proc_InsertOrder @OrderId output, 'Akif', 'Test'
Akif
|
|
|
|
|
write an asp page that actually creates an xml document
And in that page, after the xml document is created, the stored procedure should be called and the xml document should be passed as the input parameter
suneel
|
|
|
|
|
I want to create an xml document and want to store in sqlserver.
And i want to create xml document in asp page and want to store in database as input parameter using stored procedure.
Can any body help to me
please send to suneel@softwarenucleus.com
suneel
|
|
|
|
|
Can you tell me urgency to store whole file in sql server database.
Instead, u can save url of the file and file physically to some folder in server.
Any systematic work reflects its significance for a long time, Though I m not totally against "The Prototyping".So let's discuss in depth...
|
|
|
|
|
Iam happy about your reply. thank u.
As i have two stored procedures in my project, so i want to get all details from one stored procedure and i want to send the information as input paramenter to the second stored procedure.
suneel
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
