Hot answers tagged c#
10
Have you tried just writing and see what happens? Do you have a known bottleneck?
If you need to prevent your app being blocked then you one way would be to queue the writes to defer the database call. However, I'd expect the queue to clear in a second or 2: so do you need an queue if this is OK?
Or you can spool to a staging table and then flush later? We ...
9
I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it ...
7
You can only add references to those assemblies which have been registered with Sql Server. If they are not registered, they will no show up in the Add References dialog.
There are a number of steps you'll need to do register a DLL, firstly you'll need to reconfigure your database:
ALTER DATABASE [MyDatabase] SET TRUSTWORTHY ON;
sp_configure 'clr enabled', ...
7
An nvarchar(max) value will be stored "in-row" if it is short enough.
The default behaviour can be modified using sp_tableoption, "large value types out of row" option. I wouldn't bother. The DB engine will manage this efficiently by itself.
As for design, there are several ways of doing this based on your model:
Will you always have both English and ...
7
At our company we have that exact setup. When you create a CLR assembly a binary representation of the assembly is stored within the database that you create it in. This enables you to take it with you (and even script it out) should you move the database at any point in time.
A couple of months back our data center got flooded - filling several servers ...
6
Unless I'm missing something, this would violate the Durability requirement from ACID (http://en.wikipedia.org/wiki/ACID). That is, if your application "writes" the data to RAM and then the server crashes, your data is lost.
So, what you seek is either a non-database system that serves as a queue for eventual storage into a database or a database system ...
6
The assembly binary is stored as a blob in the database, so it's carried wherever the database goes. CLR is only enabled on the instance -- there are no database-specific settings for that.
In any event, why are you trying to do this?
(I'm not trying to be argumentative; I just want to hear the motives involved, because perhaps the problem could be solved ...
4
Assuming a company can be "attended to" by more than one employee, and likewise for client, you would model like this:
So both relations are 'many:many'. As gbn also said, adding "comma separated ids" would be something of a disaster in database design: you would regret it many times over.
4
If you want to restore a *.bak file onto a machine e.g. TESTSERVER, then that *.bak file needs to be
on a local drive of TESTSERVER, or:
on a share in the network that TESTSERVER can reach (a "public" share)
So if you have the *.bak file on your local PC MyPC, you can try to do a restore from \\MyPC\C$\ShareFolder\Backupfile.bak and this might or might ...
4
I am assuming you are asking about alternatives to installing SQL CLR assemblies from Visual Studio.
Having the code in Visual Studio is not required.
Deploying CLR Database Objects on MSDN details the options, including SQL statements and deployment scripts.
3
A varbinary value is of the form 0x01020304 for SQL Server to read.
So your CSV would be
col1,col2,varbinary
foo,1,0x01020304
bar,2,0x988776655443
Edit May 2013,
After testing, I can't get this to work with BULK INSERT with or without a format file.
I will post something more when I get it working. I can't delete this answer
3
If there's a where clause in the DELETE statement, then the server still needs to perform a SELECT internally to the DELETE statement in order to delete only the specified records.
You may be only issuing one DELETE statement, but that's a convenience of SQL. The Database software has to process that statement and perform the necessary logic to do the job ...
3
You have a many-many (aka link or junction) table between the 2
EmployeeCompany
Columns:
EmployeeID
CompanyID
Keys:
Primary key is (EmployeeID, CompanyID)
Add a unique index (CompanyID, EmployeeID)
EmployeeID has FK to Employeetable
CompanyID has FK to Company table
Do not store a CSV in a column: this is bad practice, can't enforce data integrity, ...
3
Your procedure is missing two OUT parameters:
PROCEDURE myprocedure (myinput IN Varchar2,
myretcursor OUT sys_refcursor,
p_count OUT NUMBER,
p_message OUT VARCHAR2) IS
BEGIN
OPEN myretcursor FOR
SELECT * FROM MYTABLE;
p_count := 123;
p_message := 'message';
...
3
Strongly recommend you look into Table-Valued Parameters and treat your insert/update as a set (using MERGE probably) instead of calling a singleton stored procedure 25,000 times. You can skip a lot of steps here (in C# you can just stuff your 25K rows into a DataTable, for example, making it easy to pass to a stored procedure in a single call), and reduce ...
2
A couple basic SQL Server checks: Is your database set to trustworthy? Is the assembly set to allow UNSAFE execution?
If that seems to be working, there are a couple of tutorials out on the web that might help. If none of the tutorials out there help, then it sounds like a permissions issue. (Unfortunately, that gets tricky, as this thread discussing ...
2
I'm not 100% sure about LINQ but i would say that the seperation is based on transaction control. You apply any number of updates, inserts or deletes. That are then held as a unit of work, then once you've finished your changes you simply commit them to the database as a single action or roll all of the changes back, maintaining the integrity of your ...
2
To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:
Error: 18456, Severity: 14, State: 8.
The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:
Err Message
2 Invalid userid
5 Invalid ...
2
How valuable is the lost data, how many schema changes have there been in the last three months and what exactly was the nature of the corruption? Some of it may be recoverable if you are prepared to spend sufficient time on the project.
You could download the source code for OrcaMDF as a starting point.
2
It sounds like you have a copy of the MDF file, rather than an actual file generated by BACKUP DATABASE. If so, you have lost 3 months data.
You can try attaching this without log file using
CREATE DATABASE databasenamehere ON (filespechere) FOR ATTACH_REBUILD_LOG
The current MDF is most likely can't be salvaged. Reading the file is impractical
After ...
2
I'm 90% sure it's somewhere without our ASP.NET webforms app where a
connection is being opened and not closed
I hope in your webforms, you are following the below mentioned approach to dispose the IDisposable objects....
using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) {
con.Open();
SqlCommand ...
2
You can query sys.dm_exec_sessions on the last_request_end_time column to find open yet "sleeping" connections.
From this, you can find out the last SQL executed
SELECT session_id, TEXT
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST
WHERE c.session_id = <suspicious one from sys.dm_exec_sessions>
2
MS SQL Server has a fixed page size of 8KB.
A row is never splitted over several pages, but several rows can share a single page.
nvarchar(max) and other BLOB data may however be stored outside of the row/page.
This means that for everything to fit into one row, the sum of all sizes must be less than 8K. If it doesn't, SQL Server will store the BLOBs ...
2
Here are some of the things you can try to get it to work:
Compare Enlist=false in your connection strings
Compare MultipleActiveResultSets=true in your connection strings
User instance databases appear to work differently for some reason - we couldn't get it to work consistently with them - use full databases all of the time
You can reuse the same ...
2
No, there's no way to do this.
Any option that did what you want would be extremely confusing because there would need to be a way to turn it off within the string literal syntax. There are other considerations (like storage size) to be aware of when choosing data types, so a setting like this could be very dangerous to put in play.
With regards to .NET ...
2
You cannot use conversation groups to exclude application instances, if that's what you're trying to do. If Instance A needs to receive messages from the queue only for Instance A and Instance B needs to receive messages only for Instance B then instance A needs to use queue A and instance B needs to use queue B. Conversation group can be used only when ...
2
I would suggest that instead of deleting the rows rather create a new table which will have the data you would like to keep in the table. Create appropriate constraints and index.
Then drop the original table and then rename the new table. This would be best if you are deleting lots of data.
Deleting batches is good when you delete only very few data as ...
2
If you are using csv , you can not do this. But if you are willing to use other tools like Microsoft SQL Server Reporting Services or Crystal Reports you may do this.
SSRS Chart Example, here another one with code example.
If you can not install Reporting Services Server or do not want to, you can still use it for basic reporting purposes. Take a look at ...
2
If you want to clone prod. db into dev. env. regulary I would recommend you to use RMAN for whole database. Or exp/imp (expdp/impdp) for single schema.
RMAN is not easy to use for developers who have no Oracle experience, but finally you will find this approach fastest and safest.
If you're about to clone your prod db into some reporting server maybe you ...
Only top voted, non community-wiki answers of a minimum length are eligible
