|
The trouble with this approach is that SQL Server generates and caches an execution plan the first time you run a stored procedure, then reuses that execution plan every time until it hasn't been used for long enough to be discarded. Only on the next execution will it then be recompiled.
If you have one of these amorphous stored procedures, where the behaviour is very different - effectively using different columns in the WHERE or ORDER BY clauses - the cached execution plan for one set of parameters can be very wrong for a different set. This translates into bad and unpredictable performance. You can force compilation of the execution plan every time by specifying WITH RECOMPILE, but obviously you incur the overhead of computing the execution plan every time it's executed.
It may well be better to use dynamic SQL explicitly from the client. You can still prevent SQL injection by using replaceable parameters in your SQL query text, and this will actually improve execution plan compilation times as SQL Server prefers retaining execution plans from parameterized versus non-parameterized queries (SQL Server caches all computed execution plans unless specifically told not to, but gives greater weight to stored procedures over parameterized text over non-parameterized text, so the more structured queries are retained longer).
You do lose the access control benefits of stored procedures as the user account used to query the database must be given SELECT access to the tables being queried (rather than just GRANTing access to EXEC the stored procedure).
|
|
|
|
|
Or, you can build the string within the proc, and get the same result without the loss of access control ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I want to make a column in a certain table ,this column consists of 3 chars an 6 autoincrement numbers like "abc-123456" .Furthermore this column is a primary key
how can i do this?
thanks
Mohammed Sobhi
|
|
|
|
|
Create a stored procedure to insert rows into the table.
In the stored procedure extract the last 6 characters from the ID column
and find the maxmimum number.
Add 1 to that number and insert it into the table along with the
other arguments.
Regards,
Arun Kumar.A
|
|
|
|
|
your db will perform better if your keys are numbers instead of strings. However, as someone else said, you can do this, but you can't make it autoincrement, you need to increment it yourself.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
hi all,
i use following two string to connect database located at remote sql server (mean on internet)
sql connection string
SqlConn = "Data Source=xxx.xxx.xx.xx\server;Network Library=DBMSSOCN;Initial Catalog=databaseName;User ID=Databaseuser ; Password=DatabasePassword;"
error:
“An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)”
OLEDB connection string
oledbConn = "provider=SQLOLEDB; "Data Source=xxx.xxx.xx.xx\server;Network Library=DBMSSOCN;Initial Catalog=databaseName;User ID=Databaseuser; Password=DatabasePassword;"
error:
“[DBNETLIB][ConnectionOpen (Connect())]Specified SQL server not found”
I already have done the following steps:
1. Enable the TCP/IP protocol using the Surface Area Configuration Utility
2. the TCP/IP protocol is enabled in the SQL Server Configuration Utility
3. the SQL Server browser is started.
4. SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.
i dont know where i m wrong. Please Help Me
any help will be appriciated. thanks
Rupesh Kumar Swami
|
|
|
|
|
Is there only one sql server instance on that server. If that is true then your connection string is wrong. You just need to be in the IP address not IP address/server. If you do have multiple sql server instances you may need to set the one you want to use to a static port and then add that port to your connection string. If you need more details on this let me know.
Hope that helps.
Ben
|
|
|
|
|
hi ben,
according to ur specification, i remove the server name from my connection string. but now this time, i face new error which is following
when i use following SQLConnection string (vb.net)
SqlConn = " Data source=xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A socket operation was attempted to an unreachable host.)
when i use following SQLConnection string (vb.net)
SqlConn = " Data source=http://xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
and when i use oledbconnection string (vb.net)
oledbConn = "provider=SQLOLEDB; Data source=xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
and when i use oledbconnection string (vb.net)
oledbConn = "provider=SQLOLEDB; Data source=http://xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.
operating system-XP sp2
Thanks
Rupesh Kumar Swami
-- modified at 7:29 Tuesday 1st May, 2007
|
|
|
|
|
Can you ping the ip address?
Ben
|
|
|
|
|
hi ben,
i can ping the IP address
i don't understand what is the problem ?
thanks
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
Do you have multiple sql servers running on that server?
Ben
|
|
|
|
|
hi ben
there r only single instance.
thanks
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
Can you successfully create an odbc connection to it?
Go to Control Panel -> Administrative tools -> Data sources (ODBC)
Click the add button. Then scroll down to the sql server and click finish.
Next type in the server name, click next and select sql authentication. Put in your username and password. click next. If you get an error, you have a problem with your user name and password perhaps. If you get to the next screen select your database. If you don't see your database in the drop down your user doesn't have rights to that database. If you get to the next screen you should be able to test the connection. If the test works then you should be able to use the same server, database, user and password to make your connection work in your program.
Hope that helps.
Ben
|
|
|
|
|
hi ben ,
according to your specification i follow steps as following
pressing finish button after selecting Sqlsever from datasource list ,there r new window appear. I type ip address of remote server(in textbox related to server) and click next. after that i select SQL Server authentication and put username & password and clicking on next button give error.
here i m sure that user name & password is correct.
any idea where i m wrong.Thanks
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
If you can't create an odbc connection, something is wrong. Try using the sql server name. there must be some issue with the name / ip of the sql server and the username and password. Once you can get it working with an odbc connection you can use the same values to get it working in your app.
Ben
|
|
|
|
|
hi ben ,
i also try with ip address & server name (not only with server name) but problem not solved.
if u have any other idea ,please inform me
Thanks for your support.
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
I guess you could try using the query sql tool to connect to the sql server with that user name and password, but I am pretty sure you will get the same results. For some reason your sql server name or IP or username/password is not correct. Until you figure that out you will not be able to connect.
Ben
|
|
|
|
|
Please will someone recommend some good, detailed resources on SQL Server 2005 security. I am going to select and purchase a book on the subject next week, on which I would also appreciate recommendations, but I need to achieve some tasks in the mean time, so I would like some on-line resources I can read now.
I would like to learn more about asynchronous keys and associated logins, how to grant unsafe assembly permission to a database owner[1], more about 'alias' users such as 'dbo', the relationship between the machine administrator group and SQL server, owner ship or rights over schemas vs actual objects etc. and even basic secutiry such as logins vs. users.
|
|
|
|
|
I don't know books about SQL Security, however i know some sites that talk about SQL Injection, which can be very very destructive.
Links, you will need to search into the blogs the related topics to SQL Injection:
FR3DC3RV BLOG
RONALD BLOG
Good luck
|
|
|
|
|
|
Hello everyone:
i write a win32 c++ program,then i add ATL OLE DB consumer in my project.now i need the function that when the SQL database's data change,my main program's window can refresh view.so i refer to msnd:
==========================================================================
Visual C++
Receiving Notifications
OLE DB provides interfaces for receiving notifications when events occur. These are described in OLE DB Object Notifications in the OLE DB Programmer's Reference. Setup of these events uses the standard COM connection-point mechanism. For example, an ATL object that wants to retrieve events through IRowsetNotify implements the IRowsetNotify interface by adding IRowsetNotify to the class-derived list and exposing it through a COM_INTERFACE_ENTRY macro.
IRowsetNotify has three methods, which can be called at various times. If you want to respond to only one of these methods, you can use the IRowsetNotifyImpl class, which returns E_NOTIMPL for the methods you are not interested in.
When you create the rowset, you must tell the provider that you want the returned rowset object to support IConnectionPointContainer, which is needed to set up the notification.
The following code shows how to open the rowset from an ATL object and use the AtlAdvise function to set up the notification sink. AtlAdvise returns a cookie that is used when you call AtlUnadvise.
---------------------------------------------------
CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IConnectionPointContainer, true);
product.Open(session, _T("Products"), &propset);
AtlAdvise(product.m_spRowset, GetUnknown(), IID_IRowsetNotify, &m_dwCookie);
============================================================================
the code is so little that i don't know how to implement in my code,did anyone can help me?
thanks a lot!
|
|
|
|
|
Hi All,
How can i make my database more and more secure so that unautorised user cannot have access to my database by Enterprises Manager
If they are unauthorised then Enterprise Manager won't let them in.
Thanxxxxxxxxxxxxxxx
|
|
|
|
|
If they have the admin password, and access to your network, then there's not anything you can hope to do. Your DB is as secure as your network, no more, no less.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I've answered this question several times now: previous answer[^]
Why won't you accept the answer you were given?
|
|
|
|
|
Wow - we seem to have a flood of people who ask the same question over and over.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
