t-sql's questions - Chinese 1answer

2.101 t-sql questions.

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...

What is the best way to 'flatten' tables into a single row? For example, with the following table: +-----+-------+-------------+------------------+ | Id | hProp | iDayOfMonth | dblTargetPercent | +-...

How can I execute a large (>40k char) block of T-SQL on each database in a MSSQL instance, considering the following have character limits which would truncate the command passed in a variable, and I ...

I'm looking to store GPS co-ordinates in an SQL Server 2005 for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL ...

First, I want to say how wonderful a tool this has been over the years to implement a great backup strategy. I did run into a unique situation this last month. During an upgrade to a third party ...

Does the order of operation that you place your LTRIM and RTRIM matter when used in conjunction with ISNULL? For instance, take the following example where a user potentially enters a bunch of spaces ...

Question UPDATED: Yes I know the tables are not normalized and yes I know there are no primary keys. Semester table Semester, Year, City 1|2017|Washington 2|2017|Paris 3|2017|London 1|2018|Paris 2|...

I need to UNION data from tables with the same name in multiple databases. All databases have the same prefix: CDNXL_(NAME OF DB) How would I execute this query against more than one database at at ...

I've got this query which used to take minutes to run and now takes like 6 seconds but it runs a thousands of time a day so I'd like to make it faster. https://www.brentozar.com/pastetheplan/?id=...

I have an SQL table of varchar columns which contain Greek formatted numbers (. as thousand separator and comma as decimal separator) The classic conversion CONVERT(numeric(10,2),REPLACE([value],',',...

List Jobs, Schedules, and Next Scheduled Run Datetimes I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes. However, I don't appear to be succeeding. sysjobactivity has ...

I am attempting to query a maximum of 65,000 records in a database (minimum of 0). The server is using MS SQL Server 2014, and I have no way to change the hardware on the server. Schema [Id] (PK) ...

I have to maintain FRP (Financial Resource Planning) Software that was implemented by some shady consultants (they came up with the jobs), and it stores the data in a SQL Server database. The software ...

How can I get the string between the html tags <p> and </p> using regular expressions? '<div class="ExternalClass849E0BFE74914F8BB79B2C64E3D07AE6"><p>​Just help me!</p>&...

I've been tasked to try to recover a database which suffered from corruption (due to I/O failure, which has been fixed since). I'm not familiar with the database or what it contains. I've been given ...

I am attempting to insert data into a linked server using openquery. I have attempted the below syntax, but get an error of Cannot get the column information from OLE DB provider "MSDASQL" for ...

I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is ...

I have a Dependency table that has a ParentID, ParentEntityType, ChildID, and ChildEntityType along with additional fields. The EntityTypes refer to a variety of object types (Nodes, Groups, ...

I know that the CONCAT function does not exist in SQL Server prior to 2012. How would i optimize my code to not use the CONCAT function? SELECT dbname = DB_NAME(), ...

I suspect my Google skills are just insufficient today, but I'm looking for a quick TSQL script that will identify all SSIS packages in the Package Store that are using a given Environment Variable ...

I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no ...

In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, ...

Below is a simplified version of my current tables. Each test type is a type of test (eg: one table is for different appearance tests, ie substance is yellow and sticky, or substance is green and dry, ...

I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column '...

A client has a thorny problem. Their client encrypted one column of a data file using openssl. (Their client is a Linux based shop and they encrypted the column with openssl using AES 256 CBC.) My ...

This is a followup thread to GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful? In my solution to this challenge, I use a query that groups by an expression ...

So i have a Ad group on a database which i want to deny from viewing a table but i want give access to one person from AD Account. I'm not sure if it is possible. I highly think its not. but maybe i ...

Hi i'm trying to insert some data in my table but i got this error ...

In got a programming task in the area of T-SQL. Task: People want to get inside an elevator every person has a certain weight. The order of the people waiting in line is determined by the column ...

I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical. I am trying to query Active Directory services, on 2 of them this is working ...

I have a big *.sql file that I run with Invoke-Sqlcmd and variables to do some tasks, and when I have an issue, it's a pain to debug because of the variables and I want to do logging of the SQL script ...

Running this results in "test", as expected: SELECT REPLACE(NCHAR(1234), NCHAR(1234), N'test'); However, running this results in "aӒa", which doesn't have "test" in it: SELECT REPLACE(N'a' +...

I have a few characters that are being replaced by a ?. How do you keep the original character in the table? The character that I am trying to insert is a ɬ; Latin Small Letter L With Belt. Is it ...

I am wanting to send an email through the SQL Server Agent once an insert has been made to a specific table. I tried the syntax below, but the email is not being sent, nor queued to send. ALTER ...

I have following table: create table osoba( id int primary key, idNd int, nazwim nvarchar(500), ) Data looks like below: id idNd nazwim 1 NULL grandpa 2 1 child1 3 1 child2 4 2 ...

On larger DDL scripts for deployments, usage of SET NOEXEC ON as part of error handling is commonplace to prevent execution of downstream code. If coupled with SET NOCOUNT ON, there's a chance code ...

I've been searching everywhere but I have not been able to find anything about it, seems like everyone is concerned only with WHERE and CASE. (and I haven't a SQL Server at hand reach to verify it by ...

I need to get metadata information about tables/views and its columns in SQL Server. When I run the query separatelly for tables/views (sys.tables) or columns (sys.columns) I can get the extended ...

I have a table with a unique key that includes an NVARCHAR(50) column (correct or not, but is there). So, when trying to insert Șc or C (doesn't matter the order of the insert) it breaks on the 2nd ...

I am working with a data-ware house and i want to partition the last three years of data into Hot data storage and any further than three years should be placed to cold. I want to be able to run a ...

When you drag the Columns folder to editor Window, the full list of Columns are added without Brackets. If you drag columns individually they have Brackets. Is there anyway to turn that off? I'm ...

The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.

SQL Tricky query

1 answers, 101 views sql-server sql t-sql
Creating Account_holder_details table : CREATE TABLE [dbo].[AMASTER]( [ACID] [int] NOT NULL, [NAME] [varchar](40) NOT NULL, [ADDRESS] [varchar](50) NOT NULL, [BRID] [char](3) NOT NULL, [PID] [char](2)...

I'm getting bad results from a JOIN; it's joining rows where the relevant data doesn't appear to match. To illustrate the problem, assume I have the following: Table FRUITS: Fruit_ID | Fruit_Name ...

I'm trying to perform a restore of a database from a different server onto my localhost server .That database is encrypted (the bak file ) ,so I have to decrypt it ,that's fine I have all the cert ...

In this code I am converting the subjects(columns) English , Hindi , Sanskrith , into rows DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @colsUnpivot =...

Here is a conundrum I need to solve with some T-SQL Magic, I tried and failed. It's one of these things that, before I think of the actual solution I think I can solve in 3 minutes with some T-SQL, ...

I am trying to ignore duplicate rows from a CTE but I am not able to do that, it seems like a CTE does not allow to use ROWNUM() variable to WHERE clause as it is showing “Invalid column name 'numrows'...

I have 3 tables: StuddentMaster CourseMster Enroll_Master The data is about students enrolling for courses at an institute. I need a query which shows the names of courses where every enrolled ...

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...

Related tags

Hot questions

Language

Popular Tags