Any Ideas Or Suggestions On How To Implement Excel Color Coding?
Apr 16, 2008
My SSIS packages produce output Excel files. Each Excel has a set of columns that must have a background color.
For instance, Col A1-B2 background should be Yellow while Col E background should be Green.
If my excel files are statically named, this requirement can be implemented with the Excel conditional formatting rules.
However, my scenario calls for dynamic Excel filenames. ExcelFilePath is set to be
in the form of <timestamp to the millisecs> <accountname> <costcenter> .xls
So I am not sure what to do: script task( ??? to do what). I am not well-versed with doing IO functions to Excel files.
Thank you much in advance.
View 1 Replies
ADVERTISEMENT
Feb 26, 2008
Hi,
I need some help regarding SSIS.
I have a table containing a list of source table names to be transferred to destination.I need to pass each and every table name as a variable and based on the name of the variable I need to select data from source and insert into destination.The source and destinations are on 2 different servers.The source tables have different metadata (different column names and data types).Any help how to implement this would be greatly appreciated.
To be more clear I have List table which has below format
TABLE NAME
CUSTOMER
PRODUCT
INVENTORY
Customer , product, inventory are tables names. So my package should first go and collect the table name from LIST table and then transfer the Customer table data from Server A to Server B. This should be repeat untill all the tables listed in LIST table are transferred.
View 3 Replies
View Related
Mar 24, 2008
Hi,
Im a Jr DBA and have been given an assignment by my lead to find information on the following.
We are to migrate existing db of size 4TB to a
DELL PowerEdge 2950[Mem:Up to 32GB]
OS : Windows Server 2003 Std Edition X64 SP2
DB : SQL Server Enterprise Edition x64
I am to find on how to design the db to provide optimum performance,fail over and consider the growing factor of the db.
1)What would be the recommended RAID settings?
2)Placement of the tempdb ?
3)Should we do clustering and why ?
4)What Data partioning would do to help?
5)Any Other aspects to be considered for sizing db ?
6)Placement of data files and log file on separate physical disk ?
7)Indexing?
I have read many sites.I would appreaciate if someone could write suggestions and opinions based on their current db design spec or previous experience,by selecting best db design points.Thank You.
View 8 Replies
View Related
Apr 11, 2002
I've read thru all the threads here which say basically the same thing, use a drop table & then a create table to clear the spreadsheet. My problem is the process works the first time to create the table in a new Excel file but fails to clear excel after that. The processes drop, create, & pump data all work without fail but the spreadsheet data is appended to. I've used the wizard as well, saved the package and ran it again without success. Anyone ran into this one?
View 1 Replies
View Related
Apr 29, 2004
My issue involves importing data from excel into MS SQL properly.
Currently I am using MSSQL Standard Edition, ASP Pages and VBScript together to allow users of my site to upload excel sheets with a specific column header arrangement and the subsequent data below.
The issue I am having is that even with the data formatted (General, text, or any other ways I have tried), MS SQL still does not recognize most of the data in the cells. Mainly data in the first column, the rest of the columns seem not to have as much of an issue.
Problem being is that the data consists of numbers and letters, more specifically part numbers. These part numbers can be a wide range of combinations of say 12HJ78UY-001 or NT78E64AA. If it is pure numbers such as 0983465, then it imports perfect, but if there are numbers and letters mixed then MS SQL places a <NULL> in the field instead.
I thought it was the VBScript, so I took one of the excel files and manually imported it into its own table into MS SQL. The exact same problem occurred.
I exported the data in the excel sheet to a text file (tab delimited), then imported it into a new table into MS SQL, it worked perfect. No data problems whatsoever.
I have even converted the data to a text file, copied it all, then performed a “Paste Special” (which I know my users will never do) on a new fresh Excel sheet, and MS SQL still cannot read the information properly, I get the <NULL>.
Currently everything I have is automated and I need to keep it that way, but I cannot seem to find an automated solution to convert xls to txt or make MS SQL read these excel sheets properly.
Any ideas? Thank you very much in advance if you have the answer.
View 12 Replies
View Related
Mar 9, 2007
I have one issue left to solve before releasing my drilldown report to production. The report displays fine in report manager; however, when it is exported to excel, the color (Coral) in the last column on the right is bleeding into the next three columns in excel. I have looked at properties, sizing and searched the archives. Anyone have an idea how I can fix this to render properly?
View 1 Replies
View Related
Mar 28, 2013
We are using SSRS 2012. We have a report that conditionally formats a background color for some cells. The report renders properly in a browser and in Excel 2003 format. In Excel format all cells after the first one that meets the condition are highlighted, even if only one cell should.
The sample expression that triggers this condition looks like this:
=IIF(Fields!VIOL_NOTE.Value="Internal","Green","No Color")
All cells after the first one that meets the condition Fields!VIOL_NOTE.Value="Internal" have a green background.
Excel 2003 (proper) results:
Excel (improper) results:
View 9 Replies
View Related
Aug 6, 2015
When using the back color property for SSAS 2008 R2, is there a good way to match the number to the desired color? I found some color pickers online, but the numbers don't match the same colors in SSAS. How can I best determine the number needed for the color I want?
View 2 Replies
View Related
Dec 28, 2007
Hi Guyz
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
thanks in advance.
View 4 Replies
View Related
Mar 14, 2015
I have a field in one of my tables that has the RGB colors stored as 255,255,255 format. Is there a way to convert this to Hex color code to be used inside SSRS for a conditional color expression?
View 9 Replies
View Related
Jun 23, 2006
Dim objConn As New SqlConnection Dim objCmd As New SqlCommand Dim Value As String = EventCmb.SelectedItem.ToString() objConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename='C:Documents and SettingsHPMy DocumentsVisual Studio 2005WebSitesFYP2App_DataEvent.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True" Try objConn.Open() objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @Value)" ' See how i changed Value to @Value. This is called a Named Parameter objCmd.Parameters.AddWithValue("@Value", Value) ' Add the @value withthe actual value that should be put. This makes it securer Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned. If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then ContactLbl.Text = RetVal.ToString() Else ' noting was returned End If Catch ex As Exception Throw ex Finally objConn.Close() End Try
There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "
Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int."
It is due to "ExecuteScalar() " can only store int? I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!
View 1 Replies
View Related
Jul 2, 2002
I'm attempting to copy data from an Epicore server with multiple company
databases to another server. All this data will be placed in a single
table on the receiving server with the addition of an database identifier,
so that the receiving department will know, to which company the data
belongs. I have code that will dynamically create the SQL and then
execute it using EXEC (@SQL), and this works just fine acrosss multiple
databases. I've read in my Transact-SQL Programming book that using
EXEC() is not limited to 255 characters, but it still appears that my
dynamically created SQL is being shortened. The insert statement and the
select statement list each column, and here is that code:
SELECT @CMD = "INSERT ##APMaster(Company, timestamp, vendor_code,
pay_to_code, address_name,
short_name, addr1, addr2, addr3, addr4, addr5, addr6, addr_sort1,
addr_sort2, addr_sort3, address_type,
status_type, attention_name, attention_phone, contact_name, contact_phone,
tlx_twx, phone_1, phone_2,
tax_code, terms_code, fob_code, posting_code, location_code,
orig_zone_code, customer_code,
affiliated_vend_code, alt_vendor_code, comment_code, vend_class_code,
branch_code, pay_to_hist_flag,
item_hist_flag, credit_limit_flag, credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag,
restock_chg, prc_flag, vend_acct, tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time,
doc_ctrl_num, one_check_flag, dup_voucher_flag, dup_amt_flag, code_1099,
user_trx_type_code, payment_code,
limit_by_home, rate_type_home, rate_type_oper, nat_cur_code,
one_cur_vendor, cash_acct_code, city, state,
postal_code, country, freight_code, url, note) SELECT '" + @DBName + "' AS
Company, timestamp, vendor_code,
pay_to_code, address_name, short_name addr1, addr2, addr3, addr4, addr5,
addr6,
addr_sort1, addr_sort2, addr_sort3, address_type, status_type,
attention_name, attention_phone, contact_name,
contact_phone, tlx_twx, phone_1, phone_2, tax_code, terms_code, fob_code,
posting_code, location_code,
orig_zone_code, customer_code, affiliated_vend_code, alt_vendor_code,
comment_code, vend_class_code,
branch_code, pay_to_hist_flag, item_hist_flag, credit_limit_flag,
credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag, restock_chg, prc_flag, vend_acct,
tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time, doc_ctrl_num, one_check_flag, dup_voucher_flag,
dup_amt_flag, code_1099,
user_trx_type_code, payment_code, limit_by_home, rate_type_home,
rate_type_oper, nat_cur_code, one_cur_vendor,
cash_acct_code, city, state, postal_code, country, freight_code, url, note
FROM " + @DBName + "..APMaster"
Can anyone provide any insight to my problem?
TIA,
Chris
View 4 Replies
View Related
Oct 31, 2001
Hello,
I have the following code and it seems that it is not comparing the request_close_date. What I want to do is compare the request_close_date between two tables and if it is less than the date from the TEAM3B_PULL_TOTAL_TST then insert the data into RDD_UPDATE_TST. Any help will be appreciated. It also needs to insert data into RDD_UPDATE_TST if a request exists in REQUEST_BUS_REQ and BUS_REQ_DESCRIPTION. I think that I have that coded correctly but I am not certain.
Thanks in advance,
Anne
begin
Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr,request_close_date)
select distinct request,business_req_id,test_case_id,test_case as test_case_descr,request_close_date
from TEAM3B_PULL_TOTAL_TST A
where not exists (select * from BUS_REQ_DESCRIPTION_TST B where A.REQUEST = B.REQUEST)
and not exists (select * from REQUEST_BUS_REQ_TST C where A.REQUEST = C.REQUEST)
and not exists (select * from RDD_UPDATE_TST D where A.REQUEST = D.REQUEST)
and exists (select * from RDD_UPDATE_TST where REQUEST_CLOSE_DATE <>B.REQUEST_CLOSE_DATE)
end
View 1 Replies
View Related
Apr 28, 2003
Hi im an newbee so im not the best but try to get there some day..
CREATE TABLE table_members
(id int(11) NOT NULL auto_increment,
fornamn char(60) default NULL,
efternamn char(60) default NULL,
address char(60) default NULL,
personnr INT(11) default 0,
smeknamn char(60) default NULL,
epost char(60) default NULL, i must be a @ value in it! any ides?
kon ENUM('M','K','V') default 'M', is this right, must only be enable to type in this Alphabets sings MKV!
losenord char(60) default NULL, verlosenord char(60) default NULL,
How do i get a or fix a retype field for this losenord and verlosenord thay must be the same value to be insered in to the sql!
PRIMARY KEY (id)
UNIQUE ('smeknamn');
Any ides are good
View 1 Replies
View Related
Mar 12, 2007
Help me...
I need C# Language ASP.Net coding...
i've two column... One for msg another for msgLastposted
in label control view msg(last visitor)....... that msg stored in my db.
Example Table Data
---------------------------------------------
MSg|MsgLastPosed
How edit forum |2007-03-12 10:50:25.747
How to Value Change|2007-03-12 10:56:36.373
Sql Command|2007-03-12 11:00:25.047
User Control|2007-03-12 11:02:10.793
How I can uninstall|2007-03-12 13:07:51.233
-----------------------------------------------
In table have many record..
label control display msg based on last visitor time.. after that again msg
changed( based on next last visitor time).. Continue for upto first 5 record msg automatically changed..
View 1 Replies
View Related
Aug 28, 2007
Hi all,
I want codeing for to find (subtotal & total)the following
Input
productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal
gh 10 10
ab 10 10
Total
output
productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal 15 10
gh 10 10
ab 10 10
Total 20 20
View 3 Replies
View Related
Jul 25, 2006
:This is a segement of VBscript for checking lastest Backup in SQLServer 2000:
'temporary variables to useDim sTemp1Dim i, sTemp2
'read start arguments' 1 => servername' 2 => path for logfile' . => excluded databasesDim objArgsSet objArgs = WScript.ArgumentsIf objArgs.Count < 2 Then 'Not enough parameters WScript.Quit 2 end If
If the above VBScript is converted to VB.NET, how can the lines containing a boldfaced WScript be coded? What namespace in .NET should be used for that? Thanks?
View 3 Replies
View Related
Jun 14, 2007
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')
also is it wise to use Group by and having in sub-queries?
View 2 Replies
View Related
Jul 20, 2005
Hi;Is there an **easy** way to tell tsql apart from standard sql?Will sqlserver run scripts written only in standard sql?What about variable definitions?Thanks in advanceSteve
View 8 Replies
View Related
May 16, 2007
I am attempting to build a query that will allow me to fetch data from a table for only the last seven days. This will be used for a daily report that will need to update the day to current date every time it is run. Any help would be greatly appreciated.
Thanks
Andrew
View 2 Replies
View Related
Oct 29, 2004
I need for a database to give my users and indication that a renewal has been complete. Basically what happens is every year once a month a report is generated from sql of how many employees need their gaming license renewed the filter is based off of a field called final suit. I need to find a way to let them know through the database that an employee has been renewed. anyone got any ideas??
View 9 Replies
View Related
Apr 9, 2007
Hi all
I am fishing for ideas on the following scenario and hope someone can point me in the best direction.
I create a new table and insert data from relevant other tables but i want to set the data in the new table colums to set widths that have leading zeros where applicable.
I.E the new table column is varchar (10) the data going in to the column is coming from another table where the size was varchar (8) but the data was only 2 characters in size so i want to pad it out to the full new varchar (10) with leading zeros if that makes sence.
I am really just trying to get some ideas on the best possible way to do this thanks.
View 2 Replies
View Related
Nov 9, 2007
I have a table with 18,000 records with beg_eff_date since the year 2005. I need to separate the entries based on their daily activity. For example if Beg_eff_date 01/0/2005 then day is "1" if Beg_eff_date is 01/27/2005 then the day is "27".
Repeating the same process until I reach "NOW" present time. Any ideas of how can I do this?
Thank You for all your previous help and the current one!!
View 9 Replies
View Related
Nov 12, 2007
Hi:
I created a Temp Table that hold a collection of records by date.
I need to do create calculation that give me the Total fuel used per day:
c.rcpt_nom_vol -c.rcpt_fuel- c.rcpt_act_vol = Total Fuel used per day.
Then pull the result of this calculation and assign the result to the specific day of the month. For example is the Calculation Result is 4.25 on Feb 12, 2007. Then the record is insert into a Temp Table as 4.25 day #12.
Does anyone has an idea of how to do this? Thanks and Let me know!!!!
Create Table #TP_Daily_Imb(
contract_nbr char (8),
contract_sub_type char (3),
contract_type_code char (3),
current_expirtn_date datetime,
nom_id char(12),
nom_rev_nbr char(2),
beg_eff_date datetime,
rcpt_dlvry_ind float,
rcpt_nom_vol float,
rcpt_fuel float,
rcpt_act_vol float,
end_eff_date_DGC datetime)
SELECT Distinct a.contract_nbr, a.contract_sub_type, a.contract_type_code,a.current_expirtn_date,
b.nom_id, b.nom_rev_nbr,
c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel,c.rcpt_act_vol
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')
View 9 Replies
View Related
Feb 5, 2007
Hi everyone
We've got currenlty around 500 dts 2000 in production.
In order to know in what ETL processes we have Oracle connections, or FTP tasks or whatever, we did a VB6 app using dtspkg.dll which load all the properties for each DTS into Sql server tables. So that, then you could see from a specific DTS how many connections, Sql Tasks it had and so on..
How to accomplish the same with SSIS? I know, doing the same but using .Net, of course, but is there any else approximation? I am little bit concerned when we will have hundreds of them up.
Maybe 2005 is offering this feature automatically, I don't know.
Thanks in advance for your time/advices/ideas,
View 1 Replies
View Related
Mar 1, 2008
Am working on an SSIS project and I am not sure how to handle this situation. I have four tables in two completely separate networks {AB} {CD} and I want to populate one of the tables D based on the information from tables A and C with the following scenario I get data from from table A make a lookup transformation to check what did has changed if the data has changed or if there is a new entry in table A get the identity key from table C {they both share a common field} and add the identity key as part of my data inserted in table D
A ----lookup <
data that has changed --- get the identity key from C ---- insert data from table A + identity key into D
View 5 Replies
View Related
Mar 19, 2007
I have a text file that I am importing in a data flow task. Not all of the rows are the same, there are "header" rows that contain a company code.
What I want to do is keep that company code and append that to the row of text that I am writing to a CSV file.
Since you cannot change variables until the post execute, what are my options?
Hope that's clear
Thanks!
BobP
View 3 Replies
View Related
May 16, 2008
I have some performance issues which I can not explain; The database it is running on on a dedicated SQL server box with 8 cpu€™s 4 GB memory. Users are connecting with a fat client.
There is hardly on users using it but they are getting long delays. So I kicked of a profiler trace and sp_blocker. I noticed some queries taking over 30 sec to complete in profiler but when I run them in query analyzer they run in under a second.
I check the output from the blocker and there was no blocking over that time period and it was the only processes actually running.
How can it be so slow I have run out of ideas please any ideas would be welcome
Many thanks
View 7 Replies
View Related
Nov 2, 2004
Hello,
I'm coding a stored procedure. I am creating a search function where I search multiple resultsets of data. I'm getting stuck on one part of the query, and I don't know what the best option is. Basically the query looks like this:
select * from Table where ...
and Official in (... problem area ...)
What I want is if the parameter passed in is null, return all of the valid values from the table that references this field (for example, if null is passed in, I want to pass in 'T', 'F', etc.). If the value is there, I want to pass in the value. I've been stuck on this and can't figure it out.
Any ideas? I don't want to use a dynamic query. Thanks,
Brian
View 5 Replies
View Related
May 5, 2006
Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updateddatetime)
insert into #blindman (pkey, fkey, updated)
select1, 'A', '1/1/2006'
UNION
select2, 'B', '1/1/2006'
UNION
select3, 'A', '1/2/2006'
UNION
select4, 'B', '1/2/2006'
UNION
select5, 'A', '1/4/2006'
UNION
select6, 'B', '1/2/2006'
UNION
select7, 'A', '1/3/2006'
UNION
select8, 'B', '1/3/2006'
UNION
select9, 'A', '1/5/2006'
UNION
select10, 'B', '1/5/2006'
drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
------ ---- ---------- --------
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.
Who's up for the challenge?
View 14 Replies
View Related
Oct 10, 2006
ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:
What is the total weight of different colored parts?
ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:
Select sum pweight
from parts
where distinct pcolor
looks horribly wrong so any help is appreciated. here is another one:
What colored part has total weight greater than 8 units?
select pcolor
from parts
where sum pweight > 8
???
i dunno lol.
there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?
View 2 Replies
View Related
Nov 14, 2006
Hello
Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.
Is it possible to grant permissions without specifying the username,passwd
Thanks In Advance
Poornima
View 16 Replies
View Related
Jun 22, 2007
I am new to MS SQL coding and I am having a problem with date conversions. In PL/SQL, I could convert numeric months into character months in their own columns by using the DECODE function. An example would be:
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Apr', '05', 'May', '06', 'Jun', '07',
'Jul', '08', 'Aug', '09', 'Sep', '10', 'Oct',
'11', 'Nov', '12', 'Dec', '01', 'Jan', '02',
'Feb', '03', 'Mar'),
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Q1', '05', 'Q1', '06', 'Q1',
'07', 'Q2', '08', 'Q2', '09', 'Q2',
'10', 'Q3', '11', 'Q3', '12', 'Q3',
'01', 'Q4', '02', 'Q4', '03', 'Q4'),
How does one convert something like this in MS SQL?
View 15 Replies
View Related
