The tables are what the tables are. Here is what I am trying to accomplish:
ID CODES DESCRIPTIONS -- ----------------- -------------------------------------- 01 Code1,etc Description1,Description2,Description3
I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
0 AS SalaryMin, 2088 AS SalaryMax, 2088 AS BillableHours, 'Month' AS SalaryPaidCode, 0 AS SalaryBreakdownHourly, 0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output 0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below
Database versions :ms sql 2008/2012 tablename:system_id's column:system id information
sample data from system_id_information column
######################################## <obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>
[Code] .....
I am looking output of two columns, which are bolded
product_id snumber 654081-B21 MXQ43905SW
for serial number this is common
before string :HostSystemIdentificationInfo"><identifierValue>
and after string </identifierValue><identifierType><label>Service tag
and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies
I was just doing a table import task (right click database name/Tasks/Import Data), not knowing my boss had just loaded the same file. it did not warn me that the table currently existed. It just appended the same information to the same table, doubling it. I fixed that one, but, it seems that I might have done this myself in the last couple of weeks, and I'd like to find that table, and there have been a LOT of table loads.
I'm thinking I could get the difference between tables by comparing:
select distinct count(*) from tblname against select count(*) from tblname
But how do I incorporate this into some sort of proc that will go through all the tables and let me know where the issue is? I'm swamped and don't have the time to go through each table manually.
I have code that shows me row counts, and have been able to eliminate a few tables from contention, as they are loading monthly data that should only increase minorly month to month, so, no double jumps there.
One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.
Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].
I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above
[##actor##] [##dramatist##] [##playwright##] -- 2 occurrances, but I need it only once in my result set [##poems##] [##poet##] -- 2 occurrances, but I need it only once in my result set [##sonnets##] [##verses##] [##writer##]
I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.
Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.
DECLARE @MyTable TABLE (MyString VARCHAR (8000)) INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
I can't figure out what is causing this error: can you please have a look at my code and let em know what is wrong or rework it for me:
="SELECT ARIBC.cntbtch, ARIBC.btchdesc, ARIBC.AUDTUSER, ARIBC.AUDTDATE, ARIBC.AUDTTI, GLPSOFTMAP.PSPRODUCT, GLPSOFTMAP.PSDEPT, GLPSOFTMAP.PSACCOUNT, GLPSOFTMAP.ACCTID, ARIBH.CODECURN, ARIBH.INVCDESC, CONVERT(varchar,ARIBH.FISCYR + ARIBH.FISCPER) as PERIOD, CONVERT(varchar,ARIBH.IDCUST) AS ACCOUNT, ARCUS.NAMECUST as ARNAME, CASE TEXTTRX WHEN 1 THEN 'INV' WHEN 2 THEN 'DM' WHEN 3 THEN 'CM' END AS CLASS, CONVERT(varchar,ARIBH.IDINVC) AS TRX_NUMBER, (CASE TEXTTRX WHEN 3 THEN ROUND((ARIBD.AMTEXTN*-1),2) ELSE ROUND((ARIBD.AMTEXTN),2) END) AS ""AMOUNT FROM ARIBC"" INNER JOIN ARIBH ON ARIBC.CNTBTCH = ARIBH.CNTBTCH INNER JOIN ARCUS ON ARIBH.IDCUST = ARCUS.IDCUST INNER JOIN ARIBD ON ARIBH.CNTBTCH = ARIBD.CNTBTCH AND ARIBH.CNTITEM = ARIBD.CNTITEM INNER JOIN GLAMF ON ARIBD.IDACCTREV = GLAMF.ACCTFMTTD INNER JOIN GLPSOFTMAP ON ARIBD.IDACCTREV = GLPSOFTMAP.ACCTID WHERE ARIBC.BTCHSTTS=3 AND ERRENTRY <= 0 AND BTCHDESC NOT LIKE '%54-8003%' AND ARIBH.IDINVC in ('" &Replace(Parameters!invoicenum.Value,",","','")"
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
Hi.. Please help me resolve this error "Cast from string 'OPEN' to type 'Double' is not valid.". Error here If CallStatus = 10 Then ....Code:Public Sub UpdateCallStatus() Dim CALLID, RequestorID, CommentsFromITD, MessageFromITD, MessageToITD, CallStatus, strSQL As String CALLID = Request.QueryString("CallID") RequestorID = Session("USER_ID") CommentsFromITD = lblcomments.Text MessageFromITD = lblmessage.Text MessageToITD = txt_desc.Text CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID)) Dim ObjCmd As SqlCommand Dim ObjDR As SqlDataReader Try If CallStatus = 10 Then strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() gbVariables.insertuserevents(CALLID, RequestorID, "Call Closed") Response.Redirect("UserCallClosed.aspx") ObjConn.Close() Else strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() ObjConn.Close() strSQL = "SELECT STATUS_LABEL FROM STATUS WHERE STATUS_ID = " & CallStatus & "" ObjCmd = New SqlCommand(strSQL, ObjConn) ObjConn.Open() ObjDR = ObjCmd.ExecuteScalar() ObjConn.Close() gbVariables.insertuserevents(CALLID, RequestorID, CallStatus) CallStatus = "" End If Catch ex As Exception lblmsg.Text = ex.Message.ToString End Try End SubThanks...
we have a value in a column with double space between characters like" abcd  efgh", when I do the preview of the report I see the value as it is with double space; but when I deploy the report on to the reporting server, I just see only single space between the characters. whys is it eliminating one space when it is deployed on the reporting server? how can I get the same value with 2 spaces?
I have a table in SQL Server with following spec Table1(Grossamount(money))
I have a SSIS variable called grosstot of type double and use following sql in Execute SQL task in SSIS
Select Sum(Grossamount) from Table1
I then assign the result of above sql stmt to the SSIS variable grosstot within the same Execute SQL task.
it gives me the error : [Execute SQL Task] Error: An error occurred while assigning a value to variable "grosstot ": "The type of the value being assigned to variable "User::grosstot " differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".
I tried the following sql to no avail
Select CONVERT(numeric (12,2), Sum(Grossamount) from Table1
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT -- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) (SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.
So for example: A source table with two developers
------------------------------------------------------------------- | dev1 | comp1 | Microsoft Visual Studio Ultimate 2013 | dev1 | comp1 | Microsoft Visual Studio Professional 2010 | dev1 | comp2 | Microsoft Visual Studio Premium 2010 | dev2 | comp3 | Microsoft Visual Studio Professional 2010 | dev2 | comp4 | Microsoft Visual Studio Premium 2012 --------------------------------------------------------------------
I want the result to be: ----------------------------------------------------- | dev1 | Microsoft Visual Studio Ultimate 2013 | dev2 | Microsoft Visual Studio Premium 2012 ------------------------------------------------------
I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.
My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.
This is how far I have come now:
IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255)) INSERT INTO #swRank(rankID, vsVersion) VALUES (1, 'Microsoft Visual Studio Ultimate 2013'), (2, 'Microsoft Visual Studio Ultimate 2012'),
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.
Query example:
Select * from myTable Where item like '%FSC%'
This works great with the exception of when FSC is surrounded by double quotes.
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
I have a large poorly designed table (inherited) With a Name field that contains comma delimited text containing address information. I need to do several things with it but unfortunately there doesn't appear to be any true consistency in it. When it displays in its own text box it works by placing each section on a new Line and looks ok.But I need to pull it apart and place things like unit number, Building Name in its own column etc. In the data it could be in either the 2nd,3rd, 4th, dependent on what came 1st. the data looks some thing like the following
unitNumber/StreetNumber Space StreetName (Building Name), Subub,City,Country
Some addresses won't have unit number or Suburb or country so when splitting you could have Suburbs and Citys in multiple columns even if you try and stagger the split process.Has any body go a good tool or reference site for dealing for this sort of problem. I have a table that I have made up that has some of the street names that could be used for comparing against existing records but it is by no means fool proof due to spelling inconsistencies . I also have another list of Common building names that could be used to compare, remove and place in the new building column.
We have an issue with importing a CSV file into SQL where using a double quote " text qualifier is failing. The data is correct but it fails on a particular line, complaining about the qualifier even though the qualifier is in place and previous lines have imported fine.
I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.
For example,
[atc1.123/atc2.456/atc3.789/atc4.xyz/]
If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing( DepDate datetime, val int) insert into testing values ('2014-01-10 00:00:00.000', 1) insert into testing values ('2014-05-19 00:00:00.000', 10) insert into testing values ('2014-08-15 00:00:00.000', 20) insert into testing values ('2014-11-20 00:00:00.000', 30)
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
I have scoured the Microsoft forums and the internet to find out how I can generate the output of a CSV report that has double quotes around each value and is comma separated as follows:
"Abcd","123456","Efghi","789012","JKLMN"
If I try to concatenate double quotes around the values in the stored procedure or in the RDL, two double quotes appear around each value as follows.Â
I understand that this is because the default qualifier is double quote.  What I see is that every time a double quote appears in a value (along with commas and line breaks), the qualifier will activate. Is there any way to turn this off for double quotes?Â
If I try to enter: <Qualifier>false</Qualifier>, the word "false" appears as the qualifier instead.
The only way I have found that produces a result similar to what I need ("Abcd","123456","Efghi","789012","JKLMN") is if I add a line break - chr(10) in the RDL in each field. However, this won't work for me because I can't have line breaks in each field in the output.Â
Note that in SSRS 2005, I was able to produce the report output as IÂ state above by setting the field delimiter and qualifier as follows:
This essentially turned the field delimiter and qualifier off, as the values entered would never appear in the data. I then could add double quotes and commas in the RDL.  This used to work in the old version but does not anymore.
