I am facing a problem of invisible data. I have developed a ASP based application in my local laguage and data is entered from 8 clients. Now I have found that in a particular table in the SQL Server very few data is not visible if I see from the SQL query analyzer. But if I go the application and try to enter the data again it gives a message that the data already exist and next time if i see it from query analyzer the data is displayed.
I need your help on figuring out what is this invisible character in one of my data column.
The file is from IBM DB2 as text. I am comparing the data with the one in SQL server. There is one white space at the end of the column I couldn't get rid off.
The text strings from both side look idential except the DB2 one has a "space" at the end.
The len() show it's 13, while the sql one is 12. However, the rtrim()/ltrim() did not get rid of it.
This column is at the end of the row. So it could be CR or LF.
To use padindex or stuff functions, I need to know what is this stuff.
I am not sure why when the packages are run, the components dissapear. The only thing I see is the result in the output. So there is no visual on the tabs. Thanks
Recently found a situation which I can not figure out the cause. Wish somebody can help me here.
The scenario is as follows:
Server: SQL 7.0 with SP2 login name: DBA, standard SQL login, member of sysadmins DB Name: TEST
DBA is the owner of database TEST and a member of db_owners in the database. Using EM, if you look at Users in TEST database, the only entry you can find is DBA where both Names (user name Login Name) are the same: DBA. There's no entry for user dbo. (from BOL, dbo can not be deleted)
Run sp_helpuser gives out two rows. user login name dbo NULL DBA DBA
I don't know what happened to this machine before. Any clue about the possible cause is highly appreciated.
I am trying to locate the "My Reports" folder on the SSRS site, but I dont think I am able to see it. I am an admin on the box with Browser, Content Manager, My Reports roles.
I also tried creating the folder manually and it says the folder exists but I cant see it.
I have also checked on the "Site Settings" and checked the box "Enable My Reports". What else am I missing here?
Is there anyway I could run an Audit trace on SQl Server which records SQL Server System Admin Login/Logout , failed login and machine names I don't want the trace window to show on screen however would like a file generated for later viewing. Also due to Firewall issues we have, We don't have SQL tools enabled to connect to that server.
Can I create invisible table in SQL Server 2005? Access(all versions) has this option if you add Usys_TableName. The table will be invisible, but you can get data from it
When defaulted to Visible or Invisible, +/- displays correctly. But when using the Expression, + is always showing as the default even when a group is expanded. Is this expected? TIA.
Publisher: SQL Server 2000 SP4 Subscriber: SQL Server 2005 Mobile Edition
Sometimes, after a failed merge replication(due to communication error) some rows on the subscriber became invisible for the publisher.
I've reproduced a case:
[START Short Version] All rows inserted between a comm failed sync and a fine sync became invisible. This seems to be caused because the subscriber keeps generating rows with the same generation number and the publisher doesn't look for this generation anymore. [END Short Version]
STEP 4: After THIRD SYNC (with comm error) Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.] When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process. Publisher
Orders Table
OrderId
000001
000002
000003
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81 Note: Publisher has the row but subscriber keeps the state in 81.
STEP 5: INSERT ANOTHER ORDER (BEFORE A GOOD SYNC)
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81 Note: Orders 000003 and 000004 have the same system info
STEP 6: After FOURTH SYNC Publisher
Orders Table
OrderId
000001
000002
000003
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81
Note: Even with a fine merge process Publisher didn't get the 000004 row and the Subscriber didn't update the __sysMC
STEP 7: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81
000005 8 8 81
STEP 8: After FIFTH SYNC
Publisher
Orders Table
OrderId
000001
000002
000003 000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81
000002 8 8 80
Note: Data from generation 8 merge correctly, but data from generation 6 keeps invisible to publisher.
STEP 9: DUMMY UPDATE ON GEN 6 ROWS
Action: I made a non relevant update to force the Subscriber update the generation. Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 10 81
000004 6 10 81
000005 8 8 80
Note: sysCG get its value updated correctly.
STEP 10: After SIXTH SYNC
Publisher
Orders Table
OrderId
000001
000002
000003 000004
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 80
000004 6 6 80
000002 8 8 80
Note: Data merges correctly.
[END Detailed case]
I could code a system that checks all the publication tables and updates all the last generation rows in case of communication error but i would really like to avoid doing it.
I don't know if I'm missing something or if this is a bug or a known issue.
This is a big problem for me because communication errors on cellular phone based connection are quite common and users keep working even if there's an error.
I have a table which visibility can be toggled by a text box. By default it is invisible. After it is made visible, clicking a sortable column header makes the table invisible. Does this mean sorting makes the table go back to its default visibility?
If a user selects 'value1' from the first parameter list then parameter list 2 & 3 should be open for the user to make their selection. BUT if the user selects 'value2' from parameter 1 then only parameter list 2 should be open and parameter list 3 invisible. Is this possible??
Hi, Do you know why when I run my ssis packages in the dev machine, the diagrams are not visible during run time. I can design the package but not sure why when I start the package, the diagrams in the control flow can not be viewed. Please note that if I do this on the server, I can see the diagrams during run time.
-The data is displayed in a table -The table groups by one field -Each table row calls a subreport -There is about 6 paramaters in the report -The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the list the corresponding subreport must be shown. -So i use a custom vbscript funtion to determine if a specific value was selected or not. This functionality is working fine.
My problem is if the user does not select all the values in the multi select then i want to make the row invisble and remove the whitespace so that there is not a gap between the other subreports which is shown.
I can make the subreport invisible inside the row but there is still the white space which does not display very nicly.
How can i make the row invisible if the vbscript function that is called returns a false value?
Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value)
I am joining on table A AND Table B ON the accountnumber field. The accountnumber on the tblB has some invisble characters at the end, ie "12344 ". The Table A does not have these invisble chars and is "12344". Due to the extra spaces at the end, the join fails and does not find a match for that acctno. I have used ltrim(rtrim) hoping that will elimante the extra spaces and find a match. Heres my qry:
select * from Tbla a inner join tblb B on ltrim(rtrim(ACCTNO))=ltrim(rtrim(ACCT))
How do I do this, so that it finds a match and returns the acctno.
I have been tring to get this one line figured out for a few days now. 'Job2 Info Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM '" & CompanyKey & "'" '<<-------HERE Dim cmd2 As New SqlCommand(selectSQL2, con) 'Job2 Select Try con.Open() reader = cmd2.ExecuteReader()
(I have the full code below.) So here is the problem, this code is not populating the datagrid. There is data in the table I am selecting from. When I log in, my CompanyKey value displays in the label as "21". When I take out the "CompanyKey" variable, and just type in 21, the grid is populated. It is confusing the heck out of me. I have tried it this way: selectSQL2 = "SELECT * FROM [" & CompanyKey & "]" -and this way: selectSQL2 = "SELECT * FROM & CompanyKey and all the other ways I could think of. I researched it and can just not get it to work any way I do it. Any suggestions? Full code below: ____________________________________
Partial Class _Default Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true") 'Job1(Info)Dim currentUserID currentUserID = Context.User.Identity.Name.ToString() Label1.Text = currentUserID Dim selectSQL1 As StringselectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')" Dim cmd1 As New SqlCommand(selectSQL1, con) Dim reader As SqlDataReader Dim CompanyKey 'Job1 Select Try con.Open() reader = cmd1.ExecuteReader()Do While reader.Read() CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message Finally con.Close() End Try 'Job2 Info Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM [" & CompanyKey & "]" Dim cmd2 As New SqlCommand(selectSQL2, con) 'Job2 Select Try con.Open() reader = cmd2.ExecuteReader() GridView1.DataSource = reader GridView1.DataBind() reader.Close()Catch err As Exception ReaderError.Text = "Error selecting record." ReaderError.Text &= err.Message Finally ReaderResults.Text = CompanyKey con.Close()End TryEnd Sub
I have a couple reports that have parameters on them. Problem is when the report is printed, the parameters are not displayed.
To solve this, I have added the selected values to the header section of the report in a text box. I now want to find away to hide these values when the report is being viewed on screen but visible when printed. Any suggestions are welcome. Thanks
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
I have used both data readers and data adapters(with datasets) in the projects that I have worked on. I am trying to get some clarification on when I should be using which one. I think I am doing this correctly but I want to be sure I am developing good habits.
As the name might suggest, it seems like a datareader is for only reading data. I have read that the data adapter and dataset are for a disconnected architecture. Or, that they can be used for this type of set up. I have been using the data adapter and datasets when writing to a database and the datareader when reading from a database.
Is this how these should be used? Is the data reader the best choice for reading data? Am I doing this the optimal way from a performance stand point?
......................................................thanks in advance
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:Â
[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
and also this:
[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.
I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected. It seems that somehow the 3 columns are not read in from the source file? ans alslo fiscal year, fiscal week is not set up up properly in my data destination? anyone faced such errors before?
When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".
USE [FileSharing] GO /****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Code] .....
Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25 Arithmetic overflow error converting expression to data type int. The statement has been terminated. (1 row(s) affected)
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
RE: XML Data source .. Expression? Variable? Connection? Error: unable to read the XML data.
I want my XML Data source to be an expression as i will be looping through a directory of xml files.
I don't see the expression property or the connection property??
I tried setting the XMLData property to @[User::filename], but that results in:
Information: 0x40043006 at Load XML Files, DTS.Pipeline: Prepare for Execute phase is beginning. Error: 0xC02090D0 at Load XML Files, XML Source [108]: The component "XML Source" (108) was unable to read the XML data. Error: 0xC0047019 at Load XML Files, DTS.Pipeline: component "XML Source" (108) failed the prepare phase and returned error code 0xC02090D0. Information: 0x4004300B at Load XML Files, DTS.Pipeline: "component "OLE DB Destination" (341)" wrote 0 rows. Task failed: Load XML Files Information: 0xC002F30E at Bad, File System Task: File or directory "d:jcpxmlLoadjcp2.xml.bad" was deleted. Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package.dtsx" finished: Failure. The program '[3312] Package.dtsx: DTS' has exited with code 0 (0x0).
I setup this package to import data from a Sharepoint list to a SQL Server data table. The primary key of my SQL table is mapped to the Title column of my Sharepoint list. There is a possibility that duplicate values will be entered in the Title field of the Sharepoint list. So when importing data into my table via SSIS, my package always error-out when there it comes across duplicate values. how you others have managed data integrity when importing from a Sharepoint list with the Title column being mapped to the primary key of a table.
"pRecordSet" is an ADO recordset. The database column "MyColumn" is of type "decimal(19,10)".
The most important question for me is, if the regional settings of the database server or the regional settings of the client PC are considered during the conversion from the string to the decimal value. For example in standard French regional settings the "." would not be recognized as decimal separator.
I am also wondering if the language of the database instance, in which this data is saved, is considered during this conversion or any other settings of this database instance.
So my general question is: Does anybody know exactly what rules apply during the above mentioned conversion?
I've question about how to handle structural datamodel changes in a datasource of PowerPivot. Suppose I'm developing a starmodel in SQL Server and sometimes a datatype changes or a name of a field changes in a table. It seems to me that PowerPivot handle this not gracefully as Analysis MD does (mostly). I received an error because of a wrong fieldname or even no error when a dattype changes in PowerPivot. Is this common or do I something wrong here. Does this mean that every time the datamodel changes the PowerPivot should be recreated? Or am I missing the clue here?
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?