I need to parse SQL statements directly and extract each segment
individually. Is there a way reference the Microsoft SQL Parser
directly from VB.Net?
Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
Anybody dealt with EDI parsing/composing?
View 7 Replies View RelatedHi,
Is there any built-in SQL parser available in .NET framework?
I need such a function that when clients send me a SQL statement, I can parse what Tables are within the SQL, parameters, where condition, etc.
Thanks in advance.
Ricky.
I'm getting an error in a Execute SQL command task that returns XML. The error is: An invalid character was found in text content. I traced the error to this article:
http://support.microsoft.com/kb/238833
My question is how would I go about changing the encoding scheme? I've been able to run this package successfully on our dev SSIS box, but it's failing in prod. I suspect because the encoding schemes are different. Where do I go to verify this? How would I change it?
Thanks,
Phil
Does anyone know of a good XML parser where I can map out tables and fields in an SQL 6.5 database and have that information written out XML format?
View 1 Replies View RelatedI get a parser error on a particular page that reads:
Server Error in '/' Application.
Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.
Parser Error Message: The base class includes the field 'LongDescription', but its type (FreeTextBoxControls.FreeTextBox) is not compatible with the type of control (System.Web.UI.HtmlControls.HtmlTextArea).
Source Error:
Line 35: <td class="content" align="right" valign="top">Long
Line 36: Description: </td>
Line 37: <td class="content" align="left" colSpan="3"><textarea id="LongDescription" runat="server" rows="4" class="Content" cols="50"></textarea></td>
Line 38: <TD class="ContentTable" width="1"><IMG src="images/clear.gif" width="1"></TD>
Line 39: </TR>
Source File: *sslmanagementControlsProductDetailsControl.ascx Line: 37
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300
If I comapare the code with the equivalant page in another working instance they are line for line the same.
Hi every one,
I am having a problem with SSIS. i want to two email addresses to the dts via sql server, ex:
select @strSendTo = 'jsilva@pt.sss.com;vcarreira@pt.sss.com'
select @getdisp = 'dtexec /f "'+ bas.dbo.PathName('SSIS') + 'ProjectsXPNSSISANFSSISANFgetTestes.dtsx" /set Package.Variables[SendTo].Value;"' + @strSendTo + '" /set Package.Variables[vezes].Value;"33"'
exec xp_cmdshell @getdisp
but it returns an error saying:
Argument ""Package.Variables[SendTo].Value;jsilva@pt.imshealth.com;vcarreira@pt.imshealth.com"" for option "set" is not valid.
Can u help me plz.
Thanks.
Looking for TSQL stored procedure code to parse a single column's value into pieces (each piece stored in a seperate variable) for a subsequent update of other columns on this row -- (generically):
1. Declare/Open cursor on TBLx where COOKIES like '%CLIENT1=' or like '%CLIENT2=%' or like '%CLIENT3=%'
2. Fetch row; initiate 3 Loops to locate the trailing values of CLIENT1=nn, CLIENT2=nn,CLIENT3=nn (eg. CLIENT1=3243, CLIENT2=11, CLIENT3=4451)
3. Save the 3 values in variables (eg @VARclient1=3245, @VARclient2=11, @VARclient3=4451)
4. UPDATE where current of cursor.. SET COL1=@VARclient1, COL2=@VARclient2, col3=@VARclient3)
Caveat.. in step 2 above, the various CLIENT strings can be listed in various orders in any various start positions w/in the COOKIEs column)
(eg. CLIENT3=732,CLIENT1=9,CLIENT2=32 ----- starting in a variable position)
I'm familiar w/ the CHARINDEX feature... Any smaple code would be greatly appreciated.. thx in advance!
BT (william.t.turner@ceridian.com)
Back last October Paul DB mentioned the MS Log Parser tool and that he was using it for Exchange Transaction Logs.
I'm not an SQL person myself and would dearly love a little help to get started. Paul, I would be most grateful if you could post/send me even one example of the command line you use to get info out of Exchange T Log files. (I'm using 5.5 at present.)
Much thanks in advance,
Babylon Burns.
When I'm in query analyzer writing a stored procedure, if I make a mistake it will frequently go on and create the procedure without any warnings...I am on 9.00.3042.00, developer edition when this happens.
i.e., i had a stored procedure that had input parameters, and I declared a parameter @xyz nvarchar=NULL. It created the stored procedure anyway, and parsed it correctly as well. In fact, clicking the little 'parse' checkmark doesn't appear to do anything at all...
I didn't find out about my error until I ran the script in a 2000 database.
Is this a known defect? Will there be a patch? Is there a setting I don't yet know about to fix this?
Informatica has an XML parser component that allows me to read an xml file from a data source (Oracle Clob attribute in table in this case), parse it out in our mapping, and then transform the parsed date.
Does anyone know if SSIS has similar functionality?
Flow:
DataSource --> XML Parser --> Expression Component (Transform) --> DataTarget
Thanks
Scott
Can anyone tell me why my syntax is wrong and a fix? Obviously it's the PERIODICITY_HINT, but I'm doing what the BOL says...
ALTER MINING STRUCTURE [zzz]
ADD MINING MODEL [xxx]
(
...
)
Using Microsoft_Time_Series (
PERIODICITY_HINT = {12},
AUTO_DETECT_PERIODICITY=0.1
)
I get this error:
Query (9, 24) Parser: The syntax for '{' is incorrect.
Hi Guys,
I have a script task that is supposed to read and parse a fixed width source file.
Basically, I want to make the FieldWidths dynamic so that I'll be able to reuse this package with different files. So Instead of hardcoding the field widths directly into my script task, I want it to be stored somewhere that the package can get when executions starts. Is there a way of doing this?
The code looks like this:
Using Reader As New TextFieldParser(mTempFilePAth)
Reader.TextFieldType = FieldType.FixedWidth
Reader.SetFieldWidths(1, 8, 8, 8, 4, 8) <-- I want to change this to handle dynamic widths.
Hi All,
I€™ve been battling with a client who€™s supplied us with what they consider to be a legitimate CSV file. 1st off let me say that I understand that there€™s no CSV €œspecification€? per say, but here€™s the situation regardless.
The client has a test string in one of their fields that looks something like this:
He said "STOP" so, of course he stopped
The CSV best practice requires that you double quote, so a valid CSV filed would look like this:
He said ""STOP"" so, of course he stopped
Once this is placed into a comma delimited CSV it looks something like this
"Col1","Col2"
"He said ""STOP"" so, of course he stopped","value in col2"
---------------------
So the problem here is that the client saves the above as a CSV, opens it in Excel and say - Look, Excel deals with this just fine €“ why can€™t you handle it?
Trying to explain to the client that SSIS can€™t deal with a field that has embedded commas in it but that Excel can is quite honestly a little embarrassing (especially considering the const difference between the two).
It seem that having embedded quotes in the filed is fine, but that as soon as you have an embedded comma €“ SSIS can€™t handle it, yet Excel can.
---------------------
That said €“ I€™ve also read quite a few posts where people flame the original poster saying €¦ change your delimiter. That€™s all good and well when you€™re the one generating the CSV but when your client knows hinks they€™re generating a legit CSV (according the Excel and quite a few other CSV parsers) it€™s not a pleasant argument €“ especially when you know that asking them to make this change is going to take a few weeks of your project timeline.
I know I€™m not the 1st person to experience the problem, but I did want to see if I could get a straight answer as to why the Excel CSV Parser would dela with this situation but the SSIS parser would not.
Thanks
Warrick
I can not get this query to work can some on help me?
SELECT count(JOBID) AS 'transcount', count(distinct patientid) AS 'patient count', sum(TRANSPORTATION_TCOST) AS 'tcost,sum(TRANSPORTATION_DISC_COST) as ' dtcost ',avg(TRANSPORTATION_DISC) as "avgTDisc",(sum(transportation_tcost) + sum(TRANSPORTATION_DISC_COST)) as "TGrossAMTBilled', (sum(transportation_tcost) / count(DISTINCT patientid)) AS 'PatAvgT', sum(TRANSPORTATION_DISC) AS 'avgPercentDiscT' job.JURSDICTIONFROM job LEFT JOIN payer ON payer.payerid = job.payerid LEFT JOIN states ON job.jurisdiction = states.initialsWHERE (job.transportation = '1') AND ((job.datedcreated = @startdate) AND (job.datecreated = @enddate)) AND states.region = 'GA'GROUP BY job.JURISDICTIONORDER BY PatAvgT DESC
This is a question for anyone familiar with log parser. I use log parser to insert the IIS log information into our SQL database. It has been working great but we have now run into 1 problem.
I have been using a SELECT * statement before to put all the IIS log info into a SQL table and it works fine. I want to add 1 extra column to the SQL table though (a primary key). It wont let me use the select * statement anymore because the fields do not match. I even tried specifying the fields one by one but it will not work because there is a different amount of columns now.
What I did was I modified the table already there with a new column (RecordID which is a auto-increment primary key). Can someone please give me advice on how I should persue this? Keep the table with the new design and input the data differently, or somehow specify this new primary key column in the logparser statement.
OLD COMMAND
logparser.exe "SELECT * INTO tblIISLog_NEW FROM \Hamweb001LogFilesW3SVC85298408*.log" -o:SQL -server:hamdev005 -database:IISLOG_REPORTS -driver:"SQL Server" -createTable:ON -i:IISW3C -iCheckPoint:d:checkpointweb.lpc>>checkpointweblog.txt
NEW COMMAND I TRIED
logparser.exe "SELECT LogFilename, LogRow, date, time, c-ip, cs-username, s-sitename, s-computername, s-ip, s-port, cs-method, cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status, sc-bytes, cs-bytes, time-taken, cs-version, cs-host, cs(User-Agent), cs(Cookie), cs(Referer), s-event, s-process-type, s-user-time, s-kernel-time, s-page-faults, s-total-procs, s-active-procs, s-stopped-procs INTO tblIISLog_NEW FROM \Hamweb001LogFilesW3SVC85298408*.log" -o:SQL -server:hamdev005 -database:IISLOG_REPORTS -driver:"SQL Server" -createTable:ON -i:IISW3C -iCheckPoint:d:checkpointweb.lpc>>checkpointweblog.txt
Errors I get now:
1) Number of columns in table 'tblIISLog_NEW' (33) is different than number of columns in SELECT clause (32)
2) SQL table column "RecordID" data type is not compatible with SELECT clause item "LogFilename" (type STRING)
Thank you
I have a text field that contains abstract information formated inHTML, I'd like strip the HTML and insert the data in another Textfield within a DTS package. Is this possible?any suggestions would be appreciatedMatt
View 1 Replies View RelatedParser for XMLA (XML for Analysis): Timeout geting XMLA-Request (XML for Analysis).
Error when executing the administrated stored procedure 'GetItemsets': Exception has been thrown by the target of an invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.
I get this Error in Miningmodel-Viewer, what to do? This occurs when I set the MAXIMUM_ITEMSET_COUNT to 1,500,000 .
Hi All,
I receive data in the form of xml file and it has one to many relationships. Below I am giving you the sample node and how it should be parsed. Please suggest whether this is possible in SSIS using XML Task if not pls suggest a fast and efficient method.
<Element1>
<SubElement1>S1</SubElement1>
<SubElement2>S2</SubElement2>
..
..
<SubElement20>
<Child1>c1</Child1>
<Child2>c2</Child2>
</SubElement20>
<SubElement20>
<Child1>c3</Child1>
<Child2>c4</Child2>
</SubElement20>
..
..
<SubElement25>
<SubElement25Child1>s25_One</SubElement25Child1>
</SubElement25>
<SubElement25>
<SubElement25Child1>s25_Two</SubElement25Child1>
</SubElement25>
</Element1>
The above one is a sample xml. I should get 8 rows for the above element. There are 2 elements with "SubElement20" and it has 2 childs each. So there are 4 such nodes. The element "SubElement25" occured 2 times. Now we need to multiply these two counts.i.e. 4 * 2 = 8.
The relationship in the xml is one to many.
After parsing the xml I should get the rows in the following format and it should be one to one mapping only.
S1, S2, ....c1,... s25_One
S1, S2, ....c1,... s25_Two
S1, S2, ....c2,... s25_One
S1, S2, ....c2,... s25_Two
S1, S2, ....c3,... s25_One
S1, S2, ....c3,... s25_Two
S1, S2, ....c4,... s25_One
S1, S2, ....c4,... s25_Two
The options are as follows
1) Writing a custom source component to parse this xml and produces the rows in the specified way.
2) First write a XSLT or Style Sheet to break the above one to many relationships to one to one relationships and create an xml file and then use native SSIS xml parser.
3) Using the Script Task as the source component and do the parsing. But I think it would be difficult to handle and maintain.
If any other suggessions please share. Thanks in advance.
Regards
Venkat.
Hi all,is there any tool which is capable to convert query initially written formsaccess database to query for mssql server.I have tons of queries which contains iif, trim and similar functions whichshould be converted to case, ltrim(rtrim(, etc. etc.Does anyone know for tool which could do that automatically?Thanks in advance,Anabella
View 1 Replies View Relatedhi,
when i try to execute the rpdataextraction parser, i am getting the following message - Unable to access site collection: User cannot be found.
it will be of immense help for me if any one helps me on this or directs me to right resource.
thanks
Hello All,
All of a sudden our reports stopped working today giving this error:
Server Error in '/Reports' Application.
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Security level already defined for 'RosettaMgr'.
Source Error:
Line 21: <httpRuntime executionTimeout="9000"/>Line 22: <securityPolicy>Line 23: <trustLevel name="RosettaMgr" policyFile="rsmgrpolicy.config"/>Line 24: </securityPolicy>Line 25: <trust level="RosettaMgr" originUrl=""/>
Source File: C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportManagerweb.config Line: 23
Version Information: Microsoft .NET Framework Version:1.1.4322.2407; ASP.NET Version:1.1.4322.2407
If I comment out the lines 22 - 25 in the web.config file the reports start to work again, but I am reluctant to do this as I'm not sure what the implications would be if left..
Does anybody have any suggestions??
Many Thanks
i am unable to use the Text Qualifer in SSIS package Flat file connection manager Editor, it says, "The flat file parser does not support embedding text qualifier in data",why is that?
it was supported nicely in DTS 2000. also I have no control on Source file TXT. so I can not eliminate the Text qualifer (") from the file.
any advices.
I'm sure I am not undestanding some basic concept here but the following formula always produces an invalid token error at the '-' sign. In this example, I'm trying to subtract out a specific month from the total (this is a simplified example, my actual formula needs to compute a % change over time using lag...)
This produces the invalid token error (it always errors at the '-' in the equation)
with member [Measures].[MyCalcMeasure] as [Measures].[MyBaseMeasure]-([Date Submitted].[Date Submitted YQMD].[month].&[2008]&[1],[Measures].[MyBaseMeasure])
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
But this works
with member [Measures].[MyCalcMeasure] as [Measures].[MyBaseMeasure]
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
As does this
with member [Measures].[MyCalcMeasure] as ([Date Submitted].[Date Submitted YQMD].[month].&[2008]&[1],[Measures].[MyBaseMeasure])
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
What am I missing?
I need help! When installing SQL Server 2005 Express on one machine, I get an error with the following summary.txt
Machine : xxx
Product: MSXML 6.0 Parser
Product version: 6,10,1129,0
Installed: Failed.
Log file: c:Program Files... (see below)
Error Number: 1625
in log file from above.
MainEngineThread is returning 1625
This installation is forbidden by system policy. Contact your system administrator.
The person trying to install has administration privileges. BTW: The computer shares a wireless internet connection with other computers in the office but the clients or not setup to share data. My user is installing SQL, the data, and app on a single client. The MS Sql Server 2005 express seems to install on all other boxes. Thanks for your time.
Zone: SQL Server 2005
Hi y'all
Does anybody know reference or pdf file or free e-learning on the web?
DECLARE Loan_cursor CURSOR FOR
SELECT Loan_No,store
FROM loan
WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'
OPEN Loan_cursor
-- Perform the first fetch.
FETCH NEXT FROM Loan_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @LoanNo varchar(12)
** Set @LoanNo = Loan_No
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Loan_cursor
END
CLOSE Loan_cursor
DEALLOCATE Loan_cursor
when l run the cursor l get the error
Server: Msg 207, Level 16, State 3, Line 15
Invalid column name 'Loan_No'.
.
If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error
Server: Msg 107, Level 16, State 3, Line 15
The column prefix 'loan' does not match with a table name or alias name used in the query.
All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word
Hi friends,
Just i modified one sub stored procedures which is not getting affected in the main stored procedures
Create procedure main
as
begin
set nocount on
--First sp
exec Data_transfer_sp
--Second sp
Exec Clearance_sp
set nocount off
end
In data_transfer_sp,i have commented the select statement
but still iam use to the select result while execution the main sp.
Note:I have compiled the data_transfer_sp after making comment
.Txs in advance
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTE more than once in my SP
am I trying to use the CTE in a way that was not intended?
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTW more than once in my SP
am I trying to use the CTE in a way that was not intended?
hi ,
can any body help me that how can i use my .net dll into my rdl file.
thanks
Gorla
in this code ,whtat is the references of
RelationalDataSourceView
and also
what are the references of these?
OleDbConnection
OleDbCommand
OleDbDataAdapter