Hi, I have an excel sheet with 10 columns with headers. For a column I can have the data as "FF5" or else 620. The sheet name is IODE. I am trying to import this data from SSIS Import data wizard into the database of table IODE. On selecting source and destination in the wizard, when I click on PREVIEW DATA in Select Source Tables and...
Hello all, I've the following requirement. I'm passing 'ABC DEF' as a command parameter @MyString to a stored procedure. In the stored procedure level i need to replace the substring DEF with XYZ to get the string 'ABC XYZ'. How can i do that? Thank you. NLV ...
Hi All, I'm using Visual Studio 2008 and SQL Server 2008. I would like .sql files in my VS projects to open in SQL Management Studio by default. No problem so far: I right-click the file, Open With, Add SSMS, and set it as the default. What I don't like is that it opens a new instance of SSMS every time, and I would like it to open in an...
This might be a very simple question, but am just curious about it. Are these queries both representations of equi join? I find that both returns the same result. Query1: SELECT C.* FROM [Case] A, [Event] B, Notice C WHERE A.CaseID = B.CaseID AND B.EventID = C.EventID Query2: SELECT C.* FROM [Case] A join [Event] B on A.CaseID = B...
I am trying to drop user from role in several databases. It is not sure that user can be in same role in all the databases. How can i check if the user is in the role and if it is drop the user from the role. e.g. IF user exists in role BEGIN drop user from role END ...
We use an assembly with some user defined functions in our installation of SQL Server 2005 (32 bit). We deploy this to production with a script like this: CREATE ASSEMBLY [Ourfunctions] AUTHORIZATION [dbo] FROM 0x4D5A9000...000 WITH PERMISSION_SET = SAFE GO CREATE FUNCTION [dbo].[GLOBAL_FormatString](@input [nvarchar](4000)) RETURNS [nv...
I have a java program that in some circumstances must update a large amount of records in a database (e.g 100,000). The way it does it is by creating a PreparedStatement and by using the addBatch technique. Here is the snippet: connection.setAutoCommit(false); PreparedStatement ps = connection.prepareStatement( "UPDATE myTable...
On our company's SQL server, there are a bunch of databases that don't appear to be used. Is there a way to determine the last time someone used a particular db, connected to it, or ran a query against it? ...
Folks, is there a tool to format/indent SQL Server queries in query window ? TIA ...
Is there a tool that can let one browse relational data as a graph of connected nodes? For example, i'm faced with trying to cleanse some anomolous data. i can start with two offending rows. In this particular example, the TransactionID should, by business rules, be unique to the table, but i find a transaction that violates that rule: ...
What are examples of SQL Server Query features/clauses that should be avoided? Recently I got to know that the NOT IN clause degrades performance badly. Do you have more examples? ...
Our production database is 5-7 years old, and many people have had their hands in it making schema modifications during this time. I just discovered there are Instead Of triggers on some of our views. I didn't even know this was possible. For those of you who have used Instead of triggers on views, what use case were you trying to solve?...
In a SQL 2008 DB there are 2 tables of data that I need to use to build a result in a stored procedure. Table 1 looks something like this. date name hour amount price ------------------------------------------ 2009-10-12 tom 12 20 15.43 2009-10-13 fred 16 -10 6.98 Table 2 looks like this. Note ...
I have a stored procedure which returns a mixture of plain columns, and one 'column' of xml datatype. As in: Select Field1, Field2, ( SELECT Certification As '@certification', LCID As '@lcid' FROM dbo.MemberCertifications FOR XML PATH('certification'), TYPE, ROOT('certifications') ) ...
Can you folks suggest some Questions regarding performance issues in SQL? ...
If i have a view that generates multiple columns: CREATE VIEW dbo.foo AS SELECT id, SUM(SELECT [...] ) AS c1, STDEV(SELECT [...] ) AS c2, AVG(SELECT [...] ) AS c3, MIN(SELECT [...] ) AS c4, MAX(SELECT [...] ) AS c5, SUM(SELECT [...] ) AS c6, [...] COUNT(SELECT [...] ) AS cN, FROM Table GROUP BY id ...
Objective: we want to write a DLL using VB.NET 2008 using .Net 3.5 that performs an HTTP request we then want to call the DLL From SQL Server 2005 (in a trigger) and pass the DLL some variables and get a return value. Where im at in the process. I have created the DLL 'HTTPDLL.dll' Source: Imports System Imports System.Configurati...
We are creating several SSIS packages to migrate a large database as part of a release cycle. We may end up with about 5-10 SSIS packages. As we have 4 environments (dev, QA, staging, production, etc.), is there an efficient way to change the destination server for each SSIS package as they go through the different server environments...
When I am testing a query I will typically drop the following lines in front of whatever i am testing to make sure I'm starting from the same baseline every time i run a query. CHECKPOINT GO DBCC FREEPROCCACHE GO DBCC DROPCLEANBUFFERS GO EXEC sp_MyProc 12345 On the Stored proc i was running today i noticed that when I ran it with thes...
I'd like to move 26 tables from one DB to another. I see I can do this in the SSIS Import and Export Wizard. I believe the other approach would be to select tools from the toolbar in Data Flow and then configure them all. When is it better to use the wizard and when is it best to create the package manually (with the visual tools) or ...