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
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)
hi my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.
i want to read the file in my root folder excluding the bin path
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
I want to move the SQL Server 7.0 root directory from C:MSSQL7 to D:MSSQL7 (same host). I see the startup parameters under server properties could be changed to point to the new location, but Master has the paths to all the databases recorded in sysdatabases table, so at least that would have to change, and sysdevices table points to files on C:. And maybe the NT registry points to some files there too. Anyway, is there some established method to move it? If that isn't practical, at least I would want to move TEMPDB ( perhaps by using the SP_DETACH and SP_ATTACH method ). My main database is already on D: so I do not have to move it. Any informed advice would be appreciated. Thanks.
I have a sql compact table that stores a series of directory paths like so: C:F1BackupadministratorCDatakit.zip__--1-07272007124351.F1O C:F1BackupadministratorCDataDeploySMD_FSWsetup.exe__--1-11292007122443.F1O C:F1BackupadministratorCDataDeploySMD_FSWSMD_FSW.application__--1-11292007122443.F1O
Is there a way in sql that I can get just the directory name so the results would look like this: C:F1BackupadministratorCData C:F1BackupadministratorCDataDeploy C:F1BackupadministratorCDataDeploy
Also, is there a way to just get the file names so the results would look like this: kit.zip__--1-07272007124351.F1O setup.exe__--1-11292007122443.F1O SMD_FSW.application__--1-11292007122443.F1O
I've seen some examples with Sql server but they use the reverse function which is not available in sql compact.
There was a 2008 SSRS service running on server "abc".
I have a .Net UI that uses the wsdl at [URL] ....
They deployed SSRS 2012 service on a new server "xyz".
DNS points abc to xyz. Nslookup looks right shows both xyz and abc alias to the same IP.
when I try to go to [URL] .... it redirects to xyz as expected for the host name but the HTTP GET /ReportServer is appended with GET /Reports/ReportServer which doesn't exist and I get an 404 error.
I have no admin rights to see anything and I have no idea if it was an inplace upgrade or a new server. I assume it's new since the actual server name changed in nslookup. Why would it append the default virtual directory when it makes the hop to server "xyz"?
I'm getting a rule check fail "Long path names to files on SQL Server Installation media failed" when installing SQL 2012 Standard edition from a network share.
I am adding a file to a filegroup on a remote system, and i don't know the direcotry structure of that machine, how can i provide the path for FILENAME param. :eek:
ALTER DATABASE TESTDB ADD FILE (NAME = N'TESTFILE', FILENAME = N'physicalfilepath.ndf') TO FILEGROUP TESTFILEGROUP
Hi everyone! I have a configuration file, I want to place it in somewhere of SSIS package to extract configuration information. But i dont know where is root directory. can you help me? Thanks you very much
I would like to know how to put an attribute on the root node of thexml returned from a FOR XML PATH query. One thing I tried is this:selectm.msgid '@msgID',st.namelong 'set/@namelong',st.nameshort 'set/@nameshort',frommsgset minner join settable st on (st.setid = m.setid)where m.msgID = 195for xml path('set'), root('message')but it gives me:<message><set msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /></set><set msgID="195"><set namelong="MORE STUFF" nameshort="M STUFF" /></set><set msgID="195"><set namelong="TESTING 123" nameshort="TEST" /></set></message>here is what I want:<message msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /><set namelong="MORE STUFF" nameshort="M STUFF" /><set namelong="TESTING 123" nameshort="TEST" /></message>I can't get it. If I use: root(''), then it tells me: "Row tagomission (empty row tag name) cannot be used with attribute-centric FORXML serialization." I'm sure there is a trick to this-- anysuggestions?Many thanks.the chippster
I want to write a stored procedure or a simple T-SQL snippet that takes a parameter "Directory Path" and gives me the name and datetimestamp associated with each of the files within that directory. I can not use xp_cmdshell since it is not allowed. CLR integration is not enabled on the server either.
Is there any other way at all to achieve this directly from SQL Server using T-SQL?
I am trying to restore database from network drive but sql server 2005 express is giving error "system.data.sqlclient.sqlErroratabase <dbname> cannot be opened due to inaccessible files or insufficient memory or disk space (microsoft.sqlserver.express.smo)." But when I restore database from "C:" or local drive it alows to do so. Can anyone help.
how to properly use the "For XML Path" code to concatenate multiple values into one record. What I have is a procedure I've written for a SSRS report that summarizes drive information: date, account, recruiter, times.But each drive can have multiple shifts, and each shift will have it's own vehicle assigned. So a drive may have 1 vehicle assigned to it or it may have 5. If there are 5 shifts/5 vehicles, I don't want to the report to display 5 rows of data for the one drive. So I'm trying to get all of the vehicles to be placed into one field for the report.My initial sub-query will not work because it is possible that it will contain more than one item (vehicle):
Select DM.DriveID [DriveID], DM.FromDateTime [FromDateTime], DSD.ShiftID [ShiftID], Case When DM.OpenToPublic = 1 Then 'Yes' Else 'No' End As [OpenToPublic], Case When DM.OwnerType=0 Then 'Mobile' Else 'Fixed' End As [OwnerType], Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
[code]...
SQL Server Newbie or here either. I'm a newbie as well.
I need to concatenate the DisplayName column with distinct UserName and SysName using XML path
SELECT [sysName], [User_Name],[DisplayName] FROM (SELECT v_Add_Remove_Programs.DisplayName0 AS [DisplayName], v_Add_Remove_Programs.Publisher0 AS [Publisher] , v_R_System_Valid.Netbios_Name0 AS[sysName],v_R_System_Valid.User_Name0 AS [User_Name],v_Add_Remove_Programs.Version0 as [Version] FROM [Offline].[dbo].v_Add_Remove_Programs JOIN [Offline].[dbo].v_R_System_Valid ON [Offline].[dbo].v_Add_Remove_Programs.ResourceID = [Offline].[dbo].v_R_System_Valid.ResourceID)
I'm posting this in the ClickOnce forums as well...
My application doesn't include the .mdf and .ldf files, rather it creates the database the first time the application is run using a script that is included in the build. I want to create the database in the ClickOnce data directory. The problem I'm having is that SQL Server complains when I execute the CREATE DATABASE command as follows:
CREATE DATABASE [C:Documents and SettingsxxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89DataiFieldMobile.mdf] ON PRIMARY ( NAME = N'myDatabase', FILENAME = N'C:Documents and SettingsxxxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89DatamyDatabase.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'myDatabase_log', FILENAME = N'C:Documents and SettingsxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89DatamyDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) END
Here's the error:
System.Data.SqlClient.SqlException "The identifier that starts with 'C:Documents and SettingsxxxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.00' is too long. Maximum length is 128."
basically what we need is a query that will allow me to provide a directory and a variable for number of days for instance 1 day old and I want to be able to able to delete all files older than that date and of course I want to be able exclude files of a particular type where I would give it a wild card statement for example say i wanted to save all csv files i would have the wild card say <> '%.csv'
I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
I'm trying to use a WMI Event Watcher Task as a directory watcher. I know it is available from Konesans at http://www.sqlis.com/23.aspx but they don't feel obliged to make this open source yet. I definitely appreciate the contribution of the binary to the community.
I'm very interested in understanding how the event handler gets the path of the file that caused the event. I added a Script Task to the Event Handler tab and it fires as expected when I create a new file in the directory. I can retrieve the value of the SourceName which is just the name of my Event Watcher Task. I can't find documentation on an object model that is exposed through SSIS that would reveal the name or path of the file, although it's clear this information is available in direct WMI queries from code or a WSH script.
My only guess is that you need to do a workaround in Script (or a custom module) by querying the directory for the most recent file (or list of files compared to a list of processed files) rather than a synchronous feed of the path directly into the SSIS event handler. Ideally, you'd like to get one event per new file created, although this might not be deterministic with the way the OS fires these events and SSIS responds to them. So at the end of the day, querying the directory for recently created files may be the only reliable way of getting the (list of) new files.
I know that this can take hours and hours to figure out since you really need to understand how WMI interacts with SSIS but I would be indebted if you can shed some light on this for me and the community.
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>
I have a View that I'm using to power a Crystal Report. I want to include an extra column that will be a string containing all the values from a single column of another table, seperated by comma.
Hello,I'm wondering if there is a way to concatenate two fields or a field and astring value in a single field in a view?Where in Access I might write;[field1] & " (m3)" as TotalVolumeis there a way to do this in an SQL Server View?Thanks!
My goal is to take the btb-date and store it in the same table I loaded the seperate nodes to. Currently I am loading the req-header, req-property-address, and req-borrowers.
This date will be static in that it will remain the same for every record. My goal is to read it in and store it along with each record. Hope someone can give me some help.
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
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.
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...