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?
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.
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
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?
I am update/inserting records threw a web form in vb.net. I need to insert 'NULL' into my microsoft sql server database. I am not talking about the below line of code where website is the name of my paramater. If i do that it will just place a blank into that field in the database. If i dont enter anything into that textbox I want it to to say NULL in that field. So if I go into the actual table in the SQL Server Management Studio and look at the website field of the recored I just added or updated and did not type anything into the web site textbox it needs to say NULL. I also tried the second line of code but that places a single quote in front and behind NULL. So the field will have the value 'NULL'. website.value = txtwebsite.text.tostirng if txtwebsite.text ="" then website.value ="NULL" end if The reason why I need the NULL there is because I bind the website filed to a hyperlink template in a gridview. The actual text of the hyperlink is bound to PAYER which is a name of a insurance payer but the navigateto is bound to the website field. If i do not enter any data into the website field and it stays as NULL, then when my gridview is loaded, payers that dont have a website will not be underlined and user wont have the option to click on them since there is no value for the navigateto. But if i use my form to update/add a payer and leave the website textbox blank in puts a blank into that field in my database and when it loads that new row into the gridview the PAYER is underlined and u can click on it but it will just take u to the web site is unavailable page. So is there anyway to actaull have NULL placed into a filed and not just blank space
I 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>
I need to parse SQL statements directly and extract each segmentindividually. Is there a way reference the Microsoft SQL Parserdirectly from VB.Net?Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
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!
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.)
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
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.
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.
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)
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
Parser 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
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'.
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..
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.
I did a backup using SQL Server 2005 Express on 1 machine, and when I tried to restore to SQL Server 2005 Developer Edition (included with VS 2005 Pro), it gives me the following error: TITLE: Microsoft SQL Server Management Studio------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ADDITIONAL INFORMATION: The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476 ------------------------------BUTTONS: OK------------------------------ I looked at the link and it is talking about SQL Server 7 and 2000. I read a few posts about not being able to restore 2005 to 2000, but I have 2005 on both machines. I'm not sure the best way to check, but I know I installed the SQL Server 2005 Developer edition to the new machine recently. Any ideas? Thanks!
Hi Should ASP.Net 1.x work togheter with sql 2005 without problems? I have try to open a web project but I got the following error: Cannot open database "crm" requested by the login. The login failed. Login failed for user 'crmuser'. This crmuser is "promoted" to owner of the crm base, but still I got the problem. I upgrade this web project to ASP.Net 2.0 and I don't have the login problems, that's why I'm wondring. Hope someone can answear me on this question. Thanks! Jan
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
I have the code below to text. Unfortunately, the result I keep getting when I run my query does not make sense to me. Can anyone please help me ? Thanks in advance DECLARE @TOtalPoints AS INTEGER DECLARE @TOtalPointsFinal AS INTEGER SET @TOtalPoints = CONVERT( INTEGER, (SELECT COALESCE(SUM(AMOUNT),0) FROM Transactions WHERE USERID = 4337011 AND Amount < 20 AND UPPER([Status]) ='SUCCESS' )) SELECT @TOtalPointsFinal = @TotalPoints%(CONVERT(INTEGER, 20)) PRINT @TOtalPoints PRINT @TOtalPointsFinal
When supporting interactive grouping in reports using an execution snapshot, In 2000 I used:
<UsedInQuery>False</UsedInQuery> in the RDL below the appopriate Report Paramater names as well as using stored procedure based reports with Prompts set to certain paramaters. These prompted parameters would still show up after an excecution snapshot was run with default parameters. The UsedInQuery was just used to determine if it would rerun vs database or not.
I'm trying to do the same in 2005 but can't seem to get it to work anymore.
Per books online:
"The UsedInQuery element must contain one of the following string values:
True False Auto
You use the UsedInQuery to determine whether queries containing parameters that are associated with the report parameter are executed if the report parameter changes. This impacts whether the parameter can be changed in snapshot reports. Setting the value to Auto indicates that the report server attempts to detect whether the parameter is used in a query. "
Any suggestions on how to get interactive grouping to work with execution snapshots based on stored procedure reports in 2005? In 2005, the prompt Parameters are showing up as not enabled even though I've used false for UsedInQuery when I base the report from stored proc. It looks like they may work if the report is not based off of a store proc. I'll continue to test the query side to confirm the problem is only with stored proc based reports. Thanks!
I have the same error occuring each day at the same exact time on my mirror server. I looked online, and there are tons of references online to people having this issue, but not so many with resolutions. The server is identical to its Primary server in every aspect. It has the same patches, same exact hardware, it was originally going to be a cluster until some other issues turned it into a mirror set, and the Primary doesn't have this issue, just the mirror which is why it doesn't make sense.
Everyday between 17:56:30 and 17:58:30 I get this error
Date 4/6/2008 5:57:56 PM Log SQL Server (Current - 4/7/2008 12:00:00 AM) Source spid1s Message A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 2361601 seconds. Working set (KB): 147436, committed (KB): 461280, memory utilization: 31%.
There is no plausible reason for this to occur on this particular server and not on the Primary which is doing all the work. I read the blog on PSS about this, as well as some of the MVP blogs. Is there anything else I can look at for this? Some of the existing references I have looked at are below.
http://support.microsoft.com/kb/918483/en-us No matter what I do the problem persists. I gave up on this a while back since it was a mirror only, and I was busy working on other projects, but now that I have a bit of time to look at it again I am going to. Any one got any ideas?
I wrote some views in sql2000 and all was well. I upgraded to sql 2005 and non of the order by clauses work when I run the views directly. If I open the view up in desing mode and run them, they work. Any Ideas? Thanks, Rick
