Tag Info

Hot answers tagged

10

There are several options. Here are the main ones that come to mind. SSIS - SQL Server Integration Services: This is SQL Servers ETL tool and you can move data from pretty much any source to pretty much any destination and it can be quite fast. BCP - Bulk Copy Program: This is a command line tool that comes with SQL Server. It's very good at moving text ...


6

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I ...


6

You cannot redeclare variables, nor can you test to see if they have been declared (at least not that I have ever been able to find). BUT, you shouldn't need to do this anyway. If you know the variable names ahead of time, just declare them all at the beginning of the process. Then, use them throughout the script. For example: ...


4

When I try to drop a person that recently had a connection with SQL Server Why are you doing this? What drove the need to have to do this? It sounds like you're running into connection pooling. But connection pooling is there to help you, it's a great optimization. By clearing the pool you're wiping out this caching mechanism. My recommendation, ...


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 ...


3

A quick way to resolve your issue would be simply to return a Null column in each of the select statements. SELECT url, NULL AS Category, NULL AS SubCategory,'Web' As Type FROM Website WHERE (url='www.facebook.com') UNION ALL SELECT NULL, category, NULL AS SubCategory, 'Cat' As Type FROM Category WHERE (category='www.facebook.com') UNION ALL ...


3

Your query actually needs to use two properties of the CONTAINSTABLE function- <maximum distance> and <match_order>. Using CONTAINSTABLE(OpinionDocuments, HTML, 'NEAR((Foo, bar), 0, TRUE)') AS KEY_TBL Specifies to find "Foo" near "Bar" with 0 search objects between (<maximum distance> ", 0,") and in the order specified ...


3

Is the default setting for Sql Server comparable to "innodb_flush_log_at_trx_commit = 1" or to "innodb_flush_log_at_trx_commit = 2"? No. Assuming similar hardware, SQL Server should be just as slow as MySQL, since both would have to flush the commit for every row. Therefore there must be something else at play, very likely in the (not posted) C# code ...


2

Yes, you can do this, and in fact with just 2 data centers you really have no other choice. Essentially, whenever you do not have a primary you can still do a reconfig but you must pass in {force:true} to make it happen on a non-primary node. This is listed in the docs for the rs.reconfig() command and in detail (with examples) as part of a tutorial which ...


2

I did a little bit more research and found the answer to the problem. Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text'). The actual parameter names for stored procedures can ...


2

Just in case this helps, I'm posting some code that might help you work out what account that sid represents: USE tempdb; GO -- to translate SID in binary format to AD format CREATE FUNCTION [dbo].[fn_SIDToString] ( @BinSID AS VARBINARY(100) ) RETURNS VARCHAR(100) AS BEGIN IF LEN(@BinSID) % 4 <> 0 RETURN(NULL) DECLARE @StringSID VARCHAR(100) ...


2

We've used Change Tracking to handle this. We've found it lightweight and easy to set up and administer. The largest system's moving tens of thousands of rows (from a table containing millions) per day without signs of stress. Of course your configuration will dictate your throughput. It's big cousin - Change Data Capute - will handle the case where you ...


2

If you need to execute sql file use sqlcmd documented in the MSDN: Run Transact-SQL Script Files Using sqlcmd This did the trick for me: sqlcmd -S serverName\instanceName -i script.sql -o output.txt


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(''), ...


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 ...


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.


1

i know it's been a while since the question but i found a possible solution. This link provides an explanation and examples of how to use the connections available in the connection manager enabled for the Script Component in the SSIS https://msdn.microsoft.com/en-us/library/ms136060.aspx The portion of code that we are interested is this: ...


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 ...


1

How can I grant execute permissions to sa user? You don't need to grant execute anything to sa as sa is in the sysadmin server role and can pretty much do anything. Hence, if you are getting an error executing xp_logevent, then you aren't logging in as sa. Is the connection string using context connection = true; or a regular / external connection? If ...


1

Why not just execute xp_logevent via SqlContext.Pipe.ExecuteAndSend() in your catch block?


1

What you are asking is to be able to create a report name dynamically, which is not something that can be programmed, on download, say, from Report Manager. You can (although I've never tested this) possibly do this through a subscription using @ParameterName @ReportName. citation from msdn It's about the best you can do for now, and there's a link on the ...


1

You can use an array constructor: select array( select zone from mytable) as areas;


1

Create a set of staging tables in the target database. Write rows to these as they are generated, which seems to be one or two at a time. This can be inside a transaction. Once the whole batch (200 rows?) is in these staging tables use a stored procedure in the target database to move them en masse from the staging tables to the real ones.


1

I've seen similar problems addressed by decoupling the app from the central server: Remote sites install a local SQL Server Express along with the app Apps talk to the local Express instance. Low latency, good availability. SQL Server Express uses Service Broker to deliver the updates to the central server Service Broker handles the network availability, ...


1

You have used parametrized query, which is the bare minimum expectation to prevent on SQL-Injection. I would suggest some points you can take care of as a validation in C# code I don't see any server side validation on the text you are getting from TextBoxSearch.Text, you can check for some set of character that can be an attempt for SQL Injection. you ...


1

Instead of using a script component, I recommend using a more database-centric approach. Consider the following approach: Read the data from the flat file using a flat file source. Keep your flat file source simple. It should have one column which contains the EntireRow. (optional) add a script component or derived column to count rows and generate a ...


1

When i did a systems analysis module in my years at university, i was doing my computer science degree. One thing our lecture told us all at the time was something we couldn't comprehend. "Strictly speaking, there is no wrong or right way to approach DBs" As computer scientists this is hard to swallow. lets look at your example: Ok, so you have ...


1

Note, that I assume C# (since this language is present in tags) and Entity Framework. Both approaches assumes, that entity types are inherited from abstract, non-persistent base entity type, which accumulates 75% of fields, so the code base discussion is not relevant. Pros of Table-Per-Hierarchy (all descendants are stored in single table): database ...


1

The more SSISic way of doing this would be through Expressions. Despite seeing multiple variables here, don't worry about them. I have found that when people try to do too much with an Expression, diagnosing and debugging bad logic will leave you reaching for the nearest blunt object. Variables FlatFileConnection, string. Value = ...



Only top voted, non community-wiki answers of a minimum length are eligible