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 RepliesHi all,I have a problem and need some ideas.What I have done: I created a page to upload an excel file into a SQL Server table along with some customer info (from the login, day, etc.). This excel file contains several rows (some of them may be blank) and columns (also some may be blank). The file is stored in an image object.The file will be checked (they want to do it manually, because contents is a problem). If they say it is OK, I want to run a program to add a record into an existing table with the request no. (from the first table, where the object is stored) and all the information available from the filled rows (first row is header). I have a column, which can be checked, if the row contains data or not.Any ideas?I know how to read from and write the contents of the object to a field in the SQL table. Can I use this?Thanks for any idea / code / link.
View 2 Replies View RelatedHow can I use code (wither it be SQL or .Net Framework) to programmatically import 8 different Excel Sheets into One SQL Table (that currently does not exist)?
This is SQL Server 2005. Excel 2007.
I'm battling with importing sales data from different ERP systems into a SQL table. I have multiple spreadsheets, which have the same sheet name, column names and formats. I use a for each file loop container to loop through the different files. I use variables passed to a script to pull the relevant source information from the file name. I then use the variables to create derived columns in the flow to indicate the datadate and the company division.
My problem is even though all the data and metadata looks the same, there are a couple of columns between the two spread sheets that alway complain about the metadata not matching or a coumn needing to be added to the external metadata column collection and the same coumn name needs to be removed from the external metadata column collection.
These spreadsheets are coming from different countries but I have gone in and adjusted any fromats that are different from the excel spreadsheet used as the template. The same two columns out of 12 complain and I'm not sure what is causing the error. In the error is
Warning: 0x800470C8 at Read excel File, Excel Source [274]: The external metadata column collection is out of synchronization with the data source columns. The column "COGS" needs to be added to the external metadata column collection.
The "external metadata column "COGS " (2512)" needs to be removed from the external metadata column collection.
Eventually, I'll have 3 spreadsheets to load and need to find out why they are not viewed as the same data and metadata when they apear that way on the surface. I've searched numerous blogs out there (Even Jamie's) and can't find anything.
Thanks,
pkdenver
Hi All.
Would you happen to know how one could convert some Excel spreadsheets to MSSQL? There is some commercially available software, but there must also be a way for me to manipulate the files.
Many thanks!
Nazli
Hi, there
Some of the reports I am generating have tens of columns so the management decides to use Excel files only.
Is there any way that for a single report (not the whole project) I can disable printing and most of the exporting options (including PDF, HTML, TXT ...) and only leave the xls files available?
Thanks a lot.
Heng
I am trying to import data from named ranges in excel spreadsheets. I have no troubles if the named sheets have the same number of columns but I can not get the package to work when the spreadsheets have different number of columns. I am using the excel source as the input to my dataflow. I have tried setting it up with the maximum number of columns but if the named range has less than that, I am getting an error.
View 1 Replies View RelatedI need some help. I am writing a report in SSRS 2005 that I then need to export to Excel. When I put a report header I would expect the header to not display in the Excel spreadsheet until the Print Preview or the Print. The report footer works just fine I put some text in the footer, and it shows up in the footer. The header though, shows up as a row in the Excel spreadsheet that then causes columns to merge. How do I get the report header to act like a page header?
View 1 Replies View RelatedI 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.
Hi All,
I am having a server where replication is set up between 2 differnt databases. It is currently running. I want to add a couple of tables to the replication. I tried using sp_addArticle, but after executing it, in the properties of the publication it shows the new tables, but at the database level the tables are missing.
I tried with sp_addsubscription but I am getting strange error:
Server: Msg 14100, Level 16, State 1, Procedure sp_addsubscription, Line 240
Specify all articles when subscribing to a publication using concurrent snapshot processing.
What can I do to publish the tables into the target database?
Thanks in advance.
Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!
I want to add a computed field to an existing SQL table:
where Field1 is >0 and field2 is not null
set newfield = 'Y'
else set newfield = 'N'
I want to keep this existing table because I'm using it as the basis for an Access Report that is nearly complete.
BTW this table is the result of a DTS package that is comprised of several SQL executables so I need to be able to repeat this as part of a larger process.
Thanks!
sgreene
Hi Friends,
I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
Thanks in Advance
Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!
how do i add columns to an existing table?
View 1 Replies View RelatedDear Friends,
what is best and quicker way to add 500 columns to existing table having 145 columns already.
Is there any way to avoid manual work of adding columns one by one in design mode or using script.
I have a TXT file (comma delimited) that contains all those columns names as a first row,but I am not sure if i can use DTS package to create table design having such sourcre TXT file.
Any advice?
Thanks,
Hello,
We currently have a 2 node Active/Passive 2 named instance SQL 2000 cluster. We will be chaning the configuration to Active/Active, basically moving 1 instance to the passive node (so we can take advantage of the resources on the passive node).
We would also like to add 3 nodes to the cluster making it a 5 node SQL cluster. What we are thinking of doing is basically making it an Active/Active/Active/Active with the 5th server being passive. The question I have is will I be able to add and install the 3 new nodes without having to redo the SQL cluster? Should I install the two new active nodes with the default SQL instace or do I have to install SQL with named instances? (actually I think the named instances is the way to go but then I wouldn't be posting here if I was sure about the answer).
Thx!
-Albert-
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.
I've built a fairly straight forward report in RS that looks normal in preview mode and in PDF format with out any issues.But when I export it to Excel report header is not appearing in each page.Any ideas as to why this is occurring?thanks in advance,Ramesh KS
View 1 Replies View RelatedIs there a way for the rdl to make a header/title and make it to be displayed in the excel header when exported?
View 6 Replies View RelatedI cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :
ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)
This gives me a syntax error.
The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.
Anyone have any ideas? Thanks in advance.
Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.
Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
Any ideas?
I want to add new primary key into existing table which already has a primary key. But,I do not want to remove the old primary key, since there are many records and the old primary key also have relationship with other table
When I am using this query:
alter table hem154
add indexNO uniqueidentifier default newid()
alter table hem154
add CONSTRAINT pk_hem154_indexNo PRIMARY KEY (PK_indexNO)
go
Note:
Hem154 ~ Table name
indexNo ~ Column Name
I get this runtime error:
Msg 1779, Level 16, State 0, Line 1
Table 'hem154' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I have a table. I want to add 2 date columns. One when we are inserting any record it will show and another whenever the record updated to record that.
I want to insert dummy data for the previous dates. How to insert those dummy dates in batch wise?
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:
SELECT A.COMPANYCODE,
A.INVOICENUMBER,
A.LINENUMBER,
A.SONUMBER,
A.CUSTOMERCODE,
A.SHIPPERNUMBER,
A.INVOICEDATE,
A.ITEMCODE,
A.QUANTITYINVOICED,
A.UNITPRICE AS 'InvPrice',
A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice',
INVENTORY.UNITPRICE AS 'StdPrice',
INVENTORY.STANDARDCOST,
A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost',
(A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
(INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff'
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE
AND INVENTORY.ITEMCODE = A.ITEMCODE
AND ((A.COMPANYCODE = 'csp')
AND (A.QUANTITYINVOICED <>$ 0)
AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
A.ITEMCODE
They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:
SELECT NewColumn = CASE
WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
ELSE 'null'
END,
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.
Any help would be greatly appreciated to modify the current query to display the new column.
Is it possible to add new data to an existing report. I already updated the SQL query, but the new data does not appear within the report. How can I modify the rows, columns and data fields???
Thanks in advance!
I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.
thx
Kat
HI,
Is there an easy way to install reporting services on a machine that is already running sp1? When I initially isntalled Sql Server I did not install ssrs. Now, SP1 is installed and when I run the setup tool to install ssrs, it warns me regarding a "version change", presumablly because my media is not sp1, but RTM. Any ideas?
Dear Friends,
I need a SQL Query to add a identity coloumn for anExisting table. (ie) when i try to alter the table i want to add an identity coloumn.
Thanks in advance.
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 RelatedAfter i run the sql which adds some columns on one particular table.I am getting this Warning
Warning: The table 'usac499_499A' has been created but its maximum row size (9033) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I got a series of the above warning message , but the coulmn wa created.
I have this already existing query and want to add another field to it but its failing. Below is the current query
REPLACE(CONVERT(CHAR(10), A.BATDAT, 1), '/', '') + RIGHT('00000' + CONVERT(VARCHAR, A.BATSEQ), 5
RIGHT('000' + CONVERT(VARCHAR, A.SEQNBR), 3) AS CLIENT_TRACK_NO
I want to add a field from a table name APTTL and the filed name is DTYYY char (2)
In my local environment , i was setup transnational replication then i have added new article by using below script
EXEC sp_changepublication @publication = 'demo', @property =
N'allow_anonymous', @value='TRUE'
Go
EXEC sp_changepublication @publication = 'demo', @property =
N'immediate_sync', @value='TRUE'
EXEC sp_addarticle @publication = 'demo', @article ='employee',
[Code]....
But article not added and showing this error message The initial snapshot for article 'employee' is not yet available.
What is the issue, what can i do to add new article ? What did I mistake?