T-SQL (SS2K8) :: File Sequencing / Indenting And Reporting
Jun 6, 2015
I have been trying to query SQL to generate relationship table for a set of files (CAD Files).They are stored in our Data management System I am able to get the following results from SQL.
INSERT@Files
SELECT'0' LEVEL, NULL PARENT, 'A' CHILD UNION ALL
SELECT'1' , 'A' , 'B' UNION ALL
SELECT'1' , 'A' , 'C' UNION ALL
SELECT'1' , 'A' , 'D' UNION ALL
SELECT'1' , 'A' , 'E' UNION ALL
SELECT'1' , 'A' , 'H' UNION ALL
Kalpan writes "i want sequential data as below can anybody help
select dateofservice,tblsortorder.orroomno from tblcasedata , tblsortorder where tblcasedata.orroomno= tblsortorder.orroomno and dateofservice >= '8/25/2005' and dateofservice <= '8/27/2005' group by dateofservice ,tblsortorder.orroomno
sequence number which i have shown is not in the database but i want like this.
I have a maintenance plan in sql server 2005. After completing a full backup of all databases, I run a job which ZIPS each database. There is a job step for each of my 20 databases. After the ZIP, I want to start another OS command to FTP the files offsite. The FTP fails becausethe zip files are not there. They are not there, becase the zip job starts at exactly the same time as the FTP job, ecven though I have setup job constraints in the maintenance plan. It doesn;t seem to respsect the Start after success r Completion. It starts at the same time.
i have a table containing the top 5 teams points for the current season using the rank function, and as well as their points for last season.the only problem is that i want to add a sequential ranking to the table so that if there are 2 or more teams with the same score this season, they are ranked based on their points last season;
League -- Team - Season -- Points League 1 AAA 2013-14 90 League 1 BBB 2013-14 80 League 1 CCC 2013-14 75 League 1 DDD 2013-14 70 League 1 EEE 2013-14 65 League 1 AAA 2014-15 90 League 1 BBB 2014-15 80 League 1 CCC 2014-15 80 League 1 DDD 2014-15 70 League 1 EEE 2014-15 65
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I'm sure I'm not the only one frustrated trying to figure out which log file SSRS writes to when an error occurs. Does anybody know a sure way to tell? It doesn't change the date/time of the date modified, so you can't sort by date in windows explorer. There seems to be no rhyme or reason to which file it writes to. For example, I had to go through 6 log files to find which one was being written to. I had thought (hoped) that it would always write to the log files with the latest embedded date/time stamp as part of the file name. It does not.
I'm tempted to start using a file system spy, or resort to other tactics. Does anyone have a sure fired way to see which log file is written to when an error occurs? I'm not talking about SQL Dump files - just "normal" errors when processing reports.
I remembered that the Thesaurus that comes with SQL Server is essentially empty.Is there a Thesaurus that can be downloaded for US English so I won't have to sit there with Roget's doing data entry for a few weeks?
I would like to add to it the actual file size in mb or gb of each file to the results.
select sd.name,mf.name as logical_name,mf.physical_name, case when dm.mirroring_state is null then 'No' else 'Yes' end as Mirrored from sys.sysdatabases sd JOIN sys.master_files mf on sd.dbid = mf.database_id join sys.database_mirroring dm on sd.dbid = dm.database_id
The sp_spaceused procedure does a nice job on it's own giving me what I want (only one db though), plus a bonus allocated space column. How can I combine this sp with my other query, or is there a better way to ad this information?
I have about 1200 sql files in one of my folders. Almost all of these files do data inserts and updates, so they should be run only once. As and when required I have manually ran around 150 of them already. Whenever I ran any of these scripts, I log that file name into a log table in my sql server including the execution time. Since running 1000+ more files takes a lot of time, I want to automate running of these files through a batch file. But I also want to filter the files that are already run.My file list looks like follows.
select * from sqlfileexecutionlog FileNameRunTimeResult --------------------- DeleteDuplicateOrders.sql03/12/2014 14:23:45:091Success UpdateInventory.sql04/06/2014 08:44:17:176Success
Now I want to create a batch file to run the remaining files from my directory to my sql server. I also want to wrap each of these sql file executions in a transaction and log success/failure along with the runtime and filename into sqlfileexecutionlog table. As I add new sql files into this directory, I should be able to run the same batch file and execute only the sql files that have not bee run.
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master] RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1, MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf', MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
Below is the test data and my query. Basically i am trying to get the File records along with if any error messages occurred. I am getting the output as expected but the message column has "," at the front and sometime at the end of the column. How do i avoid that.
declare @Files table (ID int identity(1,1) primary key,Filename varchar(50),Date_Created datetime) insert into @Files (Filename,Date_Created) select 'File1',GETDATE()-1 union all select 'File2',GETDATE()-1 union all select 'File3',GETDATE()-1 union all
I've got a query that returns the data I need. I want to put the query in a stored procedure such that, when the SP runs I get a pipe delimited text file on disk. I don't really want to mess with SSIS, etc. Is there a Q&D way to do this?
I have created a trigger to call a program that is written by our program. The program is basically read the record in the table and write to a text file, then delete the record from the table.
The trigger is a after insert trigger. After we added the trigger, we insert a record to the table. The result is that the record still and did not get deleted. Also, the text file didn't get created either. It seems that it take a long time for the record to be written to the table.
But if we just run the program (a exe file), it can write a text file in the folder and delete the record. the trigger is basically:
USE [Zinter] GO /****** Object: Trigger [dbo].[ZinterProcess] Script Date: 04/29/2014 18:34:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
Does any one know a simple way of changing the delimiter value for a CSV report delivered via email from SQL Server 2000 Reporting Services? The default is comma. From the research I've done it seems that it can be done by writing a VB script that calls the Render method, but I'm not a developer and it seems crazy to have to go to those lengths just to change one attribute of a rendering extension that is available out-of-the-box. I hoped that I would be able to change a value in a config file (e,g, RSReportServer.config), but this seems not to be the case.
Does anyone know how i can go about merging preexisting pdf files and SQL server reporting services output. Can this be done in reporting services? For example, I have 5 pages from a pdf files which is created from another 3rd party software provider. I then i have output from sql reporting services. How can i merge these two outputs and deliver it over .Net/ ASP framework?
When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?
example : Report name : Testreport
Exported file should be : Testingreport-November-22-2007.pdf
I have a requirement to transform the XML format of the SSRS report while exporting. I tried with some XSLT to implement this, but not getting desried output. Default XML format of my report is
I am using BIDS 2012.I am getting this error: Unable to load assembly U2.
Data.Client, Version=2.1.0.0, Culture=neutral , PublicKeyToken=9ab9148678f4f448, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ---> System.IO.FileNotFoundException: Could not load file or assembly 'U2.Data.Client, Version=2.1.0.0, Culture=neutral, PublicKeyToken=9ab9148678f4f448' or one of its dependencies. The system cannot find the file specified.
[code]....
I am expecting C:WindowsMicrosoft.NETFramework64v4.0.30319configmachine.config
how can I force SSRS to use "C:WindowsMicrosoft.NETFramework64v4.0.30319configmachine.config".
When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?
example : Report name : Testreport
Exported file should be : Testreport-November-22-20076.pdf
When I am saving a SSRS report in excel format in a shared network drive, how can I add the date extension to the filename ?I assume this is something that needs to be handled within the SSRS reporting side rather than the subscription side.
When I am working on an Integrations Services project and I open a Reporting Services file, it displays it in the Code view. Is there a way to display it in the Design view? Or do I have to close the project and open up a Reporting Services project?
I'm trying to use SQL Server Reporting Services (SQL Server 2008 R2) to produce a CSV file. Row 1 in the CSV has to be a summary row with 8 columns. The detail rows which follow have 24 columns. The data in the summary row is "static" except for a date, a count of detail rows, and a total amount due based on the detail rows that follow. Here's an example of what we need it to look like:
HDR,4242,0,1,20150203,25,I,25823.18,,,,,,,,,,,,,,,, DTL,4242,0,1,20150203,255092,20150129,989,C,Net 0,Due Upon Receipt,12703,Some Super Customer,1001 Grandview Dr,,SomeCity,TX,US,75012,9729990000,,,, DTL,4242,0,1,20150203,255093,20150129,1360,C,Net 0,Due Upon Receipt,23774,Another Awesome Customer,52 Six Flags Dr,,DeepInTheHeart,TX,US,76006,8174445555,,,,
I've been able to get the report itself to render correctly in Visual Studio or from a browser using several different approaches but they all fail in one way or another when I try to save it as a CSV (eg, a header is prepended to each detail so they end up side by side, header and detail end up with extra columns, etc).
during period of troubles connecting to Local Servers and Sites location i.e., to [URL], the place where all Reports are saved by default, I have continued to work on some Reports that have been saved to file system of my localhost/developing machine. But, upon recovery of http location, I can not see/open those files?
how to approach those files in order to open them in Report Builder 3.0 and thus, to "save as" them to the http location? Seems that Report Builder has chosen that location, by default, somehow.....
I am creating a SSRS report which would be executed by User manually through ReportServer URL.User would be generating the SSRS report for different Customer ID based on ad-hoc basis.
I am passing CustomerID as input parameter to the report. Is there any way to get the manually generated SSRS report name as 'Report_CustomerID_TodayDate.xls'.
E.g.If User is generating report for Customer ID 123 today then report name should be 'Report_123_07092013.xls'
