Dominique Boucher
  • Member for 5 years, 3 months
  • Last seen this week
  • Saint-Hubert, Longueuil, QC, Canada
3 answers
-2 votes
3k views
sql query for select first word of data from column
1 votes

You can use "Substrig" and "charindex" functions to get what you want. See this code as a demo on how to use it: declare @name varchar(30) set @name = 'shyam banarjee' select ...

View answer
1 answers
-2 votes
51 views
Same query performance problems when using ORDER BY
Accepted answer
1 votes

When you join your CTE with another table, SQL may (depending on the execution plan) execute the code within your CTE multiple time. Ex : Running this against the stackoverflow database: set ...

View answer
1 answers
0 votes
405 views
Receiving "Query processor could not produce a query plan because of the hints defined in this query" when using FORCESEEK but can use FORCESCAN?
Accepted answer
1 votes

SQL Cannot "Seek" as the order of this index is by date first and then by PrimaryKey. As you are selecting with a date that match the full content of the index and as the PrimaryKey are not ...

View answer
2 answers
0 votes
74 views
SQL Server Basic Availability Group: config-only replica?
Accepted answer
1 votes

In linux, there is no "Windows Cluster" (which is used behind the scene for AGs). In order to have automatic failover, you need to use something like Pacemaker which is like the windows ...

View answer
2 answers
0 votes
2k views
Materialized view in SQL Server
1 votes

For that kind of need, I like to build a SSIS package that will copy the content of the view to a different SQL DB (on a different instance if I want to make sure their reporting won't hurt the prod ...

View answer
2 answers
0 votes
98 views
Lock only a row in insert command sql server
1 votes

The best way, if you need to be able to read data while there is some operation that modifies the data is to use Optimistic isolation level. Turn on "read commited snapshot" and you will be ...

View answer
1 answers
0 votes
889 views
MS SQL Server: Import Flat File with auto-increment ID
Accepted answer
1 votes

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY. Then use an update statement to update the new column from the ...

View answer
4 answers
0 votes
1k views
Why does SQL Server say it can't convert varchar to numeric?
1 votes

This is because you are using a column that contains text values (pid) to join on a numeric column INNER JOIN sales ON products.idn = sales.pid When you do a join of those 2 tables, SQL needs to ...

View answer
1 answers
0 votes
65 views
Multi-Subnet Always On with Availability Group for DR
Accepted answer
1 votes

Usually, if you want to use a node for "failover" (high availability), you want to have the AG set for synchronous replication (to enable the auto-failover). That being said, if you set it to "sync" ...

View answer
1 answers
0 votes
75 views
Insert cursor result into a table
Accepted answer
1 votes

You will need the table to exist and have all the columns returned by your statement. Then you can run something like: INSERT INTO MyTable Exec (@SQL);

View answer
1 answers
0 votes
88 views
List top N rentals for each month
Accepted answer
1 votes

Use pretty much the same script in a CTE but add the row number (partition by month). This will return all rows with a row number in front, reseting at 1 for every month. Then you can just issue your ...

View answer
1 answers
0 votes
238 views
Get availability group and listener of the current DB (dynamic SQL)
1 votes

You need to run queries agains the DMV to get the AG name. Here's some code you could use as a base: select DB_NAME(AG_DB.database_id) "DB", AG.name "AG Name", li.dns_name "Listener" from sys....

View answer
1 answers
1 votes
25 views
Tsql query help
Accepted answer
1 votes

Is this what you are looking for ? SELECT database_name, AVG(DATEdiff(SECOND,backup_start_date,backup_finish_date)) "AVG duration (Sec)", SUM(DATEdiff(SECOND,backup_start_date,backup_finish_date)) "...

View answer
1 answers
0 votes
77 views
backup history using availability groups
Accepted answer
1 votes

We made a SSIS package that collect msdb history from each instance and centralize it on a "DBA" database. We then "purge" the local msdb from each instance. That way, we can query the centralize DB ...

View answer
2 answers
0 votes
1k views
How to debug a growing Redo Queue Size in the Availability Group Dashboard?
1 votes

Please look at this webpage: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver15 There is ...

View answer
2 answers
0 votes
122 views
High availability sql server, secondary replicas can't be connected on the Availability group
1 votes

The AG are using "Database Mirroring" Endpoint to communicate between all nodes. Your SQL Service account need to have "connect" permission on the endpoint. You also need to grant "ConnectSQL" to the ...

View answer
2 answers
0 votes
6k views
Select column name dynamically based on rows from another table in SQL server
1 votes

It's probably not the optimal way to do it and I would probably look at normalizing the database but here's a way to get the data you want. with cte as ( select 'field1' "name", convert(nvarchar(...

View answer
2 answers
0 votes
53 views
SQL Server Availability Group configuration steps
1 votes

Microsoft documentation is pretty clear about this: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/getting-started-with-always-on-availability-groups-sql-server?view=...

View answer
2 answers
0 votes
185 views
pre login initialization =29237; handshake=26244
1 votes

By default, SQL uses port 1433 and 1434 (if you are using named instance or the DAC connection). You can validate which port is used using SQL Server Configuration Manager

View answer
1 answers
0 votes
303 views
Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?
1 votes

Looking at the execution plan, you can see that every query is done as a different query. Again, if you want to see if those query are using parallelism, you will see it in the execution plan. If ...

View answer
2 answers
0 votes
50 views
What is the best way to know the structure of a database
1 votes

You can use "database Diagram" and add all tables to get a physical representation of the tables with PK and FK and some link between table but this is certainly not as detailed as you would like it ...

View answer
3 answers
3 votes
2k views
Multiple Batches in SQL Server Migration Script
Accepted answer
1 votes

I guess it would be easier to simply restrict the access to the database then take a backup. If there is a problem, simply restore the backup. That way, you won't have to worry about that kind of ...

View answer
2 answers
0 votes
3k views
Database Shrink task fails
1 votes

Why are you doing that shrink operation ? If you run it everyday, that mean that your database growth everyday and consume the "freed" space. In that case, you are creating fragmentation in the ...

View answer
1 answers
0 votes
25 views
How do you detect a change in grouping when using Partition By in SQL Server 2012+ and execute logic based upon the change in the same SQL statement?
Accepted answer
0 votes

Here's an example of an SQL Query that would achieve what you are expecting: create table #school (school_name varchar(20), school_area varchar(20)); insert into #school values ('someschool1', '...

View answer
2 answers
0 votes
74 views
How to 'pause' SQL Server AlwaysOn Availability Group
0 votes

The simpliest way seems to simply shut down both nodes. That way, no one will be able to connect to them. If ever you need to roll back to them, just turn one of them (or both) on and they will then ...

View answer
1 answers
-4 votes
47 views
Updating column value across all SQL server versions
Accepted answer
0 votes

This "update" statement will update all rows with the provided values. If this is what you need, then I would simply ask why you have this column on every single rows of this table... You ...

View answer
1 answers
1 votes
30 views
Always On with multi AGs primary on each replica - Any gotcha's?
0 votes

You'll need to automate some kind of "load balancing" otherwise, if there is an issue with one one the node during the night, all AGs will run on the same server. Unless you want to fail ...

View answer
1 answers
-3 votes
32 views
SQL Query to Return Rows that don't exist as blank
0 votes

You could use a table that would contains numbers from 1 to 100 and then use a left join in your query to get all numbers (from this table) join to the data in your other tables. ex: create table #...

View answer
1 answers
0 votes
44 views
Disabled SA user is listed as logged in
0 votes

This is because some "system" session are showing "sa" as the username. I think you can add a where is_user_process = 1 in your query to exclude those system session and you ...

View answer
3 answers
0 votes
52 views
select one row based on a string
0 votes

You can also use a reference table and use it to do a left join like so: create table demo (computer_id int, soft varchar(20)); insert into demo values (1,'Adobe'),(1,'Office'),(2,'Adobe'),(2,'7Zip'),(...

View answer