SELECT
part.num, woitem.qtytarget AS woitemqty,
(SELECT LIST(wo.num, ',')
FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID
[Code] ...
I have few complex queries and I want to extract the output of results to all different dateformatted output files.
How to write the queries?
I know BCP is a solution but any other effective way to implement it?
I have a question on date manipulation functions and CASE statements
My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-
monthColHeader = eg 'Feb 2015'
defaultStartDate and defaultEndDate
filterStartDate and filterEndDate.
These are my conditions;-
if defaultStart and End = filterStart and End use monthColHeader for the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.
When I say use monthColHeader I mean like this;-
(r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))
This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'....
I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances.
View 8 Replies View RelatedI am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).
How to find particular queries/stored procedures that causing this?
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,
[Code] ....
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
We have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.
The original server is an ancient Win2003 86x
The Server team will build a new Win2008r2 64x, and use the same name and IP address
And I'm tasked with the SQL part.
I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.
How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?
Merge two rows into one (conditions or Pivot?)
I have Temptable showing as
Columns:EmpName, Code, Balance
Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10
I want to insert the above temp table to another table with column names defined below like this
Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10
Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.
Can the collation used by SSIS be changed or influenced during install or run time? We have found that our databases, that use a mandatory "LATIN1_GENERAL_BIN", have incorrect SSIS Merge Join output. Changing our database collation in testing didn't make a difference. What matters is the data. Which Windows collation is SSIS using?
Example Data:
FIRSTNAME
FIRSTNAME
FIRSTS-A-NAME
FIRSTS_A_NAME
FIRST_NAME
FIRST_NAME
FIRSTname
FIRSTname
FIRS_NAME
put in a Sort task before the Merge Join task as setting advanced properties isn't enough (as described by Eric Johnson here --> [URL] ......
We are using 64-bit SQL Server 2008 R2 w/ SP1 in Windows Server 2008 R2 ENT w/ SP1.
UPDATE from ETL team: Explicitly ordering the source with "COLLATE Latin1_General_CS_AS" seems to have the same effect as using a separate sort task. We don't feel that we can rely on our findings, however, unless we have documentation that this collation is what is behind SSIS.
I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
This is my starting point:
boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209
After I switch partition 1 to a staging table I run:
ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;
and
ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');
I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:
PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210
How do I make sure that the data stays in AdtLog_Archive_201209 file group?
I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.
I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?
My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?
For study effect, can I configure merge replication using just one SQL Server Instance?
View 3 Replies View RelatedI am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed
CREATE TABLE [dbo].[CMS_LOCKS7](
[PARENTID] [int] NOT NU, --we have a non clustered index on this column
[CHILDID] [int] NOT NULL, --we have a non clustered index on this column
[ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column
[ORDINAL] [int] NULL,-- we have a non clustered index on this column
[Code] ....
I have a table:
declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target
using ...
and I can't have:
merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
With merge/insert statements ...Is DISTINCT best way to handle problem of source table containing duplicate rows, along with WHERE NOT IN statement? the source dataset is large and having to do DISTINCT and further filtering is taxing on the ETL.
DDL
source table
CREATE TABLE [dbo].[source](
[Product_ID] [INT] NOT NULL,
[ProductCode] [VARCHAR](20) NULL,
[ProductName] [VARCHAR](100) NULL,
[ProductColor] [VARCHAR](20) NULL,
[code]....
I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.
Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.
There the following article options:
Copy file group associations
Copy table partitioning schemes
Copy index partitioning schemes
I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.
I could not find and option to copy partition functions.
Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?
I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.
Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.
Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM
SQL Code:
Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;
[code]....
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
There is a column for Name with data type varchar 50 the input data is like Alok Kumar, Sunita kuamri, Rohit Gupta Like that. The column contains the data as Combination of First name and second name.I would like to write a select query for selecting first part of name like Alok, Sunit, Rohit and so on only, ignoring the second part of name.
View 3 Replies View RelatedThe passwords in table 'users', column 'passwordencrypted' are encrypted. Someone enters a password and I'd like to compare if it is correct. The syntax below seems ok but nothing is returned. Why not?
OPEN SYMMETRIC KEY mykey DECRYPTION BY CERTIFICATE mycert;
DECLARE @mypw varchar(300);
SET @mypw = 'test';
SELECT * FROM users WHERE passwordencrypted = EncryptByKey(Key_GUID('mykey'), @mypw);
CLOSE SYMMETRIC KEY mykey;
Can I safely use top n select/delete in a while loop? For example:
declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
Getting this error after successfully setting up merge replication scenario:
The server agent could not connect to the publisher (29049)
Well, successful all the way up to the point of subscribing that is.
The publication is configured with IIS 5 under XP. Already had a 3.1 sample publication running. The new web share is running sqlcesa35.dll and successfully loads diag page. The publication snapshot is successfully created. The snapshot agent and IUSR accounts have permission in publication and distribution dbs and are both in the PAL. I created the publication under SQL 2008 Mgt Studio. The web config did not work from within Mgt Studio, but did work when I ran wizard from Start Menu. Subscription steps all seem normal and then when attempt to sync at end of wizard get this error.
Will try again tomorrow with fresh environment, only using SQL 2005, SQLCE 3.5 and the 3.5 server tools. This is on a secondary test machine with lots of new Visual Studio stuff etc.
Reference here on MSDN says contact product support if reproducible.
http://technet.microsoft.com/en-us/library/ms172357.aspx
A simple Query to select alternate rows from a table ?
View 9 Replies View RelatedI have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.
example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />
I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.
Not really sure where to start. Table is GAME, and column is XMLDATA.
I have this sql....
Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,
[code]....
however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'
So the stand alone sql could be....
Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc
I'm just not sure how to incorporate that into my sql or whether there is a better way,
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
I need to investigate about what happened to our production server at the last weekend.i restored it to another server which is development. I restored it under a name "old_master_2015_07_10".
But if I run a query
SELECT *
FROM [old_master_2015_07_10].[sys].[servers]
it actually selects from master.sys.servers, not from my old_master... In order to prove it, I created a linked server in this, development server, and if I run SELECT * FROM [old_master_2015_07_10].[sys].[servers], it selects it. And in database selection drop-down box I also selected old_ master_ 2015_07_10. What I think it apparently recognizes familiar names like sys.servers and redirects the query to the master.
What I can do to select really from old_master_2015_07_10 database? I already thought about renaming sys.servers to something different, but did not do it not to break something in master in case if SQL Server will run it in master as well.
We have a database where the nvarchar columns currently holding English only data. It keeps the training information. Currently the default collation is Latin Case insensitive accent insensitive.
Now we are planning to allow multiple language support. When we go for it, we will upload the data from different languages. Based on user preferences, he/she should be able to query the data (One language at a time).
I am worried on how the existing queries will work if I load all language data in the same database.
For example, some characters in English are used in Norwegian too. But they have different sort order. ALso LIKE conditions too may fail.
So far I thought of few solutions: Add the Collation information along with select , order by clauses. It means we need to add more procedures (one set per collation) The other option is to create new database for each language. Each will have its own collation. The dowside is we need more databases which may lead to more servers and more maintenance work.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
FROM myTable
[code]....
I have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.
I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.
There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan