SQL 2012 :: Adding Header Information To XML Generated File
Apr 23, 2015
I have a sql query:
SELECT
'5' AS 'value/@version',
'database' AS 'value/@type',
'master' AS 'value/name',
LTRIM(RTRIM(( [Server Name] ))) AS 'value/server',
'True' AS 'value/integratedSecurity',
15 AS 'value/connectionTimeout',
4096 AS 'value/packetSize',
'False' AS 'value/encrypted',
'True' AS 'value/selected',
LTRIM(RTRIM(( [Server Name] ))) AS 'value/cserver'
FROM dbo.RedGateServerList
FOR XML PATH(''), ELEMENTS
I need to add some header information to the beginning of the query:
<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34--><multiScriptApplication version="2" type="multiScriptApplication"><databaseLists type="List_databaseList" version="1">
Everything I have tried ends up as a failure, usually compile issues. My goal here is to be able to automare a configuration file for multiscript so I can keep my server list up to date.
View 2 Replies
ADVERTISEMENT
Mar 28, 2007
Hi,
I've created a stored procedure that creates a script to create a number of objects within the database (based on what existing objects are in the database). From Management Studio, this works fine, and the output is exactly as I want it.
I'm now trying to create a job that will execute this stored procedure, and deposit the results into a file somewhere on the server. When the job runs, the script is created in the correct place and is essentially ok.
However, there are a couple of questions I'd like to ask.
Why does SQL Server Agent put a header at the top of the output file? I was hoping to be able to use that output file 'as is' and execute it automatically to recreate my objects when required. (Obviously, I can manually remove the header, but this is an inconvenience in this situation). How do I stop it?
Also, when executed from SSMS, the output is correctly line-spaced. But, the output from the scheduled job adds an extra line between each line of text, which is, again, inconvenient. Why does it do this, and how can I prevent this (again, without manually editting the output)?
Any thoughts and help greatly appreciated.
Cheers
Simon
View 8 Replies
View Related
Nov 21, 2014
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
View 2 Replies
View Related
Apr 18, 2004
Hi there :)
I'm in the final stage of my asp.net project and one of the last things I need to do is add the following file information to my SQL server 2000 database when a file is uploaded:
First of all I have a resource table to which I need to add:
- filename
- file_path
- file_size
(the resource_id has a auto increment value)
so that should hopefully be straight forward when the file is uploaded. The next step is to reference the new resource_id in my module_resource table. My module resource table consists of:
- resource_id (foreign key)
- module_id (foreign key)
So, adding the module_id is easy enough as I can just get the value using Request.QueryString["module_id"]. The bit that I am unsure about is how to insert the new resource_id from the resource table into the module_resource table on file upload. How is this done? Using one table would solve the issue but I want one resource to be available to all modules - many to many relationship.
Any ideas?
Many thanks :)
View 1 Replies
View Related
Jun 21, 2007
Hi-I have a sql database (2005) that I need to extract a report from that looks somehintg like SELECT * From Empl_Hours WHERE some_flag <> 'true' .The thing works fine, but the problem is this: I need to insert a record in the 1st row that looks like "Static_text"+row_count() +"more_static_text"where row_count is the actual # of rows that were retrieved. Thanks in advance for any help.DAn
View 3 Replies
View Related
Mar 5, 2015
I need to be able to see if the incoming csv file had a head row different than the previous files header row. That will tell me that I have new columns.
View 3 Replies
View Related
Sep 9, 2014
I have enabled 1222 and 1204 trace and restarted sql server. then i have replicated deadlock in my local.
This is the error came in sql
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Then i opened ERRORLOG file , there is no information about deadlocks .
Where can found deadlock information and file path. is there any extra options required to enable trace?
DBCC TRACESTATUS(1222,-1)
TraceFlagStatusGlobalSession
1222110
How to find location trace log?
View 9 Replies
View Related
Oct 13, 2014
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
View 4 Replies
View Related
Sep 17, 2014
When the database is configured for mirroring and you want to do partitioning on that database, How can we do? Is this similar process or any variation there while adding file groups and files? The partition will reflect in the mirroring database also?
View 1 Replies
View Related
Mar 3, 2006
My config :
1 computer with win2000, IIS, McAfee Virus Scan, and all I need to develope.
1 computer with VS2005.
When I try to make merge replication I have this Error :Header information is either corrupted or missing.
I search on the web and find some information like that error is cause by NIS (Norton Internet Security) but I dont have it.
I have another mobile software in VS 2003 and I don't have trouble whit im. It work perfectly but not in VS 2005.
I can access to the replication folder. I use sscesa20.dll.
Need help
thanks.
View 2 Replies
View Related
Nov 26, 2007
Hi
I'm trying to add columns to an existing table using ADOX. I'm using C++/CLI. My code is as follow
Table->default->Append("new_column", ADOX:: DataTypeEnum::adWChar, 255);
Table->default["prev_instance_id"]->Properties["Jet OLEDB:Compressed UNICODE Strings"]->default = true;
The first line of code works fine but when I try to set the value of the property I get the following excpetion:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll
Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Any idea?
Marco
View 7 Replies
View Related
Jun 4, 2015
We have a process here that produces several dozen Excel Spreadsheets every day from SQL Server using the bcp utility. the problem is that we cannot include the column headers when producing the Output. We are therefore trying to find a method where we can append the column Headers to the Spreadsheets; is there a way of doing this?
View 2 Replies
View Related
May 29, 2008
I am using an execute SQL Task item on the control flow to be used as input for the foreach loop container. I have a select statement but, based on other information provided before this is executed, additional 'and' information may need to be added onto the SQL Statement before execution. For example, the select statement may read:
select applicatonid, created, createdby
from application
where referred by = 'xxx'
But based on information obtained from an ini file, something like:
and referred date = '2008/05/28'
may need to be appended to the SQL statement. I have tried setting the SQL statement as:
select applicatonid, created, createdby
from application
where referred by = 'xxx'
+ @addlwhere
and defined the @addlwhere in the parameter mapping but it raises an error. Has anyone run across this type of need and how did you resolve it? Thanks!
View 4 Replies
View Related
Nov 7, 2007
How to add a link to the child report in the page header section of a report in SSRS 2005 ?Basically I want a link to the child report which should appear on all the pages of the report if the report has multiple pages.
View 2 Replies
View Related
Jun 30, 2007
Hi
I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0.
getting an Error "Header information is either corrupted or missing."
My Specification is follow :
Windows XP with SP2
Visual Studio 2005 Enterprise edition
SQL Server 2000 (SP3a)
SQL Server CE 2.0
Application is Smart Device application (Pocket PC 2003) using C#.
Microsoft ActiveSync 4.5
IIS 5.1
I followed steps as per given in this URL :
http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
private void button1_Click(object sender, EventArgs e)
{
getSyncReady();
}
SqlCeReplication loSqlCeReplication;
private void getSyncReady()
{
try
{
String lsDBFile = "Northwind.sdf";
loSqlCeReplication = new SqlCeReplication();
loSqlCeReplication.InternetUrl = "http://192.168.0.5/Northwind/sscesa20.dll";
loSqlCeReplication.Publisher = "C99";
loSqlCeReplication.PublisherSecurityMode = SecurityType.NTAuthentication;
loSqlCeReplication.Publication = "NorthwindProducts";
loSqlCeReplication.PublisherDatabase = "Northwind";
loSqlCeReplication.Subscriber = Dns.GetHostName();
loSqlCeReplication.SubscriberConnectionString = "Data Source=" + lsDBFile;
if (!System.IO.File.Exists(lsDBFile))
{
MessageBox.Show("The application requires synchronization", "Replicator",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
loSqlCeReplication.AddSubscription(AddOption.CreateDatabase);
}
loSqlCeReplication.Synchronize();
}
catch (Exception foException)
{
throw foException;
}
}
I am not getting whats going wrong.
My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
View 4 Replies
View Related
Feb 25, 2014
There are times when I either compile a stored procedure, or right now as I am creating indexes on tables, when sp_who2 shows a SPID assigned to my name that I do not currently have opened in SSMS. When I check the statement with dbcc inputbuffer it shows a query hitting many of the system views. Earlier this week, someone tried killing one of these SPIDS and SQL Server generated a new SPID with the same type of query.
View 5 Replies
View Related
Dec 29, 2014
My question: Is it okay to drop all the auto generated column statistics? (for the following scenario)
- I am cleaning up unnecessary objects (tables, unused indexes, overlapping statistics etc) from databases and found out there are more than 1400 auto generated column statistics on one database (lets call it A).
- Database A was used to be our reporting database but from last several years we are using database B for reporting. DB A has all the historical data while DB B only has valid records.
- We are updating all the column statistics with full scan nightly on database A and it is talking almost 2.5 hours to do that. Now I want to drop all the "unnecessary" statistics those were created when DB A was reporting database and they are no longer in use. There is no way to know the creation date of the column statistics that I know of. Statistics "last update date" is of no use because of our nightly job. So I was thinking of dropping all the auto generated column statistics and let the sql server create as it needs from now.
View 0 Replies
View Related
Jan 18, 2006
Hello,
I am working with an .xml file that I want to break up into various tables. A couple of fields in my xml file include html tags (<p> tags specifically). The generated xsd file thinks these are nested xml tags and creates a "P" table for the information contained. Is there a way I can modify the generated xsd within the designer? Or is the only way to fix this is to manually modify the xsd?
There is also an <id> tag within the xml. However, SSIS is not using that <id> as the primary key as it adds data to the various tables that I've specified. Instead it appears to be creating its own primary key - calling it "Id". Is there some way to specify that SSIS use the <id> tag contained within the xml instead of creating its own primary key?
Thank you for any help.
View 1 Replies
View Related
Jul 30, 2007
Hi Guys,
Related to my last questions on SSIS work i'm doing, Is there a way to pad 0 on my generated flat file dynamically.
I'm getting the data from 1 table and then generating the file. The file i need to generate would have data at the desired location as the file is being used by another system.
Depedning on the data I want to put the padding of "0" and "3" inc certain fields. How am i suppose to do it.
Apart from this I would need to megre 2 or more column and before the merge do an airthmatic operation.
What would be the best component to use script component or derived column?
Ta
Gemma
View 6 Replies
View Related
Jul 23, 2005
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies
View Related
Jun 26, 2006
Has anyone import into SQL Server a text file generated by DB2?
the DB2 files has bunch of weird characters in it that look like they are used for formatting...as opposed to actually being data.
Also, SQL can't find a row terminator.
I received a cobol declaration copy book for the data, but I have no idea what to do with the special characters.
Any help would be appreiciated.
There's a sample of the copybook...
* COBOL DECLARATION FOR TABLE @TNJ00.PL_JUDGM_ATTORNEY *
************************************************** ****************
01 DCLPL-JUDGM-ATTORNEY.
10 PJATY-ATTORNEY-KEY.
15 PJATY-JUDGMENT-ID PIC X(14).
15 PJATY-GROUPING-CDE PIC S9(4) USAGE COMP.
15 PJATY-ROLE-CDE PIC X(1).
88 PJATY-CREDITOR VALUE 'C'.
88 PJATY-DEBTOR VALUE 'D'.
88 PJATY-TRUSTEE VALUE 'T'.
15 PJATY-GROUP-SEQ PIC S9(4) USAGE COMP.
15 PJATY-ENTRY-SEQ PIC S9(4) USAGE COMP.
10 PJATY-NAME-PREFIX PIC X(4).
10 PJATY-LAST-NME PIC X(25).
10 PJATY-FIRST-NME PIC X(12).
10 PJATY-MIDDLE-NME PIC X(12).
10 PJATY-NAME-SUFFIX PIC X(4).
10 PJATY-CITY-NME PIC X(25).
10 PJATY-STATE PIC X(2).
10 PJATY-ZIP-CDE PIC X(9).
10 PJATY-TELEPHONE PIC X(10).
10 PJATY-LST-MNT-DTE PIC S9(9) USAGE COMP.
10 PJATY-REC-LOCK-CDE PIC X(1).
10 PJATY-ALT-NAME.
49 PJATY-ALT-NAME-LEN PIC S9(4) USAGE COMP.
49 PJATY-ALT-NAME-TEXT PIC X(75).
10 PJATY-ADDR-LINE-1.
49 PJATY-ADDR-LINE-1-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-1-TEXT PIC X(50).
10 PJATY-ADDR-LINE-2.
49 PJATY-ADDR-LINE-2-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-2-TEXT PIC X(50).
10 PJATY-ADDR-LINE-3.
49 PJATY-ADDR-LINE-3-LEN PIC S9(4) USAGE COMP.
49 PJATY-ADDR-LINE-3-TEXT PIC X(50).
************************************************** ****************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 20 *
************************************************** ****************
View 6 Replies
View Related
Apr 28, 2004
We are using SQL Server with logshipping.
I noticed in the last two days the presence of 2 bigs journal files
(about 2Go each, transfered from primary to secondary ).
I want to know if there is a way to see what was the operation that lead to have this to big 2 log files
(sql statement or transaction... : with Oracle for instance, if we have chance, we can find this kind of info in
a dynamic views by the name of : v$sqlarea...)...
Thanks in advance
View 1 Replies
View Related
Aug 16, 2007
I have a tsql script that gets the data I need, into the format I need, and saves it in a format (.output) I needI also have a script that creates a header for the report, basically its just a name and rowcount() that also works fine.PROBLEM: If I combine them using UNION, I have to pad out the header report with NULL columns, and it messes up the layout of the report.Anyone have a simple way to do this?here's my code:SELECT 'A71310000'+ltrim(Str(count(UserName))) + 'HRBATCH' AS header, NULL as col2, NULL as col3, NULL as col4, NULL as col5, NULL as col6, NULL as col7, NULL as col8FROM db_owner.PS_HR_HrsWHERE Reported is NULLUNION ALLSELECT EmplID, Convert(VarChar,DateWorked,111),'STSSH', CAST(REPLACE(STR(HoursWorked,9, 5), SPACE(1), '0') AS nchar(9)), HRAccountCode, CAST(REPLACE(STR(EmployeePayRate,18, 6), SPACE(1), '0') AS nchar(18)), 'A_STUDSUM', HRAccountCodeOverrideFROM db_owner.PS_HR_HrsWHERE reported is NULL What I need it to look like is: A713100007HRBATCH
10068800
2007/06/04STSSH012.00000 A108145 00000000007.500000 A_STUDSUM
10068800
2007/06/05STSSH002.00000 A108145 00000000007.500000 A_STUDSUM ...(this is a ragid right with spaces padding out fixed width columns) THANKS for ANY light ANYONE can shed on this.
View 14 Replies
View Related
Aug 27, 2015
In my SSIS Package, I have to write my [FileHeaderRecord] row, then my [BatchHeaderRecord] row, then my details. How can I do this in a SQL Server Query? When I try my SSIS, my file looks like so..
FHTEST 00000208262015 BH000208262015
I want my BH, Batch Header data, to appear on a new row in the file.Do I have to build a dynamic query to do this?Is there any trick in SSIS to do something like this?I did try creating separate Data Flow Tasks to Query the [FileHeaderRecord] and then use a Flat File Destination and then another Data Flow Task to Query the [BatchHeaderRecord] and use a Flat File Destination again NOT overwriting the file.
View 2 Replies
View Related
Dec 21, 2014
I have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.
UIDParam_ID Parameter Param_Threshold
1P1 Parameter1 NULL
4P2 Parameter2 NULL
6P3 Parameter3 NULL
7P4 Parameter4 NULL
8P5 Parameter5 NULL
9P6 Parameter6 NULL
The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.
UIDSiteIDP1P2P3P4P5P6P7P8
1721177503107NULLNULLNULL
27227129537710NULLNULLNULL
372897125851113NULLNULLNULL
4726881252997NULLNULLNULL
5727867502612NULLNULLNULL
673071312567107NULLNULLNULL
77214127535911NULLNULLNULL
I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.
UIDSiteIDParameter1 Parameter2 Parameter3 Parameter4 Parameter5 Parameter6 Parameter7 Parameter8
17211
27227
37289
47268
57278
67307
77214
I have attached the screenshot of the data for reference. I am not that much aware of sql queries.
[URL] .....
View 9 Replies
View Related
Jan 28, 2014
I have a table which I would like to format like so:
From this:
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED
[Code] ....
Just as a single varchar(max) field.
View 9 Replies
View Related
Jul 9, 2013
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'
View 2 Replies
View Related
Aug 12, 2015
I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).
The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
Example:
Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL
I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.
View 4 Replies
View Related
Aug 16, 2007
I have a parent a package which contains a bunch of Execute Package tasks. The parent package sets a variable which contains the directory for writing logs to. Each child is configured to write logs to a text file, and uses a connection manager for doing so. The connection manager uses an expression for setting the connection string, and in this expression the log directory varaible is used (e.g. @[User::LogDir] + "\FileName.log").
Now the problem is this, when I run the ETL, I'm getting 2 set of log files for each package: one log file is created in C: and the other in the correct dirctory. Each log in C: just contains a single header row, and the corresponding log file in the logging dir contains the log data (including the header row). Even though the filename is specified in an expression, a value for the connection string appears in the properties for the connection manager ("Filename" which probably where the C: log files are coming from). I can't seem to remove this value, and I don't want to hard-code it to a fixed path. I've also set DelayValidation to True, with no luck. I feel I must be missing something obvious, any suggestions? Thanks!
View 3 Replies
View Related
Apr 26, 2012
I am using SSRS 2012 RC0 in Visual Studio 2010 sp1 to build this tabular report. I had inadvertently removed the initial header row from my tablix before adding numerous nested groupings to move the column headers to the header section so they would show up on each page.
I would like to reverse that decision, but I seem to be unable to now re-add that header row back to my tablix. In older versions of SSRS, I was able to right click on the table and then click on insert row-->Header row but that does not seem to be possible in ssrs 2012.. I tried adding a outer row to my top most grouping, but that option was grayed out.
How I can add my header row back to my table.
View 7 Replies
View Related
May 29, 2015
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
View 2 Replies
View Related
Jul 16, 2015
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,
[Code] ....
View 3 Replies
View Related
May 16, 2002
Is there a way to save the column heading name in query analyzer when you save as a csv.file
View 1 Replies
View Related
