Data Reside In Memory And Accessed Via CLR Database Objects.
Nov 7, 2007
Hi,
I want to manage memory relational data in the memory(Insert, delete, return) and manipulate the data via SQL 2005 and CLR database objects.
The gool is to manage a table that relatively has a lot of inserts and deletes which takes a lot of IO operations and i want to reduce it. the data itself is not permanant each row deleted from the table after 15-20 minutes.
I looked at the extended stored procedure and other CLR enable objects and it seams that they all return data from the database itself.
How can i return data that is reside in the memory?
Thanks.
While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?
Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.
I need to create a SQL server database and add some tables to it. Then access it with a C# application. The problem is that the new SQL server database and it's tables must reside on an external hard drive. How do I point SQL server to this external drive, so that I can create a database on this drive and then create tables and access data on it?
In SQL Server 2000 can we bind objects, like tables, etc. to memory/cache to enhance the performance? This feature is available in Sybase and I am very new to SQL Server.
What is the best approach when transferring all database objects & data from a SQL Server 6.5 database to SQL Server 7.0 database running on different servers.
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX) DECLARE @Handle AS INT DECLARE @TransactionCount AS INT SELECT @Config = ' <ConfigurationDirectory > <ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
I have a bunch of datbases I have created during the development process. I want to delete those no longer being used. How do I find when the last time a database was accessed?
I was having issue with one of my databases in SQL EXPRESS. It is offline this morning …it said “Database 'MyDB' cannot be opened due to inaccessible files or insufficient memory or disk space. When I checked the error log ..it only said “FCB::Open failed: Could not open file D:DatabasesMyDB_Data.mdf for file number 1. OS error: 32(failed to retrieve text for this error. Reason: 15100).
I did alter database offline and and online ..it works for me( I can access the database again but I need to find the cause of this issue )I checked the max memory setting is still by default .. not limited for SQL ..it could be the cause ?
Is anyone aware of a method of determining when a table was last modified or accessed?
Some of our databases have tables that I am sure are not being used and I would like to generate a list of tables that have not been accessed or modified for some period of time.
I looked for a system procedure but didn't see anything that satisfied my need.
Currently I rename suspect tables and wait for someone or some process to gripe, but I don't care for that method for obvious reasons.
I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.
Also, are there any things that I should take care with my database from outside users.
I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents.
Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.
Also, are there any things that I should take care with my database from outside users.
Hi,I am hosting my database on a third-party Microsoft SQL server. The guest user is "Off". But, I am not sure if any one else is accessing/viewing/editing my database contents. Can I know/log/record the users/logins who are viewing/accessing my database. Is there any script or any exisitng machnism which can be used to track these users.Also, are there any things that I should take care with my database from outside users.Thanks-Sudhakar
Our application team make design changes of the database in their development server and asks me to restore the bak file from the development server into the production server with replace command. The database in the production server is connected to the application server and so when I try to restore the database error message comes that the database is in use.
So first I find out the sessions with the logins who is the user in the database with sys.dm_exec_sessions joined with sys.dm_exec_requests and then kill the sessions. And after that it is possible to restore. But many times when I kill the session and try to restore in between new sessions comes into effect which are generated from the web server and many times I have to resubmit the query in the dmv's and find the sessions and kill it again and again before restore can be done. Is there any correct method to restore an active database which is being accessed by the application from a web server with out stopping the web server?
I have a plan of decommissioning of unused databases which are not been in use for quite some time. is there any simple way/t-sql to find out When was the database last accessed by each user/login in sqlserver.
The goal is to address visual source safe database on the network. We have the srcsafe.ini in the network as \ipaddrsrcsafe.ini. Now I create a new VSSDatabase object and call its OpenDb. Well for simple consle app or winform it is ok. But I was running it under Sql server Stored Procedure. It failed for I cannot access the source safe path throgh the COM object.
I know it is because of Windows identity. So I add the following code before I want to open the database, changing the to the WindowsIdentity: WindowsIdentity impersonId = SqlContext.WindowsIdentity; WindowsImpersonationContext orgCtx = null; try { orgCtx = impersonId.Impersonate(); VSS_Database = new MVSI.VSSDatabase(); // VSS_Database.ImpersonateCaller = true; VSS_Database.Open(Path, UserName, PassWord); } catch (Exception err) {
Without the commented line "// VSS_Database.ImpersonateCaller = true", this does not work at all. It just behave like no changes to the windows identity. However if I add this code, well, OpenDb will result in a No-response query. The Sql server is running the query with no responses. Have you ever met that before? I am really frustrated. Thanks
Word of warning! I'm new to the forum and relatively new to SQL Server. Please excuse my lack of knowledge for proper terminology.
I have SQL Server 2000 installed locally on my laptop and have been using it several months. Everything works fine. I also need to have SQL Server 2005 installed. I tried to install 2005 to a new instance name and got errors on Visual Studio Integrated Development Environment 2005, SQL Server Books Online, and SQLXML4 modules. I have the log files if someone needs to see them. Something common to all 3 module errors is: HRESULT: 0x80070020
Question 1: Can 2000 and 2005 reside on the same machine without conflicts? or do I need to uninstall 2000? I would prefer to have both installed if that's possible. Question 2: How do I fix the 2005 install? Just reinstall it? or do I have to uninstall 2005 and then reinstall?
Hi All, Where Connection pool reside, is in client side(in .net framwork) memory or at server(in sql server) momory or at both side it maintains pool information? Best RegardsImran Zubair
Can I change the folder that the databases reside in for my installation of SQL Server? I would like to have them in a folder on another drive, but I can't seem to find where the path is configured.Any help is appreciated.
I know now that AlwaysOn feature HAS to be installed/configured on a Windows Clustering environment, BUT the secondary replicas, like the Disaster recovery replica residing in a different Data Center HAS to be also in a Windows Clustering environment or can it reside on a SINGLE SQL Server INSTANCE?.
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
I would be grateful if you could mail/point me to a TSQL script that can show me the Data Definitions of my SQLserver database objects. In fact, I have found a Visual Basic script that may be of help but it demands the installation of the Visual Basic software, which I do not possess. Thanks,
I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:
1. If I run this command
EXEC sp_MScheck_uid_owns_anything 5
I get this weird error message:
"The user owns objects in the database and cannot be dropped."
Not sure why that is since I'm just trying to list the objects, not drop them.
2. I tried running a simple query to get the objects from the system table instead:
SELECT * from [dbo].[sysobjects] where uid = 5
This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:
The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.
Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!
I have an ActiveX script which I need to convert to a T-SQL Stored Procedure. As a part of it, I need to loop through all the tables in a Database and check whether the particular Table/View exists? If doesn't exist, I need to create one with Primary Key, Foreign Keys, Indexes (Clustered & Non-Clustered), and Check Constraints.
Here is the ActiveX Script I have:
Code: Dim sNextMonthTable Dim oServerName Dim oTables Dim CheckDate sNextMonthTable = "Jan_2012"
There are 2 databases A and B.How can I create a procedure in database A so that the procedure works with contains tables in Database B. IS IT POSSIBLE
