Dynamic SQL On Cross-Tab W/ Varying Col Count
Feb 25, 2008
Hi, I have this idea of using Dynamic SQL on a cross-tab and the following questions popped-up:
1) Is there a limit to the length of the statement the EXEC / sp_executesql command can execute?
2) Should I use cursors (Haven't used this one, I preferred work tables)?
Here are the details:
TABLE_SAMPLE
RptDate DATETIME,
SomeField CHAR(1),
ID CHAR(8),
RetVal VARCHAR(15)
SP Input:
A date range that defines what the RptDates are:
@DateFrom DATETIME,
@DateTo DATETIME
SP Output could be:
| SomeField | RptDate1 | RptDate2 | RptDate3 | RptDate4 | RptDate5 | RptDate..n
--------------------------------------------------------------------------------------------------
ID1 | A | RetVal1 | RetVal2 | RetVal3 | [BLANK] | RetVal5 | RetVal..n
ID2 | A | [BLANK] | ... | ... | ... | ... | ...
ID3 | C | [BLANK] | ... | [BLANK] | ... | ... | [BLANK]
ID..n | B | ... | ... | ... | ... | ... | RetVal..n
[BLANK] - means that there was no record for that RptDate for the ID
Conditions:
- The number of records per ID varies and depends on the number of RptDates covered by @DateFrom and @DateTo
ID1 can have records for 02/26/2008, 02/25/2008, 02/22/2008
ID2 can have records for one, two or all of the same dates.
etc...
Thanks.
View 5 Replies
ADVERTISEMENT
May 13, 2008
Hello,
I am doing a report that uses paging and in order to optimize it, i used row_number() so i could make it return x rows per page, so, in order to compute the number of pages needed, i have to count the total number of rows, which gets very slow because i'm using a cross apply with a table-valued function. Is there any way so i can get the number of rows processed by row_number() so i dont have the need to do count?
Thanks in advance !
View 3 Replies
View Related
Jul 20, 2005
I am using the Dynamic Cross-Tab code supplied in an article from SQLServer Magazine (http://www.winnetmag.com/SQLServer/...608/15608.html).I modified the script to generate a temp table inside the storedprocedure, and then use this temp table as the source for thecross-tab. However, the problem seems to be that the dynamic SQLstring generated by the script is longer than what can be stored inthe @SQL variable. The Cross-tab works great, so long as the amount ofdata to be pivoted is small.Is there any way around this? E.g. a User defined type, or anotherdata type which can store more characters?Thanks,TimCREATE procedure CBN_CrossTab@StudyID varchar(100), --Model ID passed from web app - Only one modelcan be selected@Level int --The level to which the taxonomy should be rolled upAsDECLARE@Table as sysname, --Table to crosstab@OnRows as nvarchar(128), --Groupuing key values (on rows)@OnRowsAlias as sysname, --Alias for grouping cloumn@OnCols as nvarchar(128), --destination columns (on columns)@SumCol as sysname, --data cels@SQL AS varchar(8000), -- String to hold generated SQL String@NEWLINE as char(1) --Holds the New Line Character for the codeSET @OnRowsAlias = NullSET @SumCol = NullSET @NEWLINE = CHAR(10)-- Generate the Temp table for the taxa and countsCREATE TABLE #RefOrganisms (sampleid int, txtTaxa varchar(75),fltCount float)INSERT INTO #RefOrganisms(sampleid, txtTaxa, fltCount)SELECT dbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ") AS Taxa,SUM(dbo.tblbenthic.[count] /dbo.tblsitedetail.numberofreps) AS SumCountFROM dbo.tblstudylist INNER JOINdbo.tblsite ON dbo.tblstudylist.studyid =dbo.tblsite.study_id INNER JOINdbo.tblsitedetail ON dbo.tblsite.siteid =dbo.tblsitedetail.site_id INNER JOINdbo.tblsampledata ONdbo.tblsitedetail.sitedetailsid = dbo.tblsampledata.sitedetails_idINNER JOINdbo.tblbenthic ON dbo.tblsampledata.sampleid =dbo.tblbenthic.sample_id INNER JOINdbo.iter_intlist_to_table(@StudyID) i ONdbo.tblstudylist.studyid = i.number INNER JOINdbo.tblbenthictaxa ON dbo.tblbenthic.organism_tsn =dbo.tblbenthictaxa.tsnWHERE (dbo.tblsampledata.qaqc = 0) AND (dbo.tblsampledata.status =2) AND (dbo.tblbenthictaxa.rank_id >= @Level)GROUP BYdbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ")-- Identify the Temp table info for the CrossTabSELECT @Table = '#RefOrganisms'SELECT @OnRows = 'sampleid'SELECT @OnCols = 'txtTaxa'SELECT @OnRowsAlias = NullSELECT @SumCol = 'fltCount'--STEP1 BEGININNING OF SQL STRINGSET @sql = 'SELECT'+ @newline +' '+ @onrows +CASEWHEN @ONROWSALIAS IS NOT NULL THEN ' AS ' + @ONROWSALIASELSE ''ENDCREATE TABLE #KEYS(KEYVALUE NVARCHAR(100)NOT NULL PRIMARY KEY)DECLARE @KEYSSQL AS VARCHAR (1000)SET @KEYSSQL = 'INSERT INTO #KEYS ' + 'SELECT DISTINCT CAST(' +@ONCOLS + 'AS NVARCHAR(100)) ' + 'FROM ' + @TABLEEXEC (@KEYSSQL)DECLARE @KEY AS NVARCHAR(100)SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHILE @KEY IS NOT NULLBEGINSET @SQL = @SQL + ' ,'+ @NEWLINE +' SUM(CASE CAST(' + @ONCOLS +' AS NVARCHAR(100))' + @NEWLINE +' WHEN N''' + @KEY +''' THEN '+ CASEWHEN @SUMCOL IS NULL THEN '1'ELSE @SUMCOLEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS [' + @KEY + ']'SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHERE KEYVALUE > @KEYENDSET @SQL = @SQL + @NEWLINE +'FROM ' + @TABLE + @NEWLINE +'GROUP BY ' + @ONROWS + @NEWLINE +'ORDER BY ' + @ONROWSPRINT @SQL --+ @NEWLINE --FOR DEBUGEXEC (@SQL)GO
View 1 Replies
View Related
Jun 17, 2014
See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL
[Code] .....
View 9 Replies
View Related
Apr 29, 2008
Hi all,
I'm using sql 2005. Can some one please tell me how to write dynamic sql for count. What i want is that i want to count the number of employees existing for the given department names and get the counted values as output into my vb.net 2.0 project. If any one know who to write this please send the code.. Please help me.. I want the below code to change to dynamic sql:
Alter proc GetCountforemp
@DestName varchar(200)=null,
@total int output
as
begin
SELECT @total = Count(distinct Employee.EmployeeID) FROM Employee
INNER JOIN Dest R on R.DestID=Employee.DestID
WHERE R.DestName in ('''+@DestName+''')
end
View 1 Replies
View Related
Aug 18, 2005
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
View 1 Replies
View Related
Jul 22, 2014
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ
View 7 Replies
View Related
Nov 6, 2006
Hello,
I want to write a function that takes varying number of parameters.I mean:
Fonk(4,5,7)
Fonk(2)
Fonk(6,6,6,8)
There is a way to do this in T-SQL?
Thanks in advance...
View 5 Replies
View Related
Nov 28, 2006
Hello
I need an advice for an easy maintainable and highly flexible solution using SSIS. We're supplying our customers with an exportservice. The data is extracted from different tables, all with the same tableschema, fairly easy to create a foreach-container and iterate through the tablenames and extract data and use a flatfile destination to write it to...
But the not so easy part is to have different exportformats and still be using only one package. I could maybe use different ConnectionManagers for the FlatFileDestination, but that's not a very easy maintainable solution in my world. Our Customers will soon demand a webinterface where they can select necessary columns and apply different formatting for example they would demand the datetime to be '2006-12-24' or maybe '12/24/2006' etc. for alot of the available columns.
Any good suggestion on how to accomplish such a task?
Kind regards
View 1 Replies
View Related
Oct 5, 2000
I receive several TXT files daily that need to update information in SQL Server databases. The process requires that all TXT files be appended to a master file and also update individual files' information based on the TXT file name. For example:
File TABLE1_x_ddmmyy.TXT (ddmmyy = date, x = "O" or "B") is to be appended to the master file and also update SQL table "TABLE1" by setting a flag for those records in the table that match a unique key that is provided in the TABLE1.TXT file.
In VFP, I had the following process in place:
a) open the TXT file.
b) read its file name and open the corresponding VFP file
c) update the VFP file based on the key provided in TXT
d) append the key to the master file.
e) repeat c-d for next record in TXT
f) repeat c-e for next TXT file
Using the same process with ADO takes a considerable time since I am processing one line at a time.
Is there any way to do this using a DTS package of some sort? How can I read the TXT file names in SQL Server?
Thank you.
View 3 Replies
View Related
Jun 2, 2001
re: [Windows 2000 SP1, SQL Server 7.0 SP2]
I am developing an online web-based address book for multiple users. There are STANDARD FIELDS and CUSTOM FIELDS.
Standard fields include: Name,Street,City,State,Zip.
Custom fields are those defined by a specific user. For example:
User-A Custom fields:
Interest Rate <real>
Loan Amount <currency>
Start date <date>
User-B Custom fields:
Blood type <char 3>
Date of birth <date>
Referred by <varchar 50>
Different users can have different custom fields in their address book. As you can see, while the standard fields for each user can be
stored in a single table. However, I have several methods by which I can store the CUSTOM fields.
------------------------------------------------
Method 1: Create 2 separate tables called CustomField and CustomValue:
CustomField has fields:
FieldID <int>
FieldName <varchar 25>
UserID <int>
CustomValue has fields:
ValueID <int>
Value <varchar 50>
FieldID <int>
------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...
CustomField has fields:
FieldID <int>
FieldName <varchar 25>
FieldType <smallint> (determines which TABLE, below, contains the data)
UserID <int>
CustomCharValue
CharValueID <int>
IntValue <Varchar 50>
FieldID <int>
CustomIntValue
IntValueID <int>
IntValue <int>
FieldID <int>
etc....etc...
The structures of those tables would be similar to Method 1, but the data would be segregated based on their data type.
--------------------------------------------------
I'm thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I'm going
to assume that I'll have at least 1-5 million records to work with over the course of my first year and I will need the ability to sort
records based on values in the custom fields as well.
My first question is: Which method should I be considering and is there an alternative or hybrid that I should be considering?
My second question is: What statements should I use in my stored procedure that will enable me to retrieve a list of USERID, CustomFieldIDs and their values as one resulting table that I can query at will and with solid performance?
Gregory
email: sqlGuy@clubtel.com
View 1 Replies
View Related
Jun 6, 2007
Hi Everyone,
I'm trying to create a DTS package that will let me import an Excel file. The user will be able to name the file the same name every time. But can the DTS package read a different worksheet name each time? Right now, if I use the Excel connection object in DTS designer, it wants to hard code the worksheet name.
Thanks,
Eric
View 1 Replies
View Related
May 18, 2007
Is it possible to set up the permissions to not allow a specific user to create schemas, but to allow that user to create tables and procedures and functions in one schema, and to create procedures and functions but not tables, in a different schema within the same database?
View 3 Replies
View Related
Nov 13, 2007
Hello All,
I have a requirement, where the number of parameters being to a stored procedure, is not fixed. It is to have a list of computers, belonging to a particular Domain, or, more DOMAINS or maybe just a list irrespective of the Domain. For this, the @Domain parameter, could have one value, or more values, or no values as well. Can you please let me know how do I go about this?
Thanks a lot.
Mannu.
View 3 Replies
View Related
May 31, 2007
I'm brand-spankin'-new to SSIS, so I hope this is a simple, easy problem to fix, but I need everything in one-syllable words, thanks.
I have an Execute SQL task attempting to run a stored procedure:
exec risp_extract_pos_direct_data_competetive_test ?
(the SP has two parameters; I get the same results with two question marks. The connection is an OLE DB connection.)
I have two input variables mapped to the Execute SQL task, User::company_sysno (GUID) and User:: start_period_sysno (LONG). Depending on the iteration, I also have a returnvalue variable mapped, User::ErrorCode (I have tried every data type I can think of). The stored procedure does contain a RETURN @m_intErrorCode line, but the behavior I'm getting does not change if this line is commented out.
When the ResultSet is set to "none," I get the following error:
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
When the ResultSet is set to either SingleRow or FullResultSet, I get the following, somewhat different, error:
There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
(Obviously, change the last bot of that to "FullResultSet" when FullResultSet is selected.)
I've found a handful of webpages dealing with each of those errors separately, but have not discovered one that deals with them in tandem, so I'm not even sure where to start looking. For that matter, I'm not even sure what else I need to tell you to give you an idea of what's going on. I'll post the stored procedure if necessary, but it's rather long, and my company are a tad paranoid about releasing code into the wild.
Thanks.
View 6 Replies
View Related
Oct 17, 2007
Hello! Given three dimensions in my cube (Product, Calendar, & Sales). I'm trying to create a calculated measure that will sum 8 weeks of sales starting from the third week in which sales started. For instance, weeks 1-4 had zero sales. Week 5 is the first appearance of any sales. Therefore, the measure would sum the sales of weeks 7-14 (8 weeks starting with the 3rd week after sales begin).
Any ideas?? Keep in mind that every product is going to be different (sales start in different weeks).
Thanks in advance for ANY help! Lance
View 1 Replies
View Related
Jun 2, 2001
I am developing an online web-based address book for multiple users. There are STANDARD FIELDS and CUSTOM FIELDS.
Standard fields include: Name,Street,City,State,Zip.
Custom fields are those defined by a specific user. For example:
User-A Custom fields:
Interest Rate <real>
Loan Amount <currency>
Start date <date>
User-B Custom fields:
Blood type <char 3>
Date of birth <date>
Referred by <varchar 50>
Different users can have different custom fields in their address book. As you can see, while the standard fields for each user can be
stored in a single table. However, I have several methods by which I can store the CUSTOM fields.
------------------------------------------------
Method 1: Create 2 separate tables called CustomField and CustomValue:
CustomField has fields:
FieldID <int>
FieldName <varchar 25>
UserID <int>
CustomValue has fields:
ValueID <int>
Value <varchar 50>
FieldID <int>
------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...
CustomField has fields:
FieldID <int>
FieldName <varchar 25>
FieldType <smallint> (determines which TABLE, below, contains the data)
UserID <int>
CustomCharValue
CharValueID <int>
IntValue <Varchar 50>
FieldID <int>
CustomIntValue
IntValueID <int>
IntValue <int>
FieldID <int>
etc....etc...
The structures of those tables would be similar to Method 1, but the data would be segregated based on their data type.
--------------------------------------------------
I'm thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I'm going
to assume that I'll have at least 1-5 million records to work with over the course of my first year and I will need the ability to sort
records based on values in the custom fields as well.
My first question is: Which method should I be considering and is there an alternative or hybrid that I should be considering?
My second question is: What statements should I use in my stored procedure that will enable me to
retrieve a list of USERID, CustomFieldIDs and their values as one resulting table that I can query at will and with solid performance?
Gregory
email: sqlGuy@clubtel.com
View 1 Replies
View Related
May 27, 2014
I have a large SQL 2012 table containing survey details. The number of questions vary in each survey and can range in number from as little as 10 questions to a maximum of 50.I need to adapt my crosstab code below to include a CASE statement that outputs a column (Q1, Q2, Q3 etc) representing the questions up to a maximum 50 questions (Q50) and to place the answer under the corresponding question column within each survey. Ideally I want to avoid having to write 50 CASE statements in my code. I chose the CASE statement method as I understand that the PIVOT option isn't as flexible,I have included some test data and the output should look like this:
SURVEY_REFQ1Q2Q3Q4Q5ETCETC
100 AnswerAnswerAnswerNULLNULL
200 AnswerAnswerAnswerAnswerAnswer
300 AnswerAnswerNULLNULLNULL
[code]....
View 3 Replies
View Related
Nov 7, 2007
True story. Thanks. Dont think that fixed or ragged is going to work for me though. File does not seem to be structured.
I have what appears to be a fixed length file that I would like to import using SSIS. However it does not look loike the spacing between fields is constant. Is there a way to say from character 1 to 10 is X, and 11 to 15 is Y, etc etc? Below is a couple of rows that all pertain to the same record.
01000000225672101242253 55232101242253 5TKTT / 0101 FFVV 5235MCYSNR/CE MUSSETT/BRIAN.A.MR 20071017U7700002277000022 U00000000ZAJNB JNB ZA 00000000 00000000 00000000 SITII Y06 405
02000000235672101242253 55232101242253 5ICE 20071017 1407000017OCT 17OCT
03000000245672101242253 55232101242253 5 PLZPLZ ZAR 1000 ZAR 72ZAZAR 140ZVZAR 562XTZAR 1774 ADT PENALTY APPLIES
04000000255672101242253 55232101242253 50000017740000000100000ZAR200000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 000000000000000000000000000000000ZA 00000007200ZV 00000014000EV 00000001600YR 00000054600 00000000000 00000000000 00000000000 00000000000 00000000000
05000000265672101242253 55232101242253 51 OPLZ DUR CE CE 0633 0633 K K 18OCT18OCT18OCT1625 1740 OK KOW 2 DUR PLZ CE CE 0602 0602 B B 20OCT20OCT20OCT1000 1115 OK BOW
07000000275672101242253 55232101242253 5CCIK5221 000001774005221591015192982 0308M067781 00000000000 00000000000 00000000000
08000000285672101242253 55232101242253 509PLZ CE DUR550CE PLZ450ZAR1000 END CE XT16EV546YR 1 2 3 4
What do you reckon is the best option? SUBSTRING in a Dervied Column? Below is an example of the text.
View 5 Replies
View Related
Jan 31, 2008
I would like to fail a package depending on the error. The package extracts data from Excel files. I would like to continue processing if an Excel file is badly formatted, but stop processing if there is a serious issue. like the file server hosting the Excel files crashed.
I was thinking about dynamically changing the MaxeErrorCount property based on the Error ID or description.
Any ideas on an intelligent/simple way to do this
View 1 Replies
View Related
Aug 19, 2014
I'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.The fields are pretty straight forward, only the display is where I have a question.
Fields Used: PERIOD ('MON-yyyy') and VALUE
The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))
This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12)).Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?
View 1 Replies
View Related
Oct 23, 2014
I get error reports in simple text files like the one below in relatively the same format. The only thing that varies is the number error reasons as there can be any number of error reasons for a file. Usually there is only one but there can be a handful. What is the best way to capture the error description and count of errors no matter how many there are? I want to take these items and update a table I have in sql server 2008r.
Report Message example:
************************************************** *****
Original File Name: some.file.YYYYMMDD.d.incr.02of02.1.dat
Source File ID: file02YYYYMMDD
File Receipt Date: 10/17/2014
Total records received: 1331136
Total records loaded: 1329987
ERROR REASONS
Error code: EBBW002 Error desc: Duplicate Record Total records: 1146
Error code: EABC001 Error desc: Invalid Length Record Total records: 1
Error code: ERRCM10 Error desc: Missing First Name Total records: 2
Total number of Errors encountered during the ODS update processing: 1149
************************************************** *****
View 2 Replies
View Related
Feb 10, 2014
I need to import data to a MSSql table from massive (read: a million and a half rows, every single day) logs that come in .txt format separated in tabs with a ";" symbol and then have some stored procedures analyze that data to generate some reports in an excel file with that info. The text files include the column headers in the first row and the data starts on the second one.
The challenge is that the text files differ in column order and count every single day.
The analysis that I need to do only needs about 15 columns from the nearly 90-120 that those files include, and those columns sadly happen to be in a different order in those files.
View 8 Replies
View Related
Jun 20, 2007
I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.
Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);
this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;
}
}
protected void btnViewRpt_Click(object sender, EventArgs e)
{
ReportParameter[] rptParams = GetReportParameters();
this.rptViewer.ServerReport.SetParameters(rptParams);
this.rptViewer.ServerReport.Refresh();
}
private ReportParameter[] GetReportParameters()
{
int paramCount = 0;
ReportParameter[] retVal;
string emptyVal = null;
if (txtName.Text != "") paramCount++;
if (txtAddress.Text != "") paramCount++;
if (txtZip.Text != "") paramCount++;
retVal = new ReportParameter[paramCount];
paramCount = 0;
if (txtName.Text != "")
retVal[paramCount++] = new ReportParameter("Name", txtName.Text);
if (txtAddress.Text != "")
retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);
if (txtZip.Text != "")
retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);
return retVal;
}
The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.
The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.
So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.
Any suggestions?
View 7 Replies
View Related
Apr 24, 2008
How do i import a Varying Column Width Flat file into a Table using SSIS?
I have a flat file that has 4 columns with varying width
Like I should read the file as
Col 1 - (1 to 10 Characters)
Col 2 - (12 to 21 Characters)
Col 3 - (22 to 35 Characters)
Col 4 - (36 to 38 Characters)
At the end of the record is a "LF"
I think "Fixed Width" Columns allow me to define a standard column length for all the columns.. Right?
Any thoughts on how to?
View 9 Replies
View Related
Aug 6, 2006
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 5 Replies
View Related
May 11, 2015
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies
View Related
Sep 9, 2015
I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.
I have performed the following tasks **
1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)
3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.
4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.
What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.
View 5 Replies
View Related
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
View 3 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Jul 3, 2013
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
View 9 Replies
View Related
