I'm hoping someone can shed some light on a reliable way to get Reporting Services if you don't have an MSDN subscription.
I have registered copies of SQL Server 2000 Developer and SQL Server 2000 Standard that I bought late last year.
I've had a backorder with MS Parts since January to get updated CDs with Reporting Services on them. Each month, the back order is pushed up another month. The disks are now due sometime in late May. I called the fulfillment center and the operator end didn't seem too enthusiastic about it coming in stock anytime soon.
All 3 versions (Developer, Standard, and Enterprise) are on a long back-order.
I thought I could get by temporarily by using the 90-day trial copy of Reporting Services, but I'm now concerned it might run out before I get my own non-trial version.
Isn't there a way for legit users to get a hold of this product? I'd be happy to download it, but it's not available unless I want to pay add'l money for an MSDN subscription.
In Report Builder 1.0 you could drag a column to a filter and see the data before select the values for the filter.I can't find a way to do that in report builder 3.0. is it possible to see the data of a column in Report builder or SSRS?
I am working on a custom component to implement some rules based on the column name. I am looking for ways to identify the column name using lineage id. Is there anyway we can derive column name using the lineage id?
I am creating a report on a database where some of the table names start with the @ sign - ie @table1.
Reporting services picks this up as a parameter, instead of a table name in my query, even though I am encapsulating the table name in square brackets eg. [@table1]
I have several data sets in the report that i am using to populate valid parameters. These datasets are all variations of queries from tables that have @ as the first character.
When i then try to run the report i get an error message as follows: "The report parameter pool has a default value or a valid value that depends on the report parameter SD_POOLCONTRACTS. Forward dependencies are not valid"
This is frustrating as SD_POOLCONTRACTS is not a report parameter but one of the database tables that has @ for it's first character.
here is the query that i use to obtain the valid values for the pool report parameter that i am trying to set up.
SELECT distinct u_poolcode as Pool FROM OCRD INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN CRD1 ON OCRD.CardCode = CRD1.CardCode inner JOIN [@SD_POOLCONTRACT] INNER JOIN [@SD_POOLCONTRCT_LINE] ON [@SD_POOLCONTRACT].DocEntry = [@SD_POOLCONTRCT_LINE].DocEntry INNER JOIN [@SD_CONTRACTS] ON [@SD_POOLCONTRCT_LINE].U_DocNo = [@SD_CONTRACTS].DocNum ON case when len(ocrd.fathercard) = 0 then ocrd.cardcode else ISNULL(OCRD.FatherCard, OCRD.CardCode) end = [@SD_CONTRACTS].U_CardCode WHERE (OCRD.CardType = 'c') AND (OCRG.GroupName LIKE N'producer%') AND (CRD1.AdresType = 'b') ORDER BY OCRG.GroupName, FatherCard, OCRD.CardCode
I have a requirement to show Day of week in parameter drop down list in different order, actual order is Monday to Sunday (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) in DayOfWeek dimension in cube.
But my requirement is to show Friday to Thursday(Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday) in DayOf Week parameter drop down list and report table. How I can get this requirement done.
I have multiple excel Files each has one sheet (With same column names) need to be loaded in a single table. I tried For each loop but couldn't succeed.
As I am new to SSIS. How to configure For each loop container for this...
We run std 2008 r2. I'm looking at the files this transform is complaining about. They seem to be named appropriately. The customerid folders don't exist when this runs. I'm going to put one in place to see if that is the problem.
The errors i'm getting are...
[Export Column [22]] Error: The file name "c:usersmyuserid heprojectnamecustomeridafilename.doc" is not valid. The file name is a device or contains invalid characters. [Export Column [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Export Column" (22)" failed because error code 0xC020207F occurred,
and the error row disposition on "input column "FILENAME" (29)" specifies failure on error. An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Export Column" (22) failed with error code 0xC0209029
while processing input "Export Column Input" (23). The identified component returned an error from the ProcessInput method. The error is specific to the component,but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
I'm in the process of converting a rather huge VSAM database into a set of SQL tables. I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME). I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255). And I have to cut and paste the data names from the manframe side to the server side (and the data types to.) So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away... Thanks for any help.
Ok, i am finally giving in on this one and asking for some help! I am trying to set up a T-SQL Statement that will extract data from all the tables in the current database to a csv file including Column names!
I know that bcp can not handle the column names, so i tried to get around this with an append of the column names from a select, but unfortunatly the select gives me the names in Alpha order and not the order of the fields.
I have tried putting in an order by on the select, but this does not seem to have any effect. I have included the snippet of my script that is causing the problem here :
-- set up the echo command select @colcommand= 'exec master..xp_cmdshell' + ' ''' + 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt' + '''' from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE
and just in case you are interested in the rest of the script, the full monster is included at the bottom of the post. Also if you can see any more efficient ways of doing what i am trying to do, please let me know!
-- Script to create a csv file of data from all tables inside current database
-- declare all variables declare @command varchar(200) -- command used for bcp declare @fetch_status int-- variable for fetch status in cursor
declare @TABLE varchar (200)-- Variable to hold table name declare @colcommand varchar (200)-- Variable to hold column creation command declare @count int-- Variable used to determine first itteration of Column loop declare @names varchar(100) -- variable used for the column names declare @delimiter varchar(10)-- variable used for delimiter in column names
SET @delimiter = ','-- set up the delimiter to comma select @count=0-- initialises the COUNT variable
-- setup cursor to create the bcp command to backup the data files to csv format declare bcpcommand cursor READ_ONLY FOR select 'exec master..xp_cmdshell' + ' ''' + 'bcp' + ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' out' + ' c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt' + ' -c -t,' + ' -T' + ' -S' + @@servername + ''''from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
-- setup cursor to pick up all the tables in the given database (used for column names section) declare dbtables cursor READ_ONLY FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
open bcpcommand
select @fetch_status=0
while @fetch_status=0 begin fetch next from bcpcommand into @COMMAND select @fetch_status=@@fetch_status if @fetch_status<>0 begin continue end
-- print 'Command to be run : ' + @COMMAND EXEC (@COMMAND) end
-- close and tidy up close runme deallocate runme
-- now create the fieldname files and then echo the 2 files together!
open dbtables select @fetch_status=0 while @fetch_status=0 begin fetch next from dbtables into @TABLE select @fetch_status=@@fetch_status
if @fetch_status<>0 begin continue end
SELECT @names = COALESCE(@names + @delimiter, '') + name FROM syscolumns where id = (select id from sysobjects where name=@TABLE)
-- due to the concatonation used, the second itteration onwards has a , attached to the front of the line -- this section removes the first char if @count <> 0 begin Select @names=SUBSTRING(@names,2,198) end
-- set up the echo command select @colcommand= 'exec master..xp_cmdshell' + ' ''' + 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt' + '''' from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE
-- print 'COMMAND : ' + @colcommand
exec (@colcommand)
-- reset @names variable for next itteration, and set count to 1 to trigger IF above select @names='' select @count=1 end
-- close and tidy up close dbtables deallocate dbtables
I'm working on my first data warehouse and I'm not sure how I should name the columns in the database.
The first phase of the data warehouse is to store a bunch of data from one third party source. The source contains over 100 pieces of data and the business user doesn't even know what some of the fields are but he wants to store everything. The third party refers to the each field with a somewhat cryptic short name and a longer description. The short name isn't always cryptic.
My question is am I better off naming my columns the same as the source system's short name so that I can easily debug problems later? Should I instead try to shorten their definition into something meaningful? On a side note, I'm 100% positive that we'll never populate the tables in questions with data from an additional source.
I am creating a report in SSRS which has the following criteria:
- Row 1 (parent) is 'Product'
- Row 2 (child) is 'Feed'
- Columns are date. I have 5 dates showing at any one time across the top. The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.
- Data is the number of records.
I have a sub total on the Product and the report is collapsed on Product as default.
What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates. So the difference between the date the user selected (@date parameter) and the day before that.
I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.
In ssrs how can i have a report showing closing value = Opening+Addition-Sale in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...
How do I get the values of a column from a table separated by a comma.
For example
Suppose I have a table with column Levels (below), I want the values of the corresponding column separated by a comma, so that I can use this in a different query to pull these values from a different table
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A LEFT JOIN #SqlStatement B ON A.spid = B.spid where B.Statement IN ( SELECT T.name, C.name FROM sys.tables T JOIN sys.columns C ON T.object_id=C.object_id WHERE T.type='U' )
Something like this : find the column names and tables name
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I created a ssis package which exports the data from oledb source to flat file (csv format). For this i have OLEDB source and Flat File as destination. I generate the file and filename dynamically with the column names in the first row. So if the dynamically generated file name already exists , then i want to append the data in the same existing file. But I dont want to append the column names again. I just want to append the rows to the existing rows.
so lets say first time i generate a file called File1_3132008.csv.
Col1, Col2 1,2 3,4
After some days if my ssis package generates the same file name i.e. File1_3132008.csv, this time i just want to append the rows to the existing file. So the file should look like this- Col1, Col21,23,45,67,8
But instead my file looks like this if i set Overwrite propery to false
Col1,Col2 1,2 3,4 Col1,Col2 5,6 7,8
Can anyone help me to get the file as shown in the highlighed
I work in a big project and we will begin in using reporting services as the base technique for reports and I will be responsible for this part. but I have a problem I will discuss in the following:
Currently: We use currently devexpress reports and we have 2 languages(Arabic and English). the data in tables saved in two ways (Arabic and English). when the end user change the language of the web site the report data language changed when run it.
Example:
we have table with (ID, NAME_AR, NAME_EN, JobTitle_AR, JobTitle_EN). designed report will display(ID, NAME_EN, JobTitle_EN) . but the end user change the language of the system the report will
I have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container (I guess!).
Post data import, I have a set of T-SQL query that I plan to execute via Execute SQL Task.
My requirement for the parameter is multivalue parameter with a text box. for example when user enters aa15 it need to include product aa15. when the user enters aa15, aa16, zz15 than it needs to include all the three products. the last case is when the user enters AA** than i need to inclued all the products start with AA. when i use default multivalue parameter with data source analytical services than i am getting a drop down box. I dont want that. I need a text box where user can enter the value. 1. In sql we have a like key word to query . for example select * from product where product like "AA%". what is equavalent mdx query to get such results ? 2.How to impliment the multivalue parameters without using dropdown box?
OLE DB source which calls a stored proc that returns a result set
data conversion
Excel destination I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
What am I missing or doing wrong? Thanks in advance
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
Hi I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides. Thanks
