SQL Server 2012 :: Encoding With Single Apostrophes In FOR XML PATH

Oct 16, 2014

Here is my problem:-

declare @test as varchar(32)
declare @test2 as varchar(32)

set @test='today''s problem'
set @test2='my <string> '

select @test as '@attribute' for xml path ('myrow')
select @test2 as '@attribute' for xml path ('myrow')

I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.

View 4 Replies


ADVERTISEMENT

SQL 2012 :: How To Fetch Single File From Local Path Using SSIS Package

Nov 14, 2014

I have 15 Dealers files .With the Files name as follows

1. ''ACTEST00001_20141112_0408_INV.TXT''

2. ''ACTEST00002_20141112_0408_INV.TXT''

I will get these files through FTP on Daily basis with changes on date alone.

I will have 4 files for each dealers.like INV,SERVICE,SALES,APPOINTMENT.

SO i need to fetch a particular dealer with particular date.

I need to create this scenario in SSIS package. How to create this and what are all the Tasks i need to use to implement this process.

View 1 Replies View Related

SQL Server 2012 :: Nested FOR XML Path

Oct 16, 2014

I'm trying to reverse engineer an XML output based on a client's need of a very specific format. Besides the issue of getting the XML declaration written in and removing NULLs, I'm having an issue with a three times nested PATH query.

So far the document almost has the correct format, except for the first nested root being returned. Is there any way to prevent this?

*some data redacted

Query:

DECLARE @a XML
SET @a =
(SELECT FileCreationDate,
(
SELECT PCE_TPD.PJN,
(
SELECT top (2) TaskCode, TaskName, RSI, TAFFD, AFD, Notes, PID, CUSID, NeedToBeNA

[Code] ....

Current Output (first few lines):

<TDBUIData xmlns="http://www.xxxxx">
<TDBUIData12 xmlns=""> --NEED TO ELIMINATE THIS
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>

[Code] ...

Desired Output (first few lines):

<TDBUIData xmlns="http://www.xxxxx">
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
<TaskData>

[Code] ...

View 4 Replies View Related

Reporting Services :: Can Change Encoding From UTF8 To SJIS In SSRS 2012?

Jul 14, 2015

How to change encoding in SSRS from UTF8 to SJIS for export to CSV. Is it possible or not.

View 3 Replies View Related

SQL Server 2012 :: Compare A Table With Physical Path

Mar 31, 2015

I have a table with two columns

id | filepath
--------------------------------------------------
1| D:Doc filesThe BestHHT.JPG
2| D:Doc filesThe Bestsealed_pack.txt
3| D:Doc filesThe Bestlsbom.JPG
4| D:Doc filesThe Bestmoc.png
5| D:Doc filesThe Beststock.txt
6| D:Doc filesThe Bestdepot.JPG

And in a physical system there are more files than the table.

D:Doc filesThe BestHHT.JPG
D:Doc filesThe Bestsealed_pack.txt
D:Doc filesThe BestJKSlsbom.JPG
D:Doc filesThe Bestmoc.png
D:Doc filesThe Beststock.txt
D:Doc filesThe BestGDNdepot.JPG

D:Doc filesThe BestCASA.JPG
D:Doc filesThe BestSO.txt
D:Doc filesThe BestBA.JPG

I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...

View 3 Replies View Related

SQL Server 2012 :: Bulk Insert Using UNC Path Of File Table Directory

Jul 22, 2013

Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.

Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.

Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard

View 9 Replies View Related

SQL Server 2012 :: Using Recursive Query To Find Path Between Assembly And Parts?

Jul 2, 2014

I'm trying to use a recursive query to find path between assembly and parts.

The BOM is similar to(I've limited the number of rows and lines):

BOM NumberMat Number
20000222001770
20000222003496
20000222001527
20000222003495
20002462002005
20005062000246

[code]....

How should I modify it so that is returns the 4 path?

View 1 Replies View Related

SQL Server 2012 :: View To Concatenate File / Path Names Back To Root Directory?

Sep 25, 2014

We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root

Below is a sample:

Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4

iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.

I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.

Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)

The end result of my view on the above data should return:

Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)

View 5 Replies View Related

Single Command To Change The Backup Device Path

Feb 29, 2008



Hi guys.

acutally my network dept. has change the backup file server IP address.. i m now hving problem for taking backup.
i have around 85 backup that runs everyday.. what i m doing now running every single command to drop backup device and then adding again.. but it's taking agess to do..

is there any simple script that just update the device path folder..

Thanks and looking foward.
-MALIK

View 6 Replies View Related

SQL Server 2k Querying Varchar Without Apostrophes

Jul 20, 2005

I am currently migrating our Intranet from SQL Server 7 to SQL Server2000 and have hit a problem with one of the applications. Theapplication in question executes the folloiwing query against thedatabase:select * from employee where emp_id = 760In SQL Server 7 this works without a problem, however, in SQL server2k the following error is returned:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error convertingthe varchar value 'Con25' to a column of data type int.This is clearly because the column emp_id is a varchar, I understandthat this code has not exactly followed best practices but do not wantto have to trawl through the application and surround all instanceswith apostrophes.Is it possible to make SQL 2k act in the same way as SQL 7 or will Ihave to trawl through the code and correct the SQL statements.Thanks for any help.John

View 2 Replies View Related

SQL Server Not Starting - Tempdb Path Updated To Wrong Path

Oct 4, 2007

After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service

thanks

Leena

View 13 Replies View Related

SQL Server 2012 :: All Permutations Of A Single String

Sep 20, 2015

How to retrieve all possible sets of values from a table, with each set having a unique identifier.

Here's sample data, where any number of rows can be in the "animal" set:

select animal.name
from (
select 'Cat' as name
union all select 'Dog'
union all select 'Bird'
) animalHere's what I'm trying to get:

[Code] ....

It'd be an easy task if I knew how many rows were in the set, but without knowing how many (and being able to do x number of cross joins and CUBE/ROLLUP to produce the list of permutations) or writing a horrid complex of while loops, I'm at a loss.

A single column. All permutations of the values in a single column. Perhaps I should stay off here nearer the end of the day .

View 2 Replies View Related

SQL Server 2012 :: Comma Separated In A Single Result

May 30, 2014

I have the following table

CREATE Table #Table1
(
ID INT, Name VARCHAR(50), Class VARCHAR(10)
)
INSERT INTO #Table1
Select 1, 'name1', 'a' UNION ALL
Select 2, 'name1', 'a' UNION ALL

[Code] ....

Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?

CREATE Table #Table2
(
ID INT, CommaSeparated VARCHAR(100)
)
INSERT INTO #Table2
Select 1, 'name1, a' UNION ALL

[Code] ...

What I have

Select * FROM #Table1

Final Result
Select * FROM #Table2

Note that I still want to see all the IDs regardless.

If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.

CREATE Table #Table3
(
CommaSeparated VARCHAR(100)
)
INSERT INTO #Table3
Select 'name1, a' UNION ALL
Select 'name2, b, c, d' UNION ALL
Select 'name3, e, f'
Select * FROM #Table3

View 3 Replies View Related

SQL Server 2012 :: Query For Single Entry Record Only?

May 26, 2015

I have records like below, single query to get result below, basically records that has single entry only, which has type '0'

table : temp_test
idtype
c10
c25
c30
c40
c47
c59
c64
c60
c77
c80
c90

Result out of query

idtype
c10
c30
c80
c90

View 4 Replies View Related

SQL 2012 :: TDE Encrypted Databases On 2 Different Server To Be Restored On Single Dev Server

Apr 29, 2015

have a Prod Server A having TDE enabled on 2 of those databases. I have a Prod Server B having TDE enabled on 3 of those databases. Now I have to create a single Dev server Server C for all the above 5 databases residing on the two servers. So how can I restore all the 5 database backup files on server C.

Does it mean that I need to copy the certificates and Keys from both the Prod server to this Dev Box and then restore the backup files. Once done, I can enable the encryption ON on those 5 database on Dev box or is there any different approach.Also how will tempdb behave in this scenario.

View 5 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Return A Single Table

Nov 21, 2013

I have this SP

ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
AS
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
DECLARE @i bigint
SET @i=0

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Multiple Records Queried Into Single Record?

Jan 27, 2014

I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.

People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801

[code]....

Results

PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom

View 8 Replies View Related

SQL Server 2012 :: Merge Multiple Rows From Single Table

Nov 17, 2014

I have resulting rows from a query similar to the following:

The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,

[Code] ....

I basically want to merge the like Employee ID's together into a single row like the following:

I know I have done this before and it is probably pretty simple.

View 4 Replies View Related

SQL Server 2012 :: How To Add Column To Multiple Table Using Single Script

Feb 12, 2015

I am looking a script which allow me add single coilumn to multiple table of my database.

For Example :-

I am having 4 table

1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table

Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.

Alter table EMP
Add Rowchecksum varbinary(8000)

View 1 Replies View Related

SQL Server 2012 :: Splitting A Single Column Into Multiple Columns?

Mar 3, 2015

I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.

For Eg:

Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L

View 9 Replies View Related

SQL Server 2012 :: Stored Procedure To Update And Insert In Single SP

Jul 17, 2015

I have Table Staffsubjects

with columns and Values

Guid AcademyId StaffId ClassId SegmentId SubjectId Status

1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1

I need to do 3 scenarios in this table.

1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId

I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate

Here is the stored Procedure what i have wrote

ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]

@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier

[Code] ....

View 8 Replies View Related

SQL Server 2012 :: Get Multiples Rows And Columns Data Into 1 Single Row?

Oct 28, 2015

I have the following results:

CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110

And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.

View 9 Replies View Related

SQL Server 2012 :: Replacing Recurring Characters In A String With Single Character

Jan 20, 2014

I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.

For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:

DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');

and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.

View 9 Replies View Related

SQL Server 2012 :: Insert Values In A Single Table With Four Columns From 4 Different Sources?

Jan 30, 2014

I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other

View 3 Replies View Related

SQL Server 2012 :: Assigning Multiple Rows To A Single Variable Parameter

Nov 27, 2014

The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?

CREATE TABLE #AWP (
[TransDate] [datetime] NULL,
[Description] [varchar](1000) NULL,
[Amount] [float] NULL,
[TotalDueNow] [float] NULL,

[code]....

View 4 Replies View Related

SQL Server 2012 :: How To Calculate Attendance For Single Student For Yearly Basis

Jul 2, 2015

I have to calculate the Total number of days present and absent for a singel student.

AS of now i have 3 tables.

1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]

So now i have loaded all the data into the table.

I can fetch the counts for total present and absent

Query i have tried is

Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
Begin
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,

[Code] ....

AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.

SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.

View 7 Replies View Related

SQL Server 2012 :: Patindex Function - Cannot Retrieve Or Extract Single Numeric Value

Jul 17, 2015

I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.

"2 8 7"
"2 8"
"2"
"22"
"3 2 8"

I have tried the following

Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,

Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,

Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),

View 5 Replies View Related

SQL Server 2012 :: How To Replace Multiple Values In Single Select Statement

Aug 18, 2015

how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.

DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))

[code]....

View 7 Replies View Related

SQL Server 2012 :: Displaying Code Of A Stored Procedure To A Single Line

Sep 14, 2015

Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.

I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.

Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')

[Code] .....

View 3 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

SQL Server 2012 :: Statement To Group Rows As Multiple Child Under Single Parent?

Sep 18, 2014

I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like

where london
where paris
where toronto

why us
why japan
why indonesia

I want to eliminate the repetitive question and group them as parent child items.

with cte as (
select cq.ConditionalQuestionID from ConditionalQuestions cq
inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y')
select distinct q.Question, a.Answer from QuestionAnswers qa
inner join Answers a on a.AnswerID = qa.AnswerID
inner join Questions q on q.QuestionID = qa.QuestionID
inner join cte c on c.ConditionalQuestionID = qa.QuestionID;

View 4 Replies View Related

SQL Server 2012 :: SELECT Query - Cursor To Display Result In Single Transaction

May 25, 2015

Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.

CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Creating Running Total Based On Single (SUM) Opening Balance

Jun 7, 2015

I am creating a query that shows the consumption of stock against Manf Orders (M/O) and struggling on the last hurdle. I am having difficulties calculating a running total based on an Opening Balance. The first line returns the correct results but the following lines do not. I have tried other variants of the "Over Partition" but still no joy?

SELECT CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.Component
END AS Component ,
CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.SKU

[Code] ....

Please see attachment to view output.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved