Tag Info

New answers tagged

0

Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well: This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source ...


1

It's not entirely clear what the wanted output is but based on the comments that you want a "lightweight db export" and that the solution needs to be dynamic and "to be able to apply it to different dbs and different schemata", I'll try to make an outline of a solution: Find all FKs in the db in question. Copy all tables to a new db - but without the FKs ...


0

Create a script component as a data source. You can probably re-use a lot of your code that you used to create the script task. Then you can set your batch size.


1

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.


2

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks. DECLARE @qry NVARCHAR(MAX) = 'SELECT ' + STUFF(( SELECT ', ' + QUOTENAME(c.name) FROM Report.InfoColumnOrder r JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info') WHERE u.UserId = @user ORDER BY r.SN FOR XML PATH(''), ...


0

When you are sending 2000 requests to the same table, SQL Server already has the pertinent pages in memory i.e. SQL Server caches data pages in memory so they don't have to be read in from disk next time. When you are writing to multiple tables, SQL has to read data pages in each of those tables to find the correct spot to insert new rows. Unless you have ...


3

It appears that this behavior is specific to connections using "Context Connection = true;". I have tried to get around this by writing out the try-catch-finally structure instead of using the using macro, but that had no effect. A Microsoft Connect bug was filed almost 3 months ago regarding this behavior. In that Connect bug it was speculated that THROW ...


0

Yes you can. However, it depends on the internals of the System.Data.SqlClient assembly and so I would not recommend it. If it is truly required I would reassess if CLR is actually the correct way to do what you are trying to do. The SqlTransaction is actually only a wrapper class around the SqlInternalTransaction class. The SqlInternalTransaction class ...


1

XACT_STATE() is not something that SqlClient has any special insight on, much like transaction level. So, just query it with SqlCommand. // If you already have an open connection... public int GetXactState (SqlConnection connection) { using (SqlCommand command = new SqlCommand("Select XACT_STATE();", connection)) { return ...


1

It appears the OP is conflating how the data should be stored in the database with how the data will be displayed and/or entered when taking classroom attendance. These are not necessarily the same thing. Display should not drive storage. The UI could display the data the way the OP shows above in a classroom attendance form and have a middle layer that ...



Top 50 recent answers are included