SQL Server 2008 :: Query A Column Of XML Files?
Feb 12, 2015
I have a table with lots of xml files in one column(more than 1000), like this
1. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
2. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
3. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...each is big
I want to query some values for all to see the duration but now I can only query one of them
declare @bp xml
select @bp=xml
from bloodpressureohneschema
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from(
select
m.c.value('(./bp:time/bp:value)[1]','date') as time,
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as value
from @bp.nodes('/bp:content/bp:data/bp:events') as m(c)
)m
is there somewhere I can make better?
View 5 Replies
ADVERTISEMENT
Apr 30, 2015
I want to query a column of xml files in a table,
use mysql1
declare @bp xml
select @bp=xml
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from (
select
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as systolisch
from
BloodpressureMitSchema cross apply
@bp.nodes('/bp:content/bp:data/bp:events') as m(c))m
But with this "cross apply" I can only query all the values in one xml and repeat them. Is there something wrong at "declear"
View 2 Replies
View Related
Jul 29, 2015
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
View 2 Replies
View Related
Jul 31, 2015
The below query will read the data in XML format but any query to read from each column of XML tags easily?
SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
View 5 Replies
View Related
May 9, 2015
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]....
View 5 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Jun 18, 2015
SQL SERVER 2008R2
The number of files to retain for SQL Server Logs is set to 99. When I expand the SQL Server Logs node in SQL Server Mgt Studio it shows the current log file through Archive#49. The oldest archive file is dated 2015/05/26. If I select that archive in SQL Server Mgt Studio it shows me the details and entries of that archive file. Yet when I go to the directory on SQL Server for the log files there are only the 5 most recent files. I have searched for '.trc' files on the entire drive and have found no other files.
How can SQL Server Mgt Studio show archive files that have no corresponding archive file in the directory that is supposed to contain the log files?
View 1 Replies
View Related
Aug 21, 2015
I am trying to locate the mdf,log and bak files but I am not seeing folder (MSSQL10_50.MSSQLSERVER) the only folder I see there are 90,100,110 and Report builder.
I located the properties of one of the databases below right clicking the properties on SSMS, but when I physically go there, I don't find that folder.
C:Program Files (x86)Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA
I gave myself all the permissions under the security tab fro the folder Microsoft SQL Server but still nothing.
View 9 Replies
View Related
Mar 11, 2015
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies
View Related
Mar 26, 2010
I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies
View Related
Feb 4, 2015
I need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .
View 2 Replies
View Related
Feb 8, 2015
I need to use SSIS to connect to an FTP server. From there I need to download files to a local folder. I need to download only today's files and also on those files starting with Training or Recruitment. I have managed to set up tasks that copy all but I am having such a hard time writing a script using C# that will download using the filters.
View 2 Replies
View Related
Feb 26, 2015
I am developing the SSIS and stuck on copying specific files.
1. We receive CSV file to our drive on a daily basis.
2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.
3. There are a lot of files in this directory.
[URL]
What we would like to do:
Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.
View 1 Replies
View Related
Aug 18, 2015
I have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often - the last one is dated January 5, 2015.
I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
View 4 Replies
View Related
Sep 14, 2015
I want to to move all database log files from drive E to F .
There are more than 10 databases so I don’t wanna move them 1 by 1 .
At the moment I use detach – attach method .
-Detach db
-Move log file
-Attach db
How do I do this massively in one go ?
View 5 Replies
View Related
Jan 10, 2012
I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:
use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)
I get the following message:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.
I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.
I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.
I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.
View 9 Replies
View Related
Feb 11, 2015
I am trying to create a job using power-shell script to move the backup files from one folder to another. I am using Ola Hallengren script for backups. Ola hallengren created a common backup folder with sub-folders for databases and even more sub folders for Full and Log backups. My goal is to move full backups, which are older than a month and save them in a different drive along with the same folder structure. I was able to move the first set of backups without any problem, but I can't move anymore files and keep getting this error even when I try to overwrite the previous file with the force statement:
Move-Item : Cannot create a file when that file already exists.
At line:5 char:9
+ Move-Item $i.FullName C:Test -force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (C:BackupVALIDATIONgcommon:DirectoryInfo) [Move-Item], I
+ FullyQualifiedErrorId : MoveDirectoryItemIOError,Microsoft.PowerShell.Commands.MoveItemCommand
Here's the script that I used to move the first set of files:
foreach ($i in Get-ChildItem C:BackupVALIDATION)
{
if ($i.CreationTime -lt ($(Get-Date).AddMonths(-1)))
{
Move-Item $i.FullName C:Test
}
}
View 0 Replies
View Related
Mar 18, 2015
I am new to SQL and I haven't written any scripts in the past. I thought I would give it a go. Basically, I am trying to write a script that will check if a database has more than one log files, free the VLFs that belong to the secondary log files and then remove them. I created a database named rDb as this link suggests and followed the steps.
[URL] ....
It works. However, I want to have to run just 1 script that will do the entire job. This is what I have gotten so far and it doesn't work:
create table #tempsysdatabase(
File_id int,
file_guid varchar(50),
type_desc varchar (20),
data_space_id int,
name nvarchar (50),
state int,
[Code] ....
View 0 Replies
View Related
Sep 15, 2015
We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:
MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp
With other tables, I'll get just one LARGE snapshot file, named:
MyOtherTable_4.bcp
In the latter case, the file can be very large (most recent is 38GB).
In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'
My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.
View 0 Replies
View Related
Oct 14, 2015
Any good starting point to understand for a specific db, how many max VLFs are good to have so that it does not cause long startup or backup times?
Also, I need some calculation so that I can identify a best growth parameter I will setup for each database ?
I'm seeing the below msg in errorlog and curious to know the changes (right sizing/growth) to be done? As of now 100 MB of log file growth value is set (refer: [URL] ....)
Database BizTalkMsgBoxDb has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
View 3 Replies
View Related
Sep 9, 2015
I just have a question regarding storing values to a column in ms sql 2008.
Why is it that the value I inserted at the column is truncated when selected in a query.
The column for this is created to accept max. values.
-> Message VARCHAR(MAX) NULL
The string which I need to insert is a combination of characters with a length of 14,720.
According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)
View 7 Replies
View Related
Feb 9, 2015
I need to import multiple csv files and load into table and everytime new database has to be created .
I was able to create new databases using stored proc
How do i do a bulk insert for all the files at once to insert into tables .
i want to load all the files at once .
View 6 Replies
View Related
Apr 23, 2015
SET NOCOUNT ON
Declare @daysOld int,@deletedate nvarchar(19) ,@strDir varchar(250)
declare @cmd11 nvarchar(2000)
declare @mainBackupDir varchar(2000),
@result1 nvarchar(max);
[Code] ....
View 1 Replies
View Related
Jun 3, 2015
I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp
View 0 Replies
View Related
Jun 11, 2015
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?
View 2 Replies
View Related
Jul 17, 2015
I've been struggling with this issue,
1) Test--FolderName (This Test folder name should use as a database name for below sub folders)
a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql
[Code] .....
The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..
Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?
The databases that they need to run against have the name of the database at the beginning of the name of the folder.
View 0 Replies
View Related
Jul 19, 2015
Im trying to upload 1000 txt files into one table in SQL. I'm using the following query, to upload one txt file at a time:
bulk insert [dbo].AAA_2013_2015
from 'dataserverSQL Data FilesSQL_EMELIZFC x Bloque Detallada201308 Detalle FacturasFACT_BLOQ_AGO13 (4).txt'
with (firstrow = 2,
lastrow = ???,
fieldterminator = ';',
rowterminator = '0x0A')
I'm trying that the query skip the last row because gives me the following error:
Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 17. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
know a command to skip the last row, something like lastrow= all-1...or something like that.
I also executed using MAXERRORS command...like this:
bulk insert [dbo].AAA_2013_2015
from 'dataserverSQL Data FilesSQL_EMELIZFC x Bloque Detallada201308 Detalle FacturasFACT_BLOQ_AGO13 (15).txt'
with (firstrow = 2,
fieldterminator = ';',
MAXERRORS = max_errors,
rowterminator = '0x0A')
does not recognize MAXERRORS command, also tried to put a number of error instead of max_errors.
View 0 Replies
View Related
Oct 22, 2015
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
View 0 Replies
View Related
Jun 11, 2015
Script to find the details of creation date and modified date of all files located in a path?
I wanna write few custom messages before I delete some files from a path.
View 9 Replies
View Related
Jun 27, 2014
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
View 9 Replies
View Related
Mar 4, 2015
I try to take the value from row 1 divided by the value from row 2 but it is not working, here is the following:
Here is my table called Trans
ID Period Sales Profit
1 Current 20 5
2 Previous 40 20
I want to take 20 divided by 40(20/40), 5 divided by 20(5/20)
Here is the result I want:
ID Period Sales Sales_Per Profit Profit_Per
1 Current 20 50% 5 25%
2 Previous 40 50% 20 25%
Here is my query:
Select
(t1.Sales/NULLIF(t2.Sales,0) * 100) as Sales_Per
From Trans t1
INNER JOIN Trans t2
on t1.Id = t2.ID - 1
View 2 Replies
View Related
Mar 12, 2015
I have table Called as ‘DC_BIL_ActivityLog’ and XML column name is ‘ActivityDescription’ in SQL Server 2012.
The following information stored on that Column. I want to read cancellation date (12/23/2015) using select statement.
<ActivityDescription>
<text value="PCN was initiated for Policy ^1 on 12/07/2015. Cancellation Date is: 12/23/2015. Amount needed to rescind PCN is: $XX.80." />
<link id="1" linkText="GLXXXP2015 12/02/2015 - 12/02/2016" linkType="policy">
<linkId parm="1" value="1140" />
</link>
</ActivityDescription>
View 8 Replies
View Related
Jul 29, 2015
I have a column name Classname and I would like to to Concatenate value.
ID ClassName
1 Class A
2 Class B
3 Class C
4 Class D
I need a output in ssrs report with title like (Class A, Class B, Class C, Class D) .
Can I do in SSRS as well ?
I tried join function in ssrs and I am getting #error join(Field!classname,",")
View 5 Replies
View Related
