Serge
  • Member for 7 years, 1 month
  • Last seen more than 1 year ago
Oracle CTE to SQL Server Using SSIS OLE DB Source
1 votes

I had the same problem, which I managed to resolve by updating the Oracle driver. My environment: SSDT 11.1.20627.00 running on Visual Studio 2012 Update 4 Oracle OLEDB driver 11.2.0.1 - this ...

View answer
SQL Server 2014 All Administrator Accounts Disabled
1 votes

You need to start SQL Server in single-user mode. The documentation Connect to SQL Server When System Administrators Are Locked Out says: Start the instance of SQL Server in single-user mode by ...

View answer
Interbase SQL Grouping and displaying
Accepted answer
2 votes

You are getting two rows per document because you are grouping by documents.status. You need to use SUM() slightly differently and then remove documents.status from the GROUP BY clause: Select ...

View answer
Best way to delete large set of rows knowing field to NOT delete
0 votes

In relation to the first table, appointments, make sure that you have an index on company_id column. In relation to the mappings table, using EXISTS rather than IN may yield better performance. You ...

View answer
Using inner join and left join together
0 votes

It's not clear what you mean by 35 tables. You can achieve the result you need from the 3 tables described as following: SELECT l.lid ,l.name ,l.meter ,l.address ,p....

View answer
UNION ALL or other ways to return first row of result-set
1 votes

Thank you to Paul White, Lennart and Kunal Chitkara for their answers. Also, thanks to ypercubeᵀᴹ for being the first to point out that ORDER BY is required even when a SELECT TOP 1 * query consists ...

View answer
MySQL SELECT n-th row between dates (time interval)
1 votes

You should create a summary table that will store this summary data. 5 minute summaries will require additional 288 rows per day (24 * 60 / 5); 30 minute summaries - 48 rows per day (24 * 60 / 30); ...

View answer
Revoking execute access on builtin functions on SQL Server
Accepted answer
2 votes

You cannot restrict permissions to LEFT built-in function. You can control permissions that are listed by the output of this command: SELECT * FROM fn_builtin_permissions(default)

View answer
How to store user activity that supports undo?
0 votes

Create a TRIGGER on the table in which deletes are recorded and update related tables as required. Relevant postgresql documentation Info about triggers: http://www.postgresql.org/docs/9.5/static/...

View answer
Mapping Fields based on unknown relationship
Accepted answer
1 votes

You can use FOR XML PATH to help construct a dynamic query: -- Generate a list of field names to be used in the SELECT statement DECLARE @Sql VARCHAR(MAX) = ( SELECT '[' + FieldNumber + '] AS [' +...

View answer
How to get response from stored procedure before it finishes?
0 votes

If your stored procedure needs to run in the background (ie asynchronously), then you should use Service Broker. It's a bit of a pain to set up, but once done, you will be able to kick off the stored ...

View answer
Is there a way to validate XML using variable as schema?
Accepted answer
3 votes

I don't think this is possible using T-SQL, however you can achieve what you need using C# and .NET CLR assembly. Steps Create a new C# class library project in Visual Studio. Write a public static ...

View answer
How to change default port for T-SQL Debugger
Accepted answer
1 votes

I don't think it's possible to change the TCP port used by T-SQL Debugger. You can use an application such as RelayTCP and port forwarding rules on your router to achieve what you want. It's difficult ...

View answer
Get 2 Most Recent Records By Product
1 votes

Another solution using LAG function: Query: WITH passes AS( SELECT ProductId ,CreateDate ,CASE WHEN Status = 'pass' AND LAG(Status) OVER(PARTITION BY ...

View answer
Transforming multiple rows into a single row
1 votes

Try something like this: SELECT ID ,[date] ,SUM(case when hour = 1 THEN money END) AS Hour1 ,SUM(case when hour = 2 THEN money END) AS Hour2 ,SUM(case when hour = 3 THEN money ...

View answer
MySQL - Order by closest value on multiple columns
0 votes

Try something like this: SELECT l.id ,l.user_id # current user ID ,others.id # other user's ID ,others.connection ,others.level FROM lobby l INNER JOIN users AS myuser ON l....

View answer
MySQL: sum time ranges exluding overlapping ones
Accepted answer
3 votes

Original You want to sum up all time ranges, except those that fit inside other time ranges on the same day: SELECT DATE_FORMAT(DAY, '%Y-%m-%d') ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC( ...

View answer
How to write function in sql which accept table as input and return result back as table?
Accepted answer
3 votes

You cannot include a table declaration in the stored procedure declaration. If you really want to pass rows of data, create a table-valued parameter type, then use it in your stored procedure. How ...

View answer
how to Create A SP with table variable and execute 3 SP statements in the same statement
-2 votes

Try something like this: CREATE TABLE #Churn( Current_Churn INT NOT NULL, Goal_Churn INT NOT NULL, Caption NVARCHAR(100) NOT NULL ) INSERT INTO #Churn EXEC Churn_MTD_Current_Year INSERT INTO #...

View answer
Why is MySQL choosing that index?
0 votes

17 minutes is not a reasonable time to wait. Try to provide an index hint: SELECT * FROM contact USE INDEX (IDX_LIST_ID_SUBSCRIBED_DELETED_EMAIL_ADDRESS) WHERE list_id = '014c7cba-c124-11e5-b4ea-...

View answer
Fail to execute SQL command asynchronously
2 votes

You need create a separate pCmdChange object for each INSERT: for (UINT uIndex = 0; uIndex < 3000000, uIndex ++) { // Create command object. pCmdChange.CreateInstance(__uuidof(Command)); ...

View answer
Import quoted CSV
Accepted answer
1 votes

OPENROWSET command and Microsoft Access Text Driver (*.txt, *.csv) driver come to the rescue: SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Access Text Driver (*.txt, *.csv)}; DefaultDir=D:\...

View answer