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.
My source data is present in XML File which is stored in CLOB column Of Oracle. CLOB column is compressed.I need to Migrate data by Uncompressing XML to SQL 2012 .
Do I need to define XML column in SQL Server 2012 for storing Uncompressed CLOB values ?
How to uncompress the clob and extract the required data from XML using SSIS .
heyI have a problem string index;Command.CommandText = "Select * from employees where LastName = ' " Index " ' ";but the problem is iam not able to pass the index value properly like it should be ( ' Index ' ) for SqlServer2000.but its either giving me an error or its not taking the value properly.Can anyone help me out with that.Thanks
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest] CREATE TABLE [dbo].[StoreNumberTest]( [StoreNumber] [varchar](50) NULL, [StoreNumberParsed] [varchar](50) NULL) INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117 CA132 OH174 MD163 FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
Has anyone been able to solve the issue with setting up a package in EM, which connects to an Exchange(.edb) database and extracts the data into a SQL Server db. Previous message posts don't offer much insight. So far, all I have is using Access 2000, to connect & move the data, then extract from here. Thanks in advance for any help. BV
I have a form in Notes that is huge. I need to extract a part/field from that form into SQL SERVER. If I use NotesSQL Thru SQL DTSPackages, I am afraid it will run very long. Is there any other option on that?
Hey all, WE have a document management system where by Adminstrators can upload documents, once the document is uploaded the binary data is stored on in a folder on the web server. We used to stored the documents in the actaul db table, but we found that there were to many documents and it was using alot of space on db server. So my boss has decided we are now going to upload the binary data onto the web server. Currently we are donig this with new documents which have been added or documents which are gettinguploaded when reloading, but there are many documents in the db table which have not been updated and are still embedded in the db table. So i need to figure out how to go about copying the data storewd in the db table and storing it in web servers folder location. I've tried various things for a enitre day but im going round in circiles. MemoryStream mStream = new MemoryStream((Byte[])dtrResults["file"]); BinaryReader bReader = new BinaryReader(mStream); int intFileSize = (int)mStream.Length; Byte[] byteFile = (Byte[])dtrResults["file"]; i can get to this state but then how do i create a folder on the BinaryREader to then store the binary data of the file to the location. BinaryReader bReader2 = new BinaryReader(File.Open(strDocFolder + strSavedFileName, FileMode.Create)); int count2 = bReader2.Read(byteFile, 0, intFileSize); bReader2.Close();i've also tried this but when the file gets created in the folder there is no content. i do know that the file does contain content as ive tried this and downlaoding the file from that page acctually works string strContentTpe = WValue.WStr(dtrResults["contenttype"]); int intFileSize = VValue.VInt(dtrResults["filesize"]); / Byte[] byteFile = ((Byte[])dtrResults["file"]); //Downloads the data correctly Response.ClearContent(); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment; filename="" + WValue.WStr(dtrResults["docfilename"]) + """); Response.AddHeader("Content-Length", WValue.WStr(intFileSize)); Response.ContentType = strContentTpe; Response.BinaryWrite(byteFile); I hope ive made some snese andthat someone can hlep me. Have a nice dayZal
I have a coworker who needs to extract data from Oracle into SQL Server 6.5 and Sybase as part of an interface that runs daily. How is the best way to do this?
I am running an hotel application for all the bookings.
I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.
The application has an SQL database locally on my computer.
Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?
I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need.
In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.
When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.
When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'
This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.
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 am doing a project where I heed to extract data from a table in a sql server and then automatically create a bar chart with this data preferrably in PowerPoint (or excel). I was thinking myself that it may be done in vba/macro added to a button in powerpoint. If anyone knows how to do this i would be very grateful or if anyone know a useful website could they post it up.
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.
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 am trying to extract what I believe is called a Node from a column in a table that contains XML. What's the best way to do this? It is pretty straightforward since I'm not even looking to include a WHERE clause.
What I have so far is:
SELECT CCH.OrderID, CCH.CCXML.query('/cc/auth') FROM tblCCH AS CCH
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)
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
I would really apprecite if anyone could make a comment on this...
what i wanna know is.... there are two tables, called 'A' and 'B' with identical structures... is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2 * Records of table 'A' which not in table 'B' and * Records of table 'B' which not in table 'A'
